springBoot通过FreeMarker将模板文件导出复杂格式的Excel文件

springBoot通过FreeMarker将模板文件导出复杂格式的Excel文件

一、 准备模板文件

1、打开填充好的Excel模板文件另存为xml文件2、然后将xml文件拷贝到项目中重命名为ftl文件

3、项目配置文件配置templates为模板文件夹
4、 配置模板路径

// An highlighted block
spring.freemarker.template-loader-path=classpath:/templates/

5、传输数据
Controller传输需要的数据

	@GetMapping("/exportExcel")
    public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
        JSONObject param = new JSONObject();
        String date = request.getParameter("date");
        String creditCode = request.getParameter("creditCode");
        String userName = request.getParameter("userName");
        param.put("creditCode",creditCode);
        param.put("date",date);
        param.put("userName",userName);
        String fileName =  date+"月人工费用项目分摊表" + ".xlsx";
        Map<String,Object> resultMap= getList(param);
	        FreeMarkerTemplateUtil.downloadDoc(freemarkerConfigurer,"template/personCost2.ftl", resultMap, fileName, response);
    }
public Map<String,Object> getList(JSONObject param){
        String date = param.get("date").toString();
        String creditCode = param.get("creditCode").toString();
        String userName = param.get("userName").toString();
        //人员列表
        Set<Map<String,Object>> list = dDeductionPersonnelService.getUserName(param);
        //rd列表
        List<Map<String,Object>> rdList = dPersonCostService.getRdList(param);
        //数据列表
        QueryWrapper<DPersonCost> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("credit_code",creditCode).eq("date",date);
        if(StringUtils.isNotEmpty(userName)){
            queryWrapper.like("user_name",userName);
        }
       List<DPersonCost> dPersonCostList = dPersonCostService.list(queryWrapper);
        if(list.size()>0){
            for(Map map2:list){
                String userName2 = map2.get("userName").toString();
                param.put("userName",userName2);
                Integer tolTall = dPersonCostService.getWorksAll(param);
                map2.put("worksAll",tolTall);//每个人的总工时
            }
        }
        Map map = new HashMap();
        map.put("userList",list);//人员列表
        map.put("rdList",rdList);//rd列表
        map.put("data",dPersonCostList);//数据列表
        return map;
    }

新建FreeMarkerTemplateUtil导出工具类

// An highlighted block
package com.chenva.main.util;

import freemarker.template.Configuration;
import freemarker.template.Template;
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.view.freemarker.FreeMarkerConfigurer;

import javax.servlet.http.HttpServletResponse;
import java.io.PrintWriter;
import java.net.URLEncoder;
import java.util.Map;

@Component
public class FreeMarkerTemplateUtil {

    private static Configuration configuration = null;
    static {
        try {
            configuration = new Configuration(Configuration.VERSION_2_3_23);
            configuration.setDefaultEncoding("UTF-8");
            //configuration.setTemplateUpdateDelayMilliseconds(0);
            //configuration.setTemplateExceptionHandler(TemplateExceptionHandler.RETHROW_HANDLER);
            //cfg.setServletContextForTemplateLoading(getServletContext(), "/WEB-INF/templates");
            configuration.setClassForTemplateLoading(FreeMarkerTemplateUtil.class, ""); // FTL文件所存在的位置
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void downloadDoc(FreeMarkerConfigurer freemarkerConfigurer, String modelPath, Map<String, Object> data,
                                   String fileName, HttpServletResponse response) {
        try {
            Template template = freemarkerConfigurer.getConfiguration().getTemplate(modelPath);
            response.reset();
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            PrintWriter writer = response.getWriter();
            template.process(data, writer);
            writer.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

6、模板文件填充数据

<#assign total = total +(users.thisWages!0)*1+(users.thisSocialInsurance!0)*1+(users.thisAccumulationFund!0)*1>
                <#list rdList as rdList>
                 <Cell ss:Index="${2*(3+rdList_index)}" ss:StyleID="s52">
                     <#list data as item>
                         <#if (item.userName == users.userName) && (item.rdName == rdList.rdName)>
                             <Data ss:Type="Number">${(item.rdWages!0)*1+(item.rdSocialInsurance!0)*1+(item.rdAccumulationFund!0)*1}</Data>
                         </#if>
                     </#list>
                 </Cell>
                </#list>

至此导出结束