查询结果集使用freemarker解析生成excel

  • 获取结果集
  • 解析模板
  • 生成文件

获取结果集

...
// 查询用户列表
List<UserVo> list = queryUserList.getData().getResultList();
// 判断是否存在
if (list != null && list.size() > 0) {
    TemplateParseUtil templateParseUtil = new TemplateParseUtil();
    Map<String, Object> data = new HashMap<>();
    data.put("userName", "文件管理系统");
    data.put("createTime", CustDateUtil.getTimestamp());
    data.put("rows", list);
    data.put("subTitle", "文件列表");
    // 生成EXCEL文件，返回文件url路径
    String url = templateParseUtil.downloadExcelAndSave(data,     "01", TemplateParseUtil.Type.FileInfo);
    // 返回的url后续用户保存文件表
    ...
}

其中工具类TemplateParseUtil为导出excel模板类文件。枚举类

public enum Type {

    FileInfo("文件表信息", "fileInfo.ftl"),
    UserInfo("用户信息","userInfo.ftl");


    private final String tile;

    private final String ftlName;

    Type(String tile, String ftlName) {
        this.tile = tile;
        this.ftlName = ftlName;
    }

    public String getTitle() {
        return tile;
    }

    public String getFtlName() {
        return ftlName;
    }
}

其中fileInfo.ftl文件为可编辑的excel，后缀为xml格式的excel文件。可以新建一个excel文档，另存为xml格式，此时fileInfo.ftl文件即为xml文件内容

excel文件另存为xml格式

