hutool poi excel 百万级 模板 写入

pom.xml

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>xerces</groupId>
            <artifactId>xercesImpl</artifactId>
            <version>2.12.0</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.5.1</version>
        </dependency>

工具类

import cn.hutool.core.io.IoUtil;
import cn.hutool.core.io.resource.ClassPathResource;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.RowUtil;
import cn.hutool.poi.excel.cell.CellUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

/**
 * @author liaozesong
 */
public class ReportExcelUtil {
    public static byte[] process(String modelExcel, Map<String, Object> titleMap, List<List<String>> data) throws IOException {
        Assert.isTrue(data.size() < 1000000, "暂不支持数据量超过一百万条");
        InputStream oldStream = null;
        InputStream copyStream = null;
        ExcelReader reader = null;
        BigExcelWriter writer = null;
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        try {
            ClassPathResource resource = new ClassPathResource("model/" + modelExcel);
            oldStream = resource.getStream();
            copyStream = new ByteArrayInputStream(oldStream.readAllBytes());

            reader = new ExcelReader(copyStream, 0);
            //设置标题
            Cell cell = reader.getCell(0, 0);
            CellStyle cellStyle = cell.getCellStyle();
            String title = (String) reader.readCellValue(0, 0);
            title = StrUtil.format(title, titleMap);
            CellUtil.setCellValue(cell, title, cellStyle);

            //设置行数据
            writer = getWriter(reader);
            writer.setCurrentRow(reader.getRowCount());
            for (int i = 0; i < data.size(); i++) {
                writeRow(writer, data.get(i), i % 2 == 1);
            }
            writer.flush(outStream);
            return outStream.toByteArray();
        } finally {
            IoUtil.close(oldStream);
            IoUtil.close(copyStream);
            IoUtil.close(reader);
            IoUtil.close(writer);
            IoUtil.close(outStream);
        }
    }


    /**
     * 转 大数据写
     */
    private static BigExcelWriter getWriter(ExcelReader reader) {
        Workbook workbook = reader.getWorkbook();
        workbook = new SXSSFWorkbook((XSSFWorkbook) workbook);
        Sheet sheetAt = workbook.getSheetAt(0);
        return new BigExcelWriter(sheetAt);
    }

    /**
     * 写行 工具方法
     */
    private static void writeRow(ExcelWriter writer, List<String> data, boolean isWhite) {
        RowUtil.writeRow(writer.getSheet().createRow(writer.getCurrentRow()), data, writer.getStyleSet(), !isWhite);
        writer.passCurrentRow();
    }

}

待导入对象

    @Order(1)
    private String id;
    /**
     * 检测时间戳
     */
    @Order(2)
    private Long ts;
    public List<String> toList() {
        this.init();
        List<String> result = new ArrayList<>();
        Field[] fields = ReflectUtil.getFields(this.getClass());
        fields = Arrays.stream(fields).filter(field -> field.getAnnotation(Order.class) != null).toArray(Field[]::new);
        AnnotationAwareOrderComparator.sort(fields);
        for (Field field : fields) {
            Object fieldValue = ReflectUtil.getFieldValue(this, field.getName());
            if (fieldValue == null) {
                result.add("");
            } else {
                result.add(StrUtil.format("{}", fieldValue));
            }
        }
        return result;
    }

调用

		List<GeoReportEntityBase> entities = mongoTemplate.find(rcdResultId, documentClass);
        List<List<String>> rows = entities.stream().map(GeoReportEntityBase::toList).collect(Collectors.toList());

        Map<String, Object> map = BeanUtil.beanToMap(param);
        map.put("startTime", param.getStartTime().format(DATE_TIME_FORMATTER));
        map.put("endTime", param.getEndTime().format(DATE_TIME_FORMATTER));
        
        return ReportExcelUtil.process(modelName, map, rows);