java easyexcel读取多个sheet示例代码

以下是利用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>