From 7e6c4c3de8f02a6a2f486fddf8cbb509dbd25c2c Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Sun, 7 Dec 2008 14:10:23 +0000 Subject: [PATCH] example demonstrating how to generate large workbooks and avoid OutOfMemory exception git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@724135 13f79535-47bb-0310-9956-ffa450edef68 --- .../xssf/usermodel/examples/BigGridDemo.java | 252 ++++++++++++++++++ 1 file changed, 252 insertions(+) create mode 100755 src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java diff --git a/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java b/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java new file mode 100755 index 0000000000..6495ddbe89 --- /dev/null +++ b/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java @@ -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 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 createStyles(XSSFWorkbook wb){ + Map styles = new HashMap(); + 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 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("" + + "" ); + out.write("\n"); + } + + public void endSheet() throws IOException { + out.write(""); + out.write(""); + } + + /** + * Insert a new row + * + * @param rownum 0-based row number + */ + public void insertRow(int rownum) throws IOException { + out.write("\n"); + this.rownum = rownum; + } + + /** + * Insert row end marker + */ + public void endRow() throws IOException { + out.write("\n"); + } + + public void createCell(int columnIndex, String value, int styleIndex) throws IOException { + String ref = new CellReference(rownum, columnIndex).formatAsString(); + out.write(""); + out.write(""+value+""); + out.write(""); + } + + 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(""); + out.write(""+value+""); + out.write(""); + } + + 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); + } + } + +} -- 2.39.5