在asp.net中绑定DataTable时导出到excel不起作用

Question

这里是我的代码片段，用于将DataTable对象呈现到Excel.But当我点击ExporttoExcel按钮时，我无法将数据导入到Excel中，所有的帮助将是非常有必要的。感谢提前。

<asp:Button ID="btnExport" runat="server" Text="ExporttoExcel" OnClick="ExporttoExcel"/> 

protected void ExporttoExcel(object sender, EventArgs args) 
{ 
    DataTable dt=GetValues(); 
    Response.Clear(); 
    Response.ClearContent(); 
    Response.Buffer = true; 
    Response.AddHeader("content-disposition", "attachment;filename=Data.xls"); 
    Response.ContentType = "application/vnd.ms-excel"; 
    Response.Charset = "utf-8"; 
    string tab = ""; 
    foreach (DataColumn column in dt.Columns) 
    { 
     Response.Write("<b>"); 
     Response.Write(tab + column.ColumnName.ToString()); 
     Response.Write("</b>"); 
     tab = "\t"; 
    } 
    Response.Write("\n"); 
    int i; 
    foreach (DataRow row in dt.Rows) 
    { 
     tab = ""; 
     for (i = 0; i < dt.Columns.Count; i++) 
     { 
      Response.Write(tab + row[i].ToString()); 
      tab = "\t"; 
     } 
     Response.Write("\n"); 
    } 

    Response.Flush(); 
} 

public DataTable GetValues() 
{ 
    DataTable table = new DataTable(); 
    table.Columns.Add("Dosage", typeof(int)); 
    table.Columns.Add("Drug", typeof(string)); 
    table.Columns.Add("Patient", typeof(string)); 
    table.Columns.Add("Date", typeof(DateTime)); 

    // Here we add five DataRows. 
    table.Rows.Add(25, "Indocin", "David", DateTime.Now); 
    table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now); 
    table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now); 
    table.Rows.Add(21, "Combivent", "Janet", DateTime.Now); 
    table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now); 
    return table;  
} 

Answer 1

我已经使用了下面的代码，它为我工作。您必须与我们分享您的GetValue函数定义，以便我们可以为您提供帮助。

<asp:Button ID="btnExport" runat="server" Text="ExporttoExcel"/> 

protected void ExporttoExcel(object sender EventArgs args) 
{ 
    dtCity = city.GetAllCity();//your datatable 
    string attachment = "attachment; filename=city.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(); 
} 

Answer 2

我用来创建excel文件的方法是使用gridview控件。 在gridview控件的帮助下，您可以轻松创建excel文件。 在下面的代码，我填gridview与datatable数据，并使用RenderControl方法来生成HTML，然后将其保存到一个文件，一个.xls扩展

protected void ExporttoExcel(object sender, EventArgs args) 
{ 
    DataTable dt = GetValues();   
    var grid = new System.Web.UI.WebControls.GridView(); 
    grid.ShowHeaderWhenEmpty = true;   
    grid.DataSource = dt; 


    grid.DataBind(); 
    Response.ClearContent(); 
    Response.AddHeader("content-disposition", "attachment; filename=Data.xls"); 
    Response.ContentType = "application/excel"; 
    StringWriter sw = new StringWriter(); 
    System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw); 
    grid.RenderControl(htw); 
    Response.Write(sw.ToString()); 
    Response.End();    
}