Creating an excel file from 1s 8.3. Which is better - 1C or Excel? Do you have a question or need help from a consultant?

The 1C application has long been the most popular program among accountants, planners, economists and managers. Not only does it have a diverse number of configurations for various types activities, but also localization to accounting standards in several countries around the world. More and more enterprises are switching to accounting in this program. But the procedure for manually transferring data from other accounting programs to 1C is a rather long and boring task that takes a lot of time. If the enterprise kept records using Excel, then the transfer process can be significantly automated and accelerated.

Transferring data from Excel to 1C is required not only during the initial period of working with this program. Sometimes there is a need for something like this when, in the course of activity, you need to enter some lists stored in the spreadsheet book. For example, if you need to transfer price lists or orders from an online store. If the lists are small, you can enter them manually, but what if they contain hundreds of items? In order to speed up the procedure, you can resort to some additional options.

For automatic download Almost all types of documents are suitable:

• List of nomenclature;
• List of counterparties;
• List of prices;
• List of orders;
• Information about purchases or sales, etc.

It should be noted right away that 1C does not have built-in tools that would allow you to transfer data from Excel. For these purposes, you need to connect an external bootloader, which is a file in the format epf.

Data preparation

We will need to prepare the data in the Excel table itself.

In addition to these universal actions for preparing data in an Excel book, you will also need to bring the document into compliance with the requirements of the specific loader that we will use, but we will talk about this a little later.

Connecting an external bootloader

Connect an external bootloader with extension epf application 1C can be done both before and after preparing the Excel file. The main thing is that by the beginning of the download process both of these preparatory moments were resolved.

There are several external Excel table loaders for 1C, which were created by various developers. We will consider an example using a tool for processing information "Loading data from spreadsheet document» for version 1C 8.3.

One of the main databases that 1C works with is a list of products and services. Therefore, to describe the loading procedure from Excel, we will focus on the example of transferring this particular type of data.

1. We return to the processing window. Since we will load the product range, the switch in the parameter should be in the position "Directory". However, it is installed this way by default. You should switch it only when you are going to transfer another type of data: a tabular section or an information register. Next in the field "Directory view" Click on the button with the ellipsis on it. A drop-down list opens. In it we should select the item "Nomenclature".



2. After this, the handler automatically places the fields that the program uses in this type of reference book. It should be noted right away that it is not at all necessary to fill out all the fields.



3. Now open the portable Excel document again. If the name of its columns differs from the names of the 1C directory fields that contain the corresponding ones, then you need to rename these columns in Excel so that the names completely match. If the table contains columns for which there are no analogues in the directory, then they should be deleted. In our case, these columns are "Quantity" And "Price". It should also be added that the order of the columns in the document must strictly coincide with the one presented in the processing. If you do not have data for some of the columns that are displayed in the loader, then these columns can be left empty, but the numbering of those columns where there is data must match. For convenience and speed of editing, you can use a special Excel feature to quickly move columns in places.

   After these steps have been completed, click on the icon "Save", which is represented by an icon depicting a floppy disk in the upper left corner of the window. Then close the file by clicking on the standard close button.



4. We return to the 1C processing window. Click on the button "Open", which is shown as a yellow folder.



5. The file open window opens. Let's go to the directory where the Excel document we need is located. The default file display switch is set to extension mxl. In order to show the file we need, we need to move it to the position "Excel Sheet". After that, select the document to be transferred and click on the button "Open".



6. The content is then opened in the handler. To check that the data is filled in correctly, click on the button "Filling control".



7. As we can see, the fill control tool tells us that no errors were found.



8. Now let's move to the tab "Settings". IN "Search field" We put a tick in the line that will be unique for all names entered in the nomenclature directory. The fields most often used for this are "Vendor code" or "Name". This must be done so that when adding new items to the list, the data is not duplicated.



9. After all the data has been entered and the settings have been completed, you can proceed to directly loading the information into the directory. To do this, click on the inscription "Load data".



10. The download process is in progress. After its completion, you can go to the item reference book and make sure that all the necessary data has been added there.

We followed the procedure for adding data to the nomenclature directory in the 1C 8.3 program. For other directories and documents, downloading will be carried out according to the same principle, but with some nuances that the user can figure out on their own. It should also be noted that the procedure may differ for different third-party loaders, but the general approach remains the same for all: first, the processor loads information from the file into the window where it is edited, and only then it is added directly to the 1C database.

