背景
如题
在业务中经常遇到这样的需求, 上传excel后对数据进行校验, 如果校验不通过, 需要提供excel下载, 并在后面一列提供错误原因.
常见的做法是在导出的方法中, 加一个字段然后在list中, 然后sheet写到返回流中. 但是如果需要统一样式就比较麻烦, 且每次都需要写这么一串代码.
SheetWriteHandler
我使用的阿里的EasyExcel. 它提供了一个处理器来处理sheet, 相当于拦截器
/**
* intercepts handle sheet creation
*
* @author Jiaju Zhuang
*/
public interface SheetWriteHandler extends WriteHandler {
/**
* Called before create the sheet
*
* @param writeWorkbookHolder
* @param writeSheetHolder
*/
void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder);
/**
* Called after the sheet is created
*
* @param writeWorkbookHolder
* @param writeSheetHolder
*/
void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder);
}
于是我们可以在afterSheetCreate方法中做文章, 统一做处理, 包括实现其他的功能
写一个类来实现这个处理器
/**
* 自定义拦截器.新增注释,第一行头加批注
*
*/
@Slf4j
public class AddErrColWriteHandler implements SheetWriteHandler {
/**
* 第几行
*/
private final Integer headIdx;
private Integer colIdx;
private final String errTitle;
private Map<Integer, String> errMap;
/**
* @param headIdx 标题行,为了符合easyexcel的习惯,下标从1开始
* @param colIdx 错误所在列. 下标从0开始. 如果没指定,自动取标题行的下一列
* @param errTitle
* @param errMap 错误信息,key是内容list的下标(为了方便list遍历时传值),最终它的行是 headIdx+errMap+1
*/
public AddErrColWriteHandler(Integer headIdx, Integer colIdx, String errTitle, Map<Integer, String> errMap) {
this.headIdx = headIdx-1;
this.colIdx = colIdx;
this.errTitle = errTitle;
this.errMap = errMap;
}
/**
* @param headIdx 标题行,为了符合easyexcel的习惯,下标从1开始
* @param errTitle
* @param errMap 错误信息,key是内容list的下标,从0开始(为了方便list遍历时传值),最终它的行是 headIdx+errMap+1
*/
public AddErrColWriteHandler(Integer headIdx, String errTitle, Map<Integer, String> errMap) {
this.headIdx = headIdx-1;
this.errTitle = errTitle;
this.errMap = errMap;
}
/**
* 表格样式
*/
private CellStyle cellStyle;
private CellStyle cellStyleHeader;
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Row row = writeSheetHolder.getCachedSheet().getRow(headIdx);
if (row == null) {
row = writeSheetHolder.getCachedSheet().createRow(headIdx);
}
if (colIdx == null){
short lastCellNum = row.getLastCellNum();
colIdx = (int) lastCellNum;
}
Cell cell = row.getCell(colIdx);
if (cell == null) {
cell = row.createCell(colIdx);
}
Workbook workbook = writeWorkbookHolder.getWorkbook();
// 表头样式
if (cellStyleHeader == null) {
cellStyleHeader = workbook.createCellStyle();
cellStyleHeader.setAlignment(HorizontalAlignment.CENTER);
cellStyleHeader.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyleHeader.setFillForegroundColor(IndexedColors.WHITE.getIndex());
Font font = workbook.createFont();
font.setFontName("微软雅黑");
font.setColor((short) 10);
font.setFontHeightInPoints((short) 12);
cellStyleHeader.setFont(font);
}
cell.setCellStyle(cellStyleHeader);
cell.setCellValue(errTitle);
// 内容样式
if (cellStyle == null) {
cellStyle = workbook.createCellStyle();
// cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
Font font = workbook.createFont();
font.setFontName("微软雅黑");
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);
}
Sheet sheet = writeSheetHolder.getCachedSheet();
for (Map.Entry<Integer, String> en : errMap.entrySet()) {
int rowIdx = en.getKey() + headIdx + 1;
Row row0 = sheet.getRow(rowIdx);
if (row0 == null) {
row0 = sheet.createRow(rowIdx);
}
// 第几列。我这里是1.正常业务根据需求传递
Cell cell0 = row0.getCell(colIdx);
if (cell0 == null) {
cell0 = row0.createCell(colIdx);
}
cell0.setCellStyle(cellStyle);
cell0.setCellValue(en.getValue());
}
}
}
使用
//在生成导出的数据list方法中
Map<Integer, String> errMap = new HashMap<>();
for (int i = 0; i < list.size(); i++) {
if(错误){
errMap.put(i, errmsg);
}
}
/**
* @param title 错误列的标题名
* @param headIdx 标题所在行,为了符合easyexcel的习惯,下标从1开始
* @param errMsgMap 错误信息,key是内容list的下标,从0开始(为了方便list遍历时传值),最终它的行是 headIdx+errMap+1
*/
EasyExcel.write(os)
.withTemplate(is)
.registerWriteHandler(new AddErrColWriteHandler(headIdx,title,errMsgMap))
.sheet()
.doWrite(new ArrayList());
}));
