EasyExcel讀寫、模板填充、Web上傳下載入門到實戰(zhàn)全指南

更新時間：2026年01月26日 08:24:04 作者：五阿哥永琪

EasyExcel類是一套基于Java的開源Excel解析工具類,相較于傳統(tǒng)的框架如Apache poi、jxl等更加快速、簡潔,還可以解決大文件內(nèi)存溢出問題,這篇文章主要介紹了EasyExcel讀寫、模板填充、Web上傳下載入門到實戰(zhàn)的相關(guān)資料,需要的朋友可以參考下

一、主要功能

1. 讀 Excel（支持 .xls / .xlsx）

按行讀取，自動映射到 Java 對象
支持讀取表頭、數(shù)據(jù)、異常處理
可監(jiān)聽每行數(shù)據(jù)（適合大數(shù)據(jù)量）

EasyExcel.read(fileName, DemoData.class, new MyReadListener())
         .sheet()
         .doRead();

2. 寫 Excel

自動根據(jù)對象字段生成表頭和數(shù)據(jù)
支持大數(shù)據(jù)量分批寫入（百萬行無壓力）
可指定列寬、行高、樣式等

EasyExcel.write(fileName, DemoData.class)
         .sheet("Sheet1")
         .doWrite(dataList);

3. Excel 填充（模板填充）

提供 Excel 模板（帶占位符如 {name}）
自動填充數(shù)據(jù)，適合生成報表、證書等

EasyExcel.write(out, DemoData.class)
         .withTemplate(templateFile)
         .sheet()
         .doFill(data);

4. 豐富的注解支持

@ExcelProperty("姓名")：指定列名
@ColumnWidth(20)：設(shè)置列寬
@DateTimeFormat("yyyy-MM-dd")：日期格式化
@NumberFormat("#,##0.00")：數(shù)字格式化
@ExcelIgnore：忽略字段

public class User {
    @ExcelProperty("用戶姓名")
    private String name;

    @ExcelProperty("注冊時間")
    @DateTimeFormat("yyyy年MM月dd日")
    private Date registerTime;
}

二、快速開始

1. 引入依賴

<!--Lombok依賴-->  
<dependency>  
    <groupId>org.projectlombok</groupId>  
    <artifactId>lombok</artifactId>  
    <optional>true</optional>  
</dependency>  
<!--easyexcel依賴-->  
<dependency>  
    <groupId>com.alibaba</groupId>  
    <artifactId>easyexcel</artifactId>  
    <version>4.0.3</version>  
</dependency>  
<!--fastjson依賴-->  
<dependency>  
    <groupId>com.alibaba</groupId>  
    <artifactId>fastjson</artifactId>  
    <version>2.0.57</version>  
</dependency>

2. 寫實體類

package com.nuc.demoexcel.entity;  
  
import com.alibaba.excel.annotation.ExcelIgnore;  
import com.alibaba.excel.annotation.ExcelProperty;  
import com.alibaba.excel.annotation.write.style.ColumnWidth;  
import com.alibaba.excel.annotation.write.style.HeadRowHeight;  
import com.alibaba.fastjson.annotation.JSONField;  
import lombok.Data;  
  
import java.util.Date;  
  
@Data  
@HeadRowHeight(20)  
@ColumnWidth(20)  
public class DemoDate {  
    @ExcelProperty("字符串標題")  
    private String name;  
    @ExcelProperty("日期標題")  
    @JSONField(format = "yyyy-MM-dd,HH:mm:ss") // 格式化日期 沒有注解會默認轉(zhuǎn)成時間戳  
    private Date date;  
    @ExcelProperty("數(shù)字標題")  
    private Double doubleData;  
    @ExcelIgnore  
    private String ignore;  
}

3. Excel讀取

讀取常用有三種方法

這是我提前準備好的excel文件

方法一：

有封裝的實體類
用.head(DemoData.class)封裝
結(jié)果用List<DemoData>存

