Use a Java report streaming class
This topic describes how to use a Java report streaming class to generate Excel reports.
What is Java report streaming class
A Java report streaming class is a Java class that runs at the end of the Excel report process. It enables you to use the Apache POI SXSSF (Streamed XSSF) API to edit the XLSX file, and to insert data in the template row by row with high performance and low memory consumption.
You can retrieve all the data available to the report from the Java code. You can also use the same database-related tools as in the Java preprocessor class.
When to use Java report streaming class
When you have a huge amount of data (tens of thousands of rows or hundreds of thousands of cells) to include in your report, you need to stream the data to the XLSX to get good performance with low memory usage. One option is to use the large data mode (see Large data mode for details). However, the large data mode has these limitations: data can only be inserted at the end of the first spreadsheet of the workbook; you cannot change the style of the cells.
If you want full control of the XLSX streaming process, you can use a Java Report Steaming class as an alternative. This gives you the access to the workbook through the POI SXSSF API. This API lets you stream data to any spreadsheet of the workbook, and gives you full POI control of the data inserted (including cell style).
Using Apache POI SXSSF API has these limitations: you can only add data to existing spreadsheets, and can only manipulate one row at a time. To avoid the Apache POI SXSSF API limitations, you can first modify the workbook with a Java report postprocessor (see Use a Java report postprocessor for details) and then insert large amounts of data with a Java Report Streaming class.
Note: You cannot use both the large data mode and a Java report streaming class or a Java report postprocessor. Doing so will cause an error.
How to use Java report streaming class
Follow the steps below to use a Java report streaming class in your Excel reports.
Create the report type
In the report type’s command steps, anywhere between the first line
ksc_run_excel_report and the last line
ksc_end_report_parameters, add the
REPORT_STREAM_DATA_CLASS parameter. Set the parameter value to the fully qualified class name of your Postprocessor class. For example , for the class
com.hpe.ppm.excelreports.SampleSteamingClass that you can find in the attached bundle in folder
7 - Using a Java report streaming class, the line would be:
Create a Java report streaming class
You should create a Java class that extends the existing abstract class
com.mercury.itg.common.excel.exporter.data.ExcelReportStreamData. The single method to implement is:
public void streamDataToWorkbook(SXSSFWorkbook wb).
You can then directly work on the passed SXSSF workbook object to modify the report XLSX document, but keep in mind that you can only add rows to the spreadsheets with this API.
For more information, see the sample streaming class
SampleStreamingClass.java located in the attached bundle (in the path
7 - Using a Java report streaming class\1 - Sample Report Streaming class source code\com\hpe\ppm\excelreports).
Deploy the Java report streaming class
Deploy the Java Report Streaming class in the same way as you deploy a Preprocessor class. See Deploy the Java preprocessor class.
Create the Excel template
There is nothing special to do on the excel template if you use a Java Report Streaming Class. Just make sure that there are no empty rows at the end of the sheets you want to modify. You can remove them first with a Java Report Postprocessor class.
Example of report using a Java streaming class
You can find an example of an Excel template and the resulting report in file
Samples\7 - Using a Java report streaming class. The source code and a jar version of the Java Streaming class are included.