Create an Excel spreadsheet report

Set up and configure output to Microsoft® Excel™ from your reporting application. This option is useful if you want to perform custom calculations in Excel.

Note:

Excel reports, as output from Genero Report Writer, do not print at high quality. If you require high-fidelity printing, use one of the other output options, such as SVG.

Use fgl_report_selectDevice("XLS") or fgl_report_selectDevice("XLSX") to output the data to an Excel spreadsheet. Configure the report output using fgl_report_configureXLSDevice or fgl_report_configureXLSXDevice.

To configure cell merging, use fgl_report_setXLSMergeCells or fgl_report_setXLSXMergeCells.

Borders are taken into account in the Excel output, but are disabled by default. To enable borders, use fgl_report_setXLSHandleBorders or fgl_report_setXLSXHandleBorders.

Example

 # Merge pages
 IF fgl_report_loadCurrentSettings(reportname) THEN
     CALL fgl_report_configureXLSXDevice(NULL,NULL,NULL,NULL,NULL,NULL,TRUE) 
     CALL fgl_report_selectDevice("XLSX") 
     LET HANDLER = fgl_report_commitCurrentSettings()
ELSE
    EXIT PROGRAM
END IF

Optimizing report design document for Excel

Any report design document (.4rp) can be output to Excel. However, the following guidelines may be useful:

  • Tables provide the best results for mapping to Excel.
  • Avoid overlapping report elements, because they will produce images in Excel.
  • Some borders, rulers, and background coloring don't map well to Excel.

Name sheets in XLS/XLSX output

To define a name for each sheet, place an Infonode named nameOfSheet in the report and give it a value which represents the sheet name. Values can be strings or RTL expressions.

XLS/XLSX does not allow multiple sheets to have the same name. In cases of duplicate names, a number is added to the duplicate sheet names.

The following image shows the Report sample, within the application OrderReport and the file is OrderReport.4rp. An Infonode named nameOfSheet has been added and the value is set to name sheets with the first and last name of each client.



The following image shows the generated report, which is an XLS file with the sheets named depending on the clients first and last names with duplicates adding a number.