/**  
 * 測試讀1  
 * 有封裝的實體類  
 */  
@Test  
void readExcel1() {  
    System.out.println("測試");  
    List<DemoDate> list = EasyExcel.read("D:\\測試\\demo.xlsx").head(DemoDate.class).sheet().doReadSync();  
    list.forEach(data ->{  
        String jsonString = JSON.toJSONString(data);  
        log.info("讀取到數(shù)據(jù){}",jsonString);  
    });  
}

測試結(jié)果

方法二：沒有封裝的實體類

沒有封裝的實體類
結(jié)果用List<Map<Integer,String>>存

/**  
 * 測試讀2  
 * 沒有分裝的實體類用Map來存  
 */  
@Test  
void readExcel2() {  
    System.out.println("測試");  
    List<Map<Integer,String>> list = EasyExcel.read("D:\\測試\\demo.xlsx").sheet().doReadSync();  
    list.forEach(data ->{  
        String string = data.toString();  
        log.info("讀取到數(shù)據(jù){}",string);  
    });  
}

測試結(jié)果同上

方法三：使用監(jiān)聽器來讀取文件

使用監(jiān)聽器來讀取文件

/**  
 * 測試讀3  
 * 使用監(jiān)聽器讀取文件。  
 * 需要我們額外創(chuàng)建一個監(jiān)聽器  
 */  
@Test  
void readExcel3() {  
    System.out.println("測試");  
    EasyExcel.read("D:\\測試\\demo.xlsx", DemoDate.class, new ExcelListener(demoService)).sheet().doRead();  
}

這里需要額外寫一個監(jiān)聽器

package com.nuc.demoexcel.listener;  
  
import com.alibaba.excel.context.AnalysisContext;  
import com.alibaba.excel.event.AnalysisEventListener;  
import com.alibaba.fastjson.JSON;  
import com.nuc.demoexcel.entity.DemoDate;  
import com.nuc.demoexcel.service.DemoService;  
import lombok.RequiredArgsConstructor;  
import lombok.extern.slf4j.Slf4j;  
  
import java.util.ArrayList;  
import java.util.List;  
  
/**  
 * 這里不能用SprongIoC容器來管理Listener，Listener可添加多個，會自動執(zhí)行  
 */  
@Slf4j  
@RequiredArgsConstructor  
public class ExcelListener extends AnalysisEventListener<DemoDate> {  
  
    /**  
     * 每隔5條存儲數(shù)據(jù)庫,清理list，方便內(nèi)存回收  
     */  
    private static final int BATCH_COUNT = 5;  
    private List<DemoDate> list = new ArrayList<DemoDate>();  
    private final DemoService demoService;  
  
    @Override  
    public void invoke(DemoDate demoDate, AnalysisContext analysisContext) {  
        log.info("讀取到數(shù)據(jù){}", JSON.toJSONString(demoDate));  
        list.add(demoDate);  
        // 到達批次數(shù)，保存數(shù)據(jù)，防止數(shù)據(jù)幾萬條數(shù)據(jù)在內(nèi)存中，容易OOM  
        if (list.size() >= BATCH_COUNT) {  
            saveData();  
            // 保存完成，清理list  
            list.clear();  
        }  
  
    }  
  
  
    /**  
     * 所有的數(shù)據(jù)都讀完，最后收尾數(shù)據(jù)  
     * @param analysisContext  
     */  
    @Override  
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {  
        //這里也要保存數(shù)據(jù)，確保最后遺留的數(shù)據(jù)也存儲到數(shù)據(jù)庫  
        saveData();  
        log.info("所有數(shù)據(jù)解析完成！");  
    }  
  
    /**  
     * 存儲數(shù)據(jù)到數(shù)據(jù)庫  
     */  
    private void saveData() {  
        log.info("{}條數(shù)據(jù)，開始存儲數(shù)據(jù)庫！", list.size());  
        demoService.saveData(list);  
        log.info("存儲數(shù)據(jù)庫成功！");  
    }  
}

