ASP.NET Export DataTable to Excel

把Datatable导出为Excel

protected void ExportDataTableToExcel(DataTable dt, string ExcelFileName)
    {
        //DataTable dt = GetData();

        string attachment = "attachment; filename=" + ExcelFileName + ".xls";

        Response.ClearContent();

        Response.AddHeader("content-disposition", attachment);

        Response.ContentType = "application/vnd.ms-excel";

        string tab = "";

        foreach (DataColumn dc in dt.Columns)
        {

            Response.Write(tab + dc.ColumnName);

            tab = "\t";

        }

        Response.Write("\n");

        int i;

        foreach (DataRow dr in dt.Rows)
        {

            tab = "";

            for (i = 0; i < dt.Columns.Count; i++)
            {

                Response.Write(tab + dr[i].ToString());

                tab = "\t";

            }

            Response.Write("\n");

        }

        Response.End();

    }

 

 

---------------------

DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(sqlText, ConfigurationManager.ConnectionStrings["xxxConnectionString"].ConnectionString);
sda.SelectCommand.CommandTimeout = 99999;

sda.Fill(dt);

foreach (DataColumn c in dt.Columns)
{
         string cn = c.ColumnName;
          for (int i = cn.IndexOf("_"); i > 0; i = cn.IndexOf("_"))
         {
             string chr = cn.Substring(i + 1, 1);
             cn = cn.Replace("_" + chr, chr.ToUpper());
         }
        cn = Regex.Replace(Regex.Replace(cn.Substring(0, 1).ToUpper() + cn.Substring(1), "(?<=[a-z])[A-Z]", " $0"), "([A-Z])([A-Z][a-z][a-z])", "$1 $2");
      c.ColumnName = cn;
}
ExportDataTableToExcel(dt, Session["Table"].ToString());

-----------------------------------

http://www.codedigest.com/Articles/ASPNET/130_Export_to_Excel_in_ASPNet_20_%E2%80%93Gridview_to_Excel_DataTable_to_Excel.aspx