EasyExcel根据列值是否相等导出动态设置指定单元格CellStyle字体颜色，背景等

fei.y058

已于 2024-04-12 16:37:17 修改

阅读量988

点赞数 4

文章标签： java 服务器前端

于 2024-04-12 16:36:34 首次发布

本文链接：https://blog.csdn.net/Yang19950816/article/details/137685926

版权

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

    public static void init(HttpServletResponse response, String name) {
        try {
            response.setContentType(
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode(name, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-Disposition",
                "attachment;filename*=utf-8''" + fileName + ".xlsx");
        } catch (Exception ex) {
            log.warn("导出初始化失败，err={}", ex.getMessage());
        }
    }

首先，创建一个CustomWriteHandler类实现WriteHandler接口，在这个类中定义如何根据单元格值来改变CellStyle：

package com.canyue.guoyan.service.converter;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.ruoyi.common.utils.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;

import java.util.List;

@Slf4j
public class CustomWriteHandler implements CellWriteHandler {


    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (isHead) {
            return;
        }
        Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
        Row row = cell.getRow();
        //比较列1
        Cell rowPurchaseNum = row.getCell(14);
        //比较列2
        Cell rowSingNum = row.getCell(19);
        Cell rowSignAmount = row.getCell(20);
        CellStyle cellStyle = workbook.createCellStyle();
        if (rowPurchaseNum != null && rowSingNum != null && rowSignAmount!=null &&
                StringUtils.isNotBlank(rowPurchaseNum.getStringCellValue())&&
                StringUtils.isNotBlank(rowSingNum.getStringCellValue()) &&
                StringUtils.isNotBlank(rowSignAmount.getStringCellValue())&&
                !rowPurchaseNum.getStringCellValue().equals(rowSingNum.getStringCellValue())) {
            //背景颜色
            cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            //填充色纯色
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            Font font = workbook.createFont();
            //字体颜色
            font.setColor(IndexedColors.RED.getIndex());
            cellStyle.setFont(font);
            rowSingNum.setCellStyle(cellStyle);
            rowSignAmount.setCellStyle(cellStyle);
        } else {
            cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            cellStyle.setFillPattern(FillPatternType.NO_FILL);
            Font font = workbook.createFont();
            font.setColor(IndexedColors.BLACK.getIndex());
            cellStyle.setFont(font);
        }
        cell.setCellStyle(cellStyle);

    }
}

然后，在写入Excel的时候，注册这个handler：


    public void export(HttpServletResponse response, List<WorkbenchOrderDataExcel> collectDataExcels, String fileName) {
        try {
            ExcelUtils.init(response, fileName);
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            WriteFont headWriteFont = new WriteFont();
            conHead(headWriteCellStyle, headWriteFont);
            EasyExcel.write(response.getOutputStream(), WorkbenchOrderDataExcel.class)
                    .registerWriteHandler(new WorkbenchCellStyleHandler())
                    .autoCloseStream(Boolean.TRUE).sheet(fileName)
                    .doWrite(collectDataExcels);
        } catch (Exception e) {
            log.error("导出" + fileName + "错误", e);
        }
    }

在上面的代码中，WorkbenchOrderDataExcel应该替换为你的数据模型类，dataList是包含你的数据模型实例的列表，fileName是生成的Excel文件的名称。