Output as Excel (Java)
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.
import com.fourjs.report.runtime.*;
You can then use methods from the ExcelRenderer class to configure your Excel output.
Example
ExcelRenderer
class:// code snippet starts
String designFile = "OrderReport.4rp";
FormatHandler handler = new FormatWriter(outputFilename);
ExcelRenderer renderer = new ExcelRenderer(handler);
//configuring Excel output
renderer.setMergeCells(false);
renderer.setMergePages(true);
renderer.setOutputFormat(ExcelRenderer.OutputFormat.XLSX);
FourRpLayouter report = new FourRpLayouter(designFile, renderer);
//Run the report
report.setDebugLevel(9);
// some code omitted - view full source in demo
report.runFromJAXBObject(data);
// open the generated file:
File result = new File(outputFilename);
Desktop desktop = Desktop.getDesktop();
desktop.open(result);
// code snippet ends
- A
FormatHandler
object namedhandler
is defined using theFormatWriter
class, taking the name of the output file as its input. - The
setMergeCells()
method, when set to true, activates cell merging when the content spans several cells; when set to false, separate empty cells are created. - The
setMergePages()
method, when set to true, activates page merging; when set to false, separate sheets for each page are created. - The
setOutputFormat()
method sets the output format as determined by the argument. When set toExcelRenderer.OutputFormat.XLSX
, the format is output as XLSX format (for Microsoft Excel starting with version 2007).
For the full list of methods for this renderer class, refer to the Genero Report Writer Java API documentation.
Excel report output
The output is 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.
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.
When items in the report design overlap, by default the placement but not the alignment is preserved in the spreadsheet. To make the necessary decisions, Genero Studio marks the column and row boundaries internally with tabs. If two consecutive tabs have no element that bounds on them, the column or row collapses. The implication of this is:
- White space (empty columns, empty rows) is eliminated when possible.
- Elements that overlap may be placed in the same column, but they maintain their relative placement (above/below).
- Contiguous items are never placed in the same column, but maintain their relative placement and alignment.
The values in the cells are generated for the report, not the Excel formulas.
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 therefore 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.