Build a Rich Excel Report with ZK Spreadsheet and Database"

From Documentation
m (correct highlight (via JWB))
 
(21 intermediate revisions by 3 users not shown)
Line 1: Line 1:
 
{{ZKSpreadsheetEssentialsPageHeader}}
 
{{ZKSpreadsheetEssentialsPageHeader}}
 +
 +
 +
{{Deprecated|url=http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials}}
 +
 +
  
 
__TOC__
 
__TOC__
Line 5: Line 10:
 
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.
 
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==
+
===Purpose===
 
Combine ZK Spreadsheet with a back end database to generate a rich report.
 
Combine ZK Spreadsheet with a back end database to generate a rich report.
  
==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>.
+
Design an Excel template file with proper formulas and styles for '''header''' in <code>ReportSheet</code> and '''row''' in <code>TemplateSheet</code>.
 
 
<tt>ReportSheet</tt>
 
  
[[genreport-report.png]]
+
<code>''ReportSheet''</code>
  
<tt>TemplateSheet</tt>
+
[[Image:genreport-report.png]]
  
[[genreport-template.png]]
+
<code>''TemplateSheet''</code>
  
==Generate Report==
+
[[Image:genreport-template.png]]
Use <tt>ReportSheet</tt> as Report showing page, and <tt>TemplateSheet</tt> as Row template sheet plus some control variables.  
 
  
 +
===Generate Report===
 +
Use <code>ReportSheet</code> as showing page, and <code>TemplateSheet</code> as Row template sheet plus some control variables.
  
The operation is straightforward:
 
  
 +
The operation is straightforward,  when end user presses <code>''Generate the report''</code> button:
 
* Load data list from the back-end database.
 
* Load data list from the back-end database.
 
* Per the number of the retrieved data, copy '''row''' template from TemplateSheet to ReportSheet.
 
* Per the number of the retrieved data, copy '''row''' template from TemplateSheet to ReportSheet.
Line 30: Line 34:
 
* There you go!
 
* There you go!
  
[[genreport-copy.png]]
+
[[Image:genreport-copy.png]]
  
===ZUML Example===
+
===ZUML===
<source lang="xml">
+
Here prepare a button that will trigger the report generation.
<window id="reportwin" apply="demo.genreport.ReportComposer" vflex="1">
+
<source lang="xml" highlight="2">
 +
<window id="reportwin" apply="org.zkoss.zssessentials.report.ReportComposer" vflex="1">
 
   <button label="Generate the report" onClick="reportwin$composer.generate();"/>
 
   <button label="Generate the report" onClick="reportwin$composer.generate();"/>
   <spreadsheet id="report" src="/genreport.xls" maxrows="40" maxcolumns="8" vflex="1" width="100%"/>
+
   <spreadsheet id="report" src="/WEB-INF/excel/report/genreport.xls" maxrows="40" maxcolumns="8" vflex="1" width="100%"/>
 
</window>
 
</window>
 
</source>
 
</source>
  
===Report Composer===
+
===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, <tt>copy()</tt> and <tt>setValue()</tt> is used to copy template and fill in data, respectively. Note that you can easily locate a Range by giving <tt>Ranges#range()</tt> the proper ''Defined Names''.
+
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, <code>copy()</code> and <code>setValue()</code> is used to copy template and fill in data, respectively. Note that you can easily locate a Range by giving <code>Ranges#range()</code> the proper ''Defined Names''.
<source lang="java" high="14,15,40,50">
+
<source lang="java" highlight="14,15,19,40,50">
 
public class ReportComposer extends GenericForwardComposer {
 
public class ReportComposer extends GenericForwardComposer {
 
private Spreadsheet report;
 
private Spreadsheet report;
 
private Book book;
 
private Book book;
private Sheet reportSheet;
+
private Worksheet reportSheet;
 
private int offset; //offset to next row
 
private int offset; //offset to next row
 
private Range templateRange;
 
private Range templateRange;
Line 54: Line 59:
 
super.doAfterCompose(comp);
 
super.doAfterCompose(comp);
 
book = report.getBook();
 
book = report.getBook();
reportSheet = book.getSheet("ReportSheet");
+
reportSheet = book.getWorksheet("ReportSheet");
final Sheet templateSheet = book.getSheet("TemplateSheet");
+
final Worksheet templateSheet = book.getWorksheet("TemplateSheet");
 
templateRange = Ranges.range(templateSheet, "template"); //retrieve named range "template"
 
templateRange = Ranges.range(templateSheet, "template"); //retrieve named range "template"
 
offset = ((Number)Ranges.range(templateSheet, "offset").getValue()).intValue(); //retrieve offset value between rows
 
offset = ((Number)Ranges.range(templateSheet, "offset").getValue()).intValue(); //retrieve offset value between rows
Line 107: Line 112:
 
}
 
}
 
</source>
 
</source>
 +
 +
===Result===
 +
[[Image:genreport-result.png]]
 +
 +
View complete source code of ZUML [http://code.google.com/p/zkbooks/source/browse/trunk/zssessentials/examples/WebContent/report/genreport.zul genreport.zul]
 +
 +
View complete source code of Composer [http://code.google.com/p/zkbooks/source/browse/trunk/zssessentials/examples/src/org/zkoss/zssessentials/report/ReportComposer.java ReportComposer.java]
  
 
=Version History=
 
=Version History=

Latest revision as of 12:54, 19 January 2022


DocumentationZK Spreadsheet EssentialsWorking with ZK SpreadsheetBuild a Rich Excel Report with ZK Spreadsheet and Database
Build a Rich Excel Report with ZK Spreadsheet and Database




Stop.png This article is out of date, please refer to http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials for more up to date information.


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

Genreport-report.png

TemplateSheet

Genreport-template.png

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!

Genreport-copy.png

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 Worksheet 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.getWorksheet("ReportSheet");
		final Worksheet templateSheet = book.getWorksheet("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

Genreport-result.png

View complete source code of ZUML genreport.zul

View complete source code of Composer ReportComposer.java

Version History

Last Update : 2022/01/19


Version Date Content
     


All source code listed in this book is at Github.


Last Update : 2022/01/19

Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License.