In 1C there are 2 ways to work with MS Excel files: through a COM object and built-in 1C tools using a spreadsheet document object. Let's look at both of these methods in more detail.

1. Work through a COM object.

This method requires installed MS Excel, and if you are working with a file on the server, then MS Excel must be installed on the server, if on the client, then MS Excel is also required on the client side.

Example (you can see it in text form at the end of the article):

It should be noted that all objects, methods and properties provided by the COM object "Excel.Application" are objects, methods and properties of VBA, the MS Office programming language.

Note:

Sometimes you need to work with an existing template. Then we need to store this template somewhere so that all the necessary users have access to it. However, there is a simpler solution, save the template as a binary layout.

2. Work through Tabular Document 1C.

The 1C spreadsheet document supports the MS Excel format for recording starting from platform 8, but for opening only starting from platform 8.3.6. Moreover, when opened, all sheets are loaded into one spreadsheet document. Since version 8.3.10, when loading, different sheets are loaded as different areas.

An example entry is very simple and does not require special attention:

However, here we have a problem. When recording from 1C to Excel workbook By default, displaying sheet names is disabled.

This problem can be solved in 2 ways, 1 - in the book itself, enable the display of sheets in the settings (not all users will agree to do this), 2 - do this through a COM object (we again need MS Excel installed).

After this small addition, sheet labels will be visible in the MS Excel file.

Reading MS Excel is not such an easy task, since the read method is only available on the server or thick client. To do this, we need to transfer the MS Excel file to the server.

&OnClient

Procedure CreateDocument()

Exception

EndAttempt;

Book = Excel.WorkBooks.Add(); //Create new book MS Excel

Sheet = Book.WorkSheets.Add(); //Add a sheet

Sheet.Name = "Example from 1C"; //Set the sheet name

Sheet.Cells(1,1).Value = "Let's create a formula";!}

Sheet.Cells(2,1).Value = 1;

Sheet.Cells(2,2).Value = 2;

Sheet.Cells(2,3).Formula = "=A2+B2";

Sheet.Cells(3,1).Value = CurrentDate();

Book.SaveAs("C:\1\Test.xlsx");

Book.Close();

EndProcedure

&On server

Function GetLayoutServer()

Return FormAttributesValue("Object").GetLayout("TemplateForUpload"); //This way we get the external processing layout;

EndFunction

&OnClient

Procedure Work()

Excel = New COMObject("Excel.Application");

Exception

Report("Unsuccessful attempt to connect Excel components. Perhaps Excel program is not installed on this computer!");

EndAttempt;

Layout = GetLayoutServer();

Layout.Write(TemporaryFileName);

Do you have a question or need help from a consultant?

Book = Excel.WorkBooks.Open(TemporaryFileName);

SheetTemplate = Book.WorkSheets(1);

SheetTemplate.Cells(6,1).Value = "Date:";!}

SheetTemplate.Cells(6,2).Value = CurrentDate();

SheetTemplate.NumberFormat = "dd/mm/yy;@"; // Give the date format, this format was obtained by recording a macro in MS Excel

SheetTemplate.Columns("B:B").EntireColumn.AutoFit; // Stretch the column so that the date fits exactly

Book.SaveAs(FileName);

Book.Close();

EndProcedure

&OnClient

EndProcedure

&OnClient

Procedure WriteTabularDocument()

TabularDocument = New TabularDocument();

TabularDocument.Area("R1C1").Text = "Example of a record in MS Excel from 1C";

TabularDocument.Write("C:\1\Test2.xls",TabularDocumentFileType.XLSX);

Excel = New COMObject("Excel.Application");

Excel.WorkBooks.Open("C:\1\Test2.xls");

Excel.Visible = 0;

Excel.ActiveWindow.DisplayWorkbookTabs = 1;

Excel.ActiveWindow.TabRatio = 0.6;

Excel.ActiveWorkbook.Save();

Excel.Application.Quit()

EndProcedure

BinaryData = New BinaryData("C:\1\test2.xlsx");

Address=PlaceInTemporaryStorage(BinaryData,ThisForm.UniqueIdentifier) ​​;

TabularDocument = UploadOnServer(Address);

TabularDocument.Show();

EndProcedure

&On server

Function UploadOnServer(Address)

TemporaryFileName = GetTemporaryFileName("xlsx");

FileData = GetFromTemporaryStorage(Address);

FileData.Write(TemporaryFileName);

TabularDocument = New TabularDocument();

return TabularDocument;

• When exchanging information between counterparties (price lists, reconciliation reports, etc.);
• To facilitate the work of operators in cases where the main accounting is carried out in 1C, and some of it is performed in Excel tables;
• When filling the database for the first time.

To perform this operation, you can use both standard functionality, available with a subscription to information technology support (ITS), and independently written processing, implemented through various connection options. In our article we will try to analyze as fully as possible all possible cases and answer most of the existing questions regarding loading data from Excel into 1C.

Universal mechanism

On the ITS disks, as well as on the 1C portal, by going to the “Technological Support” -> “Universal Reports and Processing” menu, the corresponding processing is located in the “Loading data from a spreadsheet document” folder.

When you launch it, the form opens (Fig. 1):

As you can see from the form, it allows you to transfer information to the following configuration objects:

1. Directory;
2. Tabular part document or reference book;
3. Register of information.

Depending on the position in which the switch is set, the object selection field changes.

The processing tabular form is filled in when the downloaded file is opened.

Typical processing supports downloads from:

• Developed by specialists from 1C, file formatmxl;
• Lisztxls saved in the formatExcel 97-2003;
• Text filetxt;
• Tablesdbf.

Excel files of large size can take quite a long time to load, so if it is assumed that data loading can occur in several stages or you know for sure that you will have to open an existing file several times, to save nerves and time, it is better to save the information from it in mxl format. This can be done directly from the processing form.

It is important to understand that if there are line groupings, subheadings and notes in the source file, they will need to be removed manually.

Now let's move on to the “Settings” tab (Fig. 2):

Fig.2

Quite often, Excel tables contain a header with all sorts of details and data (name printed form, details of the counterparty, date and number of the incoming document, column names, etc.), in order to exclude their processing by the program on the form, in the “First line of a spreadsheet document” attribute, you must indicate the first line with the transferred information

After selecting a metadata object into which information will be recorded, the tabular part of the “Settings” tab will be automatically filled in with the names of details, descriptions of their types, and other important information. A separate section should be devoted to the consideration of the columns of the tabular part of the “Settings” tab.

Columns "Settings"

Marking – by checking or unchecking a checkbox in a line, it is determined whether the corresponding attribute will be filled in.

Attribute representation – here the synonym (name) of the metadata attribute is written, as it is specified in the configurator.

Search field - if you check this field, processing will search for elements using the corresponding details and, if successful, change existing data; an additional function of this field is protection against duplicates.

Description of types – displays the data type that a particular metadata attribute has.

Boot mode – offers three options to choose from (Fig. 3):

Fig.3

• Search – a search will be carried out for the corresponding element; if it is missing, a new one can be created;
• Set – a certain value is subordinately set;
• Calculate – in this case, the result of calculating the expression specified in the “Connection condition/Expression for value” column will be set in the field of the created element.

In the latter case, activating the Expression field will open the form (Fig. 4).

Fig.4

Column number is a field used to indicate which column of the Excel table should be filled with data.

Default value – quite often a situation arises when the downloaded file does not contain all the data necessary to record an element; in this case, the information contained in this field will be filled in.

Link condition/Expression for a value – we have already partially touched on this field when we looked at calculated fields; in addition, you can specify the condition in accordance with which the source data will be synchronized.

This is, in principle, all the information that is available on the “Settings” tab.

In order not to waste a lot of time each time loading, writing correspondence and expressions, the developers have provided the ability to save the configuration option to a file with the mxlz extension.

You can check the correctness of the transferred data by clicking on the “Filling Control” button (Fig. 1). After this, you can start the download process. You will be notified separately about the successful completion of the procedure or unusual situations.

To load data from Excel into information databases“Management and Trade” there is another mechanism. It is less universal than the above method, but does not require an ITS subscription and is included in the standard delivery.

This processing can be found on the “Purchases” tab, in the “Service” menu, it is called “Loading supplier prices from files” (Fig. 5)

Fig.5

The processing form contains:

