java使用freemarker作为模板导出Excel表格

水深无声 2022-04-15 00:25 370阅读 0赞

1：首先新建一个excel表格自己弄好格式如下图

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hleHVfYmxvZw_size_16_color_FFFFFF_t_70][]

2：把excel 表格另存为xml格式文件如下图

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hleHVfYmxvZw_size_16_color_FFFFFF_t_70 1][]

3：这个时候的文件就是xml 格式的文件了,在eclipse里面项目工程里面新建一个文件后缀为.ftl 然后把弄好的xml文件内容直接复制粘贴到.ftl文件里面

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hleHVfYmxvZw_size_16_color_FFFFFF_t_70 2][]

4.看一下js代码

//导出备份日志报表  
    export\_backup\_log\_report:function(url)  
    \{  
        var startime=$("\#time1").val();  
        var endtime=$("\#time2").val();  
        if(confirm("确定要导出？"))\{  
            $.ajax(\{  
                type : "post",// 指定是post还是get encodeURI()  
                url : ""+url+"?startime="+startime  
                +"&endtime="+endtime,// 发送请求的地址  
                async : true, // 默认为true为异步,false为同步  
                dataType : "json",  
                contentType:'application/x-www-form-urlencoded; charset=UTF-8',  
                beforeSend: function (XMLHttpRequest) \{  
                    XMLHttpRequest.setRequestHeader("Accept", "application/json");  
                \},  
                error : function(err) \{// 如果确定能正确运行,可不写  
                    alert("服务器正忙,请稍后再试!");  
                    top.location ="logout";  
                \},  
                success : function(result)\{  
                    var status=result.status;  
                    var msg=result.msg;  
                    if(status==0)\{  
                        var report\_export\_url=result.data.report\_export\_url;  
                        var url=report\_export\_url+"备份日志报表.xls";  
                        window.open(url);  
                    \}else  
                    \{  
                        alert(msg);  
                    \}  
                \}  
            \});  
        \}  
    \},

5.看一下java代码

//导出备份日志报表  
    @RequestMapping(value="/admin/backupLogReport",method=\{RequestMethod.GET,RequestMethod.POST\})  
    @ResponseBody  
    public WebResult backupLogReport(HttpServletRequest request,HttpServletResponse response,  
            @RequestParam(value="page",defaultValue="0") int page,  
            @RequestParam(value="searchvalue" ,defaultValue="") String searchvalue,  
            @RequestParam(value = "startime", defaultValue = "") String startime,  
            @RequestParam(value = "endtime", defaultValue = "") String endtime) throws Exception\{  
          
        response.setContentType("text/html;charset=UTF-8");     
        request.setCharacterEncoding("UTF-8");    
        String fileName ="备份日志报表.xls";    
          
        List<TblLogsBk> list=null;  
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");  
          
        String templateDir = request.getSession().getServletContext().getRealPath("/")+"template";//模板路径   
        String excelPath = request.getSession().getServletContext().getRealPath("/")+"tempfile";//导出地址  
        try\{  
            //初始化时间    
            if(StringUtils.isEmpty(startime) || StringUtils.isEmpty(endtime))  
            \{  
                startime=WebUtil.formatBeginDate();  
                endtime=sdf.format(WebUtil.getDateAfter(new Date(), 1));  
            \}  
            list=baseDao.getListByLike(TblLogsBk.class, "bktime",sdf.parse(startime), sdf.parse(endtime), "id", "desc", 0,10000);  
            for(TblLogsBk log:list)  
            \{  
                // 1.手动 2.自动  
                if(log.getBktype().equals("1")) log.setBktype("手动");  
                if(log.getBktype().equals("2")) log.setBktype("自动");  
            \}  
            if(list!=null && list.size()>0)  
            \{  
                if(list.size()>10000) return WebResult.error("最多导出10000条记录!", null); Map<String, Object> dataMap = new HashMap<String, Object>();  
                dataMap.put("backupLoglist",list);  
                dataMap.put("totalrecord",list.size()+"条");  
                TemplateParseUtil.parse(templateDir, "backuplog.ftl",excelPath+"/"+fileName,dataMap);  
                  
                String report\_export\_url = ReadResource.report\_export\_url;//下载地址  
                HashMap<String,Object> valuemap=WebUtil.getHashMap();  
                valuemap.put("report\_export\_url",report\_export\_url);  
                return WebResult.success("导出成功",valuemap);    
            \}else  
            \{  
                return WebResult.error("没有可导数据!", null);    
            \}  
        \}catch(Exception e)  
        \{  
            e.printStackTrace();  
        \}  
          
        return WebResult.error("导出失败", null);   
    \}

6.自己去设置下载地址

![20181119151816367.png][]

7.以下是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>Administrator</Author>  
  <LastAuthor>Administrator</LastAuthor>  
  <Created>2008-09-11T17:22:52Z</Created>  
  <LastSaved>2018-11-19T06:57:23Z</LastSaved>  
  <Version>12.00</Version>  
 </DocumentProperties>  
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">  
  <WindowHeight>5715</WindowHeight>  
  <WindowWidth>12765</WindowWidth>  
  <WindowTopX>0</WindowTopX>  
  <WindowTopY>105</WindowTopY>  
  <ProtectStructure>False</ProtectStructure>  
  <ProtectWindows>False</ProtectWindows>  
 </ExcelWorkbook>  
 <Styles>  
  <Style ss:ID="Default" ss:Name="Normal">  
   <Alignment ss:Vertical="Bottom"/>  
   <Borders/>  
   <Font ss:FontName="Tahoma" x:Family="Swiss" ss:Size="11" ss:Color="\#000000"/>  
   <Interior/>  
   <NumberFormat/>  
   <Protection/>  
  </Style>  
  <Style ss:ID="s56" ss:Name="常规 2">  
   <Alignment ss:Vertical="Center"/>  
   <Borders/>  
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="\#000000"/>  
   <Interior/>  
   <NumberFormat/>  
   <Protection/>  
  </Style>  
  <Style ss:ID="m43397792">  
   <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>  
   <Borders>  
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>  
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>  
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>  
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>  
   </Borders>  
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="18" ss:Color="\#000000"  
    ss:Bold="1"/>  
   <Interior ss:Color="\#C5D9F1" ss:Pattern="Solid"/>  
  </Style>  
  <Style ss:ID="s58">  
   <Borders>  
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>  
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>  
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>  
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>  
   </Borders>  
  </Style>  
  <Style ss:ID="s59" ss:Parent="s56">  
   <Borders>  
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>  
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>  
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>  
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>  
   </Borders>  
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="\#000000"  
    ss:Bold="1"/>  
   <Interior ss:Color="\#00B050" ss:Pattern="Solid"/>  
  </Style>  
 </Styles>  
 <Worksheet ss:Name="Sheet1">  
  <Table ss:ExpandedColumnCount="8" ss:ExpandedRowCount="$\{backupLoglist?size+3\}" x:FullColumns="1"  
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">  
   <Column ss:AutoFitWidth="0" ss:Width="93.75" ss:Span="3"/>  
   <Column ss:Index="5" ss:AutoFitWidth="0" ss:Width="142.5"/>  
   <Column ss:AutoFitWidth="0" ss:Width="159.75"/>  
   <Column ss:AutoFitWidth="0" ss:Width="110.25"/>  
   <Column ss:AutoFitWidth="0" ss:Width="159"/>  
   <Row ss:AutoFitHeight="0" ss:Height="30">  
    <Cell ss:MergeAcross="7" ss:StyleID="m43397792"><Data ss:Type="String">备份日志报表</Data></Cell>  
   </Row>  
   <Row ss:AutoFitHeight="0" ss:Height="20.0625">  
    <Cell ss:StyleID="s59"><Data ss:Type="String">IP</Data></Cell>  
    <Cell ss:StyleID="s59"><Data ss:Type="String">备份类型</Data></Cell>  
    <Cell ss:StyleID="s59"><Data ss:Type="String">端口号</Data></Cell>  
    <Cell ss:StyleID="s59"><Data ss:Type="String">用户名</Data></Cell>  
    <Cell ss:StyleID="s59"><Data ss:Type="String">保存路径</Data></Cell>  
    <Cell ss:StyleID="s59"><Data ss:Type="String">文件名</Data></Cell>  
    <Cell ss:StyleID="s59"><Data ss:Type="String">数据库名</Data></Cell>  
    <Cell ss:StyleID="s59"><Data ss:Type="String">备份时间</Data></Cell>  
   </Row>  
  <\#list backupLoglist as backupqust>  
   <Row ss:AutoFitHeight="0" ss:Height="20.0625">  
    <Cell ss:StyleID="s58"><Data ss:Type="String">$\{backupqust.serverip!\}</Data></Cell>  
    <Cell ss:StyleID="s58"><Data ss:Type="String">$\{backupqust.bktype!\}</Data></Cell>  
    <Cell ss:StyleID="s58"><Data ss:Type="String">$\{backupqust.portnum!\}</Data></Cell>  
    <Cell ss:StyleID="s58"><Data ss:Type="String">$\{backupqust.username!\}</Data></Cell>  
    <Cell ss:StyleID="s58"><Data ss:Type="String">$\{backupqust.savepath!\}</Data></Cell>  
    <Cell ss:StyleID="s58"><Data ss:Type="String">$\{backupqust.filename!\}</Data></Cell>  
    <Cell ss:StyleID="s58"><Data ss:Type="String">$\{backupqust.databasename!\}</Data></Cell>  
    <Cell ss:StyleID="s58"><Data ss:Type="String">$\{backupqust.bktime!\}</Data></Cell>  
   </Row>  
   </\#list>  
  </Table>  
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">  
   <PageSetup>  
    <Header x:Margin="0.3"/>  
    <Footer x:Margin="0.3"/>  
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>  
   </PageSetup>  
   <Print>  
    <ValidPrinterInfo/>  
    <PaperSizeIndex>9</PaperSizeIndex>  
    <HorizontalResolution>600</HorizontalResolution>  
    <VerticalResolution>600</VerticalResolution>  
   </Print>  
   <Selected/>  
   <Panes>  
    <Pane>  
     <Number>3</Number>  
     <ActiveRow>3</ActiveRow>  
     <ActiveCol>4</ActiveCol>  
    </Pane>  
   </Panes>  
   <ProtectObjects>False</ProtectObjects>  
   <ProtectScenarios>False</ProtectScenarios>  
  </WorksheetOptions>  
 </Worksheet>  
 <Worksheet ss:Name="Sheet2">  
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"  
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">  
  </Table>  
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">  
   <PageSetup>  
    <Header x:Margin="0.3"/>  
    <Footer x:Margin="0.3"/>  
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>  
   </PageSetup>  
   <ProtectObjects>False</ProtectObjects>  
   <ProtectScenarios>False</ProtectScenarios>  
  </WorksheetOptions>  
 </Worksheet>  
 <Worksheet ss:Name="Sheet3">  
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"  
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">  
  </Table>  
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">  
   <PageSetup>  
    <Header x:Margin="0.3"/>  
    <Footer x:Margin="0.3"/>  
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>  
   </PageSetup>  
   <ProtectObjects>False</ProtectObjects>  
   <ProtectScenarios>False</ProtectScenarios>  
  </WorksheetOptions>  
 </Worksheet>  
</Workbook>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hleHVfYmxvZw_size_16_color_FFFFFF_t_70]: /images/20220415/f59cfd327d3940eb909a108ee1968e21.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hleHVfYmxvZw_size_16_color_FFFFFF_t_70 1]: /images/20220415/b2acf01bd726478eac13b9ee51fb9e0f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hleHVfYmxvZw_size_16_color_FFFFFF_t_70 2]: /images/20220415/92f6d7631b6040fba366d98675687d76.png
[20181119151816367.png]: /images/20220415/f0b6e26fa86d4c92ac911229af39eec1.png