【EasyExcel 单个多个sheet文件导入导出，通用超简单】

EasyExcel 单个多个sheet文件导入导出，通用超简单
废话少说，直接上代码：

1、pom依赖引入

        <!--alibaba-easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.0</version>
        </dependency>

2、Controller，web请求代码示例如下：

    /**
    * 导入excel文件
    **/
    @PostMapping("/import")
    public R importExcel(@RequestParam("file") MultipartFile multipartFile) throws IOException {
        // 读取文件开始
        List<DataListener> listData=new ArrayList<>();

        DataListener sheetListener1 = new DataListener<Ttt>(Ttt.class, "sheet1");
        DataListener sheetListener2 = new DataListener<Ttt>(Ttt.class,"sheet2");
        listData.add(sheetListener1);
        listData.add(sheetListener2);

        ExcelUtil.readExcel(multipartFile.getInputStream(),listData);


        listData.forEach(listDataInfo->{
            //将每个sheet对应的list入库，写自己的业务逻辑
            tttService.saveBatch(listDataInfo.list);
        });

        return R.ok();

    }

    /**
    * 导出excel文件
    **/
    @GetMapping("/export")
    public void exportExcel(HttpServletResponse response , TttVO vo) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        String encodedFileName = URLEncoder.encode(System.currentTimeMillis() + "", StandardCharsets.UTF_8.name()).replaceAll("\\+", "%20");
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename*=utf-8''" + encodedFileName + ".xlsx");

			//自己的业务数据列表
            List<Ttt> data1 = tttService.getAllTtts();
            List<Ttt> data2 = tttService.getAllTtts();

            //将数据写入sheet页中
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "sheet1").head(Ttt.class).build();
            WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "sheet2").head(Ttt.class).build();

            excelWriter.write(data1, writeSheet1);
            excelWriter.write(data2, writeSheet2);

            excelWriter.finish();
            response.flushBuffer();
        System.out.println("多sheetExcel导出成功");

    }

3、导入导出实体类

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.baomidou.mybatisplus.annotation.TableName;
import java.math.BigDecimal;
import io.swagger.annotations.*;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

import java.util.Date;
import java.lang.*;

/**
 * @author xmg
 * @date 2022年 05月15日 17:22:47
 */
 
@Data
@TableName("ttt")
public class Ttt{

    /**
     * 主键
     */
    @ExcelProperty(value = "主键")
    @ExcelIgnore
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    /**
     * 名称
     */
    @ExcelProperty(value = "名称")
    private String name;

    /**
     * 年龄
     */
    @ExcelProperty(value = "年龄")
    private Integer age;

    /**
     * 描述
     */
    @ExcelProperty(value = "描述")
    @ColumnWidth(20)
    private String dsc;

}

4、导入两个相关类：

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

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

/**
 * @author xmg
 * @date 2022年 05月15日 17:22:47
 */
public class DataListener<T> extends AnalysisEventListener<T> {

    //存放头的Key
    private static final String HEAD="head";

    public DataListener(Class clazz,String sheetName){
        this.classa=clazz;
        this.sheetName=sheetName;
        this.sheetHead=sheetName+HEAD;
    }
   public List<T> list= new ArrayList<>();
    public String sheetName;
   public   Class classa;

   public Map<Integer, String> headMap;

   public String sheetHead;


    /**
     * 获取到表头
     * @param headMap
     * @param context
     */
   @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
      this.headMap=headMap;
    }
    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        list.add(t);

    }

    /**
     * 所有数据解析完成了 都会来调用
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author xmg
 * @date 2022年 05月15日 17:22:47
 */
public class ExcelUtil {
    public static void readExcel(InputStream inputStream, List<DataListener> listData) {

/*
  List<MemberExcelData> list = EasyExcel.read(inputStream).sheet().head(MemberExcelData.class).doReadSync();
            System.out.println(list);
* */



        ExcelReader excelReader = EasyExcel.read(inputStream).build();
        List<ReadSheet> listReadSheet=new ArrayList<>();
        for (int i = 0; i < listData.size(); i++) {
            ReadSheet readSheet1 = EasyExcel.readSheet(listData.get(i).sheetName).head(listData.get(i).classa).registerReadListener(listData.get(i)).build();
            listReadSheet.add(readSheet1);
        }


    /*    DataListener dataListener1=  new DataListener<MemberExcelData>();

        ReadSheet readSheet1 = EasyExcel.readSheet(0).head(MemberExcelData.class).registerReadListener(dataListener1).build();

        DataListener dataListener2=  new DataListener<MemberExcelData>();
        ReadSheet readSheet2 =
                EasyExcel.readSheet(1).head(MemberExcelData.class).registerReadListener(dataListener2).build();
        excelReader.read(readSheet1, readSheet2);
        List<MemberExcelData> list1= dataListener1.list;
        System.out.println(list1);
        List<MemberExcelData> list2= dataListener2.list;
        System.out.println(list2);*/
        excelReader.read(listReadSheet);

        excelReader.finish();



    /*  List<MemberExcelData>  list1= CsvUtil.getCsvData(inputStream,MemberExcelData.class);
        System.out.println(list1);*/
    }

5、导出相关：导入easyExcel相关类即可

6、演示导出文件，我这里sheet1和sheet2内容相同（主键id忽略了）。

7、将导出的excel文件，导入到项目的表中，导入成功后记录