FileInfo.ftl文件内容（略长）
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

      xmlns:o="urn:schemas-microsoft-com:office:office"
      xmlns:x="urn:schemas-microsoft-com:office:excel"
      xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
      xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
    <Author>caitc_yanfa_006</Author>
    <LastAuthor>caitc_yanfa_006</LastAuthor>
    <Created>2017-03-30T11:59:28Z</Created>
    <LastSaved>2019-12-04T08:20:15Z</LastSaved>
    <Version>15.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
    <AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>8544</WindowHeight>
    <WindowWidth>23016</WindowWidth>
    <WindowTopX>0</WindowTopX>
    <WindowTopY>0</WindowTopY>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
    <Style ss:ID="Default" ss:Name="Normal">
        <Alignment ss:Vertical="Bottom"/>
                                        <Borders/>
                                                 <Font ss:FontName="Arial" x:Family="Swiss"/>
                                                                                            <Interior/>
                                                                                                      <NumberFormat/>
                                                                                                                    <Protection/>
    </Style>
    <Style ss:ID="s62">
        <Alignment ss:Vertical="Center"/>
                                        <Borders/>
                                                 <Font ss:FontName="MS Sans Serif" x:Family="Swiss" ss:Size="8"
        ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s63">
        <Alignment ss:Vertical="Center"/>
                                        <Borders/>
                                                 <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="8"
        ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s64">
        <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
                                                             <Borders/>
                                                                      <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="6"
        ss:Color="#000000"/>
                           <Interior/>
                                     <NumberFormat ss:Format="@"/>
                                                                 <Protection/>
    </Style>
    <Style ss:ID="s65">
        <Alignment ss:Vertical="Center"/>
                                        <Borders/>
                                                 <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="6"
        ss:Color="#000000"/>
                           <Interior/>
                                     <NumberFormat ss:Format="@"/>
                                                                 <Protection/>
    </Style>
    <Style ss:ID="s66">
        <Alignment ss:Vertical="Center"/>
    </Style>
    <Style ss:ID="s67">
        <Alignment ss:Vertical="Center"/>
                                        <Borders/>
                                                 <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="6"
        ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s68">
        <Alignment ss:Vertical="Center"/>
                                        <Borders/>
                                                 <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="12"
        ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s70">
        <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
                                                             <Borders/>
                                                                      <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="12"
        ss:Color="#000000" ss:Bold="1"/>
                                       <Interior/>
                                                 <NumberFormat ss:Format="@"/>
                                                                             <Protection/>
    </Style>
    <Style ss:ID="s71">
        <Alignment ss:Vertical="Center"/>
                                        <Borders/>
                                                 <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="12"
        ss:Color="#000000" ss:Bold="1"/>
                                       <Interior/>
                                                 <NumberFormat ss:Format="@"/>
                                                                             <Protection/>
    </Style>
    <Style ss:ID="s72">
        <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
                                                             <Borders/>
                                                                      <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="8"/>
    </Style>
    <Style ss:ID="s73">
        <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
                                                             <Borders/>
                                                                      <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="8"
        ss:Bold="1"/>
                    <Interior ss:Color="#C5D9F1" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s74">
        <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
                                                             <Borders/>
                                                                      <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="8"
        ss:Bold="1"/>
                    <Interior ss:Color="#C5D9F1" ss:Pattern="Solid"/>
                                                                    <NumberFormat ss:Format="@"/>
    </Style>
    <Style ss:ID="s75">
        <Alignment ss:Vertical="Center"/>
                                        <Borders/>
                                                 <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="8"/>
    </Style>
    <Style ss:ID="s76">
        <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
                                                             <Borders/>
                                                                      <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="8"/>
                                                                                                                                            <NumberFormat ss:Format="@"/>
    </Style>
    <Style ss:ID="s78">
        <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
                                                             <Borders/>
                                                                      <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="8"/>
                                                                                                                                            <Interior ss:Color="#C5D9F1" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s79">
        <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
                                                             <Borders/>
                                                                      <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="8"/>
                                                                                                                                            <Interior ss:Color="#C5D9F1" ss:Pattern="Solid"/>
                                                                                                                                                                                            <NumberFormat ss:Format="@"/>
    </Style>
    <Style ss:ID="s80">
        <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
                                                             <Borders/>
                                                                      <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="8"/>
                                                                                                                                            <Interior ss:Color="#C5D9F1" ss:Pattern="Solid"/>
                                                                                                                                                                                            <NumberFormat ss:Format="General Date"/>
    </Style>
    <Style ss:ID="s81">
        <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
                                                               <Borders/>
                                                                        <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="8"/>
    </Style>
    <Style ss:ID="s82">
        <Borders/>
                 <Font ss:FontName="微软雅黑" x:CharSet="134" x:Family="Swiss" ss:Size="8"/>
                                                                                       <NumberFormat ss:Format="@"/>
    </Style>
