springboot整合easyexcel实现导入导出

1 引入依赖

 <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <!-- easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.10</version>
        </dependency>
        <!-- hutool-all -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.9</version>
        </dependency>
        <!-- fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.78</version>
        </dependency>

2 定义导入导出实体类

导出

package com.grm.pojo;


import java.util.Date;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.grm.converter.StringConverter;

import lombok.Data;
 
/**
 *desc:  导出模型
 *
 * ContentRowHeight 内容行高
 * HeadRowHeight 表头行高
 * ColumnWidth 列宽
 *
 * @author gaorimao
 * @date 2021/08/19
 */
@ContentRowHeight(20)
@HeadRowHeight(25)
@ColumnWidth(25)
@Data
public class ExportModel {
    /**
     * desc: 字段映射，第几列
     */
    @ExcelProperty(value = "姓名" ,index = 0)
    private String name;
 
    @ExcelProperty(value = "性别" ,index = 1)
    private String sex;
 
    @ExcelProperty(value = "年龄" ,index = 2)
    private Integer age;
    
    @ExcelProperty(value = "创建日期" ,index = 3)
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    private Date createTime;
    
    //百分比数字
    @ExcelProperty(value = "百分比" ,index = 4)
    @NumberFormat("#.##%")
    private Double doubleData;
    
    //自定义转换格式
    @ExcelProperty(value = "转换格式" ,index = 5,converter = StringConverter.class)
    private String stringTest;
}

导入

package com.grm.pojo;

import java.util.Date;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import com.grm.converter.StringConverter;

import lombok.Data;
 
/**
 * desc: 导入模型
 *
 * @author gaorimao
 * @date 2021/08/19
 */
@Data
public class ImportModel {
	 /**
     * desc: 字段映射，第几列
     */
    @ExcelProperty(value = "姓名" ,index = 0)
    private String name;
 
    @ExcelProperty(value = "性别" ,index = 1)
    private String sex;
 
    @ExcelProperty(value = "年龄" ,index = 2)
    private Integer age;
    
    @ExcelProperty(value = "创建日期" ,index = 3)
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    private Date createTime;
    
    //百分比数字
    @ExcelProperty(value = "百分比" ,index = 4)
    @NumberFormat("#.##%")
    private Double doubleData;
    
    //自定义转换格式
    @ExcelProperty(value = "转换格式" ,index = 5,converter = StringConverter.class)
    private String stringTest;
}

3 自定义格式转换类

package com.grm.converter;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

/**
 * desc: 自定义String格式转换器
 * 
 * @author: gaorimao
 * @since: 2021-8-19
 */
public class StringConverter implements Converter<String> {
    @Override
    public Class supportJavaTypeKey() {
        return String.class;
    }
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
    /**
     * desc: 这里读的时候会调用
     *
     * @param cellData
     *            NotNull
     * @param contentProperty
     *            Nullable
     * @param globalConfiguration
     *            NotNull
     * @return
     */
    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
        GlobalConfiguration globalConfiguration) {
        return "自定义读："+cellData.getStringValue();
    }
    /**
     * desc: 这里是写的时候会调用
     *
     * @param value
     *            NotNull
     * @param contentProperty
     *            Nullable
     * @param globalConfiguration
     *            NotNull
     * @return
     */
    @Override
    public CellData convertToExcelData(String value, ExcelContentProperty contentProperty,
        GlobalConfiguration globalConfiguration) {
        //return new CellData(value);
    	return new CellData("自定义写：" + value);
    }
}

4 定义Listener

package com.grm.listener;

import java.util.ArrayList;
import java.util.List;
 
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.grm.mapper.UserMapper;

import lombok.extern.slf4j.Slf4j;
 
/**
 * excel监听类
 *
 * @author gaorimao
 * @date 2021/08/19
 */
@Slf4j
public class ExcelListener extends AnalysisEventListener {
	 /**
     * desc: 每隔3000条存储数据库，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 3000;
    /**
     * desc: 可以通过实例获取该值
     */
    private List<Object> dataList = new ArrayList<>();
    
//    /**
//     * desc: 假设这个是一个mapper，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
//     */
//    private UserMapper userMapper;
//    
//    /**
//     * desc: 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
//     *
//     * @param userMapper
//     */
//    public ExcelListener(UserMapper userMapper) {
//        this.userMapper = userMapper;
//    }
    
    /**
     * desc: 这个每一条数据解析都会来调用
     *
     * @param data
     * @param context
     */
    @Override
    public void invoke(Object object, AnalysisContext context) {
    	log.info("解析到一条数据:{}", JSON.toJSONString(object));
        //数据存储到list，供批量处理，或后续自己业务逻辑处理。
        dataList.add(object);
        
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (dataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            dataList.clear();
        }
    }
 
    /**
     * desc: 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
    	// 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }
 
    //根据业务自行实现该方法，例如将解析好的dataList存储到数据库中
    private void saveData() {
    	//userMapper.save();
    }
 
    public List<Object> getDataList() {
        return dataList;
    }
 
    public void setDataList(List<Object> dataList) {
        this.dataList = dataList;
    }
}

5 easyexcel工具类

package com.grm.util;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;

import cn.hutool.core.convert.Convert;
import com.grm.listener.ExcelListener;

/**
 * excel工具类
 *
 * @author gaorimao
 * @date 2021/08/19
 */
public class ExcelUtil {
    /**
     * 读取Excel（多个sheet可以用同一个实体类解析）
     * @param excelInputStream
     * @param fileName
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> List<T> readExcel(InputStream excelInputStream, String fileName,Class<T> clazz) {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader excelReader = getReader(excelInputStream, fileName,clazz, excelListener);
        if (excelReader == null) {
            return new ArrayList<>();
        }
        List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
        for (ReadSheet readSheet : readSheetList) {
            excelReader.read(readSheet);
        }
        excelReader.finish();
        return Convert.toList(clazz, excelListener.getDataList());
    }

    /**
     * 导出Excel(一个sheet)
     *
     * @param response  HttpServletResponse
     * @param list      数据list
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的sheet名
     * @param clazz     实体类
     */
    public static <T> void writeExcel(HttpServletResponse response, List<T> list, String fileName, String sheetName, Class<T> clazz) {

        OutputStream outputStream = getOutputStream(response, fileName);
        ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
        excelWriter.write(list, writeSheet);
        excelWriter.finish();
    }


    /**
     * 导出时生成OutputStream
     */
    private static OutputStream getOutputStream(HttpServletResponse response, String fileName) {
        //创建本地文件
        String filePath = fileName + ".xlsx";
        File file = new File(filePath);
        try {
            if (!file.exists() || file.isDirectory()) {
                file.createNewFile();
            }
            fileName = new String(filePath.getBytes(), "ISO-8859-1");
            response.addHeader("Content-Disposition", "filename=" + fileName);
            return response.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 返回ExcelReader
     *
     * @param inputStream         输入流
     * @param filename         文件
     * @param clazz         实体类
     * @param excelListener
     */
    private static <T> ExcelReader getReader(InputStream inputStream, String filename, Class<T> clazz, ExcelListener excelListener) {
        try {
            if (filename == null ||
                (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
                return null;
            }
            ExcelReader excelReader = EasyExcel.read(inputStream, clazz, excelListener).build();
            inputStream.close();
            return excelReader;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

6 controller

package com.grm.controller;


import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
 
import javax.servlet.http.HttpServletResponse;
 
import com.alibaba.fastjson.JSON;
 
import com.grm.pojo.ExportModel;
import com.grm.pojo.ImportModel;
import com.grm.util.ExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
 
/**
 * @author gaorimao
 * @date 2021/08/19
 */
@RestController
@Slf4j
@RequestMapping("/excel")
public class ExcelController {
    @PostMapping("/import")
    public List<ImportModel> read(@RequestParam("file")MultipartFile excel) throws IOException {
        List<ImportModel> datas = ExcelUtil.readExcel(excel.getInputStream(), excel.getOriginalFilename(),
            ImportModel.class);
        log.info("[excel] datas = {}", JSON.toJSONString(datas));
        return datas;
    }
 
    @GetMapping("/export")
    public String writeExcel(HttpServletResponse response) {
        List<ExportModel> list = getList();
        String fileName = "Excel导出测试";
        String sheetName = "sheet1";
        ExcelUtil.writeExcel(response, list, fileName, sheetName, ExportModel.class);
        return "success";
    }
 
    /**
     * desc: 模拟数据
     *
     * @return {@link List<ExportModel>}
     */
    private List<ExportModel> getList() {
        List<ExportModel> modelList = new ArrayList<>();
        ExportModel firstModel = new ExportModel();
        firstModel.setName("李明");
        firstModel.setSex("男");
        firstModel.setAge(20);
        firstModel.setCreateTime(new Date());
        firstModel.setDoubleData(0.2683);
        firstModel.setStringTest("gaorimao");
        modelList.add(firstModel);
 
        ExportModel secondModel = new ExportModel();
        secondModel.setName("珍妮");
        secondModel.setSex("女");
        secondModel.setAge(19);
        secondModel.setCreateTime(new Date());
        secondModel.setDoubleData(0.2015);
        secondModel.setStringTest("tieren");
        modelList.add(secondModel);
 
        return modelList;
    }
}

6 测试

测试导出

测试导入

用这个excel测试

测试结果，ok