用SXSSFWorkbook导出50万条数据到本地Excel文件（简单实现例子）

不废话，上代码：

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
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.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class BigDataExcelUtils {

    public static void writeBigData() throws IOException {
        long startTime = System.currentTimeMillis();    //获取开始时间,单位毫秒
        FileInputStream inputStream = new FileInputStream("C:\\temp\\template.xls");
        XSSFWorkbook wb_template = new XSSFWorkbook(inputStream);
        inputStream.close();

        SXSSFWorkbook wb = new SXSSFWorkbook(wb_template);
        wb.setCompressTempFiles(true);
        SXSSFSheet sh = (SXSSFSheet) wb.getSheetAt(0);
        sh.setRandomAccessWindowSize(100);// keep 100 rows in memory, exceeding rows will be flushed to disk，每次加载到内存100条
        for(int rowNum = 0; rowNum < 500000; rowNum++){ //500000为行数
            Row row = sh.createRow(rowNum);
            for(int cellNum = 0; cellNum < 10; cellNum++){ //10为列数
                Cell cell = row.createCell(cellNum);
                String address = new CellReference(cell).formatAsString();
                cell.setCellValue(address);
            }
        }
        FileOutputStream outFile = new FileOutputStream("C:\\temp\\tempsxssf.xlsx");
        wb.write(outFile);
        outFile.close();
        wb.dispose();// dispose of temporary files backing this workbook on disk
        long endTime=System.currentTimeMillis(); //获取开始时间,单位毫秒
        long duration = endTime - startTime;
        System.out.println("程序运行时长：" + Double.valueOf(duration)/1000 + "秒");
    }

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

程序运行时间约25秒。当然，实际业务处理还有别的地方消耗时间。