Freemarker Spring中Excel导出浏览器中下载

遇到的问题

导出数据不显示
ExpandedRowCount="具体数值" 改成 ExpandedRowCount="${resultList?size + 6}"

<Table ss:ExpandedColumnCount="11" ss:ExpandedRowCount="${resultList?size + 6}" x:FullColumns="1"

导出模板错误

FTL stack trace ("~" means nesting-related):
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:876)
    - Failed at: ${dto.stateDesc}  [in template "orderListTemplate.ftl" at line 110, column 67]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)

解决办法

${dto.stateDesc}      改成     ${(dto.stateDesc)!""}
${dto.createdAt}      改成     ${dto.createdAt?string('yyyy-MM-dd HH:mm:ss')}

模板制作
excel另存为xml格式

项目中新建文件.ftl

把xml用编辑器打开复制到新建的文件中

<#list resultList as dto>
     <Row>
         <Cell ss:StyleID="s63"><Data ss:Type="String">${dto.createdAt?string('yyyy-MM-dd')}</Data></Cell>
         <Cell ss:StyleID="s69"><ss:Data ss:Type="String">${(dto.userName)!""}</ss:Data></Cell>
     </Row>
</#list>

代码实现

pom

<!-- Freemarker -->
<dependency>
    <groupId>org.freemarker</groupId>
    <artifactId>freemarker</artifactId>
    <version>2.3.26-incubating</version>
</dependency>

excel util

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.util.Map;

import freemarker.template.Configuration;
import freemarker.template.Template;


public class ExcelUtils {

    private static Configuration configuration =null;
    private static Map<String, Template> allTemplates =null;
    public ExcelUtils(){
        throw new AssertionError();
    }
    /**
     * 创建excel
     * @param dataMap
     * @return
     */
    public static File createExcel(Map<String,Object> dataMap, String templatePath,String templateName) throws IOException {
        try {
            configuration = new Configuration();
            configuration.setDefaultEncoding("UTF-8");
            configuration.setDirectoryForTemplateLoading(new File(templatePath));
        } catch (IOException ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        }
        String name = "temp" + (int) (Math.random() * 100000) + ".xls";
        File file = new File(name);
        Template template = configuration.getTemplate(templateName,"utf-8");

        try {
            Writer w = new OutputStreamWriter(new FileOutputStream(file), "utf-8");
            template.process(dataMap, w);
            w.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return file;
    }

}

header util 直接从浏览器下载

import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

public class XlsUtil {
    
    public static void setXlsHeader(HttpServletResponse response,String fileName){
        response.reset();
        response.setHeader("Expires", "0");
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
        response.setHeader("Cache-Control", "public");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF8"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
    }
}

导出代码

String filename = "每日数据总计列表" + DateUtil.formatDateTime("yyyyMMdd", new Date()) + "_" + new Random().nextInt() + ".xls";

    File file = null;
    InputStream inputStream = null;
    OutputStream out = null;
    try {
        request.setCharacterEncoding("UTF-8");
        file = ExcelUtils.createExcel(data, templatePath, "dailyAmountTotal.ftl");
        inputStream = new FileInputStream(file);
        XlsUtil.setXlsHeader(response, filename);
        out = response.getOutputStream();
        byte[] buffer = new byte[512]; // 缓冲区
        int bytesToRead = -1;
        // 通过循环将读入的Excel文件的内容输出到浏览器中
        while ((bytesToRead = inputStream.read(buffer)) != -1) {
            out.write(buffer, 0, bytesToRead);
        }
        out.flush();
    } catch (FileNotFoundException e) {
        log.error("导出每日数据总计列表出错：", e);
    }  catch (IOException e) {
        log.error("导出每日数据总计列表出错：", e);
    } finally {
        if (null != out) {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

• Spring Boot 中freemarker导出

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

package com.ranhan.utils;

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

import javax.annotation.Resource;
import java.io.*;
import java.util.Map;

/**
 * Excel工具类
 * Created by Ran Han on 2018/1/3.
 */
@Component
public class ExcelUtil {

    @Resource
    FreeMarkerConfigurer freeMarkerConfigurer;

    /**
     * 生成Excel
     * @param excleTemplat 导出模板信息
     * @param exportData 导出数据
     * @return
     * @throws IOException
     */
    public String createExcel(String excleTemplat, Map<String, Object> exportData) throws IOException {
        // 获得模板信息
        Template excelTemplate = freeMarkerConfigurer.getConfiguration().getTemplate(excleTemplat);
        String filename = DateUtil.getNewDate() + ".xls";
        File file = new File(filename);
        try {
            Writer w = new OutputStreamWriter(new FileOutputStream(file), "utf-8");
            excelTemplate.process(exportData, w);
            w.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return filename;
    }

    public void deleteExcel(String filename) {
        File file = new File(filename);
        file.delete();
    }
}

Spring Boot中Jxls导出