This is a VERY crude - emphasise very and crude - first pass attempt to include the ability to create forumlae through Yegor's BigGridDemo. There is lots of work wtil to do - for example, I need to create another entry in the archoive called calcChain.xml, populate it and add the relationship, also there is work still to be done setting the type for the formulae cell. Gaving said that, this approach does seem to work, at least given the testing I have completed. Do not be tempted to use it in a productiopn environment before throughly testing it yourself however.
Will play a little more with the code over the weekend and post again if I make any progress. Yours Mark B /* ==================================================================== 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; package workbookprotection; import java.io.*; import java.util.*; import java.util.zip.ZipEntry; import java.util.zip.ZipFile; import java.util.zip.ZipOutputStream; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.usermodel.*; /** * 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 { private static final String XML_ENCODING = "UTF-8"; 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("C:/temp/template.xlsx"); wb.write(os); os.close(); //Step 2. Generate XML file. File tmp = File.createTempFile("sheet", ".xml"); Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING); generate(fw, styles); fw.close(); //Step 3. Substitute the template entry with the generated data FileOutputStream out = new FileOutputStream("C:/temp/big-grid.xlsx"); substitute(new File("C:/temp/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 < 10; 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.insertRow(10); sw.createFormulaCell(0, "\'Big Grid\'!$A$2"); sw.createFormulaCell(3, "SUM(D2:D10)", styles.get("currency").getIndex()); sw.endRow(); 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); @SuppressWarnings("unchecked") Enumeration en = (Enumeration) zip.entries(); while (en.hasMoreElements()) { ZipEntry ze = 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 final Writer _out; private int _rownum; public SpreadsheetWriter(Writer out) { _out = out; } public void beginSheet() throws IOException { _out.write("<?xml version=\"1.0\" encoding=\"" + XML_ENCODING + "\"?>" + ""); _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("<row r=\"" + (rownum + 1) + "\">\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("<c r=\"" + ref + "\" t=\"inlineStr\""); if (styleIndex != -1) { _out.write(" s=\"" + styleIndex + "\""); } _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("<c r=\"" + ref + "\" t=\"n\""); if (styleIndex != -1) { _out.write(" s=\"" + styleIndex + "\""); } _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); } public void createFormulaCell(int columnIndex, String formula) throws IOException { createFormulaCell(columnIndex, formula, -1); } public void createFormulaCell(int columnIndex, String formula, int styleIndex) throws IOException { String ref = new CellReference(_rownum, columnIndex).formatAsString(); _out.write("<c r=\"" + ref + "\""); if (styleIndex != -1) { _out.write(" s=\"" + styleIndex + "\""); } _out.write(">"); _out.write("" + formula + ""); _out.write(""); _out.write(""); } } } -- View this message in context: http://apache-poi.1045710.n5.nabble.com/setFormula-for-Generated-xlsx-file-through-BigGridDemo-tp3957674p4081090.html Sent from the POI - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
