EasyExcel导入导出

EasyExcel导入导出

导入依赖

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

EasyExcel工具类

package com.company.project.common.easyexcelUtil;

import com.alibaba.excel.EasyExcel;
import lombok.extern.slf4j.Slf4j;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;

/**
 * excel工具类
 * easyexcel使用的3.0.2版本，跟以前版本有很大区别，且不兼容1.x版本
 *
 * @author lxd
 */
@Slf4j
public class EasyExcelUtil {


    public static <T> List<T> read(String filePath, final Class<?> clazz) {
        File f = new File(filePath);
        try (FileInputStream fis = new FileInputStream(f)) {
            return read(fis, clazz);
        } catch (FileNotFoundException e) {
            log.error("文件{}不存在", filePath, e);
        } catch (IOException e) {
            log.error("文件读取出错", e);
        }

        return null;
    }

    public static <T> List<T> read(InputStream inputStream, final Class<?> clazz) {
        if (inputStream == null) {
            throw new MyAppRunException("解析出错了，文件流是null");
        }

        // 有个很重要的点 DataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        DataListener<T> listener = new DataListener<>();

        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(inputStream, clazz, listener).sheet().doRead();
        return listener.getRows();
    }

    public static void write(String outFile, List<?> list) {
        Class<?> clazz = list.get(0).getClass();
        // 新版本会自动关闭流，不需要自己操作
        EasyExcel.write(outFile, clazz).sheet().doWrite(list);
    }

    public static void write(String outFile, List<?> list, String sheetName) {
        Class<?> clazz = list.get(0).getClass();
        // 新版本会自动关闭流，不需要自己操作
        EasyExcel.write(outFile, clazz).sheet(sheetName).doWrite(list);
    }

    public static void write(OutputStream outputStream, List<?> list, String sheetName) {
        Class<?> clazz = list.get(0).getClass();
        // 新版本会自动关闭流，不需要自己操作
        // sheetName为sheet的名字，默认写第一个sheet
        EasyExcel.write(outputStream, clazz).sheet(sheetName).doWrite(list);
    }

    /**
     * 文件下载（失败了会返回一个有部分数据的Excel），用于直接把excel返回到浏览器下载
     */
    public static void download(HttpServletResponse response, List<?> list, String sheetName) throws IOException {
        Class<?> clazz = list.get(0).getClass();

        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode(sheetName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(list);
    }

}

EasyExcel解析过程监听器

package com.company.project.common.easyexcelUtil;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
import java.util.ArrayList;
import java.util.List;
 
/**
 * excel解析过程监听器
 *
 * @author lxd
 */
public class DataListener<T> extends AnalysisEventListener<T> {
 
    private static final Logger LOGGER = LoggerFactory.getLogger(DataListener.class);
 
    private final List<T> rows = new ArrayList<>();
 
    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        rows.add(t);
    }
 
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        LOGGER.info("解析完成！读取{}行", rows.size());
    }
 
    public List<T> getRows() {
        return rows;
    }
}
 
 

解析对象

package com.company.project.common.easyexcelUtil;

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

import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
 public class Student {
     @ExcelProperty(value = "姓名", index = 0)
     private String name;

     @ExcelProperty(value = "年龄", index = 1)
     private int age;

     // easyexcel日期类型不支持LocalDate，只能是Date
     @ExcelProperty(value = "生日", index = 2)
     @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
     private Date birthday;

    @ExcelProperty(value = "薪资", index = 3)
    private Double pay;
 }
 

测试

package com.company.project.common.easyexcelUtil;

import com.alibaba.druid.support.json.JSONUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 功能描述:
 *
 * @Author: lxd
 * @description:easyexcel导入导出下载测试
 */
@RequestMapping("/excel")
@RestController
public class ExcelTest {

    @GetMapping(value = "/download")
    public String download(HttpServletRequest request, HttpServletResponse response) throws IOException {

        // 写入测试
        List<Student> stus = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            stus.add(new Student("张1", 12 + i, new Date(),new Double(Math.random())));
        }
        EasyExcelUtil.download(response, stus, "学生信息表");
        return "";
    }

    public static void main(String[] args) {
        // 读取测试
//        String readFile = "C:\\Desktop\\测试.xlsx";
//        List<Student> data = EasyExcelUtil.read(readFile, Student.class);
//        System.out.println(data);

        // 写入测试
//        List<Student> stus = new ArrayList<>();
//        for (int i = 0; i < 10; i++) {
//            stus.add(new Student("张1", 12 + i, new Date(),new Double(Math.random())));
//        }
//        String file = "C:\\student信息表2.xlsx";
//        EasyExcelUtil.write(file, stus);



    }


}