EasyExcel导入Excel和导出Excel教程

女爷i 2022-10-26 11:29 797阅读 0赞

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。 它底层是基于POI做的封装和优化，避免了POI耗费内存，内存溢出的问题。笔者之前做导出、导入Excel也是使用POI，后来在生产上总是出现内存溢出的问题，在寻求解决方案的路途中，就找到了EasyExcel。比POI要快很多，做过简单的测试，使用EasyExcel2.1.6和POI3.17做了一个导出的对比测试，写入50000条数据，easyexcel耗时2.5s，poi耗时12s；写入10万条数据，easyexcel耗时3.4s，poi耗时22s。所以若是优化导出的话，替换掉POI吧。接下来我们看简单的导出和导入的demo。

## 1.集成ExasyExcel ##

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

我们点到easyexcel2.1.6的pom文件后，看到它依赖的poi版本是3.17，工程中若是已经集成了poi，需要把poi版本改到和easyexcel依赖的版本一致才行，3.17是最低支持的版本。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNTU2MTg1_size_16_color_FFFFFF_t_70][]

## 2.导出示例 ##

###   2.1实体与excel列的映射 ###

@ExcelIgnore把不需要写到Excel的字段标注上；

@ExcelProperty标注是excel的列，value属性设置列的名称，index设置列的序号，从0开始，converter设置的转换器。

由于id是long类型，excel对数字只支持15位，超过的数字都会变成0，所以此处用到了官方提供的LongStringConverter转换器（将单元格的类型从long类型转换为字符串类型），写入Excel的时候，会根据我们的转换器来设置单元格的类型。官方提供了很多的转换器，在com.alibaba.excel.converters下面，我们也可以自定义转换器。

@Data
    public class PartnerBusinessOrderResponse {
        /**
         * 主键id
         */
        @ExcelProperty(value = "订单号",index = 0,converter= LongStringConverter.class)
        private Long id;
        /**
         * APPID
         */
        @ExcelIgnore
        private Long appId;
        /**
         * 订单号
         */
        @ExcelProperty(value = "订单号",index=1)
        private String orderNo;
        /**
         * 业务类型名称
         */
        @ExcelProperty(value = "业务类型",index=2)
        private String businessTypeName;
        /**
         * 业务编码
         */
        @ExcelProperty(value = "业务编码",index=3)
        private String businessCode;
        /**
         * 业务名称
         */
        @ExcelProperty(value = "业务名称",index=4)
        private String businessName;
        /**
         * 办理号码
         */
        @ExcelProperty(value = "办理号码",index=5)
        private String handleMobile;
        /**
         * 办理时间
         */
        @ExcelIgnore
        private Long handleTime;
        /**
         *办理时间---格式化
         * @return
         */
        @ExcelProperty(value = "办理时间",index=6)
        private String handleTimeString;
        /**
         * 办理渠道：1.APP内部办理 2.分享链接办理 3.二维码办理 4.海报办理 5.其他
         */
        @ExcelIgnore
        private String handleChannel;
        /**
         * 办理渠道：1.APP内部办理 2.分享链接办理 3.二维码办理 4.海报办理 5.其他
         */
        @ExcelProperty(value = "办理渠道",index=7)
        private String handleChannelString;
        /**
         * 平台名称
         */
        @ExcelProperty(value = "所属平台",index=8)
        private String platformName;
        /**
         * 归属工号
         */
        @ExcelProperty(value = "归属工号",index=9)
        private String belongEmployeeNum;
        /**
         * 四级机构名称
         */
        @ExcelProperty(value = "地市",index=10)
        private String fourOrgName;
        /**
         * 五级机构名称
         */
        @ExcelProperty(value = "区县",index=11)
        private String fiveOrgName;
        /**
         *直属高级合伙人姓名
         */
        @ExcelProperty(value = "归属合伙人姓名",index=12)
        private String partnerLeaderName;
        /**
         *直属高级合伙人手机号
         */
        @ExcelProperty(value = "归属合伙人手机号",index=13)
        private String partnerLeaderMobile;
        /**
         * 合伙人等级名称
         */
        @ExcelProperty(value = "合伙人等级",index=14)
        private String partnerLevelName;
        /**
         *经办人
         */
        @ExcelProperty(value = "经办人姓名",index=15)
        private String partnerName;
        /**
         *经办人手机号
         */
        @ExcelProperty(value = "经办人手机号",index=16)
        private String partnerMobile;
        /**
         * 办理状态（1：成功；2：失败）
         */
        @ExcelIgnore
        private Integer handleStatus;
        /**
         * 办理状态（1：成功；2：失败）---中文
         */
        @ExcelProperty(value = "是否办理成功",index=17)
        private String handleStatusString;
    
    }

