ASP.NET Export GridView to Excel

Many times there is requirement to export the data from Gridview to Excel or PDF. Here I am going to discuss export to excel. When initially this was the requirement I thought there will be ready to use code snippet MSDN perhaps I couldn’t find that. After searching other sites I settled on following approach.

Here along with exporting gridview data I also wanted to add some additional information say report name and report generation date. Also there are controls in gridview which I need to handle while exporting it.

Basically I have created two functions one for export and other for handling the controls in the Gridview cells.

public static void Export(string fileName, GridView gv,string ReportName,DateTime ExportDate )
    {
        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 table to contain the grid
                Table table = new Table();
                TableRow HeaderRow = new TableRow();
                TableRow HeaderRow1 = new TableRow();
                TableCell cell = new TableCell();
                TableCell cell1 = new TableCell();
                TableCell cell2 = new TableCell();
                TableCell cell3 = new TableCell();
                //  include the gridline settings
                table.GridLines = gv.GridLines;
                cell.Text = "Report Name";
                cell1.Text = ReportName;
                cell2.Text = "Date:";
                cell3.Text =Convert.ToString( ExportDate);
                cell.ColumnSpan = gv.HeaderRow.Cells.Count - 2;
               
                cell2.ColumnSpan = gv.HeaderRow.Cells.Count - 2;
                HeaderRow.Cells.Add(cell);
                HeaderRow.Cells.Add(cell1);
                HeaderRow1.Cells.Add(cell2);
                HeaderRow1.Cells.Add(cell3);
                table.Rows.Add(HeaderRow);
                table.Rows.Add(HeaderRow1);
                cell3.HorizontalAlign = HorizontalAlign.Left;
                cell2.HorizontalAlign = HorizontalAlign.Right;
                cell.HorizontalAlign = HorizontalAlign.Right;
                cell.Font.Bold = true;
                cell2.Font.Bold = true;
                cell.Font.Size = FontUnit.Small;
                cell2.Font.Size = FontUnit.Small;
    //SET CELL BACKGROUND
                foreach (TableCell tc in gv.HeaderRow.Cells)
                    tc.BackColor = System.Drawing.Color.FromArgb(197, 217, 241);
                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    PrepareControlForExport(gv.HeaderRow);
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {

                   PrepareControlForExport(row);
                    row.VerticalAlign = VerticalAlign.Top;
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    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();
            }
        }
    }


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())
            {
               PrepareControlForExport(current);
            }
        }
    }

Indexed view

Views are very useful for fetching data from multiple tables. If data is very huge, then performance of view goes very down. Microsoft has provided now ability to increase performance by indexing view. On MSDN it is explained in great details at http://msdn.microsoft.com/en-us/library/aa933148%28SQL.80%29.aspx

How to create

We will go by example we will create two tables which will have large data.

CREATE TABLE Items(

ItemID INT PRIMARY KEY,

Dsc VARCHAR(20),

)

GO

CREATE TABLE CustOrders(

OrderID BIGINT PRIMARY KEY,

CustNo BIGINT,

ItemID VARCHAR(20),

QTY INT)

GO

CREATE VIEW [dbo].[MyView]

WITH SCHEMABINDING

AS

SELECT dbo.CustOrders.OrderID, dbo.CustOrders.CustNo, dbo.Items.Dsc, dbo.CustOrders.QTY

FROM dbo.CustOrders INNER JOIN

dbo.Items ON dbo.CustOrders.ItemID = dbo.Items.ItemID

GO

Indexed views:

•      Must be created the WITH SCHEMABINDING view option
•      May only refer to base tables in the same database.
•      If there is a GROUP BY clause, the view may not have a HAVING, CUBE, or ROLLUP.
•      May not have an OUTER JOIN clause.
•      May not have a UNION.
•      May not have DISTINCT or TOP clauses
•      May not have full-text predicates such as CONATINSTABLE
•      May not have a ROWSET function such as OPENROWSET
•      May not use derived tables or subqueries.
•      Must be created with ANSI_NULLS ON and QUOTED_IDENTIFIER ON

RadioButtonList add title for items

Radiobuttonlist displays same title as of Item’s text. There was situation to display more informative title for Radiobuttonlist Items. Here is simple trick I found out.

 <asp:RadioButtonList ID=”rdoBtnListOptions” runat=”server” RepeatDirection=”Horizontal”>
<asp:ListItem Value=”1?>One</asp:ListItem>
<asp:ListItem Value=”2?>Two</asp:ListItem>
</asp:RadioButtonList>

 In code behind file you can add

foreach (ListItem oItem in rdoBtnListOptions.Items)
{
if (oItem.Value == “1?)
oItem.Attributes.Add(“OnMouseOver”, “this.title=’This is option one.’”);
else
oItem.Attributes.Add(“OnMouseOver”, “this.title= This is option two.”);
} 

Delete all data in the database SQL server

Many times there is scenario where you need to delete all of the data in your database, you can do it easily using the MSForEachTable stored procedure. First you need disable referential integrity checks so you can delete data from parent tables.

-- disable referential integrity

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

GO

EXEC sp_MSForEachTable 'DELETE FROM ?'

GO

-- enable referential integrity again

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO