投稿
  1. 首页
  2. 随笔

使用easyexcel导入excel

挖矿卡 • 2022-12-17 • 随笔 • 阅读 84

使用easyexcel导入excel,第1张

使用easyexcel导入excel

第一步：先导包。网上很多版本，我用的1.1版本


            org.apache.poi
            poi
            ${poi.version}
        
        
            org.apache.poi
            poi-ooxml
            ${poi.version}
        

        
            com.alibaba
            easyexcel
            1.1.2-beta5
        

        
            org.projectlombok
            lombok
            1.18.2

第二步：工具类，里面包含了两个版本的工具类，1.1版本和3.02版本，这个可以不用看，下面的方法，不用它

package com.tufang.erp.util;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.baseRowModel;
import com.alibaba.excel.metadata.Sheet;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.io.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;



public class EasyExcelUtil{

    private static final Logger LOGGER = LoggerFactory.getLogger(EasyExcelUtil.class);
//
//    public static  List read(String filePath, final Class clazz) {
//        File f = new File(filePath);
//        try (FileInputStream fis = new FileInputStream(f)) {
//            return read(fis, clazz);
//        } catch (FileNotFoundException e) {
//            LOGGER.error("文件{}不存在", filePath, e);
//        } catch (IOException e) {
//            LOGGER.error("文件读取出错", e);
//        }
//
//        return null;
//    }
//
//    public static  List read(InputStream inputStream, final Class clazz) {
//        if (inputStream == null) {
//            throw new BusinessException("解析出错了，文件流是null");
//        }
//
//        // 有个很重要的点 DataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
//        DataListener listener = new DataListener<>();
//
//        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
//        EasyExcel.read(inputStream, clazz, listener).sheet().doRead();
//        return listener.getRows();
//    }
//
//    public static void write(String outFile, List list) {
//        Class clazz = list.get(0).getClass();
//        // 新版本会自动关闭流，不需要自己 *** 作
//        EasyExcel.write(outFile, clazz).sheet().doWrite(list);
//    }
//
//    public static void write(String outFile, List list, String sheetName) {
//        Class clazz = list.get(0).getClass();
//        // 新版本会自动关闭流，不需要自己 *** 作
//        EasyExcel.write(outFile, clazz).sheet(sheetName).doWrite(list);
//    }
//
//    public static void write(OutputStream outputStream, List list, String sheetName) {
//        Class clazz = list.get(0).getClass();
//        // 新版本会自动关闭流，不需要自己 *** 作
//        // sheetName为sheet的名字，默认写第一个sheet
//        EasyExcel.write(outputStream, clazz).sheet(sheetName).doWrite(list);
//    }
//
//    
//    public static void download(HttpServletResponse response, List list, String sheetName) throws IOException {
//        Class clazz = list.get(0).getClass();
//
//        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
//        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//        response.setCharacterEncoding("utf-8");
//        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
//        String fileName = URLEncoder.encode(sheetName, "UTF-8").replaceAll("\+", "%20");
//        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
//        EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(list);
//    }





    private static Sheet initSheet;

    static {
        initSheet = new Sheet(1, 0);
        initSheet.setSheetName("sheet");
        //设置自适应宽度
        initSheet.setAutoWidth(Boolean.TRUE);
    }

    
    public static List readLessThan1000Row(String filePath){
        return readLessThan1000RowBySheet(filePath,null);
    }

    
    public static List readLessThan1000RowBySheet(String filePath, Sheet sheet){
        if(!StringUtils.hasText(filePath)){
            return null;
        }

        sheet = sheet != null ? sheet : initSheet;

        InputStream fileStream = null;
        try {
            fileStream = new FileInputStream(filePath);
            return EasyExcelFactory.read(fileStream, sheet);
        } catch (FileNotFoundException e) {
            LOGGER.info("找不到文件或文件路径错误, 文件：{}", filePath);
        }finally {
            try {
                if(fileStream != null){
                    fileStream.close();
                }
            } catch (IOException e) {
                LOGGER.info("excel文件读取失败, 失败原因：{}", e);
            }
        }
        return null;
    }

    
    public static List readMoreThan1000Row(String filePath){
        return readMoreThan1000RowBySheet(filePath,null);
    }

    
    public static List readMoreThan1000RowBySheet(String filePath, Sheet sheet){
        if(!StringUtils.hasText(filePath)){
            return null;
        }

        sheet = sheet != null ? sheet : initSheet;

        InputStream fileStream = null;
        try {
            fileStream = new FileInputStream(filePath);
            ExcelListener excelListener = new ExcelListener();
            EasyExcelFactory.readBySax(fileStream, sheet, excelListener);
            return excelListener.getDatas();
        } catch (FileNotFoundException e) {
            LOGGER.error("找不到文件或文件路径错误, 文件：{}", filePath);
        }finally {
            try {
                if(fileStream != null){
                    fileStream.close();
                }
            } catch (IOException e) {
                LOGGER.error("excel文件读取失败, 失败原因：{}", e);
            }
        }
        return null;
    }

    
    public static void writeBySimple(String filePath, List> data, List head){
        writeSimpleBySheet(filePath,data,head,null);
    }

    
    public static void writeSimpleBySheet(String filePath, List> data, List head, Sheet sheet){
        sheet = (sheet != null) ? sheet : initSheet;

        if(head != null){
            List> list = new ArrayList<>();
            head.forEach(h -> list.add(Collections.singletonList(h)));
            sheet.setHead(list);
        }

        OutputStream outputStream = null;
        ExcelWriter writer = null;
        try {
            outputStream = new FileOutputStream(filePath);
            writer = EasyExcelFactory.getWriter(outputStream);
            writer.write1(data,sheet);
        } catch (FileNotFoundException e) {
            LOGGER.error("找不到文件或文件路径错误, 文件：{}", filePath);
        }finally {
            try {
                if(writer != null){
                    writer.finish();
                }

                if(outputStream != null){
                    outputStream.close();
                }

            } catch (IOException e) {
                LOGGER.error("excel文件导出失败, 失败原因：{}", e);
            }
        }

    }

    
    public static void writeWithTemplate(String filePath, List data){
        writeWithTemplateAndSheet(filePath,data,null);
    }

    
    public static void writeWithTemplateAndSheet(String filePath, List data, Sheet sheet){
        if(CollectionUtils.isEmpty(data)){
            return;
        }

        sheet = (sheet != null) ? sheet : initSheet;
        sheet.setClazz(data.get(0).getClass());

        OutputStream outputStream = null;
        ExcelWriter writer = null;
        try {
            outputStream = new FileOutputStream(filePath);
            writer = EasyExcelFactory.getWriter(outputStream);
            writer.write(data,sheet);
        } catch (FileNotFoundException e) {
            LOGGER.error("找不到文件或文件路径错误, 文件：{}", filePath);
        }finally {
            try {
                if(writer != null){
                    writer.finish();
                }

                if(outputStream != null){
                    outputStream.close();
                }
            } catch (IOException e) {
                LOGGER.error("excel文件导出失败, 失败原因：{}", e);
            }
        }

    }

    
    public static void writeWithMultipleSheel(String filePath,List multipleSheelPropetys){
        if(CollectionUtils.isEmpty(multipleSheelPropetys)){
            return;
        }

        OutputStream outputStream = null;
        ExcelWriter writer = null;
        try {
            outputStream = new FileOutputStream(filePath);
            writer = EasyExcelFactory.getWriter(outputStream);
            for (MultipleSheelPropety multipleSheelPropety : multipleSheelPropetys) {
                Sheet sheet = multipleSheelPropety.getSheet() != null ? multipleSheelPropety.getSheet() : initSheet;
                if(!CollectionUtils.isEmpty(multipleSheelPropety.getData())){
                    sheet.setClazz(multipleSheelPropety.getData().get(0).getClass());
                }
                writer.write(multipleSheelPropety.getData(), sheet);
            }

        } catch (FileNotFoundException e) {
            LOGGER.error("找不到文件或文件路径错误, 文件：{}", filePath);
        }finally {
            try {
                if(writer != null){
                    writer.finish();
                }

                if(outputStream != null){
                    outputStream.close();
                }
            } catch (IOException e) {
                LOGGER.error("excel文件导出失败, 失败原因：{}", e);
            }
        }

    }


    

