1s 8 loading data from a spreadsheet document. Accounting info. DIY transfer processing options

This method is simple. Its essence is that the object SpreadsheetDocument has methods:

burn (< ИмяФайла>, < ТипФайлаТаблицы >) to upload data to a file;
Read (< ИмяФайла>, < СпособЧтенияЗначений >) to load data from a file.

Attention!

The Write() method is available on both the client and the server. The Read() method is only available on the server side. Need to remember this
when planning client-server interaction.

Consider an example of saving a spreadsheet document to a file. It is necessary to create and fill in the TableDocument object in any way, and unloading file is done with just one line:

TabDoc . Write(FilePath, SpreadsheetDocumentFileType.XLSX);

Here TabDoc- generated spreadsheet, The path to the file- the name of the file to upload, SpreadsheetDocumentFileType.XLSX— the format of the generated file. The following Excel formats are supported:

XLS95 - Excel 95 format;
XLS97 - Excel 97 format;
XLSX is Excel 2007 format.

TabDoc = New SpreadsheetDocument;
TabDoc . Read(PathToFile, WayToReadSpreadsheetDocumentValues.Value);

Here The path to the file- Path to the downloaded Excel file. WayToReadValuesSpreadDocument.Value determines how to interpret the data read from the source document. Options available:

Meaning;
Text.

Exchange via OLE

Exchange through OLE automation technology is perhaps the most common option for working with Excel files programmatically. It allows you to use all the functionality provided by Excel, but is slower than other methods. Exchange via OLE requires MS Excel to be installed:

On the end user's computer, if the exchange takes place on the client side;
On the 1C:Enterprise server computer, if the exchange takes place on the server side.

Example unloading:

// Create a COM object
Excel = New COMObject("Excel.Application");
// Disable warnings and questions
Excel . displayalerts = false;
// Create a new book
Book = Excel. workbooks. add();
// Positioning on the first sheet
Sheet = Book. Worksheets(1);

// Write value to cell
Sheet . Cells(RowNumber, ColumnNumber). Value = CellValue;

// Save the file
Book . SaveAs(FileName);

Excel . Quit();
Excel = 0;

Examples reading:

// -- OPTION 1 --

// Create a COM object
Excel = New COMObject("Excel.Application");
// Open book
Book = Excel. workbooks. Open( The path to the file );

Sheet = Book. Worksheets(1);

// Close the book
Book . Close(0);

// Close Excel and free memory
Excel . Quit();
Excel = 0 ;

// —— OPTION 2 ——

// Open book
Book = GetCOMObject( The path to the file );
// Positioning on the desired sheet
Sheet = Book. Worksheets(1);

// Read the value of the cell, this is usually where the cell bypass loop is located
CellValue = Sheet. Cells(RowNumber, ColumnNumber). value;

// Close the book
Book . application. Qui t();

For bypass all filled rows of the Excel sheet, you can use the following tricks:

// -- OPTION 1 --
Number of Rows = Sheet. Cells(1 , 1 ). SpecialCells(11 ). Row;
For RowNumber = 1 By Number Of Rows Loop
CellValue = Sheet. Cells(RowNumber, ColumnNumber). value;
EndCycle;

// —— OPTION 2 ——
LineNumber = 0 ;
While True Loop
LineNumber = LineNumber + 1 ;
CellValue = Sheet. Cells(RowNumber, ColumnNumber). value;
If NOT ValueFilled(CellValue) Then
abort;
EndIf;
EndCycle;

Instead of sequentially traversing all the lines of the sheet, you can dump all data into an array and work with him. This approach will be faster when reading a large amount of data:

TotalColumns = Sheet. Cells(1 , 1 ). SpecialCells(11 ). Column;
TotalRows = Sheet. Cells(1 , 1 ). SpecialCells(11 ). Row;

Region = Sheet. Range(Sheet. Cells(1 , 1 ), List. Cells(TotalRows, TotalColumns));
Data = Region. value. unload();

