JAVA解析Excel工具easyexcel

Java解析、生成Excel比較有名的框架有Apache poi、jxl。但他們都存在一個(gè)嚴(yán)重的問題就是非常的耗內(nèi)存，poi有一套SAX模式的API可以一定程度的解決一些內(nèi)存溢出的問題，但POI還是有一些缺陷，比如07版Excel解壓縮以及解壓后存儲(chǔ)都是在內(nèi)存中完成的，內(nèi)存消耗依然很大。easyexcel重寫了poi對07版Excel的解析，能夠原本一個(gè)3M的excel用POI sax依然需要100M左右內(nèi)存降低到KB級別，并且再大的excel不會(huì)出現(xiàn)內(nèi)存溢出，03版依賴POI的sax模式。在上層做了模型轉(zhuǎn)換的封裝，讓使用者更加簡單方便

快速開始

讀Excel

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>{latestVersion}</version>
</dependency>

讀07版小于1000行數(shù)據(jù)返回List<List<String>>

List<Object> data = EasyExcelFactory.read(inputStream, new Sheet(1, 0));

讀07版小于1000行數(shù)據(jù)返回List<? extend BaseRowModel>

List<Object> data = EasyExcelFactory.read(inputStream, new Sheet(2, 1,JavaModel.class));

讀07版大于1000行數(shù)據(jù)返回List<List<String>>

ExcelListener excelListener = new ExcelListener();
EasyExcelFactory.readBySax(inputStream, new Sheet(1, 1), excelListener);

讀07版大于1000行數(shù)據(jù)返回List<? extend BaseRowModel>

ExcelListener excelListener = new ExcelListener();
EasyExcelFactory.readBySax(inputStream, new Sheet(2, 1,JavaModel.class), excelListener);

讀03版方法同上

寫Excel

沒有模板

ExcelWriter writer = EasyExcelFactory.getWriter(out);

//寫第一個(gè)sheet, sheet1  數(shù)據(jù)全是List<String> 無模型映射關(guān)系
Sheet sheet1 = new Sheet(1, 3);
sheet1.setSheetName("第一個(gè)sheet");
//設(shè)置列寬 設(shè)置每列的寬度
Map columnWidth = new HashMap();
columnWidth.put(0,10000);columnWidth.put(1,40000);columnWidth.put(2,10000);columnWidth.put(3,10000);
sheet1.setColumnWidthMap(columnWidth);
sheet1.setHead(createTestListStringHead());
//or 設(shè)置自適應(yīng)寬度
//sheet1.setAutoWidth(Boolean.TRUE);
writer.write1(createTestListObject(), sheet1);

//寫第二個(gè)sheet sheet2  模型上打有表頭的注解，合并單元格
Sheet sheet2 = new Sheet(2, 3, JavaModel1.class, "第二個(gè)sheet", null);
sheet2.setTableStyle(createTableStyle());
writer.write(createTestListJavaMode(), sheet2);

//寫第三個(gè)sheet包含多個(gè)table情況
Sheet sheet3 = new Sheet(3, 0);
sheet3.setSheetName("第三個(gè)sheet");
Table table1 = new Table(1);
table1.setHead(createTestListStringHead());
writer.write1(createTestListObject(), sheet3, table1);

//寫sheet2  模型上打有表頭的注解
Table table2 = new Table(2);
table2.setTableStyle(createTableStyle());
table2.setClazz(JavaModel1.class);
writer.write(createTestListJavaMode(), sheet3, table2);

//關(guān)閉資源
writer.finish();
out.close();

有模板

OutputStream out = new FileOutputStream("/Users/jipengfei/2007.xlsx");
ExcelWriter writer = EasyExcelFactory.getWriterWithTemp(inputStream,out,ExcelTypeEnum.XLSX,true);

//寫第一個(gè)sheet, sheet1  數(shù)據(jù)全是List<String> 無模型映射關(guān)系
Sheet sheet1 = new Sheet(1, 3);
sheet1.setSheetName("第一個(gè)sheet");
//設(shè)置列寬 設(shè)置每列的寬度
Map columnWidth = new HashMap();
columnWidth.put(0,10000);columnWidth.put(1,40000);columnWidth.put(2,10000);columnWidth.put(3,10000);
sheet1.setColumnWidthMap(columnWidth);
sheet1.setHead(createTestListStringHead());
//or 設(shè)置自適應(yīng)寬度
//sheet1.setAutoWidth(Boolean.TRUE);
writer.write1(createTestListObject(), sheet1);

