Send report data to an Excel spreadsheet

The report output can be sent to an Excel spreadsheet by passing the string "XLS" or "XLSX" to the function fgl_report_selectDevice. The output will be in the specified Excel format, with the XLS format limited to 65,536 rows. In contrast, for the XLSX format the document is generated with constant memory consumption. so that very large documents can be produced without exhausting heap space.

Note: The XLSX format can only be opened with newer versions of Microsoft™ Excel ( 2007 or later). A backward compatibility pack can be downloaded from Microsoft; however, the XLSX format will then be subject to the 65,536 limit of the earlier versions.

Any existing report, including ASCII, can be output to the spreadsheet.

The layout of the cells (size, font color, etc.) can be predictably controlled from the Report Designer. The goal is to put the report layout into the cells of the spreadsheet efficiently. There is an option in the functions fgl_report_configureXLSDevice and fgl_report_configureXLSXDevice to ignore the vertical or horizontal alignment. The default is TRUE for Excel document output, since the emphasis is on the ability to compute the values in the cells, rather than primarily on the appearance.

When items in the report design overlap, by default the placement is preserved in the spreadsheet, but not the alignment. To make the necessary decisions, Genero Studio marks the column and row boundaries internally with tabs, If there are two consecutive tabs that have no element that bounds on them, the column or row will collapse. The implication of this is:

Rivers of white space (empty columns, empty rows) are eliminated when possible.
Elements that overlap may be placed in the same column, but they will still maintain their relative placement (above/below).
Contiguous items will never be placed in the same column, they will maintain their relative placement and alignment.

The values in the cells are generated for the report, not the Excel formulas. Graphical elements such as bar codes are implemented by rendering them as images. Business charts are currently drawn as tables; we cannot create charts or pivot tables.

This code fragment illustrates the functions enabling Excel output:

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

Creating a report with data but no titles

To obtain a spreadsheet containing only the data in a flat table:

Create a new empty report (from the main menu, File>>New, Reports, Empty report).
Configure a large custom page size, 300cm x 200cm, for example (from the main menu, File>>Report Properties, Paper Setting).
From the Toolbox, drag a Stripe container into the report design.
In the Data View, select the button create a table column value object; then, double-click the fields to select them for the report.
In the Structure View, associate a Trigger node with the stripe, to specify when the contents of the stripe should print (ON EVERY ROW, for example)

Creating a report with tables that contain headings

To obtain a spreadsheet containing tables with column headings, use the standard list template (from the main menu, File>>New, Reports, List report). Use a large custom page size, 300 cm x 200cm, for example. The output will contain a title for the document and headings for each column.

Creating a single-sheet document

Currently each page produces a separate Sheet in Excel. 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. To avoid this problem, we recommend that you use a standard page size and set the mergepages parameter of the function fgl_report_configureXLSDevice or fgl_report_configureXLSXDevice. This will merge the pages into a single-sheet result.

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