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