You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2008/12/07 15:10:24 UTC
svn commit: r724135 -
/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java
Author: yegor
Date: Sun Dec 7 06:10:23 2008
New Revision: 724135
URL: http://svn.apache.org/viewvc?rev=724135&view=rev
Log:
example demonstrating how to generate large workbooks and avoid OutOfMemory exception
Added:
poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java (with props)
Added: poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java?rev=724135&view=auto
==============================================================================
--- poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java (added)
+++ poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java Sun Dec 7 06:10:23 2008
@@ -0,0 +1,252 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+package org.apache.poi.xssf.usermodel.examples;
+
+import org.apache.poi.xssf.usermodel.*;
+import org.apache.poi.ss.util.CellReference;
+import org.apache.poi.ss.usermodel.IndexedColors;
+import org.apache.poi.ss.usermodel.DateUtil;
+
+import java.io.*;
+import java.util.zip.ZipFile;
+import java.util.zip.ZipEntry;
+import java.util.zip.ZipOutputStream;
+import java.util.*;
+
+/**
+ * Demonstrates a workaround you can use to generate large workbooks and avoid OutOfMemory exception.
+ *
+ * The trick is as follows:
+ * 1. create a template workbook, create sheets and global objects such as cell styles, number formats, etc.
+ * 2. create an application that streams data in a text file
+ * 3. Substitute the sheet in the template with the generated data
+ *
+ * @author Yegor Kozlov
+ */
+public class BigGridDemo {
+ public static void main(String[] args) throws Exception {
+
+ // Step 1. Create a template file. Setup sheets and workbook-level objects such as
+ // cell styles, number formats, etc.
+
+ XSSFWorkbook wb = new XSSFWorkbook();
+ XSSFSheet sheet = wb.createSheet("Big Grid");
+
+ Map<String, XSSFCellStyle> styles = createStyles(wb);
+ //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml
+ String sheetRef = sheet.getPackagePart().getPartName().getName();
+
+ //save the template
+ FileOutputStream os = new FileOutputStream("template.xlsx");
+ wb.write(os);
+ os.close();
+
+ //Step 2. Generate XML file.
+ File tmp = File.createTempFile("sheet", ".xml");
+ Writer fw = new FileWriter(tmp);
+ generate(fw, styles);
+ fw.close();
+
+ //Step 3. Substitute the template entry with the generated data
+ FileOutputStream out = new FileOutputStream("big-grid.xlsx");
+ substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out);
+ out.close();
+ }
+
+ /**
+ * Create a library of cell styles.
+ */
+ private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
+ Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
+ XSSFDataFormat fmt = wb.createDataFormat();
+
+ XSSFCellStyle style1 = wb.createCellStyle();
+ style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
+ style1.setDataFormat(fmt.getFormat("0.0%"));
+ styles.put("percent", style1);
+
+ XSSFCellStyle style2 = wb.createCellStyle();
+ style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
+ style2.setDataFormat(fmt.getFormat("0.0X"));
+ styles.put("coeff", style2);
+
+ XSSFCellStyle style3 = wb.createCellStyle();
+ style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
+ style3.setDataFormat(fmt.getFormat("$#,##0.00"));
+ styles.put("currency", style3);
+
+ XSSFCellStyle style4 = wb.createCellStyle();
+ style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
+ style4.setDataFormat(fmt.getFormat("mmm dd"));
+ styles.put("date", style4);
+
+ XSSFCellStyle style5 = wb.createCellStyle();
+ XSSFFont headerFont = wb.createFont();
+ headerFont.setBold(true);
+ style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
+ style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
+ style5.setFont(headerFont);
+ styles.put("header", style5);
+
+ return styles;
+ }
+
+ private static void generate(Writer out, Map<String, XSSFCellStyle> styles) throws Exception {
+
+ Random rnd = new Random();
+ Calendar calendar = Calendar.getInstance();
+
+ SpreadsheetWriter sw = new SpreadsheetWriter(out);
+ sw.beginSheet();
+
+ //insert header row
+ sw.insertRow(0);
+ int styleIndex = styles.get("header").getIndex();
+ sw.createCell(0, "Title", styleIndex);
+ sw.createCell(1, "% Change", styleIndex);
+ sw.createCell(2, "Ratio", styleIndex);
+ sw.createCell(3, "Expenses", styleIndex);
+ sw.createCell(4, "Date", styleIndex);
+
+ sw.endRow();
+
+ //write data rows
+ for (int rownum = 1; rownum < 100000; rownum++) {
+ sw.insertRow(rownum);
+
+ sw.createCell(0, "Hello, " + rownum + "!");
+ sw.createCell(1, (double)rnd.nextInt(100)/100, styles.get("percent").getIndex());
+ sw.createCell(2, (double)rnd.nextInt(10)/10, styles.get("coeff").getIndex());
+ sw.createCell(3, rnd.nextInt(10000), styles.get("currency").getIndex());
+ sw.createCell(4, calendar, styles.get("date").getIndex());
+
+ sw.endRow();
+
+ calendar.roll(Calendar.DAY_OF_YEAR, 1);
+ }
+ sw.endSheet();
+ }
+
+ /**
+ *
+ * @param zipfile the template file
+ * @param tmpfile the XML file with the sheet data
+ * @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml
+ * @param out the stream to write the result to
+ */
+ private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException {
+ ZipFile zip = new ZipFile(zipfile);
+
+ ZipOutputStream zos = new ZipOutputStream(out);
+
+ for (Enumeration en = zip.entries(); en.hasMoreElements(); ) {
+ ZipEntry ze = (ZipEntry)en.nextElement();
+ if(!ze.getName().equals(entry)){
+ zos.putNextEntry(new ZipEntry(ze.getName()));
+ InputStream is = zip.getInputStream(ze);
+ copyStream(is, zos);
+ is.close();
+ }
+ }
+ zos.putNextEntry(new ZipEntry(entry));
+ InputStream is = new FileInputStream(tmpfile);
+ copyStream(is, zos);
+ is.close();
+
+ zos.close();
+ }
+
+ private static void copyStream(InputStream in, OutputStream out) throws IOException {
+ byte[] chunk = new byte[1024];
+ int count;
+ while ((count = in.read(chunk)) >=0 ) {
+ out.write(chunk,0,count);
+ }
+ }
+
+ /**
+ * Writes spreadsheet data in a Writer.
+ * (YK: in future it may evolve in a full-featured API for streaming data in Excel)
+ */
+ public static class SpreadsheetWriter {
+ private Writer out;
+ private int rownum;
+
+ public SpreadsheetWriter(Writer out){
+ this.out = out;
+ }
+
+ public void beginSheet() throws IOException {
+ out.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
+ "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
+ out.write("<sheetData>\n");
+ }
+
+ public void endSheet() throws IOException {
+ out.write("</sheetData>");
+ out.write("</worksheet>");
+ }
+
+ /**
+ * Insert a new row
+ *
+ * @param rownum 0-based row number
+ */
+ public void insertRow(int rownum) throws IOException {
+ out.write("<row r=\""+(rownum+1)+"\">\n");
+ this.rownum = rownum;
+ }
+
+ /**
+ * Insert row end marker
+ */
+ public void endRow() throws IOException {
+ out.write("</row>\n");
+ }
+
+ public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
+ String ref = new CellReference(rownum, columnIndex).formatAsString();
+ out.write("<c r=\""+ref+"\" t=\"inlineStr\"");
+ if(styleIndex != -1) out.write(" s=\""+styleIndex+"\"");
+ out.write(">");
+ out.write("<is><t>"+value+"</t></is>");
+ out.write("</c>");
+ }
+
+ public void createCell(int columnIndex, String value) throws IOException {
+ createCell(columnIndex, value, -1);
+ }
+
+ public void createCell(int columnIndex, double value, int styleIndex) throws IOException {
+ String ref = new CellReference(rownum, columnIndex).formatAsString();
+ out.write("<c r=\""+ref+"\" t=\"n\"");
+ if(styleIndex != -1) out.write(" s=\""+styleIndex+"\"");
+ out.write(">");
+ out.write("<v>"+value+"</v>");
+ out.write("</c>");
+ }
+
+ public void createCell(int columnIndex, double value) throws IOException {
+ createCell(columnIndex, value, -1);
+ }
+
+ public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException {
+ createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);
+ }
+ }
+
+}
Propchange: poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java
------------------------------------------------------------------------------
svn:executable = *
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org