Excel via AutoFormat

In Ask-Reuben 62 I had an article on the various ways to create Excel spreadsheets.  I only had a few paragraphs on using Genero Report Writer to create Excel spreadsheets, the main focus being to use Apache POI libraries as this gives you the most control.  However we still get a number of support requests that involve developers attempting to use Genero Report Writer to create Excel spreadsheets, so in this article and the next a future article I will give two techniques on how to quickly create Excel spreadsheets using Genero Report Writer.

This first technique does not involve a .4rp file.  The report engine takes the output from the PRINT information in the REPORT block and makes a guess as to the desired format of the format.  The code example is at the end of the article and I will explain a few lines of code in the article.

IF NOT fgl_report_loadCurrentSettings(NULL) THEN

By not passing the name of a .4rp report file to fgl_report_loadCurrentSettings, this is telling the report engine to use compatibility mode.  The report layout is computed from the REPORT statement in the 4gl, no .4rp file is required.

CALL fgl_report_selectDevice("XLSX")

The fgl_report_selectDevice determines the output format.  For Excel you should use XLSX as this will create an Excel file with the more modern extension .xlsx.  I’ll let you Google the difference between .xls and .xslx files when it comes to Excel, I’d consider it unlikely that you would every use “XLS” as the argument to his function.

CALL fgl_report_setAutoformatType("FLAT LIST")

The fgl_report_setAutoformatType function  and the “FLAT LIST” argument instruct the report engine to produce a report that is a list of the values passed to the report, as opposed to respecting the X,Y position of the PRINT statement.

CALL fgl_report_configureAutoFormatOutput(NULL,NULL,NULL,NULL,NULL,NULL)

You can configure some aspects of the report when using an Auto Format report such as “FLAT LIST”.  This is achieved by use of the fgl_report_configureAutoFormatOutput function.  In this example I have not needed to use this function but have listed it anyway.  You might use the second to last parameter to exclude certain columns from your report.

CALL fgl_report_configureXLSXDevice(NULL, NULL, NULL, NULL, NULL, NULL, TRUE)

The fgl_report_configureXLSXDevice function allows you to configure the Excel document.  The key thing is the use of TRUE in the last argument which is mergePages.  This results in a single sheet being produced rather than a new sheet appearing every 50-60 lines.

CALL fgl_report_setXLSXMergeCells(FALSE)

Typically you don’t want cells to be merged together, so use the fgl_report_setXLSXMergeCells function to instruct the engine not to merge cells together.

REPORT
...
ON EVERY ROW
PRINTX row.*

The REPORT instruction is very simple.  It simply passes all the information supplied to it to the Genero Report Engine.

How Compatibility mode works with Genero Report Writer is that it effectively generates the report layout from the data presented to it.  With a normal report it would take the LINE and COLUMN position to place a Report object (WordBox etc) at a certain X and Y position in a report.  With the “FLAT LIST” argument passed to fgl_report_configureAutoFormatOutput, the LINE and COLUMN are ignored, a new row is created for each row triggered by the 4gl and each field output is added to this row.  If you look beneath $GREDIR/src you can see some of the code that is being executed to generate the report (Hint: In $GREDIR/src/saxstyles/GenericReportFormatter.4gl you may recognise some of the configureAutoFormatOptions in some of the IF statements).

If you do want to use Genero Report Writer to create an Excel spreadsheet, the use of “FLAT LIST”  is probably the quickest technique you can use.