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(); }
就不写测试情况了,到这里所有代码就结束了,业务流程代码我已省略,请根据需求编写。
下次直接拿下!!!