    @Data
    public static class MultipleSheelPropety{

        private List data;

        private Sheet sheet;
    }

    
    @Getter
    @Setter
    public static class ExcelListener extends AnalysisEventListener {

        private List datas = new ArrayList<>();

        
        @Override
        public void invoke(Object object, AnalysisContext context) {
            //当前行
            // context.getCurrentRowNum()
            if (object != null) {
                datas.add(object);
            }
        }


        
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            //解析结束销毁不用的资源
        }

    }
}
 

第三步，监听类，必须写。这是监听和获取数据的
 

package com.tufang.erp.manage.controller.excel;


import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.tufang.erp.dao.object.ManagePurchaseSupplierPriceDO;
import com.tufang.erp.dao.object.excel.PriceExcelInputRequest;
import com.tufang.erp.util.BeanUtil;
import org.springframework.transaction.annotation.Transactional;

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


public class PurchasePriceExcelListener extends AnalysisEventListener {

    
    private static final int BATCH_COUNT = 5;
    List list = new ArrayList();

    private static int count = 1;
    @Override
    @Transactional
    public void invoke(PriceExcelInputRequest inputRequest, AnalysisContext context) {
        //新建采购价目表对象
        ManagePurchaseSupplierPriceDO priceDO = new ManagePurchaseSupplierPriceDO();
        priceDO.setPurchasePrice(inputRequest.getPurchasePrice());

        BeanUtil.copy(inputRequest,priceDO);
        System.out.println("解析到一条数据:{ "+ inputRequest.toString() +" }");
        list.add(inputRequest);
        count ++;
//        if (list.size() >= BATCH_COUNT){
//            saveData( count );
//            list.clear();
//        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
//        priceMapper.insertPurchasePriceList(priceDOList);
        saveData( count );
//        System.out.println("所有数据解析完成！");
//        System.out.println(" count ：" + count);
    }

    
    private void saveData(int count) {
//        System.out.println("{ "+ count +" }条数据，开始存储数据库！" + list.size());
//        System.out.println("存储数据库成功！");
    }

//通过这个方法获取数据
    public List getDatas() {
//        System.out.println("{ "+ count +" hahahahhahahhah }条数据，开始存储数据库！" + list.size());
        return list;
    }
}


 

PriceExcelInputRequest 是自定义的类，根据业务需求新建。
 

第四步，控制层（controller层）
 

 @PostMapping("addPurchasePriceExcelInput")
    @ApiOperation(value = "excel数据导入", httpMethod = "POST")
    @PublicInterface
    public ResultResponse addPurchasePriceExcelInput(@RequestBody MultipartFile file) throws IOException {
        //获取登录信息
        ManageUserDO user = tokenService.getLoginUser(ServletUtils.getRequest()).getUser();
        //上传过来excel文件
        Sheet sheet = new Sheet(1,1, PriceExcelInputRequest.class);

            //监听器
            PurchasePriceExcelListener listener = new PurchasePriceExcelListener();
            //读取数据
            EasyExcelFactory.readBySax(file.getInputStream(),sheet,listener);
            //数据
            List datas = listener.getDatas();
            //数据校验
            List priceDOS = checkExcelDatas(datas, user.getId());
            //新增数据库
            priceService.insertPurchasePriceList(priceDOS);

        return ResultResponse.successResponse();
    }

 

第五步，获取到数据，必须进行数据校验（校验很繁琐，但必须写，没办法）， *** 作自己的数据库，这个就不用教了吧。
 

