[alibaba/easyexcel]能不能支持fill填充时同时指定模板sheet和写入sheet

1

是的也需要这样的功能现在是根据模板中固定的sheet填充的我也需要这样的功能 @ableYang123 这问题解决了么？

tofdragon

9

在写入模板之前，调用POI的API复制Sheet,然后返回复制后的流，在调用EasyExcel写入模板，已测试可行

tofdragon

5

谢谢楼上，我研究出来了

     @Test
     public void testFillSheets() throws IOException {
        String filePath = createOutputFilePath("fills_sheets");  // 输出结果的文件路径
        String templateFilePath = TestFileUtil.getTemplatePath() + "a.xlsx"; // 模板文件路径

        // create template work book
        XSSFWorkbook templateWorkbook = new XSSFWorkbook(new FileInputStream(templateFilePath));

        // copy sheet
        copyFirstSheet(templateWorkbook, 2);

        // update sheet name
        String sheetNamePrefix = "Sheet-";
        for (int i = 0; i < templateWorkbook.getNumberOfSheets(); i++) {
            String sheetName = sheetNamePrefix + (i + 1);
            templateWorkbook.setSheetName(i, sheetName);
        }

        // outStream to inStream
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        templateWorkbook.write(outStream);
        ByteArrayInputStream templateInputStream = new ByteArrayInputStream(outStream.toByteArray());

        // fill sheets
        ExcelWriter excelWriter = EasyExcel.write(filePath).withTemplate(templateInputStream).build();
        for (int i = 0; i < templateWorkbook.getNumberOfSheets(); i++) {
            fillSheet(excelWriter, i);
        }
        excelWriter.finish();
    }

    private void copyFirstSheet(XSSFWorkbook workbook, int times) {
        if (times <= 0)  throw new IllegalArgumentException("times error");
        for (int i = 0; i < times; i++) {
            workbook.cloneSheet(0);
        }
    }

    private void fillSheet(ExcelWriter excelWriter, int sheetNo) {
        Map<String, Object> data = new HashMap<>();
        data.put("id", 1);
        data.put("name", "张三");
        data.put("result", 5.2);
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo).build();
        excelWriter.fill(data, writeSheet);
    }

vincent0214

2

忘记关注了，贴下代码和楼上的差不多 public ExcelFillUtil(OutputStream out, String templateFilePathStr){

    InputStream resourceAsStream = ExcelFillUtil.class.getResourceAsStream(templateFilePathStr);
    if(null==resourceAsStream){
        throw new RuntimeException(String.format("templateFilePathStr[%s] is not exist",templateFilePathStr));
    }
    excelWriter = EasyExcel.write(out).withTemplate(resourceAsStream).inMemory(true).build();
    workbook = (XSSFWorkbook) excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
}

public void fill(Object data, String sheetName) {
    if(workbook.getSheet(sheetName)==null){
        workbook.cloneSheet(0,sheetName);
    }
    excelWriter.fill(data,EasyExcel.writerSheet(sheetName).build());
}
public void fill(Object data, FillConfig fillConfig, String sheetName) {
    if(workbook.getSheet(sheetName)==null){
        workbook.cloneSheet(0,sheetName);
    }
    excelWriter.fill(data,fillConfig,EasyExcel.writerSheet(sheetName).build());
}

public void finish() {
    workbook.removeSheetAt(0);
    excelWriter.finish();
}

ableYang123

1

我想问下。我看你们都是clonesheet。你们每个模板的sheet都长的一样？如果是不一样的sheet呢。最终转换成一个多sheet的excel出来

guoxunbo

3

一样的写法啊，可以使用多个模板，使用哪个模板就克隆哪个模板，所有操作完毕后删除所有模板

ableYang123

2

一样的写法啊，可以使用多个模板，使用哪个模板就克隆哪个模板，所有操作完毕后删除所有模板

我想问下。我看你们都是clonesheet。你们每个模板的sheet都长的一样？如果是不一样的sheet呢。最终转换成一个多sheet的excel出来

ableYang123

2

@ableYang123 请教下哈。我现在是多个excel的，每个excel都有多个sheet。最终想把这多个excel里的所有sheet都转移到一个excel上。有参考的代码么

guoxunbo

8

