EasyExcel导出大量数据

1：EasyExcel导出数据

2：查询时分sheet页查询写入

3：自定义导出格式

1：pom文件：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

2：EasyExcelDataUtil

/**
 *  * Description:EasyExcel导出Util
 *  *
 *  * @author 
 *  * @date 2021/7/14 14:13
 *  
 */
@Configuration
@Component
@Slf4j
public class EasyExcelDataUtil {
    /**
     * 通用EasyExcel导出 表头、数据写入
     * @param searchResults
     * @param lefts
     * @return
     */
    public static List<List<Object>> createTestListObject( List<Map<String, Object>> searchResults,List<FieldGroupItem> lefts) {
        List<List<Object>> object = new ArrayList<List<Object>>();
        for(int i=0;i<searchResults.size();i++){
            List<Object> da = new ArrayList<Object>();
            for(FieldGroupItem fieldGroupItem:lefts){
                da.add(searchResults.get(i).get(fieldGroupItem.getFieldName()));
            }
            object.add(da);
        }
        return object;
    }

    /**
     * 创建表头
     * @param lefts
     * @return
     */
    public static List<List<String>> createTestListStringHead( List<FieldGroupItem> lefts){
        List<List<String>> head = new ArrayList<List<String>>();
        for(FieldGroupItem fieldGroupItem:lefts){
            List<String> headCoulumn = new ArrayList<String>();
            headCoulumn.add(fieldGroupItem.getLabel());
            head.add(headCoulumn);
        }
        return head;
    }

    /**
     * EasyExcel自定义样式
     * @return
     */
    public static TableStyle createTableStyle() {
        TableStyle tableStyle = new TableStyle();
        Font headFont = new Font();
        headFont.setBold(true);
        headFont.setFontHeightInPoints((short)12);
        headFont.setFontName("微软雅黑");
        tableStyle.setTableHeadFont(headFont);
        tableStyle.setTableHeadBackGroundColor(IndexedColors.WHITE);
        return tableStyle;
    }

    /**
     * 下载
     * @param fileName  文件名
     * @param response
     * @return
     * @throws Exception
     */
    public static HttpServletResponse getResponse(String fileName, HttpServletResponse response) throws Exception{
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(fileName, "utf-8"));
        return response;
    }
}

3:EasyExcelConstant

/**
 *  * Description:EasyExcel常量
 *  *
 *  * @author thw
 *  * @date 2021/7/12 11:39
 *  
 */
public class EasyExcelConstant {
    /**
     * 每个sheet存储的记录数 100W
     */
    public static final Integer PER_SHEET_ROW_COUNT = 5000;

    /**
     * 每页sheet页前缀名
     */

    public static final String SHEET_NAME="sheet";
}

4:导出对象

/**
 * EasyExcel定制导出Vo
 */
@Data
public class MesEafExportEasyParam extends BaseRowModel {
    //查询时需查询id用于分sheet页查询，导出时忽略该字段。
    @ExcelIgnore
    private Long id;

    @ExcelProperty(value = "物料编号", order = 1)
    private String EAF001;

    @ExcelProperty(value = "物料名称", order = 2)
    private String EAF002;

    @ExcelProperty(value = "工艺编号", order = 3)
    private String EAF003;

    @ExcelProperty(value = "工艺说明", order = 4)
    private String EAF004;

    @ExcelProperty(value = "状态", order = 5)
    private String EAF005;

    @ExcelProperty(value = "规格尺寸", order = 6)
    private String BAM004;

}

5：控制类

 @Resource
    private EasyExcelDataUtil easyExcelDataUtil;


     @GetMapping(path = "/exportEasyExcel")
    @ApiOperation(value = "导出 列表EasyExcel API", notes = "导出 EasyExcel API接口")
    public void exportEasyExcel(HttpServletResponse response,@RequestBody MesEafQueryParam mesEafQueryParam) throws Exception {
        long start = System.currentTimeMillis();
        ServletOutputStream out = null;
        ExcelWriter writer = null;
        try {
            out = response.getOutputStream();
            writer = new ExcelWriterBuilder()
                    .autoCloseStream(true)
                    .excelType(ExcelTypeEnum.XLSX)
                    .file(out)
                    .head(MesEafExportEasyParam.class)
                    .build();
            response = easyExcelDataUtil.getResponse("列表.xlsx", response);
            long lastBatchMaxId = 0L;
            int limit = EasyExcelConstant.PER_SHEET_ROW_COUNT+1;
            for (int i = 1; ; i++) {
                Sheet writeSheet = new Sheet(i, 0, MesEafExportEasyParam.class);
                writeSheet.setSheetName("sheet" + i);
                List<MesEafExportEasyParam> list = mesEafService.findMesEafPageListEasy(mesEafQueryParam, lastBatchMaxId, limit);
                if (list.isEmpty()) {
                    writer.finish();
                    break;
                } else {
                    lastBatchMaxId = list.stream().map(MesEafExportEasyParam::getId).max(Long::compareTo).orElse(Long.MAX_VALUE);
                    writer.write(list, writeSheet);
                    list.clear();
                }
            }
            log.debug("导出excel所花时间：" + (System.currentTimeMillis() - start) / 1000);
        } catch (Exception e) {
            log.error("下载失败" + e.getMessage(), e);
        } finally {
            out.flush();
            writer.finish();
            out.close();
        }
    }

6：serviceimpl层

  @Transactional(rollbackFor = Exception.class)
    @Override
    public List<MesEafExportEasyParam> findMesEafPageListEasy(MesEafQueryParam mesEafQueryParam , long lastBatchMaxId,
                                                              int limit) {
        return baseMapper.findMesEafPageListEasy(mesEafQueryParam,lastBatchMaxId,limit).stream().map(s -> {
            MesEafExportEasyParam dto=new MesEafExportEasyParam();
            dto.setEAF001(s.getEaf001());
            dto.setEAF002(s.getEaf002());
            dto.setEAF003(s.getEaf003());
            dto.setEAF004(s.getEaf004());
            dto.setEAF005(s.getEaf005());
            dto.setBAM004(s.getBam004());
            dto.setId(Long.valueOf(s.getId())); //查询脚本需查出id按此分段数据库查询分sheet页
            return dto;
        }).collect(Collectors.toList());
    }