关于easyexcel:EasyExcel解析动态表头及导出

前言

excel 文件导入及导出，是日常开发中常常遇到的需要。本次笔者以 EasyExcel 为例，针对在我的项目中遇到的 动静表头解析及导出 的场景，具体介绍具体的代码实现过程。

参考地址

https://github.com/alibaba/ea…

前端下载

  const download = () => {
    axios({
      method: 'GET',
      url: config.http.baseUrl + '/templateDownload',
      responseType: 'blob',
    })
      .then(function (res) {
      const content = res.data
      const blob = new Blob([content], {type: "application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"})
      const downloadElement = document.createElement("a");
      const href = window.URL.createObjectURL(blob);
      downloadElement.href = href;
      downloadElement.download = decodeURI(res.headers['filename']);
      document.body.appendChild(downloadElement);
      downloadElement.click();
      document.body.removeChild(downloadElement); // 下载实现移除元素
      window.URL.revokeObjectURL(href); // 开释掉 blob 对象
    })
  }

模板下载

excel 文件导入性能，经常须要进行模板下载，在 springboot 我的项目中，程序是以 jar 包的模式运行的，所以有很多小伙伴经常

遇到在 本地开发中可能实现下载性能，但部署到服务器的时候，找不到模板文件 的问题。

@Override
public void templateDownload(HttpServletResponse response, HttpServletRequest request) {
    // 获取要下载的模板名称
    String fileName = "批量导入模板.xlsx";
    // 获取文件下载门路
    String filePath = "/template/template.xlsx";
    TemplateDownloadUtil.download(response, request, fileName, filePath);
}

import lombok.extern.slf4j.Slf4j;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;

/**
 * 模板文件下载工具类
 * @author 
 * @date 2021/05/20 9:20
 */
@Slf4j
public class TemplateDownloadUtil {public static void download(HttpServletResponse response, HttpServletRequest request,String fileName,String filePath){
        try {response.setContentType("application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里 URLEncoder.encode 能够避免中文乱码 当然和 easyexcel 没有关系
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setHeader("filename", URLEncoder.encode(fileName, "UTF-8"));
            response.setHeader("Access-Control-Expose-Headers", "filename,Content-Disposition");
          
            // 获取文件的门路，此形式本地开发能够运行，服务器无奈获取文件
//            String filePath = getClass().getResource("/template/template.xlsx").getPath();
//            FileInputStream input = new FileInputStream(filePath);
          
              // 在服务器中可能读取到模板文件
            ClassPathResource resource = new ClassPathResource(filePath);
            InputStream input = resource.getInputStream();
            OutputStream out = response.getOutputStream();
            byte[] b = new byte[2048];
            int len;
            while ((len = input.read(b)) != -1) {out.write(b, 0, len);
            }
            // 修改 Excel 在“xxx.xlsx”中发现不可读取的内容。是否复原此工作薄的内容？如果信赖此工作簿的起源，请点击 "是"
//            response.setHeader("Content-Length", String.valueOf(input.getChannel().size()));
            input.close();} catch (Exception e) {log.error("下载模板失败 :", e);
        }
    }

}

EasyExcel 动静表头解析

EasyExcel 简略的读文件，官网中曾经有具体的阐明，本文不再赘述，具体操作参见官网。

本文次要针对笔者遇到的简单表头及动静表头进行解说。

模板示例

解析

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 发薪单上传 excel 读取类
 *
 * @author yupf
 * @description Listener 不能被 spring 治理，要每次读取 excel 都要 new, 而后外面用到 spring 能够构造方法传进去
 */
@Slf4j
@Data
public class BatchReadListener extends AnalysisEventListener<Map<Integer, String>> {

    /**
     * 每隔 500 条存储数据库，而后清理 list，不便内存回收
     */
    private static final int BATCH_COUNT = 500;
    //Excel 数据缓存构造
    private List<Map<Integer, Map<Integer, String>>> list = new ArrayList<>();
    //Excel 表头（列名）数据缓存构造
    private Map<Integer, String> headTitleMap = new HashMap<>();


    /**
     * 假如这个是一个 DAO，当然有业务逻辑这个也能够是一个 service。当然如果不必存储这个对象没用。*/
    private DbFileBatchService dbFileBatchService;
    private DbFileContentService dbFileContentService;
    private FileBatch fileBatch;
    private int total = 0;

