ZK Spreadsheet Essentials/Working with ZK Spreadsheet/Build a Rich Excel Report with ZK Spreadsheet and Database

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

Design an Excel template file with proper formulas and styles for header in ReportSheet and row in TemplateSheet.

ReportSheet

TemplateSheet

Generate Report

Use ReportSheet as showing page, and TemplateSheet as Row template sheet plus some control variables.

The operation is straightforward, when end user presses Generate the report button:

• 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

Here prepare a button that will trigger the report generation.

<window id="reportwin" apply="org.zkoss.zssessentials.report.ReportComposer" vflex="1">
  <button label="Generate the report" onClick="reportwin$composer.generate();"/>
  <spreadsheet id="report" src="/WEB-INF/excel/report/genreport.xls" maxrows="40" maxcolumns="8" vflex="1" width="100%"/>
</window>

Composer

It provides more programming flexibility to use Excel Defined Names to keep information in templateSheet. 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()));
		}
	}
}

Result

Version History

Version	Date	Content

All source code listed in this book is at Github.