springmvc下解析模板生成Excel ,用freemarker

工具类解析模板生成Excel

 

public class TemplateParseUtil {
	/** 
     * 解析模板生成Excel 
     * @param templateDir  模板目录 
     * @param templateName 模板名称 
     * @param excelPath 生成的Excel文件路径 
     * @param data 数据参数 
     * @throws IOException 
     * @throws TemplateException 
     */  
    public static void parse(String templateDir,String templateName,String excelPath,Map<String,Object> data) throws IOException, TemplateException {  
        //初始化工作  
        Configuration cfg = new Configuration();  
        //设置默认编码格式为UTF-8  
        cfg.setDefaultEncoding("UTF-8");   
        //全局数字格式  
        cfg.setNumberFormat("0.00");  
        //设置模板文件位置  
        cfg.setDirectoryForTemplateLoading(new File(templateDir));  
        cfg.setObjectWrapper(new DefaultObjectWrapper());  
        //加载模板  
        Template template = cfg.getTemplate(templateName,"utf-8");  
        OutputStreamWriter writer = null;  
        try{  
            //填充数据至Excel  
            writer = new OutputStreamWriter(new FileOutputStream(excelPath),"UTF-8");  
            template.process(data, writer);  
            writer.flush();  
        }finally{  
            writer.close();  
        }     
    }  
  
  
    /** 
     * 解析模板返回字节数组 
     * @param templateDir  模板目录 
     * @param templateName 模板名称 
     * @param data 数据参数 
     * @throws IOException 
     * @throws TemplateException 
     */  
    public static byte[] parse(String templateDir,String templateName,Map<String,Object> data) throws TemplateException, IOException{  
        Configuration cfg = new Configuration();  
        cfg.setDefaultEncoding("UTF-8");  
        cfg.setNumberFormat("0.00");  
        cfg.setDirectoryForTemplateLoading(new File(templateDir));  
        cfg.setObjectWrapper(new DefaultObjectWrapper());  
        Template template = cfg.getTemplate(templateName,"utf-8");  
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();  
        Writer out = new OutputStreamWriter(outStream,"UTF-8");  
        template.process(data, out);  
        return outStream.toByteArray();  
    }  
      
    /** 
     * 自定义模板字符串解析 
     * @param templateStr  模板字符串 
     * @param data 数据   
     * @return 解析后的字符串 
     * @throws IOException 
     * @throws TemplateException 
     */  
    public static String parse(String templateStr, Map<String, Object> data)  
            throws IOException, TemplateException {  
        Configuration cfg = new Configuration();  
        cfg.setNumberFormat("#.##");  
        //设置装载模板  
        StringTemplateLoader stringLoader = new StringTemplateLoader();   
        stringLoader.putTemplate("myTemplate", templateStr);      
        cfg.setTemplateLoader(stringLoader);  
        //加载装载的模板  
        Template temp = cfg.getTemplate("myTemplate", "utf-8");  
        Writer out = new StringWriter();  
        temp.process(data, out);  
        return out.toString();  
    }  
}

模板文件excel.ft

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<?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"
	xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">
	<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
		<Author>mocking</Author>
		<LastAuthor>‎</LastAuthor>
		<Created>2018-05-17T06:23:39Z</Created>
		<LastSaved>2018-05-17T06:31:50Z</LastSaved>
	</DocumentProperties>
	<CustomDocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
		<KSOProductBuildVer dt:dt="string">2052-10.1.0.7346
		</KSOProductBuildVer>
	</CustomDocumentProperties>
	<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
		<WindowWidth>27525</WindowWidth>
		<WindowHeight>13650</WindowHeight>
		<ProtectStructure>False</ProtectStructure>
		<ProtectWindows>False</ProtectWindows>
	</ExcelWorkbook>
	<Styles>
		<Style ss:ID="s40" ss:Name="强调文字颜色 4">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF" />
			<Interior ss:Color="#FFC000" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s35" ss:Name="20% - 强调文字颜色 1">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000" />
			<Interior ss:Color="#DDEBF7" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s32" ss:Name="适中">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#9C6500" />
			<Interior ss:Color="#FFEB9C" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s27" ss:Name="20% - 强调文字颜色 6">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000" />
			<Interior ss:Color="#E2EFDA" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s20" ss:Name="标题 2">
			<Borders>
				<Border ss:Position="Bottom" ss:LineStyle="Continuous"
					ss:Weight="2" ss:Color="#5B9BD5" />
			</Borders>
			<Font ss:FontName="宋体" x:CharSet="134" ss:Size="13" ss:Color="#44546A"
				ss:Bold="1" />
		</Style>
		<Style ss:ID="s16" ss:Name="警告文本">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FF0000" />
		</Style>
		<Style ss:ID="s1" ss:Name="货币[0]">
			<NumberFormat
				ss:Format="_ "¥"* #,##0_ ;_ "¥"* \-#,##0_ ;_ "¥"* "-"_ ;_ @_ " />
		</Style>
		<Style ss:ID="s8" ss:Name="千位分隔">
			<NumberFormat
				ss:Format="_ * #,##0.00_ ;_ * \-#,##0.00_ ;_ * "-"??_ ;_ @_ " />
		</Style>
		<Style ss:ID="s43" ss:Name="强调文字颜色 5">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF" />
			<Interior ss:Color="#4472C4" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s31" ss:Name="好">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#006100" />
			<Interior ss:Color="#C6EFCE" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s14" ss:Name="60% - 强调文字颜色 2">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF" />
			<Interior ss:Color="#F4B084" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s9" ss:Name="60% - 强调文字颜色 3">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF" />
			<Interior ss:Color="#C9C9C9" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s7" ss:Name="差">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#9C0006" />
			<Interior ss:Color="#FFC7CE" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s48" ss:Name="60% - 强调文字颜色 6">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF" />
			<Interior ss:Color="#A9D08E" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s33" ss:Name="20% - 强调文字颜色 5">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000" />
			<Interior ss:Color="#D9E1F2" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s30" ss:Name="汇总">
			<Borders>
				<Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3"
					ss:Color="#5B9BD5" />
				<Border ss:Position="Top" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#5B9BD5" />
			</Borders>
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"
				ss:Bold="1" />
		</Style>
		<Style ss:ID="s6" ss:Name="40% - 强调文字颜色 3">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000" />
			<Interior ss:Color="#DBDBDB" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s44" ss:Name="40% - 强调文字颜色 5">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000" />
			<Interior ss:Color="#B4C6E7" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s25" ss:Name="计算">
			<Borders>
				<Border ss:Position="Bottom" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#7F7F7F" />
				<Border ss:Position="Left" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#7F7F7F" />
				<Border ss:Position="Right" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#7F7F7F" />
				<Border ss:Position="Top" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#7F7F7F" />
			</Borders>
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FA7D00"
				ss:Bold="1" />
			<Interior ss:Color="#F2F2F2" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s19" ss:Name="标题 1">
			<Borders>
				<Border ss:Position="Bottom" ss:LineStyle="Continuous"
					ss:Weight="2" ss:Color="#5B9BD5" />
			</Borders>
			<Font ss:FontName="宋体" x:CharSet="134" ss:Size="15" ss:Color="#44546A"
				ss:Bold="1" />
		</Style>
		<Style ss:ID="s13" ss:Name="注释">
			<Borders>
				<Border ss:Position="Bottom" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#B2B2B2" />
				<Border ss:Position="Left" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#B2B2B2" />
				<Border ss:Position="Right" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#B2B2B2" />
				<Border ss:Position="Top" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#B2B2B2" />
			</Borders>
			<Interior ss:Color="#FFFFCC" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="Default" ss:Name="Normal">
			<Alignment />
			<Borders />
			<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" />
			<Interior />
			<NumberFormat />
			<Protection />
		</Style>
		<Style ss:ID="s2" ss:Name="20% - 强调文字颜色 3">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000" />
			<Interior ss:Color="#EDEDED" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s5" ss:Name="千位分隔[0]">
			<NumberFormat ss:Format="_ * #,##0_ ;_ * \-#,##0_ ;_ * "-"_ ;_ @_ " />
		</Style>
		<Style ss:ID="s46" ss:Name="强调文字颜色 6">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF" />
			<Interior ss:Color="#70AD47" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s45" ss:Name="60% - 强调文字颜色 5">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF" />
			<Interior ss:Color="#8EA9DB" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s37" ss:Name="20% - 强调文字颜色 2">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000" />
			<Interior ss:Color="#FCE4D6" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s34" ss:Name="强调文字颜色 1">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF" />
			<Interior ss:Color="#5B9BD5" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s26" ss:Name="检查单元格">
			<Borders>
				<Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3"
					ss:Color="#3F3F3F" />
				<Border ss:Position="Left" ss:LineStyle="Double" ss:Weight="3"
					ss:Color="#3F3F3F" />
				<Border ss:Position="Right" ss:LineStyle="Double" ss:Weight="3"
					ss:Color="#3F3F3F" />
				<Border ss:Position="Top" ss:LineStyle="Double" ss:Weight="3"
					ss:Color="#3F3F3F" />
			</Borders>
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"
				ss:Bold="1" />
			<Interior ss:Color="#A5A5A5" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s22" ss:Name="标题 3">
			<Borders>
				<Border ss:Position="Bottom" ss:LineStyle="Continuous"
					ss:Weight="2" ss:Color="#ACCCEA" />
			</Borders>
			<Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#44546A"
				ss:Bold="1" />
		</Style>
		<Style ss:ID="s17" ss:Name="标题">
			<Font ss:FontName="宋体" x:CharSet="134" ss:Size="18" ss:Color="#44546A"
				ss:Bold="1" />
		</Style>
		<Style ss:ID="s10" ss:Name="超链接">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#0000FF"
				ss:Underline="Single" />
		</Style>
		<Style ss:ID="s4" ss:Name="货币">
			<NumberFormat
				ss:Format="_ "¥"* #,##0.00_ ;_ "¥"* \-#,##0.00_ ;_ "¥"* "-"??_ ;_ @_ " />
		</Style>
		<Style ss:ID="s47" ss:Name="40% - 强调文字颜色 6">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000" />
			<Interior ss:Color="#C6E0B4" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s41" ss:Name="20% - 强调文字颜色 4">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000" />
			<Interior ss:Color="#FFF2CC" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s38" ss:Name="40% - 强调文字颜色 2">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000" />
			<Interior ss:Color="#F8CBAD" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s3" ss:Name="输入">
			<Borders>
				<Border ss:Position="Bottom" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#7F7F7F" />
				<Border ss:Position="Left" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#7F7F7F" />
				<Border ss:Position="Right" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#7F7F7F" />
				<Border ss:Position="Top" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#7F7F7F" />
			</Borders>
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#3F3F76" />
			<Interior ss:Color="#FFCC99" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s39" ss:Name="强调文字颜色 3">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF" />
			<Interior ss:Color="#A5A5A5" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s24" ss:Name="输出">
			<Borders>
				<Border ss:Position="Bottom" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#3F3F3F" />
				<Border ss:Position="Left" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#3F3F3F" />
				<Border ss:Position="Right" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#3F3F3F" />
				<Border ss:Position="Top" ss:LineStyle="Continuous"
					ss:Weight="1" ss:Color="#3F3F3F" />
			</Borders>
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#3F3F3F"
				ss:Bold="1" />
			<Interior ss:Color="#F2F2F2" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s23" ss:Name="60% - 强调文字颜色 4">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF" />
			<Interior ss:Color="#FFD966" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s21" ss:Name="60% - 强调文字颜色 1">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF" />
			<Interior ss:Color="#9BC2E6" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s18" ss:Name="解释性文本">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#7F7F7F"
				ss:Italic="1" />
		</Style>
		<Style ss:ID="s15" ss:Name="标题 4">
			<Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#44546A"
				ss:Bold="1" />
		</Style>
		<Style ss:ID="s12" ss:Name="已访问的超链接">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#800080"
				ss:Underline="Single" />
		</Style>
		<Style ss:ID="s11" ss:Name="百分比">
			<NumberFormat ss:Format="0%" />
		</Style>
		<Style ss:ID="s42" ss:Name="40% - 强调文字颜色 4">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000" />
			<Interior ss:Color="#FFE699" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s36" ss:Name="40% - 强调文字颜色 1">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000" />
			<Interior ss:Color="#BDD7EE" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s29" ss:Name="链接单元格">
			<Borders>
				<Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3"
					ss:Color="#FF8001" />
			</Borders>
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FA7D00" />
		</Style>
		<Style ss:ID="s28" ss:Name="强调文字颜色 2">
			<Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF" />
			<Interior ss:Color="#ED7D31" ss:Pattern="Solid" />
		</Style>
		<Style ss:ID="s49" />
		<Style ss:ID="s50">
			<Alignment ss:Horizontal="Center" />
		</Style>
	</Styles>
	<Worksheet ss:Name="Sheet1">
		<Table ss:ExpandedColumnCount="8" ss:ExpandedRowCount="${rsvCountList?size + 2}"
			x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54"
			ss:DefaultRowHeight="14.25">
			<Row>
				<Cell ss:StyleID="s50">
					<Data ss:Type="String">所在市</Data>
				</Cell>
				<Cell ss:StyleID="s50">
					<Data ss:Type="String">大(1)型</Data>
				</Cell>
				<Cell ss:StyleID="s50">
					<Data ss:Type="String">大(2)型</Data>
				</Cell>
				<Cell ss:StyleID="s50">
					<Data ss:Type="String">中型</Data>
				</Cell>
				<Cell ss:StyleID="s50">
					<Data ss:Type="String">小(1)型</Data>
				</Cell>
				 <Cell ss:StyleID="s50">
					<Data ss:Type="String">小(2)型</Data>
				</Cell>
				<Cell ss:StyleID="s50">
					<Data ss:Type="String">合计</Data>
				</Cell>
			</Row>
			
			 <#list rsvCountList as rsv>  
   			 
   			 <Row ss:Index="${rsv_index+2}">  

			    <Cell><Data ss:Type="String">${rsv.name!}</Data></Cell>
			    <#list rsv.countList as c> 
				    <Cell><Data ss:Type="String">${c.count!}</Data></Cell>  
			 	</#list>
			 	<Cell><Data ss:Type="String">${rsv.count!}</Data></Cell>
			 </Row>
			 </#list>  
		</Table>
		<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
			<PageSetup>
				<Header x:Margin="0.511111111111111" />
				<Footer x:Margin="0.511111111111111" />
			</PageSetup>
			<Selected />
			<TopRowVisible>0</TopRowVisible>
			<LeftColumnVisible>0</LeftColumnVisible>
			<PageBreakZoom>100</PageBreakZoom>
			<Panes>
				<Pane>
					<Number>3</Number>
					<ActiveRow>0</ActiveRow>
					<ActiveCol>7</ActiveCol>
					<RangeSelection>R1C8</RangeSelection>
				</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" />
		<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
			<PageSetup>
				<Header x:Margin="0.511111111111111" />
				<Footer x:Margin="0.511111111111111" />
			</PageSetup>
			<TopRowVisible>0</TopRowVisible>
			<LeftColumnVisible>0</LeftColumnVisible>
			<PageBreakZoom>100</PageBreakZoom>
			<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" />
		<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
			<PageSetup>
				<Header x:Margin="0.511111111111111" />
				<Footer x:Margin="0.511111111111111" />
			</PageSetup>
			<TopRowVisible>0</TopRowVisible>
			<LeftColumnVisible>0</LeftColumnVisible>
			<PageBreakZoom>100</PageBreakZoom>
			<ProtectObjects>False</ProtectObjects>
			<ProtectScenarios>False</ProtectScenarios>
		</WorksheetOptions>
	</Worksheet>
</Workbook> ss:ExpandedRowCount="${rsvCountList?size + 2}"
			x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54"
			ss:DefaultRowHeight="14.25">
			<Row>
				<Cell ss:StyleID="s50">
					<Data ss:Type="String">所在市</Data>
				</Cell>
				<Cell ss:StyleID="s50">
					<Data ss:Type="String">大(1)型</Data>
				</Cell>
				<Cell ss:StyleID="s50">
					<Data ss:Type="String">大(2)型</Data>
				</Cell>
				<Cell ss:StyleID="s50">
					<Data ss:Type="String">中型</Data>
				</Cell>
				<Cell ss:StyleID="s50">
					<Data ss:Type="String">小(1)型</Data>
				</Cell>
				 <Cell ss:StyleID="s50">
					<Data ss:Type="String">小(2)型</Data>
				</Cell>
				<Cell ss:StyleID="s50">
					<Data ss:Type="String">合计</Data>
				</Cell>
			</Row>
			
			 <#list rsvCountList as rsv>  
   			 
   			 <Row ss:Index="${rsv_index+2}">  

			    <Cell><Data ss:Type="String">${rsv.name!}</Data></Cell>
			    <#list rsv.countList as c> 
				    <Cell><Data ss:Type="String">${c.count!}</Data></Cell>  
			 	</#list>
			 	<Cell><Data ss:Type="String">${rsv.count!}</Data></Cell>
			 </Row>
			 </#list>  
		</Table>
		<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
			<PageSetup>
				<Header x:Margin="0.511111111111111" />
				<Footer x:Margin="0.511111111111111" />
			</PageSetup>
			<Selected />
			<TopRowVisible>0</TopRowVisible>
			<LeftColumnVisible>0</LeftColumnVisible>
			<PageBreakZoom>100</PageBreakZoom>
			<Panes>
				<Pane>
					<Number>3</Number>
					<ActiveRow>0</ActiveRow>
					<ActiveCol>7</ActiveCol>
					<RangeSelection>R1C8</RangeSelection>
				</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" />
		<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
			<PageSetup>
				<Header x:Margin="0.511111111111111" />
				<Footer x:Margin="0.511111111111111" />
			</PageSetup>
			<TopRowVisible>0</TopRowVisible>
			<LeftColumnVisible>0</LeftColumnVisible>
			<PageBreakZoom>100</PageBreakZoom>
			<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" />
		<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
			<PageSetup>
				<Header x:Margin="0.511111111111111" />
				<Footer x:Margin="0.511111111111111" />
			</PageSetup>
			<TopRowVisible>0</TopRowVisible>
			<LeftColumnVisible>0</LeftColumnVisible>
			<PageBreakZoom>100</PageBreakZoom>
			<ProtectObjects>False</ProtectObjects>
			<ProtectScenarios>False</ProtectScenarios>
		</WorksheetOptions>
	</Worksheet>
</Workbook> 
 

一定要注意标红处,

ss:ExpandedRowCount="${rsvCountList?size + 2}

注:这里一定要改,因为这里设置的是excel的行数,要动态的加载数据,则行数也要跟着改变(设置的行数必须>=实际的行数,不然生成的excel会打不开),${RsvCountList?size + 6}是得到userList的大小加上原来标题格式所占的6行,${list?size}可以得到list的大小。

 

<#list rsvCountList as rsv>  
   			 
   			 <Row ss:Index="${rsv_index+2}">  

			    <Cell><Data ss:Type="String">${rsv.name!}</Data></Cell>
			    <#list rsv.countList as c> 
				    <Cell><Data ss:Type="String">${c.count!}</Data></Cell>  
			 	</#list>
			 	<Cell><Data ss:Type="String">${rsv.count!}</Data></Cell>
			 </Row>
			 </#list>   

			    <Cell><Data ss:Type="String">${rsv.name!}</Data></Cell>
			    <#list rsv.countList as c> 
				    <Cell><Data ss:Type="String">${c.count!}</Data></Cell>  
			 	</#list>
			 	<Cell><Data ss:Type="String">${rsv.count!}</Data></Cell>
			 </Row>
			 </#list> 

循环遍历 RsvCountList的标签(内部又有list遍历)

public void excelTest(HttpServletResponse response,HttpServletRequest request) throws IOException{
List<RsvCountList> rsvCountList = GetRsvCanton();
//测试Excel文件生成
Map<String,Object> data = new HashMap<String, Object>();
data.put("rsvCountList", rsvCountList);
Date now = new Date();
String filename = "/resources/TempFiles/excel"+now.getTime()+".xls";
String nodepath = this.getClass().getClassLoader().getResource("/").getPath(); 
String substring = nodepath+"../../";
String path2=substring+"/resources/template";

try {
TemplateParseUtil.parse(path2, "excel.ftl", substring+filename, data);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (TemplateException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
response.sendRedirect("./"+filename);
   } 

-------------------修改版-------------------------

service and impl

/*
*    service
*/

import java.io.IOException;
import java.util.Map;

import freemarker.template.TemplateException;

public interface IFreemarkerService {
	byte[] parse(String fileName, Map<String,Object> data) throws IOException, TemplateException;

}


/*
*    serviceimpl
*/

import java.io.ByteArrayOutputStream;
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 org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import org.springframework.web.servlet.view.freemarker.FreeMarkerConfigurer;

import freemarker.template.Configuration;
import freemarker.template.DefaultObjectWrapper;
import freemarker.template.Template;
import freemarker.template.TemplateException;

@Service
public class FreemarkerService implements IFreemarkerService {

	@Autowired
	@Qualifier("freemarkerConfig")
	private FreeMarkerConfigurer freemarkerConfig;//来自org.springframework.web.servlet.view.freemarker;
	@Override
	public byte[] parse(String fileName, Map<String, Object> data) throws IOException, TemplateException {
		Configuration cfg = freemarkerConfig.getConfiguration();
		cfg.setDefaultEncoding("UTF-8");  
        cfg.setNumberFormat("0.00");          
        cfg.setObjectWrapper(new DefaultObjectWrapper());        
        //cfg.setDirectoryForTemplateLoading(new File(freemarkerConfig.));
        Template template = cfg.getTemplate(fileName,"utf-8");  
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();  
        Writer out = new OutputStreamWriter(outStream,"UTF-8");  
        template.process(data, out);  
        return outStream.toByteArray(); 		
	}

    public void parseTest(String templateName,String excelPath,Map<String,Object> data) throws IOException, TemplateException {  
        //初始化工作  
		Configuration cfg = freemarkerConfig.getConfiguration();
        //设置默认编码格式为UTF-8  
        cfg.setDefaultEncoding("UTF-8");   
        //全局数字格式  
        cfg.setNumberFormat("0.00"); 
        
        cfg.setDirectoryForTemplateLoading(new File("d:/tmp/template/"));  

        cfg.setObjectWrapper(new DefaultObjectWrapper());  
        //加载模板  
        Template template = cfg.getTemplate(templateName,"utf-8");  
        OutputStreamWriter writer = null;  
        try{  
            //填充数据至Excel  
            writer = new OutputStreamWriter(new FileOutputStream(excelPath),"UTF-8");  
            template.process(data, writer);  
            writer.flush();  
        }finally{  
            writer.close();  
        }     
    }  
}
//Spring配置文件
<!-- freemaker配置 -->
	<bean id="freemarkerConfig"
		class="org.springframework.web.servlet.view.freemarker.FreeMarkerConfigurer">
        <!-- webapp下 -->
		<property name="templateLoaderPath" value="/resources/template" />
		<property name="freemarkerSettings">
			<props>
				<prop key="template_update_delay">0</prop>
				<prop key="default_encoding">UTF-8</prop>
				<prop key="number_format">0.##########</prop>
				<prop key="datetime_format">yyyy-MM-dd HH:mm:ss</prop>
				<prop key="classic_compatible">true</prop>
				<prop key="template_exception_handler">ignore</prop>
			</props>
		</property>
	</bean>

	<bean id="viewResolver"
		class="org.springframework.web.servlet.view.freemarker.FreeMarkerViewResolver">
		<property name="viewClass"
			value="org.springframework.web.servlet.view.freemarker.FreeMarkerView" />
		<property name="suffix" value=".html" />
		<property name="contentType" value="text/html; charset=utf-8" />
		<property name="cache" value="true" />
	</bean>
@RequestMapping(value = "hztjExcel", method = RequestMethod.GET)
	public void hztjExcel(HttpServletResponse response,HttpServletRequest request,ReservoirFilterDto rsvDto) throws IOException{
		
		List<ReservoirStatisticsInfo> reservoirStatisticsCountList = service.ReservoirStatisticsCount(rsvDto);
		
		Map<String,Object> data = new HashMap<String, Object>();

		//将DTO对象放入excelMap中
		data.put("statistics", reservoirStatisticsCountList.get(0));
		
		
		Date now = new Date();
		String filename = "/resources/TempFiles/hztj"+now.getTime()+".xls";
		String nodepath = this.getClass().getClassLoader().getResource("/").getPath(); 
		String substring = nodepath+"../../";
		String path2=substring+"/resources/template";

		OutputStream outputStream = null;
		try {
			//TemplateParseUtil.parse(path2, "jszbtj.ftl", substring+filename, data);
			byte[] content = freemarkerService.parse("jszbtj.ftl",data);
			response.setContentType("application/octet-stream;charset=UTF-8");
			String userAgent = request.getHeader("user-agent").toLowerCase();
			if(userAgent != null &&((userAgent.indexOf("msie")!=-1) || (userAgent.indexOf("rv") != -1 && userAgent.indexOf("firefox") == -1))
					&& ((userAgent.indexOf("msie") != -1 ||(userAgent.indexOf("rv")!=-1 && userAgent.indexOf("chrom") == -1)))){
				//识别IE浏览器
				filename = URLEncoder.encode(filename, "UTF-8");
			} else {
				//非IE浏览器
				filename = new String(filename.getBytes("UTF-8"), "iso-8859-1");
			}
			response.setHeader("Content-Disposition", "attachment; filename=\"" + filename + "\"");
			response.addHeader("Content-Length", String.valueOf(content.length));
			outputStream = new BufferedOutputStream(response.getOutputStream());
			outputStream.write(content);
			outputStream.flush();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (TemplateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//response.sendRedirect("../"+filename);
   } 

导出其它格式文件参考https://blog.csdn.net/u010722643/article/details/41732607

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值