EasyExcel两种方式导入

方式一：
Controller层

 @PostMapping("/import")
    public Result<String> importDemo(HttpServletRequest request, @RequestParam("file") MultipartFile file) {
   
        log.info("import.request:{}", file.getOriginalFilename());
        if (file.getOriginalFilename() == null || !file.getOriginalFilename().endsWith("xlsx")) {
   
            throw new Exception("只支持.xlsx类型的文件导入!");
        }
        // 读取Excel内容
        List<DemoPo> demoList;
        try {
   
            ExcelImportPoSoAdapter adapter = new ExcelImportPoSoAdapter();
            EasyExcelFactory.read(file.getInputStream(), DemoPo.class, adapter).doReadAll();
            Map<String, String> map = adapter.getMap();
            if (map.size() != 0){
   
                throw  new BizException(map.get("msg"));
            }
            demoList= adapter.demoList();
        } catch (Exception e) {
   
            log.error("import.error:", e);
            throw new Exception("您上传的文件格式与模板格式不一致，请检查后重新上传");
        }
        if(demoList.size() > 500){
   
            throw new Exception("Excel数据超出限制, 最大支持导入500条!");
        }
        if (ObjectUtil.isEmpty(demoList)) {
   
            throw new Exception("导入数据为空，请添加数据后再次导入");
        }
        demoUserCase.save(request, demoList);
        log.info("import:success");
        return Result.buildResult(null);
    }

Excel适配器类

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
 
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
/** * Excel适配器 */
@Slf4j
@Getter
public class ExcelImportPoSoAdapter extends AnalysisEventListener<ExcelPayload> {
   
    
    /** * 数据集合 */
    List<DemoPo> demoPoList = new ArrayList<>();
 
    // 此map用来存储错误提示
    protected Map<String, String> map = new HashMap<>();
 
     /** * 校验表头 */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
   
        // headMap.containsKey(下标)，先判空
        // headMap.get(0).equals() 判内容是否相符
        if (!headMap.containsKey(0) || !headMap.containsKey(1)
                || !headMap.get(0).equals("enmu1") || !headMap.get(1).equals("enmu2")) {
   
            // 这里给demoPoList 加1条空数据，是因为doAfterAllAnalysed方法最后有判是否是空列表
            demoPoList.add(new DemoPo());
            map.put("msg", "您上传的文件格式与模板格式不一致，请检查后重新上传");
        }
    }
    @Override
    public void invoke(ExcelPayload excelPayload, AnalysisContext analysisContext) {
   
        demoPoList .add(excelPayload.excelPayloadToDemoPo(excelPayload));
    }
 
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
   
        log.info("ExcelImportAdapter.doAfterAllAnalysed.success");
    }
}

service
接着写业务的逻辑校验

执行批量导入

注：注意List切片，因为sql传入参数最大限制2100个

import com.google.common.collect.Lists;
上方为该Lists导包
List<List<DemoPo>> partition = Lists.partition(demoList, 100);

方式二：
1.项目依赖
demo项目基于springboot2.2.1版本。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.1.RELEASE</version>
        <relativePath/>
    </parent>

    <groupId>com.evan.easyexcel</groupId>
    <artifactId>easyexcel-basic-demo</artifactId>
    <version>1.0.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>easyexcel-basic-demo</name>
    <description>Easy excel 2.x import and export demo</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </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>

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

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

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

2.ExcelApplication 主启动类

package com.evan.easyexcel;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class ExcelApplication {
   

    public static void main(String[] args) {
   
        SpringApplication.run(ExcelApplication.class, args);
    }
}

3.定义导入和导出模型

package com.evan.easyexcel.model;

import com.alibaba.excel.annotation.ExcelProperty;
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 lombok.Data;

/** * @ClassName ExportModel * @Description 导出模型 * @Author Evan Wang * @Version 1.0.0 * @Date 2020/4/1 20:55 */
@ContentRowHeight(20)
@HeadRowHeight(25)
@ColumnWidth(25)
@Data
public class ExportModel  {
   

    @ExcelProperty(value = "姓名" ,index = 0)
    private String name;

    @ExcelProperty(value = "性别" ,index = 1)
    private String sex;

    @ExcelProperty(value = "年龄" ,index = 2)
    private Integer age;

}

package com.evan.easyexcel.model;

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

/** * @ClassName ImportModel * @Description 导入模型 * @Author Evan Wang * @Version 1.0.0 * @Date 2020/4/1 20:54 */
@Data
public class ImportModel {
   

    @ExcelProperty(index = 0)
    private String date;

    @ExcelProperty(index = 1)
    private String author;

    @ExcelProperty(index = 2)
    private String book;

}

4.Object与实体类转换工具类

