模板

image.png

导出数据到同一个excel中

根据一份模板，导出多个 sheet 中
根据一份模板，导出多分数据到同一个 sheet 中

  1. package com.demo.activiti7;
  3. import com.alibaba.excel.EasyExcel;
  4. import com.alibaba.excel.ExcelWriter;
  5. import com.alibaba.excel.write.handler.SheetWriteHandler;
  6. import com.alibaba.excel.write.handler.WorkbookWriteHandler;
  7. import com.alibaba.excel.write.metadata.WriteSheet;
  8. import com.alibaba.excel.write.metadata.fill.FillConfig;
  9. import com.alibaba.excel.write.metadata.fill.FillWrapper;
  10. import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
  11. import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
  12. import com.demo.activiti7.config.CustomRowWriteHandler;
  13. import com.demo.activiti7.domain.DemoVo;
  14. import java.io.File;
  15. import java.io.FileInputStream;
  16. import java.util.ArrayList;
  17. import java.util.Date;
  18. import java.util.HashMap;
  19. import java.util.Iterator;
  20. import java.util.List;
  21. import java.util.Map;
  22. import java.util.Map.Entry;
  23. import java.util.UUID;
  24. import org.apache.poi.ss.usermodel.Cell;
  25. import org.apache.poi.ss.usermodel.CellCopyPolicy;
  26. import org.apache.poi.ss.usermodel.Row;
  27. import org.apache.poi.ss.usermodel.Sheet;
  28. import org.apache.poi.ss.usermodel.Workbook;
  29. import org.apache.poi.util.IOUtils;
  30. import org.apache.poi.xssf.streaming.SXSSFSheet;
  31. import org.apache.poi.xssf.usermodel.XSSFSheet;
  32. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  33. import org.junit.Test;
  35. public class DemoTest {
  37.     @Test
  38.     public void test001() {
  39. //        List<String> list = Arrays.asList("a,b,c", "e,f,g,a,v,b,c", "xiaohui");
  40. //        List<String> result = list.stream().map(x -> Stream.of(x.split(","))).flatMap(Stream::distinct).distinct()
  41. //                .collect(Collectors.toList());
  42. //        System.out.println(result);
  43. ////        FileCopyUtils
  44. //
  45. //        CustomedUser customedUser = new CustomedUser();
  46.         File file = new File("C:\\Users\\DELL\\Desktop\\工作簿1.xlsx");
  47.         System.out.println(file.exists());
  48.         ExcelWriter excelWriter =
  49.                 EasyExcel.write().file("xiaohui.xlsx")
  50.                         .withTemplate(file)
  51.                         .head(DemoVo.class)
  52.                         .registerWriteHandler(new CustomRowWriteHandler())
  53. //                        .registerWriteHandler(new SheetWriteHandler() {
  54. //                            @Override
  55. //                            public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder,
  56. //                                    WriteSheetHolder writeSheetHolder) {
  57. //                                System.out.println("SheetWriteHandler:"+writeSheetHolder.getSheetName());
  58. //                                writeSheetHolder.setSheetName("xiaohui ceshi");
  59. //                                Integer sheetNo = writeSheetHolder.getSheetNo();
  60. //                                System.out.println("SheetWriteHandler:"+sheetNo);
  61. //                                Workbook workbook = writeWorkbookHolder.getWorkbook();
  62. //                                workbook.setSheetName(sheetNo, "1235465");
  63. ////                                workbook.
  64. //                            }
  65. //
  66. //                            @Override
  67. //                            public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder,
  68. //                                    WriteSheetHolder writeSheetHolder) {
  69. ////                                writeSheetHolder.setSheetName("xiaohui ceshi");
  70. //                            }
  71. //                        })
  72.                         .registerWriteHandler(new WorkbookWriteHandler() {
  73.                             @Override
  74.                             public void beforeWorkbookCreate() {
  76.                             }
  78.                             @Override
  79.                             public void afterWorkbookCreate(WriteWorkbookHolder writeWorkbookHolder) {
  81.                             }
  83.                             @Override
  84.                             public void afterWorkbookDispose(WriteWorkbookHolder writeWorkbookHolder) {
  85.                                 Workbook workbook = writeWorkbookHolder.getWorkbook();
  86.                                 Sheet sheet = workbook.getSheetAt(0);
  87.                                 System.out.println(sheet.getSheetName());
  88.                                 workbook.setSheetName(0, "xiaohui test");
  89.                                 Sheet workbookSheet = workbook.createSheet("复制");
  90.                             }
  91.                         })
  92.                         .needHead(false)
  93.                         .build();
  94. //        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.FALSE).build();
  95.         WriteSheet writeSheet = EasyExcel.writerSheet().needHead(Boolean.TRUE).build();
  96.         List<DemoVo> data = new ArrayList<>();
  97.         for (int i = 0; i < 5; i++) {
  98.             data.add(new DemoVo((long) i, new Date(), new Date()));
  99.         }
  101. //        excelWriter.fill(data, fillConfig, writeSheet);
  103.         Map<String, Object> map = new HashMap<>();
  104.         map.put("company", "xiaohui喜欢");
  105.         map.put("contact", "小辉123123");
  106.         map.put("address", "禅道成都");
  107.         map.put("sheetName", "禅道成都");
  108.         excelWriter.fill(map, writeSheet);
  109.         System.out.println(excelWriter.writeContext().writeSheetHolder().getSheetName());
  110.         excelWriter.write(data, writeSheet);
  112.         excelWriter.finish();
  114.     }
  116.     /**
  117.      * 固定模板格式导出数据 要求将各部分数据导入到同一个 excel 的同一个 sheet 中，每个部分的数据间间隔一行
  118.      *
  119.      * 注意：
  120.      *
  121.      * 当前测试使用 easyExcel 版本
  122.      * <dependency>
  123.      * <groupId>com.alibaba</groupId>
  124.      * <artifactId>easyexcel</artifactId>
  125.      * <version>2.2.6</version>
  126.      * </dependency>
  127.      *
  128.      * ！！！模板格式要为 xlsx 格式；导出的格式也是 xlsx 格式；
  129.      *
  130.      * 实现思路： 1 获取模板 2 通过 XSSFSheet 的 copyRows 方法复制模板数据（构造新的填充模板） 3 使用 easyExcel 填充
  131.      */
  132.     @Test
  133.     public void testExportToSingleSheetByTemplate() {
  134.         File file = new File("C:\\Users\\DELL\\Desktop\\工作簿2填充2.xlsx");
  135.         System.out.println(">>>>>>>>>>>>>>>" + file.exists());
  136.         String fileName = "testExportToSingleSheetByTemplate.xlsx";
  137.         ExcelWriter excelWriter = null;
  138.         int sheetSize = 5;
  139.         FileInputStream fileInputStream = null;
  140.         XSSFWorkbook xssfWorkbook = null;
  141.         try {
  143.             // 这里 指定文件
  144.             excelWriter = EasyExcel.write(fileName)
  145.                     .withTemplate(file)
  146.                     .registerWriteHandler(new SheetWriteHandler() {
  147.                         @Override
  148.                         public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder,
  149.                                 WriteSheetHolder writeSheetHolder) {
  150.                             Workbook workbook = writeWorkbookHolder.getWorkbook();
  151.                             Sheet sheet = workbook.getSheetAt(0);
  152.                             System.out.println(">>>>>>>>>sheet type:" + sheet.getClass().getCanonicalName());
  153.                             SXSSFSheet sxssfSheet = (SXSSFSheet) sheet;
  154.                             XSSFSheet xssfSheet = sxssfSheet.getWorkbook().getXSSFWorkbook().getSheetAt(0);
  155.                             int lastRowNum = xssfSheet.getLastRowNum();
  156.                             System.out.println(">>>>>>>>>lastRowNum:" + lastRowNum);
  157.                             System.out.println(">>>>>>>>>> workbook type:" + workbook.getClass().getCanonicalName());
  159.                             // 复制模板
  160.                             for (int i = 1; i < sheetSize; i++) {
  161.                                 xssfSheet.copyRows(0, lastRowNum, i * (lastRowNum + 2), new CellCopyPolicy());
  162.                             }
  163.                             // 替换变量
  164.                             Iterator<Row> iteratorRow = xssfSheet.iterator();
  165.                             int i = 0;
  166.                             while (iteratorRow.hasNext()) {
  167.                                 Row row = iteratorRow.next();
  168.                                 Iterator<Cell> iterator = row.iterator();
  169.                                 while (iterator.hasNext()) {
  170.                                     Cell next = iterator.next();
  171.                                     String stringCellValue = next.getStringCellValue();
  172.                                     if (stringCellValue.startsWith("{.")) {
  173.                                         next.setCellValue(stringCellValue.replace("{.", "{data" + i + "."));
  174.                                     } else if (stringCellValue.contains("{")) {
  175.                                         next.setCellValue(stringCellValue.replace("{", "{data" + i + "_"));
  176.                                     }
  177.                                 }
  178.                                 int rowNum = row.getRowNum();
  179.                                 if (rowNum > 0 && (rowNum % (lastRowNum + 2) == 0)) {
  180.                                     i++;
  181.                                 }
  182.                             }
  183.                         }
  185.                         @Override
  186.                         public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder,
  187.                                 WriteSheetHolder writeSheetHolder) {
  189.                         }
  190.                     })
  191.                     .registerWriteHandler(new WorkbookWriteHandler() {
  192.                         @Override
  193.                         public void beforeWorkbookCreate() {
  195.                         }
  197.                         @Override
  198.                         public void afterWorkbookCreate(WriteWorkbookHolder writeWorkbookHolder) {
  200.                         }
  202.                         @Override
  203.                         public void afterWorkbookDispose(WriteWorkbookHolder writeWorkbookHolder) {
  204.                             writeWorkbookHolder.getWorkbook().setSheetName(0, "终于调试好了");
  205.                         }
  206.                     })
  207.                     .build();
  208.             WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
  209.             HashMap<String, List<DemoVo>> mapData = new HashMap<>();
  210.             Map<String, Object> map = new HashMap<>();
  211.             for (int i = 0; i < sheetSize; i++) {
  212.                 // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
  213.                 List<DemoVo> data = new ArrayList<>();
  214.                 for (int j = 0; j < 5; j++) {
  215.                     data.add(new DemoVo((long) j, new Date(), new Date()));
  216.                 }
  217.                 String fillObjName = "data" + i;
  218.                 map.put(fillObjName + "_company", "xiaohui喜欢");
  219.                 map.put(fillObjName + "_contact", "小辉123123");
  220.                 map.put(fillObjName + "_address", "禅道成都");
  221.                 map.put(fillObjName + "_sheetName", "禅道成都");
  222.                 mapData.put(fillObjName, data);
  223.             }
  224.             System.out.println(">>>>>>>>>>>");
  225.             excelWriter.fill(map, writeSheet);
  226.             for (Entry<String, List<DemoVo>> ele : mapData.entrySet()) {
  227.                 excelWriter.fill(new FillWrapper(ele.getKey(), ele.getValue()),
  228.                         FillConfig.builder().forceNewRow(true).build(),
  229.                         writeSheet);
  230.             }
  231.         } finally {
  232.             // 千万别忘记finish 会帮忙关闭流
  233.             if (excelWriter != null) {
  234.                 excelWriter.finish();
  235.             }
  236.             IOUtils.closeQuietly(fileInputStream);
  237.             IOUtils.closeQuietly(xssfWorkbook);
  238.         }
  239.     }
  241.     /**
  242.      * 固定模板格式导出数据 要求将各部分数据导入到同一个 excel 的不同 sheet 中
  243.      *
  244.      * 注意：
  245.      * <dependency>
  246.      * <groupId>com.alibaba</groupId>
  247.      * <artifactId>easyexcel</artifactId>
  248.      * <version>2.2.6</version>
  249.      * </dependency>
  250.      * 模板的格式必须是 xls 的。 导出的格式可以为 xls、xlsx
  251.      *
  252.      * 实现思路：1 加载模板；2 复制模板sheet到新的sheet；3 逐一填充每个sheet；
  253.      */
  254.     @Test
  255.     public void testExportToMultiSheetByTemplate() {
  256.         File file = new File("C:\\Users\\DELL\\Desktop\\工作簿2.xls");
  257.         System.out.println(">>>>>>>>>>>>>>>" + file.exists());
  258.         String fileName = "testExportToMultiSheetByTemplate.xlsx";
  259.         ExcelWriter excelWriter = null;
  260.         int sheetSize = 5;
  261.         try {
  262.             // 这里 指定文件
  263.             excelWriter =
  264.                     EasyExcel.write(fileName).withTemplate(file).registerWriteHandler(new WorkbookWriteHandler() {
  265.                         @Override
  266.                         public void beforeWorkbookCreate() {
  268.                         }
  270.                         @Override
  271.                         public void afterWorkbookCreate(WriteWorkbookHolder writeWorkbookHolder) {
  272.                             Workbook workbook = writeWorkbookHolder.getWorkbook();
  273.                             for (int i = 1; i < sheetSize; i++) {
  274.                                 // java.lang.RuntimeException: NotImplemented,xlsx格式报错,xls才有clone方法
  275.                                 workbook.cloneSheet(0);
  276.                                 // 重命名文件sheet名称
  277.                                 workbook.setSheetName(i, "模板" + UUID.randomUUID().toString().substring(10));
  278.                             }
  279.                         }
  281.                         @Override
  282.                         public void afterWorkbookDispose(WriteWorkbookHolder writeWorkbookHolder) {
  284.                         }
  285.                     }).build();
  287.             // 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
  288.             for (int i = 0; i < sheetSize; i++) {
  289. //                new FillConfigBuilder().
  290.                 WriteSheet writeSheet = EasyExcel.writerSheet(i).head(DemoVo.class).needHead(true).build();
  291.                 // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
  292.                 List<DemoVo> data = new ArrayList<>();
  293.                 for (int j = 0; j < 5; j++) {
  294.                     data.add(new DemoVo((long) j + i, new Date(), new Date()));
  295.                 }
  296.                 Map<String, Object> map = new HashMap<>();
  297.                 map.put("company", "xiaohui喜欢");
  298.                 map.put("contact", "小辉123123");
  299.                 map.put("address", "禅道成都");
  300.                 map.put("sheetName", "禅道成都");
  301.                 excelWriter.fill(map, writeSheet);
  302.                 System.out.println(excelWriter.writeContext().writeSheetHolder().getSheetName());
  303.                 excelWriter.write(data, writeSheet); // 如果设定的是fill填充方式，就使用 fill 方法
  304.             }
  305.         } finally {
  306.             // 千万别忘记finish 会帮忙关闭流
  307.             if (excelWriter != null) {
  308.                 excelWriter.finish();
  309.             }
  310.         }
  311.     }
  313. }

小结

实现的思路：都是构造一个与之对应份数的模板，然后逐一填充数据。

何时使用填充，何时使用直接写呢（或者说是否使用模板）？大多数时候二者可以混用。填充需要指定模板（占位符的存在），直接写入可以不用额外的模板（当然也可以使用）。对于存在异形数据的或者特殊格式（样式）的，比如制表人、统计等，建议使用填充，直接写入不用模板的方式太过于麻烦。

将多个sheet中的数据合并到同一个 excel 中（相同结构的数据）

根据模板，把多分数据合并导出到一个 excel 内

未去具体实现，初步思考的思路仍是进行相关复制和填充（需要注意excel很多行的情形，超过行数和 oom 问题）