ASP.NE导出Excel

来源：互联网发布：中国股指期货数据编辑：程序博客网时间：2024/05/01 05:57

支持DataSet、DataReader、DataTable、DataGrid等数据源

看起来有点耍赖，用HTML构造的，但是能用就行

using System;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
namespace DY.BLL
{
/// <summary>
/// BLL_Excel 的摘要说明。
/// </summary>
public class BLL_Excel
{

public BLL_Excel(){}

  #region 导出Exce
  /// <summary>
  /// 导出Excel
  /// </summary>
  /// <param name="dr">OleDbDataReader 数据源</param>
  /// <param name="FileName">文件名</param>
  /// <param name="biaotou">表头</param>
  public void EduceExcel(OleDbDataReader dr,string FileName,string[] biaotou)
  {
   HttpContext.Current.Response.Clear();
   HttpContext.Current.Response.Buffer= true;//设置缓冲输出
   HttpContext.Current.Response.Charset="GB2312";//设置输出流的HTTP字符集

   HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=/""+System.Web .HttpUtility.UrlEncode (FileName,System.Text .Encoding .UTF8 )+".xls/"");
   HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
   HttpContext.Current.Response.ContentType = "application/ms-";
   //   _page.EnableViewState = false;//是否保持视图状态

   HttpContext.Current.Response.Write( HTML(dr,biaotou) );

   HttpContext.Current.Response.End ();
  }
  /// <summary>
  /// 导出Excel
  /// </summary>
  /// <param name="_page">this</param>
  /// <param name="DB">DataGrid控件名称</param>
  /// <param name="FileName">要导出的文件名</param>
  public void EduceExcel(Page _page, DataGrid DB,string FileName)
  {
   HttpContext.Current.Response.Clear();
   HttpContext.Current.Response.Buffer= true;//设置缓冲输出
   HttpContext.Current.Response.Charset="GB2312";//设置输出流的HTTP字符集

   HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=/""+System.Web .HttpUtility.UrlEncode (FileName,System.Text .Encoding .UTF8 )+".xls/"");
   HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
   HttpContext.Current.Response.ContentType = "application/ms-";
   _page.EnableViewState = false;//是否保持视图状态

   System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); //将信息写入字符串
   System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); //在WEB窗体上写出一系列的HTML特定字符和文本

   DB.RenderControl (oHtmlTextWriter);
   HttpContext.Current.Response.Write(oStringWriter.ToString ());

   HttpContext.Current.Response.End ();
  }
  /// <summary>
  /// 导出Excel
  /// </summary>
  /// <param name="ds">DataSet 数据源</param>
  /// <param name="FileName">文件名</param>
  /// <param name="biaotou">表头</param>
  public void EduceExcel(DataSet ds,string FileName,string[] biaotou)
  {
   HttpContext.Current.Response.Clear();
   HttpContext.Current.Response.Buffer= true;//设置缓冲输出
   HttpContext.Current.Response.Charset="GB2312";//设置输出流的HTTP字符集

   HttpContext.Current.Response.Write( HTML(ds,biaotou) );

   HttpContext.Current.Response.End ();
  }

  /// <summary>
  /// 导出Excel
  /// </summary>
  /// <param name="ds">DataSet 数据源</param>
  /// <param name="FileName">文件名</param>
  /// <param name="biaotou">表头</param>
  public void EduceExcel(DataTable dt,string FileName,string[] biaotou)
  {
   HttpContext.Current.Response.Clear();
   HttpContext.Current.Response.Buffer= true;//设置缓冲输出
   HttpContext.Current.Response.Charset="GB2312";//设置输出流的HTTP字符集

   HttpContext.Current.Response.Write( HTML(dt,biaotou) );

   HttpContext.Current.Response.End ();
  }
  #endregion

  #region 构造HTML
  /// <summary>
  /// 使用DataSet数据源
  /// </summary>
  /// <param name="ds"></param>
  /// <param name="biaotou"></param>
  /// <returns></returns>
  private string HTML (DataSet ds,string[] biaotou)
  {
   StringBuilder ss = new StringBuilder();
   ss.Append("<table>");
   ss.Append("<tr>");
    ss.Append(" <td>序号</td>");
   foreach(string str in biaotou)
   {
    ss.Append(" <td> "+ str +"</td>");
   }
   ss.Append("</tr>");
   int ii=1;
   foreach (DataRow dr in ds.Tables[0].Rows)
   {
    ss.Append("<tr>");
    ss.Append(" <td> "+ (ii++).ToString() +"</td>");
    int I = dr.Table.Columns.Count;
    for (int i=0;i<I;i++)
    {
     ss.Append(" <td> "+ dr[i].ToString() +"</td>");
    }

    ss.Append("</tr>");
   }
   ss.Append ("</table>");

   return ss.ToString();
  }
  /// <summary>
  /// 使用OleDbDataReader 数据源
  /// </summary>
  /// <param name="dr"></param>
  /// <param name="biaotou"></param>
  /// <returns></returns>
  private string HTML (OleDbDataReader dr,string[] biaotou)
  {
   StringBuilder ss = new StringBuilder();
   ss.Append("<table>");

   ss.Append("<tr>");
   ss.Append(" <td>序号</td>");
   foreach(string str in biaotou)
   {
    ss.Append(" <td> "+ str +"</td>");
   }
   ss.Append("</tr>");

   int ii=1;
   while( dr.Read() )
   {
    ss.Append("<tr>");
    ss.Append(" <td> "+ (ii++).ToString() +"</td>");
    int I = dr.FieldCount;
    for (int i=0;i<I;i++)
    {
     ss.Append(" <td> "+ dr[i].ToString() +"</td>");
    }

    ss.Append("</tr>");
   }
   ss.Append ("</table>");
   dr.Close();
   return ss.ToString();
  }

  /// <summary>
  /// 使用DataTable数据源
  /// </summary>
  /// <param name="dt"></param>
  /// <param name="biaotou"></param>
  /// <returns></returns>
  private string HTML (DataTable dt,string[] biaotou)
  {
   StringBuilder ss = new StringBuilder();
   ss.Append("<table>");

   ss.Append("<tr>");
   ss.Append(" <td>序号</td>");
   foreach(string str in biaotou)
   {
    ss.Append(" <td> "+ str +"</td>");
   }
   ss.Append("</tr>");

   int ii=dt.Rows.Count;
   foreach (DataRow dr in dt.Rows)
   {
    ss.Append("<tr>");
    ss.Append(" <td> "+ (ii++).ToString() +"</td>");
    int I = dr.Table.Columns.Count;
    for (int i=0;i<I;i++)
    {
     ss.Append(" <td> "+ dr[i].ToString() +"</td>");
    }

    ss.Append("</tr>");
   }
   ss.Append ("</table>");

   return ss.ToString();
  }
  #endregion
}
}