Generate Huge Data Report in a Second

Ivan Cheng, Engineer, Potix Corporation
May 20, 2008

Version

Applicable to ZK 3.0.5 and later

Applicable to ZK Spreadsheet 1.0.0 beta2 and later

Live Demo

Introduction

In this article, we will demonstrate how to generate 500 data report with ZK Spreadsheet. You can copy a template for many times dynamically and set the value in each cell. When you need to display huge data and each with same format, ZK Spreadsheet can help you to generate it easily. All you have to do is to specify the positions.

If you'd like to know more about ZK Spreadsheet, please refer to the following URLs:

• New Features of ZK Spreadsheet 0.7.0
• A Preview of ZK Spreadsheet Component
• A Sip of the ZK Spreadsheet Component
• How to Build a Rich Excel Report with ZK Spreadsheet
• Use JavaBean in Excel with ZK Spreadsheet
• Change the Style of Spreadsheet within 1 Min
• Integrate ZK Spreadsheet with Spring

Let's start to build an application for generating a report.

Build a Template Using Excel

Design a Template in Excel

Design a template, input the function, and define the name range.

report.xls

Assign names for the sheet ("Report" and "Template").

Display Data

sheet.zul

<window>
	<button label="Show Employee Information" onClick="listData();"/>
	<spreadsheet id="report" url="/report.xls" maxrow="40" maxcolumn="20" height="600px" width="1000px"/>
	<zscript>
		import model.Database;
		import model.Utility;

		void listData() {
		
			//Initial data
			List data = new Database().data;
			
			Utility util = new Utility(report.book);
			
			//Call the method to copy template
			util.copyTemplate(1, 6, 1, 5, 6, data.size());
			
			//Call the method to fill data
			util.fillData(data);
			
			//Resize Spreadsheet
			report.maxrow = 6 * data.size();
		}
	</zscript>
</window>

Utility.java

  

   

   

    
import java.util.List;

import org.zkoss.zss.model.Book;

import org.zkoss.zss.model.Range;

import org.zkoss.zss.model.Sheet;

import org.zkoss.zss.model.impl.RangeSimple;



public class Utility {

  private Book book;

  private Sheet sheet;

  

  public Utility(Sheet sheet) {

    this.book = book;

    this.book = sheet.lookupSheet("Report");

  }

  

  public void copyTemplate(int left, int right, int top, int bottom, int space, int length) {

    //Get the template by NameRange

    Range range = book.lookupNameRange("Template");

    for(int i = 0; i < length; i++) {

      //Copy to new position

      range.copy(new RangeSimple(sheet, null, left, top + i * space, right, bottom + i * space));

    }

  }

  

  public void fillData(List data) {

    for(int i = 0; i < data.size(); i++) {

      ComputerBean computerBean = (ComputerBean)data.get(i);

      

      //Set values of new cells

      ......


      sheet.setCellValue(2+i*6, 2, computerBean.getId());


      ......


    }

  }

}
    
   
   

Download

ZssReport.war

Conclusion

It becomes more convenient to generate a report with ZK Spreadsheet. Copy the template and set the value is just the beginning. In the near future, we will introduce some practical functions of ZK Spreadsheet. We expect your response to make it better, feel free to leave a message or post it to ZK forum .