Build a Rich Excel Report with ZK Spreadsheet and Database"
Line 10: | Line 10: | ||
==Template Excel File with Proper Styles== | ==Template Excel File with Proper Styles== | ||
Construct an Excel template file with proper formulas and styles for '''header''' in <tt>ReportSheet</tt> and '''row''' in <tt>TemplateSheet</tt>. | Construct an Excel template file with proper formulas and styles for '''header''' in <tt>ReportSheet</tt> and '''row''' in <tt>TemplateSheet</tt>. | ||
+ | |||
+ | <tt>ReportSheet</tt> | ||
+ | |||
+ | [[genreport-report.png]] | ||
+ | |||
+ | <tt>TemplateSheet</tt> | ||
+ | |||
+ | [[genreport-template.png]] | ||
==Generate Report== | ==Generate Report== |
Revision as of 02:12, 19 November 2010
ZK Spreadsheet APIs support importing Excel file and exporting to PDF file, copying and pasting cells, and updating values. With all these capabilities plus versatile formula functions provided make it a perfect tool to generate a rich enterprise level report. You can either view(or even do some after editing) the report on the browser and/or print it into PDF later.
Purpose
Combine ZK Spreadsheet with a back end database to generate a rich report.
Template Excel File with Proper Styles
Construct an Excel template file with proper formulas and styles for header in ReportSheet and row in TemplateSheet.
ReportSheet
TemplateSheet
Generate Report
Use ReportSheet as Report showing page, and TemplateSheet as Row template sheet plus some control variables.
The operation is straightforward:
- Load data list from the back-end database.
- Per the number of the retrieved data, copy row template from TemplateSheet to ReportSheet.
- Fill in data to each row on the ReportSheet.
- There you go!
ZUML Example
<window id="reportwin" apply="demo.genreport.ReportComposer" vflex="1">
<button label="Generate the report" onClick="reportwin$composer.generate();"/>
<spreadsheet id="report" src="/genreport.xls" maxrows="40" maxcolumns="8" vflex="1" width="100%"/>
</window>
Report Composer
Using Excel Defined Names to keep information in templateSheet provides more programming flexibility. You can use ZK Spreadsheet Range APIs to retrieve value stored in the cell and/or do various range operations. In this example, copy() and setValue() is used to copy template and fill in data, respectively. Note that you can easily locate a Range by giving Ranges#range() the proper Defined Names.
public class ReportComposer extends GenericForwardComposer {
private Spreadsheet report;
private Book book;
private Sheet reportSheet;
private int offset; //offset to next row
private Range templateRange;
@Override
public void doAfterCompose(Component comp) throws Exception {
super.doAfterCompose(comp);
book = report.getBook();
reportSheet = book.getSheet("ReportSheet");
final Sheet templateSheet = book.getSheet("TemplateSheet");
templateRange = Ranges.range(templateSheet, "template"); //retrieve named range "template"
offset = ((Number)Ranges.range(templateSheet, "offset").getValue()).intValue(); //retrieve offset value between rows
}
//Generate report per the database contents.
public void generate() throws IOException {
//Load data from database
List<ComputerBean> data = DataProvider.query();
final int row = 1;
final int col = 1;
//copy row templates
copyTemplate(row, col, data.size());
//fill data
fillData(data);
//resize Spreadsheet
report.setMaxrows(offset * data.size());
}
//copy Row templates into ReportSheet
private void copyTemplate(int row, int col, int size) {
for(int j = 0; j < size; j++) {
//Copy to new position
templateRange.copy(Ranges.range(reportSheet, row + j * offset, col));
}
}
//fill in data to the Report
private void fillData(List<ComputerBean> data) {
for(int j = 0, sz = data.size(); j < sz; j++) {
final ComputerBean computerBean = data.get(j);
//fill in data into cells
Ranges.range(reportSheet, 1+j*offset, 1).setValue(Integer.valueOf(j+1)); //Sequence Number
Ranges.range(reportSheet, 2+j*offset, 2).setValue(computerBean.getId());
Ranges.range(reportSheet, 3+j*offset, 2).setValue(computerBean.getModel());
Ranges.range(reportSheet, 4+j*offset, 2).setValue(computerBean.getWarrantyTime());
Ranges.range(reportSheet, 5+j*offset, 2).setValue(Double.valueOf(computerBean.getSalvage()));
Ranges.range(reportSheet, 2+j*offset, 4).setValue(computerBean.getProduct());
Ranges.range(reportSheet, 3+j*offset, 4).setValue(computerBean.getSerialNumber());
Ranges.range(reportSheet, 4+j*offset, 4).setValue(computerBean.getOs());
Ranges.range(reportSheet, 2+j*offset, 6).setValue(computerBean.getBrand());
Ranges.range(reportSheet, 3+j*offset, 6).setValue(computerBean.getDate());
Ranges.range(reportSheet, 4+j*offset, 6).setValue(Double.valueOf(computerBean.getCost()));
}
}
}
Version History
Version | Date | Content |
---|---|---|
All source code listed in this book is at Github.