alibaba easyexcel导出
我们excel框架选用的是alibaba easyexcel。
最近有个新的需求,导出文件时,需要根据某一列的值来做导出颜色的处理,当这列的值=指定值时,要求这一整列都要变为红色。
看了下源码,重写这个填充完表格之后执行的方法afterCellDispose就可以了,下面附上代码:
package com.cy.sd.export;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.DateUtils;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
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.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicBoolean;
/**
* 工具类
*/
public class WriteCellDataUtil {
private static ThreadLocal<List<Cell>> cellsThreadLocal = new ThreadLocal<>();
private static ThreadLocal<Integer> cellIndexThreadLocal = new ThreadLocal<>();
/**
* @param fileName 文件名
* @param clazz 导出的列bean
* @param list 导出的数据
* @param sheetName sheet名称
* @param enhanceColumns 数组:增强列们,哪列想添加颜色验证显示
* @param specialVal 指定值status
*/
public static void writeCellDataWrite(HttpServletResponse response, String fileName, Class<?> clazz, List<?> list,
String sheetName, Object specialVal, Integer... enhanceColumns) throws Exception {
cellsThreadLocal.set(new ArrayList<>());
if (StringUtils.isBlank(fileName)) {
//当前日期
// fileName = DateUtils.format(new Date());
fileName = System.currentTimeMillis() + "";
}
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
// fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), clazz).registerWriteHandler(new CellWriteHandler() {
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
if (false == isHead) {
cellsThreadLocal.get().add(cell);
AtomicBoolean verifySpecialVal = new AtomicBoolean(false);
CellType cellType = Enum.valueOf(CellType.class, cell.getCellTypeEnum().name());
if (cellType.equals(CellType.NUMERIC) && specialVal instanceof Integer) {
verifySpecialVal.set(cell.getNumericCellValue() == (Integer) specialVal);
} else if (cellType.equals(CellType.STRING)) {
verifySpecialVal.set(cell.getStringCellValue().equals(specialVal));
}
for (Integer i : enhanceColumns) {
if (i == cell.getColumnIndex() && verifySpecialVal.get()) {
cellIndexThreadLocal.set(cell.getRowIndex());
cellsThreadLocal.get().forEach(item -> {
if (item.getRowIndex() == cell.getRowIndex()) {
item.setCellStyle(getCellStyle(writeSheetHolder));
}
});
}
}
if (null != cellIndexThreadLocal.get() && cellIndexThreadLocal.get().equals(cell.getRowIndex())) {
cell.setCellStyle(getCellStyle(writeSheetHolder));
}
}
}
}).sheet(sheetName).doWrite(list);
}
private static CellStyle getCellStyle(WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
Workbook workbook = sheet.getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setColor(IndexedColors.RED.getIndex());
cellStyle.setFont(font);
return cellStyle;
}
}
package com.cy.sd.export;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
/**
* VO
*/
@Data
public class GoodsExcelVO {
@ExcelProperty(value = "名称",index = 0)
private String goodsName;
@ExcelProperty(value = "价格",index = 1)
private Integer goodsPrice;
}
package com.cy.sd.controller;
import com.cy.sd.export.GoodsExcelVO;
import com.cy.sd.export.WriteCellDataUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
/**
* 控制层模拟调用
*/
@RestController
public class OderController {
@GetMapping("/index")
public void exePay(HttpServletResponse response) {
List<GoodsExcelVO> l = new ArrayList<>();
GoodsExcelVO g2 = new GoodsExcelVO();
g2.setGoodsName("1");
g2.setGoodsPrice(2);
l.add(g2);
GoodsExcelVO g1 = new GoodsExcelVO();
g1.setGoodsName("1");
g1.setGoodsPrice(1);
l.add(g1);
GoodsExcelVO g3 = new GoodsExcelVO();
g3.setGoodsName("已完成");
g3.setGoodsPrice(2);
l.add(g3);
GoodsExcelVO g4 = new GoodsExcelVO();
g4.setGoodsName("2");
g4.setGoodsPrice(4);
l.add(g4);
System.out.println(l);
try {
// 一列
WriteCellDataUtil.writeCellDataWrite(response, null, GoodsExcelVO.class, l, "sheetName", "已完成",
new Integer[]{0});
} catch (Exception e) {
}
}
}
下面是效果:
如果大家有什么建议,请留言。