Excel模板构建

所有Excel构建器均不可单例化，务必注意！！！

模板构建，核心原理是根据模板引擎渲染出符合需求布局的Html Table元素，构建器迭代table元素中的单元格渲染出Excel单元格。

1.导出模板引擎选定

1. 以下模板引擎默认均未被引入，使用者可根据自身需要选择在pom.xml中声明引入。

   The following template engine is not introduced by default except Beetl. Users can choose to introduce the introduction in pom.xml according to their needs.

2. 以下模板引擎版本为最低版本号。

   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生成

1. 已存在Html文件时，使用这种方式，Html文件不局限于放在项目的classpath（如：resources）下，也无需模板引擎

// 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. 使用内置的Freemarker等模板引擎Excel构建器，模板文件应当存放在classpath下，具体请参照项目中的example

/**
* use non-default-style excel builder
* 模板文件放置在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
* 模板文件放置在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.模板示例

<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>