John KilgoMike Dolan
Inherits System.ComponentModel.Component
Public Shared Sub DataGridToExcel(ByVal dgExport As DataGrid, ByVal response As HttpResponse)
'clean up the response.object
response.Clear()
response.Charset = ""
'set the response mime type for excel
response.ContentType = "application/vnd.ms-excel"
'create a string writer
Dim stringWrite As New System.IO.StringWriter()
'create an htmltextwriter which uses the stringwriter
Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
'instantiate a datagrid
Dim dg As New DataGrid()
' just set the input datagrid = to the new dg grid
dg = dgExport
' I want to make sure there are no annoying gridlines
dg.GridLines = GridLines.None
' Make the header text bold
dg.HeaderStyle.Font.Bold = True
' If needed, here's how to change colors/formatting at the component level
'dg.HeaderStyle.ForeColor = System.Drawing.Color.Black
'dg.ItemStyle.ForeColor = System.Drawing.Color.Black
'bind the modified datagrid
dg.DataBind()
'tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite)
'output the html
response.Write(stringWrite.ToString)
response.End()
End Sub
End Class
<asp:TemplateColumn HeaderText="Store #">
<ItemTemplate>
<%# GetStore(DataBinder.Eval(Container.DataItem, "intCustomerID")) %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="LA">
<ItemTemplate>
<%# GetLabor(DataBinder.Eval(Container.DataItem, "dblLabor"), DataBinder.Eval(Container.DataItem, "dblService")) %>
</ItemTemplate>
</asp:TemplateColumn>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>DataGridExport</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body>
<div align="center">
<form id="Form1" method="post" runat="server">
<P>
<asp:DataGrid id="dgToExport" runat="server" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="3" GridLines="Vertical" AutoGenerateColumns="False">
<SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#008A8C"></SelectedItemStyle>
<AlternatingItemStyle BackColor="Gainsboro"></AlternatingItemStyle>
<ItemStyle ForeColor="Black" BackColor="#EEEEEE"></ItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#000084"></HeaderStyle>
<FooterStyle ForeColor="Black" BackColor="#CCCCCC"></FooterStyle>
<Columns>
<asp:BoundColumn DataField="EmployeeID" ReadOnly="True" HeaderText="ID"></asp:BoundColumn>
<asp:TemplateColumn HeaderText="Name">
<ItemTemplate>
<%# ReturnName(DataBinder.Eval(Container.DataItem, "LastName"), DataBinder.Eval(Container.DataItem, "FirstName")) %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn DataField="Title" ReadOnly="True" HeaderText="Title"></asp:BoundColumn>
</Columns>
<PagerStyle HorizontalAlign="Center" ForeColor="Black" BackColor="#999999" Mode="NumericPages"></PagerStyle>
</asp:DataGrid>
</P>
<P>
<asp:Button id="btnExport" runat="server" Text="Export to Excel"></asp:Button></P>
</form>
</div>
</body>
</HTML>
Inherits System.Web.UI.Page
Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
Protected WithEvents dgToExport As System.Web.UI.WebControls.DataGrid
Protected WithEvents btnExport As System.Web.UI.WebControls.Button
Protected WithEvents Form1 As System.Web.UI.HtmlControls.HtmlForm
Protected WithEvents DataSet1 As System.Data.DataSet
' Web Form Designer Generated Code left out
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
BindGrid()
End Sub
Sub BindGrid()
' Fill our dataset
SqlDataAdapter1.Fill(DataSet1)
' Assign the dataset to our Datagrid called dgToExport
dgToExport.DataSource = DataSet1
' Finally bind the datagrid
dgToExport.DataBind()
End Sub
Function ReturnName(ByVal strLastName, ByVal strFirstName)
' This is the function I'm calling in the aspx page to show the difference
' between exporting a dataset versus exporting a datagrid. This function is
' simply going to combine the first and last names and and return the
' full name to the datagrid template column for "Name".
Dim strReturn As String
strReturn = strFirstName & " " & strLastName
Return strReturn
End Function
Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
' One line handles all of the export. We're simply calling the component (cmpDataGridToExcel),
' then we're using it's only method (DataGridToExcel), and we're passing our DataGrid (dgToExport) and the value reponse. Note: If you're using VS.Net, once you
' build your solution after creating the component, Intellisense will now include your
' component. Just remember you have to build it first.
'
' You could also modify your datagrid here before exporting it. For instance in my
' invoice example we had a checkbox in our datagrid. If you have one of those the export
' will generate an error so we simply removed the column first like this before exporting:
' dgToExport.Columns.Remove(dgToExport.Columns.Item(11))
cmpDataGridToExcel.DataGridToExcel(dgToExport, Response)
End Sub
End Class
You may download the code here.