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 the 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.

Next page: Analyzing value types


Be the first to know tips & tricks on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.