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();
                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
                 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;
                 foreach (GridViewRow row in source.Rows)
                     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;


                 return fileName;
                 throw new Exception("FileName is required!");
             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" "">
<html xmlns="">
<head runat="server">
    <title>Export to Excel</title>
    <form id="form1" runat="server">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1"
                 <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" />

        <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:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />

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)
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

GridView data to Excel exporter  (2061 Views)