下面是我的数据校验，你们要根据自己的写。
 

    public List checkExcelDatas(List datas, Long id){
        //返回集合
        List priceDOList = new ArrayList();
        //循环
        for (PriceExcelInputRequest inputRequest :datas){
            ManagePurchaseSupplierPriceDO priceDO = new ManagePurchaseSupplierPriceDO();
            BigDecimal purchasePrice = inputRequest.getPurchasePrice();
            Long supplierId = inputRequest.getSupplierId();
            String supplierName = inputRequest.getSupplierName();
            String materialName = inputRequest.getMaterialName();
            Date startTime = inputRequest.getStartTime();
            Date endTime = inputRequest.getEndTime();
            Long materialId = inputRequest.getMaterialId();
            //校验数据类型

            //必填项校验
            if (supplierId ==null || materialId==null || startTime==null || endTime==null || purchasePrice==null ){
                throw new BusinessException("供应商编码,产品编码,采购价格,生效日期,失效日期,为必填");
            }
            //第一步，校验供应商id 本库有无
          if (supplierId !=null){
            ManageBasicSupplierDO manageBasicSupplierDO = supplierMapper.selectByPrimaryKey(supplierId);
            if (Objects.isNull(manageBasicSupplierDO)){
                throw new BusinessException("请填写正确的供应商编码 "+supplierName+"的编码 "+supplierId+"错误");
            }else if (manageBasicSupplierDO.getStatus()!=1){
                throw new BusinessException("供应商名称为 "+supplierName+"未审核通过 ");
            }
          }else {
            throw new BusinessException("供应商编码,必填");
          }
         //第二步，校验商品id正确否
         if (materialId !=null){
            ManageBasicMaterialDO manageBasicMaterialDO = materialMapper.selectByPrimaryKey(materialId);
            if (Objects.isNull(manageBasicMaterialDO)){
                throw new BusinessException("产品编码为"+materialId+"填写错误");
            }else if (!materialName.equals(manageBasicMaterialDO.getName())){
                    throw new BusinessException("产品编码和产品名称必须对应");
                }else if (!"2".equals(manageBasicMaterialDO.getStatus())){
                 throw new BusinessException("产品id 为"+materialId+"商品库审核未通过");
               }
        }else {
            throw new BusinessException("产品编码,必填");
        }
        //生效时间和失效时间校验
        Date nowDate = Date.from(LocalDateTime.now().atZone(ZoneId.systemDefault()).toInstant());
        if (startTime ==null || endTime==null){
            throw new BusinessException("生效时间和失效时间必填");
        }
        if (startTime.getTime() > endTime.getTime()){
            throw new BusinessException("失效时间必须小于生效时间");
        }else if (endTime.getTime() managePurchasePriceMaterialDOS = priceExtMapper.selectMaterialListByMaterialId(supplierId, materialId);
        if (managePurchasePriceMaterialDOS !=null && managePurchasePriceMaterialDOS.size()>0){
            throw new BusinessException("供应商名称   "+supplierName+"   对应的产品   "+materialName+"   已经存在");
        }
        priceDO.setPurchasePrice(inputRequest.getPurchasePrice());
        BeanUtil.copy(inputRequest,priceDO);
        //获取登录信息
//        list.add(inputRequest);
        priceDO.setCreateUserId(id);
        priceDO.setUpdateUserId(id);
        priceDO.setCreateTime(Date.from(LocalDateTime.now().atZone( ZoneId.systemDefault()).toInstant()));
        priceDOList.add(priceDO);
        }
        return priceDOList;
    }



					
										


					

						
欢迎分享，转载请注明来源：内存溢出
原文地址: http://outofmemory.cn/zaji/5712375.html

					

				
								

										
										
										
										
										
									

								

					
						
							
						 赞
						(0)
					
					

						
							 打赏
						
							
								微信扫一扫 微信扫一扫
							
							
								支付宝扫一扫 支付宝扫一扫
							
						
					

				

				

					

						

							
								挖矿卡
								挖矿卡
								一级用户组
							
						

						

							

								
									
										
									
									0
								
								
									
										
									
									0
								
							

							
							

								
									
										
									
								
							

						

					

				

			
			
            

            	            

            
			

								

					
					

         				
							
								
							 上一篇
						
						2022-12-18
					

				

												

					
					

         				
							下一篇 
           						
							
						
						2022-12-17
					

				

							


			
			

				

					

						发表评论
					

										

						
						

							

								登录后才能评论
							

							
						

					

									

								
 评论列表（0条）

				
    
									

				
											

		

	

	





	


	

		

			

			

				
				

					

						Copyright © 2022 内存溢出 版权所有
						
							湘ICP备2022025235号						
						Powered by
						
							outofmemory.cn
						
					
					

				

			

			

				

					
					
						
						
					
					
						
					
				

			

		

	




























    
    

    

        
            保存