Output a single-sheet document to Excel

By default, when you send report data to an Excel™ spreadsheet, each page produces a separate Excel sheet. You can merge the pages into a single sheet, but you must then configure the report to avoid problems with alignment and calculation.

If the page breaks are not desirable, you can change the page size to a larger custom value. However, creating a huge page can adversely affect memory reclamation and performance. It is often better to use a standard page size to merge the pages into a single-sheet result.

Note: If you specify "XLS" in the call to fgl_report_selectDevice, and the resulting sheet has more than 65536 rows, the exceeding rows will spill over into extra sheets. If you specify "XLSX" as the Excel format, the size of the sheet is not limited by available memory.

In the fgl_report_configureXLSDevice or fgl_report_configureXLSXDevice method, use the mergepages parameter to write all data to a single sheet:

...
   CALL fgl_report_configureXLSXDevice (
   NULL,  #fromPage INTEGER,
   NULL,  #toPage INTEGER,
   NULL,  #removeWhitespace INTEGER,
   NULL,  #ignoreRowAlignment INTEGER,
   NULL,  #ignoreColumnAlignment INTEGER,
   NULL,  #removeBackgroundImages INTEGER,
   TRUE ) #mergePages TRUE
   CALL fgl_report_selectDevice("XLSX")
...

Fixing issues with column alignment

When you merge sheets, elements such as page headers and group totals might cause problems with layout and calculation. For example, Figure 1 shows how a page header can disrupt the columns.

Report has been output to Excel. Columns do not line up correctly and are not useful for calculation — Figure: Single Page Excel -- Unaligned

To avoid this problem, remove all page footers and turn all page headers to first page headers. You can remove these elements at runtime by using the RTL Runtime class method producingExcelOutput():

In all Page Footers, and in the First Page Header, set the Visibility Condition property to:
```
!Runtime.producingExcelOutput()
```

In the Any Page Header, set the Section property to:

Runtime.producingExcelOutput()?Port.FirstPageHeader:Port.AnyPageHeader

When you merge sheets, the page headers and footers now line up correctly, as in Figure 2.

Report has been output to Excel. Columns line up correctly and can be used for calculation — Figure: Single Page Excel -- Aligned

Group totals

Group totals can also disrupt the column alignment. For example, Figure 3 shows a report design document where the "Total" is created from two separate elements: a Word Box and a Decimal Format Box.

Report Design Document showing the group total — Figure: Report Design Document -- With Group Total

For the first page of the report, the product ID is listed in column A and the product name is listed in column B. However, for the last page of the report, the overall total is printed in column B and the product name is moved to the right into column C, as in Figure 4

Report has been output to Excel. Group Total caused disruption of column alignment — Figure: Single Page Excel -- With Group Total

Note: This problem occurs because of the way the report engine renders a page into Excel output. The elements of the original output with floating point coordinates are placed into cells of the spreadsheet in a process called “tabulation”. The edge of an element that appears above the edge of another element in the pixel exact output (for example, SVG) will end up in a row with a lower index than the other element. The edge of an element that appears to the right of the edge of another element will be placed in a column with a higher index than the other element. Tabulation happens for each page as it is streamed, and so the last page might be tabulated in a different way to the first page.

To avoid this problem, edit the report design document using one of the following strategies:

Force a page break by adding a vertical box before the total and setting the Y-size of the box to “rest”. The page break moves the total line to a separate page where it is tabulated apart from the main table data.
Change the size and position of the total so it does not require new cells. In this case, move the decimal format box to the right so it is directly beneath the product name.

Note: You can also increase the page length of the report so it fits on one page. This strategy is not recommended because it can cause performance and memory problems.