使用easyexcel动态生成excel导出模版总结

QTGOD

于 2024-08-23 17:58:37 发布

阅读量316

点赞数 7

文章标签： excel

本文链接：https://blog.csdn.net/QTGOD/article/details/141471020

版权

1.简单模版动态导出

public static void main(String[] args) throws IOException {
        FileOutputStream fileOutputStream = new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\easyexcel-export-user5.xlsx"));
        List<List<String>> headColumnMap = new ArrayList<>();
         //主体
        String title = "能源";
        headColumnMap.add(Lists.newArrayList(title, "设备编号"));
        headColumnMap.add(Lists.newArrayList(title, "关联表计"));
        headColumnMap.add(Lists.newArrayList(title, "投用日期"));
        headColumnMap.add(Lists.newArrayList(title, "备注"));
        headColumnMap.add(Lists.newArrayList(title, "是否启用"));
        //内容 需要一一对应
        List<List<Object>> dataList = new ArrayList<>();
        dataList.add(Lists.newArrayList("对应标号","未关联","2020-01-01","无备注","禁用"));

        byte[] excelFile = createExcelFile(headColumnMap, dataList, null, "cs");
//        byte[] excelFile = createWithSumExcelFile(headColumnMap, dataList, null, "cs", dataList.size(),1);
        fileOutputStream.write(excelFile);
        fileOutputStream.close();

    }

注意：数据要和标题进行对应

根据该工具类可以快速生成此类简单的excel导出数据模版

2.导出动态生成多层级excel

思路，该excel数据不同点在于层级名称

有多少层级，就展示多少层级，那么获取该数据最后一层级就可以算出需要导出多少层（可以通过FSD）等树的遍历获取最大深度

不同层级的数据存放位置不相同，其余为需要设置null占位

工具类主体


/**
 * DynamicEasyExcelExportUtils 动态表格工具
 *
 * @author Xujie
 * @version 2024/08/09 08:58
 **/
public class DynamicEasyExcelExportUtils {

    private static final Logger log = LoggerFactory.getLogger(DynamicEasyExcelExportUtils.class);

    private static final String DEFAULT_SHEET_NAME = "sheet1";


    /**
     * 生成不需要合计的excel文件
     *
     * @param excelHead     表头
     * @param excelRows      数据
     * @param mergeColumnNames 需要合并的列
     * @param sheetName       sheet名称
     * @return
     */
    public static byte[] createExcelFile(List<List<String>> excelHead, List<List<Object>> excelRows, List<String> mergeColumnNames, String sheetName) {
        try {
            if (CollectionUtils.isNotEmpty(excelHead)) {
                ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
                EasyExcel.write(outputStream).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                        .head(excelHead)
                        .registerWriteHandler(new CustomMergeStrategy(mergeColumnNames))
                        .registerWriteHandler(new AdaptiveColumnWidthStrategy())
                        .registerWriteHandler(getHorizontalCellStyleStrategy())
                        .sheet(sheetName)
                        .doWrite(excelRows);
                return outputStream.toByteArray();
            }
        } catch (Exception e) {
            log.error("动态生成excel文件失败，headColumns：" + JSONArray.toJSONString(excelHead) + "，excelRows：" + JSONArray.toJSONString(excelRows), e);
        }
        return null;
    }

    /**
     * 生成需要合计的excel文件
     *
     * @param excelHead       表头
     * @param excelRows      数据
     * @param mergeColumnNames 需要合并的列
     * @param sheetName      sheet名称
     * @param rowIndex         第几行是合计
     * @param colMergeLength   合计后需要合并多少列不包括合计那一列
     */
    public static byte[] createWithSumExcelFile(List<List<String>> excelHead, List<List<Object>> excelRows, List<String> mergeColumnNames, String sheetName, int rowIndex, int colMergeLength) {
        try {
            if (CollectionUtils.isNotEmpty(excelHead)) {
                ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
                EasyExcel.write(outputStream).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                        .head(excelHead)
                        .registerWriteHandler(new CustomMergeStrategy(mergeColumnNames))
                        .registerWriteHandler(new AdaptiveColumnWidthStrategy())
                        .registerWriteHandler(new ColMergeStrategy(true, rowIndex, colMergeLength))
                        .registerWriteHandler(getHorizontalCellStyleStrategy())
                        .sheet(sheetName)
                        .doWrite(excelRows);
                return outputStream.toByteArray();
            }
        } catch (Exception e) {
            log.error("动态生成excel文件失败，headColumns：{}，excelRows：{}", JSONArray.toJSONString(excelHead), JSONArray.toJSONString(excelRows), e);
        }
        return null;
    }

    /**
     * 导出样式居中
     *
     * @author Xujie
     * @date 2024/8/12 上午11:33
     */
    public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 设置头字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 13);
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 设置垂直居中
        contentWriteCellStyle.setVerticalAlignment(org.apache.poi.ss.usermodel.VerticalAlignment.CENTER);
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 11);
        contentWriteFont.setFontName("宋体");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 导出文件测试
     *
     * @param args
     * @throws IOException
     */
    public static void main(String[] args) throws IOException {
        FileOutputStream fileOutputStream = new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\easyexcel-export-user5.xlsx"));
        List<List<String>> headColumnMap = new ArrayList<>();
        headColumnMap.add(Lists.newArrayList("支路编号"));
        headColumnMap.add(Lists.newArrayList("支路名称"));
        headColumnMap.add(Lists.newArrayList("00:00"));
        headColumnMap.add(Lists.newArrayList("00:10"));
        headColumnMap.add(Lists.newArrayList("总计"));
        List<List<Object>> dataList = new ArrayList<>();
        dataList.add(Lists.newArrayList("1", "支路1", "100", "100", "1000"));
        dataList.add(Lists.newArrayList("2", "支路2", "200", "2000", "2000"));
        byte[] excelFile = createExcelFile(headColumnMap, dataList, null, "cs");
        fileOutputStream.write(excelFile);
        fileOutputStream.close();

    }

}

合并策略类

/**
 * CustomMergeStrategy excel到处单元格相同合并策略
 *
 * @author Xujie
 * @version 2024/08/09 09:06
 **/
public class CustomMergeStrategy implements CellWriteHandler {

    private List<String> mergeColumnNames = null;


    public CustomMergeStrategy(List<String> mergeColumnNames) {
        this.mergeColumnNames = mergeColumnNames;
    }

    public CustomMergeStrategy() {
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 检验：是否表头
        if (isHead) {
            return;
        }
//         检验：是否第一行
        if (relativeRowIndex == 1) {
            return;
        }
        // 检验：是否设置合并列策略
        if (mergeColumnNames == null) {
            return;
        }
        String headName = head.getHeadNameList().get(0);

        if (!mergeColumnNames.contains(headName)) {
            return;
        }
        // 获取当前行数据
        Sheet sheet = cell.getSheet();
        int rowIndexCurrent = cell.getRowIndex();
        int rowIndexPrev = rowIndexCurrent - 1;

        Row rowCurrent = sheet.getRow(rowIndexCurrent);
        Row rowPrev = sheet.getRow(rowIndexPrev);
        if (rowPrev == null) {
            return;
        }
        Cell cellPrev = rowPrev.getCell(cell.getColumnIndex());

        // 获取当前当前单元和上一单元数据 默认字符串
        String cellValueCurrent = cell.getStringCellValue();
        String cellValuePrev = cellPrev.getStringCellValue();

        //是否相等
        if (!cellValueCurrent.equals(cellValuePrev)) {
            return;
        }

        //合并
        Boolean isMerged = false;
        //获取以及合并的区域
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
        for (int i = 0; i < mergedRegions.size(); i++) {
            CellRangeAddress cellRangeAddress = mergedRegions.get(i);
            if (cellRangeAddress.isInRange(rowIndexPrev, cell.getColumnIndex())) {
                //删除之前的
                sheet.removeMergedRegion(i);
                // 合并单元格
                cellRangeAddress.setLastRow(cell.getRowIndex());
                sheet.addMergedRegion(cellRangeAddress);
                isMerged = true;
                break;
            }
        }
        if (!isMerged) {
            // 合并单元格
            sheet.addMergedRegion(new CellRangeAddress(rowIndexPrev, rowIndexCurrent, cell.getColumnIndex(), cell.getColumnIndex()));
        }


    }
}

表头格式设置

/**
 * AdaptiveColumnWidthStrategy 表头自适应列宽
 *
 * @author Xujie
 * @version 2024/08/12 10:38
 **/
public class AdaptiveColumnWidthStrategy extends AbstractColumnWidthStyleStrategy {

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        //表头自适应列宽
        if (isHead) {
            int length = cell.getStringCellValue().getBytes().length;
            if (length < 5) {
                length = 5;
            }
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), length * 400);
        }
    }
}