上午给系统的订单管理部分添加了一个功能,把查询到的订单信息导出到Excel,供管理员分析用。以前写的代码如下:
Response.Clear();
Response.BufferOutput
=
true
;
Response.Charset
=
"
GB2312
"
;
Response.AppendHeader(
"
Content-Disposition
"
,
"
attachment;filename = FileName.xls
"
);
Response.ContentEncoding
=
Encoding.GetEncoding(
"
GB2312
"
);
Response.ContentType
=
"
application/ms-excel
"
;
this
.EnableViewState
=
false
;
System.Globalization.CultureInfo myinfo
=
new
System.Globalization.CultureInfo(
"
ZH-CN
"
,
true
);
StringWriter osw
=
new
StringWriter(myinfo);
HtmlTextWriter ohtw
=
new
HtmlTextWriter(osw);
dgQueryResult.RenderControl(ohtw);
Response.Write(osw);
Response.End();
我的查询用了分页,客户要求把所有查询到的数据都导出到Excel,所以不能象以前那样通过把控件的内容导出到Excel来实现此功能,于是把代码改成这样:
我这里只是简单的把查询到的数据以Excel的形式Write出来(当然也可以是其他格式,比如XML),对简单的需求足矣了,当然,用.NET直接去操作Excel文件也是很简单的,有很多这样的例子可以参考。
![](https://i-blog.csdnimg.cn/blog_migrate/d631c8c331f0e38b6fa6bedcf78636de.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/d631c8c331f0e38b6fa6bedcf78636de.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/d631c8c331f0e38b6fa6bedcf78636de.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/d631c8c331f0e38b6fa6bedcf78636de.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/d631c8c331f0e38b6fa6bedcf78636de.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/d631c8c331f0e38b6fa6bedcf78636de.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/d631c8c331f0e38b6fa6bedcf78636de.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/d631c8c331f0e38b6fa6bedcf78636de.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/d631c8c331f0e38b6fa6bedcf78636de.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/d631c8c331f0e38b6fa6bedcf78636de.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/d631c8c331f0e38b6fa6bedcf78636de.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/d631c8c331f0e38b6fa6bedcf78636de.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/d631c8c331f0e38b6fa6bedcf78636de.gif)
我的查询用了分页,客户要求把所有查询到的数据都导出到Excel,所以不能象以前那样通过把控件的内容导出到Excel来实现此功能,于是把代码改成这样:
Response.Clear();
Response.BufferOutput = true ;
Response.Charset = " GB2312 " ;
Response.AppendHeader( " Content-Disposition " , " attachment;filename = 订单.xls " );
Response.ContentEncoding = System.Text.Encoding.GetEncoding( " GB2312 " );
Response.ContentType = " application/ms-excel " ;
string strExcelHeader = string .Empty;
string strExcelItems;
if (ViewState[ " SQL " ] != null ) // 取前面查询用的SQL语句
{
// 取得各列标题,各标题之间以/t分割,最后一个列标题后加回车符
strExcelHeader = " 订单号/t经销商/t地区/t小类/t商品/t实付金额/t下单时间/t有效状态/t处理状态/t/n " ;
// 向HTTP输出流中写入取得的数据信息
Response.Write(strExcelHeader);
// 逐行处理查询结果数据
ITDBHandle itDbHandle = new ITDBHandle();
itDbHandle.QueryString = ViewState[ " SQL " ].ToString();
SqlDataReader reader = itDbHandle.ExecuteDataReader();
while (reader.Read())
{
strExcelItems = string .Empty;
strExcelItems += reader[ " OrderID " ].ToString() + " /t " ;
strExcelItems += reader[ " DealerName " ].ToString() + " /t " ;
strExcelItems += reader[ " City " ].ToString() + " /t " ;
strExcelItems += reader[ " SmallClassName " ].ToString() + " /t " ;
strExcelItems += reader[ " BrandName " ].ToString() + reader[ " Model " ].ToString() + " /t " ;
strExcelItems += reader[ " TotalPrice " ].ToString() + " /t " ;
strExcelItems += reader[ " OrderDate " ].ToString() + " /t " ;
strExcelItems += reader[ "Is Valid " ].ToString() + " /t " ;
strExcelItems += reader[ " DealState " ].ToString() + " /n " ;
Response.Write(strExcelItems);
}
reader.Close();
Response.End();
}
Response.BufferOutput = true ;
Response.Charset = " GB2312 " ;
Response.AppendHeader( " Content-Disposition " , " attachment;filename = 订单.xls " );
Response.ContentEncoding = System.Text.Encoding.GetEncoding( " GB2312 " );
Response.ContentType = " application/ms-excel " ;
string strExcelHeader = string .Empty;
string strExcelItems;
if (ViewState[ " SQL " ] != null ) // 取前面查询用的SQL语句
{
// 取得各列标题,各标题之间以/t分割,最后一个列标题后加回车符
strExcelHeader = " 订单号/t经销商/t地区/t小类/t商品/t实付金额/t下单时间/t有效状态/t处理状态/t/n " ;
// 向HTTP输出流中写入取得的数据信息
Response.Write(strExcelHeader);
// 逐行处理查询结果数据
ITDBHandle itDbHandle = new ITDBHandle();
itDbHandle.QueryString = ViewState[ " SQL " ].ToString();
SqlDataReader reader = itDbHandle.ExecuteDataReader();
while (reader.Read())
{
strExcelItems = string .Empty;
strExcelItems += reader[ " OrderID " ].ToString() + " /t " ;
strExcelItems += reader[ " DealerName " ].ToString() + " /t " ;
strExcelItems += reader[ " City " ].ToString() + " /t " ;
strExcelItems += reader[ " SmallClassName " ].ToString() + " /t " ;
strExcelItems += reader[ " BrandName " ].ToString() + reader[ " Model " ].ToString() + " /t " ;
strExcelItems += reader[ " TotalPrice " ].ToString() + " /t " ;
strExcelItems += reader[ " OrderDate " ].ToString() + " /t " ;
strExcelItems += reader[ "Is Valid " ].ToString() + " /t " ;
strExcelItems += reader[ " DealState " ].ToString() + " /n " ;
Response.Write(strExcelItems);
}
reader.Close();
Response.End();
}
我这里只是简单的把查询到的数据以Excel的形式Write出来(当然也可以是其他格式,比如XML),对简单的需求足矣了,当然,用.NET直接去操作Excel文件也是很简单的,有很多这样的例子可以参考。