easyexcel -- 简单使用(写)

---

1、效果图

1、年龄小于20的标记为红色
2、姓名为男的标记为绿色

2、导入jar

1、关于最新版本, 以及jar包在哪里找: 地址:https://www.yuque.com/easyexcel/faq/shge1s
2、此包里面包含了poi, poi-ooxml, poi-ooxml-schemas. 这三个包不需要在进行导入, 如果非要在进行导入, 需要注意包冲突

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.8</version>
</dependency>

3、代码

先定一个实体类:

package com.tolern.base.base.excel.borrow;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import lombok.Data;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;


@Data
// 头字体设置成20
@HeadFontStyle(fontHeightInPoints = 13)
// 内容单元格设置实线, 居中
@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER, borderLeft = BorderStyle.THIN, borderRight = BorderStyle.THIN, borderTop = BorderStyle.THIN, borderBottom = BorderStyle.THIN)
public class RecordBorrowExcel {
    // 列宽
    @ColumnWidth(28)
    // 标题
    @ExcelProperty("名字")
    private String name;

    @ColumnWidth(19)
    @ExcelProperty("性别")
    private Integer sex;

    @ColumnWidth(22)
    @ExcelProperty("年龄")
    private Integer age;
}

编写一个拦截器:

package com.tolern.base.excel;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
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.tolern.base.base.excel.ExcelBase;
import com.tolern.base.base.excel.borrow.RecordBorrowExcelOrderStatus;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;

import java.util.List;

/**
 * @author Chaim
 * @date 2021/5/8 10:48
 */
@Slf4j
public class RecordBorrowExcelHandler 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) {

    }


    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
        // 这里可以对cell进行任何操作
        log.info("第{}行，第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex());
        // 对订单状态(第1列)进行从新编写
        if (!isHead && cell.getColumnIndex() == 1) {
            // 订单状态: 0男 1女
            // 采用内置函数避免过多if else代码
            ExcelBase excelBase = new ExcelBase(writeSheetHolder, cell);
            int i = (int) cell.getNumericCellValue();
            if (i <= 1 && i >= 0) {
                String value = new RecordBorrowExcelOrderStatus().map.get(i).apply(excelBase);
                cell.setCellValue(value);
            }
        }

        if (!isHead && cell.getColumnIndex() == 2) {
            if (cell.getNumericCellValue() < 20) {
                Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
                CellStyle cellStyle = workbook.createCellStyle();
                // 填充色, 是设置前景色不是背景色
                cellStyle.setFillForegroundColor(IndexedColors.RED.index);
                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                // 边框
                cellStyle.setBorderLeft(BorderStyle.THIN);
                cellStyle.setBorderRight(BorderStyle.THIN);
                cellStyle.setBorderTop(BorderStyle.THIN);
                cellStyle.setBorderBottom(BorderStyle.THIN);
                // 居中
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                // 将自定义风格写入
                cell.setCellStyle(cellStyle);
            }
        }
    }
}

package com.tolern.base.base.excel;

import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import lombok.Data;
import org.apache.poi.poifs.common.POIFSConstants;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.util.IOUtils;

import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;

/**
 * @author Chaim
 * @date 2021/5/8 17:33
 */
@Data
public class ExcelBase {
    WriteSheetHolder writeSheetHolder;
    Cell cell;

    public ExcelBase(WriteSheetHolder writeSheetHolder, Cell cell) {
        this.writeSheetHolder = writeSheetHolder;
        this.cell = cell;
    }

    /**
     * 处理版本poi:4.0.0向后兼容3.17
     * @param inp
     * @return
     * @throws IOException
     */
    public static boolean hasOOXMLHeader(InputStream inp) throws IOException {
        inp.mark(4);
        byte[] header = new byte[4];
        IOUtils.readFully(inp, header);
        if (inp instanceof PushbackInputStream) {
            PushbackInputStream pin = (PushbackInputStream)inp;
            pin.unread(header);
        } else {
            inp.reset();
        }

        return header[0] == POIFSConstants.OOXML_FILE_HEADER[0] && header[1] == POIFSConstants.OOXML_FILE_HEADER[1] && header[2] == POIFSConstants.OOXML_FILE_HEADER[2] && header[3] == POIFSConstants.OOXML_FILE_HEADER[3];
    }
}

package com.tolern.base.base.excel.borrow;

import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.tolern.base.base.excel.ExcelBase;
import org.apache.poi.ss.usermodel.*;

import java.util.HashMap;
import java.util.Map;
import java.util.function.Function;

/**
 * 订单状态: 0进行值 1已结束
 *
 * @author Chaim
 * @date 2021/5/8 15:52
 */
public class RecordBorrowExcelOrderStatus {
    public Map<Integer, Function<ExcelBase, String>> map = new HashMap<>(9);

    {
        map.put(0, this::stateTransitionZero);
        map.put(1, this::stateTransitionOne);
    }

    public String stateTransitionZero(ExcelBase excelBase) {
        WriteSheetHolder writeSheetHolder = excelBase.getWriteSheetHolder();
        Cell cell = excelBase.getCell();
        Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
        CellStyle cellStyle = workbook.createCellStyle();
        // 填充色, 是设置前景色不是背景色
        cellStyle.setFillForegroundColor(IndexedColors.GREEN.index);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        // 居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 将自定义风格写入
        cell.setCellStyle(cellStyle);
        return "男";
    }

    public String stateTransitionOne(ExcelBase excelBase) {
        return "女";
    }
}

导出:

    public void exportBorrow(HttpServletResponse response) throws IOException {
        List<RecordBorrowExcel> recordBorrowExcelList = new ArrayList<>();
        RecordBorrowExcel recordBorrowExcel = new RecordBorrowExcel();
        recordBorrowExcel.setName("ABC");
        recordBorrowExcel.setAge(18);
        recordBorrowExcel.setSex(1);
        recordBorrowExcelList.add(recordBorrowExcel);
        RecordBorrowExcel recordBorrowExcelOne = new RecordBorrowExcel();
        recordBorrowExcelOne.setName("BCD");
        recordBorrowExcelOne.setAge(22);
        recordBorrowExcelOne.setSex(0);
        recordBorrowExcelList.add(recordBorrowExcelOne);
        RecordBorrowExcel recordBorrowExcelTwo = new RecordBorrowExcel();
        recordBorrowExcelTwo.setName("CDE");
        recordBorrowExcelTwo.setAge(20);
        recordBorrowExcelTwo.setSex(0);
        recordBorrowExcelList.add(recordBorrowExcelTwo);

        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), RecordBorrowExcel.class)
                // 注册拦截器
                .registerWriteHandler(new RecordBorrowExcelHandler())
                .sheet("测试底部名字")
                .doWrite(recordBorrowExcelList);

    }

4、备注

1、后期会在进行补充, 这里罗列的只是一点点内容
2、这里可以补充一点, 代码最开始使用的是poi:4.0.0
替换成easyexcel, 他们并不能使用poi:4.0.0(会报错)
基于项目里面的一些由于版本导致的问题进行修改: