# spring-freemarker

**Repository Path**: www.lyc.com/spring-freemarker

## Basic Information

- **Project Name**: spring-freemarker
- **Description**: easyexcel适合处理大数据的excel，但是复杂的excel还是freemarker更合适；如果有需要，二者结合是不是更香？...
- **Primary Language**: Java
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 2
- **Created**: 2022-04-13
- **Last Updated**: 2022-04-13

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 1 分析excel

## 1.1 第一个sheet

![image-20210803162948843](https://gitee.com/xxjqr/noteimg/raw/master/img/image-20210803162948843.png)

- 不变的部分：

  第一部分就是不变的部分

- 变化的部分：

  从第二部分开始，以这个部分为整体迭代循坏

![image-20210803113247569](https://gitee.com/xxjqr/noteimg/raw/master/img/image-20210803113247569.png)





## 1.2 第二个sheet



![image-20210803104437979](https://gitee.com/xxjqr/noteimg/raw/master/img/image-20210803104437979.png)

- 不变的部分：

1. 物业这一栏不用变，最后一个cell、上下左右合并1个单元格即可

![image-20210803105244008](https://gitee.com/xxjqr/noteimg/raw/master/img/image-20210803105244008.png)



- 变化的部分：

1. 左边的日期只需要在A2建一个cell，然后根据迭代停车场数量来往下合并单元格

![image-20210803105902455](https://gitee.com/xxjqr/noteimg/raw/master/img/image-20210803105902455.png)



2. 右边异常统计框只需要在迭代停车场下->迭代值班员中第一条建一个cell，然后根据当前停车场->迭代值班员数量，来往下合并单元格

![image-20210803110412276](https://gitee.com/xxjqr/noteimg/raw/master/img/image-20210803110412276.png)



3. 右边停车场行，如果不是第一行就往右多合并两个单元格

![image-20210803112920012](https://gitee.com/xxjqr/noteimg/raw/master/img/image-20210803112920012.png)



# 2 简化excel，填充变量

根据上面的分析，我们把excel简化成方便代码操控的最小单位，并放入变量占位符

![image-20210803114046846](https://gitee.com/xxjqr/noteimg/raw/master/img/image-20210803114046846.png)



![image-20210803114131539](https://gitee.com/xxjqr/noteimg/raw/master/img/image-20210803114131539.png)





# 3 新建项目

## 3.1 pom.xml

```xml
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.5</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.16</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>

        <!-- 使用其中的FileUtils工具类 -->
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.6</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>	
```



## 3.2 模板配置

```java
import freemarker.template.Configuration;
import freemarker.template.TemplateExceptionHandler;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

import java.io.IOException;

@SpringBootApplication
public class SpringFreemarkerApplication {

    public static void main(String[] args) {
        SpringApplication.run(SpringFreemarkerApplication.class, args);
    }

    @Bean(name = "configuration")
    public Configuration getConfiguration() throws IOException {
        Configuration configuration = new Configuration(Configuration.VERSION_2_3_28);
        configuration.setDefaultEncoding("UTF-8");
        configuration.setTemplateUpdateDelayMilliseconds(0);
        //这种方式是直接指定模板位置
        //configuration.setDirectoryForTemplateLoading(new File("C:\\Users\\xxjqr\\Desktop\\MFiles"));
        
        //这种方式是根据当前class的相对位置来找模板
        configuration.setClassForTemplateLoading(this.getClass(), "/templates/");
        configuration.setTemplateExceptionHandler(TemplateExceptionHandler.RETHROW_HANDLER);
        return configuration;
    }
}
```



## 3.3 核心代码

```java
package com.example.springfreemarker.controller;

import cn.hutool.core.util.RandomUtil;
import com.example.springfreemarker.pojo.dayincome.ComboIncomeDetail;
import com.example.springfreemarker.pojo.dayincome.DayRevenue;
import com.example.springfreemarker.pojo.dayincome.IncomeDetail;
import com.example.springfreemarker.pojo.dayincome.Revenue;
import com.example.springfreemarker.pojo.duty.DayDuty;
import com.example.springfreemarker.pojo.duty.OperRecord;
import com.example.springfreemarker.pojo.duty.ParkRecord;
import com.example.springfreemarker.pojo.duty.Watchman;
import freemarker.template.Configuration;
import freemarker.template.Template;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.annotation.PostConstruct;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.util.ArrayList;
import java.util.HashMap;

@Controller
public class AppController {
    @Autowired
    private Configuration configuration;

    private DayRevenue dayRevenue;
    private DayDuty dayDuty;

    @PostConstruct
    public void init() {
        dayRevenue = DayRevenue.builder()
                .estateRevenue(Revenue.builder()
                        .gzBank("234.00")
                        .total(IncomeDetail.builder().discount("100.21")
                                .notTake("23.00")
                                .real("82.23")
                                .refund("99.12").build())
                        .subjectName("xxjqr物业")
                        .monthCardDetail(ComboIncomeDetail.detailBuilder()
                                .expired("3")
                                .newCard("20")
                                .build())
                        .oppositeTempOnline(IncomeDetail.builder()
                                .discount("88.00")
                                .notTake("121.00")
                                .order("55.13")
                                .real("99.01")
                                .refund("1899.98")
                                .build())
                        .build())
                .parkRevenues(new ArrayList<Revenue>() {
                    {
                        add(Revenue.builder()
                                .subjectName("xxjqr停车场")
                                .monthCardDetail(ComboIncomeDetail.detailBuilder()
                                        .chargeCardConsume("23.21")
                                        .newCard("34.11")
                                        .build())
                                .build());
                        add(Revenue.builder()
                                .subjectName("xxjqr2停车场")
                                .build());
                    }
                }).build();


        dayDuty = DayDuty.builder()
                .estateName("妈卖批物业")
                .totalRecord(OperRecord.builder()
                        .exceptionNum(RandomUtil.randomNumbers(2))
                        .notTakeIncome(RandomUtil.randomNumbers(2))
                        .realIncome(RandomUtil.randomNumbers(2))
                        .repealNum(RandomUtil.randomNumbers(2))
                        .shouldIncome(RandomUtil.randomNumbers(2))
                        .build()
                )
                .parkRecords(new ArrayList<ParkRecord>() {
                    {
                        for (int i = 0; i < 10; i++) {
                            add(ParkRecord.builder()
                                    .name("停车场" + i)
                                    .parkOperRecord(new OperRecord(RandomUtil.randomNumbers(2)
                                            , RandomUtil.randomNumbers(2)
                                            , RandomUtil.randomNumbers(2)
                                            , RandomUtil.randomNumbers(2)
                                            , RandomUtil.randomNumbers(2)))
                                    .watchmans(new ArrayList<Watchman>() {
                                        {
                                            for (int i = 0; i < 5; i++) {
                                                add(new Watchman(RandomUtil.randomString(4), new OperRecord(RandomUtil.randomNumbers(2)
                                                        , RandomUtil.randomNumbers(2)
                                                        , RandomUtil.randomNumbers(2)
                                                        , RandomUtil.randomNumbers(2)
                                                        , RandomUtil.randomNumbers(2))
                                                ));
                                            }
                                        }
                                    })
                                    .exceptions(new ArrayList(){
                                        {
                                            for (int i = 0; i < 5; i++) {
                                                add(new HashMap<String,String>(){
                                                    {
                                                        put("name", RandomUtil.randomString(4));
                                                        put("num", RandomUtil.randomNumbers(1));
                                                    }
                                                });
                                            }
                                        }
                                    })
                                    .build());
                        }
                    }
                }).build();
    }

    @RequestMapping("/export")
    public void export(HttpServletResponse response) throws Exception {

        HashMap<String, Object> map = new HashMap<>();
        map.put("dayRevenue", dayRevenue);
        map.put("dayDuty", dayDuty);
        //构造输出流
        Template template = configuration.getTemplate("复杂excel.ftl", "UTF-8");
        String fileName = "C:\\Users\\xxjqr\\Desktop\\MFiles\\" + "tmp" + ".xlsx";
        File file = new File(fileName);
        FileWriter out = new FileWriter(fileName);
        //变量替换
        template.process(map, out);
        //将文件输出到response,返回给客户端
        FileInputStream in = new FileInputStream(file);
        byte[] buffer = new byte[in.available()];
        in.read(buffer);
        in.close();
        response.reset();
        response.addHeader("Content-Disposition", "attachment;filename=file.xlsx");
        ServletOutputStream outputStream = response.getOutputStream();
        response.setContentType("application/octet-stream");
        outputStream.write(buffer);
        outputStream.flush();
        outputStream.close();
    }
}
```



## 3.4 excel导出xml放入项目

- 导出的xml放到项目resources->templates下，也是默认的位置

- 在idea中打开xml，ctrl+alt+l 先格式化（ftl格式化简直就是魔鬼），再把文件格式改为 ftl（ftl才能支持freemarker语法）；可以再ctrl+alt+-（减号）全体缩进一下，不然全部展开不好下手

![image-20210803120152686](https://gitee.com/xxjqr/noteimg/raw/master/img/image-20210803120152686.png)



- 第一个表单就这么处理好了

![image-20210803163119820](https://gitee.com/xxjqr/noteimg/raw/master/img/image-20210803163119820.png)

- 第二个表单的处理

![image-20210803170740225](https://gitee.com/xxjqr/noteimg/raw/master/img/image-20210803170740225.png)



## 3.5 导出看结果

![image-20210803171955362](https://gitee.com/xxjqr/noteimg/raw/master/img/image-20210803171955362.png)



第二个表单的日期没有按照需求来，一方面我觉得这样比较好看，另一方面现在脑壳晕，不想思考方案

![image-20210803172018409](https://gitee.com/xxjqr/noteimg/raw/master/img/image-20210803172018409.png)

# 4 未完成的操作

在获取到 tmp.xlsx 的流之后就可以删除临时文件了，代码里没写，各位自由发挥