1 分析excel

1.1 第一个sheet

不变的部分：

第一部分就是不变的部分
变化的部分：

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

1.2 第二个sheet

不变的部分：

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

变化的部分：

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

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

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

2 简化excel，填充变量

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

3 新建项目

3.1 pom.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 模板配置

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 核心代码

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();
    }
}