excel 导出大数据的处理

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import junit.framework.Assert;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
* 处理大数据的导出 可以用缓存
* @author
*
*
*HSSF 03 XSSF 07
*/
public class PoiTest {

public static void main(String[] args) throws Throwable {

// sxssf2(); //可以处理百万条数据的导出
// SXSSF();
xssf(); //大数据的导出会导致内存溢出 2007的excel
}

/** * 自定义缓存的大小 可以防止内存溢出  2007以上的 SXSSFWorkbook * @throws Exception */private static void sxssf2() throws Exception{     SXSSFWorkbook wb = new SXSSFWorkbook(-1); // turn off auto-flushing and accumulate all rows in memory        Sheet sh = wb.createSheet("第一页");        for(int rownum = 0; rownum < 500; rownum++){            Row row = sh.createRow(rownum);            for(int cellnum = 0; cellnum < 10; cellnum++){                Cell cell = row.createCell(cellnum);                String address = new CellReference(cell).formatAsString();                cell.setCellValue(address);            }           // manually control how rows are flushed to disk            if(rownum % 100 == 0) {                ((SXSSFSheet)sh).flushRows(100); // retain 100 last rows and flush all others                // ((SXSSFSheet)sh).flushRows() is a shortcut for ((SXSSFSheet)sh).flushRows(0),                // this method flushes all rows           }        }        Sheet sh2 = wb.createSheet("第二页");        for(int rownum = 0; rownum < 500; rownum++){            Row row = sh2.createRow(rownum);            for(int cellnum = 0; cellnum < 10; cellnum++){                Cell cell = row.createCell(cellnum);                String address = new CellReference(cell).formatAsString();                cell.setCellValue("aaaa"+address);            }           // manually control how rows are flushed to disk            if(rownum % 100 == 0) {                ((SXSSFSheet)sh2).flushRows(100); // retain 100 last rows and flush all others                // ((SXSSFSheet)sh).flushRows() is a shortcut for ((SXSSFSheet)sh).flushRows(0),                // this method flushes all rows           }        }        FileOutputStream out = new FileOutputStream("e:/sxssf2_2.xlsx");        wb.write(out);        out.close();        // dispose of temporary files backing this workbook on disk        wb.dispose();}private static void SXSSF() throws FileNotFoundException, IOException {    SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk    Sheet sh = wb.createSheet();    for(int rownum = 0; rownum < 1000; rownum++){        Row row = sh.createRow(rownum);        for(int cellnum = 0; cellnum < 10; cellnum++){            Cell cell = row.createCell(cellnum);            String address = new CellReference(cell).formatAsString();            cell.setCellValue(address);        }    }    // Rows with rownum < 900 are flushed and not accessible    for(int rownum = 0; rownum < 900; rownum++){      Assert.assertNull(sh.getRow(rownum));    }    // ther last 100 rows are still in memory    for(int rownum = 900; rownum < 1000; rownum++){        Assert.assertNotNull(sh.getRow(rownum));    }    FileOutputStream out = new FileOutputStream("e:/test2.xlsx");    wb.write(out);    out.close();    // dispose of temporary files backing this workbook on disk    wb.dispose();}/** * 大数据的导出会挂掉 * @throws Exception */public static void xssf() throws Exception{    XSSFWorkbook wb = new XSSFWorkbook();    XSSFSheet sheet = wb.createSheet("第一页");    for (int j = 0; j < 1000000; j++) {        XSSFRow row = sheet.createRow(j);         for(int cellnum = 0; cellnum < 10; cellnum++){                Cell cell = row.createCell(cellnum);                String address = new CellReference(cell).formatAsString();                cell.setCellValue(address);            }         }    FileOutputStream out = new FileOutputStream("e:/xssf.xlsx");    wb.write(out);    out.close();}

}

//pom依赖的jar

<dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi</artifactId>    <version>3.15-beta1</version></dependency><dependency>  <groupId>org.apache.poi</groupId>  <artifactId>poi-ooxml</artifactId>  <version>3.15-beta1</version>  

org.apache.poi
poi-ooxml-schemas
3.15-beta1

xml-apis
xml-apis
1.4.01