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.getExecuteStatus()) ? record.getExecuteStatus().toString() : null);
                        values.add(record.getSchedulerInfo());
                        values.add(DateTimeUtil.toDateTimeStr(ObjectUtils.isNotEmpty(record.getExecuteStatus()) ? record.getExecuteStartTime() : LocalDateTime.now()));
                        values.add(DateTimeUtil.toDateTimeStr(ObjectUtils.isNotEmpty(record.getExecuteEndTime()) ? record.getExecuteEndTime() : LocalDateTime.now()));
                        list.add(values);
                    }
                }
                excelWriter.write(list, schemaWriteSheet);
                excelWriter.finish();
            } catch (Exception e) {
                log.error(e.getLocalizedMessage(), e);
                throw new RuntimeException(e);
            }
}

(1)RowHeightColWidthModel行高列宽信息构建

import lombok.Getter;

/**
 * 行高列宽信息（Builder构建者模式）
 *
 */
@Getter
public class RowHeightColWidthModel {
    /**
     * sheet名称
     */
    private String sheetName;
    /**
     * 行号
     */
    private Integer rowIndex;
    /**
     * 列号
     */
    private Integer colIndex;
    /**
     * 行高
     */
    private Float rowHeight;
    /**
     * 列宽
     */
    private Integer colWidth;

    private RowHeightColWidthModel(Builder builder) {
        this.sheetName = builder.sheetName;
        this.rowIndex = builder.rowIndex;
        this.colIndex = builder.colIndex;
        this.rowHeight = builder.rowHeight;
        this.colWidth = builder.colWidth;
    }

    public static class Builder {

        /**
         * sheet名称
         */
        private String sheetName;
        /**
         * 行号
         */
        private Integer rowIndex;
        /**
         * 列号
         */
        private Integer colIndex;
        /**
         * 行高
         */
        private Float rowHeight;
        /**
         * 列宽
         */
        private Integer colWidth;


        public Builder rowIndex(Integer rowIndex) {
            this.rowIndex = rowIndex;
            return this;
        }

        public Builder colIndex(Integer colIndex) {
            this.colIndex = colIndex;
            return this;
        }

        public Builder rowHeight(Float rowHeight) {
            this.rowHeight = rowHeight;
            return this;
        }

        public Builder colWidth(Integer colWidth) {
            this.colWidth = colWidth;
            return this;
        }

        public Builder(String sheetName) {
            this.sheetName = sheetName;
        }

        public RowHeightColWidthModel build() {
            return new RowHeightColWidthModel(this);
        }
    }

    /**
     * 创建隐藏行信息
     *
     * @param sheetName sheet页名称
     * @param rowIndex  行号
     * @return
     */
    public static RowHeightColWidthModel createHideRowModel(String sheetName, Integer rowIndex) {
        return createRowHeightColWidthModel(sheetName, rowIndex, 0f, null, null);
    }

    /**
     * 创建隐藏列信息
     *
     * @param sheetName sheet页名称
     * @param colIndex  列号
     * @return
     */
    public static RowHeightColWidthModel createHideColModel(String sheetName, Integer colIndex) {
        return createRowHeightColWidthModel(sheetName, null, null, colIndex, 0);
    }

    /**
     * 创建行高信息
     *
     * @param sheetName sheet页名称
     * @param rowIndex  行号
     * @param rowHeight 行高
     * @return
     */
    public static RowHeightColWidthModel createRowHeightModel(String sheetName, Integer rowIndex, Float rowHeight) {
        return createRowHeightColWidthModel(sheetName, rowIndex, rowHeight, null, null);
    }

    /**
     * 创建列宽信息
     *
     * @param sheetName sheet页名称
     * @param colIndex  列号
     * @param colWidth  列宽
     * @return
     */
    public static RowHeightColWidthModel createColWidthModel(String sheetName, Integer colIndex, Integer colWidth) {
        return createRowHeightColWidthModel(sheetName, null, null, colIndex, colWidth);
    }

    /**
     * 创建行高列宽信息
     *
     * @param sheetName sheet页名称
     * @param rowIndex  行号
     * @param rowHeight 行高
     * @param colIndex  列号
     * @param colWidth  列宽
     * @return
     */
    public static RowHeightColWidthModel createRowHeightColWidthModel(String sheetName, Integer rowIndex, Float rowHeight, Integer colIndex, Integer colWidth) {
        return new Builder(sheetName)
                //行号
                .rowIndex(rowIndex)
                //行高
                .rowHeight(rowHeight)
                //显示列号
                .colIndex(colIndex)
                //列宽
                .colWidth(colWidth)
                .build();
    }
}

(2)CustomVerticalCellStyleStrategy定义表头样式

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.AbstractVerticalCellStyleStrategy;

public class CustomVerticalCellStyleStrategy extends AbstractVerticalCellStyleStrategy {
    // 重写定义表头样式的方法
    @Override
    protected WriteCellStyle headCellStyle(Head head) {
        WriteCellStyle writeCellStyle = new WriteCellStyle();
        WriteFont writeFont = new WriteFont();
        writeFont.setBold(true);
        writeFont.setFontName("宋体");
        writeFont.setFontHeightInPoints((short) 11);
        writeCellStyle.setWriteFont(writeFont);
        return writeCellStyle;
    }

    // 重写定义内容部分样式的方法
    @Override
    protected WriteCellStyle contentCellStyle(Head head) {
        WriteCellStyle writeCellStyle = new WriteCellStyle();
        WriteFont writeFont = new WriteFont();
        writeFont.setBold(false);
        writeFont.setFontName("宋体");
        writeFont.setFontHeightInPoints((short) 11);
        writeCellStyle.setWriteFont(writeFont);
        return writeCellStyle;
    }
}

