快捷導航

使用EasyExcel導出excel模板實現(xiàn)教程

更新時間：2026年03月13日 10:53:16 作者：我是菜鳥吶

EasyExcel是Java解析、生成Excel的高效框架,通過重寫POI對07版Excel的解析,解決了內(nèi)存溢出的問題,使用步驟包括導入依賴、準備Excel模板、寫入數(shù)據(jù),并提供了基礎(chǔ)寫入、集合寫入和多個sheet同時寫入的效果

一、什么是EasyExcel

java解析、生成Excel比較有名的框架有Apache poi、jxl。但他們都存在一個嚴重的問題就是非常的耗內(nèi)存，poi有一套SAX模式的API可以一定程度的解決一些內(nèi)存溢出的問題，但POI還是有一些缺陷，比如07版Excel解壓縮以及解壓后存儲都是在內(nèi)存中完成的，內(nèi)存消耗依然很大。

easyexcel重寫了poi對07版Excel的解析，一個3M的excel用POI sax解析依然需要100M左右內(nèi)存，改用easyexcel可以降低到幾M，并且再大的excel也不會出現(xiàn)內(nèi)存溢出；03版依賴POI的sax模式，在上層做了模型轉(zhuǎn)換的封裝，讓使用者更加簡單方便

二、使用步驟

1.導入依賴

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

2.excel模板

我在這里準備了一個excel表格里面有倆個sheet，分別會用幾種方法寫入

3.寫入excel模板數(shù)據(jù)

先來一個基礎(chǔ)的sheet1的寫入數(shù)據(jù)，excel模板要放入指定位置或修改讀取路徑

 //簡單寫入
  @Test
    void test01() {
        //獲取模板位置
        InputStream templateFileName = getClass().getResourceAsStream("/static/simple.xlsx");
        String fileName = TestFileUtil.getPath() + "simpleFill" + System.currentTimeMillis() + ".xlsx";
        // 這里 會填充到第一個sheet， 然后文件流會自動關(guān)閉
        Map<String, Object> map = MapUtils.newHashMap();
        map.put("code", "8888");
        map.put("leader", "測試用戶");
        map.put("endTime", "2023-10-10 09:51:32");
        map.put("reportNo", "123456789");
        EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(map);
    }

  //集合寫入
    @Test
    void test02() {
        List<data> asList = Arrays.asList(
                new data(1, "1", "1", "1", "1", "1", "1", "1", "1", "1", "1"),
                new data(2, "2", "2", "2", "2", "2", "2", "2", "2", "2", "2"),
                new data(3, "3", "3", "3", "3", "3", "3", "3", "3", "3", "3"),
                new data(4, "3", "3", "3", "3", "3", "3", "3", "3", "3", "3")
        );
        InputStream templateFileName = getClass().getResourceAsStream("/static/simple.xlsx");
        String fileName = TestFileUtil.getPath() + "complexFill" + System.currentTimeMillis() + ".xlsx";
        // 方案1
        try (ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet(1).build();
            // 這里注意 入?yún)⒂昧薴orceNewRow 代表在寫入list的時候不管list下面有沒有空行 都會創(chuàng)建一行，然后下面的數(shù)據(jù)往后移動。默認 是false，會直接使用下一行，如果沒有則創(chuàng)建。
            // forceNewRow 如果設(shè)置了true,有個缺點 就是他會把所有的數(shù)據(jù)都放到內(nèi)存了，所以慎用
            // 簡單的說 如果你的模板有l(wèi)ist,且list不是最后一行，下面還有數(shù)據(jù)需要填充 就必須設(shè)置 forceNewRow=true 但是這個就會把所有數(shù)據(jù)放到內(nèi)存 會很耗內(nèi)存
            // 如果數(shù)據(jù)量大 list不是最后一行 參照下一個
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(asList, fillConfig, writeSheet);
            //excelWriter.fill(asList, fillConfig, writeSheet);
            Map<String, Object> map = MapUtils.newHashMap();
            map.put("reportNo", "8888");
            excelWriter.fill(map, writeSheet);
        }
    }

  //多個sheet寫入
    @Test
    void test03() {
        List<data> asList = Arrays.asList(
                new data(1, "1", "1", "1", "1", "1", "1", "1", "1", "1", "1"),
                new data(2, "2", "2", "2", "2", "2", "2", "2", "2", "2", "2"),
                new data(3, "3", "3", "3", "3", "3", "3", "3", "3", "3", "3"),
                new data(4, "4", "4", "4", "4", "4", "4", "4", "4", "4", "4")
        );
        InputStream templateFileName = getClass().getResourceAsStream("/static/simple.xlsx");
        String fileName = TestFileUtil.getPath() + "complexFill" + System.currentTimeMillis() + ".xlsx";
        try (ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build()) {
            // 去調(diào)用寫入,這里我調(diào)用了五次，實際使用時根據(jù)數(shù)據(jù)庫分頁的總的頁數(shù)來。這里最終會寫到5個sheet里面
            for (int i = 0; i < 1; i++) {
                // 每次都要創(chuàng)建writeSheet 這里注意必須指定sheetNo 而且sheetName必須不一樣。
                // 實際上可以一直變
                WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "sheet1").build();
                // 分頁去數(shù)據(jù)庫查詢數(shù)據(jù) 這里可以去數(shù)據(jù)庫查詢每一頁的數(shù)據(jù)
                Map<String, Object> maps = MapUtils.newHashMap();
                maps.put("code", "8888");
                maps.put("leader", "測試用戶");
                maps.put("endTime", "2023-10-10 09:51:32");
                maps.put("reportNo", "123456789");
                excelWriter.fill(maps, writeSheet1);
                WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "sheet2").build();
                FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                excelWriter.fill(asList, fillConfig, writeSheet2);
                Map<String, Object> map = MapUtils.newHashMap();
                map.put("reportNo", "8888");
                excelWriter.fill(map, writeSheet2);
            }
        }

    }

