Export"

From Documentation
m
 
(10 intermediate revisions by 4 users not shown)
Line 3: Line 3:
 
You can export the computed result of Pivottable to other formats.
 
You can export the computed result of Pivottable to other formats.
  
==Export to CSV==
+
=Export to Microsoft Excel=
  
==Export to Microsoft Excel==
+
Given a Pivottable instance, you can dump the current result in .xls format to an OutputStream. The exported Excel file contains a sheet that reflects the computed result you see on browser windows, without paging. For example,
 +
 
 +
==XLS Format==
 +
<source lang="java">
 +
ByteArrayOutputStream out = new ByteArrayOutputStream();
 +
PivotExportContext context = Exports.getExportContext(pivot, false, null);
 +
Exports.exportExcel(out, "xls", context, null); // writes Pivottable information to the output stream
 +
Filedownload.save(out.toByteArray(), "application/vnd.ms-excel", "pivot.xls"); // file download
 +
try {
 +
out.close();
 +
} catch (IOException e) {}
 +
</source>
 +
 
 +
== XLSX format ==
 +
It needs extra jar files including
 +
* dom4j-1.6.1.jar
 +
* ooxml-schemas-1.1.jar
 +
* xmlbeans-2.3.0.jar
 +
 
 +
<source lang="java">
 +
ByteArrayOutputStream out = new ByteArrayOutputStream();
 +
PivotExportContext context = Exports.getExportContext(pivot, false, null);
 +
Exports.exportExcel(out, "xlsx", context, null); // writes Pivottable information to the output stream
 +
Filedownload.save(out.toByteArray(), "application/vnd.ms-excel", "pivot.xlsx"); // file download
 +
try {
 +
out.close();
 +
} catch (IOException e) {}
 +
</source>
 +
 
 +
== Change Styles==
 +
The export utility provides a chance to handle Excel style via POI API. You can specify custom styles by passing a <javadoc directory="zkpvt">org.zkoss.pivot.util.poi.CellStyleConfigurator</javadoc>. For example,
 +
 
 +
<source lang="java">
 +
Exports.exportExcel(out, "xls", context, new CellStyleConfigurator() {
 +
public void config(PivotExportCell.Type type, Cell cell, StyleFactory styleFactory) {
 +
switch (type) {
 +
// you can specify style by cell type, use StyleFactory to create new style
 +
}
 +
}
 +
 
 +
public String getDateFormat(String field) {
 +
//specify date format by filed
 +
return null;
 +
}
 +
});
 +
</source>
 +
 
 +
=PivotExportContext=
 +
 
 +
PivotExportContext is an intermediate result for exporting Pivottable to a table/sheet data structure. You can construct it from a Pivottable, or from a PivotModel and a PivotRenderer (so you don't need a component instance).
 +
 
 +
<source lang="java">
 +
// construct from a Pivottable
 +
PivotExportContext context = Exports.getExportContext(pivot, open, titles);
 +
 
 +
// construct from a PivotModel and a PivotRenderer
 +
// in this case, you also have to specify the data field orientation ("column" or "row")
 +
PivotExportContext context = Exports.getExportContext(model, renderer, "column", open, titles);
 +
</source>
 +
 
 +
There are two additional parameters in the API:
 +
*boolean open: Expand all header tree nodes for the export, but the model itself it not changed.
 +
*String[] titles: The text in the title cells (data title, column title, row title, respectively). See [[ZK_Pivottable_Essentials/Quick_Start/Concept#Trivia | title cells definition]].
 +
 
 +
&nbsp;
 +
 
 +
=Export to CSV=
 +
 
 +
You can also export to CSV format in a similar manner. For example,
 +
 
 +
<source lang="java">
 +
ByteArrayOutputStream out = new ByteArrayOutputStream();
 +
PivotExportContext context = Exports.getExportContext(pivot, true, TITLES);
 +
Exports.exportCSV(out, context);
 +
Filedownload.save(out.toByteArray(), "text/csv", "pivot.csv");
 +
try {
 +
out.close();
 +
} catch (IOException e) {}
 +
</source>
 +
 
 +
&nbsp;
  
 
==Version History==
 
==Version History==
Line 11: Line 91:
 
{| border='1px' | width="100%"
 
{| border='1px' | width="100%"
 
! Version !! Date !! Content
 
! Version !! Date !! Content
 +
|-
 +
| 2.0.0
 +
| May, 2012
 +
| Support Microsoft Excel xlsx format
 
|-
 
|-
 
| &nbsp;
 
| &nbsp;

Latest revision as of 09:35, 17 July 2017


You can export the computed result of Pivottable to other formats.

Export to Microsoft Excel

Given a Pivottable instance, you can dump the current result in .xls format to an OutputStream. The exported Excel file contains a sheet that reflects the computed result you see on browser windows, without paging. For example,

XLS Format

ByteArrayOutputStream out = new ByteArrayOutputStream();
PivotExportContext context = Exports.getExportContext(pivot, false, null);
Exports.exportExcel(out, "xls", context, null); // writes Pivottable information to the output stream
Filedownload.save(out.toByteArray(), "application/vnd.ms-excel", "pivot.xls"); // file download
try {
	out.close();
} catch (IOException e) {}

XLSX format

It needs extra jar files including

  • dom4j-1.6.1.jar
  • ooxml-schemas-1.1.jar
  • xmlbeans-2.3.0.jar
ByteArrayOutputStream out = new ByteArrayOutputStream();
PivotExportContext context = Exports.getExportContext(pivot, false, null);
Exports.exportExcel(out, "xlsx", context, null); // writes Pivottable information to the output stream
Filedownload.save(out.toByteArray(), "application/vnd.ms-excel", "pivot.xlsx"); // file download
try {
	out.close();
} catch (IOException e) {}

Change Styles

The export utility provides a chance to handle Excel style via POI API. You can specify custom styles by passing a CellStyleConfigurator. For example,

Exports.exportExcel(out, "xls", context, new CellStyleConfigurator() {
	public void config(PivotExportCell.Type type, Cell cell, StyleFactory styleFactory) {
		switch (type) {
			// you can specify style by cell type, use StyleFactory to create new style
		}
	}

	public String getDateFormat(String field) {
		//specify date format by filed
		return null;
	}
});

PivotExportContext

PivotExportContext is an intermediate result for exporting Pivottable to a table/sheet data structure. You can construct it from a Pivottable, or from a PivotModel and a PivotRenderer (so you don't need a component instance).

// construct from a Pivottable
PivotExportContext context = Exports.getExportContext(pivot, open, titles); 

// construct from a PivotModel and a PivotRenderer
// in this case, you also have to specify the data field orientation ("column" or "row")
PivotExportContext context = Exports.getExportContext(model, renderer, "column", open, titles);

There are two additional parameters in the API:

  • boolean open: Expand all header tree nodes for the export, but the model itself it not changed.
  • String[] titles: The text in the title cells (data title, column title, row title, respectively). See title cells definition.

 

Export to CSV

You can also export to CSV format in a similar manner. For example,

ByteArrayOutputStream out = new ByteArrayOutputStream();
PivotExportContext context = Exports.getExportContext(pivot, true, TITLES);
Exports.exportCSV(out, context);
Filedownload.save(out.toByteArray(), "text/csv", "pivot.csv");
try {
	out.close();
} catch (IOException e) {}

 

Version History

Last Update : 2017/07/17


Version Date Content
2.0.0 May, 2012 Support Microsoft Excel xlsx format
     



Last Update : 2017/07/17

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