easyexcel -- 简单使用(写)

深碍√TFBOYSˉ_ 2023-01-19 09:51 59阅读 0赞

### 文章目录 ###

*  1、效果图
 *  2、导入jar
 *  3、代码
 *  4、备注

--------------------

# 1、效果图 #

1、年龄小于20的标记为红色  
2、姓名为男的标记为绿色  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4_size_16_color_FFFFFF_t_70]

# 2、导入jar #

1、关于最新版本, 以及jar包在哪里找: [地址:https://www.yuque.com/easyexcel/faq/shge1s][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(会报错)  
基于项目里面的一些由于版本导致的问题进行修改:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][20210509232627429.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4_size_16_color_FFFFFF_t_70]: /images/20221021/69e5d4fc0ff748fd8bd0399947a4ca78.png
[https_www.yuque.com_easyexcel_faq_shge1s]: https://www.yuque.com/easyexcel/faq/shge1s
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4_size_16_color_FFFFFF_t_70 1]: /images/20221021/59415f111edd4d6f9813c56977f42b36.png
[20210509232627429.png]: /images/20221021/3d39f453ba6c4acebb851bc3f418f8e7.png