Fading Coder

One Final Commit for the Last Sprint

Home > Notes > Content

Dynamic Cell Style Configuration Based on Column Values Using EasyExcel

Notes 1
<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("Export initialization failed, err={}" , ex.getMessage());
    }
}

Create a CustomWriteHandler class that implements the CellWriteHandler interface to define how to modify the cell style based on cell values:

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();
        // Compare column 1
        Cell rowPurchaseNum = row.getCell(14);
        // Compare column 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())) {
            // Background color
            cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            // Fill pattern
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            Font font = workbook.createFont();
            // Text color
            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);
    }
}

Register this handler when writing to Excel:

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("Export " + fileName + " error", e);
    }
}

In the above code, WorkbenchOrderDataExcel should be replaced with your data model class, collectDataExcels is the list of instances of your data model, and fileName is the name of the generated Excel file.

Related Articles

Designing Alertmanager Templates for Prometheus Notifications

How to craft Alertmanager templates to format alert messages, improving clarity and presentation. Alertmanager uses Go’s text/template engine with additional helper functions. Alerting rules referenc...

Deploying a Maven Web Application to Tomcat 9 Using the Tomcat Manager

Tomcat 9 does not provide a dedicated Maven plugin. The Tomcat Manager interface, however, is backward-compatible, so the Tomcat 7 Maven Plugin can be used to deploy to Tomcat 9. This guide shows two...

Skipping Errors in MySQL Asynchronous Replication

When a replica halts because the SQL thread encounters an error, you can resume replication by skipping the problematic event(s). Two common approaches are available. Methods to Skip Errors 1) Skip a...

Leave a Comment

Anonymous

◎Feel free to join the discussion and share your thoughts.