這里只是演示EasyExcel
所以就不寫Service層代碼

4. Excel寫入

  
/**  
 * 測試寫  
 */  
@Test  
void testWrite() {  
    // 文件輸出位置  
    String fileName = "D:\\text" + System.currentTimeMillis() + ".xlsx";  
    EasyExcel.write(fileName, DemoDate.class).sheet("模板").doWrite(data());  
  
  
  
}  
  
private List<DemoDate> data(){  
    //模擬數(shù)據(jù)  
    List<DemoDate> list = new ArrayList<>();  
    for (int i = 0; i < 13; i++) {  
        DemoDate data = new DemoDate();  
        data.setName("張三"+i);  
        data.setDate(new Date());  
        data.setDoubleData(Math.random()*100);  
        list.add(data);  
    }  
    return list;  
}

成功寫入

5. Excel下載&上傳

package com.nuc.demoexcel.controller;  
  
import com.alibaba.excel.EasyExcel;  
import com.alibaba.fastjson.JSON;  
import com.nuc.demoexcel.entity.DemoDate;  
import com.nuc.demoexcel.listener.ExcelListener;  
import com.nuc.demoexcel.service.DemoService;  
import jakarta.servlet.http.HttpServletResponse;  
import org.springframework.beans.factory.annotation.Autowired;  
import org.springframework.stereotype.Controller;  
import org.springframework.web.bind.annotation.PostMapping;  
import org.springframework.web.bind.annotation.RequestMapping;  
import org.springframework.web.bind.annotation.ResponseBody;  
import org.springframework.web.multipart.MultipartFile;  
  
import java.io.IOException;  
import java.net.URLEncoder;  
import java.util.*;  
  
@Controller  
public class UploadDownController {  
  
    @Autowired  
    private DemoService demoService;  
  
    /**  
     * 下載失敗了（會返回一個有部分數(shù)據(jù)的Excel）  
     * @param response  
     * @throws IOException  
     */    @RequestMapping("/download")  
    public void download(HttpServletResponse  response) throws IOException {  
        response.setContentType("application/vnd.ms-excel");  
        response.setCharacterEncoding("utf-8");  
        //這里的encode是解決中文亂碼, 這里必須使用UTF-8, 不然亂碼  
        String fileName = URLEncoder.encode("測試", "UTF-8").replaceAll("\\+", "%20");  
        //這里使用附件的形式下載  
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");  
        EasyExcel.write(response.getOutputStream(), DemoDate.class).sheet("模板").doWrite(data());  
    }  
  
  
    @RequestMapping("/api/download")  
    public void downloadApi(HttpServletResponse response) throws IOException {  
        try {  
            response.setContentType("application/vnd.ms-excel");  
            response.setCharacterEncoding("utf-8");  
            //這里的encode是解決中文亂碼, 這里必須使用UTF-8, 不然亂碼  
            String fileName = URLEncoder.encode("測試", "UTF-8").replaceAll("\\+", "%20");  
            //這里使用附件的形式下載  
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");  
            EasyExcel.write(response.getOutputStream(), DemoDate.class).sheet("模板").doWrite(data());  
        } catch (IOException e) {  
            //重置響應(yīng)  
            response.reset();  
            response.setContentType("application/json");  
            response.setCharacterEncoding("utf-8");  
            Map<String, Object> map = new HashMap<>();  
            map.put("status", 500);  
            map.put("message", "數(shù)據(jù)寫入失敗" + e.getMessage());  
            response.getWriter().println(JSON.toJSONString(map));  
        }  
  
    }  
  
    @PostMapping("/upload")  
    @ResponseBody  
    public String upload(MultipartFile file) throws IOException {  
        EasyExcel.read(file.getInputStream(), DemoDate.class, new ExcelListener(demoService)).sheet().doRead();  
        return "success";  
    }  
  
