欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

阿里巴巴EasyExcel导入/导出Excel

程序员文章站 2024-03-20 17:19:04
...

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是最低支持的版本。

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。

2.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;
    }

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数据的功能。
————————————————
版权声明：本文为CSDN博主「李秀才」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_33556185/article/details/113532227

相关标签： Utils

上一篇： [poj 2018]Best Cow Fences ｛二分查找+最大子段和问题｝

下一篇： 数据结构实验之查找四：二分查找

推荐阅读