拆分多Sheet =＞ 多个单Sheet的文件(二)

需求

将一个Excel中多个Sheet页转换为多个单Sheet的文件

思路

鉴于上一章节中的存在的缺点，本次使用Stream拷贝的形式完成。

Code

@Test
public void splitSheetByCopyStream() {
    ClassPathResource resource = new ClassPathResource("/template/template.xlsx");
    Assert.isTrue(resource.exists());
    try (InputStream inputStream = resource.getInputStream()) {
        byte[] bytes = this.copyStream(inputStream);
        Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bytes.clone()));
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            byte[] clone = bytes.clone();
            Workbook target = WorkbookFactory.create(new ByteArrayInputStream(clone));
            // 反向删除
            for (int j = numberOfSheets - 1; j >= 0; j--) {
                if (j != i) {
                    target.removeSheetAt(j);
                }
            }
            FileOutputStream fileOutputStream = new FileOutputStream(workbook.getSheetName(i) + ".xlsx");
            target.write(fileOutputStream);
            fileOutputStream.close();
        }
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

优缺点

• 优点

  • 方案简单，不受xlsx和xls文件格式限制

• 缺点

  • 占用内存大(要进行多次流拷贝)