Excel report output

As with all output formats, the layout of the cells (size, font color, etc.) in an XLS or XLSX report are controlled from the Report Designer. However, there are default behaviors and options specific to the Microsoft™ Excel™ output.

The XLS format is limited to 65,536 rows per sheet. If the number of rows exceeds this, an additional sheet is automatically created.

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 is then subject to the 65,536 limit of the earlier versions.

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

The report application generates values to be placed in the Excel report; it does not generate Excel formulas. You can edit your Excel spreadsheet to take advantage of Excel's computational abilities.

Default behavior

When a report is output to an Excel spreadsheet, there is a tradeoff between WYSIWYG capability and computability. The default behavior of items in Excel output places emphasis on computing the values in the cells, rather than on maintaining a WYSIWYG appearance.

When items in the report design intersect each other, the items are rendered as images and the resulting bitmap is inserted into the Excel spreadsheet. If debugging is enabled, a warning is issued.

To make the necessary decisions, the Genero Report Engine marks the column and row boundaries internally with tabs. If two consecutive tabs have no element with boundaries on them, the column or row collapses. The implication of this are:
  • Whitespace (empty columns, empty rows) is eliminated when possible.
  • Elements that overlap are placed in the same column or row but maintain their relative placement (above/below or right/left). Elements that overlap horizontally are placed in the same column, and elements that overlap vertically are placed in the same row.
  • Non-overlapping elements are placed in separate cells.
The following report elements are specially rendered in Excel:
  • Graphical elements such as bar codes are rendered as images.
  • Business graphs are rendered as tables.
  • For pivot tables, only the fact rows are shipped. The pivot table is rendered as a flat table structure without being disrupted by group totals. You can then use the data to create an Excel pivot table with business charts and row-spanning formulas.

Changing default behavior

You can change the default behavior using options in the fgl_report_configureXLSDevice and fgl_report_configureXLSXDevice functions. For example, if you want to create cells for empty strings, and avoid collapsing rows and columns for consecutive tabs with no bounding elements, set removeWhitespace to FALSE. If you want the cells to follow the alignment of the report design document (similar to the appearance in SVG output), set ignoreColumnAlignment or ignoreRowAlignment to FALSE. See Figure 1.
Figure: ignoreColumnAlignment in Excel sheets

The top part of this figure shows the display in Excel when ignoreColumnAlignment is set to TRUE (the default). The bottom part of this figure shows the display in Excel when ignoreColumnAlignment is set to FALSE.

If ignoreColumnAlignment is TRUE, the two overlapping elements produce only one column. If ignoreColumnAlignment is FALSE, the two elements produce at least three columns. In both cases, removeWhitespace is TRUE (the default), which means there are only two rows. If removeWhitespace was set to FALSE, the whitespace would create an extra row between "A String" and "Another String".