alibaba easyexcel导出

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) {

        }
    }

}

​   下面是效果：

如果大家有什么建议，请留言。