Go homepage(回首页) Upload pictures (上传图片) Write articles (发文字帖)
The author:(作者)delvpublished in(发表于) 2014/1/6 9:13:28 把WebForm数据导出到Excel中_[Asp.Net教程]
上午给系统的订单管理部分添加了一个功能,把查询到的订单信息导出到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来实现此功能,于是把代码改成这样: 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["IsValid"].ToString() + "\t"; strExcelItems += reader["DealState"].ToString() +"\n"; Response.Write(strExcelItems); } reader.Close(); Response.End();} 我这里只是简单的把查询到的数据以Excel的形式Write出来(当然也可以是其他格式,比如XML),对简单的需求足矣了,当然,用.NET直接去操作Excel文件也是很简单的,有很多这样的例子可以参考。
赞