uptostart.ru News. Games. Instructions. Internet. Office.
Quite often, when communicating with potential clients, I hear that they have enough table functionality from the office suite, and they do not see the point in 1C programs. This is because some still associate this solution only with accounting programs, which is not true.
Excel is a universal and mass product, you can't argue with that. Almost everyone can use this program, unlike the developments of "1C", which are aimed at narrow specialists. Using "green" tables, you can arbitrarily implement the necessary functionality that will be convenient: here is the financial model of the organization with all mutual settlements, management accounting, even regulated, a large selection of data visualization, the ability to expand the program's capabilities yourself using various add-ons - everything is in your hands all from scratch...
It’s good if you are just starting your business, gradually taking into account the necessary data in the table on relationships with customers, suppliers, regulatory authorities, building your arbitrary structure, but what to do next when the database becomes voluminous, and the staff will be in the tens? All the same to consider according to their own parameters? I am in favor of not confusing convenience with habit, which often interferes with the systematization of a business, and, consequently, its optimization.
For clarity, let's imagine what needs may arise in the automation of accounting, for example, in trade:
1. Analysis of sales within a certain period.
The head of the sales department can analyze sales data. After all, it is important for him to understand whether the plan is being implemented, how effective his strategies are, in order to make timely adjustments in case of inefficiency.
2. Warehouse accounting.
Do I need to explain that it is also important for sales to understand the availability of goods in stock, which goods can be reserved and which are out of stock, and what needs to be ordered? I think everything is clear here.
3. Maintaining a database of counterparties.
Even if the owner sells only what you can buy here and now, which is typical for the B2C sector, then he has slightly different relations with the supplier base - contracts, primary ... Imagine how convenient it is when everything related to the supplier is automatically immediately displayed in a single database? The manager responsible for the warehouse immediately sees the availability of goods in the warehouse, another employee responsible for the documents monitors information on their availability, whether all obligations under contractual relations are being fulfilled, who owes whom and how much. And in case of discrepancies, you can check the data for a certain period, generating a result in less than a minute.
4. Profit of the organization.
It will not be difficult to generate a profit report, since all data on expenses and incomes are already present in the database. Provided that the information is entered in a timely manner, at least by the required date. Here the fundamental factor is the personal responsibility of the user.
If you do not have many clients and suppliers, Excel will really suit you, since there is plenty of time to design your own schemes in the table and fill them in, with the subsequent distribution of documents into various folders on your computer: contracts, suppliers, clients, clients in the neighborhood , clients in development, clients to be deleted - the endless and exciting process of creating folders within a folder. And if there is a lot of information, is it convenient to keep a record of your database in this case? Of course, there are always exceptions, sometimes there are organizations with large turnovers, whose financial data is maintained only in Excel. How much time does it take them to exchange and combine data between departments? Answer: a lot.
I have no idea how to quickly find the right one in a large number of Excel documents organized by an arbitrary system. For example, when submitting documents for tax requirements. How will an accountant submit reports to regulatory authorities when the information is all scattered? Accounting, of course, can be outsourced and some business owners, unfortunately, may not care what processes a given specialist may have, as long as he balances. But even if so, how then is the process of document exchange organized? How quickly will an employee on the side understand the impromptu information structure?
I can’t say the same about 1C, because all the information there is structured and interconnected:
- A single database for both remote and full-time employees, in which you can work with all standardized and arbitrary documents;
Reporting to the regulatory authorities is based on the data that is already reflected in the program, there is no need to open many files, reduce several values into one table to get the final results.
In defense of Excel
Probably, it seemed to you that I am against building data in "green" tables. Not at all. In fact, I myself often do some business in Excel: if I need arbitrary calculations, if I need to segment data by clients, conducting analytics according to those parameters that are not in 1C - yes, this happens - I go to " excel. Simply put, in tables I do calculations, non-standard analysis, but then I systematize everything later anyway in 1C.
And then, to view the uploaded files from 1C, you still need Excel, since the data in 1C is stored in spreadsheets. Do you think it's a vicious circle? I will put it differently: "1C" and "Excel" go side by side like two partners, but each of them has its own purpose, they complement each other well, but do not interchange.
If you need to systematize your affairs, taking into account the legislative system of our state, taking into account the specifics and direction of business, especially when it comes to large amounts of information, then you need 1C. If you need arbitrary calculations, building a strategy from scratch, visualization of non-standard analytics data, then Excel is at your service. But it is much more convenient to work with these solutions simultaneously.
What is the result - "1C" or "Excel"?
At the very start, if the question is precisely in finance for automation, although the basic 1C programs do not cost that much money, I would use Excel. A huge plus is that the data from the "green" tables can be loaded into 1C without loss. But as you scale up, I would advise you to pay attention to business process automation programs. There are many of them, and it may not necessarily be "1C" ...
The transition to 1C can be made when you understand that it is necessary to optimize your resources, including automating issues related to relationships with customers and suppliers, with supervisory authorities. In general, to improve your workflows in order to increase business efficiency, when information processing will take a lot of time, and Excel files will no longer cope with the volume of data entered.
However, not every "1C" can suit you, you need to take into account various factors: the specifics and scope of business, scale, the need for daily tasks, the routine that needs to be minimized. Everything is individual. The truth, as I said, is somewhere between "1C" and "Excel" - it is in addition to each other.
On this, perhaps, everything. But if you have any questions, please contact us and we will try to help you. Successful business processes, colleagues!
- When performing the exchange of information between counterparties (price lists, reconciliation acts, etc.);
- To facilitate the work of operators in cases where the main accounting is kept in 1C, and some of it is performed in Excel tables;
- During the initial filling of the database.
To perform this operation, you can use both standard functionality available with a subscription to information technology support (ITS), and self-written processing implemented through various connection options. In our article, we will try to analyze all possible cases as fully as possible and answer most of the existing questions regarding loading data from Excel to 1C.
Universal mechanism
On the ITS disks, as well as on the 1C portal, by going to the menu "Technological support" -> "Universal reports and processing", in the folder "Loading data from a spreadsheet document" there is a corresponding processing.
When it is launched, a form opens (Fig. 1):
As you can see from the form, it allows you to transfer information to the following configuration objects:
- Directory;
- The tabular part of the document or reference book;
- Register of information.
Depending on the position of the switch, the object selection field changes.
The processing tabular form is filled in when you open the uploaded file.
Type processing supports uploads from:
- Developed by specialists of the company 1C, files of the formatmxl;
- Lisztxls saved in the formatExcel 97-2003;
- text filetxt;
- tablesdbf.
Excel files of large volume can take a long time to load, so if it is assumed that data loading can take place in several stages or you know for sure that you will have to open an existing file several times, it is better to save information from it in mxl to save nerves and time format. You can do this directly from the processing form.
It is important to understand that if line groupings, subheadings, and notes exist 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 spreadsheets contain a header with all sorts of details and data (name of the printed form, details of the counterparty, date and number of the incoming document, column names, and so on), in order to exclude their processing by the program on the form, in the "First line of the spreadsheet document" attribute, you must specify the first line with the transferred information
After selecting the metadata object into which the information will be written, the tabular section of the "Settings" tab will be automatically filled with the names of attributes, descriptions of their types, and other important information. Consideration of the columns of the tabular part of the "Settings" tab should be devoted to a separate section.
Settings columns
Flag - by checking or unchecking the box in the line, it is determined whether the corresponding attribute will be filled in.
Representation of the attribute - a synonym (name) of the metadata attribute is written here, as it is specified in the configurator.
Search field - if you check this box, the processing will search for elements by the corresponding attribute and, if successful, change the existing data, additional function this field - protection against the appearance of duplicates.
Description of types - displays the data type that has one or another metadata attribute.
Download mode - offers three options to choose from (Fig. 3):
Fig.3
- Search - the corresponding element will be searched, in case of its absence, a new one can be created;
- Set - a certain value is set in a subordinate way;
- Calculate – in this case, the result of the calculation of the expression specified in the column "Relationship condition/Expression for value" will be set in the field of the element being created.
In the latter case, activating the Expression field will open the form (Fig.4).
Fig.4
Column number - a field used to indicate from which column of the Excel spreadsheet you need to take data for filling.
Default value - quite often a situation arises when the uploaded 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 for filling.
Link condition / Expression for value - we have already partially touched on this field when we considered calculated fields, in addition, you can specify the condition in accordance with which the source data will be synchronized.
Here, in principle, is all the information that is available on the "Settings" tab.
In order not to kill a lot of time each time for loading, prescribing correspondences and expressions, the developers have provided the ability to save the settings option to a file with the mxlz extension.
You can check the correctness of the transferred data by clicking on the "Fill control" button (Fig. 1). After that, you can start the download process. You will be notified separately about the successful completion of the procedure or non-standard situations.
To upload data from Excel to information bases"Management and trade" there is another mechanism. It is less universal than the above method, however, it 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:
- A date selection field that will indicate for what time this price is relevant;
- The field for selecting the counterparty who sent his price list;
- A button that allows you to select the type of prices to be set;
- A tabular section that can be filled with uploaded data.
This form can be seen in Fig.6
Fig.6
The legend at the top of the form explains how to use the first tab of the form.
After selecting a counterparty (depending on whether it acts as a supplier, commission agent or seller), additional columns for each price type 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 that, we, using the clipboard, will be able to transfer information from one table to another. When the columns we need (“Article”, “Name”, “Price”) are filled in, we press 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 there are none, will offer options to resolve the error. Appearance tabular part can be controlled with a switch. In addition, the user can independently compare the elements of the uploaded file with those 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:
- A corresponding element will be created in the "Supplier nomenclature" directory (if it was not there);
- It will be matched with an element of the reference book "Nomenclature";
- The document "Setting item prices" will be created and posted, indicating: supplier, price type and data fixing date.
Processing "Loading goods from external files" 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 Excel connections to 1C:
- Through Microsoft ADODB - a fairly fast method, which, as a rule, is applicable to both file and client-server database storage options;
- Across Microsoft use Office - a method that sometimes fails when working with SQL databases, as a rule, it works somewhat slower than the first method, and Office is also required;
- Through Libre Office - unlike the previous method, it is free, in addition to xls and xlsx formats, it also supports its own tables, but requires an installed LibreOffice package and some preparation of the uploaded file (the first row of the table should 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 serves to program 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.
There are three options for Excel files:
Connection string options:
- Provider - the driver used is determined 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 - from the second), whether the file is opened read-only (ReadOnly) and some other additional options.
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) start fetching information from the downloaded file.
IN this case the “Sheet” parameter determines which sheet from the excel workbook we have to work with.
The set of records stored on a sheet can be read using the Recordset object. In this case, the first record of the sheet can be obtained by the BOF (beginning of the 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 the database drivers, Excel must be installed on the computer where the connection is made. Only in this case we can initialize the application for reading data from the table (Figure 16).
This COM object has several child parameters, but the main one for us, under the current conditions of the task, is the WorkBooks parameter (Fig. 17).
After initializing the book, it is necessary to determine the sheet from which the data will be read (Fig. 18).
After that, you can iterate inside the cycles of 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 in use by another application. It's fine if the taskbar shows that Excel is running on the computer, but if you or another user opened it from external processing, visually this can only be determined through the "Task Manager", so do not forget to close the connection before the end of 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. twenty.
The connection and the procedure for completing work with data are best organized inside the Attempt-Exception-End Attempt construct, causing an error description in an exceptional situation. Although this sometimes slows down the work, it greatly simplifies the determination of the cause of the error and, ultimately, ways to eliminate it.
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 examine both of these methods in more detail.
1. Work through COM-object.
This method requires MS Excel to be installed, 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.
An example (at the end of the article can be viewed in text form):
It should be noted that all objects, methods and properties provided by the "Excel.Application" COM object 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 an easier solution, we save the template as a binary layout.
2. Work through Spreadsheet 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 opening, all sheets are loaded into one spreadsheet document. Starting from version 8.3.10, when loading, different sheets are loaded as different areas.
The recording example is very simple and does not require much attention:
However, here we have a problem. When recording from 1C in an Excel workbook, the display of sheet names is disabled by default.
This problem can be solved in 2 ways, 1 - enable the display of sheets in the book itself in the settings (not all users will agree to do this), 2 - do it 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 a simple task, since the read method is available only on the server or thick client. To do this, we need to transfer the MS Excel file to the server.
&AtClient
CreateDocument() Procedure
An exception
End of Attempt;
Book = Excel.WorkBooks.Add(); //Create a new MS Excel workbook
Sheet = Book.WorkSheets.Add(); //Add a sheet
Sheet.Name = "Example from 1C"; //Set the sheet name
Sheet.Cells (1,1).Value = "(!LANG: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
GetLayoutServer() function
Return FormAttributeToValue("Object").GetLayout("Upload Template"); //This is how we get the external processing layout;
EndFunctions
&AtClient
Procedure Work()
Excel = New COMObject("Excel.Application");
An exception
Show("Unsuccessful attempt to connect Excel component. Excel may not be installed on this computer!");
End of Attempt;
Layout = GetLayoutServer();
Layout.Write(TemporaryFileName);
Do you have a question, do you need the help of a consultant?
Book = Excel.WorkBooks.Open(TempFileName);
SheetTemplate = Book.WorkSheets(1);
SheetTemplate.Cells (6,1).Value = "(!LANG:Date:";!}
SheetTemplate.Cells(6,2).Value = CurrentDate();
SheetTemplate.NumberFormat = "dd/mm/yy;@"; // Let's give the date format, this format is obtained by recording a macro in MS Excel
SheetTemplate.Columns("B:B").EntireColumn.AutoFit; // Stretch the column to fit the date exactly
Book.SaveAs(FileName);
Book.Close();
EndProcedure
&AtClient
EndProcedure
&AtClient
Procedure WriteSpreadsheetDocument()
SpreadsheetDocument = New SpreadsheetDocument();
SpreadsheetDocument.Area("R1C1").Text = "Example of an entry in MS Excel from 1C";
SpreadDocument.Write("C:\1\Test2.xls",SpreadDocumentFileType.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=PutToTempStorage(BinaryData,ThisForm.UniqueIdentifier) ;
SpreadsheetDocument = DownloadOnServer(Address);
SpreadsheetDocument.Show();
EndProcedure
&On server
Function LoadOnServer(Address)
TempFileName = GetTemporaryFileName("xlsx");
FileData = GetFromTempStorage(Address);
FileData.Write(TemporaryFileName);
SpreadsheetDocument = New SpreadsheetDocument();
Return SpreadsheetDocument;
Introduction.
Many people know that Excel is much older than 1C. In my opinion, this is a very successful product, and I have not heard any complaints about it. Excel is simple and versatile. It is able to perform not only simple arithmetic operations, but also complex calculations, graphing, etc. I know examples when organizations, before switching to 1C, kept part of the accounting in Excel. Many and now in parallel use two programs. In this article, we will consider ways to exchange data between 1C and Excel.
Tables 1C and Excel.
For convenience, many users save 1C tables (printable forms) in Excel format (* .xls). After that, various groupings, sortings, calculations, etc. are made in the resulting file. This is due to the fact that in 1C tables there is no such huge functionality as in Excel tables. But in version 1C 8.0 there are innovations that make working with tables more comfortable.
The 1C website (http://www.1c.ru/) has a useful program that allows you to open 1C tables in Excel and save an Excel sheet as a 1C table. This is useful if the 1C table was not saved in Excel format, and 1C is not installed on the computer where you need to open this table. Yes, and constantly remember that you need to save the 1C table in Excel format, there will be no need.
Full information about the program can be found here.
You can download the program here (zip archive 682 739 bytes).
Comment: In Excel, the decimal separator is ",". Therefore, before saving the 1C table in Excel format, replace another separator in it (for example, ".") with ",". Otherwise, Excel will not be able to perform calculations with these numbers, or they will not be displayed as numbers at all. For example, in table 1C "15.2" will be displayed in Excel as "Feb 15".
Getting data from Excel.
Access from 1C to Excel is made through OLE. For example, code
Attempt
An exception
Notify(Error Description() + );
Return;
End of Attempt;
will allow us to access through the "Excel" variable to running application Excel. And then you can already access the book (file), sheet and cell with data. Below are code examples.
Opening a book (file):
Book = Excel. workbooks. Open(PathToFile);
PathToFile is the full path to the Excel workbook file.
Selecting a book sheet to work with:
or
Sheet = Book. WorkSheets(SheetName);
SheetNumber is the number of the sheet in the book, SheetName is the name of the sheet in the book.
Getting the value of a sheet cell:
Value = Sheet. Cells (LineNumber , ColumnNumber ). value;
Important: do not forget to add Excel code to the field for performing the necessary actions. Quit(); , otherwise the running process will remain incomplete and will take up the memory and processor of the computer.
Data output to Excel.
To output (upload) data to Excel, you must either open an existing workbook or create a new one and select a worksheet for data output. Opening an existing book is described above, but to create a new book, you need to use the following code:
Attempt
Excel = CreateObject("Excel.Application" );
An exception
Notify(Error Description() + "Excel is not installed on this computer!");
Return;
End of Attempt;
Book = Excel. workbooks. add();
Since when creating a workbook in Excel, sheets are automatically created (Tools->Options->General->Sheets in a new workbook), you only need to select the sheet to work with:
Sheet = Book. WorkSheets(SheetNumber);
or add a new sheet to the book, if necessary:
Sheet = Book. Sheets. add();
The next step is to set the value of the cell:
Sheet. Cells (LineNumber , ColumnNumber ). value = value ;
RowNumber, ColumnNumber - row number and column number at the intersection of which the cell is located.
And at the end, you need to record the created book:
Attempt
Book . SaveAs(PathToFile);
An exception
Notify(Error Description()+
"File not saved!");
Return;
End of Attempt;
PathToFile - the full path to the Excel workbook file (including the name).
Important: remember that filenames must not contain \ / : * ? " >< |.
Commonly used methods for reading/setting values in Excel.
| Excel = CreateObject("Excel.Application" ); | Gaining access to the Excel application. |
| Excel. Visible = Visibility ; | 0 - Excel is not visible, 1 - visible. |
| Book = Excel. workbooks. add(); | Creating a new workbook (file) Excel. |
| Book . SaveAs(Filename); | Saving an Excel workbook. |
| Sheet = Book. Worksheets. add(); | Adding a new sheet to the book. |
| Book = Excel. workbooks. Open(Filename); | Opening an existing workbook (file) Excel. |
| Sheet = Book. WorkSheets(SheetNumber); | Setting a sheet as a working sheet with numberSheetNumber. |
| Sheet. Name = SheetName ; | Naming a worksheetSheetName |
| Sheet. PageSetup . Zoom = Scale ; | Specifies the Scale page option (from 10 to 400). |
| Sheet. PageSetup . Orientation = Orientation ; | Orientation: 1 - portrait, 2 - landscape. |
| Sheet. PageSetup . LeftMargin = Excel . CentimetersToPoints ( Centimeters ); | Sets the left border (in centimeters). |
| Sheet. PageSetup . TopMargin = Excel . CentimetersToPoints ( Centimeters ); | Specifies the upper limit (in centimeters). |
| Sheet. PageSetup . RightMargin = Excel . CentimetersToPoints ( Centimeters ); | Specifies the right border (in centimeters). |
| Sheet. PageSetup . BottomMargin = Excel . CentimetersToPoints ( Centimeters ); | Sets the lower limit (in centimeters). |
| Sheet. Columns (ColumnNumber) . ColumnWidth = Width ; | Set column width. |
| Sheet. Cells (LineNumber , ColumnNumber ) . value = value ; | Entering data into a cell. |
| Sheet. Cells (LineNumber , ColumnNumber ) . font. Name = FontName ; | Setting the font in a cell. |
| Sheet. Cells (LineNumber , ColumnNumber ) . font. Size = FontSize ; | Set the font size in a cell. |
| Sheet. Cells (LineNumber , ColumnNumber ) . font. Bold = Bold ; | 1 - bold font, 0 - normal. |
| Sheet. Cells (LineNumber , ColumnNumber ) . font. Italic = Italic ; | 1 - oblique font, 0 - normal. |
| Sheet. Cells (LineNumber , ColumnNumber ) . font. Underline = Underlined ; | 2 - underlined, 1 - not. |
| Sheet. Cells (LineNumber , ColumnNumber ) . NumberFormat = Format ; | Set the cell data format. |
| Sheet. Cells (LineNumber , ColumnNumber ) . Borders. Linestyle = LineType ; | Set cell borders. 1 - thin solid. |
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, WayToReadSpreadDocumentValues.Value);
Here The path to the file- Path to the downloaded Excel file. WayToReadValuesSpreadDocument.Value defines 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 way to programmatically work with Excel files. 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 through OLE:
| Action | The 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 the 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");
Command// Assign command text to create a table
Command . commandtext= "CREATE TABLE [Sheet1] (Column1 char(255), Column2 date, Column3 int, Column4 float)";
Command . execute(); // Command execution// Assigning command text to add a table row
Command . 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 data 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.
