POI 3.8 Beta5 创建大数据量 Excel文件
java web应用中，遇到导出excel文件的情况非常多，用POI进行EXCEL操作，读写都非常方便，但遇到大数据导出EXCEL文件时，讨厌的OOM错误(java.lang.OutOfMemoryError)会让你很抓狂，一直没有发现更好的办法解决，我又再一次尝试GOOGLE答案时，终于有了比较好用的方法。这是POI生成大数据EXCEL的DEMO，只需要修改比较少的代码便可以用在我们的项目中。
使用这个方法需要以下几个包
?? poi-3.8-beta5-20111217.jar
?? poi-ooxml-3.8-beta5-20111217.jar
?? xmlbeans-2.3.0.jar
?? poi-ooxml-schemas-3.8-beta5-20111217.jar
?? dom4j-1.6.1.jar
这几个包都在 poi-3.8-beta5 中，下载地址 http://poi.apache.org/download.html
以下是源代码：
package test;import org.apache.poi.ss.usermodel.DateUtil;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.*;public class BigGridDemo {public static void main(String[] args) {try {// 第一步.创建一个临时的 excel 文件，配置单元格属性，数值格式。FileOutputStream os;XSSFWorkbook wb = new XSSFWorkbook();XSSFSheet sheet = wb.createSheet("Big Grid");os = new FileOutputStream("template.xlsx");wb.write(os); os.close(); Map<String, XSSFCellStyle> styles = createStyles(wb);String sheetRef = sheet.getPackagePart().getPartName().getName();// 第二步，生成xml数据临时文件 File tmp = File.createTempFile("sheet", ".xml"); Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), "UTF-8"); generate(fw, styles); fw.close(); // 第三步，创建ZIP输出流，将xml数据临时文件数据写入到ZIP文件中。 FileOutputStream out = new FileOutputStream("big-grid.xlsx"); substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out); out.close(); } catch (FileNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();} } /** * 创建样式表 */ 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); @SuppressWarnings("unchecked") Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) 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=\""+"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); } }}
其中写入ZIP输出流的步骤还不太理解，反正能用就形了，不是么。