[开发测试]EasyExcel渲染单个单元格.

目录

业务逻辑:

EasyExcel的pom依赖

EasyExcel的实体类

?实现easyexcel的写入拦截器

Service层代码

可能讲解能力较差,但是代码全,不会掐头掐尾. 看了很多文章代码只截取局部,导致看下来都是很 **/#*

业务逻辑:

????????要根据条件判断,未达到合格值的数据渲染背景为红色.

? ? ? ?

EasyExcel的pom依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.4</version>
</dependency>

EasyExcel的实体类

/**
* @ExcelProperty注解属性介绍:
* value: 表示写入excel时候的表头名字
* index: 表示写入excel单元格的索引(从0开始)
**/
@Data
@ContentRowHeight(20)
@HeadRowHeight(40)
@ColumnWidth(15)
public class TestExcel {
    @ExcelProperty(value = "是否合格", index = 0)
    private Integer isQualified;
    @ExcelProperty(value = "总数", index = 1)
    private Integer totalNumber;
    @ExcelProperty(value = "数据质量合格数", index = 2)
    private Integer qualifyNumber;
    @ExcelProperty(value = "数据质量不合格数", index = 3)
    private Integer unQualifyNumber;

?实现easyexcel的写入拦截器

? ? ? esayexcel,有注解的方式渲染单元格,但是比较笨重,一渲染就是渲染整整一列的底色,还有由于我们要渲染的单元格不固定,从而采用代码的方式来渲染.

? ? ? 注意:afterCellDispose方法中千万不能写查询数据库的逻辑,有因为你要往excel中写入多少数据他会调用几次这个方法,下面的i变量是我计次用的.

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
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 com.alibaba.excel.write.metadata.style.WriteFont;


/**
*    easyExcel的写入配置
*    集成CellWriteHandler,在afterCellDispose方法中,重写我们的逻辑
**/
public class MyCellWriteHandler implements 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) {

    }
    /**
    *方法里面千万不要写查询数据库的逻辑,因为渲染每个单元格会多次调用改方法
    *cell表示当前行的第i个例中的数据.
    *假如说我们有4行数据,每行有6列(就是6个单元格),那么该方法就会调用4×6=24次
    **/
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //当前行
        int rowIndex = cell.getRowIndex();
        //当前行的第i列
        int columnIndex = cell.getColumnIndex();
        //cell表示你当前第rowIndex行,第columnIndex列中的数据
        String stringCellValue = cell.toString();
        if(true){
        //可以在这里写你的逻辑判断要渲染那些单元格,
        cellStyle(cell,rowIndex,columnIndex)
        }
       
    }
    /**
    * 渲染每一个单元格的方法
    * columnIndex:第i列,表示我要渲染第几列数据的单元格
    **/
    private void cellStyle(Cell cell,int rowIndex,int columnIndex){
        // 根据单元格获取workbook
        Workbook workbook = cell.getSheet().getWorkbook();
        // 单元格策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置背景颜色白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 设置垂直居中为居中对齐
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置左右对齐为靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 创建字体实例
        WriteFont cellWriteFont = new WriteFont();
        // 设置字体大小
        cellWriteFont.setFontName("宋体");
        cellWriteFont.setFontHeightInPoints((short) 14);
        //设置字体颜色 黑色
        cellWriteFont.setColor(IndexedColors.BLACK.getIndex());
        //设置单元格颜色为红
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        contentWriteCellStyle.setWriteFont(cellWriteFont);
        CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, contentWriteCellStyle);
        //设置渲染当前行第i列的样式
        cell.getRow().getCell(columnIndex).setCellStyle(cellStyle);
    }

Service层代码

public void excelTest(HttpServletResponse response){

        //你要写入excel表格中的数据
        List<TestExcel> excels= service.list(params);
        
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" +  "测试" + ".xlsx");
        EasyExcel.write(response.getOutputStream(),TabrDeviceTestRecordExcel.class)
                //注册写入配置
                .registerWriteHandler(new MyCellWriteHandler())
                .sheet("测试")
                .doWrite(excels); //写入excel的数据

}