package com.evan.easyexcel.utils.common;

import org.springframework.beans.BeanUtils;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/** * @ClassName ExportModel * @Description Object与实体类转换工具类 * @Author Evan Wang * @Version 1.0.0 * @Date 2020/4/1 20:59 */
public class BeanConvert {
   
    private BeanConvert(){
   }
    /** * 将List<Object> 转换为List<Bean> * @param sources 源对象 * @param targetClass 目标类 * @param <T> * @return */
    public static <T> List<T> objectConvertBean(List<?> sources, Class<T> targetClass) {
   
        List<?> sourcesObj = sources;
        if (sourcesObj == null) {
   
            sourcesObj = Collections.emptyList();
        }
        List<T> targets = new ArrayList<>(sourcesObj.size());
        convert(sourcesObj, targets, targetClass);
        return targets;
    }

    /** * 复制源对象到目的对象 * 注意： * org.springframework.beans.BeanUtils.copyProperties 是一个Spring提供的名称相同的工具类 * 但它不支持类型自动转换，如果某个类型属性不同，则不予转换那个属性 * org.apache.commons.beanutils.BeanUtils 是一个Apache提供的名称相同的工具类 * 支持类型自动转换，如Date类型会自动转换为字符串 * @param sources 源对象 * @param targets 目的对象 * @param targetClass 目标类 * @param <T> */
    private static <T> void convert(List<?> sources, List<T> targets, Class<T> targetClass) {
   
        if (sources == null) {
   
            return;
        }
        if (targets == null) {
   
            return;
        }
        targets.clear();
        for (Object obj : sources) {
   
            try {
   
                T target = targetClass.newInstance();
                targets.add(target);
                BeanUtils.copyProperties(obj, target);
            } catch (Exception e) {
   
                return;
            }
        }
    }

}

5.ExcelListener 监听器

package com.evan.easyexcel.utils.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.evan.easyexcel.model.ImportModel;

import java.util.ArrayList;
import java.util.List;

/** * @Author Evan Wang * @Description 监听类 * @Date 2019-09-16 */
public class ExcelListener extends AnalysisEventListener {
   
    //可以通过实例获取该值
    private List<Object> dataList = new ArrayList<>();


    @Override
    public void invoke(Object object, AnalysisContext context) {
   
        //数据存储到list，供批量处理，或后续自己业务逻辑处理。
        dataList.add(object);
        handleBusinessLogic();

          /* 如数据过大，可以进行定量分批处理 if(dataList.size()>=200){ handleBusinessLogic(); dataList.clear(); } */
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
   
        //非必要语句，查看导入的数据
        System.out.println("导入的数据 " + dataList.toString());
        //解析结束销毁不用的资源
        dataList.clear();
    }

    //根据业务自行实现该方法，例如将解析好的dataList存储到数据库中
    private void handleBusinessLogic() {
   

    }

    public List<Object> getDataList() {
   
        return dataList;
    }

    public void setDataList(List<Object> dataList) {
   
        this.dataList = dataList;
    }
}

6.ExcelUtil (核心类)
ExcelUtil对easyexcel2.X进行封装，实现一个方法完成简单的excel导入和导出。

package com.evan.easyexcel.utils.excel;

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 com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.evan.easyexcel.utils.common.BeanConvert;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.web.multipart.MultipartFile;

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

/** * * @Author Evan Wang * @Description Excel读写工具类 * @Date 2019-09-16 */
public class ExcelUtil {
   

    private ExcelUtil(){
   }

    /** * 读取Excel（一个sheet） * @param excel 文件 * @param clazz 实体类 * @param sheetNo sheet序号 * @return 返回实体列表(需转换) */
    public static <T> List<T> readExcel(MultipartFile excel, Class<T> clazz,int sheetNo) {
   

        ExcelListener excelListener = new ExcelListener();

        ExcelReader excelReader = getReader(excel,clazz,excelListener);
        if (excelReader == null) {
   
            return new ArrayList<>();
        }

        ReadSheet readSheet = EasyExcel.readSheet(sheetNo).build();
        excelReader.read(readSheet);
        excelReader.finish();

        return BeanConvert.objectConvertBean(excelListener.getDataList(), clazz);
    }


    /** * 读取Excel（多个sheet可以用同一个实体类解析） * @param excel 文件 * @param clazz 实体类 * @return 返回实体列表(需转换) */
    public static <T> List<T> readExcel(MultipartFile excel, Class<T> clazz) {
   

        ExcelListener excelListener = new ExcelListener();
        ExcelReader excelReader = getReader(excel,clazz,excelListener);

        if (excelReader == null) {
   
            return new ArrayList<>();
        }

        List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();

        for (ReadSheet readSheet:readSheetList){
   
            excelReader.read(readSheet);
        }
        excelReader.finish();

        return BeanConvert.objectConvertBean(excelListener.getDataList(), clazz);
    }