4.效果

簡單寫入的效果

集合寫入的效果

同時寫入多個sheet的效果跟上面?zhèn)z個一樣都會有數(shù)據(jù)

   @GetMapping("/excel")
    public Object excel(HttpServletResponse response) throws UnsupportedEncodingException {
        List<data> asList = Arrays.asList(
                new data(1, "1", "1", "1", "1", "1", "1", "1", "1", "1", "1"),
                new data(2, "2", "2", "2", "2", "2", "2", "2", "2", "2", "2"),
                new data(3, "3", "3", "3", "3", "3", "3", "3", "3", "3", "3"),
                new data(4, "4", "4", "4", "4", "4", "4", "4", "4", "4", "4")
        );
        //設(shè)置下載信息
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 這里URLEncoder.encode可以防止中文亂碼 當然和easyexcel沒有關(guān)系
        String fileName = URLEncoder.encode("數(shù)據(jù)寫出", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename="+ fileName + ".xlsx");

        InputStream templateFileName = getClass().getResourceAsStream("/static/simple.xlsx");
       // String fileName = TestFileUtil.getPath() + "complexFill" + System.currentTimeMillis() + ".xlsx";

        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templateFileName).build()) {
            // 去調(diào)用寫入,這里我調(diào)用了五次，實際使用時根據(jù)數(shù)據(jù)庫分頁的總的頁數(shù)來。這里最終會寫到5個sheet里面
            for (int i = 0; i < 1; i++) {
                // 每次都要創(chuàng)建writeSheet 這里注意必須指定sheetNo 而且sheetName必須不一樣。
                // 實際上可以一直變
                WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "sheet1").build();
                // 分頁去數(shù)據(jù)庫查詢數(shù)據(jù) 這里可以去數(shù)據(jù)庫查詢每一頁的數(shù)據(jù)

                Map<String, Object> maps = MapUtils.newHashMap();
                maps.put("code", "8888");
                maps.put("leader", "測試用戶123");
                maps.put("endTime", "2023-10-10 09:51:32");
                maps.put("reportNo", "123456789");
                excelWriter.fill(maps, writeSheet1);

                WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "sheet2").build();

                FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                excelWriter.fill(asList, fillConfig, writeSheet2);
                Map<String, Object> map = MapUtils.newHashMap();
                map.put("reportNo", "8888");
                excelWriter.fill(map, writeSheet2);
 				excelWriter.finish();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return "成功";
    }