</Styles>
<Worksheet ss:Name="文件管理列表结果">
    <Table ss:ExpandedColumnCount="13" x:FullColumns="1"
           x:FullRows="1" ss:StyleID="s62" ss:DefaultColumnWidth="61.8"
           ss:DefaultRowHeight="10.5">
        <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="48"/>
        <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="183.6"/>
        <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="105"/>
        <Column ss:StyleID="s62" ss:Width="482.4"/>
        <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="155.4"/>
        <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="75"/>
        <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="83.4"/>
        <Column ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="102.6"/>
        <Row ss:AutoFitHeight="0" ss:Height="13.2" ss:StyleID="s63">
            <Cell ss:StyleID="s64"><Data ss:Type="String">制表人</Data></Cell>
            <Cell ss:StyleID="s65"><Data ss:Type="String">${userName}</Data></Cell>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
        </Row>
        <Row ss:AutoFitHeight="0" ss:Height="13.2" ss:StyleID="s67">
            <Cell ss:StyleID="s64"><Data ss:Type="String">制表时间</Data></Cell>
            <Cell ss:StyleID="s65"><Data ss:Type="String">${time}</Data></Cell>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
            <Cell ss:StyleID="s66"/>
        </Row>
        <Row ss:AutoFitHeight="0" ss:Height="17.55" ss:StyleID="s68">
            <Cell ss:MergeAcross="4" ss:StyleID="s70"><Data ss:Type="String">文件管理列表结果</Data></Cell>
            <Cell ss:StyleID="s71"/>
            <Cell ss:StyleID="s71"/>
            <Cell ss:StyleID="s71"/>
        </Row>
        <Row ss:AutoFitHeight="0" ss:Height="13.2" ss:StyleID="s72">
            <Cell ss:StyleID="s73"><Data ss:Type="String">序号</Data></Cell>
            <Cell ss:StyleID="s73"><Data ss:Type="String">项目名称</Data></Cell>
            <Cell ss:StyleID="s74"><Data ss:Type="String">文件类型</Data></Cell>
            <Cell ss:StyleID="s74"><Data ss:Type="String">文件名称</Data></Cell>
            <Cell ss:StyleID="s74"><Data ss:Type="String">文件描述</Data></Cell>
            <Cell ss:StyleID="s73"><Data ss:Type="String">项目编号</Data></Cell>
            <Cell ss:StyleID="s73"><Data ss:Type="String">文件大小</Data></Cell>
            <Cell ss:StyleID="s73"><Data ss:Type="String">生成时间</Data></Cell>
        </Row>
        <#list rows as row>
            <#if row_index%2 == 0 >
                <Row ss:AutoFitHeight="0" ss:Height="13.5" ss:StyleID="s74">
                    <Cell ss:StyleID="s72"><Data ss:Type="Number">${row_index+1}</Data></Cell>
                    <Cell ss:StyleID="s72"><Data ss:Type="String">${row.projectName!"-"}</Data></Cell>
                    <Cell ss:StyleID="s76"><Data ss:Type="String">${row.fileTypeName!"-"}</Data></Cell>
                    <Cell ss:StyleID="s76"><Data ss:Type="String">${row.fileName!"-"}</Data></Cell>
                    <Cell ss:StyleID="s76"><Data ss:Type="String">${row.fileDes!"-"}</Data></Cell>
                    <Cell ss:StyleID="s76"><Data ss:Type="String">${row.projectCode!"-"}</Data></Cell>
                    <Cell ss:StyleID="s72"><Data ss:Type="String">${row.fileSize!"-"}</Data></Cell>
                    <Cell ss:StyleID="s76"><Data ss:Type="String">${row.createTimeStr!"-"}</Data></Cell>
                </Row>
            </#if>
            <#if row_index%2 == 1 >
                <Row ss:AutoFitHeight="0" ss:Height="14.25" ss:StyleID="s74">
                    <Cell ss:StyleID="s78"><Data ss:Type="Number">${row_index+1}</Data></Cell>
                    <Cell ss:StyleID="s78"><Data ss:Type="String">${row.projectName!"-"}</Data></Cell>
                    <Cell ss:StyleID="s79"><Data ss:Type="String">${row.fileTypeName!"-"}</Data></Cell>
                    <Cell ss:StyleID="s79"><Data ss:Type="String">${row.fileName!"-"}</Data></Cell>
                    <Cell ss:StyleID="s79"><Data ss:Type="String">${row.fileDes!"-"}</Data></Cell>
                    <Cell ss:StyleID="s79"><Data ss:Type="String">${row.projectCode!"-"}</Data></Cell>
                    <Cell ss:StyleID="s78"><Data ss:Type="String">${row.fileSize!"-"}</Data></Cell>
                    <Cell ss:StyleID="s79"><Data ss:Type="String">${row.createTimeStr!"-"}</Data></Cell>
                </Row>
            </#if>
        </#list>
        <Row ss:AutoFitHeight="0" ss:Height="13.2" ss:StyleID="s75">
            <Cell ss:StyleID="s81"/>
            <Cell ss:Index="3" ss:StyleID="s82"/>
            <Cell ss:StyleID="s82"/>
            <Cell ss:StyleID="s82"/>
            <Cell ss:StyleID="s82"/>
        </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
        <Unsynced/>
        <Print>
            <ValidPrinterInfo/>
            <PaperSizeIndex>9</PaperSizeIndex>
            <HorizontalResolution>600</HorizontalResolution>
            <VerticalResolution>600</VerticalResolution>
        </Print>
        <Zoom>115</Zoom>
        <Selected/>
        <FreezePanes/>
        <FrozenNoSplit/>
        <SplitHorizontal>4</SplitHorizontal>
        <TopRowBottomPane>4</TopRowBottomPane>
        <ActivePane>2</ActivePane>
        <Panes>
            <Pane>
                <Number>3</Number>
            </Pane>
            <Pane>
                <Number>2</Number>
                <ActiveRow>5</ActiveRow>
                <ActiveCol>7</ActiveCol>
            </Pane>
        </Panes>
        <ProtectObjects>False</ProtectObjects>
        <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