    /**
     * 如果应用了 spring, 请应用这个构造方法。每次创立 Listener 的时候须要把 spring 治理的类传进来
     */
    public BatchReadListener(DbFileBatchService dbFileBatchService, DbFileContentService dbFileContentService, FileBatch fileBatch) {
        this.dbFileBatchService = dbFileBatchService;
        this.dbFileContentService = dbFileContentService;
        this.fileBatch = fileBatch;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {log.info("解析到一条数据:{}", JSON.toJSONString(data));
        total++;
        Map<Integer, Map<Integer, String>> map = new HashMap<>();
        map.put(context.readRowHolder().getRowIndex(), data);
        list.add(map);
        // 达到 BATCH_COUNT 了，须要去存储一次数据库，避免数据几万条数据在内存，容易 OOM
        if (list.size() >= BATCH_COUNT) {saveData();
            // 存储实现清理 list
            list.clear();}
    }

    /**
     * 所有数据解析实现了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保留数据，确保最初遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析实现！");
    }

    /**
     * 解析表头数据
     **/
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {log.info("表头数据：{}", JSONObject.toJSONString(headMap));
        headTitleMap = headMap;
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {log.info("{}条数据，开始存储数据库！", list.size());
        FileContent fileContent = null;
        List<FileContent> fileContentList = list.stream().flatMap(integerMap -> integerMap.entrySet().stream().map(entrySet -> {//entrySet.getKey()获取的是内容的 RowIndex, 理论的行数须要依据表头数进行解决
                Integer rowIndex = entrySet.getKey();
                Map<Integer, String> value = entrySet.getValue();
                log.info(JSONObject.toJSONString(value));
                fileContent = new FileContent();
                fileContent.setBatchId(fileBatch.getId());
                fileContent.setBatchNo(fileBatch.getBatchNo());
                // 固定字段入库
                fileContent.setName(value.get(0) != null ? value.get(0).trim() : "");
                fileContent.setCertNo(value.get(1) != null ? value.get(1).trim() : "");
                fileContent.setRealAmount(value.get(2) != null ? value.get(2).trim() : "");
                // 所有动静表头数据转为 JSON 串入库
                fileContent.setFieldsValue(JSONObject.toJSONString(value));
                // 取理论的内容 rowIndex
                fileContent.setRowNum(rowIndex + 1);
                fileContent.setCreateTime(LocalDateTime.now());
                return xcSalaryFileContent;
            }
        )).collect(Collectors.toList());
        log.info(JSONObject.toJSONString(fileContentList));
        dbFileContentService.saveBatch(fileContentList);
        log.info("存储数据库胜利！");
    }

}

  BatchReadListener listener = new BatchReadListener(dbFileBatchService, dbFileContentService, fileBatch);
    try {
        // 注：headRowNumber 默认为 1，现赋值为 2，即从第三行开始读取内容
        EasyExcel.read(fileInputStream, listener).headRowNumber(2).sheet().doRead();
    } catch (Exception e) {log.info("EasyExcel 解析文件失败，{}", e);
        throw new CustomException("文件解析失败，请从新上传");
    }
    // 获取表头信息进行解决
    Map<Integer, String> headTitleMap = listener.getHeadTitleMap();
    // 获取动静表头信息
    List<String> headList = headTitleMap.keySet().stream().map(key -> {String head = headTitleMap.get(key);
        log.info(head);
        return head == null ? "": head.replace("*","");
    }).collect(Collectors.toList());
    // 能够对表头进行入库保留，不便后续导出

综上，动静表头即可实现解析。

EasyExcel 动静表头导出

导出示例

获取动静头

     private List<List<String>> getFileHeadList(FileBatch fileBatch) {String head = fileBatch.getFileHead();
         List<String> headList = Arrays.asList(head.split(","));
         List<List<String>> fileHead = headList.stream().map(item ->     concatHead(Lists.newArrayList(item))).collect(Collectors.toList());
         fileHead.add(concatHead(Lists.newArrayList("备注")));
         return fileHead;
     }

    /**
     * 填写须知
     * @param headContent
     * @return
     */
    private List<String> concatHead(List<String> headContent) {
        String remake = "填写须知：\n" +
                "1. 零碎自动识别 Excel 表格，表头必须含有“企业账户号”、“企业账户名”、“实发金额”；\n" +
                "2. 带“*”为必填字段，填写后能力上传胜利；\n" +
                "3. 若需上传其余表头，可自行在“实发金额”后增加表头，表头最多可增加 20 个，表头名称请管制在 8 个字以内；\n" +
                "4. 填写的表头内容不可超过 30 个字；\n" +
                "5. 实发金额反对填写到 2 位小数；\n" +
                "6. 每次导入数据不超过 5000 条。\n" +
                "\n" +
                "注：请勿删除填写须知，删除后将导致文件上传失败 \n" +
                "\n" +
                "表头示例：";
        headContent.add(0, remake);
        return headContent;
    }

获取数据

    List<FileContent> fileContentList = dbFileContentService.list(Wrappers.<FileContent>lambdaQuery()
        .eq(FileContent::getBatchId, fileBatch.getId())
        .orderByAsc(FileContent::getRowNum)
    );
    List<List<Object>> contentList = fileContentList.stream().map(fileContent -> {List<Object> rowList = new ArrayList<>();
        String fieldsValue = fileContent.getFieldsValue();
        JSONObject contentObj = JSONObject.parseObject(fieldsValue);
        for (int columnIndex = 0 , length = headList.size(); columnIndex < length; columnIndex++) {Object content = contentObj.get(columnIndex);
            rowList.add(content == null ? "" : content);
        }
        rowList.add(fileContent.getCheckMessage());
        return rowList;
    }).collect(Collectors.toList());

单元格格局设置

import com.alibaba.excel.metadata.data.DataFormatData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.util.List;

/**
 * 设置表头和填充内容的款式
 */
public class CellStyleStrategy extends HorizontalCellStyleStrategy {

    private final WriteCellStyle headWriteCellStyle;
    private final WriteCellStyle contentWriteCellStyle;

    /**
     * 操作列
     */
    private final List<Integer> columnIndexes;

    public CellStyleStrategy(List<Integer> columnIndexes,WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {
        this.columnIndexes = columnIndexes;
        this.headWriteCellStyle = headWriteCellStyle;
        this.contentWriteCellStyle = contentWriteCellStyle;
    }

    // 设置头款式
    @Override
    protected void setHeadCellStyle(CellWriteHandlerContext context) {
        // 获取字体实例
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("宋体");
        // 表头不同解决
        if (columnIndexes.get(0).equals(context.getRowIndex())) {headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
            headWriteFont.setFontHeightInPoints((short) 12);
            headWriteFont.setBold(false);
            headWriteFont.setFontName("宋体");
        }else{headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            headWriteFont.setFontHeightInPoints((short) 11);
            headWriteFont.setBold(false);
            headWriteFont.setFontName("微软雅黑");
        }
        headWriteCellStyle.setWriteFont(headWriteFont);
        DataFormatData dataFormatData = new DataFormatData();
        dataFormatData.setIndex((short)49);
        headWriteCellStyle.setDataFormatData(dataFormatData);
        if (stopProcessing(context)) {return;}
        WriteCellData<?> cellData = context.getFirstCellData();
        WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle());
    }

    // 设置填充数据款式
    @Override
    protected void setContentCellStyle(CellWriteHandlerContext context) {WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 11);
        // 设置数据填充后的实线边框
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        DataFormatData dataFormatData = new DataFormatData();
        dataFormatData.setIndex((short)49);
        contentWriteCellStyle.setDataFormatData(dataFormatData);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        WriteCellData<?> cellData = context.getFirstCellData();
        WriteCellStyle.merge(contentWriteCellStyle, cellData.getOrCreateStyle());
    }
}

行高设置

import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Row;

/**
 * 设置表头的主动调整行高策略
 */
public class CellRowHeightStyleStrategy extends AbstractRowHeightStyleStrategy {

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
        // 设置主题目行高为 17.7
        if(relativeRowIndex == 0){
            // 如果 excel 须要显示行高为 15，那这里就要设置为 15*20=300
            row.setHeight((short) 3240);
        }
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {}}

列宽度自适应

如果是简略表头，能够应用 EasyExcel 中的 LongestMatchColumnWidthStyleStrategy()来实现。

EasyExcel.write(fileName, LongestMatchColumnWidthData.class)
    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("模板").doWrite(dataLong());

如果是简单表头，就须要本人来实现，代码如下：

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 lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author yupf
 * @description
 * @date 2022/9/7 18:48
 */
@Slf4j
public class CellWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
        if (maxColumnWidthMap == null) {maxColumnWidthMap = new HashMap<>();
            CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
        }
        if (isHead) {if(relativeRowIndex.intValue() == 1){Integer length = cell.getStringCellValue().getBytes().length;
                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || length > maxColumnWidth) {maxColumnWidthMap.put(cell.getColumnIndex(), length);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), length * 300);
                }
            }
        }else{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);
                }
            }
        }
    }

    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;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

写入文件

EasyExcel.write(response.getOutputStream())
    .head(head)
    .registerWriteHandler(new CellRowHeightStyleStrategy())   // 设置行高的策略
    .registerWriteHandler(new CellStyleStrategy(Arrays.asList(0,1),new WriteCellStyle(), new WriteCellStyle()))
    .registerWriteHandler(new CellWidthStyleStrategy())
    .sheet(sheetName)
    .doWrite(list);

总结

以上便是 EasyExcel 解析动静表头及导出的整个过程。

在应用过程中，笔者的感触是，上手难度很低，很适宜老手去做简略的表格解析，当然，如果你的需要有简单的格局，EasyExcel 也提供了 api，可能很好的满足需要。