Convert Rows to Columns while exporting database table to Excel in ASP.Net using C# and VB.Net

Hi nauna,

Refer below sample.

HTML

<asp:GridView ID="GridView1" runat="server"></asp:GridView><br />
<asp:Button ID="btnConvert" runat="server" Text="Convert" OnClick="Convert" />

Code

C#

using System.Data;
using System.IO;

VB.Net

Imports System.Data
Imports System.IO

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[] { new DataColumn("Item"), new DataColumn("Price") });
        dt.Rows.Add("Shirt", 500);
        dt.Rows.Add("Jeans", 2000);
        dt.Rows.Add("Trousers", 1545);
        dt.Rows.Add("Tie", 200);
        dt.Rows.Add("Cap", 300);
        dt.Rows.Add("Hat", 350);
        dt.Rows.Add("Scarf", 140);
        dt.Rows.Add("Belt", 400);
        ViewState["dt"] = dt;
        this.BindGrid(dt, false);
    }
}

private void BindGrid(DataTable dt, bool rotate)
{
    this.GridView1.ShowHeader = !rotate;
    this.GridView1.DataSource = dt;
    this.GridView1.DataBind();
    if (rotate)
    {
        foreach (GridViewRow row in this.GridView1.Rows)
        {
            row.Cells[0].CssClass = "header";
        }
    }
}
protected void Convert(object sender, EventArgs e)
{
    DataTable dt = (DataTable)ViewState["dt"];

    DataTable dt2 = new DataTable();
    for (int i = 0; i <= dt.Rows.Count; i++)
    {
        dt2.Columns.Add();
    }
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        dt2.Rows.Add();
        dt2.Rows[i][0] = dt.Columns[i].ColumnName;
    }
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        for (int j = 0; j < dt.Rows.Count; j++)
        {
            dt2.Rows[i][j + 1] = dt.Rows[j][i];
        }
    }

    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView1.AllowPaging = false;
        this.BindGrid(dt2, true);
        this.GridView1.RenderControl(hw);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

public override void VerifyRenderingInServerForm(Control control)
{

}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Dim dt As DataTable = New DataTable()
        dt.Columns.AddRange(New DataColumn() {New DataColumn("Item"), New DataColumn("Price")})
        dt.Rows.Add("Shirt", 500)
        dt.Rows.Add("Jeans", 2000)
        dt.Rows.Add("Trousers", 1545)
        dt.Rows.Add("Tie", 200)
        dt.Rows.Add("Cap", 300)
        dt.Rows.Add("Hat", 350)
        dt.Rows.Add("Scarf", 140)
        dt.Rows.Add("Belt", 400)
        ViewState("dt") = dt
        Me.BindGrid(dt, False)
    End If
End Sub

Private Sub BindGrid(ByVal dt As DataTable, ByVal rotate As Boolean)
    Me.GridView1.ShowHeader = Not rotate
    Me.GridView1.DataSource = dt
    Me.GridView1.DataBind()
    If rotate Then
        For Each row As GridViewRow In Me.GridView1.Rows
            row.Cells(0).CssClass = "header"
        Next
    End If
End Sub

Protected Sub Convert(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = CType(ViewState("dt"), DataTable)
    Dim dt2 As DataTable = New DataTable()
    For i As Integer = 0 To dt.Rows.Count
        dt2.Columns.Add()
    Next
    For i As Integer = 0 To dt.Columns.Count - 1
        dt2.Rows.Add()
        dt2.Rows(i)(0) = dt.Columns(i).ColumnName
    Next
    For i As Integer = 0 To dt.Columns.Count - 1
        For j As Integer = 0 To dt.Rows.Count - 1
            dt2.Rows(i)(j + 1) = dt.Rows(j)(i)
        Next
    Next
    Response.Clear()
    Response.Buffer = True
    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")
    Response.Charset = ""
    Response.ContentType = "application/vnd.ms-excel"
    Using sw As StringWriter = New StringWriter()
        Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
        GridView1.AllowPaging = False
        Me.BindGrid(dt2, True)
        Me.GridView1.RenderControl(hw)
        Response.Output.Write(sw.ToString())
        Response.Flush()
        Response.End()
    End Using
End Sub

Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
End Sub

Screenshot