</Worksheet>

</Workbook>

解析模板

/**
  * 创建excel文件
  * @param data        查询结果集
  * @param fileName    文件名称
  * @param type        模板类型
  * @return
  * @throws IOException
  */
public File createExcel(Map<String, Object> data, String fileName, Type type) throws IOException {

    File file = null;
    try {
        // 统一设置title
        data.put("title", type.getTitle());

        // 解析模版
        file = parse(type.getFtlName(), fileName, data);
    } catch (IOException | TemplateException e) {
        e.printStackTrace();
    }
    return file;
}

具体实现parse(type.getFtlName(), fileName, data);

/**
 * 解析模板生成Excel
 *
 * @param templateName
 *            模板名称
 * @param fileName
 *            生成的Excel文件名
 * @param data
 *            数据参数
 * @throws IOException
 * @throws TemplateException
 */
private File parse(String templateName, String fileName, Map<String, Object> data)
        throws IOException, TemplateException {
    // 初始化工作
    logger.info("2、初始化FreeMarkerConfigurer，获取FreeMarker实例");
    FreeMarkerConfigurer freeMarkerConfigurer = SpringContextHolder.getBean("freeMarkerConfigurer");
    Configuration cfg = freeMarkerConfigurer.getConfiguration();
    logger.info("3、FreeMarker全局参数配置");
    // 全局数字格式
    cfg.setNumberFormat("0.0000");
    // 加载模板
    logger.info("4、FreeMarker加载模板：" + templateName);
    Template template = cfg.getTemplate(templateName, "utf-8");
    File file = new File(fileName);
    Writer writer = null;
    try {
        // 填充数据至Excel
        writer = new OutputStreamWriter(new FileOutputStream(file), "UTF-8");
        logger.info("5、FreeMarker开始数据填充");
        template.process(data, writer);
        logger.info("6、FreeMarker完成数据填充,清除模板缓存");
        cfg.clearTemplateCache();
        writer.flush();
    } finally {
        assert writer != null;
        writer.close();
        logger.info("7、关闭文件输出流");
    }

    return file;
}

在配置文件context-application.xml中注册bean：freeMarkerConfigurer

<!--设置freemaker的配置文件路径-->
<bean id="freeMarkerConfigurer" class="org.springframework.web.servlet.view.freemarker.FreeMarkerConfigurer">
    <!--定义了模板的加载路径-->
    <property name="templateLoaderPath" value="/WEB-INF/resources/template/"/>
    <property name="freemarkerSettings">
        <props>
            <prop key="defaultEncoding">UTF-8</prop>
        </props>
    </property>
</bean>

生成文件

生成文件提供两种应用场景，一种是直接生成在服务器文件路径下，一种是返回浏览器下载。

存入服务器

解析方法downloadExcelAndSave

/**
 * 用freemarker解析生成excel，存入服务器
 * @param data        查询结果集
 * @param fileType    文件类型
 * @param type        模板类型
 * @return
 * @throws IOException
 */
