ZK MVVM List Box Export to Excel

In this post, we will see how to export records from the DB to Excel format.

ZK Version : ZK 7.0.2
Project Name : zk7example3

Step 1:
Follow this post, to create ZK 7 Maven Project.

Step 2:
Follow this post, to connect MySQL Via Hibernate Spring Integration. After this step, the project structure should be like as follows;



Step 3:
Follow this post, to connect MySQL via Hibernate Spring integration and display the records in ZK List View Component . After this step, the output
will be as follows



Step 4:

Next we will see how to take a list of JAVA beans and generate an Excel spread sheet using the Apache Commons POI library, with help from the
Apache Commons BeanUtils library. First let us create our Columns class which basically defines the properties of the column.

Let us add Apache dependencies in our POM File. Add the following dependencies;

	<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.9</version>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.9</version>
		</dependency>

		<dependency>
			<groupId>commons-beanutils</groupId>
			<artifactId>commons-beanutils</artifactId>
			<version>1.8.3</version>
		</dependency>

In the package zk7example3, create a class called ExcelColumns.java and a enum called FormatType

package zk7example3;

public enum FormatType {
	TEXT, INTEGER, FLOAT, DATE, MONEY, PERCENTAGE
}

ExcelColumns.Java

package zk7example3;

import org.apache.poi.xssf.usermodel.XSSFFont;

public class ExcelColumns {

	private String m_method;
	private String m_header;
	private FormatType m_type;
	private XSSFFont m_font;
	private Short m_color;
	private int m_width;

	public ExcelColumns(String method, String header, FormatType type,
			XSSFFont font, Short color, int width) {
		this.m_method = method;
		this.m_header = header;
		this.m_type = type;
		this.m_font = font;
		this.m_color = color;
		this.m_width = width;
	}

	public ExcelColumns(String method, String header, FormatType type,
			XSSFFont font) {
		this(method, header, type, font, null, 3000);
	}

	public ExcelColumns(String method, String header, FormatType type,
			Short color) {
		this(method, header, type, null, color, 3000);
	}

	public ExcelColumns(String method, String header, FormatType type) {
		this(method, header, type, null, null, 3000);
	}

	public ExcelColumns(String method, String header, FormatType type, int width) {
		this(method, header, type, null, null, width);
	}

	public String getMethod() {
		return m_method;
	}

	public void setMethod(String method) {
		this.m_method = method;
	}

	public String getHeader() {
		return m_header;
	}

	public void setHeader(String header) {
		this.m_header = header;
	}

	public FormatType getType() {
		return m_type;
	}

	public void setType(FormatType type) {
		this.m_type = type;
	}

	public XSSFFont getFont() {
		return m_font;
	}

	public void setFont(XSSFFont m_font) {
		this.m_font = m_font;
	}

	public Short getColor() {
		return m_color;
	}

	public void setColor(Short m_color) {
		this.m_color = m_color;
	}

	public int getWidth() {
		if (this.m_width == 0)
			return 3000;
		else
			return m_width;
	}

	public void setWidth(int m_width) {
		this.m_width = m_width;
	}

}

Step 5:
Next we will create our generic class which converts list of beans to excel. In the package zk7example3, create a class called BeanToExcel

package zk7example3;

import java.io.File;
import java.io.FileOutputStream;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.sql.Date;
import java.util.Calendar;
import java.util.List;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.zkoss.zul.Filedownload;

public class BeanToExcel {

	protected List<?> dataList = null;
	protected List<ExcelColumns> excelColumns = null;
	protected String dataSheetName;
	private XSSFSheet sheet;
	private XSSFWorkbook workbook;

	public List<?> getDataList() {
		return dataList;
	}

	public void setDataList(List<?> dataList) {
		this.dataList = dataList;
	}

	public List<ExcelColumns> getExcelColumns() {
		return excelColumns;
	}

	public void setExcelColumns(List<ExcelColumns> excelColumns) {
		this.excelColumns = excelColumns;
	}

	public String getDataSheetName() {
		return dataSheetName;
	}

	public void setDataSheetName(String dataSheetName) {
		this.dataSheetName = dataSheetName;
	}

