EasyExcel多sheet的导入和导出
程序员文章站
2024-03-20 14:29:10
...
EasyExcel多sheet的导入和导出
EasyExcel对于导入导出的操作十分简洁,方便快捷。其中对于多单文件多sheet的操作比较不同,在此做一下记录
从多sheet中导入数据
public Result importDetail(@RequestParam(value = "file") MultipartFile serviceFile) throws IOException {
ExcelReader excelReader = null;
InputStream in = null;
try {
in = serviceFile.getInputStream();
ExcelListener excelListener = new ExcelListener();
excelReader = EasyExcel.read(in, PurchaseDetailVO.class, excelListener).build();
//获取采购单主信息
ReadSheet readSheet = EasyExcel.readSheet(0).build();
excelReader.read(readSheet);
List<PurchaseFeignParam> purchaseFeignParams = ConverterUtils.convertList(excelListener.getData(), PurchaseFeignParam.class);
if (CollectionUtils.isEmpty(purchaseFeignParams)) {
return Result.fail(30101, "导入数据为空", null);
}
//插入采购单数据
purchaseFeignClient.createPurchase(purchaseFeignParams.get(0));
//清空list数据
excelListener.getData().clear();
//获取明细表信息
ReadSheet readDetailSheet = EasyExcel.readSheet(1).build();
excelReader.read(readDetailSheet);
List<PurchaseDetailFeignParam> purchaseDetailFeignParam = ConverterUtils.convertList(excelListener.getData(), PurchaseDetailFeignParam.class);
//插入采购单明细数据
return purchaseDetailFeignClient.createPurchaseDetailBatch(purchaseDetailFeignParam);
} catch (IOException ex) {
logger.error("import excel to db fail", ex);
} finally {
in.close();
// 这里一定别忘记关闭,读的时候会创建临时文件,到时磁盘会崩
if (excelReader != null) {
excelReader.finish();
}
}
return Result.success();
}
导出为多sheet
public Result<Boolean> exportBookRecord(HttpServletResponse response) {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
try {
String fileName = URLEncoder.encode("template", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
//导出采购单模板 放在sheet0
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
WriteSheet mainSheet = EasyExcel.writerSheet(0, "采购单").head(PurchaseVO.class).build();
excelWriter.write(Lists.newArrayList(),mainSheet);
//导出采购单明细模板 放在sheet1
WriteSheet detailSheet = EasyExcel.writerSheet(1, "采购单明细").head(PurchaseDetailVO.class).build();
excelWriter.write(Lists.newArrayList(),detailSheet);
//关闭流
excelWriter.finish();
} catch (IOException e) {
logger.error("导出异常{}", e.getMessage());
}
return Result.success();
}