动态生成excel动态表头easyExcel

动态生成excel，内容跟随表头填充

 

组装调用工具方法：

List<FileTitleEntity> titleEntityList = new ArrayList<>();
        titleEntityList.add(new FileTitleEntity("name", "姓名"));
        titleEntityList.add(new FileTitleEntity("idNumber", "证件号码"));
        titleEntityList.add(new FileTitleEntity("projectDistrict", "参与项目行政区划"));
        titleEntityList.add(new FileTitleEntity("nationality", "民族"));
        titleEntityList.add(new FileTitleEntity("address", "住址"));
        titleEntityList.add(new FileTitleEntity("phoneNumber", "联系电话"));
        titleEntityList.add(new FileTitleEntity("subsidyAmount", "补贴金额(元)"));
        titleEntityList.add(new FileTitleEntity("bankCategory", "银行类别"));
        titleEntityList.add(new FileTitleEntity("accountName", "开户姓名"));
        titleEntityList.add(new FileTitleEntity("bankAccount", "银行账号"));
        titleEntityList.add(new FileTitleEntity("remarks", "备注"));
        List<Map<String, Object>> sheetMapList = new ArrayList<>();
        Map<String, Object> sheetOne = new HashMap<>();
        sheetOne.put("sheetName", "Sheet1");
        sheetOne.put("sourceList", "组装的List<String,String>数组");

        sheetOne.put("titleEntityList", titleEntityList);
        sheetMapList.add(sheetOne);

try {
    FileUtils.exportDynamicExcel(response, "测试模板.xlsx", sheetMapList);
} catch (Exception e) {
    e.printStackTrace();
    throw new RuntimeException("系统导出异常，请联系管理员！");
}

组装的List<String,String>数组，如下：

List<Map<String, String>> sourceThreeList = regionExcels.stream().map(template->{
    Map<String, String> map = new HashMap<>();
    map.put("姓名", template.getRegionId());
    map.put("证件号码", template.getRegionName());
    return map;
}).collect(Collectors.toList());

 

 

自定义列宽：

import com.alibaba.excel.metadata.Head;
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 java.util.List;



public class ExcelColumnWidthHandler extends AbstractColumnWidthStyleStrategy {

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (Boolean.TRUE.equals(isHead)) {
            int columnWidth = cell.getStringCellValue().length();
            columnWidth = Math.max(columnWidth * 5, 20);
            if (columnWidth > 255) {
                columnWidth = 255;
            }
            if(cell.getStringCellValue().equals("模版使用说明")){
                writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(),150 * 255);
            }else{
                writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
            }
        }

    }
}

 

 

优化后的工具类：

/**
     * 动态Excel导出，根据headMap表头顺序组装sourceList对应内容，再导出
     *
     * @param response     response
     * @param fileName     文件名
     * @param sheetMapList sheet页相关内容:
     *                     sheetName名称
     *                     titleEntityList    动态表头对象(titleEntity),例如：{"title":"name","titleName":"姓名"}
     *                     sourceList 原数据List，例如：{"姓名":"张三"}
     */
    public static void exportDynamicExcel(HttpServletResponse response, String fileName, List<Map<String, Object>> sheetMapList) throws Exception {

        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Transfer-Encoding", "binary");
        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
        response.setHeader("Pragma", "public");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
        fileName = new String(fileName.getBytes(), "ISO-8859-1");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

        //初始化输出流
        ExcelWriter writer;
        try {

            // 创建并配置写入样式
            WriteCellStyle contentStyle = new WriteCellStyle();
            // 启用自动换行
            contentStyle.setWrapped(true);

            // 应用样式到特定列或所有列
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(contentStyle, contentStyle);

            writer = EasyExcel.write(response.getOutputStream()).build();


            sheetMapList.forEach(sheetMap -> {
                String sheetName = sheetMap.get("sheetName").toString();
                List<Map<String, String>> sourceList = (List<Map<String, String>>) sheetMap.get("sourceList");
                List<FileTitleEntity> titleEntityList = (List<FileTitleEntity>) sheetMap.get("titleEntityList");

                // 设置动态表头对象
                List<List<String>> head = new ArrayList<>();

                // 设置对应表头转换属性名列表
                List<String> headRow = new ArrayList<>();
                titleEntityList.forEach(titleEntity -> {
                    headRow.add(titleEntity.getTitleName());
                });

                // 列表设置列头
                headRow.forEach(item -> {
                    List<String> list = new ArrayList<>();
                    list.add(item);
                    head.add(list);
                });
                // 构造数据列表
                List<List<Object>> excelData = new ArrayList<>();
                if (CollectionUtils.isNotEmpty(sourceList)) {
                    for (Map<String, String> map : sourceList) {
                        List<Object> row = new ArrayList<>();
                        titleEntityList.forEach(titleEntity -> {
                            String value = map.get(titleEntity.getTitleName());
                            // 转换为字符串，如果为null则填充空字符串
                            row.add(StringUtils.isNotEmpty(value) ? value : "");
                        });
                        excelData.add(row);
                    }
                }
//                writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
//            EasyExcel.write(response.getOutputStream()).head(head).registerConverter(new LongStringConverter()).sheet(sheetName).doWrite(excelData);

                writer.write(excelData, EasyExcel.writerSheet(sheetName).head(head).registerWriteHandler(new ExcelColumnWidthHandler()).registerWriteHandler(horizontalCellStyleStrategy).registerConverter(new LongStringConverter()).build());
            });
            writer.finish();
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                response.flushBuffer();
            } catch (IOException ignored) {
            }
        }
    }

 

 

 

工具方法：

/**
     * 动态Excel导出，先sourceList转换成List<targetClass>，再导出
     *
     * @param response    response
     * @param fileName    文件名
     * @param sheetName   sheetName
     * @param headMap     动态表头对象(Map<String,String>),例如：{"id":"ID","name":"姓名"}
     * @param sourceList  原数据List，例如：{"ID":"1","姓名":"张三"}
     */
    public static void exportDynamicExcel(HttpServletResponse response, String fileName, String sheetName, List<Map<String, String>> sourceList, Map<String,String> headMap) throws Exception {

//        response.setContentType("application/vnd.ms-excel");
//        response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
//        response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1) + ".xlsx");

        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Transfer-Encoding", "binary");
        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
        response.setHeader("Pragma", "public");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
        fileName = new String(fileName.getBytes(), "ISO-8859-1");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);




        // 设置动态表头对象
        List<List<String>> head = new ArrayList<>();

        // 设置对应表头转换属性名列表
//        List<String> headPropertyList = new ArrayList<>();
        List<String> headRow = new ArrayList<>();
        headMap.forEach((k, v) -> {
            headRow.add(v);
        });

        // 列表设置列头
        headRow.forEach(item -> {
            List<String> list = new ArrayList<>();
            list.add(item);
            head.add(list);
        });
        // 构造数据列表
        List<List<Object>> excelData = new ArrayList<>();
        for (Map<String, String> map : sourceList) {
            List<Object> row = new ArrayList<>();
            headMap.forEach((k, v) -> {
                String value = map.get(k);
                // 转换为字符串，如果为null则填充空字符串
                row.add(StringUtils.isNotEmpty(value) ? value : "");
            });
            excelData.add(row);
        }

        EasyExcel.write(response.getOutputStream()).head(head).registerConverter(new LongStringConverter()).sheet(sheetName).doWrite(excelData);
    }