1. A date selection field that will indicate for what time this price is valid;
2. Field for selecting the counterparty who sent their price list;
3. A button that allows you to select the type of prices to be set;
4. A tabular part that can be filled with downloaded data.

This shape can be seen in Fig. 6

Fig.6

The explanation at the top of the form explains how to use the first tab of the form.

After selecting a counterparty (depending on whether he is a supplier, commission agent or seller), additional columns for each type of price will become available in the table.

When working through the web interface, some browsers may require the installation of a browser add-on (Fig. 7). We need to click the “Start installation” button and restart our connection.

Fig.7

After this, using the clipboard, we will be able to transfer information from one table to another. When the columns we need (“Article”, “Name”, “Price”) are filled in, we click the “Next” button and go to the second page (Fig. 8)

Fig.8

The program will automatically search for matches within the database and, if none are found, offer options for eliminating the error. Appearance The tabular part can be controlled using a switch. In addition, the user can independently compare the elements of the downloaded file with the data available in the database.

• Register everything;
• Register only those that have changed compared to those already in the database.

In the text field you can enter a comment that will be recorded in the document (Fig. 9):

Fig.9

After processing is completed:

• The corresponding element will be created in the “Supplier Nomenclature” directory (if it did not exist);
• The directory element “Nomenclature” will be associated with it;
• The document “Setting item prices” will be created and posted, indicating: supplier, type of prices and date of data recording.

The “Loading products from external files” processing works in a similar way.

DIY transfer processing options

The main problem in extracting data from an Excel file is that 1C does not have a built-in unambiguous mechanism for opening it. There are several options for connecting Excel to 1C:

• Via Microsoft ADODB – enough quick way, which, as a rule, is applicable for both file and client-server database storage options;
• Through Microsoft use Office is a method that sometimes fails when working with SQL databases; as a rule, it works somewhat slower than the first method, and it also requires Office installed;
• Via Libre Office - unlike the previous method, it is free, in addition to xls and xlsx formats, it also supports its own tables, but requires the installed LibreOffice package and some preparation of the downloaded file (the first line of the table must contain the names of the columns).

Let's take a closer look various ways and options.

Via ADODB.Connection

In general, ADO stands for ActiveX Data Object and is used for programmatic access to various databases. The biggest problem when creating any connection to a third-party file (including Excel) is to correctly compose the connection string.

For Excel files, there are three options:

Connection string options:

• Provider – the driver used is defined here;
• Data Source – defines the name of the file that we will open;
• Extended Properties – here you can specify whether a header line is needed for the table (HDR = YES indicates that the data will be read from the first line, HDR = NO - what from the second), whether the file is opened read-only (ReadOnly) and some other additional parameters.

Having created a connection string, we can connect to the downloaded file (Fig. 13)

Fig.13

Now we can use a simple request (Fig. 14) to start retrieving information from the downloaded file.

In this case, the “Sheet” parameter determines which sheet from the Excel workbook we will work with.

The set of records stored in a worksheet can be read using a Recordset object. In this case, the first record of the sheet can be obtained with the BOF (beginning of file) parameter, and the last EOF (end of the file).

Via Excel application

The main difference from the previous method is that in addition to database drivers, Excel must be installed on the computer where the connection is made. Only in this case can we initialize the application to read data from the table (Fig. 16).

This COM object has several child parameters, but the main one for us, given the current conditions of the task, is the WorkBooks parameter (Fig. 17).

After initializing the workbook, it is necessary to determine the sheet from which data will be read (Fig. 18).

After this, you can loop through the rows and columns of the table of the file being opened.

A few words about possible errors

The lion's share of errors when connecting to a file occurs due to the fact that the file is already occupied by another application. It’s good if you can see in the taskbar that Excel is running on the computer, but if you or another user opened it from external processing, this can only be visually determined through the “Task Manager”, so do not forget to close the connection before completing the transfer procedure:

In the case of working through ADO (Fig. 19);

Fig.19

• In the case of working with the application (Fig. 20).

Rice. 20.

It is best to organize the connection and the procedure for completing work with data within the Attempt-Exception-EndAttempt construct, calling an error description in an exceptional situation. Although this sometimes slows down the work, it makes it much easier to determine the cause of the error and, ultimately, how to eliminate it.