最近在做统计功能,要求统计结果(表格)既能查看(BS系统,在浏览器查看),又能输出为excel文件。对于输出excel文件,在网上找到n种方案,
因为还需查看,最终选择了统计结果输出为table,查看时直接显示table,输出excel时把table写进输出流,ContentType设置为application/vnd.ms-excel再输出,具体方法如下:
软件环境:VS2008,C#,IE,office2010
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">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<table>
......
</table>
</body>
</html>
2.Response的属性设置
Response关键属性有几个
//输出的应用类型
Response.ContentType = "application/vnd.ms-excel";
//设定编码方式,若输出的excel有乱码,可优先从编码方面解决
Response.Charset = "gb2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
//关闭ViewState,此属性在Page中
EnableViewState = false;
//filenames是自定义的文件名
Response.AppendHeader("Content-Disposition", "attachment;filename=" + filenames);
//content是步骤1的html,注意是string类型
Response.Write(content);
Response.End();
这样已经能发布最基本的excel,下面说些细节
1.样式最好用css,即设style属性或class属性,有些样式属性excel不认的。th、td的宽度最好在colgroup中设
2.在excel的格子中换行,可以用这个<br style='mso-data-placement:same-cell;'/>
3.关于边框宽度的问题,如果你的table是全边框,可以设置table的border属性,其中0=不显示边框。
如果表的格式比较复杂,特别是表头,有些边要隐藏的,这个要先把table的border=0,之后对每个th td用css的边框样式进行设置,但这里有个地方要注意,
就是宽度,设为0.5pt(例:border-left: 0.5pt solid #000;),如果设为1px输出的边框会很粗。
4.excel空余部分边框的问题。用此方法输出的excel,空余部分边框都不显示,如果要做成想普通excel的样子,需在输出流的head部分加上excel的设置,例子如下:
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name></x:Name>
<x:WorksheetOptions>
<x:Selected/>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml><![endif]-->
最后附上一个成果作为例子,扩展名为xls可用excel打开
<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">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name></x:Name><x:WorksheetOptions><x:Selected/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
<style type="text/css">
.td
{
width: 84px;
}
.gdtjContainer .tb tr
{
text-align: center;
vertical-align: middle;
}
.gdtjContainer .tb th
{
border-left: 0.5pt solid #000;
border-bottom: 0.5pt solid #000;
text-align: center;
font-weight: normal;
font-size: 10pt;
middle: ;;height:30px;}
.gdtjContainer .header th
{
font-size: 12pt;
}
.gdtjContainer .tb tr th.noleftborder
{
border-left: none;
}
.gdtjContainer .tb tr th.rightborder
{
border-right: 0.5pt solid #000;
}
</style>
</head>
<body>
<div class="gdtjContainer">
<table class="tb" cellspacing="0" cellpadding="0" border="0" width="2184px">
<colgroup>
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
<col class="td" />
</colgroup>
<tr style="height: 40px">
<th style="font-size: 20pt; font-family: 宋体; border: none;" colspan="26">
2011年增城市单位土地使用权出让情况登记表(统计时间从2011-06-29至2011-06-30)
</th>
</tr>
<tr>
<th colspan="23" style="border-left: none;">
</th>
<th style="text-align: left; font-size: 12pt; border-left: none;" colspan="3">
单位:万元、平方米
</th>
</tr>
<tr class="header">
<th rowspan="2">
合同编号
</th>
<th colspan="2" rowspan="2">
用地单位
</th>
<th colspan="2" rowspan="2">
土地座落
</th>
<th rowspan="2">
供地面积
</th>
<th style="border-left: none">
</th>
<th>
</th>
<th rowspan="2">
用途
</th>
<th colspan="3" rowspan="1">
出让金
</th>
<th rowspan="2">
容积率
</th>
<th rowspan="2">
建筑密度
</th>
<th rowspan="2">
绿地率
</th>
<th rowspan="2">
规划建筑面积
</th>
<th rowspan="2">
出让方式
</th>
<th rowspan="2">
审批日期
</th>
<th rowspan="2">
合同签订日期
</th>
<th rowspan="2">
动工期限
</th>
<th rowspan="2">
竣工日期
</th>
<th rowspan="2">
批次情况
</th>
<th rowspan="2">
合同约定缴费期限
</th>
<th rowspan="2">
缴费情况
</th>
<th rowspan="2">
滞纳金
</th>
<th rowspan="2" class="rightborder">
备注
</th>
</tr>
<tr style="height: 40px" class="header">
<th>
新增面积
</th>
<th style="font-size: 10pt;">
保障性住房用地占用面积
</th>
<th>
应缴
</th>
<th>
已缴
</th>
<th>
未缴
</th>
</tr>
<tr>
<th>
440183-2011-
</th>
<th colspan="2">
</th>
<th colspan="2">
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
拍卖出让
</th>
<th>
</th>
<th>
2011-06-29
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th class="rightborder">
</th>
</tr>
<tr>
<th>
</th>
<th colspan="2">
合计
</th>
<th colspan="2">
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th>
</th>
<th class="rightborder">
</th>
</tr>
</table>
</div>
</body>
</html>
原文地址:http://www.cnblogs.com/cannel/archive/2011/06/30/2094189.html 感谢。
/// <summary> /// 导出到Excel /// </summary> /// <param name="strFileName">导出的文件名</param> /// <param name="strData">要导出的数据</param> /// <param name="strCss">Css文件的路径</param> protected void ExportTExcel(string strFileName, string strData, string strCss) { Response.Clear(); Response.ClearContent(); Response.Charset = "utf-8"; Response.ContentEncoding = System.Text.Encoding.UTF8; string strRealFile = HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8); strRealFile = strRealFile.Replace("+", " "); Response.ContentType = "application/vnd.ms-excel; name=\"" + strRealFile + "\""; Response.AppendHeader("Content-Disposition:", "attachment;filename=\"" + strRealFile + "\""); Response.Write("<html>\r\n"); Response.Write("<head>\r\n"); Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">\r\n"); Response.Write("<style type=text/css>\r\n"); #region 输出样式表 System.IO.StreamReader sr = System.IO.File.OpenText(Server.MapPath(strCss)); String input; while ((input = sr.ReadLine()) != null) { Response.Write(input); Response.Write("\r\n"); } sr.Close(); #endregion Response.Write("</style>\r\n"); Response.Write("</head>\r\n"); Response.Write("<body>\r\n"); Response.Write(strData); Response.Write("</body>\r\n"); Response.Write("</html>\r\n"); } 引用: StringBuilder sb = new StringBuilder(); sb.Append("<table id='mytable' border='1' width='100%' cellpadding='2' cellspacing='0' bordercolor='#000000' style='border-collapse: collapse'>"); sb.Append("<tr>"); sb.Append("<td align='center' rowspan='3'> 日期 </td>"); sb.Append("<td align='center' rowspan='3'> 姓名 </td>"); sb.Append("<td align='center' rowspan='3'> 班级 </td>"); sb.Append("</tr>"); sb.Append("</table>"); ExportTExcel("文件名.xls", sb.ToString(), "../Css/YFCss.css"); 原文地址:http://blog.csdn.net/qq283718380/article/details/6894002 感谢