### 2.2导出excel的代码 ###

@RequestMapping(value = "/export")
        public void exportExcel(HttpServletRequest httpServletRequest, HttpServletResponse response) {
            try {
                //获取数据  
                List<PartnerBusinessOrderResponse> data = pageResponse.getRecords();
                if (CollectionUtils.isEmpty(data)) {
                    data.add(new PartnerBusinessOrderResponse());
                }
                //attachment指定独立文件下载  不指定则回浏览器中直接打开
                String fileName = "业务订单导出_" + DateUtil.formatDate(new Date(), DateUtil.YEARMONTHDAYHHMMSS) + ".xlsx";
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
                //导出excel
                EasyExcel.write(response.getOutputStream(), PartnerBusinessOrderResponse.class).sheet("合伙人业务订单").doWrite(data);
                log.info("业务订单导出end");
            } catch (Exception e) {
                log.error("业务订单导出异常打印:", e);
            } finally {
                try {
                    response.flushBuffer();
                } catch (IOException e) {
                    log.error("业务订单导出输出流关闭失败: {}", e);
                }
            }
     }

就是3行代码，就完成的导出Excel。

EasyExcel类的源码

package com.alibaba.excel;
    
    public class EasyExcel extends EasyExcelFactory {
        public EasyExcel() {
        }
    }

EasyExcelFactory的源码

