easyexcel
easyexcel copied to clipboard
alibaba
如何实现填充多个sheet，使用同一份sheet模板
找到方案了吗。我的需求和你一样，但是我的sheet数量是不确定的
InputStream inputStream = FileUtils.getExportTemplate("部门车资.xlsx");
ExcelWriterBuilder writerBuilder = EasyExcel.write(response.getOutputStream()).withTemplate(inputStream);
ExcelWriter excelWriter = writerBuilder.build();
for (String dept : deptSendCarMap.keySet()) {
List<CarfareExcel> deptCarfareList = deptSendCarMap.get(dept);
// 省略其他数据构建
WriteSheet writeSheet = EasyExcel.writerSheet().build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
excelWriter.fill(Collections.singletonMap("dept", dept), fillConfig, writeSheet);
excelWriter.fill(deptCarfareList, fillConfig, writeSheet);
Map<String, Object> map = new HashMap<>();
map.put("totaltoll", totaltoll);
map.put("cost", cost);
map.put("invoice", invoice);
excelWriter.fill(map, writeSheet);
}
excelWriter.finish();
这样写只会重复在模版的sheet里写。得到的数据只有最后一个deptCarfareList的 如果这样写：WriteSheet writeSheet = EasyExcel.writerSheet(dept).build(); 对应的sheet是创建了，但是里面没有任何数据，因为新创建的sheet里并没有模版
成功了，完整代码放这里，供后人参考
InputStream inputStream = FileUtils.getExportTemplate("部门车资.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
List<String> deptNames = new ArrayList<>(deptSendCarMap.keySet());
for (int i = 0; i < deptNames.size(); i++) {
if (i == 0) {
workbook.setSheetName(0, deptNames.get(0));
} else {
workbook.cloneSheet(0, deptNames.get(i));
}
}
ByteArrayOutputStream bos = new ByteArrayOutputStream();
workbook.write(bos);
ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray());
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(bis).build();
for (int i = 0; i < deptNames.size(); i++) {
String dept = deptNames.get(i);
List<CarfareExcel> deptCarfareList = deptSendCarMap.get(dept);
// 省略其他数据构建
WriteSheet writeSheet = EasyExcel.writerSheet(i).build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
excelWriter.fill(Collections.singletonMap("dept", dept), fillConfig, writeSheet);
excelWriter.fill(deptCarfareList, fillConfig, writeSheet);
Map<String, Object> map = new HashMap<>();
map.put("totaltoll", totaltoll);
map.put("cost", cost);
map.put("invoice", invoice);
excelWriter.fill(map, writeSheet);
}
excelWriter.finish();
补充一个防止重名
`XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(excelTempletePath));
Set<String> usedSheetNames = new HashSet<>();
for (int i = 0; i < sheetDataList.size(); i++)
{
String baseName = sheetDataList.get(i).getProjectName();
String sheetName = baseName;
int count = 1;
// 避免重名
while (usedSheetNames.contains(sheetName))
{
sheetName = baseName + "_" + count;
count++;
}
usedSheetNames.add(sheetName);
if (i == 0)
{
workbook.setSheetName(0, sheetName);
} else
{
// 克隆模板
workbook.cloneSheet(0);
workbook.setSheetName(workbook.getNumberOfSheets() - 1, sheetName);
}
}
ByteArrayOutputStream bos = new ByteArrayOutputStream();
workbook.write(bos);
ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray());
ExcelWriter writer = EasyExcel.write(response.getOutputStream()).withTemplate(bis).build();
for (int s = 0; s < sheetDataList.size(); s++)
{
CompareSheetData data = sheetDataList.get(s);
List<IndexCompareRow> rows = data.getRows();
// 自定义处理器
RedFontCellWriteHandler redFontHandler = new RedFontCellWriteHandler(rows);
// 构建 Sheet
WriteSheet writeSheet = EasyExcel.writerSheet(s).registerWriteHandler(redFontHandler).build();
// 填充变量（ {projectName}, {indexLibraryName}）
Map<String, Object> variables = new HashMap<>();
variables.put("projectName", data.getProjectName());
variables.put("indexLibraryName", data.getIndexLibraryName());
writer.fill(variables, writeSheet);
// 填充数据
for (int i = 0; i < rows.size(); i++)
{
rows.get(i).setIndex(i + 1);
}
FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
writer.fill(rows, fillConfig, writeSheet);
}
writer.finish();
}`
补充一个防止重名
`XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(excelTempletePath));
Set<String> usedSheetNames = new HashSet<>(); for (int i = 0; i < sheetDataList.size(); i++) { String baseName = sheetDataList.get(i).getProjectName(); String sheetName = baseName; int count = 1; // 避免重名 while (usedSheetNames.contains(sheetName)) { sheetName = baseName + "_" + count; count++; } usedSheetNames.add(sheetName); if (i == 0) { workbook.setSheetName(0, sheetName); } else { // 克隆模板 workbook.cloneSheet(0); workbook.setSheetName(workbook.getNumberOfSheets() - 1, sheetName); } } ByteArrayOutputStream bos = new ByteArrayOutputStream(); workbook.write(bos); ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray()); ExcelWriter writer = EasyExcel.write(response.getOutputStream()).withTemplate(bis).build(); for (int s = 0; s < sheetDataList.size(); s++) { CompareSheetData data = sheetDataList.get(s); List<IndexCompareRow> rows = data.getRows(); // 自定义处理器 RedFontCellWriteHandler redFontHandler = new RedFontCellWriteHandler(rows); // 构建 Sheet WriteSheet writeSheet = EasyExcel.writerSheet(s).registerWriteHandler(redFontHandler).build(); // 填充变量（ {projectName}, {indexLibraryName}） Map<String, Object> variables = new HashMap<>(); variables.put("projectName", data.getProjectName()); variables.put("indexLibraryName", data.getIndexLibraryName()); writer.fill(variables, writeSheet); // 填充数据 for (int i = 0; i < rows.size(); i++) { rows.get(i).setIndex(i + 1); } FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build(); writer.fill(rows, fillConfig, writeSheet); } writer.finish(); }`
workbook 不需要 close 吗？
