java导入excel操作多sheet页上传
程序员文章站
2022-06-09 15:47:46
依赖包 cn.afterturn easypoi-base 3.0.1 ...
依赖包
<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