locked
How to Export GridView To Word/Excel/PDF/CSV in ASP.Net RRS feed

  • Question

  • User1823295476 posted

    Hi All

    I have some problem to Export GridView To Word/Excel/PDF/CSV in ASP.Net . Please help me.

    Thanks

    Monday, April 20, 2009 1:27 AM

Answers

  • User-137840861 posted

    In this article, I will explain how to export GridView to Word, Excel, PDF and CSV formats.

    Exporting to Word, Excel and CSV can be easily achieved using ASP.Net without any third party tools, but for exporting GridView to PDF I am using iTextSharp which is a free library for exporting html to PDF.

    To start with I have a GridView in which I am showing Customers records from the NorthWind Database.

    The HTML markup of the GridView is as shown below

    <asp:GridView ID="GridView1" runat="server"

        AutoGenerateColumns = "false" Font-Names = "Arial"

        Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B" 

        HeaderStyle-BackColor = "green" AllowPaging ="true"  

        OnPageIndexChanging = "OnPaging" >

       <Columns>

        <asp:BoundField ItemStyle-Width = "150px" DataField = "CustomerID"

        HeaderText = "CustomerID" />

        <asp:BoundField ItemStyle-Width = "150px" DataField = "City"

        HeaderText = "City"/>

        <asp:BoundField ItemStyle-Width = "150px" DataField = "Country"

        HeaderText = "Country"/>

        <asp:BoundField ItemStyle-Width = "150px" DataField = "PostalCode"

        HeaderText = "PostalCode"/>

       </Columns>

    </asp:GridView>

    In the figure below the GridView is shown with four buttons

    1.     Export To Word

    2.     Export To Excel

    3.     Export To PDF

    4.     Export To CSV



     

    GridView with Sample Data



     

    Export to Microsoft Word Format

    C#

    protected void btnExportWord_Click(object sender, EventArgs e)

    {

        Response.Clear();

        Response.Buffer = true;

        Response.AddHeader("content-disposition",

        "attachment;filename=GridViewExport.doc");

        Response.Charset = "";

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

        StringWriter sw= new StringWriter();

        HtmlTextWriter hw = new HtmlTextWriter(sw);

        GridView1.AllowPaging = false;

        GridView1.DataBind();

        GridView1.RenderControl(hw);

        Response.Output.Write(sw.ToString());

        Response.Flush();

        Response.End();

    }

    VB.Net

    Protected Sub btnExportWord_Click(ByVal sender As Object,

        ByVal e As EventArgs)

            Response.Clear()

            Response.Buffer = True

            Response.AddHeader("content-disposition",

            "attachment;filename=GridViewExport.doc")

            Response.Charset = ""

            Response.ContentType = "application/vnd.ms-word "

            Dim sw As New StringWriter()

            Dim hw As New HtmlTextWriter(sw)

            GridView1.AllowPaging = False

            GridView1.DataBind()

            GridView1.RenderControl(hw)

            Response.Output.Write(sw.ToString())

            Response.Flush()

            Response.End()

        End Sub

    The above function renders the GridView contents as Microsoft Word format. You will notice I have disabled paging before exporting, so that all the pages are exported.  

    The Output Exported File



     

    GridView data exported to Word Document



     

    Export to Microsoft Excel Format

    For exporting the document to Excel if you do it directly as done in case of word the row background color is applied throughout to all the columns in the Excel Sheet hence in order to avoid it. I have done a workaround below.

    First I am changing the background color of each row back to white.

    Then I am applying the background color to each individual cell rather than the whole row. Thus when you export now you will notice that the formatting is applied only to the GridView cells and not all

    Also I am applying textmode style class to all cells and then adding the style CSS class to the GridView before rendering it, this ensures that all the contents of GridView are rendered as text.

     

    protected void btnExportExcel_Click(object sender, EventArgs e)

    {

    Response.Clear();

    Response.Buffer = true;

    Response.AddHeader("content-disposition",

    "attachment;filename=GridViewExport.xls");

    Response.Charset = "";

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

    StringWriter sw = new StringWriter();

    HtmlTextWriter hw = new HtmlTextWriter(sw);

    GridView1.AllowPaging = false;

    GridView1.DataBind();

    //Change the Header Row back to white color

    GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");

    //Apply style to Individual Cells

    GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");

    GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");

    GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");

    GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");  

    for (int i = 0; i < GridView1.Rows.Count;i++ )

    {

        GridViewRow row = GridView1.Rows[i];

        //Change Color back to white

        row.BackColor = System.Drawing.Color.White;

        //Apply text style to each Row

        row.Attributes.Add("class", "textmode");

        //Apply style to Individual Cells of Alternating Row

        if (i % 2 != 0)

        {

            row.Cells[0].Style.Add("background-color", "#C2D69B");

            row.Cells[1].Style.Add("background-color", "#C2D69B");

            row.Cells[2].Style.Add("background-color", "#C2D69B");

            row.Cells[3].Style.Add("background-color", "#C2D69B");  

        }

    }

    GridView1.RenderControl(hw);

    //style to format numbers to string

    string style = @"<style> .textmode { mso-number-format:\@; } </style>";

    Response.Write(style);

    Response.Output.Write(sw.ToString());

    Response.Flush();

    Response.End();

    }

      

    VB.Net

    Protected Sub btnExportExcel_Click(ByVal sender As Object,

    ByVal e As EventArgs)

      Response.Clear()

      Response.Buffer = True

      Response.AddHeader("content-disposition",

      "attachment;filename=GridViewExport.xls")

      Response.Charset = ""

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

     

      Dim sw As New StringWriter()

      Dim hw As New HtmlTextWriter(sw)

      GridView1.AllowPaging = False

      GridView1.DataBind()

      'Change the Header Row back to white color

      GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF")

      'Apply style to Individual Cells

      GridView1.HeaderRow.Cells(0).Style.Add("background-color", "green")

      GridView1.HeaderRow.Cells(1).Style.Add("background-color", "green")

      GridView1.HeaderRow.Cells(2).Style.Add("background-color", "green")

      GridView1.HeaderRow.Cells(3).Style.Add("background-color", "green")

      For i As Integer = 0 To GridView1.Rows.Count - 1

       Dim row As GridViewRow = GridView1.Rows(i)

       'Change Color back to white

       row.BackColor = System.Drawing.Color.White

       'Apply text style to each Row

       row.Attributes.Add("class", "textmode")

       'Apply style to Individual Cells of Alternating Row

       If i Mod 2 <> 0 Then

        row.Cells(0).Style.Add("background-color", "#C2D69B")

        row.Cells(1).Style.Add("background-color", "#C2D69B")

        row.Cells(2).Style.Add("background-color", "#C2D69B")

        row.Cells(3).Style.Add("background-color", "#C2D69B")

       End If

      Next

      GridView1.RenderControl(hw)

      'style to format numbers to string

      Dim style As String = "<style>.textmode{mso-number-format:\@;}</style>"

      Response.Write(style)

      Response.Output.Write(sw.ToString())

      Response.Flush()

      Response.End()

    End Sub

    The Output Exported File



     

    GridView data exported to Excel Document



     

    Export to Portable Document Format

    For exporting the GridView to PDF I am using the iTextSharp Library. You will need to Add Reference for the iTextSharp Library in your Website.

    Then import the following Namespaces

    C#

    using iTextSharp.text;

    using iTextSharp.text.pdf;

    using iTextSharp.text.html;

    using iTextSharp.text.html.simpleparser;

    VB.Net

    Imports iTextSharp.text

    Imports iTextSharp.text.pdf

    Imports iTextSharp.text.html

    Imports iTextSharp.text.html.simpleparser

    By default the iTextSharp Library does not support background color of table cells or table rows

    Hence when you render it as PDF your GridView is rendered without any formatting.

    Recently I read an article on hamang.net where the author has provided the snippet to modify the iTextSharp so that it exports the HTML with background color.

    For this tutorial, I have already modified the iTextSharp Library DLL so that the GridView is rendered with all the background color used. You can refer the code for exporting GridView to PDF below

      

                  

    C#

    protected void btnExportPDF_Click(object sender, EventArgs e)

    {

        Response.ContentType = "application/pdf";

        Response.AddHeader("content-disposition",

         "attachment;filename=GridViewExport.pdf");

        Response.Cache.SetCacheability(HttpCacheability.NoCache);

        StringWriter sw = new StringWriter();

        HtmlTextWriter hw = new HtmlTextWriter(sw);

        GridView1.AllowPaging = false;

        GridView1.DataBind();

        GridView1.RenderControl(hw);

        StringReader sr = new StringReader(sw.ToString());

        Document pdfDoc = new Document(PageSize.A4, 10f,10f,10f,0f);

        HTMLWorker htmlparser = new HTMLWorker(pdfDoc);

        PdfWriter.GetInstance(pdfDoc, Response.OutputStream);

        pdfDoc.Open();

        htmlparser.Parse(sr);

        pdfDoc.Close();

        Response.Write(pdfDoc);

        Response.End(); 

    }

    VB.Net

    Protected Sub btnExportPDF_Click(ByVal sender As Object,

    ByVal e As EventArgs)

     Response.ContentType = "application/pdf"

     Response.AddHeader("content-disposition",

     "attachment;filename=GridViewExport.pdf")

     Response.Cache.SetCacheability(HttpCacheability.NoCache)

     Dim sw As New StringWriter()

     Dim hw As New HtmlTextWriter(sw)

     GridView1.AllowPaging = False

     GridView1.DataBind()

     GridView1.RenderControl(hw)

     Dim sr As New StringReader(sw.ToString())

     Dim pdfDoc As New Document(PageSize.A4, 10.0F, 10.0F, 10.0F, 0.0F)

     Dim htmlparser As New HTMLWorker(pdfDoc)

     PdfWriter.GetInstance(pdfDoc, Response.OutputStream)

     pdfDoc.Open()

     htmlparser.Parse(sr)

     pdfDoc.Close()

     Response.Write(pdfDoc)

     Response.End()

    End Sub

    The Output Exported File



     

    GridView data exported to PDF Document



     

    Export to Text/CSV

    Finally comes exporting GridView to CSV or Text File delimited by a separator like comma.

    To export the GridView as CSV, I am running a two for loops. While looping through the GridView columns and appending comma after each column and while looping through rows appending new line character. Refer the code below.

    C#

    protected void btnExportCSV_Click(object sender, EventArgs e)

    {

        Response.Clear();

        Response.Buffer = true;

        Response.AddHeader("content-disposition",

         "attachment;filename=GridViewExport.csv");

        Response.Charset = "";

        Response.ContentType = "application/text";

        GridView1.AllowPaging = false;

        GridView1.DataBind();

        StringBuilder sb = new StringBuilder();

        for (int k = 0; k < GridView1.Columns.Count; k++)

        {

            //add separator

            sb.Append(GridView1.Columns[k].HeaderText + ',');

        }

        //append new line

        sb.Append("\r\n");

        for (int i = 0; i < GridView1.Rows.Count; i++)

        {

            for (int k = 0; k < GridView1.Columns.Count; k++)

            {

                //add separator

                sb.Append(GridView1.Rows[i].Cells[k].Text + ',');

            }

            //append new line

            sb.Append("\r\n");

        }

        Response.Output.Write(sb.ToString());

        Response.Flush();

        Response.End();

    }

          

    VB.Net

    Protected Sub btnExportCSV_Click(ByVal sender As Object,

    ByVal e As EventArgs)

     Response.Clear()

     Response.Buffer = True

     Response.AddHeader("content-disposition",

     "attachment;filename=GridViewExport.csv")

     Response.Charset = ""

     Response.ContentType = "application/text"

     GridView1.AllowPaging = False

     GridView1.DataBind()

     Dim sb As New StringBuilder()

     For k As Integer = 0 To GridView1.Columns.Count - 1

      'add separator

      sb.Append(GridView1.Columns(k).HeaderText + ","c)

     Next

     'append new line

     sb.Append(vbCr & vbLf)

     For i As Integer = 0 To GridView1.Rows.Count - 1

      For k As Integer = 0 To GridView1.Columns.Count - 1

       'add separator

       sb.Append(GridView1.Rows(i).Cells(k).Text + ","c)

      Next

      'append new line

      sb.Append(vbCr & vbLf)

     Next

     Response.Output.Write(sb.ToString())

     Response.Flush()

     Response.End()

    End Sub

    The Output Exported File



     

    GridView data exported to CSV File



     

    When you run the application first time and click export you might receive the following error



     

    Error encountered when you click export



     

    To avoid the error you will need to add this event which ensures that the GridView is Rendered before exporting.

      

    C#

    public override void VerifyRenderingInServerForm(Control control)

    {

        /* Verifies that the control is rendered */

    }

    VB.Net

    Public Overloads Overrides Sub VerifyRenderingInServerForm

    (ByVal control As Control)

        ' Verifies that the control is rendered

    End Sub

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 20, 2009 4:31 AM

