java导入excel操作多sheet页上传

依赖包

 <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.1</version>
        </dependency>

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


        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
      <!--  <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>--> 

js代码

function confirmUpload() {
	var doc = document.getElementById('file');
	for(var i=0;i<doc.files.length;i++){
		var name = doc.files[i].name;
		var hz =name.substring( name.lastIndexOf(".")+1);
		if(hz!="xlsx"&&hz!="xls"){
			alert("文件格式错误");
			return false;
		}
	}
	var $file1 = $("#file").val();//用户文件内容(文件)
	// 判断文件是否为空
	if ($file1 == "") {
		alert("请选择上传的目标文件! ")
		return false;
	}
	var formData = new FormData();
	//数据赋值给format
	formData.append("file", $('#file')[0].files[0]);
	//执行上传
	$.ajax({
		type: "POST",
		url: baseURL + 'upload/importsheet',
		processData: false,
		contentType: false,
		data: formData,  //相当于 //data: "{'str1':'foovalue', 'str2':'barvalue'}",
		success: function (jsonResult) {
			if (jsonResult.msg="ok"){
               alert("上传成功",function(result){
                    if(result=="ok"){
                    window.location.reload();
            }
                })
			}else {
				alert("上传失败!")
			}

		},
	});

}; 

controller代码

package com.wgjn.modules.cms.controller;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import com.wgjn.common.utils.R;
import com.wgjn.common.utils.UploadUtils;
import com.wgjn.modules.cms.entity.*;
import com.wgjn.modules.cms.service.SysDataUploadService;
import com.wgjn.modules.cms.service.SysFilesUploadService;
import org.apache.poi.ss.usermodel.Workbook;
import org.json.JSONException;
import org.springframework.beans.factory.annotation.Autowired;
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;

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

@RestController
@RequestMapping("/upload")
public class SysDataUploadController {

    @Autowired
    private SysDataUploadService sysDataUploadService;

    @PostMapping("/importsheet")
    public R importSheets(@RequestParam("file") MultipartFile file) {
        List<SystemBaseInfoEntity> systemBaseInfoEntityList = new ArrayList<>();
        List<DevopsSystemBaseInfoEntity> devopsSystemBaseInfoEntityList = new ArrayList<>();
        List<DevopsSystemMappingInfoEntity> devopsSystemMappingInfoEntityList = new ArrayList<>();
        List<SysDeviceEntity> deviceEntityList = new ArrayList<>();
        List<ComponentBaseInfoEntity> componentBaseInfoEntityList = new ArrayList<>();
        List<SystemDeviceComponentRelEntity> systemDeviceComponentRelEntityList = new ArrayList<>();
        try {
            // 根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
            Workbook workBook = UploadUtils.getWorkBook(file);

            ImportParams params = new ImportParams();
            // 循环工作表Sheet
            for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
                String name = workBook.getSheetName(numSheet);
                // 表头在第几行
                params.setTitleRows(0);
                // 距离表头中间有几行不要的数据
                params.setStartRows(0);
                // 第几个sheet页
                params.setStartSheetIndex(numSheet);
                // 验证数据
                params.setNeedVerfiy(true);
                if ("OA系统字典项".equals(name)) {
                    ExcelImportResult<SystemBaseInfoEntity> result = ExcelImportUtil.importExcelMore(file.getInputStream(),
                            SystemBaseInfoEntity.class, params);

                    // 合格的数据
                    systemBaseInfoEntityList = result.getList();
//                    sysFilesUploadService.fileUpload(systemBaseInfoEntityList);
                    // 业务逻辑

                } else if ("运维系统字典信息".equals(name)) {
                    ExcelImportResult<DevopsSystemBaseInfoEntity> result = ExcelImportUtil.importExcelMore(file.getInputStream(),
                            DevopsSystemBaseInfoEntity.class, params);

                    // 校验合格的数据
                    devopsSystemBaseInfoEntityList = result.getList();
                    // 业务逻辑
//                    sysFilesUploadService.fileUpload(devopsSystemBaseInfoEntityList);
                } else if ("设备信息".equals(name)) {
                    ExcelImportResult<SysDeviceEntity> result = ExcelImportUtil.importExcelMore(file.getInputStream(),
                            SysDeviceEntity.class, params);

                    // 校验合格的数据
                    deviceEntityList = result.getList();
                    // 业务逻辑
                } else if ("组件字典信息".equals(name)) {
                    ExcelImportResult<ComponentBaseInfoEntity> result = ExcelImportUtil.importExcelMore(file.getInputStream(),
                            ComponentBaseInfoEntity.class, params);

                    // 校验合格的数据
                    componentBaseInfoEntityList = result.getList();
                    // 业务逻辑
                } else if ("设备组件".equals(name)) {
                    ExcelImportResult<SystemDeviceComponentRelEntity> result = ExcelImportUtil.importExcelMore(file.getInputStream(),
                            SystemDeviceComponentRelEntity.class, params);

                    // 校验合格的数据
                    systemDeviceComponentRelEntityList = result.getList();
                    // 业务逻辑
                } else if ("系统字典关联".equals(name)) {
                    ExcelImportResult<DevopsSystemMappingInfoEntity> result = ExcelImportUtil.importExcelMore(file.getInputStream(),
                            DevopsSystemMappingInfoEntity.class, params);

                    // 校验合格的数据
                    devopsSystemMappingInfoEntityList = result.getList();
                    // 业务逻辑
                }


            }
        } catch (Exception e) {

            return R.error("导入失败！请检查导入文档的格式是否正确");
        }

        String status = sysDataUploadService.fileUpload(systemBaseInfoEntityList, devopsSystemBaseInfoEntityList, deviceEntityList, componentBaseInfoEntityList, systemDeviceComponentRelEntityList, devopsSystemMappingInfoEntityList);
        if ("ok".equals(status)) {
            return R.ok();
        } else {
            return R.error();
        }

    }

} 

本文地址：https://blog.csdn.net/weixin_45805531/article/details/107684192