GridView data to Excel exporter

By Sasha Kotlo

This class I developed will take your GridView control as input and generate the excel file for you using either a random name or the name you provide it.

Ok first of all here's the class code, feel free to edit it to suit your needs better:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.IO;
using System.Text;
using System.Data;

/// <summary>
/// Exports GridView data to Excel file
/// </summary>
public class ExcelExporterTool
{
public ExcelExporterTool()
{
//
// TODO: Add constructor logic here
//
}

    public string ExportGridViewDataToExcel(GridView source, bool randomFilename, string nameOfFile)
    {
         if (source != null)
        {
             string fileName = string.Empty;
            if (randomFilename)
            {
                fileName = Guid.NewGuid().ToString();
             }
             else
            {
                fileName = nameOfFile;
             }

             if (fileName != "")
            {
                fileName = HttpContext.Current.Server.MapPath(fileName + ".xls");
               StreamWriter excelExporter = File.CreateText(fileName);
               StringBuilder builder = new StringBuilder();

                 builder.Append("<table border=\"1\" bordercolor=\"#666666\" cellpadding=\"5\">");

                 // Parse the column names first
                 builder.Append("<tr>");
                 foreach (DataControlField column in source.Columns)
                 {
                     builder.AppendFormat("<th valign=\"middle\" style=\"background:#777; color:#fff; height:40px;\" align=\"left\">{0}</th>", column.HeaderText);
                 }
                 int alternativeCounter = 0;
                 builder.Append("</tr>");
                 
                 foreach (GridViewRow row in source.Rows)
                 {
                     alternativeCounter++;
                     builder.Append("<tr>");
                     foreach (TableCell column in row.Cells)
                     {
                          if (alternativeCounter < 2)
                          {
                              builder.AppendFormat("<td align=\"left\" valign=\"top\">{0}</td>", column.Text);
                         }
                         else if (alternativeCounter == 2)
                          {
                              builder.AppendFormat("<td style=\"background:#ccc;\" align=\"left\" valign=\"top\">{0}</td>", column.Text);
                         }
                     }
                      if (alternativeCounter == 2)
                    {
                        alternativeCounter = 0;
                     }
                      builder.Append("</tr>");
                 }

                 builder.Append("</table>");
                 excelExporter.Write(builder.ToString());
                 excelExporter.Close();

                 return fileName;
             }
             else
            {
                 throw new Exception("FileName is required!");
            }
        }
         else
        {
             throw new Exception("GridView control cannot be null or empty!");
        }
     }
}


and now let me show you how to use it. Here's my .aspx page which contains a simple GridView control which has data assigned from the Northwind database and it pulls a list of employees and a simple button with event handler attached to it which will export the data:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Export to Excel</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
             
DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1"
             
EnableModelValidation="True">
            <Columns>
                 <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID"
                     
InsertVisible="False" ReadOnly="True" SortExpression="EmployeeID" />
                 <asp:BoundField DataField="LastName" HeaderText="LastName"
                     
SortExpression="LastName" />
                 <asp:BoundField DataField="FirstName" HeaderText="FirstName"
                     
SortExpression="FirstName" />
                 <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
                 <asp:BoundField DataField="TitleOfCourtesy" HeaderText="TitleOfCourtesy"
                     
SortExpression="TitleOfCourtesy" />
                 <asp:BoundField DataField="BirthDate" HeaderText="BirthDate"
                     
SortExpression="BirthDate" />
                 <asp:BoundField DataField="HireDate" HeaderText="HireDate"
                     
SortExpression="HireDate" />
                 <asp:BoundField DataField="Address" HeaderText="Address"
                     
SortExpression="Address" />
                 <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                 <asp:BoundField DataField="Region" HeaderText="Region"
                     
SortExpression="Region" />
                 <asp:BoundField DataField="PostalCode" HeaderText="PostalCode"
                     
SortExpression="PostalCode" />
                 <asp:BoundField DataField="Country" HeaderText="Country"
                     
SortExpression="Country" />
                 <asp:BoundField DataField="HomePhone" HeaderText="HomePhone"
                     
SortExpression="HomePhone" />
                 <asp:BoundField DataField="Extension" HeaderText="Extension"
                     
SortExpression="Extension" />
                 <asp:BoundField DataField="Notes" HeaderText="Notes" SortExpression="Notes" />
                 <asp:BoundField DataField="ReportsTo" HeaderText="ReportsTo"
                     
SortExpression="ReportsTo" />
                 <asp:BoundField DataField="PhotoPath" HeaderText="PhotoPath"
                     
SortExpression="PhotoPath" />
            </Columns>
        </asp:GridView>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
             
ConnectionString="Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"
             
ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Employees]">
        </asp:SqlDataSource>

        <asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
    </div>
    </form>
</body>
</html>

so now let's move to codebehind Button1_Click event handler:

protected void Button1_Click(object sender, EventArgs e)
    {
        ExcelExporterTool exporter = new ExcelExporterTool();
        string fileLocation = exporter.ExportGridViewDataToExcel(GridView1, true, "");
        if (fileLocation != null)
        {
           Response.Write(fileLocation);
        }
}
So, first I'm generating an instance of ExcelExporterTool (remember that's the name of the class I developed) and am giving it a name of exporter. Then i generate a new string variable which will store the file's location for me and call the exporter's ExportGridViewDataToExcel() method on it. This call will return the file location if writing was a success. The method takes 3 parameters: Your GridView control, 2nd parameter is a boolean which asks if it should generate a random name for you using Guid and 3rd parameter is your own name. So if you want to use your own name change the line above to following:

string fileLocation = exporter.ExportGridViewDataToExcel(GridView1, false, "myXLSfile");

That's all there's to it, hope you find it useful. Enjoy! ;)

Download from http://eggheadcafe.com/FileUpload/-1672755420_ExcelExporterTool.zip

GridView data to Excel exporter  (2061 Views)