EasyExcel多sheet的导入和导出例子

我就是我 2023-02-10 07:48 11阅读 0赞

### EasyExcel多sheet的导入和导出 ###

### [EasyExcel基础例子][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进行操作

[EasyExcel]: https://blog.csdn.net/sinat_29774479/article/details/104699411