EasyExcel多sheet的导入和导出
程序员文章站
2024-03-20 14:24:46
...
EasyExcel多sheet的导入和导出
EasyExcel基础例子
EasyExcel对于导入导出的操作十分简洁,方便快捷。其中对于多单文件多sheet的操作比较不同,在此做一下记录
从多sheet中导入数据
public void 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, DetailVO.class, excelListener).build();
//获取sheet0对象
ReadSheet readSheet = EasyExcel.readSheet(0).build();
//读取数据
excelReader.read(readSheet);
DetailVO detail = excelListener.getData();
//清空list数据
excelListener.getData().clear();
//获取sheet1对象
ReadSheet readDetailSheet = EasyExcel.readSheet(1).build();
//读取数据
excelReader.read(readDetailSheet);
List<PurchaseDetailFeignParam> purchaseDetailFeignParam = ConverterUtils.convertList(excelListener.getData(), PurchaseDetailFeignParam.class);
} catch (IOException ex) {
logger.error("import excel to db fail", ex);
} finally {
in.close();
// 这里一定别忘记关闭,读的时候会创建临时文件,到时磁盘会崩
if (excelReader != null) {
excelReader.finish();
}
}
}
导出为多sheet
public void export(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");
//新建ExcelWriter
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
//获取sheet0对象
WriteSheet mainSheet = EasyExcel.writerSheet(0, "采购单").head(PurchaseVO.class).build();
//向sheet0写入数据 传入空list这样只导出表头
excelWriter.write(Lists.newArrayList(),mainSheet);
//获取sheet1对象
WriteSheet detailSheet = EasyExcel.writerSheet(1, "采购单明细").head(PurchaseDetailVO.class).build();
//向sheet1写入数据 传入空list这样只导出表头
excelWriter.write(Lists.newArrayList(),detailSheet);
//关闭流
excelWriter.finish();
} catch (IOException e) {
logger.error("导出异常{}", e.getMessage());
}
}
easyexcel对于多sheet的操作基于先获取需要操作的sheet对象=》ReadSheet和WriteSheet对象。在对制定的sheet进行操作
上一篇: 数据结构之线性表的基本操作