• Accounting info. Accounting info 1s 8.3 spreadsheet document on the form

    How to upload a spreadsheet document into the 1C 8.3 Accounting program?

    In 1C 8.3 it is possible to bulk download a list of items from a spreadsheet document, for example from an Excel file.

    For loading, we use external processing LoadDataFromTabularDocument.epf for managed forms (8.2 and 8.3). It is universal and should be suitable for any configuration written for a managed application.

    To start external processing, you need to go to the “File” menu, then “Open” and select this processing from the directory in which it was saved:

    Processing Loading data from a spreadsheet document 1C 8.3 (managed forms)

    After the processing has opened in 1C, you can start working with it. First of all, we need to decide where and what we will download:

    Let's give an example in the reference book “Nomenclature“. We create a file with data in xls format.

    Processing can also load files in the following format:

    • any other format from which you can copy data

    This is what the Excel file with the source data looks like, which we will upload to the “Nomenclature” reference book:

    We did not write down all the fields in the directory, since this will be enough to understand the principle of loading. In addition, this data is enough to start working with these positions.

    These positions are not currently in the 1C information database, and we will now upload them there.

    Click on the “Open” icon (indicated in the figure as “Select source”) and select the file with the data. You can use simple copying of information. The data will be loaded into processing automatically. Now let's check if we filled out our data correctly. To do this, click the “Filling Control” button.

    As you can see, we have some errors! We will eliminate it. Go to the “Settings” tab:

    Before correcting errors, let's pay attention to one important detail. The program initially does not know in which field to look for a directory element in case it is already there. Therefore, she needs to indicate it. For example, we search by code, since it is usually unique. In the “Search field” column in the “Code” line, put a checkmark. Now, if a record with such a code is found, it will be replaced, otherwise it will be created.

    Important! If you do not specify a search field, duplicate items may appear. Especially after the second download of a similar file!

    Now let’s see why “Unity” is swearing on the field. The fact is that units of measurement in 1C 8.3 are stored in a separate directory and processing by default looks for this unit by name. But in fact, the word unit is written in the “Full name” field.

    Unfortunately, processing can only search by “Name” and “Code” (for the programmer the possibilities are wider). If you pay attention to the pictures above, you will see that there is a code in the “Unit” column. And in processing you need to indicate that the search should be carried out by code. Double-click on the “Communication Condition” column opposite “Units” and change it to “Code”.

    Now let’s see what they tell us about “Service” in the list of errors. One more important note. The columns in the file must be located strictly in the same order as the lines of the directory fields. And for us, “Type of item” is located at the very bottom, and in the file after the “Comment” column.

    In order to move the “Item type” line up, there are blue arrows at the top of the form. Using the “Up” arrow, raise the desired line and place it under “Comment”.

    Click “Load data” and this time everything goes without errors:

    Based on materials from: programmist1s.ru

    In 1C 8.3 it is possible to bulk load a list from a spreadsheet document, for example, from an Excel file.

    We use external processing for loading Loading Data From TabularDocument.epf for managed forms (8.2 and 8.3), which can be downloaded . It is universal and should be suitable for any configuration written for a managed application.

    • If you are a programmer and want to develop “your own” download from Excel, detailed instructions for;
    • An example of loading a price list in 1C Trade Management can be found at.

    To start external processing, you need to go to the “File” menu, then “Open” and select this processing from the directory in which it was saved:

    Processing Loading data from a spreadsheet document 1C 8.3 (managed forms)

    After processing has opened in 1C, you can start working with it. First of all, we need to decide where and what we will download:

    I want to give an example in the directory ““. I created a data file in xls format.

    Processing can also load files in the following format:

    • any other format from which you can copy data.

    This is what the Excel file with the source data looks like, which we will upload to the “Nomenclature” reference book:

    Get 267 video lessons on 1C for free:

    I did not write down all the fields in the directory, since this is enough to understand the principle of loading. In addition, this data is enough to start working with these positions.

    These positions are not yet in the 1C information database, and we will now upload them there.

    Click on the “Open” icon (indicated in the figure as “Select source”) and select the file with the data. You can use simple copying of information. My example file can be downloaded. The data will be loaded into processing automatically. Now let's check if we filled out our data correctly. To do this, click the “Filling Control” button.

    As you can see, we have some errors! We will eliminate it. Go to the “Settings” tab:

    Before correcting errors, I want to draw your attention to one important detail. The program initially does not know in which field to look for a directory element in case it is already there. So she needs to point it out. I prefer to search by code since it is usually unique. In the “Search field” column in the “Code” line, put a checkmark. Now, if a record with such a code is found, it will be replaced, otherwise it will be created.

    Important! If you do not specify a search field, duplicate items may appear. Especially after the second download of a similar file!

    Now let’s see why “Unity” is swearing on the field. The fact is that units of measurement in 1C 8.3 are stored in a separate directory, and processing by default looks for this unit by name. But in fact, the word unit is written in the “Full name” field.

    Unfortunately, processing can only search by “Name” and “Code” (for the programmer the possibilities are wider). If you pay attention to the pictures above, you will see that I have a code in the “Unit” column. And in processing you need to indicate that the search should be carried out by code. Double-click on the “Communication Condition” column opposite “Units” and change it to “Code”.

    Now let’s see what they tell us about “Service” in the list of errors. One more important note. The columns in the file must be located strictly in the same order as the lines of the directory fields. And for us “” is located at the very bottom, and in the file after the “Comment” column.

    In order to move the “Item type” line up, there are blue arrows at the top of the form. Using the “Up” arrow, raise the desired line and place it under “Comment”.

    Click “Load data”, and this time everything goes without errors:

    Video tutorial on loading products into 1C from Excel files:

    Typically, a spreadsheet document is formed on the basis of a layout as from building blocks. A layout is a template for creating a printing form. The layout is built in the configurator in a special table editor. The developer can create areas, change the height of rows and width of columns, set the formatting of cells and the entire spreadsheet document, and include pictures, charts and pivot tables in the layout.

    Layout areas are designed for single or multiple output.

    Some cells contain only text that should be displayed. Other cells contain parameter names (in angle brackets). The values ​​of these parameters must be specified in the program module. Unlike version 7.7, you cannot enter an expression into a cell. In version 8.0, all program code is concentrated in program modules and when sections are displayed, there is no connection with the current context of the program module. The assignment of parameters for a spreadsheet document must be done explicitly from the program module through the "Parameters" collection of the "TabularDocument" object.

    An area can consist of several rows or columns in a row, or it can be a rectangular area of ​​a table. An area can be accessed by name or coordinates. You can also refer to the intersection of areas using a vertical bar (as in version 7.7).

    General scheme
    1. Creating a layout in a table editor (defining areas, parameter names, formatting).
    2. Creating a new spreadsheet document (this item is not required if the report is output to the TabularDocumentField control element).
    3. Receiving the layout into a variable (GetLayout method).
    4. Getting layout areas (GetArea method).
    5. Filling in the area parameters (Parameters property).
    6. Outputting an area into a spreadsheet document (Output and Attach methods).
    7. Setting the display properties of a spreadsheet document (grid, headers, view only, etc.).
    8. Display a spreadsheet document on the screen (or maybe send it to a printer or save it).

    Example of a spreadsheet document output

    Below is an example of generating a printed form of an invoice from a document module:

    Procedure Print(TabDoc) Export

    //creating a new spreadsheet document
    TabDoc = New TabularDocument;

    //getting a layout for printing an invoice
    Layout = Documents.Invoice.GetLayout("Main");

    //get the "Header" area as a new spreadsheet document (!)
    Area = Layout.GetArea("Header");

    //specify the area parameters
    Area.Parameters.DocumentNumber = Number;
    Region.Parameters.From = Date;
    Area.Parameters.To = Account;

    //display the filled “Header” area in a spreadsheet document
    TabDoc.Output(Area);

    // display the "Header" area in a spreadsheet document
    TabDoc.Output(Layout.GetArea("Header"));

    //getting the area "String"
    //note that this can be done before looping over the rows
    Area = Layout.GetArea("String");

    //output document lines to printed form
    For Each Composition Of The Composition Cycle
    //filling the area parameters from the table section row
    Area.Parameters.Fill(CompositionStr);

    //output the generated area into a spreadsheet document
    TabDoc.Output(Area);

    EndCycle;

    //output the "Basement" area
    Area = Layout.GetArea("Basement");
    Area.Parameters.TotalQuantity = Composition.Total("Quantity");
    Area.Parameters.TotalSum = Composition.Total("Sum");
    TabDoc.Output(Area);

    //set the parameters for displaying the spreadsheet document
    TabDoc.ViewOnly = True;
    TabDoc.DisplayHeaders = True;
    TabDoc.DisplayGrid = False;

    //show the spreadsheet document on the screen
    TabDoc.Show();

    EndProcedure

    Basic methods

    The main methods for generating the final tabular document are as follows:
    Withdraw

    Prints out the next fragment (tabular document), adding it from the bottom, starting from the first column.
    Join
    Attaches the next fragment (spreadsheet document) to the printed form on the right, starting from the line from which the previous fragment was output using the Output method
    Region
    Allows you to get an area as an object of the "SpreadsheetDocumentCellsArea" type. Through this object you can set the formatting of the area: set the font size and color, background color and cell pattern.
    GetArea
    Allows you to get an area as an object of the "TabularDocument" type. This object can then be passed to the Output and Attach methods. The values ​​of the area parameters are also specified through this object.

    Situations arise when there is data, for example, in an Excel file, and it needs to be entered into the program. Manual entry can require an enormous amount of time, and there is a high probability of making mistakes.

    In the event that you do not have the opportunity to attract a specialist to solve this problem, use standard processing on managed forms from 1C. You can download it either on the ITS disk or from the link Loading Data From TabularDocument.epf .

    First of all, you need to open this processing in 1C:Enterprise mode. In the “File” menu, click on “Open” and in the window that appears, select processing where you saved it. You can also use the hotkey combination “Ctrl+O”.

    If the processing does not open, most likely your account does not have the “Open external reports and processing” right.

    After successfully opening this processing, the first thing you need to do is indicate where the data will be loaded. In the example considered, the data will be loaded into . Simply put, we will upload a price list in Excel format into 1C 8.3. The instructions will be very similar for loading invoices.

    Let's create a new document “Setting item prices”, or you can use an existing one. In it we will indicate only two fields “Date” and “Price type”. In our case, the “Wholesale price” type will be used. There is no need to fill out the table part. It will be populated using data loading processing.

    In the processing form, select the appropriate switch to load into the tabular section. The previously created document “Setting item prices” is inserted into the link field. In the “Tabular section” field – “Products”.

    Data can be loaded not only into the tabular parts of documents, directories, etc., but also into the directories and information registers themselves.

    After choosing a place to load the data, the processing itself generated the necessary column headings in the spreadsheet document, which is located below. The data loaded into the program can be specified manually, or by copying it from an external source to this tabular section.

    In this example, we will look at the most convenient way to download - from an Excel file. Click on the open button and select the data file. The following formats are supported for opening: *.mxl", *.xls, *.txt, *.dbf. All data from the file will be included in the processing spreadsheet document.

    Checking and monitoring filling

    In one wonderful saying, “Measure twice, cut once,” therefore, before loading data into 1C from a spreadsheet document, they must be checked. To do this, click on the “Filling Control” button.

    If any errors are detected, a message will appear indicating the number of such lines. In those cells where a discrepancy was found, a note with the reason will be indicated.

    In this case, the nomenclature item “Beam 40*40*300” was not found in the program. Before loading data, you need to create it or pre-load the product in 1s 8.3 from a similar file with goods.

    Download settings

    Let's take a closer look at the “Settings” tab. This tab contains the tabular part of the settings for the uploaded data. Each row of this table contains the setting of the corresponding attribute. The following options are available for configuration:

    • "Tag". A set flag means that this attribute must be overloaded.
    • "Presentation of props."
    • "Description of types." It can take one or several value types (composite data type).
    • “Download mode” comes in three varieties:
      • “Search” - the value is entered only if found.
      • “Calculate”—the value is calculated based on the specified expression.
      • “Set”—the value is specified unambiguously.
    • "Default value." If no data is found to substitute into the attribute, the value from this setting is set.
    • “Link Condition / Value Expression” sets the field that will be searched. For directories, searching by code is a higher priority, since it is often unique. If the code is unknown, you can leave the default setting and search by name. In this case, there should be no duplicate lines in the directory where the details will be searched.

    After you have successfully configured and checked and controlled the filling, you can download the data. Click on the “Download data” hyperlink. Messages will display information about this operation. In our case, everything went well.

    Let's go to the document where we loaded the data. As you can see, all values ​​were filled in correctly.

    For another instruction on loading into 1C from Excel using the Nomenclature example, see the video:

    Read more: