关于easyexcel导出二次处理数据的问题

詠逺、冭遙逺

已于 2023-05-09 17:00:18 修改

阅读量739

点赞数 2

分类专栏： easyexcel excel导出文章标签： java 开发语言

于 2023-05-08 09:51:29 首次发布

本文链接：https://blog.csdn.net/qq_48840792/article/details/130552330

版权

easyexcel 同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

excel导出

1 篇文章 0 订阅

订阅专栏

文章展示了如何在Java中使用EasyExcel库自定义Sheet创建后的处理，包括设置标题样式、添加项目信息、设置单元格样式以及创建单元格超链接。此外，还介绍了CellStyleWriteHandler和CustomCellWriteHandler类，用于处理单元格的样式和内容。代码示例在EasyExcel版本2.2.7下运行正常。

摘要由CSDN通过智能技术生成

/**
 * @author lh
 */
public class MyAfterSheetCreateHandler implements SheetWriteHandler {

    /**
     * 成员变量
     */
    private String projectName;
    private String purchaseUnit;
    private String purchaseNum;

    private LocalDateTime submitTime;

    // 构造函数
    public MyAfterSheetCreateHandler(String projectName, String purchaseUnit, String purchaseNum, LocalDateTime submiteTime) {
        this.projectName = projectName;
        this.purchaseUnit = purchaseUnit;
        this.purchaseNum = purchaseNum;
        this.submitTime = submiteTime;
    }
    // 新增方法，用于设置参数值
    public void setParams(String projectName, String purchaseUnit, String purchaseNum,LocalDateTime submitTime) {
        this.projectName = projectName;
        this.purchaseUnit = purchaseUnit;
        this.purchaseNum = purchaseNum;
        this.submitTime = submitTime;
    }
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        //设置标题
        Row row1 = sheet.createRow(0);
        row1.setHeight((short) 800);
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("食材预定计划");
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeight((short) 400);
        cellStyle.setFont(font);
        cell1.setCellStyle(cellStyle);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 8));

        //设置项目名称,采购单位,采购编号,提交日期
        Row row2 = sheet.createRow(1);
        row2.setHeight((short) 500);
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

        row2.createCell(0).setCellValue("项目名称："+projectName);
        row2.createCell(2).setCellValue("采购单位："+purchaseUnit);
        row2.createCell(4).setCellValue("采购编号："+purchaseNum);
        if (null != submitTime){
            row2.createCell(6).setCellValue("提交日期："+formatter.format(submitTime));
        }else {
            row2.createCell(6).setCellValue("提交日期：-");
        }
        // 合并第二行的2和3单元格（从第1行到第1行，从第1列到第2列）
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 1));
        // 合并第二行的2和3单元格（从第1行到第1行，从第1列到第2列）
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3));
        // 合并第二行的2和3单元格（从第1行到第1行，从第1列到第2列）
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 5));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 8));
    }


}

上述代码实现重写了SheetWriteHandler的方法，

beforeSheetCreate-------用户可以在sheet创建之前进行操作
afterSheetCreate--------用户可以在sheet创建之后进行操作


/**
 * 单元格样式写入处理器
 */
@Slf4j
public class CellStyleWriteHandler extends AbstractCellStyleStrategy {

    @Override
    protected void initCellStyle(Workbook workbook) {

    }

    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer integer) {
        Workbook workbook = cell.getSheet().getWorkbook();

        //设置单元格样式
        CellStyle cellStyle = workbook.createCellStyle();

        //设置字体
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setBold(true);
        font.setFontHeightInPoints((short) 14);
        cellStyle.setFont(font);

        // 设置对齐方式
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置单元格背景颜色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置自动换行
        cellStyle.setWrapText(true);
        // 设置单元格边框线
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        //将样式应用于单元格
        cell.setCellStyle(cellStyle);
    }

    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {

        // 这里可以对cell进行任何操作
        log.info("第{}行，第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex());
        Workbook workbook = cell.getSheet().getWorkbook();
        XSSFCellStyle style = (XSSFCellStyle)workbook.createCellStyle();
        // 设置对齐方式
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        if (cell.getRowIndex() != 2 && "color".equals(head.getFieldName())) {
            String colorString = cell.toString();
            // 设置底纹颜色

            Pattern pattern = Pattern.compile("rgba\\((\\d+),\\s*(\\d+),\\s*(\\d+),\\s*(\\d+(\\.\\d+)?)\\)");
            Matcher matcher = pattern.matcher(colorString);
            if (matcher.matches()) {
                // 设置RGB颜色代码
                int red = Integer.parseInt(matcher.group(1));
                int green = Integer.parseInt(matcher.group(2));
                int blue = Integer.parseInt(matcher.group(3));
                int alpha = Integer.parseInt(matcher.group(4));
                // 创建Color对象
                java.awt.Color color = new Color(red, green, blue,alpha);
                // 创建XSSFColor对象
                XSSFColor xssfColor = new XSSFColor(color);
                // 设置单元格的填充前景色
                style.setFillForegroundColor(xssfColor);
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                cell.setCellStyle(style);
                cell.setCellValue("");
            }
            else {
                cell.setCellStyle(style);
                cell.setCellValue("无");
            }
        }
        cell.setCellStyle(style);
    }

}

和上面类似，CellStyleWriteHandler继承实现重写AbstractCellStyleStrategy中的方法

setHeadCellStyle---------设置头部单元格样式

setContentCellStyle--------设置单元格的内容和样式

用户可以在这些方法中对对应的方法进行重写实现实际的业务逻辑

/**
 * 自定义拦截器
 *
 * @author 
 */
@Slf4j
public class CustomCellWriteHandler implements CellWriteHandler {


    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                 Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
        Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 这里可以对cell进行任何操作
        log.info("第{}行，第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex());
        if (cell.getRowIndex() != 2 && "color".equals(head.getFieldName())) {
            log.info("数据{}",cell);
        }
        //自定义拦截器。对第一行第一列的头超链接到:https://baidu.com
        if (isHead && cell.getColumnIndex() == 0) {
            CreationHelper createHelper =                         writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
            Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);
            hyperlink.setAddress("https://baidu.com");
            cell.setHyperlink(hyperlink);
        }
    }

}

类似，不在赘述

beforeCellCreate-------单元格创建前

afterCellCreate-------单元格创建后

afterCellDataConverted-----单元格中的数据转换后

afterCellDispose------单元处理完成后

提示：上述代码在版本2.2.7运行无误，上面的每个自定义拦截器类都需要在导出时注册才能生效，使用registerWriteHandler进行注册，支持多注册，下面代码注册了两个：

            MyAfterSheetCreateHandler myAfterSheetCreateHandler = new MyAfterSheetCreateHandler(reserve.getProjectName(),
                    reserve.getPurchaseUnit(),reserve.getPurchaseNum(),reserve.getCreateDateTime());
            myAfterSheetCreateHandler.setParams(reserve.getProjectName(),reserve.getPurchaseUnit(),reserve.getPurchaseNum(),reserve.getCreateDateTime());
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("食材预定", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), ReserveExportDto.class)
                    .sheet("食材预定")
                    .registerWriteHandler(myAfterSheetCreateHandler)
                    .registerWriteHandler(new CellStyleWriteHandler())
                    .relativeHeadRowIndex(2)
                    .doWrite(reserveExportDtos);

附maven依赖：

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

导出效果图

-------本人小白，如有说错，请见谅

詠逺、冭遙逺

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录