以下是利用alibaba easyexcel读取excel中多个sheet中的参考示例代码:
controller控制层方法:
@ApiOperation("上传文件")
@RequestMapping(value = "/upload", method = RequestMethod.POST)
public R upload(@RequestParam("file") MultipartFile file,@RequestParam(value = "processType",required = false) Integer processType,@RequestParam(value = "sourceAuditId",required = false) Integer auditId) throws Exception {
// 获取文件名
if(file==null){
return R.error("文件不能为空");
}
String fileName = file.getOriginalFilename();
if (StringUtils.isEmpty(fileName)){
return R.error("文件名不能为空");
}
// 获取文件后缀
String prefix=fileName.substring(fileName.lastIndexOf("."));
if (!prefix.toLowerCase().contains("xls") && !prefix.toLowerCase().contains("xlsx") ){
return R.error("文件格式异常,请上传Excel文件格式");
}
//由于2003和2007的版本所使用的接口不一样,所以这里统一用Workbook做兼容
boolean isExcel2003 = prefix.toLowerCase().endsWith("xls")?true:false;
InputStream inputStream=null;
UploadAssetDataVO uploadAssetDataVO=null;
try{
inputStream= file.getInputStream();
uploadAssetDataVO = uploadAssetService.uploadAssetData(inputStream,processType,isExcel2003);
return R.okWithData(uploadAssetDataVO);
}catch (Exception e){
e.printStackTrace();
if(e.getMessage()!=null&&( e.getMessage().contains("Office")|| e.getMessage().contains("模板") ) ){
return R.error("上传失败,原因如下:"+"导入模板不对");
}else{
return R.error("上传失败,原因如下:"+e.getMessage());
}
}finally {
if(inputStream!=null){
inputStream.close();
}
}
}
service实现类业务方法:
@Override
public UploadAssetDataVO uploadAssetData(InputStream inputStream,Integer processType, boolean isExcel2003) throws Exception {
UploadAssetDataVO uploadAssetDataVO=new UploadAssetDataVO();
ExcelReader excelReader = null;
List<DataSubjectDomainModel> subjectDomainList=null;
List<BizObjectModel> bizObjectModelList=null;
List<LogicEntityModel> logicEntityModelList=null;
List<AttributeModel> attributeModelList=null;
List<TechMetadataModel> techMetadataModelList=null;
LocalDateTime now=LocalDateTime.now();
Long userId= ShiroUtils.getUserId();
Integer uploadId=null;
Integer uploadFlag=3;
try {
if(isExcel2003){
excelReader = EasyExcel.read(inputStream)
.excelType(ExcelTypeEnum.XLS).build();
}else{
excelReader = EasyExcel.read(inputStream)
.excelType(ExcelTypeEnum.XLSX).build();
}
DataExcelUpload dataExcelUpload=new DataExcelUpload();
dataExcelUpload.setCreateTime(now);
dataExcelUpload.setUploadTime(now);
dataExcelUpload.setCreatePer(userId);
dataExcelUpload.setUpdateUserId(userId);
dataExcelUpload.setUpdatePer(userId);
// 1:上传成功 2:上传失败 3:上传中
dataExcelUpload.setUploadFlag(uploadFlag);
excelUploadService.save(dataExcelUpload);
uploadId=dataExcelUpload.getId();
BizObjectExcelListener bizObjectExcelListener=new BizObjectExcelListener();
ReadSheet readSheet2 = EasyExcel.readSheet("sheet1").head(BizObjectModel.class).registerReadListener(bizObjectExcelListener).build();
excelReader.read(readSheet2);
bizObjectModelList= bizObjectExcelListener.getDataList();
LogicEntityExcelListener logicEntityExcelListener=new LogicEntityExcelListener();
ReadSheet readSheet3= EasyExcel.readSheet("sheet2").head(LogicEntityModel.class).registerReadListener(logicEntityExcelListener).build();
excelReader.read(readSheet3);
logicEntityModelList= logicEntityExcelListener.getDataList();
AttributeExcelListener attributeExcelListener=new AttributeExcelListener();
ReadSheet readSheet4= EasyExcel.readSheet("sheet3").head(AttributeModel.class).registerReadListener(attributeExcelListener).build();
excelReader.read(readSheet4);
attributeModelList= attributeExcelListener.getDataList();
TechMeatadataExcelListener techMeatadataExcelListener=new TechMeatadataExcelListener();
ReadSheet readSheet5= EasyExcel.readSheet("sheet4").head(TechMetadataModel.class).registerReadListener(techMeatadataExcelListener).build();
excelReader.read(readSheet5);
techMetadataModelList= techMeatadataExcelListener.getDataList();
if(AuditBizTypeEnum.ASSET_DATA.equals(processType)){
if(CollectionUtil.isEmpty(attributeModelList)){
throw new RuntimeException("导入数据不能为空");
}
if(attributeModelList.size()>1000){
throw new RuntimeException("数据超过1000条");
}
boolean attributeFlag=attributeInfoUploadService.saveDataAttributeModelList(uploadId,attributeModelList);
// 1:上传成功 2:上传失败 3:上传中
if( !attributeFlag){
//如果业务校验失败,则标识失败
uploadFlag=2;
}else{
uploadFlag=1;
}
}else if(AuditBizTypeEnum.ASSET_TECH.equals(processType)){
if(CollectionUtil.isEmpty(attributeModelList)){
throw new RuntimeException("导入数据不能为空");
}
if(techMetadataModelList.size()>1000){
throw new RuntimeException("数据超过1000条");
}
boolean techMetadataFlag= techMetadataUploadService.saveTechMetadataModelList(uploadId,techMetadataModelList);
// 1:上传成功 2:上传失败 3:上传中
if( !techMetadataFlag){
//如果业务校验失败,则标识失败
uploadFlag=2;
}else{
uploadFlag=1;
}
}else{
if(CollectionUtil.isEmpty(bizObjectModelList)&&CollectionUtil.isEmpty(logicEntityModelList)&&CollectionUtil.isEmpty(attributeModelList)&& CollectionUtil.isEmpty(techMetadataModelList )){
throw new RuntimeException("导入数据为空");
}
if(bizObjectModelList.size()>1000){
throw new RuntimeException("超过1000条");
}
if(logicEntityModelList.size()>1000){
throw new RuntimeException("超过1000条");
}
if(attributeModelList.size()>1000){
throw new RuntimeException("超过1000条");
}
/* if(techMetadataModelList.size()>1000){
throw new RuntimeException("超过1000条");
}
*/
boolean bizObjectFlag=bizObjectUploadService.saveBizObjectModelList(uploadId,bizObjectModelList);
boolean logicEntityFlag=logicEntityUploadService.saveLogicEntityModelList(uploadId,logicEntityModelList);
boolean attributeFlag=attributeInfoUploadService.saveDataAttributeModelList(uploadId,attributeModelList);
/* boolean techMetadataFlag= techMetadataUploadService.saveTechMetadataModelList(uploadId,techMetadataModelList);*/
// 1:上传成功 2:上传失败 3:上传中
if(!bizObjectFlag || !logicEntityFlag || !attributeFlag /*|| !techMetadataFlag*/){
//如果业务校验失败,则标识失败
uploadFlag=2;
}else{
uploadFlag=1;
}
}
}catch (Exception e){
e.printStackTrace();
uploadFlag=2;
throw new RuntimeException(e.getMessage());
} finally {
if (excelReader != null) {
// 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
excelReader.finish();
}
DataExcelUpload dataExcelUploadUpdate=new DataExcelUpload();
dataExcelUploadUpdate.setId(uploadId);
dataExcelUploadUpdate.setUploadFlag(uploadFlag);
excelUploadService.updateById(dataExcelUploadUpdate);
uploadAssetDataVO.setUploadFlag(uploadFlag);
uploadAssetDataVO.setUploadId(uploadId);
}
return uploadAssetDataVO;
}
其中一个ExcelListenter
package com.cwp.service.listener;
import com.cwp.vo.BizObjectModel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
public class BizObjectExcelListener extends AnalysisEventListener<BizObjectModel> {
List<BizObjectModel> dataList=new ArrayList<>();
@Override
public void invoke(BizObjectModel data, AnalysisContext analysisContext) {
/* System.out.println("target解析到一条数据:"+JSON.toJSONString(data));*/
dataList.add(data);
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
/* System.out.println("解析到一条头数据:"+JSON.toJSONString(headMap));*/
Iterator<Map.Entry<Integer, String>> it = headMap.entrySet().iterator();
while(it.hasNext()){
Map.Entry<Integer, String> entry = it.next();
/* System.out.println("key:"+entry.getKey()+" value:"+entry.getValue()+"\r\n");*/
}
// 根据自己的情况去做表头的判断即可
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
throw new RRException(exception.getMessage());
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
public List<BizObjectModel> getDataList(){
return dataList;
}
}
其中一个ExcelModel
package com.cwp.vo;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class BizObjectModel {
@ExcelProperty(value = "编码",order = 1)
private String code;
@ExcelProperty(value = "名称",order = 5)
private String name;
}
pom.xml引入alibaba-easyexcel依赖片段
<!-- alibaba excel依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
<exclusions>
<exclusion>
<artifactId>cglib</artifactId>
<groupId>cglib</groupId>
</exclusion>
</exclusions>
</dependency>