网上找了些资料然后结合自己的实践,将这个功能实现了出来,直接上代码
- service层的部分代码如下
// 需要修改底色的行下标 start -----
List<Integer> backGroundIndex = new ArrayList<>(); // 存放需要修改背景色的行的下标
backGroundIndex.add(1); // 设置excel中第二行的背景色
// 自定义策略
Set<Integer> yellowRowsSet = new HashSet<>(backGroundIndex);
QualificationsDeclareCellWriteHandler customCellWriteHandler = new QualificationsDeclareCellWriteHandler(yellowRowsSet);
Set<String> excludeColumnFiledNames = new HashSet<String>();
excludeColumnFiledNames.add("pxqz");// excel中不导出 pxqz 这个字段
// 需要修改底色的行下标 end -----
try {
fileName = URLEncoder.encode(fileName, "utf-8");
response.addHeader("Access-Control-Allow-Origin", "*");
response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
response.setContentType("application/octet-stream; charset=UTF-8");
EasyExcel.write(response.getOutputStream(), BatchScoreVO.class)
// 自定义背景颜色策略
.registerWriteHandler(customCellWriteHandler)
.sheet("背景色标记").doWrite(resultList);
} catch (IOException e) {
e.printStackTrace();
}
- QualificationsDeclareCellWriteHandler工具类代码如下,通过覆写afterCellDispose方法给行标记背景色
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.BooleanUtils;
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.alibaba.excel.write.metadata.style.WriteCellStyle;
import org.apache.poi.ss.usermodel.*;
import java.util.List;
import java.util.Set;
public class QualificationsDeclareCellWriteHandler implements CellWriteHandler{
/**
* 标背景色行下标集合
*/
private final Set<Integer> colorRowIndex;
public QualificationsDeclareCellWriteHandler(Set<Integer> colorRowIndex) {
this.colorRowIndex = colorRowIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
// 源码context中row待优化:支持入参 获取需要字段的出现次数 从而设置背景颜色
// to do:(row.getCell(columnIndex)失效)
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// empty
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
/**
* @Description
* @author db
* @param writeSheetHolder, writeTableHolder, list, cell, head,
* relativeRowIndex:下标,从0开始(不包括顶行,即0是从第二行开始),
* isHead:是否是顶部第一行
* @return void
* @exception
* @date 2024/4/9 18:32
*/
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// CellStyle cellStyle = cell.getCellStyle();
if (!isHead && cell.getColumnIndex() <= 8 && colorRowIndex.contains(relativeRowIndex)) {
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
Font cellFont = workbook.createFont();
cellFont.setFontName("宋体");
cellStyle.setFont(cellFont);
cellStyle.setFillForegroundColor(IndexedColors.GOLD.getIndex());
cellStyle.setFillPattern(FillPatternType.FINE_DOTS);
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
cell.setCellStyle(cellStyle);
}
}
}
- 实现效果如下,这里我只给了第三行赋背景色(即除了顶行的数据行的第二行)