asp.net后台导出excel的方法一:使用response导出excel
</h1>
<div class="clear"></div>
<div class="postBody">
方法一:带StringBuilder 方法的导出
该方法是将所有的数据通过html的形式写入到StringBuilder 中,然后通过response导出。
熟悉html格式的人可以改变成各种格式。
List<U> objList = new List<U>();
objList = BLL.GetInfo();//读取数据
StringBuilder sb = new StringBuilder();
sb.Append("<style type=\"text/css\">");
sb.Append("<!--");
sb.Append(".text");
sb.Append("{mso-style-parent:style0;");
sb.Append("font-size:10.0pt;");
sb.Append("font-family:\"Arial Unicode MS\", sans-serif;");
sb.Append("mso-font-charset:0;");
sb.Append(@"mso-number-format:\@;");
sb.Append("text-align:center;");
sb.Append("border:.5pt solid black;");
sb.Append("white-space:normal;}");
sb.Append("-->");
sb.Append("</style>");
sb.Append("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">");
sb.Append("<tr align=\"Center\" style=\"font-weight:bold;\">");
sb.Append("<td>ID</td><td>用户名</td><td>真实姓名</td><td>省份</td><td>注册时间</td");
sb.Append("</tr>");
foreach (U item in objList)
{
sb.Append("<tr align=\"Center\"><td>" + item.id + "</td><td>" + item.uName + "</td><td>" + item.rName + "</td><td>" + item.proId + "</td><td>" + item.regDate + "</td></tr>");
}
sb.Append("</table>");
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.Charset </span>= <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">GB2312</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 添加头信息,为"文件下载/另存为"对话框指定默认文件名</span>
System.Web.HttpContext.Current.Response.AddHeader(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Content-Disposition</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">attachment; filename=myU.xls</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 添加头信息,指定文件大小,让浏览器能够显示下载进度
</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> System.Web.HttpContext.Current.Response.AddHeader("Content-Length",sb.ToString());
</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 指定返回的是一个不能被客户端读取的流,必须被下载</span>
System.Web.HttpContext.Current.Response.ContentType = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">application/ms-excel</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 把文件流发送到客户端</span>
System.Web.HttpContext.Current.Response.Write(sb.ToString());
// 停止页面的执行
System.Web.HttpContext.Current.Response.End();
方法二:带StringWriter的导出方法
该方法的格式不容易改变 方法与上面原理相同是将数据写入到StringWriter 中 让后到导出
众所周知,Respone.Write()是输出Html流程序给用户的。考虑到一个标准的Web页面的是有多种呈现方式的,
例如:
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 这是以标准网页形式输出Html流
<meta http-equiv="Content-Type" content="application/vnd.ms-excel">
<meta http-equiv="Content-Disposition" content="attachment; filename=ex.xls">这是以附件形式输出Html流,而且是将“数据”存放在ex.xls这个表格中。^_^
那么我们以编码的形式如何显示^_^(现在写VB了,给出的也是VB的事例)
1 Dim _DataStringWriter As StringWriter = New StringWriter 定义一个StringWriter对象
2 _DataStringWriter.WiteLine("FirstFieldName" + ControlChars.Tab + "SecondFieldName")给输出的Excel表格每 列加入名称
3 从数据“容器”里面将数据取出。例如
Dim dt as New DataTable
For i as Integer = 0 To dt.Rows.Count - 1 Then
_DataStringWriter.WiteLine(dt(i)(0) + ControlChars.Tab + dt(i)(1))
Next
4 Response.AddHeader("Content-Disposition", "attachment; filename=" & fileName)
Response.ContentType = "application/vnd.ms-excel"
Response.ContentEncoding = System.Text.Encoding.Unicode
5 Response.Write(_DataStringWriter) 输出Html流
Response.End()
以上已经可以实现将数据导入到Excel表格,如果需要导入Word则Response.ContentType = "application/vnd.ms-excel"中改为Response.ContentType = "application/vnd.ms-word"即可。但是注意将fileName也应随之改变,XX.xls或者XX.doc
方法三:datatable导出
该方法是response一行一行导出的,你可以根据自己的需求添加相应的行。
/// <summary>
/// DataTable中的数据导出到Excel并下载
/// </summary>
/// <param name="dt">要导出的DataTable</param>
/// <param name="FileType">类型</param>
/// <param name="FileName">Excel的文件名</param>
public void CreateExcel(DataTable dt, string FileType, string FileName)
{
Response.Clear();
Response.Charset = "UTF-8";
Response.Buffer = true;
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls\"");
Response.ContentType = FileType;
</span><span style="color: rgba(0, 0, 255, 1)">string</span> colHeaders = <span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)">.Empty;
</span><span style="color: rgba(0, 0, 255, 1)">string</span> ls_item = <span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)">.Empty;
DataRow[] myRow </span>=<span style="color: rgba(0, 0, 0, 1)"> dt.Select();
</span><span style="color: rgba(0, 0, 255, 1)">int</span> i = <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 0, 255, 1)">int</span> cl =<span style="color: rgba(0, 0, 0, 1)"> dt.Columns.Count;
</span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (DataRow row <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> myRow)
{
</span><span style="color: rgba(0, 0, 255, 1)">for</span> (i = <span style="color: rgba(128, 0, 128, 1)">0</span>; i < cl; i++<span style="color: rgba(0, 0, 0, 1)">)
{
</span><span style="color: rgba(0, 0, 255, 1)">if</span> (i == (cl - <span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">))
{<br>//到头换行
ls_item </span>+= row[i].ToString() + <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">\n</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
}
</span><span style="color: rgba(0, 0, 255, 1)">else</span><span style="color: rgba(0, 0, 0, 1)">
{<br>//换格
ls_item </span>+= row[i].ToString() + <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">\t</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
}
}
</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">一行一行的写入</span>
Response.Output.Write(ls_item);
ls_item = string.Empty;
}
Response.Output.Flush();
Response.End();
}</span></pre>
<div class="clear"></div>
<div id="post_next_prev">
<a href="https://www.cnblogs.com/sizhizhiyue/p/4759502.html" class="p_n_p_prefix">« </a> 上一篇: <a href="https://www.cnblogs.com/sizhizhiyue/p/4759502.html" title="发布于 2015-08-26 10:05">infragistics--web网站升级注意点</a>
<br>
<a href="https://www.cnblogs.com/sizhizhiyue/p/4825575.html" class="p_n_p_prefix">» </a> 下一篇: <a href="https://www.cnblogs.com/sizhizhiyue/p/4825575.html" title="发布于 2015-09-21 11:12">asp.net后台导出excel的方法:使用response导出excel</a>
</h1>
<div class="clear"></div>
<div class="postBody">
方法一:带StringBuilder 方法的导出
该方法是将所有的数据通过html的形式写入到StringBuilder 中,然后通过response导出。
熟悉html格式的人可以改变成各种格式。
List<U> objList = new List<U>();
objList = BLL.GetInfo();//读取数据
StringBuilder sb = new StringBuilder();
sb.Append("<style type=\"text/css\">");
sb.Append("<!--");
sb.Append(".text");
sb.Append("{mso-style-parent:style0;");
sb.Append("font-size:10.0pt;");
sb.Append("font-family:\"Arial Unicode MS\", sans-serif;");
sb.Append("mso-font-charset:0;");
sb.Append(@"mso-number-format:\@;");
sb.Append("text-align:center;");
sb.Append("border:.5pt solid black;");
sb.Append("white-space:normal;}");
sb.Append("-->");
sb.Append("</style>");
sb.Append("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">");
sb.Append("<tr align=\"Center\" style=\"font-weight:bold;\">");
sb.Append("<td>ID</td><td>用户名</td><td>真实姓名</td><td>省份</td><td>注册时间</td");
sb.Append("</tr>");
foreach (U item in objList)
{
sb.Append("<tr align=\"Center\"><td>" + item.id + "</td><td>" + item.uName + "</td><td>" + item.rName + "</td><td>" + item.proId + "</td><td>" + item.regDate + "</td></tr>");
}
sb.Append("</table>");
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.Charset </span>= <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">GB2312</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 添加头信息,为"文件下载/另存为"对话框指定默认文件名</span>
System.Web.HttpContext.Current.Response.AddHeader(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Content-Disposition</span><span style="color: rgba(128, 0, 0, 1)">"</span>, <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">attachment; filename=myU.xls</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">);
</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 添加头信息,指定文件大小,让浏览器能够显示下载进度
</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> System.Web.HttpContext.Current.Response.AddHeader("Content-Length",sb.ToString());
</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 指定返回的是一个不能被客户端读取的流,必须被下载</span>
System.Web.HttpContext.Current.Response.ContentType = <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">application/ms-excel</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 把文件流发送到客户端</span>
System.Web.HttpContext.Current.Response.Write(sb.ToString());
// 停止页面的执行
System.Web.HttpContext.Current.Response.End();
方法二:带StringWriter的导出方法
该方法的格式不容易改变 方法与上面原理相同是将数据写入到StringWriter 中 让后到导出
众所周知,Respone.Write()是输出Html流程序给用户的。考虑到一个标准的Web页面的是有多种呈现方式的,
例如:
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 这是以标准网页形式输出Html流
<meta http-equiv="Content-Type" content="application/vnd.ms-excel">
<meta http-equiv="Content-Disposition" content="attachment; filename=ex.xls">这是以附件形式输出Html流,而且是将“数据”存放在ex.xls这个表格中。^_^
那么我们以编码的形式如何显示^_^(现在写VB了,给出的也是VB的事例)
1 Dim _DataStringWriter As StringWriter = New StringWriter 定义一个StringWriter对象
2 _DataStringWriter.WiteLine("FirstFieldName" + ControlChars.Tab + "SecondFieldName")给输出的Excel表格每 列加入名称
3 从数据“容器”里面将数据取出。例如
Dim dt as New DataTable
For i as Integer = 0 To dt.Rows.Count - 1 Then
_DataStringWriter.WiteLine(dt(i)(0) + ControlChars.Tab + dt(i)(1))
Next
4 Response.AddHeader("Content-Disposition", "attachment; filename=" & fileName)
Response.ContentType = "application/vnd.ms-excel"
Response.ContentEncoding = System.Text.Encoding.Unicode
5 Response.Write(_DataStringWriter) 输出Html流
Response.End()
以上已经可以实现将数据导入到Excel表格,如果需要导入Word则Response.ContentType = "application/vnd.ms-excel"中改为Response.ContentType = "application/vnd.ms-word"即可。但是注意将fileName也应随之改变,XX.xls或者XX.doc
方法三:datatable导出
该方法是response一行一行导出的,你可以根据自己的需求添加相应的行。
/// <summary>
/// DataTable中的数据导出到Excel并下载
/// </summary>
/// <param name="dt">要导出的DataTable</param>
/// <param name="FileType">类型</param>
/// <param name="FileName">Excel的文件名</param>
public void CreateExcel(DataTable dt, string FileType, string FileName)
{
Response.Clear();
Response.Charset = "UTF-8";
Response.Buffer = true;
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls\"");
Response.ContentType = FileType;
</span><span style="color: rgba(0, 0, 255, 1)">string</span> colHeaders = <span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)">.Empty;
</span><span style="color: rgba(0, 0, 255, 1)">string</span> ls_item = <span style="color: rgba(0, 0, 255, 1)">string</span><span style="color: rgba(0, 0, 0, 1)">.Empty;
DataRow[] myRow </span>=<span style="color: rgba(0, 0, 0, 1)"> dt.Select();
</span><span style="color: rgba(0, 0, 255, 1)">int</span> i = <span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">;
</span><span style="color: rgba(0, 0, 255, 1)">int</span> cl =<span style="color: rgba(0, 0, 0, 1)"> dt.Columns.Count;
</span><span style="color: rgba(0, 0, 255, 1)">foreach</span> (DataRow row <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> myRow)
{
</span><span style="color: rgba(0, 0, 255, 1)">for</span> (i = <span style="color: rgba(128, 0, 128, 1)">0</span>; i < cl; i++<span style="color: rgba(0, 0, 0, 1)">)
{
</span><span style="color: rgba(0, 0, 255, 1)">if</span> (i == (cl - <span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">))
{<br>//到头换行
ls_item </span>+= row[i].ToString() + <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">\n</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
}
</span><span style="color: rgba(0, 0, 255, 1)">else</span><span style="color: rgba(0, 0, 0, 1)">
{<br>//换格
ls_item </span>+= row[i].ToString() + <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">\t</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
}
}
</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">一行一行的写入</span>
Response.Output.Write(ls_item);
ls_item = string.Empty;
}
Response.Output.Flush();
Response.End();
}</span></pre>
<div class="clear"></div>
<div id="post_next_prev">
<a href="https://www.cnblogs.com/sizhizhiyue/p/4759502.html" class="p_n_p_prefix">« </a> 上一篇: <a href="https://www.cnblogs.com/sizhizhiyue/p/4759502.html" title="发布于 2015-08-26 10:05">infragistics--web网站升级注意点</a>
<br>
<a href="https://www.cnblogs.com/sizhizhiyue/p/4825575.html" class="p_n_p_prefix">» </a> 下一篇: <a href="https://www.cnblogs.com/sizhizhiyue/p/4825575.html" title="发布于 2015-09-21 11:12">asp.net后台导出excel的方法:使用response导出excel</a>
【推荐】有你助力,更好为你——博客园用户消费观调查,附带小惊喜!
【推荐】博客园x丝芙兰-圣诞特别活动:圣诞选礼,美力送递
【推荐】了不起的开发者,挡不住的华为,园子里的品牌专区
【推荐】未知数的距离,毫秒间的传递,声网与你实时互动
【推荐】新一代 NoSQL 数据库,Aerospike专区新鲜入驻
· Asp.net 导出Excel 和Word
· Asp.net中导出成Excel等格式
· 关于asp.net导出Excel
· Asp.NET中把DataTable导出为Excel ,中文有乱码现象解决办法
· asp.net后台导出excel的方法:使用response导出excel
» 更多推荐...
· 软银注资作业帮背后,在线教育迎来“长期主义”胜利
· 苹果2号创始人发行虚拟货币,市值约10亿美金
· 滴滴升级防疫措施:将组织北京司机核酸检测
· 小红书发布2021年度十大生活方式趋势:回血式独居、国潮澎湃等上榜
· 微信官方公布2020年度朋友圈十大谣言
» 更多新闻...