Export Gridview to Excel


The focus of the article is the Export to Excel functionality – the Gridview and it’s data binding are only for demonstrating the Export functionality.The code fragments for the Export to Excel functionality below are not linked to the backend structure and can be re-used across projects for the common functionality provided.

Default.aspx :

           <asp:SqlDataSource ID="sqldsCustomers" runat="server" SelectCommand="select * from UserDetails"
            SelectCommandType="Text" ConnectionString="server=server;database=LegalWindow;Trusted_Connection=yes;" />
        <asp:GridView ID="gvCustomers" runat="server" AllowPaging="true" AllowSorting="true"
            PageSize="10″" DataSourceID="sqldsCustomers" GridLines="Both" />
        <asp:Button ID="btnExportGrid" runat="server" Text="Export to Excel" OnClick="BtnExportGrid_Click" />

Default.aspx.cs :

protected void BtnExportGrid_Click(object sender, EventArgs args)
{
//  pass the grid that for exporting …
GridViewExportUtil.Export(“Customers.xls”, this.gvCustomers);
}

GridViewExportUtil.cs

 public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader(
        "content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a form to contain the grid
                Table table = new Table();

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewExportUtil.PrepareControlForExport(row);
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //  render the htmlwriter into the response
                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }

     //Export Gridview Data to Excel File and Save Excel file to Server Folder Rather than
    //allowing user to Open or Save it.
    public static void ExportToFolder(string fileName, GridView gv)
    {

        System.Text.StringBuilder sb = new System.Text.StringBuilder();

        using (StringWriter sw = new StringWriter(sb))
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a form to contain the grid
                Table table = new Table();

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewExportUtil.PrepareControlForExport(row);
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //Create file
                System.IO.TextWriter w = new System.IO.StreamWriter(HttpContext.Current.Server.MapPath("~") + "\\" + fileName);
                w.Write(sb.ToString());
                w.Flush();
                w.Close();

            }
        }
    }
     

    private static void PrepareControlForExport(Control control)
    {
        for (int i = 0; i < control.Controls.Count; i++)
        {
            Control current = control.Controls[i];
            if (current is LinkButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
            }
            else if (current is ImageButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
            }
            else if (current is HyperLink)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
            }
            else if (current is DropDownList)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
            }
            else if (current is CheckBox)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
            }

            if (current.HasControls())
            {
                GridViewExportUtil.PrepareControlForExport(current);
            }
        }
    }

For the case where I actually wanted all rows exported, I turned off paging and rebound the grid before sending the control to the export utility.  For exporting just the first 100 rows, I set the PageSize property to 100 and then rebound.  You should probably use care when exporting the complete GridView just in case your grid has a few more rows that you are expecting.  Here is the code for the export button click handler

protected void BtnExportGrid_Click(object sender, EventArgs args)
    {
        if (this.rdoBtnListExportOptions.SelectedIndex == 1)
        {
            //  the user wants all rows exported, turn off paging
            //  and rebing the grid before sending it to the export
            //  utility
            this.gvCustomers.AllowPaging = false;
            this.gvCustomers.DataBind();
        }
        else if (this.rdoBtnListExportOptions.SelectedIndex == 2)
        {
            //  the user wants just the first 100,
            //  adjust the PageSize and rebind
            this.gvCustomers.PageSize = 100;
            this.gvCustomers.DataBind();
        }

        //  pass the grid that for exporting …
        GridViewExportUtil.Export("Customers.xls", this.gvCustomers);
    }

For More Extensive Study of GridView Export to Excel………………read more

kick it on DotNetKicks.com
Shout it

Advertisements