public String downloadExcelAndSave(Map<String, Object> data, String fileType, Type type) throws IOException {

    InputStream inputStream = null;
    OutputStream out = null;
    File file = null;
    String fileName = "";
    try {
        // 解析模版
        if (type.getFtlName().contains("productHold")) {
            fileName = type.getTitle();
        } else {
            fileName = data.get("subTitle") + "-" + type.getTitle();
        }
        logger.info("---------------------EXCEL 生成文件开始---------------------");
        logger.info("1、获取模板名称：" + fileName);
        // 导出EXCEL
        file = createExcel(data, fileName, type);
        logger.info("---------------------EXCEL 生成文件结束---------------------");
        inputStream = new FileInputStream(file);
        String outfileName = fileName + "_" + ToolUtil.formateYYYYMMDD(new Date());
        //EXCEL导出路径
        String path = PropertiesLoader.get("attachment.file.server.root") + "family" + File.separator + fileType + File.separator + outfileName + ".xls";
        File downloadFile = new File(path);
        // 判断目录是否存在，不存在则新增
        if (!downloadFile.exists()) {
            File parentdir = ToolUtil.parent(downloadFile);
            if (!parentdir.exists())
                parentdir.mkdirs();
        }
        out = new FileOutputStream(downloadFile);
        byte[] buffer = new byte[1024]; // 缓冲区
        int bytesToRead;
        // 通过循环将读入的Excel文件的内容输出到服务器中
        while ((bytesToRead = inputStream.read(buffer)) != -1) {
            out.write(buffer, 0, bytesToRead);
        }
        out.flush();

        //获取文件除去根路径剩余的路径
        String url = File.separator + "family" + File.separator + fileType + File.separator + outfileName + ".xls";

        return url;
    } catch (IOException e) {
        e.printStackTrace();
        throw new IOException(e);
    } finally {
        if (inputStream != null) {
            inputStream.close();
        }
        if (out != null) {
            out.close();
        }
        if (file != null) {
            file.delete();
        }
    }
}

直接生成文件到目标目录path。

返回浏览器下载

另外一种是生成了文件之后通过浏览器返回给用户下载

/**
 * 用freemarker解析生成excel，返回浏览器下载
 *
 * @param response    HttpServletResponse
 * @param data    查询结果集
 * @param type    模板类型
 * @throws IOException
 */
public void downloadExcel(HttpServletResponse response, Map<String, Object> data, Type type) throws IOException {

    InputStream inputStream = null;
    OutputStream out = null;
    File file = null;
    String fileName = "";
    try {
        // 解析模版
        if (type.getFtlName().contains("productHold")) {
            fileName = type.getTitle();
        } else {
            fileName = data.get("subTitle") + "-" + type.getTitle();
        }
           logger.info("---------------------EXCEL 生成文件开始---------------------");
        logger.info("1、获取模板名称：" + fileName);
        // 导出EXCEL
        file = createExcel(data, fileName, type);
        logger.info("---------------------EXCEL 生成文件结束---------------------");
        inputStream = new FileInputStream(file);
        String outfileName = new String(fileName.getBytes("GBK"), "ISO8859_1");
        outfileName = outfileName + "_" + ToolUtil.formateYYYYMMDD(new Date());
        response.setHeader("Content-disposition", "attachment; filename=" + outfileName + ".xls");
        response.setContentType("application/octet-stream; charset=utf-8");
        out = response.getOutputStream();
        byte[] buffer = new byte[1024]; // 缓冲区
        int bytesToRead;
        // 通过循环将读入的Excel文件的内容输出到浏览器中
        while ((bytesToRead = inputStream.read(buffer)) != -1) {
            out.write(buffer, 0, bytesToRead);
        }
        out.flush();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (inputStream != null) {
            inputStream.close();
        }
        if (out != null) {
            out.close();
        }
        if (file != null) {
            file.delete();
        }
    }
}
Last modification：December 31st, 2019 at 10:01 am
© 允许规范转载
If you think my article is useful to you, please feel free to appreciate