如何在ASP.net中使用c#导出gridview到Excel

本文关键字：导出 gridview Excel ASP net | 更新日期: 2023-09-27 17:49:14

有人知道我如何在ASP.net中导出gridview到Excel吗?

我使用这段代码，但我不能访问我的gridview，因为它是空的

protected void BtnExport_Click(object sender, EventArgs e)
{
    DataTable dtOriginal = new DataTable();
    dtOriginal = (DataTable)gvRapporten.DataSource; //Return Table consisting data
    //Create Tempory Table
    DataTable dtTemp = new DataTable();
    //Creating Header Row
    dtTemp.Columns.Add("<b>Melder</b>");
    dtTemp.Columns.Add("<b>Onderwerp</b>");
    dtTemp.Columns.Add("<b>Oplosser</b>");
    dtTemp.Columns.Add("<b>Niveau 2</b>");
    DataRow drAddItem;
    for (int i = 0; i < dtOriginal.Rows.Count; i++)
    {
        drAddItem = dtTemp.NewRow();
        drAddItem[0] = dtOriginal.Rows[i][0].ToString();//Melder
        drAddItem[1] = dtOriginal.Rows[i][1].ToString();//Onderwerp
        drAddItem[2] = dtOriginal.Rows[i][2].ToString();//Oplosser
        drAddItem[3] = dtOriginal.Rows[i][3].ToString();//Niveau 2
        dtTemp.Rows.Add(drAddItem);
    }
    //Temp Grid
    DataGrid dg = new DataGrid();
    dg.DataSource = dtTemp;
    dg.DataBind();
    ExportToExcel("Report.xls", dg);
    dg = null;
    dg.Dispose();
}
private void ExportToExcel(string strFileName, DataGrid dg)
{
    Response.ClearContent();
    Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
    Response.ContentType = "application/excel";
    System.IO.StringWriter sw = new System.IO.StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);
    dg.RenderControl(htw);
    Response.Write(sw.ToString());
    Response.End();
}

如何在ASP.net中使用c#导出gridview到Excel

你能试试吗?

System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
dg.RenderControl(htw);
Response.Write(sw.ToString());
Response.Flush();
Response.End();

您的代码只是生成一个Html-Table(这就是ASP。Net渲染GridView/DataGrid到)，没有其他。Excel可以处理这个问题，并像表一样显示它，但它不是真正的xls或csv文件。

GridView的DataSource-property在回发后总是null/nothing。在导出到excel之前，您应该再次生成数据源。

此代码将任何数据表转换为csv-String:

c# 

public static string tableToCsv(DataTable DT)
{
    System.Text.StringBuilder csv = new System.Text.StringBuilder();
    System.Text.StringBuilder rowData = new System.Text.StringBuilder();
    DataRow DR = null;
    DataColumn DC = null;
    foreach (DataColumn DC_loopVariable in DT.Columns) {
        DC = DC_loopVariable;
        rowData.Length = 0;
        rowData.Append((rowData.Length > 0 ? ";" : "").ToString());
        rowData.Append(DC.ColumnName);
    }
    csv.Append(rowData.ToString()).Append(Strings.Chr(13));
    foreach (DataRow DR_loopVariable in DT.Rows) {
        DR = DR_loopVariable;
        rowData.Length = 0;
        foreach (DataColumn DC_loopVariable in DT.Columns) {
            DC = DC_loopVariable;
            rowData.Append((rowData.Length > 0 ? ";" : "").ToString());
            rowData.Append(DR[DC.ColumnName].ToString());
        }
        csv.Append(rowData.ToString()).Append(Strings.Chr(13));
    }
    return csv.ToString();
}

VB 

Public Shared Function tableToCsv(ByVal DT As DataTable) As String
    Dim csv As New System.Text.StringBuilder
    Dim rowData As New System.Text.StringBuilder
    Dim DR As DataRow
    Dim DC As DataColumn
    For Each DC In DT.Columns
        rowData.Length = 0
        rowData.Append(IIf(rowData.Length > 0, ";", "").ToString)
        rowData.Append(DC.ColumnName)
    Next
    csv.Append(rowData.ToString).Append(Chr(13))
    For Each DR In DT.Rows
        rowData.Length = 0
        For Each DC In DT.Columns
            rowData.Append(IIf(rowData.Length > 0, ";", "").ToString)
            rowData.Append(DR.Item(DC.ColumnName).ToString)
        Next
        csv.Append(rowData.ToString).Append(Chr(13))
    Next
    Return csv.ToString
End Function

Dim dt As DataTable = ViewState("FinalState")
Dim GV As New GridView
GV.DataSource = dt ' UpdateDataTable(ViewState("FinalTable"))
GV.DataBind()
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=ProspectsBank.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Dim sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)
For i As Integer = 0 To GV.Rows.Count - 1
    'Apply text style to each Row 
    GV.Rows(i).Attributes.Add("class", "textmode")
Next
GV.RenderControl(hw)
Dim style As String = "<style> .textmode { mso-number-format:'@; } </style>"
Response.Write(style)
Response.Output.Write(sw.ToString())
clsLog.SaveLogValue(Request.ServerVariables("REMOTE_ADDR"), "SearchAudienceForMagazine.aspx", "User Export Data From Our Databank in EXCEL format. Current they Exported " & dt.Rows.Count & " Data.", Session("User_TypeId"), Session("User_Name"))
Response.Flush()
Response.End()
相关文章：