//寫第二個(gè)sheet sheet2  模型上打有表頭的注解，合并單元格
Sheet sheet2 = new Sheet(2, 3, JavaModel1.class, "第二個(gè)sheet", null);
sheet2.setTableStyle(createTableStyle());
writer.write(createTestListJavaMode(), sheet2);

//寫第三個(gè)sheet包含多個(gè)table情況
Sheet sheet3 = new Sheet(3, 0);
sheet3.setSheetName("第三個(gè)sheet");
Table table1 = new Table(1);
table1.setHead(createTestListStringHead());
writer.write1(createTestListObject(), sheet3, table1);

//寫sheet2  模型上打有表頭的注解
Table table2 = new Table(2);
table2.setTableStyle(createTableStyle());
table2.setClazz(JavaModel1.class);
writer.write(createTestListJavaMode(), sheet3, table2);

//關(guān)閉資源
writer.finish();
out.close();

web下載實(shí)例寫法

public class Down {
    @GetMapping("/a.htm")
    public void cooperation(HttpServletRequest request, HttpServletResponse response) {
        ServletOutputStream out = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
        String fileName = new String(("UserInfo " + new SimpleDateFormat("yyyy-MM-dd").format(new Date()))
                .getBytes(), "UTF-8");
        Sheet sheet1 = new Sheet(1, 0);
        sheet1.setSheetName("第一個(gè)sheet");
        writer.write0(getListString(), sheet1);
        writer.finish();
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx");
        out.flush();
        }
    }
}

easyexcel核心功能

*讀任意大小的03、07版Excel不會(huì)OO]<br />

*讀Excel自動(dòng)通過注解，把結(jié)果映射為java模型<br />

*讀Excel支持多sheet<br />

*讀Excel時(shí)候是否對Excel內(nèi)容做trim()增加容錯(cuò)<br />

*寫小量數(shù)據(jù)的03版Excel（不要超過2000行）<br />

*寫任意大07版Excel不會(huì)OOM<br />

*寫Excel通過注解將表頭自動(dòng)寫入Excel<br />

*寫Excel可以自定義Excel樣式 如：字體，加粗，表頭顏色，數(shù)據(jù)內(nèi)容顏色<br />

*寫Excel到多個(gè)不同sheet<br />

*寫Excel時(shí)一個(gè)sheet可以寫多個(gè)Table<br />

*寫Excel時(shí)候自定義是否需要寫表頭<br />

二方包依賴

使用前最好咨詢下最新版，或者到mvn倉庫搜索先easyexcel的最新版

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.0.0-RELEASE</version>
</dependency>

讀Excel

使用easyexcel解析03、07版本的Excel只是ExcelTypeEnum不同，其他使用完全相同，使用者無需知道底層解析的差異。

無java模型直接把excel解析的每行結(jié)果以List<String>返回 在ExcelListener獲取解析結(jié)果

