EasyExcel导入Excel和导出Excel教程

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。

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数据的功能。

Excel源代码,ExcelCreate.java导出类和ExcelRead.java导入类 用到的poi底层架包有poi-3.6-20091214.jar,poi-contrib-3.6-20091214.jar poi-scratchpad-3.6-20091214.jar ExcelRead.java文件导入类 Public void importExcel(String file)读取文件方法一,file指明要导入的文件路径。 public void importExcel (File file) 读取文件方法二,参数是file文件。 public void importExcel (InputStream filestream)读取文件方法三,参数是文件流。 public List readRow (int sheetNumber,int rows), 读取第几张工作表的第几行数据返回一个list. (工作表,起始行) ExcelCreate.java文件导出类 public ExcelCreate (),构造函数。 public void createSheet(String sheetName),创建工作表(工作表名称)。可创建多个工作表。 public static void setDefaultCellHighWidthInRange(short[] eachCellWidth, int high) 设置各列单元格宽度,eachCellWidth[]指定的列的列号,high设置的宽度。 public void addHeader (list rowvalues, Boolean isFilter)导入表头,list是保存表头的集合,isFilter是否过滤。 public void addRow (List rowvalues),数据的导入Excel方法, public void insertRow (List rowvalues,Int rowIndex),数据的导入Excel方法,给指定的行后追加一条数据。 public void delRow(int row) 删除指定的行。 Public void setSelect (int row,int cells,List cellvalue)给指定的列设置下拉列表,row是行号,cells 是列号,list是下拉列表的数据。 public void exportExcel (String file)导出文件的路径方法一,file是要导出数据的文件路径。 public void exportExcel (File file) 导出文件的路径方法二,file是要导出的文件。 public void exportExcel (OutputStream outputstream) 导出文件的路径方法三,outputstream是输出流。
相关推荐
©️2020 CSDN 皮肤主题: 技术工厂 设计师:CSDN官方博客 返回首页