easyExcel读写excel

最新推荐文章于 2025-06-17 22:33:22 发布

原创最新推荐文章于 2025-06-17 22:33:22 发布 · 2.7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #java

文件操作专栏收录该内容

2 篇文章

订阅专栏

该博客介绍了如何使用阿里巴巴的EasyExcel库进行Excel文件的读取和导出操作。通过创建实体类并添加注解，可以自动解析Excel数据。同时提供了读取Excel到List的工具方法，以及导出Excel到OutputStream的实现，便于文件下载。此外，还包含了模板文件的读取和填充功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

pom文件

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

实体类中加上注解 index代表单元格的第几列，会自动跳过第一行

    @ExcelProperty(index = 1)
    private Integer sum;

工具类

package cn.chinaunicom.mall.usercenter.excel;
/**
 * <p>
 * excel工具类
 * </p>
 *
 * @author yinna
 * @version: V1.0
 */

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.event.SyncReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import org.apache.http.entity.ContentType;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;

public class ExcelUtil {

    /**
     * 读取 Excel(多个 sheet)
     *
     * @param excel 文件
     * @param clz   实体类映射
     * @return Excel 数据 list
     */
    public static Object headerRead(MultipartFile excel, Class<T> clz) {
        InputStream is;
        try {
            is = new BufferedInputStream(excel.getInputStream());
        } catch (Exception e) {
            return null;
        }
        SyncReadListener tmpListener = new SyncReadListener();
        return tmpListener.getList();
    }

    /**
     * 读取 Excel(多个 sheet)
     *
     * @param excel 文件
     * @param clz   实体类映射
     * @return Excel 数据 list
     */
    public static <T> List<T> readExcel(MultipartFile excel, Class<T> clz) {
        InputStream is;
        try {
            is = new BufferedInputStream(excel.getInputStream());
        } catch (Exception e) {
            return null;
        }
        SyncReadListener tmpListener = new SyncReadListener();
        ReadSheet readSheet = new ReadSheet();
        readSheet.setClazz(clz);
        EasyExcel.read(is).registerReadListener(tmpListener).build().read(readSheet);
        return (List<T>) tmpListener.getList();
    }

    /**
     * 读取 Excel(多个 sheet)
     *
     * @param excel 文件
     * @param clz   实体类映射
     * @return Excel 数据 list
     */
    public static <T> List<T> readExcel(String path, Class<T> clz) {
        SyncReadListener tmpListener = new SyncReadListener();
        ReadSheet readSheet = new ReadSheet();
        readSheet.setClazz(clz);
        EasyExcel.read(path).registerReadListener(tmpListener).build().read(readSheet);
        return (List<T>) tmpListener.getList();
    }


    /**
     * 导出文件时为Writer生成OutputStream
     *
     * @param fileName
     * @param response
     * @return
     * @throws Exception
     */
    public static OutputStream getOutputStream(String fileName, HttpServletResponse response)
            throws Exception {
        try {
            fileName = URLEncoder.encode(fileName, "utf-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //此处指定了文件类型为xls，如果是xlsx的，请自行替换修改
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            throw new Exception("导出文件失败！");
        }
    }

    public static MultipartFile writeExcel(Class clazz, List<?> list, String fileName, InputStream templateFile) throws Exception {
        ByteArrayOutputStream os1 = new ByteArrayOutputStream();
        EasyExcel.write(os1, clazz).withTemplate(templateFile).sheet(fileName).doFill(list);
        byte[] b1 = os1.toByteArray();
        InputStream inputStream = new ByteArrayInputStream(b1);
        MultipartFile file = new MockMultipartFile(fileName, fileName, ContentType.APPLICATION_OCTET_STREAM.toString(), inputStream);
        return file;

    }

    public static String getPath() {
        return ExcelUtil.class.getResource("/").getPath();
    }


}

直接调用工具类即可