Dynamic Cell Style Configuration Based on Column Values Using EasyExcel
<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.