> 文档中心 > SpringBoot 整合阿里EasyExcel实现表格批量导入

SpringBoot 整合阿里EasyExcel实现表格批量导入

记录一次项目中批量导入excel 数据的整合,好记性不如烂笔头。

如对你有帮助,请一键三连 Thank you

1.导入依赖:

      com.alibaba     easyexcel     3.0.5

2.实体类对象 (要导入的表对应实体类)

@Data@Builder@AllArgsConstructor@NoArgsConstructorpublic class ShopOrder extends Model {    //订单编号    @TableId(type = IdType.ASSIGN_ID)    @ExcelProperty(value = "编号",index = 0)    private String orderId;    //应付金额    @ExcelProperty(value = "金额",index = 1)    private BigDecimal shouldPay;    //实付金额    @ExcelProperty(value = "金额",index = 2)    private BigDecimal practicalPay;    //消费者编号}

3.Service服务层 的接口

 Boolean updateOrderInfo(ShopOrder shopOrder);//我这里的实现是根据从excel 找到的信息去修改更新

4.接口的实现类

 @Transactional    @Override    public Boolean updateOrderInfo(ShopOrder shopOrder){ return this.updateById(shopOrder);    }

5.还需要新增一个监听类

/** *  Excel导入事件监听类~~ *  auth:qxl *  time: */@Slf4jpublic class EasyExcelLister extends AnalysisEventListener {    //放置元素的list    List list=new ArrayList();    //定义的最大值    private static final int BATCH_COUNT = 1000;    @Resource   private ShopOrderService shopOrderService;    //空参构造    public EasyExcelLister(){    }    //有参构造    public EasyExcelLister(ShopOrderService shopOrderService){ super(); this.shopOrderService=shopOrderService;    }    // 第二个执行:一行一行去读取excel内容    @Override    public void invoke(ShopOrder shopOrder, AnalysisContext analysisContext) { list.add(shopOrder); if (list.size()>=BATCH_COUNT){     list.clear(); }else {     saveData(list); }    }    // 第一个执行:读取excel表头信息    @Override    public void invokeHeadMap(Map headMap, AnalysisContext context) { System.out.println("表头信息:"+headMap);    }    //保存数据    private void saveData(List list) { for (int i=0 ;i<=list.size()-1 ; i++){//获取订单idString orderId =  list.get(i).getOrderId();// 根据id 查询对象ShopOrder shop=  shopOrderService.getById(orderId);  /**  *不相关业务逻辑代码已隐藏  */  //传入对象修改订单shopOrderService.updateOrderInfo(shop);   }     }    // 第三个执行:读取完成后执行    @Override    public void doAfterAllAnalysed(AnalysisContext analysisContext) { log.info("所有数据已导入完成");    }

6.控制层的代码:

public Result saveBatch(@Param("file") MultipartFile file) throws Exception { ExcelReader excelReader = null; InputStream in = null; try {     in = file.getInputStream();     excelReader = EasyExcel.read(in,ShopOrder.class, new EasyExcelLister(shopOrderService)).build();     ReadSheet readSheet = EasyExcel.readSheet(0).build();     excelReader.read(readSheet);     excelReader.excelExecutor(); } catch (IOException ex) {     log.info("文件导入失败"); } finally {     close(in);     if (excelReader != null) {  excelReader.finish();     } } return Result.ok("");    }    private void close(Closeable closeable) { if (closeable != null) {     try {  closeable.close();     } catch (IOException e) { log.info("输入流关闭异常", e);     } }

最最重要的启动类代码

//上面就是启动类的main 方法/**     * 此处配置excel导入时创建临时目录的配置     * 我这里用的是本地电脑的路径,如需上传服务器, 可修改为服务器的路径     * */    @Bean    MultipartConfigElement multipartConfigElement() { MultipartConfigFactory factory = new MultipartConfigFactory(); //以下的文件夹路径必须是存在的  不然会报错 factory.setLocation("D:/data/uploadtmp"); return factory.createMultipartConfig();    }

就不写测试情况了,到这里所有代码就结束了,业务流程代码我已省略,请根据需求编写。

下次直接拿下!!!