35 thoughts on “Export Gridview to Excel

  1. I am currently useing this utility however I want to pass the criteria the user selected sent to the excel as well. For Example.

    Job Cost Report
    Dates 9/21/09 – 9/27/2009
    Branches 2,3,4
    System Types A, B, C, D

    ID Name Customer Address City State
    1 Dave Big Bus. 123 Street PGH PA

  2. This works well. The only problem is that once the button is pushed, the page no longer posts back on any of the buttons. Links work fine, but none of the post backs work anymore.

    Any ideas?

    1. i have tried it in my project, i m not getting any such problem yet. might something wrong..u need to paste some code for that..

  3. i’ll tried the coding above to my system but not working..this is the error with red underline at code GridViewExportUtil.Export(“Customers.xls”, this.GridView1);”the name GridViewExportUtil does not exist in the current context”.how do i solve this prob?:-(

    1. have you created GridViewExportUtil class in app_code folder or not?

      this error indicating that class is not available..
      please check it & create the class first.

      Hope this will help
      Ramani Sandeep

  4. Good stuff – works well. One question though. I have a footer row added at data bind time containing grand totals and these do not get exported. I’m sure it’s because the object type of control is not in the if statement. I believe it to be a TableCell type and this cannot be cast as a LiteralControl so in the PrepareControlForExport method is gets passed over… Any workaround you can come up with?

    1. How you are adding Footer row in databind event?

      Because i have already covered the code to handle the footer row to be printed..

      1. Sorry for the long delay. In the RowDataBound event I call a method CreateGridViewRow which does nothing more than create a gridviewrow and then a series of Cells.Add each containing a value and some formatting…

  5. This is the one which I am looking for. Thanks a lot. BTW, As Joel said I can not even get the Footer in the Export. Showfooter=true and I add the summary info at the RowBound event. Any row which I added at run time is not getting into export.

  6. Thanks so much! I am hoping this will be easy 🙂 So, what I have is a gridview that I’ve set up using markup. However, I am adding two new rows at run-time – An extra header row that spans on top of sum summarized data (so that I can show MTD, Prev Year MTD, etc. on top of the repeated columns for Sales, GM$) I’ve also added summarized totals to an already existing footer row. The problem is that when I call the GridViewExportUtil method to export, it does not recognize the header row I’ve added nor does it recognize the already existing footer?

    Where the gridview is named ResultsGrid, here is the code I have added to the DataBound method to add the new header row to the grid and then call the second method to add the totals to the existing footer. Again, it seems that the method to export just does not see the new header row OR the existing footer row??

    Any help is GREATLY appreciated!! Thanks 🙂

    Christie

    protected void ResultsGrid_DataBound(object sender, EventArgs
    e)
    {
    GridView grid = sender as GridView;
    bool showCustomer = false;

    if (grid != null && grid.Rows.Count > 0)
    {
    GridViewRow row = new GridViewRow(0, -1,
    DataControlRowType.Header, DataControlRowState.Normal);

    switch (GroupingDropDown.SelectedIndex)
    {
    case 2:
    showCustomer = true;
    break;
    default:
    break;
    }

    TableHeaderCell product = new TableHeaderCell();
    if (showCustomer)
    {
    product.ColumnSpan = 2;
    }
    else
    {
    product.ColumnSpan = 1;
    }

    row.Cells.Add(product);

    TableHeaderCell currentMTD = new TableHeaderCell();
    currentMTD.ColumnSpan = 3;
    currentMTD.Text = “MTD”;
    row.Cells.Add(currentMTD);

    TableHeaderCell prevYearMTD = new TableHeaderCell();
    prevYearMTD.ColumnSpan = 3;
    prevYearMTD.Text = “Prev Year MTD”;
    row.Cells.Add(prevYearMTD);

    TableHeaderCell MTDDiff = new TableHeaderCell();
    MTDDiff.ColumnSpan = 3;
    MTDDiff.Font.Italic = true;
    MTDDiff.Text = “MTD Diff”;
    row.Cells.Add(MTDDiff);

    TableHeaderCell currentYTD = new TableHeaderCell();
    currentYTD.ColumnSpan = 3;
    currentYTD.Text = “YTD”;
    row.Cells.Add(currentYTD);

    TableHeaderCell prevYTD = new TableHeaderCell();
    prevYTD.ColumnSpan = 3;
    prevYTD.Text = “Prev YTD”;
    row.Cells.Add(prevYTD);

    TableHeaderCell YTDDiff = new TableHeaderCell();
    YTDDiff.ColumnSpan = 3;
    YTDDiff.Font.Italic = true;
    YTDDiff.Text = “YTD Diff”;
    row.Cells.Add(YTDDiff);

    Table t = grid.Controls[0] as Table;
    if (t != null)
    {
    t.Rows.AddAt(0, row);
    }

    grid = AddTotalRowToGridView(grid, true);
    }
    }

    protected GridView AddTotalRowToGridView(GridView Grid, bool
    ShowTotalTextInFirstColumn)
    {
    DataTable dtTotals = new DataTable();
    DataRow dr = dtTotals.NewRow();
    int intTemp = 0;
    int rowVal = 0;

    for (int iColumn = 0; iColumn < Grid.Rows[0].Cells.Count;
    iColumn++)
    {
    dtTotals.Columns.Add("Column" + iColumn);
    }

    for (int iRow = 0; iRow < Grid.Rows.Count; iRow++)
    {
    if (iRow == 0 && ShowTotalTextInFirstColumn)
    {
    dr["Column0"] = "Totals:";
    }

    if(Grid.Rows[iRow].RowType.Equals(DataControlRowType.DataRow))
    {
    for(int iCurrentColumn = 0; iCurrentColumn < Grid.Rows[iCurrentColumn].Cells.Count; iCurrentColumn++)
    {
    string rowValText = Grid.Rows[iRow].Cells[iCurrentColumn].Text;
    rowValText = rowValText.Replace("$", "");
    rowValText = rowValText.Replace("%", "");
    rowValText = rowValText.Replace("(", "-");
    rowValText = rowValText.Replace(")", "");
    rowValText = rowValText.Replace(",", "");
    bool success = int.TryParse(rowValText, out rowVal);

    if (!rowVal.Equals(0) && !
    iCurrentColumn.Equals(0) && ShowTotalTextInFirstColumn.Equals(true))
    {
    if(dr["Column" +
    iCurrentColumn].ToString().Equals(""))
    {
    dr["Column" + iCurrentColumn] = rowVal;
    }
    else
    {
    intTemp = Convert.ToInt32(dr["Column"
    + iCurrentColumn].ToString());
    intTemp += rowVal;
    dr["Column" + iCurrentColumn] = intTemp;
    intTemp = 0;
    }
    }
    }
    }
    }

    dtTotals.Rows.Add(dr);

    for(int iFooterColumn = 0; iFooterColumn < Grid.FooterRow.Cells.Count – 1; iFooterColumn++)
    {
    if (!
    dtTotals.Rows[0].ItemArray[iFooterColumn].ToString().Equals("") && !
    dtTotals.Rows[0].ItemArray[iFooterColumn].ToString().Equals("Totals:"))
    {
    Int32 total =
    Convert.ToInt32(dtTotals.Rows[0].ItemArray[iFooterColumn].ToString());
    Grid.FooterRow.Cells[iFooterColumn].Text = string.Format("{0:c0}", total);
    }
    }
    return Grid;
    }

  7. Thanx
    This code is really helpful to me. but I have question about storing images using this file upload in folder.

    B’coz I dont know how many images uploaded by User so I have to dynamically store images in folder. But I tried so much but I couldnt do this. I hope You will help me.

    Thanx
    Takecare
    JSK

    1. Hi, PrepareControlForExport method is not returning anything. it just do some processing so that we can export gridview. so thats it. it does not return anything at all. it sets few property of the class that can be later on used during export call. hope this helps.
      let me know if there is still a concern.

      Regards,
      Sandeep

  8. Hello,
    Can I hide particular headers in GridViewExportUtil.cs?? I am able to hide the data but not the headers…can u help??

  9. It’s amazing how many “How do I export from a GridView / DataSet to Excel” articles are out there, and how many of them recommend just sending out a load of values to the “HttpContext.Current.Response”.

    Here’s full source code to create *real* Office 2007 files, using the OpenXML libraries.
    You just need to include this class in your application, and call one function.

    DataSet ds = CreateSampleData();
    CreateExcelFile.CreateExcelDocument(myDataSet, “C:\\Sample.xlsx”);

    Full source code, and a demo application are provided, free of charge.
    http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

    Good luck !

  10. It’s very helpful !,
    i tried it, but in a grid with a hidden filed it shows an error..
    (Control ‘hfXid’ of type ‘HiddenField’ must be placed inside a form tag with runat=server. )
    at line( table.RenderControl(htw);)

    can you help me please!

    1. Can you tell show me the code of gridview and hiddenfield. so that able to find out what might be the issue.

      Regards,
      Sandeep Ramani

  11. Hi Sandeep,
    Code very much helpful to me but I am facing small issue.
    I used paging in my grid with another user control. When I applied gridview.allowpaging= false then excelsheet only showing header its eliminating the rows.
    When I removed gridview.allowpaging=false then the staring 10 rows are displaying in the ExcelSheet.
    Can you please give me any idea regarding this.

    Thanks
    Bhanu Prakash

  12. Hello, I am using the same code but after downloading file and when I open in excel it says

    “The file you are trying to open is in different format , please check if its corrupted”

    What am doing wrong ? I am exporting from SPGridView. Office version is 2010

    Rishi

  13. Break your 3 normal main meals into 5 smaller portion sizes – morning,
    snack, lunch, snack, dinner. Cucumber contains sterols which can help to lower
    cholesterol and prevent carbohydrates from converting to body fat.
    Hence, broccoli is a must-add ingredient in your vegetarian weight loss
    diet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s