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