	public void exportToExcel() {

		try {

			// Blank workbook
			this.workbook = new XSSFWorkbook();
			// Create a blank sheet
			this.sheet = this.workbook.createSheet(this.dataSheetName);

			int numCols = this.excelColumns.size();
			int currentRow = 0;

			// Create the report header at row 0
			Row excelHeader = sheet.createRow(currentRow);
			Cell dataCell;
			// Loop over all the column beans and populate the report headers
			for (int i = 0; i < numCols; i++) {
				dataCell = excelHeader.createCell(i);
				dataCell.setCellStyle(getHeaderCellStyle(workbook));
				dataCell.setCellValue(excelColumns.get(i).getHeader());
				this.sheet.setColumnWidth(i, excelColumns.get(i).getWidth());
			}

			currentRow++;
			Row dataRow;
			for (int i = 0; i < this.dataList.size(); i++) {
				// create a row in the spreadsheet
				dataRow = sheet.createRow(currentRow++);
				// get the bean for the current row
				// get the bean for the current row
				Object bean = dataList.get(i);
				for (int y = 0; y < numCols; y++) {
					Object value = PropertyUtils.getProperty(bean, excelColumns
							.get(y).getMethod());
					writeCell(dataRow, y, value, excelColumns.get(y).getType(),
							excelColumns.get(y).getColor(), excelColumns.get(y)
									.getFont());
				}
			}

		} catch (IllegalAccessException e) {
			System.out.println(e);
		} catch (InvocationTargetException e) {
			System.out.println(e);
		} catch (NoSuchMethodException e) {
			System.out.println(e);
		}

		Calendar now = Calendar.getInstance();
		int day = now.get(Calendar.DAY_OF_MONTH);
		int hour = now.get(Calendar.HOUR_OF_DAY);
		int minute = now.get(Calendar.MINUTE);
		int second = now.get(Calendar.SECOND);
		String fileName = this.dataSheetName + "_" + day + "_" + hour + "_"
				+ minute + "_" + second + "_";

		try {
			// Write the workbook in file system
			File temp = File.createTempFile(fileName, ".xlsx");
			FileOutputStream out = new FileOutputStream(temp);
			workbook.write(out);
			out.close();
			Filedownload.save(temp, null);
		} catch (Exception e) {
			System.out.println(e);

		}
	}

	private void writeCell(Row dataRow, int col, Object value,
			FormatType formatType, Short bgColor, XSSFFont font) {

		Cell dataCell;
		dataCell = dataRow.createCell(col);
		if (value == null) {
			return;
		}
		switch (formatType) {
		case TEXT:
			dataCell.setCellValue(value.toString());
			break;
		case DATE:
			dataCell.setCellStyle(getDateFormatStyle(this.workbook));
			dataCell.setCellValue((Date) value);
			break;
		case FLOAT:
			break;
		case INTEGER:
			dataCell.setCellStyle(getIntegerCellStyle(this.workbook));
			dataCell.setCellType(Cell.CELL_TYPE_NUMERIC);
			dataCell.setCellValue(((Number) value).intValue());
			break;
		case MONEY:
			dataCell.setCellStyle(getNumberCellStyle(this.workbook));
			dataCell.setCellType(Cell.CELL_TYPE_NUMERIC);
			dataCell.setCellValue(((BigDecimal) value).doubleValue());
			break;
		case PERCENTAGE:
			break;
		default:
			break;
		}

	}

	public static void createExcelHeader(XSSFWorkbook workbook,
			XSSFSheet excelSheet, String titles[]) {

		Row excelHeader = excelSheet.createRow(0);
		Cell dataCell;
		int count = 0;
		for (String caption : titles) {
			dataCell = excelHeader.createCell(count++);
			dataCell.setCellStyle(getHeaderCellStyle(workbook));
			dataCell.setCellValue(caption);
		}
	}

	public static XSSFCellStyle getHeaderCellStyle(XSSFWorkbook workbook) {
		XSSFCellStyle titleStyle = workbook.createCellStyle();

		titleStyle.setFillPattern(XSSFCellStyle.FINE_DOTS);
		titleStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
		titleStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
		titleStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
		titleStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
		titleStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
		titleStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);

		Font headerFont = workbook.createFont();
		headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		headerFont.setFontHeightInPoints((short) 10);
		titleStyle.setFont(headerFont);

		return titleStyle;
	}

	private XSSFCellStyle getDateFormatStyle(XSSFWorkbook workbook) {
		XSSFCellStyle style = workbook.createCellStyle();
		XSSFDataFormat df = workbook.createDataFormat();
		style.setDataFormat(df.getFormat("mm/dd/yyyy"));
		return style;
	}

	private XSSFCellStyle getNumberCellStyle(XSSFWorkbook workbook) {
		XSSFCellStyle style = workbook.createCellStyle();
		XSSFDataFormat format = workbook.createDataFormat();
		style.setDataFormat(format.getFormat("0.00"));
		return style;
	}

	private XSSFCellStyle getIntegerCellStyle(XSSFWorkbook workbook) {
		XSSFCellStyle style = workbook.createCellStyle();
		XSSFDataFormat format = workbook.createDataFormat();
		style.setDataFormat(format.getFormat("#,##0"));
		return style;
	}
}

Step 6:
Next we will see how we can use our generic class to generate  excel from List of beans. In the index.zul , at the top, add button as follows

