I. The Problem：
When doing statistical analysis with reports, users may need to merge the result of a certain type of report or the same report with different parameters into a single Excel file. Ideally, each report will be mapped into a different sheet of the single file. Take the Region Sales Situation table as an example. Carry out the statistical analysis by regions, and combine the data of each region into a single Excel file as multiple sheets. So, how to implement such a requirement by codes to achieve the result as shown below?
Case: for the region sales table, export the data for each region as a sheet in an Excel file, with each sheet named as the corresponding region name.
II. Approaches
1. Load the Template
TemplateWorkBook workbook = TemplateWorkBookIO.readTemplateWorkBook(
"Gettingstarted.cpt");
2. Assign Values To Parameter
// Set the parameter value to "East China" and obtain the result after execution, then save it to rworkbook
Parameter[] parameters = workbook.getParameters();
java.util.Map parameterMap = new java.util.HashMap();
for (int i = 0; i < parameters.length; i++) {
parameterMap.put(parameters[i].getName(), "East China");
}
PageWorkBook rworkbook = (PageWorkBook)workbook.execute(parameterMap,new PageActor());
rworkbook.setReportName(0, "East China");
Take the result worksheet of the first execution (namely, rworkbook) as a container to add the calculation results of other regions. Therefore, rename the first sheet in rworkbook to "East China", referring to the data for East China.
rworkbook.setReportName(0, "East China");
// Clear the parametermap. Change the parameter value to "North China" and get the calculated ResultReport
parameterMap.clear();
for (int i = 0; i < parameters.length; i++) {
parameterMap.put(parameters[i].getName(), "North China");
}
PageWorkBook rworkbook2 = (PageWorkBook)workbook.execute(parameterMap,new PageActor());
PageReport rreport2 = rworkbook2.getPageReport(0);
rworkbook.addReport("North China", rreport2);
3. Export the Result Workbook
// Define output stream
FileOutputStream outputStream;
// Export the result workbook as an Excel file
outputStream = new FileOutputStream(new File("E:\\ExcelExport.xls"));
PageExcelExporter excelExport = new PageExcelExporter(ReportUtils.getPaperSettingListFromWorkBook(workbook));
ExcelExport.export(outputStream, workbook.execute(parameterMap));
III. Instructions
1. Please see here for the detailed code:
2. Compile and Run
Once the program is compiled and run, you will have the exported file excelexport1.xls in the specified path and see that the data of each region have been saved as sheets separately.
Previous：Get the cell of formula in custom function
