阿里的easyExcel导出

朴灿烈づ我的快乐病毒、 2021-11-05 06:16 801阅读 0赞

easyExcel的gitHub地址：[https://github.com/alibaba/easyexcel ][https_github.com_alibaba_easyexcel]

开发环境：springboot

## 1.导入依赖 ##

>     <dependency>
>         <groupId>com.alibaba</groupId>
>         <artifactId>easyexcel</artifactId>
>         <version>1.1.2-beta5</version>
>     </dependency>

注意：easyexcel需要依赖 poi和poi-ooxml 这里easyexcel的版本是1.1.2，对应poi版本是3.1.7

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70][]

##  2.导出带标题的excel ##

如果导出的只是有一个标题，不需要合并单元格之类的，类型这样的效果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 1][]

新建一个实体类集成  BaseRowModel 然后使用@ExcelProperty 注解来绑定对象，value位标题中文，index位列的序号从0开始，format可以格式化日期。

/**
     * @author: lockie
     * @Date: 2019/8/5 17:11
     * @Description:
     */
    @Data
    @ToString
    public class OrderExportDTO extends BaseRowModel {
    
        @ExcelProperty(value = "订单流水号", index = 0)
        private String orderNo;
    
        @ExcelProperty(value = "订单生成时间", index = 1, format = "yyyy-MM-dd HH:mm:ss")
        private Date createTime;
    
        @ExcelProperty(value = "订单状态", index = 2)
        private String orderStatusShowStr;
    
        
    
    }

controller层新增一个导出接口，根据条件查询出数据，然后调用 EasyExcelUtils.writeExcelOneSheet 方法导出，我这里设置了订单号的列宽所以自己new了一个Sheet对象，如果不需要设置列宽可以调用 EasyExcelUtils.writeExcelOneSheet 方法的时候sheet传null。

/**
         * 导出excel
         * @param orderInfoEx
         * @param response
         */
        @GetMapping("/exportChargeExcel")
        public void exportPhpExcel(OrderInfoEx orderInfoEx, HttpServletResponse response) {
            if (null == orderInfoEx) {
                Assert.isTrue(true,"查询参数为空");
            }
            if(null == orderInfoEx.getDealStartDateStr()){
                Assert.isTrue(true,"请选择开始时间");
            }
            if(null == orderInfoEx.getDealEndDateStr()){
                Assert.isTrue(true,"请选择结束时间");
            }
    
            HashMap<String, Object> queryMap = new HashMap<>();
            queryMap.put("dealStartDateStr", orderInfoEx.getDealStartDateStr());
            queryMap.put("dealEndDateStr", orderInfoEx.getDealEndDateStr());
            queryMap.put("phoneNumber", orderInfoEx.getPhoneNumber());
            queryMap.put("cardNum", orderInfoEx.getCardNum());
            queryMap.put("cardType", orderInfoEx.getCardType());
            queryMap.put("orgName", orderInfoEx.getOrgName());
            queryMap.put("orderStatusShow", orderInfoEx.getOrderStatusShow());
            queryMap.put("city", orderInfoEx.getCity());
            queryMap.put("csCode", orderInfoEx.getCsCode());
    
            try {
                // 设置列宽
                Map columnWidth = new HashMap();
                columnWidth.put(0, 8000);
    
                String fileName = "正常订单";
                if (StringUtils.isNotEmpty(orderInfoEx.getExportType())) {
                    if ("1".equals(orderInfoEx.getExportType())) {
                        // 查询正常订单导出数据
                        List<OrderExportDTO> orderExportDTOList = orderService.getOrderExportByParam(queryMap);
    
                        Sheet sheet1 = new Sheet(1, 0, OrderExportDTO.class);
                        sheet1.setColumnWidthMap(columnWidth);
                        sheet1.setSheetName(fileName);
                        // 导出
                        EasyExcelUtils.writeExcelOneSheet(response, orderExportDTOList, sheet1, fileName);
                    } else {
                        fileName = "异常订单";
                        // 查询异常订单导出数据
                        List<ExceptionOrderExportDTO> exceptionOrderExportDTOList = orderService.getExceptionOrderExportByParam(queryMap);
    
                        Sheet sheet1 = new Sheet(1, 0, ExceptionOrderExportDTO.class);
                        sheet1.setColumnWidthMap(columnWidth);
                        sheet1.setSheetName(fileName);
    
                        // 导出
                        EasyExcelUtils.writeExcelOneSheet(response, exceptionOrderExportDTOList, sheet1, fileName);
                    }
                } else {
                    throw new RuntimeException("导出类型为空");
                }
            } catch (Exception e) {
                logger.error("订单导出异常",e);
                throw new RuntimeException(e);
            }
        }

封装的 EasyExcelUtils 工具类，如果导出单个Sheet的excel则使用 writeExcelOneSheet 方法，如果导出多个sheet则使用 writeExcelMutilSheet 方法

import com.alibaba.excel.EasyExcelFactory;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.event.AnalysisEventListener;
    import com.alibaba.excel.metadata.BaseRowModel;
    import com.alibaba.excel.metadata.Sheet;
    import com.alibaba.excel.support.ExcelTypeEnum;
    import com.alibaba.excel.util.CollectionUtils;
    import com.alibaba.excel.util.StringUtils;
    import lombok.Data;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    import java.io.*;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    import java.util.Map;
    
    /**
     * @author: lockie
     * @Date: 2019/8/6 10:18
     * @Description:
     */
    public class EasyExcelUtils {
        private static final Logger logger = LoggerFactory.getLogger(EasyExcelUtils.class);
    
        private static Sheet initSheet;
    
        static {
            initSheet = new Sheet(1, 0);
            initSheet.setSheetName("sheet");
            // 设置自适应宽度
            initSheet.setAutoWidth(Boolean.TRUE);
        }
    
        /**
         * 读取少于1000行数据
         *
         * @param filePath 文件绝对路径
         * @return
         */
        public static List<Object> readLessThan1000Row(String filePath) {
            return readLessThan1000RowBySheet(filePath, null);
        }
    
        /**
         * 读取少于1000行数据，带样式的
         *
         * @param filePath 文件绝对路径
         * @param sheet
         * @return
         */
        public static List<Object> readLessThan1000RowBySheet(String filePath, Sheet sheet) {
            if (!StringUtils.hasText(filePath)) {
                return null;
            }
    
            sheet = sheet != null ? sheet : initSheet;
    
            InputStream inputStream = null;
            try {
                inputStream = new FileInputStream(filePath);
                return EasyExcelFactory.read(inputStream, sheet);
            } catch (FileNotFoundException e) {
                logger.error("找不到文件或者文件路径错误", e);
            } finally {
                try {
                    if (inputStream != null) {
                        inputStream.close();
                    }
                } catch (IOException e) {
                    logger.error("excel文件读取失败，失败原因：{}", e);
                }
            }
            return null;
        }
    
        /**
         * 读取大于1000行数据
         * @param filePath
         * @return
         */
        public static List<Object> readMoreThan1000Row(String filePath) {
            return readMoreThan1000RowBySheet(filePath, null);
        }
    
        /**
         * 读取大于1000行数据
         * @param filePath
         * @param sheet
         * @return
         */
        public static List<Object> readMoreThan1000RowBySheet(String filePath, Sheet sheet) {
            if (!StringUtils.hasText(filePath)) {
                return null;
            }
            sheet = sheet != null ? sheet : initSheet;
    
            InputStream inputStream = null;
            try {
                inputStream = new FileInputStream(filePath);
                ExcelListener excelListener = new ExcelListener();
                EasyExcelFactory.readBySax(inputStream, sheet, excelListener);
                return excelListener.getDatas();
            } catch (FileNotFoundException e) {
                logger.error("找不到文件或者文件路径错误");
            } finally {
                try {
                    if (inputStream != null) {
                        inputStream.close();
                    }
                } catch (IOException e) {
                    logger.error("excel文件读取失败，失败原因：{}", e);
                }
            }
            return null;
        }
    
        /**
         * 导出单个sheet
         * @param response
         * @param dataList
         * @param sheet
         * @param fileName
         * @throws UnsupportedEncodingException
         */
        public static void writeExcelOneSheet(HttpServletResponse response, List<? extends BaseRowModel> dataList, Sheet sheet, String fileName) throws UnsupportedEncodingException {
            if (CollectionUtils.isEmpty(dataList)) {
                return;
            }
            // 如果sheet为空，则使用默认的
            if (null == sheet) {
                sheet = initSheet;
            }
            try {
                String value = "attachment; filename=" + new String(
                        (fileName + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLSX.getValue()).getBytes("gb2312"), "ISO8859-1");
                response.setContentType("multipart/form-data");
                response.setCharacterEncoding("utf-8");
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                response.setHeader("Content-disposition", value);
    
                ServletOutputStream out = response.getOutputStream();
                ExcelWriter writer = EasyExcelFactory.getWriter(out, ExcelTypeEnum.XLSX, true);
                // 设置属性类
                sheet.setClazz(dataList.get(0).getClass());
                writer.write(dataList, sheet);
                writer.finish();
                out.flush();
            } catch (IOException e) {
                logger.error("导出失败，失败原因：{}", e);
            }
        }
    
        /**
         * @Author lockie
         * @Description 导出excel 支持一张表导出多个sheet
         * @Param OutputStream 输出流
         * Map<String, List>  sheetName和每个sheet的数据
         * ExcelTypeEnum 要导出的excel的类型 有ExcelTypeEnum.xls 和有ExcelTypeEnum.xlsx
         * @Date 上午12:16 2019/1/31
         */
        public static void writeExcelMutilSheet(HttpServletResponse response, Map<String, List<? extends BaseRowModel>> dataList, String fileName) throws UnsupportedEncodingException {
            if (CollectionUtils.isEmpty(dataList)) {
                return;
            }
            try {
                String value = "attachment; filename=" + new String(
                        (fileName + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ExcelTypeEnum.XLSX.getValue()).getBytes("gb2312"), "ISO8859-1");
                response.setContentType("multipart/form-data");
                response.setCharacterEncoding("utf-8");
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                response.setHeader("Content-disposition", value);
                ServletOutputStream out = response.getOutputStream();
                ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
                // 设置多个sheet
                setMutilSheet(dataList, writer);
                writer.finish();
                out.flush();
            } catch (IOException e) {
                logger.error("导出异常", e);
            }
        }
    
    
        /**
         * @Author lockie
         * @Description //setSheet数据
         * @Date 上午12:39 2019/1/31
         */
        private static void setMutilSheet(Map<String, List<? extends BaseRowModel>> dataList, ExcelWriter writer) {
            int sheetNum = 1;
            for (Map.Entry<String, List<? extends BaseRowModel>> stringListEntry : dataList.entrySet()) {
                Sheet sheet = new Sheet(sheetNum, 0, stringListEntry.getValue().get(0).getClass());
                sheet.setSheetName(stringListEntry.getKey());
                writer.write(stringListEntry.getValue(), sheet);
                sheetNum++;
            }
        }
    
    
    
        /**
         * 导出监听
         */
        @Data
        public static class ExcelListener extends AnalysisEventListener {
            private List<Object> datas = new ArrayList<>();
    
            /**
             * 逐行解析
             * @param object 当前行的数据
             * @param analysisContext
             */
            @Override
            public void invoke(Object object, AnalysisContext analysisContext) {
                // 当前行
    //            analysisContext.getCurrentRowNum()
                if (object != null) {
                    datas.add(object);
                }
            }
    
    
            /**
             * 解析完所有数据后会调用该方法
             * @param analysisContext
             */
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    
            }
        }
    
    
        /**
         * test
         *
         * @param args
         * @param response
         * @throws UnsupportedEncodingException
         */
        public static void main(String[] args, HttpServletResponse response) throws UnsupportedEncodingException {
            // 导出多个sheet
    //        List<OrderExportDTO> orderExportDTOList = new ArrayList<>();
    //        Map<String, List<? extends BaseRowModel>> map = new HashMap<>();
    //        map.put("自营订单", orderExportDTOList);
    //        map.put("互联互通", orderExportDTOList);
    //        String fileName = new String(("测试导出2019").getBytes(), "UTF-8");
    //        writeExcelMutilSheet(response, map, fileName);
    
            // 导出单个sheet
    //        writeExcelOneSheet(response, orderExportDTOList, null, fileName);
        }
    
    }

3.导出动态excel标题

创建标题，也可以使用model创建

public static List<List<String>> createTestListStringHead(){
        // 模型上没有注解，表头数据动态传入
        List<List<String>> head = new ArrayList<List<String>>();
        List<String> headCoulumn1 = new ArrayList<String>();
        List<String> headCoulumn2 = new ArrayList<String>();
        List<String> headCoulumn3 = new ArrayList<String>();
        List<String> headCoulumn4 = new ArrayList<String>();
        List<String> headCoulumn5 = new ArrayList<String>();
    
        headCoulumn1.add("第一列");headCoulumn1.add("第一列");headCoulumn1.add("第一列");
        headCoulumn2.add("第一列");headCoulumn2.add("第一列");headCoulumn2.add("第一列");
    
        headCoulumn3.add("第二列");headCoulumn3.add("第二列");headCoulumn3.add("第二列");
        headCoulumn4.add("第三列");headCoulumn4.add("第三列2");headCoulumn4.add("第三列2");
        headCoulumn5.add("第一列");headCoulumn5.add("第3列");headCoulumn5.add("第4列");
    
        head.add(headCoulumn1);
        head.add(headCoulumn2);
        head.add(headCoulumn3);
        head.add(headCoulumn4);
        head.add(headCoulumn5);
        return head;
    }

导出

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 2][]

导出效果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 3][]

## 4.自定义表头以及内容样式 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 4][]

public static TableStyle createTableStyle() {
        TableStyle tableStyle = new TableStyle();
        // 设置表头样式
        Font headFont = new Font();
        // 字体是否加粗
        headFont.setBold(true);
        // 字体大小
        headFont.setFontHeightInPoints((short)12);
        // 字体
        headFont.setFontName("楷体");
        tableStyle.setTableHeadFont(headFont);
        // 背景色
        tableStyle.setTableHeadBackGroundColor(IndexedColors.BLUE);
    
    
        // 设置表格主体样式
        Font contentFont = new Font();
        contentFont.setBold(true);
        contentFont.setFontHeightInPoints((short)12);
        contentFont.setFontName("黑体");
        tableStyle.setTableContentFont(contentFont);
        tableStyle.setTableContentBackGroundColor(IndexedColors.GREEN);
        return tableStyle;
    }

## 5.合并单元格 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 5][]

注意下标是从 0 开始的，也就是说合并了第六行到第七行，其中的第一列到第五列，跑下代码，看下效果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 6][]

## 6.自定义处理 ##

对于更复杂的处理，EasyExcel 预留了 `WriterHandler` 接口来，允许你自定义处理代码：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 7][]

接口中定义了三个方法:

>  *  `sheet()`: 在创建每个 sheet 后自定义业务逻辑处理；
>  *  `row()`: 在创建每个 row 后自定义业务逻辑处理；
>  *  `cell()`: 在创建每个 cell 后自定义业务逻辑处理；

我们自定义一个Handler类实现了WriteHandler接口后，编写自定义逻辑处理代码，然后调用 `getWriterWithTempAndHandler()` 静态方法获取 `ExcelWriter` 对象时，传入 `WriterHandler` 的实现类即可。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 8][]

>  ExcelWriter writer = EasyExcelFactory.getWriterWithTempAndHandler(null, out, ExcelTypeEnum.XLSX, true, new MyWriterHandler());

[https_github.com_alibaba_easyexcel]: https://github.com/alibaba/easyexcel
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70]: /images/20211104/70d92e1c8d954e7b807a71d2fbfb1abc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 1]: /images/20211104/291bbc2ab2114cc9884e845500b9350e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 2]: /images/20211104/45eab49d2cc946d28a097dd644f6c21f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 3]: /images/20211104/d1f83cab971140f898dfb4d667c2b50b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 4]: /images/20211104/8e86fa1cd26b4b529984807034560e7e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 5]: /images/20211104/a90c475bf30541cdadc69b117a79e9d7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 6]: /images/20211104/f2b1bc67ef7d49a78b3cc34416aade15.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 7]: /images/20211104/41053c1538ac4d8a8b8e09afa3bd1488.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3p4bDY0NjgwMTkyNA_size_16_color_FFFFFF_t_70 8]: /images/20211104/1abbbdab9f784973b981728b66760645.png