大数据量表数据导出至Excel

背景

工作中常遇到把表数据导出至Excel的需求，一般通过Apache 的POI进行导入；

HSSFWorkbook用于导出97-03版的Excel，后缀为xls；

XSSFWorkbook用于导出07版的Excel，后缀为xlsx；

一般情况下到处都没太多问题，但是在大数据量情况下会有各种问题：

1. HSSFWorkbook最大支持写入65536行；
2. XSSFWorkbook导出大数据量，比如说100万行时会出现内存溢出的情况；

解决办法

一、使用SXSSFWorkbook来写入

SXSSFWorkbook是XSSFWorkbook的升级版POI3.8版本开始提供的一种支持低内存占用的操作方式，扩展名为.xlsx,通过流式写入的方式，可以逐行逐列地将数据写入Excel文件，因此在处理大量数据时性能更好；SXSSFWorkbook通过将数据写入临时文件而不是保存在内存中，以节省内存，这使得它能够处理更大的数据集，同时避免了内存溢出的问题；直接上代码：

先是pom依赖：

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.5</version>
        </dependency>

        <!--xls 07-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.5</version>
        </dependency>

运行代码：

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;


public class ExcelTest {
    public static void main(String[] args) throws Exception{
        test07WriteBigDataSuper();
    }
    public static  void test07WriteBigDataSuper() throws Exception{
        long start = System.currentTimeMillis();
        Workbook workbook03 = new SXSSFWorkbook(1000);
        Sheet sheet = workbook03.createSheet("用户表");
        for(int rowNum = 0; rowNum < 1000000; rowNum++){
            Row row = sheet.createRow(rowNum);
            for(int cellNum = 0; cellNum<10; cellNum++){
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        //生成表
        FileOutputStream fileOutputStream = new FileOutputStream(new File("D:\\test07WriteBigDataSuperbw.xlsx"));
        workbook03.write(fileOutputStream);
        fileOutputStream.close();
        //注意，这里要删除临时文件
        ((SXSSFWorkbook)workbook03).dispose();
        long end = System.currentTimeMillis();
        System.out.println((double) (end - start)/1000);
    }

}

以上代码中通过SXSSFWorkbook向Excel的用户表Sheet中写入100万条数据，每行数据是从0-9的纯数字。

有两个关键点需要说明一下，

1. Workbook workbook03 = new SXSSFWorkbook(1000); //1000是rowAccessWindowSize参数，每个sheet 对应一个临时文件，当行数大于rowAccessWindowSize 时，就会向临时文件中flush, 这样就保证了内存的低占用率。当行创建完，直接从临时文件中写入到Excel中。

2. ((SXSSFWorkbook)workbook03).dispose();//删除写入过程中产生的临时文件。

经过测试，通过该方式写入100万条数据的时间大约是14秒左右，同时没有出现内存溢出问题，笔记本上跑的时间大约在17秒左右。

Excel内容如下：

二、使用EasyExcel写入

这是官网地址：关于Easyexcel | Easy Excel 官网 (alibaba.com)

我的需求是从数据表中导出1亿条数据，要求不能出现内存溢出的问题，同时提高导出效率，因为我导出的表的具体数据库信息是不固定的，因此只能先通过JDBC读出数据，然后再写入。

pom

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>4.0.2</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>

代码

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.*;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import static com.hikdata.easy.Test.data;

public class WriteCellDemoDataTest {

    public static void main(String[] args) {
        noModelWrite();
    }

    public static void noModelWrite() {
        long start = System.currentTimeMillis();
        // 写法1
        String fileName = "D:\\" + "noModelWrite_" + System.currentTimeMillis() + ".xlsx";
        EasyExcel.write(fileName).head(head()).sheet("模板").doWrite(dataList());
        long end = System.currentTimeMillis();
        System.out.println((double) (end - start)/1000);
    }
    
    //设置标题行
    public static List<List<String>> head() {
        List<List<String>> list = ListUtils.newArrayList();
        List<String> head0 = ListUtils.newArrayList();
        head0.add("字符串字段" + System.currentTimeMillis());
        List<String> head1 = ListUtils.newArrayList();
        head1.add("数字字段" + System.currentTimeMillis());
        List<String> head2 = ListUtils.newArrayList();
        head2.add("日期字段" + System.currentTimeMillis());
        list.add(head0);
        list.add(head1);
        list.add(head2);
        return list;
    }
    
    //组装数据
    public static List<List<Object>> dataList() {
        List<List<Object>> list = ListUtils.newArrayList();
        for (int i = 0; i < 1000000; i++) {
            List<Object> data = ListUtils.newArrayList();
            data.add("字符串" + i);
            data.add(0.56);
            data.add(new Date());
            list.add(data);
        }
        return list;
    }
}

经测试写入100万条数据大概耗时10秒左右。

写入内容如下：