Export Grid or Listbox to PDF or Excel"

From Documentation
m (correct highlight (via JWB))
 
(19 intermediate revisions by 3 users not shown)
Line 1: Line 1:
Export Grid or Listbox to PDF or Excel
 
 
{{Template:UnderConstruction}}
 
 
{{Template:Smalltalk_Author|
 
{{Template:Smalltalk_Author|
 
|author=Sam Chuang, Engineer, Potix Corporation
 
|author=Sam Chuang, Engineer, Potix Corporation
|date=November 26, 2012
+
|date=December 07, 2012
 
|version=ZK 6.0 and later
 
|version=ZK 6.0 and later
 
}}
 
}}
  
 
== Introduction ==
 
== Introduction ==
Web application developers often require to transform data into different format. For instance, to PDF format that enhance read accessibility and to Excel format that can further analyze data. This smalltalk is going to introduce a easy way that can transform ZK Grid or Listbox to PDF/Excel.
+
 
 +
Generally, when a developer wants to add a format converting function to an application, if often requires intense and complex coding. For instance, to create a function that enables the exporting of data to PDF format in order to enhance read accessibility or to an Excel file to further analyze data would require much effort and time.  
 +
 
 +
This small talk is going to introduce an easy way where you can use a ready-to-use library to create such function; transforming ZK Grid or Listbox to PDF/Excel by leveraging the PdfExporter and ExcelExporter utilities.
  
 
==Demo==
 
==Demo==
Line 25: Line 25:
  
 
==Usage==
 
==Usage==
===Export Listbox/Grid===
+
===Export Listbox/Grid with PdfExporter and ExcelExporter===
As you can see the following sample code, export Listbox/Grid to PDF or Excel is quite straightforward.
+
 
 +
I have created two utilities, the <b>PdfExporter</b> and <b>ExcelExporter</b>. As you can see in the following sample code, exporting Listbox/Grid to PDF or Excel with these utilities is very straightforward. The resulting outputs are demonstrated as the screenshots in the previous section.
  
 
====PDF====
 
====PDF====
<source lang="java" high="5,6">
+
<source lang="java" highlight="5,6">
 
@Command
 
@Command
 