package com.alibaba.excel;
    
    import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.event.AnalysisEventListener;
    import com.alibaba.excel.event.WriteHandler;
    import com.alibaba.excel.metadata.Sheet;
    import com.alibaba.excel.read.builder.ExcelReaderBuilder;
    import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
    import com.alibaba.excel.read.listener.ReadListener;
    import com.alibaba.excel.support.ExcelTypeEnum;
    import com.alibaba.excel.write.builder.ExcelWriterBuilder;
    import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
    import com.alibaba.excel.write.builder.ExcelWriterTableBuilder;
    import java.io.File;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    public class EasyExcelFactory {
        public EasyExcelFactory() {
        }
    
        /** @deprecated */
        @Deprecated
        public static List<Object> read(InputStream in, Sheet sheet) {
            final List<Object> rows = new ArrayList();
            (new ExcelReader(in, (Object)null, new AnalysisEventListener<Object>() {
                public void invoke(Object object, AnalysisContext context) {
                    rows.add(object);
                }
    
                public void doAfterAllAnalysed(AnalysisContext context) {
                }
            }, false)).read(sheet);
            return rows;
        }
    
        /** @deprecated */
        @Deprecated
        public static void readBySax(InputStream in, Sheet sheet, AnalysisEventListener listener) {
            (new ExcelReader(in, (Object)null, listener)).read(sheet);
        }
    
        /** @deprecated */
        @Deprecated
        public static ExcelReader getReader(InputStream in, AnalysisEventListener listener) {
            return new ExcelReader(in, (Object)null, listener);
        }
    
        /** @deprecated */
        @Deprecated
        public static ExcelWriter getWriter(OutputStream outputStream) {
            return write().file(outputStream).autoCloseStream(Boolean.FALSE).convertAllFiled(Boolean.FALSE).build();
        }
    
        /** @deprecated */
        @Deprecated
        public static ExcelWriter getWriter(OutputStream outputStream, ExcelTypeEnum typeEnum, boolean needHead) {
            return write().file(outputStream).excelType(typeEnum).needHead(needHead).autoCloseStream(Boolean.FALSE).convertAllFiled(Boolean.FALSE).build();
        }
    
        /** @deprecated */
        @Deprecated
        public static ExcelWriter getWriterWithTemp(InputStream temp, OutputStream outputStream, ExcelTypeEnum typeEnum, boolean needHead) {
            return write().withTemplate(temp).file(outputStream).excelType(typeEnum).needHead(needHead).autoCloseStream(Boolean.FALSE).convertAllFiled(Boolean.FALSE).build();
        }
    
        /** @deprecated */
        @Deprecated
        public static ExcelWriter getWriterWithTempAndHandler(InputStream temp, OutputStream outputStream, ExcelTypeEnum typeEnum, boolean needHead, WriteHandler handler) {
            return write().withTemplate(temp).file(outputStream).excelType(typeEnum).needHead(needHead).registerWriteHandler(handler).autoCloseStream(Boolean.FALSE).convertAllFiled(Boolean.FALSE).build();
        }
    
        public static ExcelWriterBuilder write() {
            return new ExcelWriterBuilder();
        }
    
        public static ExcelWriterBuilder write(File file) {
            return write((File)file, (Class)null);
        }
    
        public static ExcelWriterBuilder write(File file, Class head) {
            ExcelWriterBuilder excelWriterBuilder = new ExcelWriterBuilder();
            excelWriterBuilder.file(file);
            if (head != null) {
                excelWriterBuilder.head(head);
            }
    
            return excelWriterBuilder;
        }
    
        public static ExcelWriterBuilder write(String pathName) {
            return write((String)pathName, (Class)null);
        }
    
        public static ExcelWriterBuilder write(String pathName, Class head) {
            ExcelWriterBuilder excelWriterBuilder = new ExcelWriterBuilder();
            excelWriterBuilder.file(pathName);
            if (head != null) {
                excelWriterBuilder.head(head);
            }
    
            return excelWriterBuilder;
        }
    
        public static ExcelWriterBuilder write(OutputStream outputStream) {
            return write((OutputStream)outputStream, (Class)null);
        }
    
        public static ExcelWriterBuilder write(OutputStream outputStream, Class head) {
            ExcelWriterBuilder excelWriterBuilder = new ExcelWriterBuilder();
            excelWriterBuilder.file(outputStream);
            if (head != null) {
                excelWriterBuilder.head(head);
            }
    
            return excelWriterBuilder;
        }
    
        public static ExcelWriterSheetBuilder writerSheet() {
            return writerSheet((Integer)null, (String)null);
        }
    
        public static ExcelWriterSheetBuilder writerSheet(Integer sheetNo) {
            return writerSheet(sheetNo, (String)null);
        }
    
        public static ExcelWriterSheetBuilder writerSheet(String sheetName) {
            return writerSheet((Integer)null, sheetName);
        }
    
        public static ExcelWriterSheetBuilder writerSheet(Integer sheetNo, String sheetName) {
            ExcelWriterSheetBuilder excelWriterSheetBuilder = new ExcelWriterSheetBuilder();
            if (sheetNo != null) {
                excelWriterSheetBuilder.sheetNo(sheetNo);
            }
    
            if (sheetName != null) {
                excelWriterSheetBuilder.sheetName(sheetName);
            }
    
            return excelWriterSheetBuilder;
        }
    
        public static ExcelWriterTableBuilder writerTable() {
            return writerTable((Integer)null);
        }
    
        public static ExcelWriterTableBuilder writerTable(Integer tableNo) {
            ExcelWriterTableBuilder excelWriterTableBuilder = new ExcelWriterTableBuilder();
            if (tableNo != null) {
                excelWriterTableBuilder.tableNo(tableNo);
            }
    
            return excelWriterTableBuilder;
        }
    
        public static ExcelReaderBuilder read() {
            return new ExcelReaderBuilder();
        }
    
        public static ExcelReaderBuilder read(File file) {
            return read((File)file, (Class)null, (ReadListener)null);
        }
    
        public static ExcelReaderBuilder read(File file, ReadListener readListener) {
            return read((File)file, (Class)null, readListener);
        }
    
        public static ExcelReaderBuilder read(File file, Class head, ReadListener readListener) {
            ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder();
            excelReaderBuilder.file(file);
            if (head != null) {
                excelReaderBuilder.head(head);
            }
    
            if (readListener != null) {
                excelReaderBuilder.registerReadListener(readListener);
            }
    
            return excelReaderBuilder;
        }
    
        public static ExcelReaderBuilder read(String pathName) {
            return read((String)pathName, (Class)null, (ReadListener)null);
        }
    
        public static ExcelReaderBuilder read(String pathName, ReadListener readListener) {
            return read((String)pathName, (Class)null, readListener);
        }
    
        public static ExcelReaderBuilder read(String pathName, Class head, ReadListener readListener) {
            ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder();
            excelReaderBuilder.file(pathName);
            if (head != null) {
                excelReaderBuilder.head(head);
            }
    
            if (readListener != null) {
                excelReaderBuilder.registerReadListener(readListener);
            }
    
            return excelReaderBuilder;
        }
    
        public static ExcelReaderBuilder read(InputStream inputStream) {
            return read((InputStream)inputStream, (Class)null, (ReadListener)null);
        }
    
        public static ExcelReaderBuilder read(InputStream inputStream, ReadListener readListener) {
            return read((InputStream)inputStream, (Class)null, readListener);
        }
    
        public static ExcelReaderBuilder read(InputStream inputStream, Class head, ReadListener readListener) {
            ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder();
            excelReaderBuilder.file(inputStream);
            if (head != null) {
                excelReaderBuilder.head(head);
            }
    
            if (readListener != null) {
                excelReaderBuilder.registerReadListener(readListener);
            }
    
            return excelReaderBuilder;
        }
    
        public static ExcelReaderSheetBuilder readSheet() {
            return readSheet((Integer)null, (String)null);
        }
    
        public static ExcelReaderSheetBuilder readSheet(Integer sheetNo) {
            return readSheet(sheetNo, (String)null);
        }
    
        public static ExcelReaderSheetBuilder readSheet(String sheetName) {
            return readSheet((Integer)null, sheetName);
        }
    
        public static ExcelReaderSheetBuilder readSheet(Integer sheetNo, String sheetName) {
            ExcelReaderSheetBuilder excelReaderSheetBuilder = new ExcelReaderSheetBuilder();
            if (sheetNo != null) {
                excelReaderSheetBuilder.sheetNo(sheetNo);
            }
    
            if (sheetName != null) {
                excelReaderSheetBuilder.sheetName(sheetName);
            }
    
            return excelReaderSheetBuilder;
        }
    }

