【文件导出1】easyexcel导出excel文件数据

easyexcel实现excel文件数据导出

---

前言

springBoot项目实现excel文件导出功能，可以在浏览器点击导出下载excel格式文件到本地。使用alibaba开源项目easyexcel实现，项目地址：https://easyexcel.opensource.alibaba.com/

---

一、引入easyexcel依赖

pom.xml配置如下：

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

二、实现代码

1.controller层

@GetMapping("/record/_export")
@ApiOperation("查询记录导出")
public void recordExport(HttpServletResponse response, RecordSearchRequest query) throws IOException {
   
        ruleVerifyResultService.recordExport(response, query);
    }

RecordSearchRequest为请求查询条件，根据条件导出对应的记录。

2.接口层

public interface ResultService extends IService<RuleVerifyResultEntity> {
   
 /**
     * 记录导出
     * @param query
     * @return
     */
    void recordExport(HttpServletResponse response, RecordSearchRequest query) throws IOException;
}

3.接口实现类

@Override
public void recordExport(HttpServletResponse response, RecordSearchRequest query) throws IOException {
   
        LocalDateTime startExecuteTime = null;
        LocalDateTime endExecuteTime = null;
        //设置导出文件名称
        String fileName = "检验记录列表数据";
        if (ObjectUtils.isNotEmpty(query.getExecuteStartTime())) {
   
            startExecuteTime = query.getExecuteStartTime().atStartOfDay();
            endExecuteTime = query.getExecuteStartTime().atTime(23, 59, 59);
        }
       //查询需要导出的数据，用list集合接收
        List<RuleVerifyRecordEntity> recordList = ruleVerifyRecordMapper.selectList(Wrappers.<RuleVerifyRecordEntity>lambdaQuery().
                // 关键字搜索
                and(StringUtils.isNotEmpty(query.getKeywords()), wrapper -> 
	          wrapper.like(RuleVerifyRecordEntity::getTableName, query.getKeywords()).
                        or().like(RuleVerifyRecordEntity::getTableRemark, query.getKeywords()).
                        or().like(RuleVerifyRecordEntity::getDatasourceName, query.getKeywords())
                )
                orderByDesc(RuleVerifyRecordEntity::getExecuteStartTime)
        );

        //1.导出excel数据
        if (ObjectUtils.equals(ExportTypeEnum.EXCEL.getValue(), query.getExportType())) {
   
            //隐藏“检验记录数据”这个sheet的第1行数据
            List<RowHeightColWidthModel> rowHeightColWidthList = new ArrayList<>();
            rowHeightColWidthList.add(RowHeightColWidthModel.createHideRowModel("检验记录数据", 1));
            //设置第二行第二列的行高列宽
            rowHeightColWidthList.add(RowHeightColWidthModel.createRowHeightColWidthModel("检验记录数据", 2,100.0f,2,100));
            //转换导出数据
            try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                    .registerWriteHandler(new CustomVerticalCellStyleStrategy())
                    .registerWriteHandler(new CustomWidthStyleStrategy())
                    .registerWriteHandler(new CustomRowColHandler(rowHeightColWidthList)).build()) {
   
                //设置导出参数
                ExportUtil.setExportResponse(response, fileName);
                ExportHeader<RuleVerifyRecordEntity> exportHeader = ruleVerifyHead();
                List<List<String>> heads = exportHeader.getHeaders();
                WriteSheet schemaWriteSheet = EasyExcel.writerSheet("检验记录数据").head(heads).build();
                List<List<String>> list = Lists.newArrayList();
                if (!org.springframework.util.CollectionUtils.isEmpty(recordList)) {
   
                    //依次填充字段值
                    for (RuleVerifyRecordEntity record : recordList) {
   
                        List<String> values = Lists.newArrayList();
                        values.add(record.getTableName());
                        values.add(StringUtils.join(record.getRuleStrengthCount(), "/", record.getRuleWeekCount()));
                        values.add(ObjectUtils.isNotEmpty(record.