    private List<DemoDate> data(){  
        //模擬數(shù)據(jù)  
        List<DemoDate> list = new ArrayList<>();  
        for (int i = 0; i < 13; i++) {  
            DemoDate data = new DemoDate();  
            data.setName("張三"+i);  
            data.setDate(new Date());  
            data.setDoubleData(Math.random()*100);  
            list.add(data);  
        }  
        return list;  
    }  
}

調(diào)用localhost:8080/download接口

5. Excel下載&上傳

package com.nuc.demoexcel.controller;  
  
import com.alibaba.excel.EasyExcel;  
import com.alibaba.fastjson.JSON;  
import com.nuc.demoexcel.entity.DemoDate;  
import com.nuc.demoexcel.listener.ExcelListener;  
import com.nuc.demoexcel.service.DemoService;  
import jakarta.servlet.http.HttpServletResponse;  
import org.springframework.beans.factory.annotation.Autowired;  
import org.springframework.stereotype.Controller;  
import org.springframework.web.bind.annotation.PostMapping;  
import org.springframework.web.bind.annotation.RequestMapping;  
import org.springframework.web.bind.annotation.ResponseBody;  
import org.springframework.web.multipart.MultipartFile;  
  
import java.io.IOException;  
import java.net.URLEncoder;  
import java.util.*;  
  
@Controller  
public class UploadDownController {  
  
    @Autowired  
    private DemoService demoService;  
  
    /**  
     * 下載失敗了（會返回一個有部分數(shù)據(jù)的Excel）  
     * @param response  
     * @throws IOException  
     */    @RequestMapping("/download")  
    public void download(HttpServletResponse  response) throws IOException {  
        response.setContentType("application/vnd.ms-excel");  
        response.setCharacterEncoding("utf-8");  
        //這里的encode是解決中文亂碼, 這里必須使用UTF-8, 不然亂碼  
        String fileName = URLEncoder.encode("測試", "UTF-8").replaceAll("\\+", "%20");  
        //這里使用附件的形式下載  
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");  
        EasyExcel.write(response.getOutputStream(), DemoDate.class).sheet("模板").doWrite(data());  
    }  
  
  
    @RequestMapping("/api/download")  
    public void downloadApi(HttpServletResponse response) throws IOException {  
        try {  
            response.setContentType("application/vnd.ms-excel");  
            response.setCharacterEncoding("utf-8");  
            //這里的encode是解決中文亂碼, 這里必須使用UTF-8, 不然亂碼  
            String fileName = URLEncoder.encode("測試", "UTF-8").replaceAll("\\+", "%20");  
            //這里使用附件的形式下載  
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");  
            EasyExcel.write(response.getOutputStream(), DemoDate.class).sheet("模板").doWrite(data());  
        } catch (IOException e) {  
            //重置響應(yīng)  
            response.reset();  
            response.setContentType("application/json");  
            response.setCharacterEncoding("utf-8");  
            Map<String, Object> map = new HashMap<>();  
            map.put("status", 500);  
            map.put("message", "數(shù)據(jù)寫入失敗" + e.getMessage());  
            response.getWriter().println(JSON.toJSONString(map));  
        }  
  
    }  
  
    @PostMapping("/upload")  
    @ResponseBody  
    public String upload(MultipartFile file) throws IOException {  
        EasyExcel.read(file.getInputStream(), DemoDate.class, new ExcelListener(demoService)).sheet().doRead();  
        return "success";  
    }  
  
    private List<DemoDate> data(){  
        //模擬數(shù)據(jù)  
        List<DemoDate> list = new ArrayList<>();  
        for (int i = 0; i < 13; i++) {  
            DemoDate data = new DemoDate();  
            data.setName("張三"+i);  
            data.setDate(new Date());  
            data.setDoubleData(Math.random()*100);  
            list.add(data);  
        }  
        return list;  
    }  
}

調(diào)用localhost:8080/download接口
調(diào)用localhost:8080/api/upload接口
使用postman 調(diào)用localhost:8080/upload接口
- 后端