Save This Page
Home » POI-3.5 » org.apache.poi.xssf » usermodel » examples » [javadoc | source]
    1   /* ====================================================================
    2      Licensed to the Apache Software Foundation (ASF) under one or more
    3      contributor license agreements.  See the NOTICE file distributed with
    4      this work for additional information regarding copyright ownership.
    5      The ASF licenses this file to You under the Apache License, Version 2.0
    6      (the "License"); you may not use this file except in compliance with
    7      the License.  You may obtain a copy of the License at
    8   
    9          http://www.apache.org/licenses/LICENSE-2.0
   10   
   11      Unless required by applicable law or agreed to in writing, software
   12      distributed under the License is distributed on an "AS IS" BASIS,
   13      WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   14      See the License for the specific language governing permissions and
   15      limitations under the License.
   16   ==================================================================== */
   17   
   18   package org.apache.poi.xssf.usermodel.examples;
   19   
   20   import java.io;
   21   import java.util;
   22   import java.util.zip.ZipEntry;
   23   import java.util.zip.ZipFile;
   24   import java.util.zip.ZipOutputStream;
   25   
   26   import org.apache.poi.ss.usermodel.DateUtil;
   27   import org.apache.poi.ss.usermodel.IndexedColors;
   28   import org.apache.poi.ss.util.CellReference;
   29   import org.apache.poi.xssf.usermodel;
   30   
   31   /**
   32    * Demonstrates a workaround you can use to generate large workbooks and avoid OutOfMemory exception.
   33    *
   34    * The trick is as follows:
   35    * 1. create a template workbook, create sheets and global objects such as cell styles, number formats, etc.
   36    * 2. create an application that streams data in a text file
   37    * 3. Substitute the sheet in the template with the generated data
   38    *
   39    * @author Yegor Kozlov
   40    */
   41   public class BigGridDemo {
   42       public static void main(String[] args) throws Exception {
   43   
   44           // Step 1. Create a template file. Setup sheets and workbook-level objects such as
   45           // cell styles, number formats, etc.
   46   
   47           XSSFWorkbook wb = new XSSFWorkbook();
   48           XSSFSheet sheet = wb.createSheet("Big Grid");
   49   
   50           Map<String, XSSFCellStyle> styles = createStyles(wb);
   51           //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml
   52           String sheetRef = sheet.getPackagePart().getPartName().getName();
   53   
   54           //save the template
   55           FileOutputStream os = new FileOutputStream("template.xlsx");
   56           wb.write(os);
   57           os.close();
   58   
   59           //Step 2. Generate XML file.
   60           File tmp = File.createTempFile("sheet", ".xml");
   61           Writer fw = new FileWriter(tmp);
   62           generate(fw, styles);
   63           fw.close();
   64   
   65           //Step 3. Substitute the template entry with the generated data
   66           FileOutputStream out = new FileOutputStream("big-grid.xlsx");
   67           substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out);
   68           out.close();
   69       }
   70   
   71       /**
   72        * Create a library of cell styles.
   73        */
   74       private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
   75           Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
   76           XSSFDataFormat fmt = wb.createDataFormat();
   77   
   78           XSSFCellStyle style1 = wb.createCellStyle();
   79           style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
   80           style1.setDataFormat(fmt.getFormat("0.0%"));
   81           styles.put("percent", style1);
   82   
   83           XSSFCellStyle style2 = wb.createCellStyle();
   84           style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
   85           style2.setDataFormat(fmt.getFormat("0.0X"));
   86           styles.put("coeff", style2);
   87   
   88           XSSFCellStyle style3 = wb.createCellStyle();
   89           style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
   90           style3.setDataFormat(fmt.getFormat("$#,##0.00"));
   91           styles.put("currency", style3);
   92   
   93           XSSFCellStyle style4 = wb.createCellStyle();
   94           style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
   95           style4.setDataFormat(fmt.getFormat("mmm dd"));
   96           styles.put("date", style4);
   97   
   98           XSSFCellStyle style5 = wb.createCellStyle();
   99           XSSFFont headerFont = wb.createFont();
  100           headerFont.setBold(true);
  101           style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
  102           style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
  103           style5.setFont(headerFont);
  104           styles.put("header", style5);
  105   
  106           return styles;
  107       }
  108   
  109       private static void generate(Writer out, Map<String, XSSFCellStyle> styles) throws Exception {
  110   
  111           Random rnd = new Random();
  112           Calendar calendar = Calendar.getInstance();
  113   
  114           SpreadsheetWriter sw = new SpreadsheetWriter(out);
  115           sw.beginSheet();
  116   
  117           //insert header row
  118           sw.insertRow(0);
  119           int styleIndex = styles.get("header").getIndex();
  120           sw.createCell(0, "Title", styleIndex);
  121           sw.createCell(1, "% Change", styleIndex);
  122           sw.createCell(2, "Ratio", styleIndex);
  123           sw.createCell(3, "Expenses", styleIndex);
  124           sw.createCell(4, "Date", styleIndex);
  125   
  126           sw.endRow();
  127   
  128           //write data rows
  129           for (int rownum = 1; rownum < 100000; rownum++) {
  130               sw.insertRow(rownum);
  131   
  132               sw.createCell(0, "Hello, " + rownum + "!");
  133               sw.createCell(1, (double)rnd.nextInt(100)/100, styles.get("percent").getIndex());
  134               sw.createCell(2, (double)rnd.nextInt(10)/10, styles.get("coeff").getIndex());
  135               sw.createCell(3, rnd.nextInt(10000), styles.get("currency").getIndex());
  136               sw.createCell(4, calendar, styles.get("date").getIndex());
  137   
  138               sw.endRow();
  139   
  140               calendar.roll(Calendar.DAY_OF_YEAR, 1);
  141           }
  142           sw.endSheet();
  143       }
  144   
  145       /**
  146        *
  147        * @param zipfile the template file
  148        * @param tmpfile the XML file with the sheet data
  149        * @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml
  150        * @param out the stream to write the result to
  151        */
  152   	private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException {
  153           ZipFile zip = new ZipFile(zipfile);
  154   
  155           ZipOutputStream zos = new ZipOutputStream(out);
  156   
  157           @SuppressWarnings("unchecked")
  158           Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
  159           while (en.hasMoreElements()) {
  160               ZipEntry ze = en.nextElement();
  161               if(!ze.getName().equals(entry)){
  162                   zos.putNextEntry(new ZipEntry(ze.getName()));
  163                   InputStream is = zip.getInputStream(ze);
  164                   copyStream(is, zos);
  165                   is.close();
  166               }
  167           }
  168           zos.putNextEntry(new ZipEntry(entry));
  169           InputStream is = new FileInputStream(tmpfile);
  170           copyStream(is, zos);
  171           is.close();
  172   
  173           zos.close();
  174       }
  175   
  176       private static void copyStream(InputStream in, OutputStream out) throws IOException {
  177           byte[] chunk = new byte[1024];
  178           int count;
  179           while ((count = in.read(chunk)) >=0 ) {
  180             out.write(chunk,0,count);
  181           }
  182       }
  183   
  184       /**
  185        * Writes spreadsheet data in a Writer.
  186        * (YK: in future it may evolve in a full-featured API for streaming data in Excel)
  187        */
  188       public static class SpreadsheetWriter {
  189           private final Writer _out;
  190           private int _rownum;
  191   
  192           public SpreadsheetWriter(Writer out){
  193               _out = out;
  194           }
  195   
  196           public void beginSheet() throws IOException {
  197               _out.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
  198                       "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
  199               _out.write("<sheetData>\n");
  200           }
  201   
  202           public void endSheet() throws IOException {
  203               _out.write("</sheetData>");
  204               _out.write("</worksheet>");
  205           }
  206   
  207           /**
  208            * Insert a new row
  209            *
  210            * @param rownum 0-based row number
  211            */
  212           public void insertRow(int rownum) throws IOException {
  213               _out.write("<row r=\""+(rownum+1)+"\">\n");
  214               this._rownum = rownum;
  215           }
  216   
  217           /**
  218            * Insert row end marker
  219            */
  220           public void endRow() throws IOException {
  221               _out.write("</row>\n");
  222           }
  223   
  224           public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
  225               String ref = new CellReference(_rownum, columnIndex).formatAsString();
  226               _out.write("<c r=\""+ref+"\" t=\"inlineStr\"");
  227               if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
  228               _out.write(">");
  229               _out.write("<is><t>"+value+"</t></is>");
  230               _out.write("</c>");
  231           }
  232   
  233           public void createCell(int columnIndex, String value) throws IOException {
  234               createCell(columnIndex, value, -1);
  235           }
  236   
  237           public void createCell(int columnIndex, double value, int styleIndex) throws IOException {
  238               String ref = new CellReference(_rownum, columnIndex).formatAsString();
  239               _out.write("<c r=\""+ref+"\" t=\"n\"");
  240               if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
  241               _out.write(">");
  242               _out.write("<v>"+value+"</v>");
  243               _out.write("</c>");
  244           }
  245   
  246           public void createCell(int columnIndex, double value) throws IOException {
  247               createCell(columnIndex, value, -1);
  248           }
  249   
  250           public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException {
  251               createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);
  252           }
  253       }
  254   }

Save This Page
Home » POI-3.5 » org.apache.poi.xssf » usermodel » examples » [javadoc | source]