The table below lists the most requested properties and methods for working with Excel via OLE:

Action	Code	A comment
Working with the application
Setting Application Window Visibility	Excel . visible= false;
Setting the warning display mode (display/not display)	Excel . DisplayAlerts= false;
Closing the application	Excel . Quit();
Working with a book
Create a new book	Book = Excel. workbooks. Add();
Opening an existing workbook	Book = Excel. workbooks. Open(FileName);
Saving a book	Book . SaveAs(FileName);
book closing	Book . Close(0);
Working with a sheet
Setting the current sheet	Sheet = Book. WorkSheets(SheetNumber);
Name setting	Sheet . Name = Name;
Setting protection	Sheet . Protect();
Removal of protection	Sheet . UnProtect();
Setting the page orientation	Sheet . PageSetup. Orientation = 2;	1 - portrait, 2 - landscape
Setting the left border	Sheet . PageSetup. LeftMargin = Excel. CentimetersToPoints(Centimeters);
Setting the upper limit	Sheet . PageSetup. TopMargin = Excel. CentimetersToPoints(Centimeters);
Setting the right border	Sheet . PageSetup. RightMargin = Excel. CentimetersToPoints(Centimeters);
Setting the lower bound	Sheet . PageSetup. BottomMargin = Excel. CentimetersToPoints(Centimeters);
Working with rows, columns, cells
Setting the column width	Sheet . Columns(ColumnNumber). ColumnWidth = Width;
Removing a line	Sheet . Rows(RowNumber). Delete();
Deleting a column	Sheet . Columns(ColumnNumber). delete();
Deleting a cell	Sheet . Cells(RowNumber, ColumnNumber). Delete();
Setting a value	Sheet . Cells(RowNumber, ColumnNumber). Value = Value;
Merging cells	Sheet . Range(Sheet. Cells(RowNumber, ColumnNumber), Sheet. Cells(RowNumber1, ColumnNumber1)). Merge();
Installing the font	Sheet . Cells(RowNumber, ColumnNumber). Font. Name = FontName;
Setting the font size	Sheet . Cells(RowNumber, ColumnNumber). Font. Size = FontSize;
Bold font setting	Sheet . Cells(RowNumber, ColumnNumber). Font. Bold = 1 ;	1 - bold, 0 - normal
Setting italic	Sheet . Cells(RowNumber, ColumnNumber). Font. Italic = 1 ;	1 - italic, 0 - normal
Setting an underlined font	Sheet . Cells(RowNumber, ColumnNumber). Font. Underline = 2 ;	2 - underlined, 1 - no

In order to find out which property needs to be changed or which method to call, you can use macros Excel. If you record a macro with the required actions, then you can then view the program code in the VBA of the recorded macro.

Using COMSafeArray

When unloading large amounts of data from 1C to Excel, you can use the object to speed up COMSafeArray. As defined by the syntax helper, COMSafeArray is an object wrapper over a multidimensional array SafeArray from COM. Allows you to create and use SafeArray to exchange data between COM objects. Simply put, this is an array of values that can be used to exchange between applications using OLE technology.

// Create COMSafeArray
ArrayCom = New COMSafeArray("VT_Variant", TotalColumns, TotalRows);
// Populate COMSafeArray
For Str = 0 Total Rows - 1 Loop
For Count = 0 Total Columns - 1 Cycle
ArrayCom . SetValue(Number, Str, Value);
EndCycle;
EndCycle;
// Assigning the Excel sheet area values from COMSafeArray
Sheet . Range(Sheet. Cells(1 , 1 ), List. Cells(TotalRows, TotalColumns)). Value = ArrayCom;

Exchange via ADO

An Excel file, when exchanged through ADO, is a database that can be accessed using SQL queries. Installation of MS Excel is not required, but an ODBC driver is required, through which access will be performed. The ODBC driver used is determined by specifying the connection string to the file. Usually, the required driver is already installed on the computer.

