easyexcel使用模板填充excel坑點總結

1.單層map設置值是{屬性}，那使用兩層map進行設置值，是不是可以使用{屬性.屬性}，以為取出map里字段只用{屬性}就可以設置值，那再加個.就可以從里邊map取出對應屬性，沒有兩層map寫法
填充得到的文件打開報錯?was empty (zero bytes long)

Map<String, Map<String, String>> map = new HashMap<>();Map<String, String> map1 = new HashMap<>();map1.put("name", "張三");map1.put("age", "18");HashMap<String, String> map2 = new HashMap<>();map2.put("name", "李四");map2.put("age", "19");map.put("obj1", map1);map.put("obj2", map2);ExcelWriter excelWriter = EasyExcel.write(EXCEL_OUPUT_FILE_PATH).withTemplate(TEMPLATE_FILE_PATH).build(); // 構建ExcelWriter對象 整個文件對象WriteSheet sheet1 = EasyExcel.writerSheet("Sheet1").build(); // 構建sheet對象excelWriter.fill(map, sheet1); //將操作的sheet和數據填充到ExcelWriter對象中

2.嘗試使用map包對象寫法，想著反正是.，那里面是對象就可以使用了吧，填充后文件還是報文件 was empty (zero bytes long)

public static final String EXCEL_OUPUT_FILE_PATH = "templateOutput/template1Output.xlsx";public static final String TEMPLATE_FILE_PATH = "excelTemplate/template1.xlsx";static class Person {public String name;public String age;}public static void main(String[] args) {Map<String, Person> map = new HashMap<>();Map<String, String> map1 = new HashMap<>();Person person = new Person();person.name = "張三";person.age = "18";map1.put("name", "張三");map1.put("age", "18");HashMap<String, String> map2 = new HashMap<>();Person person1 = new Person();person1.name = "李四";person1.age = "19";map2.put("name", "李四");map2.put("age", "19");map.put("obj1", person);map.put("obj2", person1);ExcelWriter excelWriter = EasyExcel.write(EXCEL_OUPUT_FILE_PATH).withTemplate(TEMPLATE_FILE_PATH).build(); // 構建ExcelWriter對象 整個文件對象WriteSheet sheet1 = EasyExcel.writerSheet("Sheet1").build(); // 構建sheet對象excelWriter.fill(map, sheet1); //將操作的sheet和數據填充到ExcelWriter對象中}

3.使用創建EasyWriter和WriteSheet對象，直接使用
EasyExcel.write(filePath).withTemplate(excelTemplate).sheet(0).doFill(excelDataMap);

EasyExcel.write(filePath).withTemplate(excelTemplate).sheet(1).doFill(excelDataMap);

對兩個sheet進行填充，只有最后一個sheet設置了值。
應該創建EasyWriter和WriterSheet對象進行設置值

正確寫法：

Map<String, String> excelDataMap = new HashMap();
ExcelWriter excelWriter = EasyExcel.write(filePath).withTemplate(asInputStream).build();
WriteSheet sheet1 = EasyExcel.writerSheet(0).build();
WriteSheet sheet2 = EasyExcel.writerSheet(0).build();
excelWriter.fill(sheet1, excelDataMap);
excelWriter.fill(sheet2, excelDataMap);
excelWriter.finish();

4.使用，沒有告訴excelwriter對象，結束導入，導致生成的excel文件打不開，報was empty (zero bytes long)。應該在寫入完成后調用

excelWriter.finish();

完整用法：

Map<String, String> excelDataMap = new HashMap();
ExcelWriter excelWriter = EasyExcel.write(filePath).withTemplate(asInputStream).build();
WriteSheet sheet1 = EasyExcel.writerSheet(0).build();
WriteSheet sheet2 = EasyExcel.writerSheet(0).build();
excelWriter.fill(sheet1, excelDataMap);
excelWriter.fill(sheet2, excelDataMap);
excelWriter.finish();

5.由于我需要將模版文件的某些sheet復制多個形成新的模版，但又不想多產生一個模版文件，我將輸出文件和文件和模版文件都設置成一個

ExcelWriter excelWriter = EasyExcel.write(TEMPLATE_FILE_PATH).withTemplate(TEMPLATE_FILE_PATH).build();

報錯Exception in thread "main" com.alibaba.excel.exception.ExcelGenerateException: Create workbook failure
?? ?at com.alibaba.excel.context.WriteContextImpl.<init>(WriteContextImpl.java:98)
?? ?at com.alibaba.excel.write.ExcelBuilderImpl.<init>(ExcelBuilderImpl.java:36)
?? ?at com.alibaba.excel.ExcelWriter.<init>(ExcelWriter.java:36)
?? ?at com.alibaba.excel.write.builder.ExcelWriterBuilder.build(ExcelWriterBuilder.java:114)
?? ?at org.example.demotask.util.EasyexcelUtils.main(EasyexcelUtils.java:60)
Caused by: org.apache.poi.EmptyFileException: The supplied file was empty (zero bytes long)
復制模版里的sheet形成新的模版賦值導出寫法：

Map<String, String> map = new HashMap();Map<String, Map<String, String>> lotMap = new HashMap();//獲取的模板文件位置String excelTemplate = "path";FileInputStream fileInputStream = new FileInputStream(excelTemplate);InputStream templateStream = fileInputStream;Workbook workbook = WorkbookFactory.create(templateStream);List<String> fatherSheet = Lists.newArrayList("Sheet1");ArrayList<String> lotNames = new ArrayList<>(lotMap.keySet());//復制sheetfor (String cloneSheet : fatherSheet) {for (String lotName : lotNames) {//根據要復制的sheet名拿到要復制的sheetSheet templateSheet = workbook.getSheet(cloneSheet);Sheet newSheet = workbook.cloneSheet(workbook.getSheetIndex(templateSheet));int newSheetIndex = workbook.getSheetIndex(newSheet);  // 獲取新克隆的 sheet 索引//設置復制的sheet名workbook.setSheetName(newSheetIndex, cloneSheet + "(" + lotName + ")");}}//新建內存輸出流，作為新的模版文件輸出ByteArrayOutputStream ops = new ByteArrayOutputStream();workbook.write(ops);ops.flush();byte[] byteArray = ops.toByteArray();ops.close();workbook.close();//使用新的文件的字節流作為模板文件輸入InputStream asInputStream = new ByteArrayInputStream(byteArray);// 原文件流后續已不使用，此處關閉templateStream.close();ops.close();ExcelWriter excelWriter = EasyExcel.write("D:\\newnewnewFormat111.xlsx").withTemplate(asInputStream).build();WriteSheet mainSheet = EasyExcel.writerSheet("Sheet1").build();excelWriter.fill(map, mainSheet);for (String lotName : lotNames) {WriteSheet sheet = EasyExcel.writerSheet("Sheet1(" + lotName + ")").build();excelWriter.fill(lotMap.get(lotName), sheet);}//重要 使用excelWriter寫入完一定要調用finish方法，不然文件打不開excelWriter.finish();

總結：
1.使用Map類型進行填充只有能普通類型
2.模板注意用{} 來表示你要用的變量如果本來就有"{","}" 特殊字符用"\{","\}"代替
3. {} 代表普通變量 {.} 代表是list的變量 {前綴.} 前綴可以區分不同的list

WriteSheet writeSheet = EasyExcel.writerSheet().build();List<String> list1 = new ArrayList<>();list1.add("張三");list1.add("李四");ArrayList<String> list2 = new ArrayList<>();list2.add("11");list2.add("22");//設置列表展開方向，默認垂直FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();// 如果有多個list 模板上必須有{前綴.} 這里的前綴就是 data1，然后多個list必須用 FillWrapper包裹excelWriter.fill(new FillWrapper("data1", list1), fillConfig, writeSheet);excelWriter.fill(new FillWrapper("data2", list1), writeSheet);

4.使用ExcelWriter進行sheet寫入時完成時，最好調用ExcelWriter對象的finish()方法，不然可能出現文件損壞的情況

5.模版文件路徑不要跟輸出文件路徑，如果不想輸出臨時的模版文件，可以將產生新的寫入內存流，然后將內存流的字節數據寫入字節輸入流作為模板輸入
?

    String excelTemplate = "path";FileInputStream fileInputStream = new FileInputStream(excelTemplate);InputStream templateStream = fileInputStream;Workbook workbook = WorkbookFactory.create(templateStream);List<String> fatherSheet = Lists.newArrayList("Sheet1");ArrayList<String> lotNames = new ArrayList<>(lotMap.keySet());//復制sheetfor (String cloneSheet : fatherSheet) {for (String lotName : lotNames) {//根據要復制的sheet名拿到要復制的sheetSheet templateSheet = workbook.getSheet(cloneSheet);Sheet newSheet = workbook.cloneSheet(workbook.getSheetIndex(templateSheet));int newSheetIndex = workbook.getSheetIndex(newSheet);  // 獲取新克隆的 sheet 索引//設置復制的sheet名workbook.setSheetName(newSheetIndex, cloneSheet + "(" + lotName + ")");}}//新建內存輸出流，作為新的模版文件輸出ByteArrayOutputStream ops = new ByteArrayOutputStream();workbook.write(ops);ops.flush();byte[] byteArray = ops.toByteArray();ops.close();workbook.close();//使用新的文件的字節流作為模板文件輸入InputStream asInputStream = new ByteArrayInputStream(byteArray);// 原文件流后續已不使用，此處關閉templateStream.close();ops.close();ExcelWriter excelWriter = EasyExcel.write("D:\\newnewnewFormat111.xlsx").withTemplate(asInputStream).build();

6.使用EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(map);進行填充會出現只有最后一個sheet填充成功，如果想對多個sheet進行填充，要使用ExcelWriter對象操作數據和sheet對象。