The 1C:Enterprise 8 spreadsheet document is used not only for printing documents and reports. It has advanced features that make it an interactive user experience. These options include:
- transcripts,
- groups,
- notes.
It is worth noting that the interactive capabilities of a spreadsheet document can also include pivot tables and controls located in cells, but now we will not dwell on them, but will take a closer look at the above mechanisms.
Transcripts
The 1C:Enterprise 8 system supports a decoding mechanism (drill-down, drill-through), when the user clicks on a line or cell of the report and receives a more detailed report, unless, of course, this is provided by the programmer.
Transcripts are divided into standard and non-standard. Standard transcripts are processed automatically by the system and do not require additional effort from the programmer, for example, a document form will be opened for documents, and an element form will be opened for directory items. If the directory is edited in a list, the cursor will be placed on the current element in the list form. The Decryption Processing event is intended to perform non-standard decryption.
The Transcript Processing event can only be processed if a spreadsheet document is placed on a form as a control rather than opened in a separate window, because this event only exists for the SpreadsheetDocumentField control. Below are the "Events" property category of the "TabularDocumentField" control element, where the decryption handler procedure is assigned.
The “DecryptString” procedure itself may look like this (as an example):
Procedure DecryptString(Element, Decryption, StandardProcessing)
If TypeValue(Decryption) = Type("DocumentLink.Sales Agreement") Then
StandardProcessing = False;
Report = Reports.ContractAnalysis.Create();
Report.Contract = Explanation; //fill in the report details
Report.Generate(FormElements.TabularDocumentField1);
endIf;
End of Procedure
Note 1. In the application module (analogous to the global module in version 7.7) there is no longer the TableCellProcessing event. All processing of transcripts must be done in the form module where the "TabularDocumentField" control element is located.
Note 2. The Transcript Processing event occurs when you click on a cell or picture that contains a transcript. Do not confuse it with the "Select" event that occurs for all cells and pictures when you double-click or press the Enter key, and the "Select" event occurs first and then the "Decryption Processing" event.
Groups
In 1C:Enterprise 8 it became possible to group data in a report. Groupings can be horizontal or vertical, then special markers appear on the left and top, allowing the user to expand and collapse groups.
The easiest way to add groupings to a report is to entrust this task to the system, then minimal effort will be required from the programmer. You just need to apply the StartAutoGroupingRows/StartAutoGroupingColumns and EndAutoGroupingRows/EndAutoGroupingColumns methods, which enable the mode of automatic grouping of rows or columns, respectively. In this mode, when calling the Output (for rows) and Attach (for columns) methods, additional parameters for grouping are specified:
Output(<Таблица>, <Уровень>, <Имя группы>, <Открыта>)
Join(<Таблица>, <Уровень>, <Имя группы>, <Открыта>)
When using groupings, it is very useful to set the cell's AutoIndent property to some non-zero value, such as 5. The system will then automatically add the specified number of spaces to the left of the cell's text, based on the current grouping level. As a result, the report will acquire a convenient appearance for displaying the data hierarchy.
The following example displays a list of products with groupings:
TabDoc = New TabularDocument;
Area = Layout.GetArea("String");
Selection = Directories.Nomenclature.SelectHierarchically();
TabDoc.StartAutoGroupingRows();
While Select.Next() Loop
Area.Parameters.Nomenclature = Selection.Link;
TabDoc.Output(Area, Selection.Link.Level(), Selection.Name, True);
EndCycle;
TabDoc.FinishAutoGroupingRows();
TabDoc.Show();
Notes
Spreadsheet document cells can have notes, in which case a small red triangle appears in the upper right corner of the cell. When you hover your mouse over a cell, the comment appears in a pop-up window.
Software installation of notes is implemented as follows:
TabDoc = FormElements.TabularDocumentField1;
TabDoc.Clear();
Layout = Directories.Nomenclature.GetLayout("Catalogue");
Selection = Directories.Nomenclature.Select();
While Select.Next() Loop
LayoutCellsArea = Layout.Area("CellName");
LayoutCellsArea.Note.Text = Selection.Link.Comment;
TabDocArea = Layout.GetArea("String");
AreaTabDoc.Parameters.Nomenclature = Selection.Link;
TabDoc.Output(AreaTabDoc);
EndCycle;
Note that the Note property of the LayoutCellArea object is a Drawing object, not a simple string. Through this object you can edit the appearance of the note, the font and color of the text, background, lines, etc.
Summary
- A spreadsheet document not only serves for printing documents and reports, but also has powerful interactive capabilities. These features primarily include transcripts, groupings, and notes (covered in this section), as well as pivot tables and controls (covered in other sections).
- Non-standard transcripts, i.e. processed by the programmer, can be implemented if only the spreadsheet document is displayed in screen form (the "TabularDocumentField" control element).
- The easiest way to use groupings is to turn on the row auto-grouping mode, and when displaying each area in a spreadsheet document, indicate the level to which it belongs. It is also recommended to set the "Auto-Indent" property for the required cells in the layout.
- Notes "pop up" when you hover the mouse over a cell. It is possible to change the appearance of notes using the built-in language.
In order to account for money and goods, various tables are widely used in business. Almost every document is a table.
One table lists the goods to be shipped from the warehouse. Another table shows the obligations to pay for these goods.
Therefore, in 1C, working with tables occupies a prominent place.
Tables in 1C are also called “tabular parts”. Directories, documents and others have them.
The query, when executed, returns a table that can be accessed in two different ways.
The first - faster - selection, obtaining rows from it is possible only in order. The second is uploading the query result into a table of values and then random access to it.
//Option 1 – sequential access to query results
//get the table
Select = Query.Run().Select();
// we go through all the lines of the query result in order
While Select.Next() Loop
Report(Selection.Name);
EndCycle;
//Option 2 – uploading to a table of values
Request = New Request("SELECT Name FROM Directory.Nomenclature");
//get the table
Table = Query.Run().Unload().
//further we can also iterate through all the lines
For each Row from Table Cycle
Report(String.Name);
EndCycle;
//or arbitrarily access strings
Row = Table.Find("Shovel", "Name");
An important feature is that in the table that is obtained from the query result, all columns will be strictly typed. This means that by requesting the Name field from the Nomenclature directory, you will receive a column of the String type with an allowable length of no more than N characters.
Table on the form (thick client)
The user works with the table when it is placed on the form.
We discussed the basic principles of working with forms in the lesson on and in the lesson on
So, let's place the table on the form. To do this, you can drag the table from the Controls panel. Similarly, you can select Form/Insert Control from the menu.
The data can be stored in the configuration - then you need to select the existing (previously added) tabular part of the configuration object whose form you are editing.
Click the "..." button in the Data property. In order to see the list of tabular parts, you need to expand the Object branch.
When you select the tabular part, 1C itself will add columns to the table on the form. Rows entered by the user into such a table will be saved automatically along with the reference book/document.
In the same Data property, you can enter an arbitrary name and select the Value Table type.
This means that an arbitrary table of values has been selected. It will not automatically add columns, nor will it be automatically saved, but you can do whatever you want with it.
By right-clicking on the table you can add a column. In the properties of a column, you can specify its name (for reference in 1C code), the column heading on the form, the connection with the attribute of the tabular part (the latter - if not an arbitrary table is selected, but a tabular part).
In the table properties on the form, you can specify whether the user can add/delete rows. A more advanced form is the View Only checkbox. These properties are convenient to use for organizing tables intended for displaying information, but not editing.
To manage the table, you need to display a command panel on the form. Select the menu item Form/Insert Control/Command Bar.
In the command bar properties, select the Autofill checkbox so that the buttons on the panel appear automatically.
Table on form (thin/managed client)
On a managed form, these actions look a little different. If you need to place a tabular part on the form, expand the Object branch and drag one of the tabular parts to the left. That's all!
If you need to place a table of values, add a new form attribute and in its properties specify the type – table of values.
To add columns, use the right-click menu on this form attribute, select Add attribute column.
Then also drag the table to the left.
In order for a table to have a command bar, in the table properties, select the values in the Usage – Command bar position section.
Uploading a table to Excel
Any 1C table located on the form can be printed or uploaded to Excel.
To do this, right-click on an empty space in the table and select List.
In a managed (thin) client, similar actions can be performed using the menu item All actions/Display list.
A spreadsheet document is used to create primary documents and reports that consist of cells organized into rows and columns. Each row and column has its own unique number. In this case, individual cells, ranges and graphic objects can be assigned a name by selecting the desired area using the “Table - Names” section. Then enter the name in the input field and click “Assign”.
In order to delete a name, select it from the list and click on the “Delete” button. Those areas that the user has not assigned a name will be given a name automatically by the program. These names can be seen in the Properties palette. The tabular editor can be used to create a new document, view the results of previous reports, and edit tabular documents.
How to create a spreadsheet document in 1C
To create a new document, click on the “File” - “New” section, then select the “Table document” line and confirm with the “OK” button. If you need to open an already created document, then in the “File” - “Open” section, select the file name of the required document from the list. In this case, you can select any format of the document to be opened by clicking on the “File type” field.
When creating a table, select a cell and enter data (text, formula, numerical data). If you need to change the size of the cells, then in the Menu select “Format - Row - Auto-fit height - Column - Auto-fit width”. To do this, move the mouse over the border of the row or column header, hold down the left mouse button, and drag to change the size. In 1C, the row height of the spreadsheet document and the column width will change. The auto-selection command is opened by double-clicking on the title border.
You can safely repeat all the steps in your database or easily create a spreadsheet document in 1C: Ready-made solutions - the cloud version of the 1C licensed program. Test the service for free for 14 days in 1C Online.
How to change row height and column width in 1C
You can resize the rows and columns of a spreadsheet document manually or automatically.
In the 1C program, the row height of a spreadsheet document will change manually, if you hover the mouse over the bottom border of the line header and wait until the mouse cursor transforms into a forked vertical arrow. After this, we set the line height we need by moving the borders of the header.
You can change the column width by placing the mouse pointer on the right border of the header and waiting for the mouse cursor to transform into a forked horizontal arrow. Move the header border with the mouse to set the required width.
The minimum required column width can be set with two clicks on the header border. The editor will independently set the width into which the entire text fits.
There is another way to change it manually. Press the “Ctrl” key and move the mouse over the desired cell border. After the cursor shape changes, set the desired size.
In 1C, the column width of a spreadsheet document will change automatically, if you select the required column and select the section “Table” - “Cells” - “Column Width”. In the window that appears, specify the width and click “OK”.
When creating a new spreadsheet document, the width of all columns is set by default. But it can also be changed. To do this, in “Table” select “Cell” - “Column Width”. In the window that appears, set the column size and click “OK”. If you select “Auto”, then the size is set to the entire document.
The line height will automatically change if you check the “Auto line height” checkbox. If the checkbox is not checked, then a fixed line size is set in the “Row Height” field.
In order to set different column widths for different lines, you need to select one or more lines of the document.
Move the mouse to the column separator and press the left mouse button. Without releasing the button, pull the column in the desired direction (for example, to the left) and release the button. In the window that appears, click on “OK”.
You can also control the color of the text, background and frames.
As a rule, a spreadsheet document is formed on the basis of a layout, which is a template for creating a printed form. The layout itself can include pictures, charts, and pivot tables. The example shows a layout for printing a consumable invoice:
Some cells contain only text, others contain the names of parameters that must be specified in the program module through the “Parameters” of the “Spreadsheet Document” objects.
Grouping rows in a spreadsheet document
In a spreadsheet document, you can group rows and columns in order to categorize data in reports. There are horizontal groupings that can be located on the right or left, and vertical groupings placed above or below.
You can also display grouping levels. When you click the numbers in the header, all groups are expanded at once. When applying grouping, the level indentation is formed automatically:
Decoding of the 1C spreadsheet document
We will use the decryption mechanism to obtain a detailed or additional report. To do this, click on a line or cell in the document.
There are two actions when decrypting a document:
- Standard - click on a document or directory element, after which the system will open this object for viewing.
- Non-standard - we will set an algorithm for obtaining a detailed report. To do this, you need to reformat the existing one using additional selection conditions (“show sales only for this counterparty”). Or,
When using decryption, you can get a completely new report (for example, “show invoices that contributed to the sales volume for this counterparty”).
Since reports contained in a specific application solution are called automatically, this complicates the user's work. In order to make the work easier, a decryption mechanism is needed.
How to save a spreadsheet document in 1C
In order to be able to use the spreadsheet document in the future (open, view, print), you need to save it. Select the “File” section and click “Save”. After this, a window will appear on the screen in which we indicate the directory and file name. If you need to rename the document, or save it in a different format, select the “File” and “Save As” section.
As a rule, the document is saved in the format used by the 1C:Enterprise system (*.mxl). However, the spreadsheet document can be exported to other data storage formats, including an ODF spreadsheet format (*.ods), a Word document (*.docx), an HTML document (*.htm) or a UNICODE text file (*. txt). In addition, it is possible to save a spreadsheet document in 1C in excel or pdf.
Printing a spreadsheet document in 1C
To view the document before printing, select the “File - Preview” section. To exit the preview, click the “Close” button.
In the page settings, we will set the parameters for printing a spreadsheet document in 1C - scale, size, borders, etc. In the table editor, set the parameters for placing rows and columns. “Page Position” forces pagination, repeating rows and columns, etc.
We will output the document to the printer by clicking on the “File” - “Print” section. A window with settings will appear on the page, where you need to set printing parameters - select a printer, page range and number of copies - and click “OK”.
Do you have any questions? Ask them in the comments and we willWe will definitely answer them.
For all output documents (primary documents and reports) in the 1C:Enterprise system, a single format is provided - the format of tabular documents. A spreadsheet document is a powerful interactive means of displaying information and can be used either on its own or as part of any of the forms used in the application solution. At its core, a spreadsheet document resembles a spreadsheet - it consists of rows and columns in which data is stored, but its capabilities are much broader:
Cell design
A spreadsheet document has great design capabilities. The developer can set the font type and size to display text located in the cells of a spreadsheet document:
A spreadsheet document allows you to decorate cells with frames of various types:
The developer also has the ability to control the color of text, background and frames, choosing colors from both the operating system palette and the Web color palette supported by the platform, or from the palette of the style that will be used by the application solution:
Groups
A spreadsheet document supports the ability to group rows and columns. This allows you to group data in reports using an arbitrary number of nested groupings.
There are horizontal and vertical groupings, and the developer has the ability to control the location of totals in the grouping: for vertical groupings they can be located at the top or bottom, and for horizontal groupings they can be located on the right or left.
Displaying grouping levels is supported, and by clicking the numbers in the headings, you can expand all groupings of a given level at once and collapse more detailed groupings.
The indentation of hierarchy levels when using groupings is generated automatically by the system:
Transcripts
The system supports a decoding mechanism that allows the user to obtain a detailed or additional report by clicking on a row or cell of a spreadsheet document. The platform supports the ability to process mouse clicks in spreadsheet document cells. In this case, the system can perform both standard actions and algorithms specified by the developer.
Standard decryption actions are performed, for example, by clicking on a document or directory element. In this case, the system will open this object for viewing (unless other behavior is provided by the developer).
Non-standard transcripts are processed using the built-in language. For example, a developer can set an algorithm for obtaining a detailed report by reformatting an existing one using additional selection conditions (“show sales only for this counterparty”). Or, using the decryption, the user can receive a completely new report (for example, “show invoices that contributed to the sales volume for this counterparty”).
The decryption mechanism can significantly facilitate the user's work, since calling certain reports contained in a specific application solution occurs automatically and does not require the user to specify initial parameters. The settings for the desired report can be determined automatically based on the context in which the user calls the report.
Notes
The developer has the ability to set notes for individual cells or groups of cells in a document. The note cell has a small triangle in the upper right corner. When you hover over a cell, the comment appears in a pop-up window. Using notes, you can add additional (reference) information to spreadsheet documents that is not displayed on the screen (in normal mode), but can be easily viewed if you move the mouse cursor over the desired cell:
Column width
A spreadsheet document supports the ability to set different column widths for different rows. This feature is very important for regulated reports. In fact, in this case, the spreadsheet document will consist of several tables, which, for example, is often found in accounting and tax reporting:
Pivot tables
A spreadsheet document can contain pivot tables. Pivot tables are a powerful tool for programmatically and interactively presenting multidimensional data.
For a pivot table, the user can independently set the composition of the rows, columns and displayed data by simply dragging the desired fields into the pivot table using the mouse. Such a table, for example, can display sales volume by counterparties and product names. Dimension values are displayed at the top of the table and sidebar, and data area cells contain summary information at the intersection of measurement data. The user can add or delete dimensions and resources, and change their relative position.
The pivot table automatically changes the composition of rows, columns and their formatting, in accordance with the data that is placed in it; In addition, the system supports several standard design options for pivot tables that can be used by the developer. All this makes pivot tables the easiest tool for analyzing multidimensional data, since the developer requires minimal effort to create them:
Additional Information
You can become familiar with using pivot tables in real time. To do this, you can download the demo video "Example of developing a trading mini-system", which shows the process of developing a trading mini-system using a pivot table in the "Sales analysis by period" report.
Entering data into a spreadsheet document
Although spreadsheet documents in the 1C:Enterprise 8.0 system are used mainly to present already processed information, it is possible to directly enter data into a spreadsheet document as into a “regular” spreadsheet.
In the process of entering data into the cells of a spreadsheet document, they can be processed by procedures in the built-in language, and the results of such processing can be used when calculating other cells of the spreadsheet document.
Direct data entry into a spreadsheet document can be used, for example, when creating a balance sheet:
Preservation
Since a spreadsheet document is most often used to generate output documents, it can be saved to a file on disk for later use or transfer to other computers. A spreadsheet document can be saved either in its own format or exported to other data storage formats, including an Excel sheet or MXL7 format (for compatibility with platform version 7.7):
Spreadsheet Document Editor
To create layouts that will eventually form a tabular document shown to the user, the developer can use the tabular document editor - a powerful tool that combines the design capabilities of a tabular structure and vector graphics. It allows you to create both small documents with a very complex structure of lines (such as a payment order), and voluminous statements, journals, etc.
Most training materials on programming in the 1C system describe the formation of printed forms based on an object "Tabular document" are limited to displaying the finished form on the screen. For users, what is much more important is how the document will look when printed. Here, in addition to a well-designed layout, printing parameters also play a role.
Almost all the parameters available in the print settings dialogs (printer settings, page properties) can be specified directly when generating a spreadsheet document.
Let's look at the properties and methods of a spreadsheet document related to printing settings (in the examples given, “TabDoc” is an object of the “Spreadsheet Document” type).
Property "PrinterName" Allows you to specify a printer other than the default for printing. The name must match the printer name configured on the system:
TabDoc. PrinterName = "HP LaserJet 3050 Series PCL 6";
When printing a batch of documents, you can save time by setting the collation option:
TabDoc. ParseByCopies = true;
The number of copies can be specified as follows:
TabDoc. Number of Instances= 5 ;
Of course, you can set the fields:
TabDoc. FieldLeft = 20 ; //Left margin is 20mm, other margins are 10mm (default)
A few more page properties:
TabDoc. OrientationPage=
OrientationPage. Landscape;
TabDoc. InstancesOnPage=
2
;
//there will be 2 pages on the sheet
TabDoc. Autoscale = true; //similar to the “page width” scale settings
If necessary, you can specify a specific scaling value in percentage (property "Print Scale").
Property "Page Size" allows you to set standard page formats - “ A 3”, “A4”, “A 5” (a complete list of options is available in 1C help).
TabDoc. PageSize = "A3" ; // letter A must be English
For a non-standard paper size (Custom), you can specify the page height and width (in mm):
TabDoc. PageSize = "Custom" ; //non-standard size
TabDoc. PageHeight = 350 ;
TabDoc. PageWidth = 350 ;
Also in a spreadsheet document, you can control the output of headers and footers and their contents. This is done using properties "Page header" And "Footer". For example:
TabDoc. Page header. Output = true; //the header will be printed
TabDoc. HeaderSizeTop=
7
;
//footer size 7mm (default 10mm)
TabDoc. Page header.
VerticalPosition=
VerticalPosition. Top ;
TabDoc. Page header.
Home page=
2
;
//the footer is displayed from the second page
FontFooter=New Font("Courier New", 8,True);
TabDoc. Page header. Font = FontFooter;
//oblique font
TabDoc. Page header. TextInCenter = "Page header";
TabDoc. Page header. TextRight = "Page[&PageNumber] of [&PagesTotal]";
//pagination
TabDoc. Page header. TextLeft = "[&Date]" ; //The current date
The generated document is sent for printing using the method "Type()". There are two possible call options.
1) Directly to the printer:
TabDoc. Print(.NotUse);
TabDoc. Print(true);
2) Before printing, a print dialog will be displayed:
TabDoc. Print ( DialogUsageModePrint. Use );
TabDoc. Print(false);
In addition, you can control how the document is divided into pages. You can estimate the number of pages in a document based on the current printer settings:
TabDoc. Number of pages();
Using methods "CheckOutput()" And "CheckAttach()" You can determine whether a spreadsheet document or an array of spreadsheet document areas will fit on the page in height and width at the current printer settings.
It should be borne in mind that the operation of the last three methods depends on the installed printer. If the method cannot find it, an exception is thrown.
Methods allow you to force insertion of page breaks"OutputVerticalPageSeparator()" And "OutputHorizontalPageSeparator()".
Thus, you can control page-by-page printing and control page filling:
If Not TabDoc. CheckOutput ( Array of OutputAreas) Then
TabDoc. OutputHorizontalPage Separator();
endIf;
An important characteristic of the 1C:Enterprise 8.2 platform is the strict separation of properties and methods by execution context. While all of the above properties are available in any context, the methods listed are not available on the thin client. The exception is the “Print()” method, the availability of which is limited to the client part for obvious reasons. This means that the formation of a spreadsheet document should occur on the server, and it should be sent for printing in a client procedure.