Exchange through ADO is noticeably faster than exchange through OLE, but when unloading, there is no way to use Excel functionality for decorating cells, marking pages, setting formulas, etc.

Example unloading:

Connection = New COMObject("ADODB.Connection");

Compound . ConnectionString="

|DataSource=" + FileName + ";
;
Compound . open(); // Open connection

// Create a COM object for the command
Command = New COMObject("ADODB.Command");
Team

// Assign command text to create a table
Team . commandtext= "CREATE TABLE [Sheet1] (Column1 char(255), Column2 date, Column3 int, Column4 float)";
Team . execute(); // Command execution

// Assigning command text to add a table row
Team . commandtext= "INSERT INTO [Sheet1] (Column1, Column2, Column3, Column4) values ('abvgdeo', '8/11/2017', '12345', '12345,6789')";
Command.Execute(); // Command execution

// Deleting the command and closing the connection
command = undefined;
Compound . close();
Connection = Undefined;

To create a new sheet and form its structure, you can use the objects ADOX.Catalog And ADOX.Table. In this case, the code will look like:

// Create a COM object to work with the book
Book = New COMObject("ADOX.Catalog");
Book . ActiveConnection = Connection;

// Create a COM object to work with the data structure on the sheet
Table = New COMObject("ADOX.Table");
Table . Name = "Sheet1" ;
Table . Columns. Append("Column1" , 202 );
Table . Columns. Append("Column2" , 7 );
Table . Columns. Append("Column3" , 5 );
Table . Columns. Append("Column4" , 5 );

// Create a sheet in the book with the described structure
Book . tables. append(table);
table = undefined;
book = undefined;

In the above example, in the method

Table . Columns. Append("Column1", 202);

the second parameter specifies the column type. The parameter is optional, here are some column type values:

5-adDouble;
6 - adCurrency;
7 - adDate;
11 - adBoolean;
202 - adVarWChar;
203-adLongVarWChar.

Example reading:

// Create a COM object for the connection
Connection = New COMObject("ADODB.Connection");

// Set connection string
Compound . ConnectionString="
|Provider=Microsoft.ACE.OLEDB.12.0;
|DataSource=" + FileName + ";
|Extended Properties=""Excel 12.0 XML;HDR=YES"";";
Compound . open(); // Open connection

// Create a COM object to receive a selection
Selection = New COMObject("ADODB.Recordset");
RequestText = "SELECT * FROM [Sheet1$]";

// Execute the request
Sample . Open(QueryText, Connection);

// Bypass the result of the selection
While NOT Sampling. EOF() Loop
ColumnValue1 = Selection. fields. Item("Column1"). value ; // Referencing by column name
Column2 value = selection. fields. Item(0 ). value; // Retrieve by column index
Sample . MoveNext();
EndCycle;

Sample . close();
Sample = undefined;
Compound . close();
Connection = undefined;

In the connection string, the parameter HDR determines how the first line on the sheet will be perceived. Possible options:

YES - the first line is perceived as the names of the columns. Values can be accessed by name and by column index.
NO - The first line is taken as data. Values can only be accessed by column index.

In the examples given, only a few ADO objects are considered. The ADO object model consists of the following objects:

connection;
command;
recordset;
record;
fields;
Stream;
errors;
parameters;
properties.

Upload without programming

To save data from 1C to Excel, it is not always advisable to resort to programming. If in the Enterprise mode the user can display the data required for uploading, then it is possible to save them in Excel without programming.

To save a spreadsheet document (for example, the result of a report), you can call the command Save or Save as… main menu.

In the window that opens, you need to select the directory, name and format of the saved file.

To save the data of dynamic lists (for example, an item list), you must:

Output data to a spreadsheet using the command More ⇒ Show list...;
Save the spreadsheet document in the required format.