All replies

  • User-137840861 posted

    In this article, I will explain how to export GridView to Word, Excel, PDF and CSV formats.

    Exporting to Word, Excel and CSV can be easily achieved using ASP.Net without any third party tools, but for exporting GridView to PDF I am using iTextSharp which is a free library for exporting html to PDF.

    To start with I have a GridView in which I am showing Customers records from the NorthWind Database.

    The HTML markup of the GridView is as shown below

    <asp:GridView ID="GridView1" runat="server"

        AutoGenerateColumns = "false" Font-Names = "Arial"

        Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B" 

        HeaderStyle-BackColor = "green" AllowPaging ="true"  

        OnPageIndexChanging = "OnPaging" >

       <Columns>

        <asp:BoundField ItemStyle-Width = "150px" DataField = "CustomerID"

        HeaderText = "CustomerID" />

        <asp:BoundField ItemStyle-Width = "150px" DataField = "City"

        HeaderText = "City"/>

        <asp:BoundField ItemStyle-Width = "150px" DataField = "Country"

        HeaderText = "Country"/>

        <asp:BoundField ItemStyle-Width = "150px" DataField = "PostalCode"

        HeaderText = "PostalCode"/>

       </Columns>

    </asp:GridView>

    In the figure below the GridView is shown with four buttons

    1.     Export To Word

    2.     Export To Excel

    3.     Export To PDF

    4.     Export To CSV



     

    GridView with Sample Data



     

    Export to Microsoft Word Format

    C#

    protected void btnExportWord_Click(object sender, EventArgs e)

    {

        Response.Clear();

        Response.Buffer = true;

        Response.AddHeader("content-disposition",

        "attachment;filename=GridViewExport.doc");

        Response.Charset = "";

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

        StringWriter sw= new StringWriter();

        HtmlTextWriter hw = new HtmlTextWriter(sw);

        GridView1.AllowPaging = false;

        GridView1.DataBind();

        GridView1.RenderControl(hw);

        Response.Output.Write(sw.ToString());

        Response.Flush();

        Response.End();

    }

    VB.Net

    Protected Sub btnExportWord_Click(ByVal sender As Object,

        ByVal e As EventArgs)

            Response.Clear()

            Response.Buffer = True

            Response.AddHeader("content-disposition",

            "attachment;filename=GridViewExport.doc")

            Response.Charset = ""

            Response.ContentType = "application/vnd.ms-word "

            Dim sw As New StringWriter()

            Dim hw As New HtmlTextWriter(sw)

            GridView1.AllowPaging = False

            GridView1.DataBind()

            GridView1.RenderControl(hw)

            Response.Output.Write(sw.ToString())

            Response.Flush()

            Response.End()

        End Sub

    The above function renders the GridView contents as Microsoft Word format. You will notice I have disabled paging before exporting, so that all the pages are exported.  

    The Output Exported File



     

    GridView data exported to Word Document



     

    Export to Microsoft Excel Format

    For exporting the document to Excel if you do it directly as done in case of word the row background color is applied throughout to all the columns in the Excel Sheet hence in order to avoid it. I have done a workaround below.

    First I am changing the background color of each row back to white.

    Then I am applying the background color to each individual cell rather than the whole row. Thus when you export now you will notice that the formatting is applied only to the GridView cells and not all

    Also I am applying textmode style class to all cells and then adding the style CSS class to the GridView before rendering it, this ensures that all the contents of GridView are rendered as text.

     

    protected void btnExportExcel_Click(object sender, EventArgs e)

    {

    Response.Clear();

    Response.Buffer = true;

    Response.AddHeader("content-disposition",

    "attachment;filename=GridViewExport.xls");

    Response.Charset = "";

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

    StringWriter sw = new StringWriter();

    HtmlTextWriter hw = new HtmlTextWriter(sw);

    GridView1.AllowPaging = false;

    GridView1.DataBind();

    //Change the Header Row back to white color

    GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");

    //Apply style to Individual Cells

    GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");

    GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");

    GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");

    GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");  

    for (int i = 0; i < GridView1.Rows.Count;i++ )

    {

        GridViewRow row = GridView1.Rows[i];

        //Change Color back to white

        row.BackColor = System.Drawing.Color.White;

        //Apply text style to each Row

        row.Attributes.Add("class", "textmode");

        //Apply style to Individual Cells of Alternating Row

        if (i % 2 != 0)

        {

            row.Cells[0].Style.Add("background-color", "#C2D69B");

            row.Cells[1].Style.Add("background-color", "#C2D69B");

            row.Cells[2].Style.Add("background-color", "#C2D69B");

            row.Cells[3].Style.Add("background-color", "#C2D69B");  

        }

    }

    GridView1.RenderControl(hw);

    //style to format numbers to string

    string style = @"<style> .textmode { mso-number-format:\@; } </style>";

    Response.Write(style);

    Response.Output.Write(sw.ToString());

    Response.Flush();

    Response.End();

    }

      

    VB.Net

    Protected Sub btnExportExcel_Click(ByVal sender As Object,

    ByVal e As EventArgs)

      Response.Clear()

      Response.Buffer = True

      Response.AddHeader("content-disposition",

      "attachment;filename=GridViewExport.xls")

      Response.Charset = ""

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

     

      Dim sw As New StringWriter()

      Dim hw As New HtmlTextWriter(sw)

      GridView1.AllowPaging = False

      GridView1.DataBind()

      'Change the Header Row back to white color

      GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF")

      'Apply style to Individual Cells

      GridView1.HeaderRow.Cells(0).Style.Add("background-color", "green")

      GridView1.HeaderRow.Cells(1).Style.Add("background-color", "green")

      GridView1.HeaderRow.Cells(2).Style.Add("background-color", "green")

      GridView1.HeaderRow.Cells(3).Style.Add("background-color", "green")

      For i As Integer = 0 To GridView1.Rows.Count - 1

       Dim row As GridViewRow = GridView1.Rows(i)

       'Change Color back to white

       row.BackColor = System.Drawing.Color.White

       'Apply text style to each Row

       row.Attributes.Add("class", "textmode")

       'Apply style to Individual Cells of Alternating Row

       If i Mod 2 <> 0 Then

        row.Cells(0).Style.Add("background-color", "#C2D69B")

        row.Cells(1).Style.Add("background-color", "#C2D69B")

        row.Cells(2).Style.Add("background-color", "#C2D69B")

        row.Cells(3).Style.Add("background-color", "#C2D69B")

       End If

      Next

      GridView1.RenderControl(hw)

      'style to format numbers to string

      Dim style As String = "<style>.textmode{mso-number-format:\@;}</style>"

      Response.Write(style)

      Response.Output.Write(sw.ToString())

      Response.Flush()

      Response.End()

    End Sub

    The Output Exported File



     

    GridView data exported to Excel Document



     

    Export to Portable Document Format

    For exporting the GridView to PDF I am using the iTextSharp Library. You will need to Add Reference for the iTextSharp Library in your Website.

    Then import the following Namespaces

    C#

    using iTextSharp.text;

    using iTextSharp.text.pdf;

    using iTextSharp.text.html;

    using iTextSharp.text.html.simpleparser;

    VB.Net

    Imports iTextSharp.text

    Imports iTextSharp.text.pdf

    Imports iTextSharp.text.html

    Imports iTextSharp.text.html.simpleparser

    By default the iTextSharp Library does not support background color of table cells or table rows

    Hence when you render it as PDF your GridView is rendered without any formatting.

    Recently I read an article on hamang.net where the author has provided the snippet to modify the iTextSharp so that it exports the HTML with background color.

    For this tutorial, I have already modified the iTextSharp Library DLL so that the GridView is rendered with all the background color used. You can refer the code for exporting GridView to PDF below

      

                  

    C#

    protected void btnExportPDF_Click(object sender, EventArgs e)

    {

        Response.ContentType = "application/pdf";

        Response.AddHeader("content-disposition",

         "attachment;filename=GridViewExport.pdf");

        Response.Cache.SetCacheability(HttpCacheability.NoCache);

        StringWriter sw = new StringWriter();

        HtmlTextWriter hw = new HtmlTextWriter(sw);

        GridView1.AllowPaging = false;

        GridView1.DataBind();

        GridView1.RenderControl(hw);

        StringReader sr = new StringReader(sw.ToString());

        Document pdfDoc = new Document(PageSize.A4, 10f,10f,10f,0f);

        HTMLWorker htmlparser = new HTMLWorker(pdfDoc);

        PdfWriter.GetInstance(pdfDoc, Response.OutputStream);

        pdfDoc.Open();

        htmlparser.Parse(sr);

        pdfDoc.Close();

        Response.Write(pdfDoc);

        Response.End(); 

    }

    VB.Net

    Protected Sub btnExportPDF_Click(ByVal sender As Object,

    ByVal e As EventArgs)

     Response.ContentType = "application/pdf"

     Response.AddHeader("content-disposition",

     "attachment;filename=GridViewExport.pdf")

     Response.Cache.SetCacheability(HttpCacheability.NoCache)

     Dim sw As New StringWriter()

     Dim hw As New HtmlTextWriter(sw)

     GridView1.AllowPaging = False

     GridView1.DataBind()

     GridView1.RenderControl(hw)

     Dim sr As New StringReader(sw.ToString())

     Dim pdfDoc As New Document(PageSize.A4, 10.0F, 10.0F, 10.0F, 0.0F)

     Dim htmlparser As New HTMLWorker(pdfDoc)

     PdfWriter.GetInstance(pdfDoc, Response.OutputStream)

     pdfDoc.Open()

     htmlparser.Parse(sr)

     pdfDoc.Close()

     Response.Write(pdfDoc)

     Response.End()

    End Sub

    The Output Exported File



     

    GridView data exported to PDF Document



     

    Export to Text/CSV

    Finally comes exporting GridView to CSV or Text File delimited by a separator like comma.

    To export the GridView as CSV, I am running a two for loops. While looping through the GridView columns and appending comma after each column and while looping through rows appending new line character. Refer the code below.

    C#

    protected void btnExportCSV_Click(object sender, EventArgs e)

    {

        Response.Clear();

        Response.Buffer = true;

        Response.AddHeader("content-disposition",

         "attachment;filename=GridViewExport.csv");

        Response.Charset = "";

        Response.ContentType = "application/text";

        GridView1.AllowPaging = false;

        GridView1.DataBind();

        StringBuilder sb = new StringBuilder();

        for (int k = 0; k < GridView1.Columns.Count; k++)

        {

            //add separator

            sb.Append(GridView1.Columns[k].HeaderText + ',');

        }

        //append new line

        sb.Append("\r\n");

        for (int i = 0; i < GridView1.Rows.Count; i++)

        {

            for (int k = 0; k < GridView1.Columns.Count; k++)

            {

                //add separator

                sb.Append(GridView1.Rows[i].Cells[k].Text + ',');

            }

            //append new line

            sb.Append("\r\n");

        }

        Response.Output.Write(sb.ToString());

        Response.Flush();

        Response.End();

    }

          

    VB.Net

    Protected Sub btnExportCSV_Click(ByVal sender As Object,

    ByVal e As EventArgs)

     Response.Clear()

     Response.Buffer = True

     Response.AddHeader("content-disposition",

     "attachment;filename=GridViewExport.csv")

     Response.Charset = ""

     Response.ContentType = "application/text"

     GridView1.AllowPaging = False

     GridView1.DataBind()

     Dim sb As New StringBuilder()

     For k As Integer = 0 To GridView1.Columns.Count - 1

      'add separator

      sb.Append(GridView1.Columns(k).HeaderText + ","c)

     Next

     'append new line

     sb.Append(vbCr & vbLf)

     For i As Integer = 0 To GridView1.Rows.Count - 1

      For k As Integer = 0 To GridView1.Columns.Count - 1

       'add separator

       sb.Append(GridView1.Rows(i).Cells(k).Text + ","c)

      Next

      'append new line

      sb.Append(vbCr & vbLf)

     Next

     Response.Output.Write(sb.ToString())

     Response.Flush()

     Response.End()

    End Sub

    The Output Exported File



     

    GridView data exported to CSV File



     

    When you run the application first time and click export you might receive the following error



     

    Error encountered when you click export



     

    To avoid the error you will need to add this event which ensures that the GridView is Rendered before exporting.

      

    C#

    public override void VerifyRenderingInServerForm(Control control)

    {

        /* Verifies that the control is rendered */

    }

    VB.Net

    Public Overloads Overrides Sub VerifyRenderingInServerForm

    (ByVal control As Control)

        ' Verifies that the control is rendered

    End Sub

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 20, 2009 4:31 AM
  • User-1171043462 posted

    In this article, I will explain how to export GridView to Word, Excel, PDF and CSV formats.

    Exporting to Word, Excel and CSV can be easily achieved using ASP.Net without any third party tools, but for exporting GridView to PDF I am using iTextSharp which is a free library for exporting html to PDF.

    To start with I have a GridView in which I am showing Customers records from the NorthWind Database.

    The HTML markup of the GridView is as shown below

    <asp:GridView ID="GridView1" runat="server"

        AutoGenerateColumns = "false" Font-Names = "Arial"

        Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B" 

        HeaderStyle-BackColor = "green" AllowPaging ="true"  

        OnPageIndexChanging = "OnPaging" >

       <Columns>

        <asp:BoundField ItemStyle-Width = "150px" DataField = "CustomerID"

        HeaderText = "CustomerID" />

        <asp:BoundField ItemStyle-Width = "150px" DataField = "City"

        HeaderText = "City"/>

        <asp:BoundField ItemStyle-Width = "150px" DataField = "Country"

        HeaderText = "Country"/>

        <asp:BoundField ItemStyle-Width = "150px" DataField = "PostalCode"

        HeaderText = "PostalCode"/>

       </Columns>

    </asp:GridView>

    In the figure below the GridView is shown with four buttons

    1.     Export To Word

    2.     Export To Excel

    3.     Export To PDF

    4.     Export To CSV



     

    GridView with Sample Data



     

    Export to Microsoft Word Format

    C#

    protected void btnExportWord_Click(object sender, EventArgs e)

    {

        Response.Clear();

        Response.Buffer = true;

        Response.AddHeader("content-disposition",

        "attachment;filename=GridViewExport.doc");

        Response.Charset = "";

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

        StringWriter sw= new StringWriter();

        HtmlTextWriter hw = new HtmlTextWriter(sw);

        GridView1.AllowPaging = false;

        GridView1.DataBind();

        GridView1.RenderControl(hw);

        Response.Output.Write(sw.ToString());

        Response.Flush();

        Response.End();

    }

    VB.Net

    Protected Sub btnExportWord_Click(ByVal sender As Object,

        ByVal e As EventArgs)

            Response.Clear()

            Response.Buffer = True

            Response.AddHeader("content-disposition",

            "attachment;filename=GridViewExport.doc")

            Response.Charset = ""

            Response.ContentType = "application/vnd.ms-word "

            Dim sw As New StringWriter()

            Dim hw As New HtmlTextWriter(sw)

            GridView1.AllowPaging = False

            GridView1.DataBind()

            GridView1.RenderControl(hw)

            Response.Output.Write(sw.ToString())

            Response.Flush()

            Response.End()

        End Sub

    The above function renders the GridView contents as Microsoft Word format. You will notice I have disabled paging before exporting, so that all the pages are exported.  

    The Output Exported File



     

    GridView data exported to Word Document



     

    Export to Microsoft Excel Format

    For exporting the document to Excel if you do it directly as done in case of word the row background color is applied throughout to all the columns in the Excel Sheet hence in order to avoid it. I have done a workaround below.

    First I am changing the background color of each row back to white.

    Then I am applying the background color to each individual cell rather than the whole row. Thus when you export now you will notice that the formatting is applied only to the GridView cells and not all

    Also I am applying textmode style class to all cells and then adding the style CSS class to the GridView before rendering it, this ensures that all the contents of GridView are rendered as text.

     

    protected void btnExportExcel_Click(object sender, EventArgs e)

    {

    Response.Clear();

    Response.Buffer = true;

    Response.AddHeader("content-disposition",

    "attachment;filename=GridViewExport.xls");

    Response.Charset = "";

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

    StringWriter sw = new StringWriter();

    HtmlTextWriter hw = new HtmlTextWriter(sw);

    GridView1.AllowPaging = false;

    GridView1.DataBind();

    //Change the Header Row back to white color

    GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");

    //Apply style to Individual Cells

    GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");

    GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");

    GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");

    GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");  

    for (int i = 0; i < GridView1.Rows.Count;i++ )

    {

        GridViewRow row = GridView1.Rows[i];

        //Change Color back to white

        row.BackColor = System.Drawing.Color.White;

        //Apply text style to each Row

        row.Attributes.Add("class", "textmode");

        //Apply style to Individual Cells of Alternating Row

        if (i % 2 != 0)

        {

            row.Cells[0].Style.Add("background-color", "#C2D69B");

            row.Cells[1].Style.Add("background-color", "#C2D69B");

            row.Cells[2].Style.Add("background-color", "#C2D69B");

            row.Cells[3].Style.Add("background-color", "#C2D69B");  

        }

    }

    GridView1.RenderControl(hw);

    //style to format numbers to string

    string style = @"<style> .textmode { mso-number-format:\@; } </style>";

    Response.Write(style);

    Response.Output.Write(sw.ToString());

    Response.Flush();

    Response.End();

    }

      

    VB.Net

    Protected Sub btnExportExcel_Click(ByVal sender As Object,

    ByVal e As EventArgs)

      Response.Clear()

      Response.Buffer = True

      Response.AddHeader("content-disposition",

      "attachment;filename=GridViewExport.xls")

      Response.Charset = ""

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

     

      Dim sw As New StringWriter()

      Dim hw As New HtmlTextWriter(sw)

      GridView1.AllowPaging = False

      GridView1.DataBind()

      'Change the Header Row back to white color

      GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF")

      'Apply style to Individual Cells

      GridView1.HeaderRow.Cells(0).Style.Add("background-color", "green")

      GridView1.HeaderRow.Cells(1).Style.Add("background-color", "green")

      GridView1.HeaderRow.Cells(2).Style.Add("background-color", "green")

      GridView1.HeaderRow.Cells(3).Style.Add("background-color", "green")

      For i As Integer = 0 To GridView1.Rows.Count - 1

       Dim row As GridViewRow = GridView1.Rows(i)

       'Change Color back to white

       row.BackColor = System.Drawing.Color.White

       'Apply text style to each Row

       row.Attributes.Add("class", "textmode")

       'Apply style to Individual Cells of Alternating Row

       If i Mod 2 <> 0 Then

        row.Cells(0).Style.Add("background-color", "#C2D69B")

        row.Cells(1).Style.Add("background-color", "#C2D69B")

        row.Cells(2).Style.Add("background-color", "#C2D69B")

        row.Cells(3).Style.Add("background-color", "#C2D69B")

       End If

      Next

      GridView1.RenderControl(hw)

      'style to format numbers to string

      Dim style As String = "<style>.textmode{mso-number-format:\@;}</style>"

      Response.Write(style)

      Response.Output.Write(sw.ToString())

      Response.Flush()

      Response.End()

    End Sub

    The Output Exported File



     

    GridView data exported to Excel Document



     

    Export to Portable Document Format

    For exporting the GridView to PDF I am using the iTextSharp Library. You will need to Add Reference for the iTextSharp Library in your Website.

    Then import the following Namespaces

    C#

    using iTextSharp.text;

    using iTextSharp.text.pdf;

    using iTextSharp.text.html;

    using iTextSharp.text.html.simpleparser;

    VB.Net

    Imports iTextSharp.text

    Imports iTextSharp.text.pdf

    Imports iTextSharp.text.html

    Imports iTextSharp.text.html.simpleparser

    By default the iTextSharp Library does not support background color of table cells or table rows

    Hence when you render it as PDF your GridView is rendered without any formatting.

    Recently I read an article on hamang.net where the author has provided the snippet to modify the iTextSharp so that it exports the HTML with background color.

    For this tutorial, I have already modified the iTextSharp Library DLL so that the GridView is rendered with all the background color used. You can refer the code for exporting GridView to PDF below

      

                  

    C#

    protected void btnExportPDF_Click(object sender, EventArgs e)

    {

        Response.ContentType = "application/pdf";

        Response.AddHeader("content-disposition",

         "attachment;filename=GridViewExport.pdf");

        Response.Cache.SetCacheability(HttpCacheability.NoCache);

        StringWriter sw = new StringWriter();

        HtmlTextWriter hw = new HtmlTextWriter(sw);

        GridView1.AllowPaging = false;

        GridView1.DataBind();

        GridView1.RenderControl(hw);

        StringReader sr = new StringReader(sw.ToString());

        Document pdfDoc = new Document(PageSize.A4, 10f,10f,10f,0f);

        HTMLWorker htmlparser = new HTMLWorker(pdfDoc);

        PdfWriter.GetInstance(pdfDoc, Response.OutputStream);

        pdfDoc.Open();

        htmlparser.Parse(sr);

        pdfDoc.Close();

        Response.Write(pdfDoc);

        Response.End(); 

    }

    VB.Net

    Protected Sub btnExportPDF_Click(ByVal sender As Object,

    ByVal e As EventArgs)

     Response.ContentType = "application/pdf"

     Response.AddHeader("content-disposition",

     "attachment;filename=GridViewExport.pdf")

     Response.Cache.SetCacheability(HttpCacheability.NoCache)

     Dim sw As New StringWriter()

     Dim hw As New HtmlTextWriter(sw)

     GridView1.AllowPaging = False

     GridView1.DataBind()

     GridView1.RenderControl(hw)

     Dim sr As New StringReader(sw.ToString())

     Dim pdfDoc As New Document(PageSize.A4, 10.0F, 10.0F, 10.0F, 0.0F)

     Dim htmlparser As New HTMLWorker(pdfDoc)

     PdfWriter.GetInstance(pdfDoc, Response.OutputStream)

     pdfDoc.Open()

     htmlparser.Parse(sr)

     pdfDoc.Close()

     Response.Write(pdfDoc)

     Response.End()

    End Sub

    The Output Exported File



     

    GridView data exported to PDF Document



     

    Export to Text/CSV

    Finally comes exporting GridView to CSV or Text File delimited by a separator like comma.

    To export the GridView as CSV, I am running a two for loops. While looping through the GridView columns and appending comma after each column and while looping through rows appending new line character. Refer the code below.

    C#

    protected void btnExportCSV_Click(object sender, EventArgs e)

    {

        Response.Clear();

        Response.Buffer = true;

        Response.AddHeader("content-disposition",

         "attachment;filename=GridViewExport.csv");

        Response.Charset = "";

        Response.ContentType = "application/text";

        GridView1.AllowPaging = false;

        GridView1.DataBind();

        StringBuilder sb = new StringBuilder();

        for (int k = 0; k < GridView1.Columns.Count; k++)

        {

            //add separator

            sb.Append(GridView1.Columns[k].HeaderText + ',');

        }

        //append new line

        sb.Append("\r\n");

        for (int i = 0; i < GridView1.Rows.Count; i++)

        {

            for (int k = 0; k < GridView1.Columns.Count; k++)

            {

                //add separator

                sb.Append(GridView1.Rows[i].Cells[k].Text + ',');

            }

            //append new line

            sb.Append("\r\n");

        }

        Response.Output.Write(sb.ToString());

        Response.Flush();

        Response.End();

    }

          

    VB.Net

    Protected Sub btnExportCSV_Click(ByVal sender As Object,

    ByVal e As EventArgs)

     Response.Clear()

     Response.Buffer = True

     Response.AddHeader("content-disposition",

     "attachment;filename=GridViewExport.csv")

     Response.Charset = ""

     Response.ContentType = "application/text"

     GridView1.AllowPaging = False

     GridView1.DataBind()

     Dim sb As New StringBuilder()

     For k As Integer = 0 To GridView1.Columns.Count - 1

      'add separator

      sb.Append(GridView1.Columns(k).HeaderText + ","c)

     Next

     'append new line

     sb.Append(vbCr & vbLf)

     For i As Integer = 0 To GridView1.Rows.Count - 1

      For k As Integer = 0 To GridView1.Columns.Count - 1

       'add separator

       sb.Append(GridView1.Rows(i).Cells(k).Text + ","c)

      Next

      'append new line

      sb.Append(vbCr & vbLf)

     Next

     Response.Output.Write(sb.ToString())

     Response.Flush()

     Response.End()

    End Sub

    The Output Exported File



     

    GridView data exported to CSV File



     

    When you run the application first time and click export you might receive the following error



     

    Error encountered when you click export



     

    To avoid the error you will need to add this event which ensures that the GridView is Rendered before exporting.

      

    C#

    public override void VerifyRenderingInServerForm(Control control)

    {

        /* Verifies that the control is rendered */

    }

    VB.Net

    Public Overloads Overrides Sub VerifyRenderingInServerForm

    (ByVal control As Control)

        ' Verifies that the control is rendered

    End Sub

    It would be simpler to give the link instead of copying the whole page

    http://www.aspsnippets.com/post/2009/03/14/Export-GridView-To-WordExcelPDFCSV-in-ASPNet.aspx

    [:|]

    Saturday, June 20, 2009 3:10 PM
  • User-1321740255 posted

    Dear All,

    I have also used the same and it is working fine.

    But can I set the page dimensions in the word like "Landscape / Portrait" , margins etc.


    Thank you a lot in advance.

    Monday, August 10, 2009 7:01 AM
  • User1180017940 posted

    I agree, this works GREAT!

     

    The only issue I'm running into is ItemTemplates.  I have some ItemTemplates for the Gridview, and can't seem to figure out how to overcome that when exporting.

    Thursday, March 4, 2010 3:59 PM
  • User-663497827 posted

    Hi Guys,

    Needing a bit of help here. This is not quite working for me. My grid is empty (that is the excel file) when I RE-bind to the data after setting AllowPaging to false.Anything I'm not doing correctly?


    UPDATE- GridView1.AllowPaging= false is also not working!!!

    Many Thanks

    UPDATE 2 =SORTED! including this - GetDataset1(txtSearch.Text.Trim()) - the results of the query ... seem to solve it !


    ---Grid---

    <asp:GridView ID="GridView1" runat="server" EmptyDataText="Nothing Found"
    OnSelectedIndexChanged="gridviewServices_SelectedIndexChanged" CssClass="gridview1_standard"
    DataKeyNames="ID,Name" OnPageIndexChanging="GridView1_PageIndexChanging"
    PageSize="15" AutoGenerateColumns="False" AllowPaging="True">
    <FooterStyle CssClass="gridview_footer" />

    <Columns>
    <asp:BoundField DataField="ID" HeaderText="ID" />
    <asp:BoundField DataField="Name" HeaderText="Name" />
    <asp:BoundField DataField="Sector" HeaderText="Sector Type" />
    <asp:BoundField DataField="status" HeaderText="Status" />
    </Columns>
    <SelectedRowStyle CssClass="gridview1_selected" />
    <HeaderStyle CssClass="gridview1_header" />
    <AlternatingRowStyle CssClass="gridview1_alternate" />
    </asp:GridView>


    ---codeBehind---


    protected void Image1_Click(object sender, ImageClickEventArgs e)
    {

    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=ExportedData.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";

    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);

    GridView1.AllowPaging = false;
    GridView1.DataBind();
    GridView1.RenderControl(hw);
    Response.Output.Write(sw.ToString());
    Response.Flush();
    Response.End();

    }



    Monday, March 15, 2010 8:07 AM
  • User-180413485 posted

    But there are bug in code as listed as below:

    ........................................................................

     Dim pdfDoc As New Document(PageSize.A4, 10.0F, 10.0F, 10.0F, 0.0F)

            Dim htmlparser As New HTMLWorker(pdfDoc)

            PdfWriter.GetInstance(pdfDoc, Response.OutputStream)

    .........................................................................

    and there are also green underline status as listed as below:

    Imports iTextSharp.text
    Imports iTextSharp.text.pdf
    Imports iTextSharp.text.html
    Imports iTextSharp.text.html.simpleparser

    Thank you for your kindly helpness

    Thursday, June 23, 2011 11:56 PM