    /** * 导出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();
    }


    /** * 导出Excel(带样式) * * @return */
    public static  <T> void writeStyleExcel(HttpServletResponse response,List<T> list, String fileName, String sheetName, Class<T> clazz) {
   
        //表头策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //背景浅灰
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)20);
        headWriteCellStyle.setWriteFont(headWriteFont);

        //内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 否则无法显示背景颜色；头默认了FillPatternType
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        //背景浅绿
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        //字体大小
        contentWriteFont.setFontHeightInPoints((short)15);
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        OutputStream outputStream = getOutputStream(response, fileName);
        EasyExcel.write(outputStream, clazz).registerWriteHandler(horizontalCellStyleStrategy).sheet(sheetName).doWrite(list);

    }


    /** * 导出Excel(动态表头) * write时不传入class,table时传入并设置needHead为false * @return */
    public static  <T> void writeDynamicHeadExcel(HttpServletResponse response,List<T> list, String fileName, String sheetName, Class<T> clazz,List<List<String>> headList) {
   

        OutputStream outputStream = getOutputStream(response, fileName);

        EasyExcel.write(outputStream)
                .head(headList)
                .sheet(sheetName)
                .table().head(clazz).needHead(Boolean.FALSE)
                .doWrite(list);
    }


    /** * 导出时生成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 excel 文件 * @param clazz 实体类 * @param excelListener */
    private static <T> ExcelReader getReader(MultipartFile excel, Class<T> clazz, ExcelListener excelListener) {
   
        String filename = excel.getOriginalFilename();

        try {
   
            if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
   
                return null;
            }

            InputStream inputStream = new BufferedInputStream(excel.getInputStream());

            ExcelReader excelReader = EasyExcel.read(inputStream, clazz, excelListener).build();

            inputStream.close();

            return excelReader;
        } catch (Exception e) {
   
            e.printStackTrace();
        }
        return null;
    }
}

7.ExcelTestController

package com.evan.easyexcel.controller;

import com.evan.easyexcel.model.ExportModel;
import com.evan.easyexcel.model.ImportModel;
import com.evan.easyexcel.utils.excel.ExcelUtil;
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.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

/** * @ClassName ExcelTestController * @Description * @Author Evan Wang * @Version 1.0.0 * @Date 2020/4/1 21:25 */
@RestController
@RequestMapping(value = "/easyExcel")
public class ExcelTestController {
   

    @PostMapping(value = "/import")
    public List<ImportModel> read(MultipartFile excel) {
   
        return ExcelUtil.readExcel(excel, ImportModel.class, 0);
    }

    @GetMapping(value = "/export")
    public void writeExcel(HttpServletResponse response) {
   
        List<ExportModel> list = getList();
        String fileName = "Excel导出测试";
        String sheetName = "sheet1";
        ExcelUtil.writeDynamicHeadExcel(response, list, fileName, sheetName, ExportModel.class, head());
    }

    private List<ExportModel> getList() {
   
        List<ExportModel> modelList = new ArrayList<>();

        ExportModel firstModel = new ExportModel();
        firstModel.setName("李明");
        firstModel.setSex("男");
        firstModel.setAge(20);
        modelList.add(firstModel);

        ExportModel secondModel = new ExportModel();
        secondModel.setName("珍妮");
        secondModel.setSex("女");
        secondModel.setAge(19);
        modelList.add(secondModel);

        return modelList;
    }

    private List<List<String>> head() {
   
        List<List<String>> headList = new ArrayList<>();
        List<String> nameHead = new ArrayList<>();
        nameHead.add("姓名");
        List<String> genderHead = new ArrayList<>();
        genderHead.add("性别");
        List<String> ageHead = new ArrayList<>();
        ageHead.add("年龄");
        headList.add(nameHead);
        headList.add(genderHead);
        headList.add(ageHead);

        return headList;
    }
}

8.application.yml 端口号配置

server:
  port: 8090

四、项目测试
1.导出测试
先启动项目，然后在浏览器中访问如下地址。

http://localhost:8090/easyExcel/export

五、总结
到此为止，EasyExcel 2.x版本的代码示范就结束了，需要源码的小伙伴可以访问我的git获取。
虽然上面已经写过了，但是避免粗心的小伙伴看不到，再重复一下。

源码地址：https://github.com/wangming2674/easyexcel-basic-demo

另外，我在应用时，是前后端分离架构，前端用的vue.js。
————————————————
版权声明：本文为CSDN博主「Evan Wang」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_41378597/article/details/105267953