11
ATTENTION! When using the codes described below, remove the symbol _ Let's look at examples of how to do it! =) I know 2 ways: 1. By replacing the template text in the Word layout. 2. By pasting text with... 9
Convert tables from Excel to MXL - download Converter Excel v MXL: For 8.x - For 7.7 - // Select Microsoft Excel file Procedure FileNameStartChoice(Element, StdProcessing) StdProcessing = False; DialogFile = New FileDialog(FileDialogMode. 3
I think everyone has come across a single-sheet excel file from 1C, which at first glance does not have the ability to add sheets. Everything is not so scary - they were simply hidden or, most often, they were not even displayed (due to the creation ... 2
Procedure SaveTableToExcelFile(Table, FullFileName=" " , OpenFileSelectDialog=True, UploadedColumns=" ") Export If EmptyString(FullFileName) OR OpenFileSelectDialog Then Dialog =...

Price list in Excel "PriceOnlineKhimSnab.xls" - Microsoft Office Excel 97-2003 (.xls)
Typical Configuration Trade Management Rev.10.3, Release 10.3.10.4, Platform 8.2 (8.2.10.77)
It is necessary to load into 1C from Excel not only the names of goods and prices as in Example 1, but also the articles and barcodes, while it is necessary that the nomenclature is not loaded into one catalog (group), but distributed by manufacturers in the 1C nomenclature directory.

In order to be able to download the nomenclature reference book from the Price List in 1C: Trade Management 8 with automatic distribution into groups (manufacturers), it will be necessary to prepare a file form for uploading. You will need to add a column indicating the unit of measure (pieces) and the manufacturer of the goods, while the name of the manufacturer in the price list must match the name of the group in the nomenclature directory of the 1C: Trade Management 8 program.

We use the typical processing "Loading DataFrom SpreadsheetDocument.epf" which goes to the ITS.

1. We launch the 1C program

2. To start processing, select the menu item "File">« Open» .

3. In the window that opens, find the processing file, which is located on the ITS disk in the directory \1CIts\EXE\EXTREPS\UNIREPS82\UploadFromTableDocument

If you previously copied the processing file to your computer, you can select it.

A window will open

Please note that data loading will be carried out in three stages:

Stage 1- loading names and setting the values of the details of the new elements of the directories necessary to start work (unit of measurement, VAT rate, product / service attribute). - loading barcodes

Set the "Download Mode" - Download to the directory

"Directory view" - Nomenclature

5. In the "Spreadsheet document" tab, click the "Open file" button, in the selection window that appears, we find the directory in which we have the price list in Excel from which we plan to upload data to 1C.

The file may not be displayed initially. Set the File Type to "Excel Sheet (*.xls)"

Choose our file

The details of the nomenclature card are displayed.

8. Set up the parameters for loading titles

For convenience, set "Manual column numbering"

At this stage, we load only those details that will allow us to start working with the program, so we uncheck the boxes that are not of interest to us yet

“The first line of data of a spreadsheet document” - we look at our price list, discard the header, the data for loading starts from the ninth line - we put “9” (see Fig. 2 “Price list”)

“Do not create new elements” - we DO NOT check the box, the option is needed if we plan only to change existing elements.

Lines "Name" and "Full name" - set the Loading mode "Search", "No. Columns of the spreadsheet document" - in our price list the name in the third column - set "3"

The line "Parent" - in the column "Load mode" select "Search", in the column "No. Columns of the spreadsheet document" - in our price list, manufacturers in the fourth column - put "4".

If there is no need to distribute by manufacturers and all new items need to be loaded into one folder, then in the "Download mode" column, select "Install", in the "Default value" column, select the product group into which we will load data from the price list.

Line "Article" - put a checkmark in the column "Search field" (second column) - the program will check for the presence in the 1C database of an element with such an article, and if it already exists, then a new element with this name will not be created. We set the Loading mode "Search", "No. Columns of the spreadsheet document" - in our price list, the article in the second column - we put "2"

The line "Basic unit of measure" - in the column "Load mode" select "Set", in the column "Default value" select the unit of measure (in our case - pcs.)

The line "Weight" - in our case, the product is NOT weighted, so "Loading mode" select "Set", in the column "Default value" select "No"

The line "VAT rate" - in our case, for all downloaded items we set VAT 18%, for this, in the column "Download mode" select "Set", in the column "Default value" select "18%".

If the goods come with different VAT, which is indicated in a separate column of the downloadable price list, then in the "Download mode" column, set "Search" and the number of the corresponding price list column in "Spreadsheet Document Column No."

Line "Service" - in the column "Download mode" select "Install", in the column "Default value" select "No" (in our case, all positions are goods).

Line "Nomenclature type" - in the column "Loading mode" select "Install", in the column "Default value" select "Product".

We have set the minimum values for the new elements of the stock list reference book, which will allow you to start working.

If you are ready to set the values of the missing details now, then specify them as well.

Click "Download" in the lower right corner, click "Yes" to the question

8. We check the presence of new elements in the corresponding groups of the reference book of the nomenclature.

At the moment, we have loaded the product names from the price list in Excel into the 1C nomenclature reference book.

As you can see, the details "Reporting Unit" and "Remaining Storage Unit" remained empty.

The next step is to set these details, because. they will be required when loading prices, barcodes and further work.

There are two options:

Manual.

These details are created by the program automatically, when you first open and save an item card. If manual installation is acceptable, then you can skip this step and go directly to the stage of loading prices. If you do not plan to download barcodes, then it is quite acceptable to even leave it as it is - the first time you use the stock item, you will need to open and save the product card.

Disadvantage: the manual procedure for opening and saving can be quite laborious and take quite a long time with a large number of directory entries.

Auto.

Automatic loading of units of measurement is also possible, but it is designed for advanced users and will require basic skills in 1C programming.

There are two options: with identification by article or name.

Identification by Article is more preferable, but there may not be a column with articles in the price list.

Option 1. Identification by Article

We put a “daw” in the “Do not create new elements” field and remove the “daws” from all lines.

Line "Article" - check the box in the column "Search field" (second column). We set the Loading mode "Search", "No. Columns of the spreadsheet document" - in our price list, the article in the second column - we put "2"

In the lines "Unit for reports" and "Unit for storing balances" in the "Load mode" field, select "Calculate". In the field "Expression" we write the algorithm in the built-in programming language 1C. For example, our specialist from the implementation department suggested the following code in a couple of minutes:

Nomenclature Reference = References.Nomenclature.FindBy Attributes("Article", CurrentData["Article"]); BaseUnit of Measurement = Reference books.Classifier of Units of Measurement.FindByCode(796); CurrentReference=References.Units.FindBy Props("UnitBy Classifier,BasicUnitofMeasurement,NomenclatureRef); If CurrentReferenceEmpty() Then FoundUnitObject = References.Units.CreateElement(); FoundUnitObject.Name = AbbreviatedLP(BasicUnit Of Measurement); Found UnitObject.UnitByClassifier = BaseUnit; FoundUnitObject.Factor = 1; FoundUnitObject.Owner = RefNomenclature; Attempt FoundUnitObject.Write(); Result = FoundUnitObject.Ref; Exception Report("Failed to write"); EndTry; Otherwise Result =CurRef. Link;EndIf;

If it is planned to repeat this procedure multiple times, then a verification procedure must be additionally added to the algorithm in order to exclude the re-entry of units of measure with the same name.

Option 2. In case of identification by Name

Check the box "Do not create new items"

We remove the "daws" from all lines

Line "Name" - check the box in the column "Search field" (second column). We set the Loading mode “Search”, “No. Columns of the spreadsheet document” - in our price list, the name in the third column - we put “3”

In the lines "Unit for reports" and "Unit for storing balances" in the "Load mode" field, select "Calculate". In the "Expression" field, we write the algorithm in the built-in 1C programming language, for example, this:

ReferenceNomenclature = References.Nomenclature.FindByName(CurrentData["Name"]);
BaseUnit of Measurement = Reference books.Classifier of Units of Measurement.FindByCode(796);
TekRef=References.Units of Measurement.FindBy Attributes("
UnitByClassifier",BasicUnitofMeasurement,NomenclatureRef);
If CurrentQuest.Empty() Then FoundUnitObject = References.Units.CreateElement();
FoundUnitObject.Name = AbbreviatedLP(BasicUnitofMeasurement);
FoundUnitObject.UnitByClassifier = BaseUnitMeasurement;
FoundUnitObject.Coefficient = 1;
FoundUnitObject.Owner = NomenclatureReference;
Attempt FoundUnitObject.Write();
Result = FoundUnitObject.Reference;
Exception Report("Failed to write");
End of Attempt;
Otherwise Result = CurrentReference.Reference;
EndIf;

// Company "Online" 2010, www.online-ufa.ru

9. Open the product card, check the correctness of the details loading

If everything is correct, then you can proceed to the second stage - loading prices from the price list into Excel.

If in the future you plan to use this procedure for downloading data with the same settings for download options, we recommend that you save the current settings. To do this, click the "Save settings" button and clearly name the current setting, for example, "Load product names from the Excel price list".

In "Trade Management" Rev.10.3, prices are set using the document "Price setting of items".

We use the same processing "Loading DataFrom SpreadsheetDocument.epf" from the ITS disk.

11. Set "Loading mode" - "Loading to the tabular part".

The document journal "Setting item prices" opens.

Here you can select an existing document in which we want to add new elements, or create a New one (recommended), in the document we indicate which “Price Type” we will upload (in our example, “Retail”).

Click "OK" and save the empty document for now.

Now select this new saved document from the journal in the "Link" line.

In the "Tabular part" field, select "Products".

13. Please note that now you need to go to the “Spreadsheet Document” tab and again select our price list in Excel from which we downloaded the names, and now we will download prices (see paragraph 5).

14. Set up boot options

Again, set the "First row of spreadsheet document data" - as when loading names, set "9" and "Manual column numbering".

In the "Nomenclature" line, set the loading mode to "Search", "Spreadsheet Document Column No." - in our example, we set "3". If identification by article is required, then in the "Search by" column, select "Article", and in the "No. of the Spreadsheet Document Column" we put the number of the column with articles - in our example, "2".

In the “Price” line, set the download mode to “Search”, “Spreadsheet Document Column No.” - put the number of the column with the prices that we want to load, first we will load the retail price, which means we put “5”.

In the "Currency" line, set the Download mode to "Install", select the currency (in the example "rubles")

In the "Price type" line, set the Download mode to "Install", select the price that we will download as "Retail".

If in your price list the prices of goods are given in different currencies, which is indicated in a separate column of the downloadable price list, then in the "Download mode" column, set "Search" and the number of the corresponding price list column in "Spreadsheet Document Column No.".

In the line “Price calculation method”, set the Loading mode to “Install”, select “By percentage margin on the base type”

Click "Download" and answer "Yes"

15. Open the document "Setting item prices", into which the data was loaded (by clicking the button with the image of a magnifying glass in the "Link" line)

16. We check the document, if everything is in order, click "OK".

Prices must be fixed.

17. Open the stock list directory, check the “Prices” tab in the loaded items card, and click the “Reread current prices” button.

If everything is in order, then you can start loading the remaining prices (wholesale, purchase, etc.). The procedure is similar.

Please note that 1C: Trade Management 8 supports the calculation of some prices based on others. Therefore, it will probably be enough to load only one base price, for example, Retail or, vice versa, Purchase, and for other prices, specify the formation algorithms (discount%, markup%, rounding to the sign, etc.).

If in the future you plan to use this procedure for downloading data with the same settings for download options, we recommend that you save the current settings. To do this, click the "Save settings" button and clearly name the current setting, for example, "Load Retail Prices from the Excel price list".

Next time you can load the saved settings by clicking on the "Restore settings" button and selecting the one you need from the list.

We use the same processing “Loading DataFrom SpreadsheetDocument.epf” from the ITS disk.

19. Set the "Loading mode" - "Loading into the register of information."
"Register type" select "Barcodes".

20. Please note that now you need to go to the “Spreadsheet Document” tab and again select our price list in Excel from which we loaded the names and prices, and now we will upload barcodes (see paragraph 5).

21. Set Up Boot Options

Again, set the parameter "First line of spreadsheet document data" - set "9" and "Manual column numbering".

Configuring boot options

We remove the "daws" from all the lines from which it is possible.

In the remaining lines, set the parameters

In the line "Barcode" we set the Loading Mode "Search", in the column "No. Columns of the spreadsheet document" we write as in our price list "9".

In the "Owner" line in the "Description of types" column, open the "Edit data type" window, set (leave) a checkbox only in the "Nomenclature" field. Set the download mode to "Search". For identification by Article, in the column “Column No. of the spreadsheet document” we write the number of the column in which articles are indicated in our price list - i.e. "2". In the "Search by" column, set the "Article".

If there is no column with articles in the price list, then in the “Search by” column, select “Name”, and in the cell “No. Columns of the spreadsheet document” indicate the number of the column of the price list with names.

In the "Barcode Type" line in the "Download Mode" column, select "Install", in the "Default Value" column, select "EAN13".

In the "Unit of measurement" line in the "Load mode" field, select "Calculate". In the field "Expression" we write the algorithm in the built-in programming language 1C. For example, this code:

ReferenceNomenclature = References.Nomenclature.FindBy Name(CurrentData["Owner"]); Result=NomenclatureReference.StandardStorageUnit;

In the "Quality" line in the "Download Mode" column, select "Install", in the "Default Value" column, select "New".

In the lines "Nomenclature characteristic", "Nomenclature series", "Delete code" in the "Load mode" column, select "Install", in the "Default value" column, leave empty cells.

Click "Download"

We open the product card, check the presence of a barcode.

If in the future you plan to use this procedure for downloading data with the same settings for download options, we recommend that you save the current settings. To do this, click the "Save settings" button and clearly name the current setting, for example, "Load barcodes of goods from the Excel price list".

Next time you can load the saved settings by clicking on the "Restore settings" button and selecting the one you need from the list.

In this article, we will consider two very important topics with you, the first is, and the second is of course uploading data from 1s to excel. It should be noted that these topics are of interest to two main audiences of users, they are:

Users of the 1C Enterprise system
1C programmers

1C users usually want to order, for example data transfer from excel to 1s in accordance with their specific porting requirements, while 1C programmers are more likely to look for some clarification or code examples. Based on these considerations, we decided to divide the information given in this article into two parts for users and programmers of 1C.

1C loading from Excel and unloading from 1C to Excel through the eyes of an ordinary user 1C

Very often it turns out that an enterprise or some firm, for example, has documentation that is stored on a computer in Excel files. And often users have a question, but how upload or download data from Excel to 1C. Of course, one cannot do without knowledge of programming, so if you are a simple user, then you need to order 1c services from us, because absolutely any of our 1c programmers will always listen to you, select a specially designed solution just for you, and will definitely help you.

Why is there no universal processing for loading data from Excel to 1C?

Let's answer this question and look at its main points:

Each user has different Excel files, the data from which he wants to transfer to 1C, of course, these files have a different number of rows and columns, and also carry a different semantic load.
Each user works with a separate configuration, i.e. configurations can be different, what works in one configuration will not work in another configuration without upgrading.
Data from Excel must be transferred to different objects, and therefore different code is needed, because each user has his own requirements.

It is for these reasons that you cannot create universal processing for loading data from Excel to 1C.

How data is loaded and unloaded from Excel to 1C on the example of real development

Now let's take a look at the general how data is loaded and unloaded from Excel in 1C, and how will it all look in the finished solution?. An example of a real individual development, as well as the original Excel file are presented below.

In this processing, you first need to select the Excel file, then click on the "Run" button, after which the necessary data from Excel will be loaded into the tabular part. Now all that is left for the user to do is to click the "Transfer to book" button, after which the data will be transferred from Excel to 1C.

Uploading data from 1C to Excel happens about the same, only here the complexity of writing processing depends on what kind of data should be unloaded from the object. For example, we have the "Nomenclature" reference book and we want to upload the name of the item itself and the unit of measure of this item, although the item itself can have many more details, such as item type, item type, full name, etc.

I need processing for loading and/or unloading data from Excel, what should I do?

All you have to do is pick up the phone and call us. After that, our specialists will perform all the necessary actions so that you get exactly what you need. If you need to perform an urgent 1c update, then call us, we are happy to help each of our clients!

Loading and unloading data from Excel to 1C through the eyes of an ordinary 1C programmer

A little higher, we gave a lot of useful information for 1C users, but now it's time to talk about the technical aspects of writing code, as well as provide information for 1C programmers that will be very useful to them when writing code. Consider the main frequently used functions for working with Excel.

We will start with how to connect Excel to 1C, this is done through a COM object. In order for this code to work properly, Excel must be installed on the computer, otherwise nothing will work:

After we have connected Excel, we need to open the necessary document from which the transfer will be carried out:

Now we need to select a sheet of an excel book, in the figure above we see how a sheet with a certain number is selected. The sheet number is stored in the "Sheet number" variable:

We can find out the number of sheets in an excel workbook using the "Count" field:

To get the name of a sheet under a certain number, you need to write a similar line:

After we have finished all the steps to work with Excel itself, the process must be closed. Close it, otherwise it will hang:

An example of a function that loads individual cells from Excel to 1C

We have reviewed the basic procedures and functions that every 1C programmer encounters when writing processing for loading data from Excel to 1C. Now let's get acquainted in more detail with the function that reads individual cells of an excel file.

We think that there is no need to provide any additional explanations for this code, since all these functions were discussed in detail above.

An example of a function that loads all data from Excel into a 1C tabular part

We have considered a function that displays individual Excel cells. But what if we need to unload the entire excel sheet at once and bypass it sequentially? This is where the code below comes in handy:

An example of a function that uploads data from 1C to Excel

Above, we have analyzed a lot of interesting things, but we did not touch on such a topic as uploading data from 1C to Excel. In fact, the principle of work here is absolutely almost the same, except that we programmatically create a new Excel file, and programmatically write it down. Well, in order to write a value into a cell, as you probably already guessed, you need to put the expression Sheet.Cells (x, y) on the left side. An example of uploading data from 1C to Excel is presented below:

Related video:

Many who work in the 1C program are faced with the need to load data from an Excel spreadsheet into 1C. When the user may need such a download:
- generated report, saved in excel, processed. The processed data must be uploaded to 1C in the form of a document (movement, return to the supplier)
- there is a list of nomenclature that needs to be uploaded to the directory. The item list is in Excel
- any other data that needs to be loaded into the tabular part of the document, directory, information register
Below is the instruction for loading data from Excel into the tabular section of the document "Return of goods from the buyer".
Action 1. Preparation of the document into which the data will be loaded. You need to create an empty document "Return of goods from the buyer" and write it down.

Action 2. Open external processing "Loading data from a spreadsheet document".

Action 3. In processing, select the location where we will upload the data.

Action 4. Setting boot options. Do everything as in the picture to practice.

Action 5. Copying data from Excel to processing in the "Spreadsheet Document" tab (as in the figure). Two buttons are highlighted in red: "Control" filling (shows errors, for example, if the unit of measurement of the loaded item is incorrectly specified) and "Download" (performs the download).

The fewer parameters you specify for loading, the less chance of errors during loading, but the more data will have to be corrected manually in the loaded document.

Actually, by myself processing data loading from Excel to 1C