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