EasyPoi实现excel多sheet导入

EXCEL

导入模板

MAVEN

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.1.0</version>
</dependency>

Controller

package io.btm.modules.importexcel;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import io.btm.common.utils.R;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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 java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


@RestController
@RequestMapping("importExcel")
public class ExcelController {

    /**
     * 导入
     */
    @PostMapping("import")
    @ApiOperation(value = "导入")
    public R ordergeneratexml(MultipartFile file) throws Exception {
        //根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
        Workbook hssfWorkbook = getWorkBook(file);
        //获取sheet数量
        int sheetNum = hssfWorkbook.getNumberOfSheets();
        ImportParams params = new ImportParams();
        //表头在第几行
        params.setTitleRows(3);
        //是否需要通过key-value导入方法，获取特定字段
        params.setReadSingleCell(true);
        //判断一个cell是key的规则,可以自定义,默认就是 “:”
        params.setKeyMark("：");
        List<ExcelVo> listAll = new ArrayList<>();
        for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
            //第几个sheet页
            params.setStartSheetIndex(sheetIndex);
            //获取表头下的数据
            ExcelImportResult<ExcelVo> result = ExcelImportUtil.importExcelMore(file.getInputStream(), ExcelVo.class, params);
            List<ExcelVo> list = result.getList();
            //获取特定字段的map
            Map<String, Object> map = result.getMap();
            list.forEach(entity -> {
                entity.setRukuriqi(map.get("日期：").toString());
                entity.setDianpumingchen(map.get("店铺名：").toString());
            });
            listAll.addAll(list);
        }
        return R.ok().put("listAll", listAll);
    }

    public static Workbook getWorkBook(MultipartFile file) throws IOException {
        //这样写excel能兼容03和07
        InputStream is = file.getInputStream();
        Workbook hssfWorkbook = null;
        try {
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (Exception ex) {
            is =file.getInputStream();
            hssfWorkbook = new XSSFWorkbook(is);
        }
        return hssfWorkbook;
    }

}

实体类VO

package io.btm.modules.importexcel;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelVo {
    @Excel(name = "序号")
    private String indexNum;
    @Excel(name = "品名")
    private String name;
    @Excel(name = "订货数（斤）")
    private String dinghuoNum;
    @Excel(name = "去皮斤数")
    private String qupiNum;
    @Excel(name = "去皮斤数")
    private String sunhaoNum;
    @Excel(name = "供货单价")
    private String gonghuoNum;
    @Excel(name = "订单金额")
    private String dingnanNum;
    @Excel(name = "订单金额")
    private String qupijine;
    @Excel(name = "订单金额")
    private String sunhaojine;
    @Excel(name = "产品差价")
    private String chanpindanjia;
    @Excel(name = "实际结账")
    private String shijijiezhang;
    @Excel(name = "是否结款")
    private String shifoujiekuan;
	@Excel(name = "是否结款")
    private String beizhu;
    private String rukuriqi;
    private String dianpumingchen;
}

源码地址
开发指南