Excel template build

All excel builders cannot be singleton, please pay attention！！！

The core principle of template build is to render HTML table elements that meet the layout requirements according to the template engine, and the builder iterates the cells in the table elements to render excel cells.

1.Export template engine selection

1. The following template engines are not imported by default. Users can choose to declare the import in pom.xml according to their own needs.

2. The following template engine version is the minimum version number.

   The following template engine version is the lowest version number.

<dependency>
    <groupId>com.ibeetl</groupId>
    <artifactId>beetl</artifactId>
    <version>2.7.23</version>
</dependency>

<dependency>
    <groupId>org.freemarker</groupId>
    <artifactId>freemarker</artifactId>
    <version>2.3.23</version>
</dependency>

<dependency>
    <groupId>org.codehaus.groovy</groupId>
    <artifactId>groovy-templates</artifactId>
    <version>2.4.13</version>
</dependency>

<dependency>
    <groupId>org.thymeleaf</groupId>
    <artifactId>thymeleaf</artifactId>
    <version>2.1.6.RELEASE</version>
</dependency>

<dependency>
    <groupId>org.apache.velocity</groupId>
    <artifactId>velocity-engine-core</artifactId>
    <version>2.3</version>
</dependency>

<dependency>
    <groupId>com.jfinal</groupId>
    <artifactId>enjoy</artifactId>
    <version>4.8</version>
</dependency>

2.Workbook build

1. When an HTML file already exists, in this way, the HTML file is not limited to the classpath (such as resources) of the project, nor does it need a template engine.

// get html file
File htmlFile = new File("/Users/liaochong/Downloads/example.html");

// read the html file and use default excel style to create excel
Workbook workbook = HtmlToExcelFactory.readHtml(htmlFile).useDefaultStyle().build();

// this is a example,you can write the workbook to any valid outputstream
FileExportUtil.export(workbook, new File("/Users/liaochong/Downloads/excel.xlsx"));

2. Use the built-in template engine excel builder such as FreeMarker. The template file should be stored in classpath. Please refer to the example in the project for details.

/**
* use non-default-style excel builder
* Template files are placed under Resources
*
* @param response response
*/
@GetMapping("/freemarker/example")
public void build(HttpServletResponse response) {
     Map<String, Object> dataMap = this.getDataMap();
     try (ExcelBuilder excelBuilder = new FreemarkerExcelBuilder()) {
          Workbook workbook = excelBuilder
                   // fileTemplate(dirPath,fileName)
                   .classpathTemplate("/templates/freemarkerToExcelExample.ftl")
                   .build(dataMap);
          AttachmentExportUtil.export(workbook, "freemarker_excel", response);
     }
}

/**
* use default-style excel builder
* Template files are placed under Resources
*
* @param response response
*/
@GetMapping("/freemarker/defaultStyle/example")
public void buildWithDefaultStyle(HttpServletResponse response) {
    Map<String, Object> dataMap = this.getDataMap();
    try (ExcelBuilder excelBuilder = new FreemarkerExcelBuilder()){
         Workbook workbook = excelBuilder
                  // fileTemplate(dirPath,fileName)
                  .classpathTemplate("/templates/freemarkerToExcelExample.ftl")
                  .useDefaultStyle()
                  .build(dataMap);
         AttachmentExportUtil.export(workbook, "freemarker_excel", response);
    }
}

private Map<String, Object> getDataMap() {
    Map<String, Object> dataMap = new HashMap<>();
    dataMap.put("sheetName", "freemarker_excel_example");

    List<String> titles = new ArrayList<>();
    titles.add("Category");
    titles.add("Product Name");
    titles.add("Count");
    dataMap.put("titles", titles);

    List<Product> data = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        Product product = new Product();
        if (i % 2 == 0) {
            product.setCategory("蔬菜");
            product.setName("小白菜");
            product.setCount(100);
        } else {
            product.setCategory("电子产品");
            product.setName("ipad");
            product.setCount(999);
        }
        data.add(product);
    }
    dataMap.put("data", data);
    return dataMap;
}

3.Template example

<table>
    <caption>${sheetName}</caption>
    <thead>
    <tr style="background-color: #6495ED">
        <th colspan="3" style="text-align: center;vertical-align: middle;font-weight: bold;font-size: 14px;">产品介绍</th>
    </tr>
    <tr>
        <#list titles as title>
            <th>${title}</th>
        </#list>
    </tr>
    </thead>
    <tbody>
    <#list data as item>
        <tr>
            <td>${item.category}</td>
            <td>${item.name}</td>
            <td>${item.count}</td>
            <td url>百度地址</td>
        </tr>
    </#list>
    </tbody>
</table>