public void exportGrid(@BindingParam("ref") Grid grid) throws Exception {
 
public void exportGrid(@BindingParam("ref") Grid grid) throws Exception {
Line 45: Line 46:
 
====Excel====
 
====Excel====
  
<source lang="java"  high="5,6">
+
<source lang="java"  highlight="5,6">
 
@Command
 
@Command
 
public void exportListboxToExcel(@BindingParam("ref") Listbox listbox) throws Exception {
 
public void exportListboxToExcel(@BindingParam("ref") Listbox listbox) throws Exception {
Line 60: Line 61:
  
 
===Export data by Renderer===
 
===Export data by Renderer===
Although export Listbox/Grid to PDF or Excel is quite straightforward. However, it does have some limitation. For instance, it cannot export full contents with Grid/Listbox with ROD enabled. In such use case, you can use renderer to render raw data to PDF/Excel directly.
+
Although export Listbox/Grid to PDF or Excel with PdfExporter and ExcelExporter is quite straightforward. However, it does have some limitation. For instance, it cannot export full contents with Grid/Listbox with ROD (Render on Demand) enabled. If you need to export full data when ROD enabled, or if you wish to go further and customize the rendering result, you can use renderer to render raw data to PDF/Excel directly. In the following subsections I will introduce how you can use renderer to export files.
Another advantage of renderer is that you can highly customize the rendering result.
 
  
 
====PDF====
 
====PDF====
The PdfExporter leverage the famous iText library to create PDF documents. Hence, the renderer will use iText API directly.
+
<b>PdfExporter</b> leverages iText library to create PDF documents. Hence, the renderer will use iText API directly.
  
  
The PdfExporter contains Factories that help create necessary instances that iText needed. <br/>
+
<b>PdfExporter</b> contains Factories that help create necessary instances which iText needs. <br/>
1. DocumentFactory to create Document  <br/>
+
1. DocumentFactory: to create Document  <br/>
2. PdfWriterFactory to get PdfWriter instance  <br/>
+
2. PdfWriterFactory: to get PdfWriter instance  <br/>
3. PdfPTableFactory to get PdfPTable instance  <br/>
+
3. PdfPTableFactory: to get PdfPTable instance  <br/>
4. FontFactory to get Font  <br/>
+
4. FontFactory: to get Font  <br/>
5. PdfPCellFactory to create PdfPCell  <br/>
+
5. PdfPCellFactory: to create PdfPCell  <br/>
  
The following sample code will demonstrate the usage of FontFactory and PdfPCellFactory  
+
The following sample code will demonstrate the usage of <b>FontFactory</b> and <b>PdfPCellFactory</b>
  
*Render header using Interceptor.beforeRendering API
+
*Render header using Interceptor<b>.beforeRendering</b> API
<source lang="java" high="14,15,17,19,21">
+
<source lang="java" highlight="14,15,17,19,21">
 
final PdfExporter exporter = new PdfExporter();
 
final PdfExporter exporter = new PdfExporter();
 
final PdfPCellFactory cellFactory = exporter.getPdfPCellFactory();
 
final PdfPCellFactory cellFactory = exporter.getPdfPCellFactory();
Line 173: Line 173:
  
 
====Excel====
 
====Excel====
The ExcelExporter is based on Apache POI, the renderer will use Apache POI API directly to create excel document. Refer to the [http://poi.apache.org/spreadsheet/quick-guide.html quick guide] here to get started with POI.
+
<b>ExcelExporter</b> is based on Apache POI, the renderer will use Apache POI API directly to create excel documents. Refer to the [http://poi.apache.org/spreadsheet/quick-guide.html quick guide] here to get started with POI.
  
*Render header using Interceptor.beforeRendering API
+
*Render header using Interceptor<b>.beforeRendering</b> API
<source lang="java" high="7,10,11,16,17,18,19">
+
<source lang="java" highlight="7,10,11,16,17,18,19">
 
final ExcelExporter exporter = new ExcelExporter();
 
final ExcelExporter exporter = new ExcelExporter();
 
final String[] headers = new String[]{"Name", "Top Nutrients", "% of Daily", "Calories", "Quantity"};
 
final String[] headers = new String[]{"Name", "Top Nutrients", "% of Daily", "Calories", "Quantity"};
Line 207: Line 207:
 
</source>
 
</source>
  
*Render contents using RowRenderer or GroupRenderer
+
*Render contents using <b>RowRenderer</b> or <b>GroupRenderer</b>
 
<source lang="java" >
 
<source lang="java" >
 
exporter.export(headers.length, _model.getData(), new org.zkoss.exporter.GroupRenderer<Row, Food>() {
 
exporter.export(headers.length, _model.getData(), new org.zkoss.exporter.GroupRenderer<Row, Food>() {
Line 266: Line 266:
  
 
==Summary==
 
==Summary==
In this smalltalk I demonstrated how application developers can easily export PDF or Excel report from Grid, Listbox or even from raw data directly. Explore the [https://github.com/samchuang/exporter project] and enjoy it.
+
In this smalltalk I have demonstrated how application developers can easily set up a function to export PDF or Excel report from Grid, Listbox or even from raw data directly. Explore the [https://github.com/samchuang/exporter project] and enjoy it.
  
 
==Download==
 
==Download==
 
*The demo web application can be downloaded here - [https://github.com/downloads/samchuang/exporter/exporter.war Github]
 
*The demo web application can be downloaded here - [https://github.com/downloads/samchuang/exporter/exporter.war Github]
 
*The exporter.jar can be downloaded here- [https://github.com/downloads/samchuang/exporter/exporter.jar Github]
 
*The exporter.jar can be downloaded here- [https://github.com/downloads/samchuang/exporter/exporter.jar Github]
 +
 +
Note that the purpose of this article is to demonstrate on how export could be done. 3rd party libraries being used in the small talk are licensed under their own licensing terms.
  
 
{{Template:CommentedSmalltalk_Footer|
 
{{Template:CommentedSmalltalk_Footer|
 
|name=Potix Corporation
 
|name=Potix Corporation
 
}}
 
}}

Latest revision as of 04:19, 20 January 2022

DocumentationSmall Talks2012DecemberExport Grid or Listbox to PDF or Excel
Export Grid or Listbox to PDF or Excel

Author
Sam Chuang, Engineer, Potix Corporation
Date
December 07, 2012
Version
ZK 6.0 and later

Introduction

Generally, when a developer wants to add a format converting function to an application, if often requires intense and complex coding. For instance, to create a function that enables the exporting of data to PDF format in order to enhance read accessibility or to an Excel file to further analyze data would require much effort and time.

This small talk is going to introduce an easy way where you can use a ready-to-use library to create such function; transforming ZK Grid or Listbox to PDF/Excel by leveraging the PdfExporter and ExcelExporter utilities.

Demo

Let's first take a look at the source and the resulting output before diving into the codes.

The Grid/Listbox below is the one we are exporting to PDF and Excel
Export Grid or Listbox to PDF or Excel demo.png

Resulting PDF
Export Grid or Listbox to PDF or Excel PDF Format.png

Resulting Excel
Export Grid or Listbox to PDF or Excel Excel Format.png

Usage

Export Listbox/Grid with PdfExporter and ExcelExporter

I have created two utilities, the PdfExporter and ExcelExporter. As you can see in the following sample code, exporting Listbox/Grid to PDF or Excel with these utilities is very straightforward. The resulting outputs are demonstrated as the screenshots in the previous section.

PDF

	@Command
	public void exportGrid(@BindingParam("ref") Grid grid) throws Exception {
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		
		PdfExporter exporter = new PdfExporter();
		exporter.export(grid, out);
		
		AMedia amedia = new AMedia("FirstReport.pdf", "pdf", "application/pdf", out.toByteArray());
		Filedownload.save(amedia);		
		out.close();
	}

Excel

	@Command
	public void exportListboxToExcel(@BindingParam("ref") Listbox listbox) throws Exception {
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		
		ExcelExporter exporter = new ExcelExporter();
		exporter.export(listbox, out);
		
		AMedia amedia = new AMedia("FirstReport.xlsx", "xls", "application/file", out.toByteArray());
		Filedownload.save(amedia);
		out.close();
	}

Export data by Renderer

Although export Listbox/Grid to PDF or Excel with PdfExporter and ExcelExporter is quite straightforward. However, it does have some limitation. For instance, it cannot export full contents with Grid/Listbox with ROD (Render on Demand) enabled. If you need to export full data when ROD enabled, or if you wish to go further and customize the rendering result, you can use renderer to render raw data to PDF/Excel directly. In the following subsections I will introduce how you can use renderer to export files.

PDF

PdfExporter leverages iText library to create PDF documents. Hence, the renderer will use iText API directly.


PdfExporter contains Factories that help create necessary instances which iText needs.
1. DocumentFactory: to create Document
2. PdfWriterFactory: to get PdfWriter instance
3. PdfPTableFactory: to get PdfPTable instance
4. FontFactory: to get Font
5. PdfPCellFactory: to create PdfPCell

The following sample code will demonstrate the usage of FontFactory and PdfPCellFactory

  • Render header using Interceptor.beforeRendering API
		final PdfExporter exporter = new PdfExporter();
		final PdfPCellFactory cellFactory = exporter.getPdfPCellFactory();
		final FontFactory fontFactory = exporter.getFontFactory();
		
		final String[] headers = new String[]{"Name", "Top Nutrients", "% of Daily", "Calories", "Quantity"};
		exporter.setInterceptor(new Interceptor <PdfPTable> () {
			
			@Override
			public void beforeRendering(PdfPTable table) {
				for (int i = 0; i < headers.length; i++) {
					String header = headers[i];
					Font font = fontFactory.getFont(FontFactory.FONT_TYPE_HEADER);
					
					PdfPCell cell = cellFactory.getHeaderCell();
					cell.setPhrase(new Phrase(header, font));
					if ("% of Daily".equals(header) || "Calories".equals(header)) {
						cell.setHorizontalAlignment(Element.ALIGN_CENTER);
					}
					table.addCell(cell);
				}
				table.completeRow();
			}
			
			@Override
			public void afterRendering(PdfPTable table) {
			}
		});
  • Render contents using RowRenderer or GroupRenderer
		exporter.export(headers.length, _model.getData(), new GroupRenderer<PdfPTable, Food>() {

			@Override
			public void render(PdfPTable table, Food food, boolean isOddRow) {
				Font font = fontFactory.getFont(FontFactory.FONT_TYPE_CELL);

				PdfPCell cell = cellFactory.getCell(isOddRow);
				cell.setPhrase(new Phrase(food.getName(), font));
				table.addCell(cell);
				
				cell = cellFactory.getCell(isOddRow);
				cell.setPhrase(new Phrase(food.getTopNutrients(), font));
				table.addCell(cell);
				
				cell = cellFactory.getCell(isOddRow);
				cell.setPhrase(new Phrase("" + food.getDailyPercent(), font));
				cell.setHorizontalAlignment(Element.ALIGN_CENTER);
				table.addCell(cell);
				
				cell = cellFactory.getCell(isOddRow);
				cell.setPhrase(new Phrase("" + food.getCalories(), font));
				cell.setHorizontalAlignment(Element.ALIGN_CENTER);
				table.addCell(cell);
				
				cell = cellFactory.getCell(isOddRow);
				cell.setPhrase(new Phrase(food.getQuantity(), font));
				table.addCell(cell);
				
				table.completeRow();
			}

			@Override
			public void renderGroup(PdfPTable table, Collection<Food> foods) {
				Iterator<Food> iterator = foods.iterator();
				if (iterator.hasNext()) {
					Food food = iterator.next();
					Font font = fontFactory.getFont(FontFactory.FONT_TYPE_GROUP);

					PdfPCell cell = cellFactory.getGroupCell();					
					cell.setPhrase(new Phrase(food.getCategory(), font));
					cell.setColspan(headers.length);
					table.addCell(cell);
					
					table.completeRow();
				}
			}

			@Override
			public void renderGroupfoot(PdfPTable table, Collection<Food> foods) {
				Font font = fontFactory.getFont(FontFactory.FONT_TYPE_GROUPFOOT);

				PdfPCell cell = cellFactory.getGroupCell();
				cell.setPhrase(new Phrase("Total size: " + (foods != null ? foods.size() : 0), font));
				table.addCell(cell);
				
				cell = cellFactory.getCell(false);
				cell.setColspan(headers.length - 1);
				table.addCell(cell);
				table.completeRow();
			}
		}, out);

Excel

ExcelExporter is based on Apache POI, the renderer will use Apache POI API directly to create excel documents. Refer to the quick guide here to get started with POI.

  • Render header using Interceptor.beforeRendering API
		final ExcelExporter exporter = new ExcelExporter();
		final String[] headers = new String[]{"Name", "Top Nutrients", "% of Daily", "Calories", "Quantity"};
		exporter.setInterceptor(new Interceptor<XSSFWorkbook>() {
			
			@Override
			public void beforeRendering(XSSFWorkbook target) {
				ExportContext context = exporter.getExportContext();
				
				for (String header : headers) {
					Cell cell = exporter.getOrCreateCell(context.moveToNextCell(), context.getSheet());
					cell.setCellValue(header);
					
					if ("% of Daily".equals(header) || "Calories".equals(header)) {
						CellStyle srcStyle = cell.getCellStyle();
						if (srcStyle.getAlignment() != CellStyle.ALIGN_CENTER) {
							XSSFCellStyle newCellStyle = book.createCellStyle();
							newCellStyle.cloneStyleFrom(srcStyle);
							newCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
							cell.setCellStyle(newCellStyle);
						}
					}
				}
			}
			
			@Override
			public void afterRendering(XSSFWorkbook target) {
			}
		});
  • Render contents using RowRenderer or GroupRenderer
		exporter.export(headers.length, _model.getData(), new org.zkoss.exporter.GroupRenderer<Row, Food>() {

			@Override
			public void render(Row row, Food food, boolean oddRow) {
				ExportContext context = exporter.getExportContext();
				XSSFSheet sheet = context.getSheet();
				
				exporter
				.getOrCreateCell(context.moveToNextCell(), sheet)
				.setCellValue(food.getName());
				
				exporter
				.getOrCreateCell(context.moveToNextCell(), sheet)
				.setCellValue(food.getTopNutrients());
				
				Cell cell = exporter.getOrCreateCell(context.moveToNextCell(), sheet);
				cell.setCellValue(food.getDailyPercent());
				CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
				cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
				cell.setCellStyle(cellStyle);
				
				cell = exporter.getOrCreateCell(context.moveToNextCell(), sheet);
				cell.setCellValue(food.getCalories());
				cellStyle = sheet.getWorkbook().createCellStyle();
				cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
				cell.setCellStyle(cellStyle);
				
				exporter
				.getOrCreateCell(context.moveToNextCell(), sheet)
				.setCellValue(food.getQuantity());
			}

			@Override
			public void renderGroup(Row row, Collection<Food> foods) {
				ExportContext context = exporter.getExportContext();
				XSSFSheet sheet = context.getSheet();
				
				exporter
				.getOrCreateCell(context.moveToNextCell(), sheet)
				.setCellValue(foods.iterator().next().getCategory());
			}

			@Override
			public void renderGroupfoot(Row row, Collection<Food> foods) {
				ExportContext context = exporter.getExportContext();
				XSSFSheet sheet = context.getSheet();
				
				exporter
				.getOrCreateCell(context.moveToNextCell(), sheet)
				.setCellValue("Total " + foods.size() + " items");
			}
		}, out);

For complete source code, please refer to here

Summary

In this smalltalk I have demonstrated how application developers can easily set up a function to export PDF or Excel report from Grid, Listbox or even from raw data directly. Explore the project and enjoy it.

Download

  • The demo web application can be downloaded here - Github
  • The exporter.jar can be downloaded here- Github

Note that the purpose of this article is to demonstrate on how export could be done. 3rd party libraries being used in the small talk are licensed under their own licensing terms.


Comments



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