讀excel代碼示例如下：

    @Test
    public void testExcel2003NoModel() {
        InputStream inputStream = getInputStream("loan1.xls");
        try {
            // 解析每行結(jié)果在listener中處理
            ExcelListener listener = new ExcelListener();

            ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLS, null, listener);
            excelReader.read();
        } catch (Exception e) {

        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

ExcelListener示例代碼如下：

 /* 解析監(jiān)聽器，
 * 每解析一行會(huì)回調(diào)invoke()方法。
 * 整個(gè)excel解析結(jié)束會(huì)執(zhí)行doAfterAllAnalysed()方法
 *
 * 下面只是我寫的一個(gè)樣例而已，可以根據(jù)自己的邏輯修改該類。
 * @author jipengfei
 * @date 2017/03/14
 */
public class ExcelListener extends AnalysisEventListener {

    //自定義用于暫時(shí)存儲(chǔ)data。
    //可以通過實(shí)例獲取該值
    private List<Object> datas = new ArrayList<Object>();
    public void invoke(Object object, AnalysisContext context) {
        System.out.println("當(dāng)前行："+context.getCurrentRowNum());
        System.out.println(object);
        datas.add(object);//數(shù)據(jù)存儲(chǔ)到list，供批量處理，或后續(xù)自己業(yè)務(wù)邏輯處理。
        doSomething(object);//根據(jù)自己業(yè)務(wù)做處理
    }
    private void doSomething(Object object) {
        //1、入庫調(diào)用接口
    }
    public void doAfterAllAnalysed(AnalysisContext context) {
       // datas.clear();//解析結(jié)束銷毀不用的資源
    }
    public List<Object> getDatas() {
        return datas;
    }
    public void setDatas(List<Object> datas) {
        this.datas = datas;
    }
}

有java模型映射

java模型寫法如下：

public class LoanInfo extends BaseRowModel {
    @ExcelProperty(index = 0)
    private String bankLoanId;
    
    @ExcelProperty(index = 1)
    private Long customerId;
    
    @ExcelProperty(index = 2,format = "yyyy/MM/dd")
    private Date loanDate;
    
    @ExcelProperty(index = 3)
    private BigDecimal quota;
    
    @ExcelProperty(index = 4)
    private String bankInterestRate;
    
    @ExcelProperty(index = 5)
    private Integer loanTerm;
    
    @ExcelProperty(index = 6,format = "yyyy/MM/dd")
    private Date loanEndDate;
    
    @ExcelProperty(index = 7)
    private BigDecimal interestPerMonth;

    @ExcelProperty(value = {"一級表頭","二級表頭"})
    private BigDecimal sax;
}

@ExcelProperty(index = 3)數(shù)字代表該字段與excel對應(yīng)列號(hào)做映射，也可以采用 @ExcelProperty(value = {"一級表頭","二級表頭"})用于解決不確切知道excel第幾列和該字段映射，位置不固定，但表頭的內(nèi)容知道的情況。

    @Test
    public void testExcel2003WithReflectModel() {
        InputStream inputStream = getInputStream("loan1.xls");
        try {
            // 解析每行結(jié)果在listener中處理
            AnalysisEventListener listener = new ExcelListener();

            ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLS, null, listener);

            excelReader.read(new Sheet(1, 2, LoanInfo.class));
        } catch (Exception e) {

        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

帶模型解析與不帶模型解析主要在構(gòu)造new Sheet(1, 2, LoanInfo.class)時(shí)候包含class。Class需要繼承BaseRowModel暫時(shí)BaseRowModel沒有任何內(nèi)容，后面升級可能會(huì)增加一些默認(rèn)的數(shù)據(jù)。

寫Excel

每行數(shù)據(jù)是List<String>無表頭

  OutputStream out = new FileOutputStream("/Users/jipengfei/77.xlsx");
        try {
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX,false);
            //寫第一個(gè)sheet, sheet1  數(shù)據(jù)全是List<String> 無模型映射關(guān)系
            Sheet sheet1 = new Sheet(1, 0);
            sheet1.setSheetName("第一個(gè)sheet");
            writer.write(getListString(), sheet1);
            writer.finish();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

每行數(shù)據(jù)是一個(gè)java模型有表頭----表頭層級為一

生成Excel格式如下圖

屏幕快照 2017-06-02 上午9.49.39.png

模型寫法如下：

public class ExcelPropertyIndexModel extends BaseRowModel {

    @ExcelProperty(value = "姓名" ,index = 0)
    private String name;

    @ExcelProperty(value = "年齡",index = 1)
    private String age;

    @ExcelProperty(value = "郵箱",index = 2)
    private String email;

    @ExcelProperty(value = "地址",index = 3)
    private String address;

    @ExcelProperty(value = "性別",index = 4)
    private String sax;

    @ExcelProperty(value = "高度",index = 5)
    private String heigh;

    @ExcelProperty(value = "備注",index = 6)
    private String last;
}

@ExcelProperty(value = "姓名",index = 0) value是表頭數(shù)據(jù)，默認(rèn)會(huì)寫在excel的表頭位置，index代表第幾列。

 @Test
    public void test1() throws FileNotFoundException {
        OutputStream out = new FileOutputStream("/Users/jipengfei/78.xlsx");
        try {
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
            //寫第一個(gè)sheet, sheet1  數(shù)據(jù)全是List<String> 無模型映射關(guān)系
            Sheet sheet1 = new Sheet(1, 0,ExcelPropertyIndexModel.class);
            writer.write(getData(), sheet1);
            writer.finish();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

每行數(shù)據(jù)是一個(gè)java模型有表頭----表頭層級為多層級

生成Excel格式如下圖：

屏幕快照 2017-06-02 上午9.53.07.png

java模型寫法如下：

public class MultiLineHeadExcelModel extends BaseRowModel {

    @ExcelProperty(value = {"表頭1","表頭1","表頭31"},index = 0)
    private String p1;

    @ExcelProperty(value = {"表頭1","表頭1","表頭32"},index = 1)
    private String p2;

    @ExcelProperty(value = {"表頭3","表頭3","表頭3"},index = 2)
    private int p3;

    @ExcelProperty(value = {"表頭4","表頭4","表頭4"},index = 3)
    private long p4;

    @ExcelProperty(value = {"表頭5","表頭51","表頭52"},index = 4)
    private String p5;

    @ExcelProperty(value = {"表頭6","表頭61","表頭611"},index = 5)
    private String p6;

    @ExcelProperty(value = {"表頭6","表頭61","表頭612"},index = 6)
    private String p7;

    @ExcelProperty(value = {"表頭6","表頭62","表頭621"},index = 7)
    private String p8;

    @ExcelProperty(value = {"表頭6","表頭62","表頭622"},index = 8)
    private String p9;
}

寫Excel寫法同上，只需將ExcelPropertyIndexModel.class改為MultiLineHeadExcelModel.class

一個(gè)Excel多個(gè)sheet寫法

 @Test
    public void test1() throws FileNotFoundException {

        OutputStream out = new FileOutputStream("/Users/jipengfei/77.xlsx");
        try {
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX,false);
            //寫第一個(gè)sheet, sheet1  數(shù)據(jù)全是List<String> 無模型映射關(guān)系
            Sheet sheet1 = new Sheet(1, 0);
            sheet1.setSheetName("第一個(gè)sheet");
            writer.write(getListString(), sheet1);

            //寫第二個(gè)sheet sheet2  模型上打有表頭的注解，合并單元格
            Sheet sheet2 = new Sheet(2, 3, MultiLineHeadExcelModel.class, "第二個(gè)sheet", null);
            sheet2.setTableStyle(getTableStyle1());
            writer.write(getModeldatas(), sheet2);

            //寫sheet3  模型上沒有注解，表頭數(shù)據(jù)動(dòng)態(tài)傳入
            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>();
            headCoulumn1.add("第一列");
            headCoulumn2.add("第二列");
            headCoulumn3.add("第三列");
            head.add(headCoulumn1);
            head.add(headCoulumn2);
            head.add(headCoulumn3);
            Sheet sheet3 = new Sheet(3, 1, NoAnnModel.class, "第三個(gè)sheet", head);
            writer.write(getNoAnnModels(), sheet3);
            writer.finish();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

一個(gè)sheet中有多個(gè)表格

@Test
    public void test2() throws FileNotFoundException {
        OutputStream out = new FileOutputStream("/Users/jipengfei/77.xlsx");
        try {
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX,false);

            //寫sheet1  數(shù)據(jù)全是List<String> 無模型映射關(guān)系
            Sheet sheet1 = new Sheet(1, 0);
            sheet1.setSheetName("第一個(gè)sheet");
            Table table1 = new Table(1);
            writer.write(getListString(), sheet1, table1);
            writer.write(getListString(), sheet1, table1);

            //寫sheet2  模型上打有表頭的注解
            Table table2 = new Table(2);
            table2.setTableStyle(getTableStyle1());
            table2.setClazz(MultiLineHeadExcelModel.class);
            writer.write(getModeldatas(), sheet1, table2);

            //寫sheet3  模型上沒有注解，表頭數(shù)據(jù)動(dòng)態(tài)傳入,此情況下模型field順序與excel現(xiàn)實(shí)順序一致
            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>();
            headCoulumn1.add("第一列");
            headCoulumn2.add("第二列");
            headCoulumn3.add("第三列");
            head.add(headCoulumn1);
            head.add(headCoulumn2);
            head.add(headCoulumn3);
            Table table3 = new Table(3);
            table3.setHead(head);
            table3.setClazz(NoAnnModel.class);
            table3.setTableStyle(getTableStyle2());
            writer.write(getNoAnnModels(), sheet1, table3);
            writer.write(getNoAnnModels(), sheet1, table3);

            writer.finish();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

測試數(shù)據(jù)分析

POI usermodel PK easyexcel(Excel 2003).png

POI usermodel PK easyexcel(Excel 2007).png

POI usermodel PK easyexcel(Excel 2003) (1).png

POI usermodel PK easyexcel(Excel 2007) (1).png

從上面的性能測試可以看出easyexcel在解析耗時(shí)上比poiuserModel模式弱了一些。主要原因是我內(nèi)部采用了反射做模型字段映射，中間我也加了cache，但感覺這點(diǎn)差距可以接受的。但在內(nèi)存消耗上差別就比較明顯了，easyexcel在后面文件再增大，內(nèi)存消耗幾乎不會(huì)增加了。但poi userModel就不一樣了，簡直就要爆掉了。想想一個(gè)excel解析200M，同時(shí)有20個(gè)人再用估計(jì)一臺(tái)機(jī)器就掛了。