<?taglib uri="http://www.zkoss.org/dsp/web/core" prefix="c"?>
<zk>
	<style src="/css/style.css" />
	<window apply="org.zkoss.bind.BindComposer"
		viewModel="@id('vm')@init('zk7example3.MyViewModel')">
		<button label="Export to Excel"
			onClick="@command('onExcelExport')">
		</button>
		<listbox selectedItem="@bind(vm.selectedOrder)"
			model="@bind(vm.ordersList)">
			<listhead sizable="true">
				<listheader label="order No" sort="auto(orderNumber)" />
				<listheader label="Customer Name"
					sort="auto(LOWER(customerFname))" />
				<listheader label="Gender" sort="auto(customerGender)" />
				<listheader label="Phone" sort="auto(customerPhone)" />
				<listheader label="Status" sort="auto(status)" />
				<listheader label="Order Total" align="right"
					sort="auto(orderTotal)" />
				<listheader label="Order Date" align="center"
					sort="auto(orderDate)" />
				<listheader label="Delivered" sort="auto(delivered)" />
			</listhead>
			<template name="model" var="p1">
				<listitem>
					<listcell label="@load(p1.orderNumber)" />

					<listcell
						label="@load(vm.getCustomerName(p1.customerFname, p1.customerLname))" />
					<listcell>
						<hbox spacing="20px">
							<image sclass="@bind(p1.customerGender)" />
							<label value="@load(p1.customerGender)" />
						</hbox>
					</listcell>
					<listcell
						label="@load(p1.customerPhone) @converter('zk7example3.MyPhoneConverter')" />
					<listcell label="@load(p1.status)" />
					<listcell
						label="@load(c:formatNumber(p1.orderTotal, '$#,###.00'))" />
					<listcell
						label="@load(p1.orderDate) @converter('formatedDate', format='MM/dd/yyyy')" />
					<listcell
						label="@bind(p1.delivered eq 1?'Yes':'No')" />
				</listitem>
			</template>
		</listbox>
	</window>
</zk>

Now let us add the method “onExcelExport” in our viewmodel.

package zk7example3;

import java.util.ArrayList;
import java.util.List;

import org.zkoss.bind.annotation.Command;
import org.zkoss.bind.annotation.Init;
import org.zkoss.zk.ui.select.annotation.WireVariable;
import org.zkoss.zkplus.spring.SpringUtil;

import com.example.business.service.CRUDService;
import com.example.domain.Orders;

public class MyViewModel {

	@WireVariable
	private CRUDService crudService;
	private List<Orders> ordersList = null;
	private Orders selectedOrder;

	public Orders getSelectedOrder() {
		return selectedOrder;
	}

	public void setSelectedOrder(Orders selectedOrder) {
		this.selectedOrder = selectedOrder;
	}

	public List<Orders> getOrdersList() {
		return ordersList;
	}

	public void setOrdersList(List<Orders> ordersList) {
		this.ordersList = ordersList;
	}

	@Init
	public void init() {
		crudService = (CRUDService) SpringUtil.getBean("CRUDService");
		ordersList = crudService.GetListByNamedQuery("Orders.getAllOrders");
	}

	public String getCustomerName(String firstName, String lastName) {
		return firstName + " " + lastName;
	}

	@Command
	public void onExcelExport() {
		List<ExcelColumns> excelColumns = new ArrayList<ExcelColumns>();
		excelColumns.add(new ExcelColumns("orderNumber", "Order No",
				FormatType.INTEGER));
		excelColumns.add(new ExcelColumns("orderDate", "Order Date",
				FormatType.DATE));

		excelColumns.add(new ExcelColumns("customerFname", "First Name",
				FormatType.TEXT));
		excelColumns.add(new ExcelColumns("customerLname", "Last Name",
				FormatType.TEXT,5000));
		excelColumns.add(new ExcelColumns("customerGender", "Gender",
				FormatType.TEXT));
		excelColumns.add(new ExcelColumns("customerAddress1", "Address1",
				FormatType.TEXT,5000));
		excelColumns.add(new ExcelColumns("customerCity", "City",
				FormatType.TEXT));
		excelColumns.add(new ExcelColumns("customerState", "State",
				FormatType.TEXT,2000));
		excelColumns.add(new ExcelColumns("customerZip", "Zip",
				FormatType.TEXT));
		excelColumns.add(new ExcelColumns("customerPhone", "Phone",
				FormatType.TEXT));
		excelColumns.add(new ExcelColumns("orderTotal", "Order Total",
				FormatType.MONEY));

		BeanToExcel beanToExcel = new BeanToExcel();
		beanToExcel.setExcelColumns(excelColumns);
		beanToExcel.setDataSheetName("orders");
		beanToExcel.setDataList(ordersList);
		beanToExcel.exportToExcel();
	}

}

Thats all. Now you can run and click the button to export the list box records into excel.





You can download the source here.