Skip to Content

Spreadsheet documents

Optimizing forms that comply with legislation requirements

This article provides recommendations for optimizing forms that comply with legislation requirements, and other similar entities (such as reports or spreadsheet documents).

1. Optimize templates by avoiding a large number of columns or merged cells.

You can win a fraction of a second every time a user finishes editing a cell by simplifying a template. We recommend that you use custom column width in each row instead of cell merging (the users will not notice any difference).

2. Calculate the changes on the client side. Only calculate cells that actually depend on the changed cell.

If your application makes a server call in order to update the form state when a user changes a cell value, this can impact the performance.

3. Do not update button states dynamically based on the currently selected spreadsheet document area.

Instead, make the action tied to a button available only in a specific document area. If you cannot explicitly specify the area, you can implement a user notification explaining why the action is not available.

4. If a server call is necessary, implement a single call.

This example includes two server calls, which is incorrect:

CalculateOnServer("GoodsPage");
CalculateOnServer("TaxesPage");

To correct this, you have to replace the two lines with a single server procedure call, which includes all of the server method calls.

Header and footer usage specifics in spreadsheet documents

This article describes the specifics of spreadsheet document header and footer operations performed from 1C:Enterprise script.

Header and footer operations

When a spreadsheet document is output to an empty spreadsheet document, its headers and footers are output to the target document. So if you need to set headers or footers from 1C:Enteprise script, do it after outputting the entire report or after outputting at least one its area.

Correct:

FormItems.SpreadsheetDocumentField.Clear();
ReportBuilderReport.Put(FormItems.SpreadsheetDocumentField);
FormItems.SpreadsheetDocumentField.Footer.Enabled = True;
FormItems.SpreadsheetDocumentField.Footer.CenterText = "[&PageNumber] out of [&PagesTotal]";

Incorrect:

FormItems.SpreadsheetDocumentField.Clear(); 
FormItems.SpreadsheetDocumentField.Footer.Enabled = True;
FormItems.SpreadsheetDocumentField.Footer.CenterText = "[&PageNumber] out of [&PagesTotal]";
ReportBuilderReport.Put(FormItems.SpreadsheetDocumentField);

Alternatively, you can specify headers and footers in the report template, either in the visual editor or from 1C:Enterprise script(prior to using the template).

Example:

ReportBuilderReport.Template = ExternalDataProcessorObject.GetTemplate("Report");
ReportBuilderReport.Template.Footer.Enabled = True;
ReportBuilderReport.Template.Footer.CenterText = "[&PageNumber] out of [&PagesTotal]";
FormItems.SpreadsheetDocumentField.Clear();
ReportBuilderReport.Put(FormItems.SpreadsheetDocumentField);

Page-by-page output to a spreadsheet document

Some reports have a summary area at the end of each page. The summary might contain the page total, the number of rows on a page, or any other data. 1C:Enterprise script provides tools for splitting spreadsheet documents into pages. This article describes how to implement page-by-page data output to a spreadsheet document.

CheckPut() method of spreadsheet document

The SpreadsheetDocument object has the CheckPut() method, which checks whether the list of areas fits a single page. If the method returns True, outputting the areas to the spreadsheet document does not require adding a new page. If the method returns False, outputting the areas adds a page. The check is based on the document print settings.

For example, you need to create a sales report. The first page should have a header displaying some general report data, while the rest of the page headers should contain the column headers of the tabular section. The page footers should display page totals. The last page footer should display the report total.

To implement this, you need the CheckPut() method. You can use the following algorithm:

1. Output the first page header area to the spreadsheet document. 
2. For each row:

2.1. Check whether the row area together with the page total area fit the current page.
2.2. If they fit the page, output the row area.
2.3. Otherwise output the page footer, a horizontal page break, the page header, and the row.

3. Output the report footer. 

The following example shows how you can implement this algorithm in 1C:Enterprise script.

RowWithFooter = New Array; // create array for output checks 
RowWithFooter.Add(DetailedRecordsArea); 
RowWithFooter.Add(PageFooter); 
PageTotal = 0;
SprDoc.Put(HeaderArea); // output report header 
SprDoc.Put(TableHeaderArea); // output page header 
While DetailsSelection.Next() Do // for each row  

    // fill spreadsheet document area parameters
    DetailedRecordsArea.Parameters.Fill(DetailsSelection); 

    If Not SprDoc.CheckPut(RowWithFooter) Then

        // The total row does not fit. 
        PageFooter.Parameters.Amount = PageTotal; // set page total
        SprDoc.Put(PageFooter); // output page total
        SprDoc.PutHorizontalPageBreak(); // new page 
        SprDoc.Put(TableHeaderArea); // output page header
        PageTotal = 0; // reset page total

    EndIf; 

    SprDoc.Put(DetailedRecordsArea); // output next row 

    PageTotal = PageTotal + DetailsSelection.Amount; // increment page total

EndDo; 
PageFooter.Parameters.Amount = PageTotal; // set page total 
SprDoc.Put(PageFooter); // output page total 
SprDoc.Put(FooterArea); // output report footer

CheckAttachment() method of spreadsheet document

The CheckAttachment() method of spreadsheet document is similar to the CheckPut() method. It checks whether the table will span additional pages horizontally when printed after adding areas with the Join() method.

Formatting spreadsheet document rows

1C:Enterprise spreadsheet documents support custom column width for individual rows. This article describes how to use this feature.

Customizing column width

To customize column width in several rows, select the rows and drag the column borders. Once you resize a column, the platform asks whether you want to create new row format. If you confirm that, new format is created for the selected rows and next time you change the column width in any of these rows this will only affect these rows.

You can set the row format for a specific spreadsheet document area using 1C:Enterprise script method CreateFormatOfRows(). It changes the row format in the area where it is called.

Selecting and copying rows

The built-in 1C:Enterprise spreadsheet document editor does not allow selection of rectangular areas if they include multiple row formats. To copy a document part that includes multiple row formats, select the entire rows. You can do it either by clicking the row headers or by pressing Alt + Shift + Up Arrow/Down Arrow.

If you copy a group of rows with multiple formats to the clipboard, the formats are preserved when you paste the rows. If rows with this specific format are already present in the target document, the pasted rows are treated as if they have the same format. In other words, copying and pasting a row with custom format within a single document results in two rows having the same format and dragging a column border in one of the rows changes the column width in the other row as well.

The algorithm described above is also applied when rows are copied from 1C:Enterprise script. Rows that have the same format in a source document also have the same format in a target document.

Exporting spreadsheet documents

Exporting a spreadsheet document with custom row format to Microsoft Excel leads to a loss of custom column width settings. If you want to preserve the formatting in an exported spreadsheet document, export it to HTML format instead.

 

Icon/Social/001 Icon/Social/006 Icon/Social/005 Icon/Social/004 Icon/Social/002