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 {
    @Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
    }@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
    }@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
    }@Overridepublic 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 会导致各种问题，请直接用浏览器或者用postmanresponse.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(会报错)
基于项目里面的一些由于版本导致的问题进行修改: