springMVC和FreeMarker简单方式导出excel

最新推荐文章于 2023-09-24 13:10:25 发布

dangerous_fire

最新推荐文章于 2023-09-24 13:10:25 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/dangerous_fire/article/details/69276383

本文介绍如何利用Spring MVC和FreeMarker框架通过输出HTML格式的XLS文件实现Excel导出功能。该方法适用于仅需导出数据而不涉及复杂Excel格式化的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一般Java操作excel文件比较流行的是apache poi包。

因为我们只是导出数据，不需要读取正规的excel文件，所以我们可以通过输出html格式的xls文件来简单实现导出excel。

环境使用的springMVC,展示使用的是FreeMarker

Action

        @RequestMapping(value="export")
	public String sfnExport(HttpServletRequest request,HttpServletResponse response){
		String export_type = request.getParameter("export_type");
		request.setAttribute("export_type", export_type);
		
		Map<String,Integer> mapYearMonth =  setYearMonth(request);
		
		List<ProjSatisfaction> dataFrom = projSatisfactionService.findProjects(mapYearMonth.get("year"), mapYearMonth.get("month"), false);
		List<ProjSatisfaction> dataTo = projSatisfactionService.findProjects(mapYearMonth.get("year"), mapYearMonth.get("month"), true);
		
		request.setAttribute("dataFromList", dataFrom);
		request.setAttribute("dataToList", dataTo);
		// 获取统计信息
		request.setAttribute("sum", projSatisfactionService.getStatistics(mapYearMonth.get("year"), mapYearMonth.get("month")));
		
		// 导出xls类型的html文件
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String export_date = sdf.format(new Date());
		request.setAttribute("export_date", export_date);
		String fileName = mapYearMonth.get("year")+"年"+ mapYearMonth.get("month")+"月服务评价统计_"+export_date+".xls";
		response.setContentType("application/x-download");
		response.setCharacterEncoding("UTF-8");
		response.addHeader("Content-Disposition", "attachment;filename=" + StringHelper.convertTitle(fileName));
		return "/sfn/export";
	}

ftl

<html xmlns:v="urn:schemas-microsoft-com:vml"
	xmlns:o="urn:schemas-microsoft-com:office:office"
	xmlns:x="urn:schemas-microsoft-com:office:excel"
	xmlns="http://www.w3.org/TR/REC-html40">
<html>
<head>
<title>导出服务评价结果</title>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="ProgId" content="Excel.Sheet" />

<style>
body {
	font-family: "微软雅黑 宋体";
	font-size: 14px;
}

table {
	border: 0.5pt solid #888;
}

table td,table th {
	border: 0.5pt solid #888;
}

.align_center {
	text-align: center;
}
.noborder{
	border:none;
}
</style>

</head>

<body>
	<table id="table_main" >
		<col width="39"  style='mso-width-source:userset;mso-width-alt:1300;'/>
   		<col width="300"  style='mso-width-source:userset;mso-width-alt:6000;'/>
   		<col width="112"  style='mso-width-source:userset;mso-width-alt:6000;'/>
   		<col width="67"  style='mso-width-source:userset;mso-width-alt:2500;'/>
   		<col width="39" style='mso-width-source:userset;mso-width-alt:1300;'/>
   		<col width="53" style='mso-width-source:userset;mso-width-alt:1696;'/>
   		<col width="72" style='mso-width-source:userset;mso-width-alt:2304;'/>
		<!-- 1st line -->
		<tr>
			<td class="align_center" colspan="7">
				<h3>服务评价结果</h3>
			</td>
		</tr>
		<tr>
			<td class="align_center" colspan="3">
				数据时间:${select_year}年${select_month}月
			</td>
			<td class="align_center" colspan="4">
				导出时间:${export_date}
			</td>
		</tr>

		<!-- 2nd line -->
		<tr>
			<td class="align_center" colspan="7">
				<h4>统计信息</h4>
			</td>
		</tr>
		<tr>
			<th>总数</th>
			<th>未审核项目数</th>
			<th>已审核项目数</th>
			<th style="color:#FF00FF;">非常满意</th>
			<th style="color:#0000FF;">满意</th>
			<th style="color:#008000;">一般</th>
			<th style="color:#FF0000;">不满意</th>
		</tr>
		<tr>
			<td class="align_center">${sum["all"]}</td>
			<td class="align_center">${sum["normal"]}</td>
			<td class="align_center">${sum["approve"]}</td>
			<td class="align_center" style="color:#FF00FF;">${sum["level1"]}</td>
			<td class="align_center" style="color:#0000FF;">${sum["level2"]}</td>
			<td class="align_center" style="color:#008000;">${sum["level3"]}</td>
			<td class="align_center" style="color:#FF0000;">${sum["level4"]}</td>
		</tr>
		<!-- 3rd line -->
		<tr>
			<td class="align_center" colspan="7">
				<h4>未审核项目评价 <#if export_type="_3_4_"> (一般与不满意) </#if></h4></td>
		</tr>
		<tr>
			<th>编号</th>
			<th>未审核项目编码</th>
			<th>未审核项目名称</th>
			<th>审核结果</th>
			<td colspan="3" class="noborder"></td>
		</tr>
		<#assign index = 1> 
		<#list dataFromList as data>
		<#if data.assesslevel == 1>
			<#assign levelColor="#FF00FF">
		<#elseif data.assesslevel == 2>
			<#assign levelColor="#0000FF">
		<#elseif data.assesslevel == 3>
			<#assign levelColor="#008000">
		<#elseif data.assesslevel == 4>
			<#assign levelColor="#FF0000">
		</#if> 
		<#if export_type == "all" || export_type?contains(data.assesslevel+"")>
		<tr>
			<td x:num>${index}</td>
			<td x:str>${data.projectId}</td>
			<td x:str>${data.projectName}</td>
			<td class="align_center" style="color:${levelColor};" x:str>${data.assessResult}</td>
			<td colspan="3" class="noborder"></td>
		</tr>
		<#assign index=index+1> 
		</#if> 
		</#list>
		
		<!-- 4th line -->
		<tr class="align_center">
			<td colspan="7">
				<h4>已审核项目评价 <#if export_type="_3_4_"> (一般与不满意) </#if></h4>
			</td>
		</tr>
		<tr>
			<th>编号</th>
			<th>已审核项目编码</th>
			<th>已审核项目名称</th>
			<th>审核结果</th>
			<td colspan="3" class="noborder"></td>
		</tr>
		<#assign index = 1> 
		<#list dataToList as data>
		<#if data.assesslevel == 1>
			<#assign levelColor="#FF00FF">
		<#elseif data.assesslevel == 2>
			<#assign levelColor="#0000FF">
		<#elseif data.assesslevel == 3>
			<#assign levelColor="#008000">
		<#elseif data.assesslevel == 4>
			<#assign levelColor="#FF0000">
		</#if>  
		<#if export_type == "all" || export_type?contains(data.assesslevel+"")>
		<tr>
			<td x:num>${index}</td>
			<td x:str>${data.projectId}</td>
			<td x:str>${data.projectName}</td>
			<td class="align_center" style="color:${levelColor};" x:str>${data.assessResult}</td>
			<td colspan="3" class="noborder"></td>
		</tr>
		<#assign index=index+1> 
		</#if> 
		</#list>
	</table>
</body>
</html>

主要分析一下ftl文件内容

1.要声明以下命名空间

<html xmlns:v="urn:schemas-microsoft-com:vml"
	xmlns:o="urn:schemas-microsoft-com:office:office"
	xmlns:x="urn:schemas-microsoft-com:office:excel"
	xmlns="http://www.w3.org/TR/REC-html40">

2.x:num 代表此格是数据类型，x:str代表此格式字符类型，这个很重要，如果你要显示的字符中都是数字，在不设置此字符的情况下，过长的内容会被excel显示成科学计数格式。

<td x:num>${index}</td>
<td x:str>${data.projectId}</td>

3. 最好不要多个表格嵌套，将多个表格统一为一个表，这样才能设置col，控制每一列的宽度。否则很多地方不可控制的换行不美观。width 和mso-width-alt 参数是1:32的比例，但我只发现设置mso-width-alt有用，所以下面有几组数据width和mso-width-alt对不上是因为我只更改了mso-width-alt参数。不知道width参数可能会在什么情况下有用，目前先保留了。

<col width="39"  style='mso-width-source:userset;mso-width-alt:1300;'/> 
<col width="300"  style='mso-width-source:userset;mso-width-alt:6000;'/>
<col width="112"  style='mso-width-source:userset;mso-width-alt:6000;'/>
<col width="67"  style='mso-width-source:userset;mso-width-alt:2500;'/>
<col width="39" style='mso-width-source:userset;mso-width-alt:1300;'/>
<col width="53" style='mso-width-source:userset;mso-width-alt:1696;'/>
<col width="72" style='mso-width-source:userset;mso-width-alt:2304;'/>

4.action中转换方法，保障中文文件名不会乱码

        public static String convertTitle(String title){
		String str = "";
		try {
			str = new String( title.replaceAll(" ", "_").getBytes("gb2312"), "ISO8859-1" );
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		return str;
	}