EasyExcel 的文件导入

说明:该方法采用 阿里 EasyExcel 实现,该方法比普通的 Excel 导出效率高

一. 添加Maven依赖

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

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.0</version>
</dependency>

二. 创建 EasyExcel 工具类

package com.example.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import lombok.extern.slf4j.Slf4j;

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

/**
 * @author :zhangbocong
 * @version :V1.0
 * @program :spring_boot_demo
 * @date :Created in 2021年1月12日 9:17
 * @description :Excel 工具类
 */
@Slf4j
public class EasyExcelUtil<T> extends AnalysisEventListener<T> {

    private List<T> list = new ArrayList<>();

    @Override
    public void invoke(T data, AnalysisContext context) {
        log.info("解析到一条数据:{}", data);
        list.add(data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("所有数据解析完成!");
    }

    public List<T> getData() {
        return list;
    }

    /**
     * 默认excel文件名和单元sheet名一样的 Excel文件导出
     * @param httpServletResponse
     * @param data 数据集
     * @param fileName 文件名
     * @param clazz 数据集泛型
     * @throws IOException
     */
    public static void writeExcel(HttpServletResponse httpServletResponse, List data
                                        , String fileName, Class clazz) throws IOException {
        writeExcel(httpServletResponse, data, fileName, fileName, clazz);
    }

    /**
     * 导出数据为Excel文件
     * @param response  响应实体
     * @param data  导出数据
     * @param fileName 文件名
     * @param sheetName 单元格名
     * @param clazz  定义excel导出的泛型
     * @throws IOException
     */
    public static void writeExcel(HttpServletResponse response, List data, String fileName
                                        , String sheetName, Class clazz) throws IOException {
        //防止中文乱码
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //防止导入excel文件名中文不乱码
        response.setHeader("Content-disposition", "attachment;fileName=" + fileName 
                                + ".xlsx" + ";fileName*=utf-8''" + fileName + ".xlsx");
        // registerWriteHandler为自动列宽的意思
        EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).doWrite(data);
    }
}

三. 创建 Excel 模板

package com.example.spring_boot_demo.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;

/**
 * @author :zhangbocong
 * @version :V1.0
 * @program :spring_boot_demo
 * @date :Created in 2021年1月12日 9:12
 * @description :Excel 导出/导入模板
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelMouldEntity implements Serializable {
    /**
     * id
     */
    @ExcelProperty(index = 0,value = "序号")
    private Integer id;

    /**
     * 姓名
     */
    @ExcelProperty(index = 1,value = "姓名")
    private String userName;

    /**
     * 年龄
     */
    @ExcelProperty(index = 2,value = "年龄")
    private int age;

    /**
     * 性别
     */
    @ExcelProperty(index = 3,value = "性别")
    private String sex;
}

四. Excel 实现类

/**
 * @Author zhangbocong
 * @Description EasyExcel 文件导入
 * @Date 2021/2/19
 * @Param [file]
 * @Return void
 */
 @RequestMapping("importExcelMould")
 public void importExcelMould(@RequestParam("file") MultipartFile file) throws IOException {
    EasyExcelUtil<ExcelMouldEntity> listener = new EasyExcelUtil<>();
    InputStream inputStream = file.getInputStream();
    EasyExcel.read(inputStream, ExcelMouldEntity.class, listener).sheet(0).doRead();
    List<ExcelMouldEntity> data = listener.getData();
    System.out.println("导入的信息:");
    System.out.println(data);
 }

五. Postman 测试


参考网址

https://www.cnblogs.com/pzw23/p/12981617.html