java excel 多sheet页_java导入excel操作多sheet页上传

依赖包

cn.afterturn

easypoi-base

3.0.1

cn.afterturn

easypoi-web

3.0.1

cn.afterturn

easypoi-annotation

3.0.1

js代码

function confirmUpload() {

var doc = document.getElementById('file');

for(var i=0;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 systemBaseInfoEntityList = new ArrayList<>();

List devopsSystemBaseInfoEntityList = new ArrayList<>();

List devopsSystemMappingInfoEntityList = new ArrayList<>();

List deviceEntityList = new ArrayList<>();

List componentBaseInfoEntityList = new ArrayList<>();

List 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 result = ExcelImportUtil.importExcelMore(file.getInputStream(),

SystemBaseInfoEntity.class, params);

// 合格的数据

systemBaseInfoEntityList = result.getList();

// sysFilesUploadService.fileUpload(systemBaseInfoEntityList);

// 业务逻辑

} else if ("运维系统字典信息".equals(name)) {

ExcelImportResult result = ExcelImportUtil.importExcelMore(file.getInputStream(),

DevopsSystemBaseInfoEntity.class, params);

// 校验合格的数据

devopsSystemBaseInfoEntityList = result.getList();

// 业务逻辑

// sysFilesUploadService.fileUpload(devopsSystemBaseInfoEntityList);

} else if ("设备信息".equals(name)) {

ExcelImportResult result = ExcelImportUtil.importExcelMore(file.getInputStream(),

SysDeviceEntity.class, params);

// 校验合格的数据

deviceEntityList = result.getList();

// 业务逻辑

} else if ("组件字典信息".equals(name)) {

ExcelImportResult result = ExcelImportUtil.importExcelMore(file.getInputStream(),

ComponentBaseInfoEntity.class, params);

// 校验合格的数据

componentBaseInfoEntityList = result.getList();

// 业务逻辑

} else if ("设备组件".equals(name)) {

ExcelImportResult result = ExcelImportUtil.importExcelMore(file.getInputStream(),

SystemDeviceComponentRelEntity.class, params);

// 校验合格的数据

systemDeviceComponentRelEntityList = result.getList();

// 业务逻辑

} else if ("系统字典关联".equals(name)) {

ExcelImportResult 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

希望与广大网友互动？？

点此进行留言吧！