(3)CustomWidthStyleStrategy定制宽度样式策略

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.util.CollectionUtils;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CustomWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {

    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    /**
     * 设置列宽
     *
     * @param writeSheetHolder
     * @param cellDataList
     * @param cell
     * @param head
     * @param relativeRowIndex
     * @param isHead
     */
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }
                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }
            }
        }
    }

    /**
     * 数据长度
     *
     * @param cellDataList
     * @param cell
     * @param isHead
     * @return
     */
    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length + 1;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length * 2;
                    default:
                        return -1;
                }
            }
        }
    }
}

(4)CustomRowColHandler自定义行高列宽处理器

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;

public class CustomRowColHandler implements RowWriteHandler {

    /**
     * sheet页名称列表
     */
    private List<String> sheetNameList;
    /**
     * 列宽信息
     */
    private List<RowHeightColWidthModel> colWidthList = new ArrayList<>();

    /**
     * 行高信息
     */
    private List<RowHeightColWidthModel> rowHeightList = new ArrayList<>();


    /**
     * 自定义行高列宽适配器构造方法
     *
     * @param rowHeightColWidthList 行高列宽信息
     */
    public CustomRowColHandler(List<RowHeightColWidthModel> rowHeightColWidthList) {
        if (CollUtil.isEmpty(rowHeightColWidthList)) {
            return;
        }
        rowHeightColWidthList = rowHeightColWidthList.stream().filter(x ->
                StrUtil.isNotBlank(x.getSheetName())).collect(Collectors.toList());
        //填充行高信息
        this.rowHeightList = rowHeightColWidthList.stream().filter(x ->
                x.getRowIndex() != null && x.getRowIndex() >= 0 && x.getRowHeight() != null && x.getRowHeight() >= 0).collect(Collectors.toList());
        //填充列宽信息
        this.colWidthList = rowHeightColWidthList.stream().filter(x ->
                x.getColIndex() != null && x.getColIndex() >= 0 && x.getColWidth() != null && x.getColWidth() >= 0).collect(Collectors.toList());
        //获取sheet页名称
        sheetNameList = this.rowHeightList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());
        sheetNameList.addAll(this.colWidthList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList()));
        sheetNameList = sheetNameList.stream().distinct().collect(Collectors.toList());
    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row
            , Integer relativeRowIndex, Boolean isHead) {
        Sheet sheet = writeSheetHolder.getSheet();
        String sheetName = sheet.getSheetName();
        //不需要设置行高列宽，或者当前sheet页不需要设置行高列宽
        if ((CollUtil.isEmpty(rowHeightList) && CollUtil.isEmpty(colWidthList))
                || sheetNameList.contains(sheetName) == false) {
            return;
        }

        int currentRowNum = row.getRowNum();

        //获取当前sheet页当前行的行高信息
        List<RowHeightColWidthModel> sheetRowHeightList = rowHeightList.stream().filter(x ->
                StrUtil.equals(x.getSheetName(), sheetName)
                        && Objects.equals(x.getRowIndex(),currentRowNum)).collect(Collectors.toList());
        for (RowHeightColWidthModel rowHeightModel : sheetRowHeightList) {
            //行高
            Float rowHeight = rowHeightModel.getRowHeight();
            //设置行高
            if (rowHeight != null) {
                row.setHeightInPoints(rowHeight);
            }
        }
        //获取当前sheet页的列宽信息
        List<RowHeightColWidthModel> sheetColWidthList = colWidthList.stream().filter(x ->
                StrUtil.equals(x.getSheetName(), sheetName)).collect(Collectors.toList());
        for (RowHeightColWidthModel colWidthModel : sheetColWidthList) {
            //列号
            Integer colIndex = colWidthModel.getColIndex();
            //列宽
            Integer colWidth = colWidthModel.getColWidth();
            //设置列宽
            if (colIndex != null && colWidth != null) {
                sheet.setColumnWidth(colIndex, colWidth * 256);
            }
        }
        //删除已添加的行高信息
        rowHeightList.removeAll(sheetRowHeightList);
        //删除已添加的列宽信息
        colWidthList.removeAll(sheetColWidthList);
        //重新获取要添加的sheet页姓名
        sheetNameList = this.rowHeightList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());
        sheetNameList.addAll(this.colWidthList.stream().map(x ->  x.getSheetName()).distinct().collect(Collectors.toList()));
        sheetNameList = sheetNameList.stream().distinct().collect(Collectors.toList());
    }
}

(5)ruleVerifyHead()动态构建表头

private ExportHeader<RuleVerifyRecordEntity> ruleVerifyHead() {
        ExportHeader<RuleVerifyRecordEntity> modelDefinitionHistoryEntityExportHeader = new ExportHeader<>();
        //获取字段中文注释
        List<List<String>> list = new ArrayList<List<String>>();
        list.add(Arrays.asList("表名称"));
        list.add(Arrays.asList("异常信息"));
        list.add(Arrays.asList("执行状态"));
        list.add(Arrays.asList("调度信息"));
        list.add(Arrays.asList("开始时间"));
        list.add(Arrays.asList("结束时间"));
        modelDefinitionHistoryEntityExportHeader.setHeaders(list);
        return modelDefinitionHistoryEntityExportHeader;
    }

三、文件导出效果

---

总结

本次实现了excel文件的导出功能，下节接着实现xml文件和html文件的导出功能。