单excel 多sheet 写法

    @SneakyThrows
    public static InputStream genExcelExportInputStream(String templateName, List<ExcelExportData> exportDatas) {
        InputStream templateInputStream = new ClassPathResource(getTemplatePath(templateName)).getInputStream();
        try (OutputStream outputStreamTemp = new ByteArrayOutputStream()) {

            ExcelWriter excelWriter = EasyExcel.write(outputStreamTemp).withTemplate(templateInputStream).build();
            //默认使用Sheet1

            for (ExcelExportData exportData : exportDatas) {
                String sheetName = exportData.getSheetName();
                Map<String, Object> objectMaps = exportData.getObjectMaps();
                Map<String, Pair<WriteDirectionEnum, List<Map<String, Object>>>> listDatas = exportData.getListDatas();

                WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
                if (objectMaps != null) {
                    excelWriter.fill(objectMaps, writeSheet);
                }
                if (CollectionUtil.isEmpty(listDatas)) {
                    continue;
                }
                listDatas.forEach((k, v) -> {
                    WriteDirectionEnum fillDirection = v.getKey();
                    List<Map<String, Object>> listData = v.getValue();
                    FillConfig fillConfig = FillConfig.builder().direction(fillDirection).build();
                    excelWriter.fill(new FillWrapper(k, listData), fillConfig, writeSheet);
                });
            }

            excelWriter.finish();
            outputStreamTemp.flush();
            outputStreamTemp.close();
            templateInputStream.close();
            return FileUtils.outputStream2InputStream(outputStreamTemp);
        }
    }

每个sheet页面一个 ExcelExportData


@Data
@AllArgsConstructor
public class ExcelExportData {
/**
 * sheetName
 */
private String sheetName;

/**
 * 主体数据
 */
private Map<String, Object> objectMaps;

/**
 * 可迭代数据
 */
private Map<String, Pair<WriteDirectionEnum, List<Map<String, Object>>>> listDatas;

public ExcelExportData(Map<String, Object> objectMaps) {
    this("sheet1", objectMaps);
}

public ExcelExportData(Map<String, Object> objectMaps, List<Map<String, Object>> listData) {
    this("sheet1", objectMaps, Pair.of("detail", listData));
}

public ExcelExportData(String sheetName, Map<String, Object> objectMaps) {
    this.sheetName = sheetName;
    this.objectMaps = objectMaps;
    this.listDatas = null;
}

public ExcelExportData(String sheetName, Map<String, Object> objectMaps, Pair<String, List<Map<String, Object>>> listData) {
    this.sheetName = sheetName;
    this.objectMaps = objectMaps;
    this.listDatas = new HashMap<>();
    this.listDatas.put(listData.getKey(), Pair.of(WriteDirectionEnum.VERTICAL, listData.getValue()));
}

public ExcelExportData(String sheetName, Map<String, Object> objectMap, List<Map<String, Object>> listData) {
    this(sheetName, objectMap, Pair.of("detail", listData));
}

public ExcelExportData() {
}

}

ZAKLLL

7

@ZAKLLL

你的这种写法是已知有多少个sheet。我那种是未知的。业务需求大致是这样的：输出填充好的N个模板的excel文件，然后要有一个总文件。这个总文件呢。里面要含有这次输出的N个文件的总和，每个文件一个sheet。

guoxunbo

9

先使用POI读取Excel模板，克隆多个Sheet，并设置每个Sheet的名称，然后再将这些Sheet交给EasyExcel处理。

示例代码如下：

public class Main {

    @SneakyThrows(IOException.class)
    public static void main(String[] args) {
        List<SheetData> sheetDataList = new ArrayList<>();
        sheetDataList.add(...);
        sheetDataList.add(...);
        sheetDataList.add(...);
        sheetDataList.add(...);
        byte[] template = adjustTemplate(new FileInputStream("template.xlsx"), Arrays.asList("sheet1", "sheet2", "sheet3"));
        fillTemplate(new ByteArrayInputStream(template), sheetDataList, new FileOutputStream("result.xlsx"));
    }

    /** 为 Excel 模版添加多个 Sheet */
    @SneakyThrows(IOException.class)
    public static byte[] adjustTemplate(InputStream template, List<String> newSheetNames) {
        // 读取Excel模板
        Workbook wb = WorkbookFactory.create(template);
        newSheetNames.forEach(newSheetName -> {
            // 克隆第一个Sheet
            Sheet newSheet = wb.cloneSheet(0);
            // 设置新Sheet的名称
            wb.setSheetName(wb.getSheetIndex(newSheet), newSheetName);
        });
        // 返回克隆后的Excel模版
        ByteArrayOutputStream out = new ByteArrayOutputStream(10 * 1024);
        wb.write(out);
        return out.toByteArray();
    }

    /** 填充 Excel 模版并保存到本地, sheetDataList 里的每个元素对应一个 sheet  */
    public static void fillTemplate(InputStream template, List<SheetData> sheetDataList, OutputStream out) {
        try (ExcelWriter excelWriter = EasyExcel.write(out).withTemplate(template).build()) {
            int sheetNo = 0;
            for (SheetData sheetData : sheetDataList) {
                WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo++, sheetData.getAssistTypeName()).build();
                excelWriter.fill(sheetData, writeSheet);
                excelWriter.fill(sheetData.getRowList(), writeSheet);
            }
        }
    }

}

oneice2020

[alibaba/easyexcel]能不能支持fill填充时同时指定模板sheet和写入sheet

回答