3.导入

### 3.1定义导入的对象 ###

@Data
    public class TestData {
        private String partnerMobile;
        private String role;
    }

### 3.2写导入的监听类，一行一行读取 ###

@Slf4j
    public class TestListener  extends AnalysisEventListener<TestData> {
        /**
         * 每隔100条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
         */
        private static final int BATCH_COUNT = 100;
        List<TestData> list = new ArrayList<TestData>();
    
        @Override
        public void invoke(TestData data, AnalysisContext context) {
            log.info("解析到一条数据:{}", JSONObject.toJSON(data));
            list.add(data);
            if (list.size() >= BATCH_COUNT) {
                saveData();
                list.clear();
            }
        }
    
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            saveData();
            log.info("所有数据解析完成！");
        }
    
        /**
         * 加上存储数据库
         */
        private void saveData() {
            log.info("{}条数据，开始存储数据库！", list.size());
            log.info("存储数据库成功！");
        }
    }

### 3.3读取代码 ###

@RequestMapping(value = "/testImport", method = RequestMethod.POST)
        @ResponseBody
        public BaseResponse testImport(@RequestParam("file") MultipartFile multipartFile){
            BaseResponse baseResponse = BackResponseUtil.getBaseResponse(ReturnCodeEnum.CODE_1000.getCode());
            try{
                EasyExcel.read(multipartFile.getInputStream(), TestData.class, new TestListener()).sheet().doRead();
            }catch (Exception e){
                baseResponse = BackResponseUtil.getBaseResponse(ReturnCodeEnum.CODE_1005.getCode());
            }
            return baseResponse;
        }

就这样读取就完成了工文件流中读取解析Excel数据的功能。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNTU2MTg1_size_16_color_FFFFFF_t_70]: /images/20221024/aa547bb249c747328cce2b8c343834f8.png