datagrid data to an Excel file

slinky · Jun 13, 2008

I found the following code to transfer datagrid data to an Excel file.
Is this written in C#?... I'm a vb.netter. I'm just not sure where to
place the code to experiment on it. Should I place it in the event
handler for a form button. Is there other ways to accomplish this? I
looked at some on the web and usergroups, but was confused as to the
functioning. BTW my datagrid is enclosed in a <div></div> for ease of
scrolling if that would effect the solution.

Thanks! Brian

Response.Clear();
Response.AddHeader("content-disposition",
"attachment;filename=FileName.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new
HtmlTextWriter(stringWrite);
myDataGrid.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();

David Wier · Jun 13, 2008

That's C# all right - however, it's very close to VB code - you can try
removing the semi-colons and see if it works.
Otherwise there's a code sample at ASPNet101 for this:
http://www.aspnet101.com/aspnet101/aspnet/codesample.aspx?code=ExportDGExcel

David Wier
http://aspnet101.com
http://iWritePro.com - One click PDF, convert .doc/.rtf/.txt to HTML with no
bloated markup

slinky · Jun 13, 2008

Thanks! I still have a rough edge or two in my code below the block:

Sub doExport(Source as Object, E as EventArgs)
RenderGridToExcelFormat(MyDataGrid,txtfilename.text)
End Sub

is giving compiler errors for both "MyDataGrid" and "txtfilename":

Compiler Error Message: BC30451: Name 'MyDataGrid' is not declared.

Source Error:

Line 6: <script language="VB" Runat="server">
Line 7: Sub doExport(Source as Object, E as EventArgs)
Line 8: RenderGridToExcelFormat(MyDataGrid,txtfilename.text)
Line 9: End Sub
Line 10:

<%@ Page Language="vb" AutoEventWireup="false"
Codebehind="MainDepartment.aspx.vb"
Inherits="Forsyth.HR_ReportingTool.UI.MainDepartment" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>MainDepartment</title>
<script language="VB" Runat="server">
Sub doExport(Source as Object, E as EventArgs)
RenderGridToExcelFormat(MyDataGrid,txtfilename.text)
End Sub

Sub RenderGridToExcelFormat(grid As DataGrid, saveAsFile As
String)
' Excel rows limit is 65536
If grid.Items.Count.ToString + 1 < 65536 Then
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.ContentType =
"application/vnd.ms-excel"
HttpContext.Current.Response.AddHeader("content-
disposition", "attachment;filename=" & saveAsFile & ".xls")
HttpContext.Current.Response.Charset = ""
grid.EnableViewState = False
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
grid.RenderControl(hw)
HttpContext.Current.Response.Write(tw.ToString())
HttpContext.Current.Response.End()
Else
HttpContext.Current.Response.Write("Too many rows -
Export to Excel not possible")
End If
End Sub
</script>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5"
name="vs_targetSchema">
</HEAD>
<BODY background="file:///C:\Inetpub\wwwroot\HR_ReportingTool
\vignette.gif">
             
<div style="PADDING-RIGHT: 10px; PADDING-LEFT: 10px; FONT-SIZE:
medium; Z-INDEX: 101; BORDER-LEFT-COLOR: white; LEFT: 224px; FLOAT:
none; BORDER-BOTTOM-COLOR: white; MARGIN-LEFT: 20px; OVERFLOW: auto;
WIDTH: 736px; CLIP: rect(auto auto auto auto); COLOR: black; BORDER-
TOP-STYLE: inset; BORDER-TOP-COLOR: white; TEXT-INDENT: 5%; FONT-
FAMILY: 'Times New Roman'; BORDER-RIGHT-STYLE: inset; BORDER-LEFT-
STYLE: inset; POSITION: absolute; TOP: 100px; HEIGHT: 599px;
BACKGROUND-COLOR: white; TEXT-ALIGN: left; BORDER-RIGHT-COLOR: white;
BORDER-BOTTOM-STYLE: inset"
align="left">
<div style="POSITION: absolute">
<form id="Form1" method="post" runat="server">
<asp:datagrid id="dgEmployees" style="Z-INDEX: 103; LEFT: -4px;
POSITION: absolute; TOP: 12px"
runat="server" ShowHeader="False" BorderColor="Silver"
AllowSorting="True" HorizontalAlign="Center"
BorderStyle="Solid" Height="136px" Width="640px">
<SelectedItemStyle Font-Underline="True" Font-Bold="True"></
SelectedItemStyle>
<AlternatingItemStyle BackColor="#C0FFC0"></
AlternatingItemStyle>
<Columns>
<asp:ButtonColumn Text="Details" ButtonType="PushButton"
CommandName="Details"></asp:ButtonColumn>
</Columns>
</asp:datagrid>
<asp:Button id="button1" Text="Export" onclick="doExport"
Runat="server" />
</form>
</div>
</div>
<asp:label id="EmpNum" style="Z-INDEX: 106; LEFT: 360px; POSITION:
absolute; TOP: 48px; TEXT-ALIGN: center"
runat="server" BorderStyle="Outset" Width="65px" Text="Manufacturer
Name" BackColor="LightSkyBlue"
Font-Bold="True">Emp #</asp:label><asp:label id="lblWelcome"
style="Z-INDEX: 105; LEFT: 8px; POSITION: absolute; TOP: 8px"
runat="server"
Height="40px" Width="216px" BackColor="Transparent" Font-
Bold="True" ForeColor="White" Visible="False">Hello</
asp:label><asp:label id="Label3" style="Z-INDEX: 104; LEFT: 648px;
POSITION: absolute; TOP: 48px; TEXT-ALIGN: center"
runat="server" BorderStyle="Outset" Width="278px" Text="Title"
BackColor="LightSkyBlue" Font-Bold="True">Title</asp:label><asp:label
id="Label1" style="Z-INDEX: 103; LEFT: 560px; POSITION: absolute; TOP:
48px; TEXT-ALIGN: center"
runat="server" BorderStyle="Outset" Width="88px" Text="First Name"
BackColor="LightSkyBlue" Font-Bold="True">First Name</
asp:label><asp:label id="Label2" style="Z-INDEX: 102; LEFT: 424px;
POSITION: absolute; TOP: 48px; TEXT-ALIGN: center"
runat="server" BorderStyle="Outset" Width="137px" Text="Last Name"
BackColor="LightSkyBlue" Font-Bold="True">Last Name</asp:label>
</BODY>
</HTML>

Steve C. Orr [MCSD, MVP, CSM, ASP Insider] · Jun 13, 2008

I've documented VB code to export a datagrid to Excel here:
http://SteveOrr.net/articles/ExcelExport.aspx

And here's another technique you may find useful as well:
http://SteveOrr.net/articles/ExportPanel.aspx

--
I hope this helps,
Steve C. Orr,
MCSD, MVP, CSM, ASPInsider
http://SteveOrr.net
http://iPhonePlaza.net

David Wier · Jun 13, 2008

Your ID for both the DataGrid and the TextBox must be the same, notice, that
in the code sample, it has a textbox called txtfilename, which you can type
in the the xls filename you'd like. there is also a button, to actually do
the conversion process.

If you're not copying the entire code, make sure you make the changes
necessary, which match your page.

David Wier
http://aspnet101.com
http://iWritePro.com - One click PDF, convert .doc/.rtf/.txt to HTML with no
bloated markup

Thanks! I still have a rough edge or two in my code below the block:

Sub doExport(Source as Object, E as EventArgs)
RenderGridToExcelFormat(MyDataGrid,txtfilename.text)
End Sub

is giving compiler errors for both "MyDataGrid" and "txtfilename":

Compiler Error Message: BC30451: Name 'MyDataGrid' is not declared.

Source Error:

Line 6: <script language="VB" Runat="server">
Line 7: Sub doExport(Source as Object, E as EventArgs)
Line 8: RenderGridToExcelFormat(MyDataGrid,txtfilename.text)
Line 9: End Sub
Line 10:

<%@ Page Language="vb" AutoEventWireup="false"
Codebehind="MainDepartment.aspx.vb"
Inherits="Forsyth.HR_ReportingTool.UI.MainDepartment" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>MainDepartment</title>
<script language="VB" Runat="server">
Sub doExport(Source as Object, E as EventArgs)
RenderGridToExcelFormat(MyDataGrid,txtfilename.text)
End Sub

Sub RenderGridToExcelFormat(grid As DataGrid, saveAsFile As
String)
' Excel rows limit is 65536
If grid.Items.Count.ToString + 1 < 65536 Then
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.ContentType =
"application/vnd.ms-excel"
HttpContext.Current.Response.AddHeader("content-
disposition", "attachment;filename=" & saveAsFile & ".xls")
HttpContext.Current.Response.Charset = ""
grid.EnableViewState = False
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
grid.RenderControl(hw)
HttpContext.Current.Response.Write(tw.ToString())
HttpContext.Current.Response.End()
Else
HttpContext.Current.Response.Write("Too many rows -
Export to Excel not possible")
End If
End Sub
</script>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5"
name="vs_targetSchema">
</HEAD>
<BODY background="file:///C:\Inetpub\wwwroot\HR_ReportingTool
\vignette.gif">
             
<div style="PADDING-RIGHT: 10px; PADDING-LEFT: 10px; FONT-SIZE:
medium; Z-INDEX: 101; BORDER-LEFT-COLOR: white; LEFT: 224px; FLOAT:
none; BORDER-BOTTOM-COLOR: white; MARGIN-LEFT: 20px; OVERFLOW: auto;
WIDTH: 736px; CLIP: rect(auto auto auto auto); COLOR: black; BORDER-
TOP-STYLE: inset; BORDER-TOP-COLOR: white; TEXT-INDENT: 5%; FONT-
FAMILY: 'Times New Roman'; BORDER-RIGHT-STYLE: inset; BORDER-LEFT-
STYLE: inset; POSITION: absolute; TOP: 100px; HEIGHT: 599px;
BACKGROUND-COLOR: white; TEXT-ALIGN: left; BORDER-RIGHT-COLOR: white;
BORDER-BOTTOM-STYLE: inset"
align="left">
<div style="POSITION: absolute">
<form id="Form1" method="post" runat="server">
<asp:datagrid id="dgEmployees" style="Z-INDEX: 103; LEFT: -4px;
POSITION: absolute; TOP: 12px"
runat="server" ShowHeader="False" BorderColor="Silver"
AllowSorting="True" HorizontalAlign="Center"
BorderStyle="Solid" Height="136px" Width="640px">
<SelectedItemStyle Font-Underline="True" Font-Bold="True"></
SelectedItemStyle>
<AlternatingItemStyle BackColor="#C0FFC0"></
AlternatingItemStyle>
<Columns>
<asp:ButtonColumn Text="Details" ButtonType="PushButton"
CommandName="Details"></asp:ButtonColumn>
</Columns>
</asp:datagrid>
<asp:Button id="button1" Text="Export" onclick="doExport"
Runat="server" />
</form>
</div>
</div>
<asp:label id="EmpNum" style="Z-INDEX: 106; LEFT: 360px; POSITION:
absolute; TOP: 48px; TEXT-ALIGN: center"
runat="server" BorderStyle="Outset" Width="65px" Text="Manufacturer
Name" BackColor="LightSkyBlue"
Font-Bold="True">Emp #</asp:label><asp:label id="lblWelcome"
style="Z-INDEX: 105; LEFT: 8px; POSITION: absolute; TOP: 8px"
runat="server"
Height="40px" Width="216px" BackColor="Transparent" Font-
Bold="True" ForeColor="White" Visible="False">Hello</
asp:label><asp:label id="Label3" style="Z-INDEX: 104; LEFT: 648px;
POSITION: absolute; TOP: 48px; TEXT-ALIGN: center"
runat="server" BorderStyle="Outset" Width="278px" Text="Title"
BackColor="LightSkyBlue" Font-Bold="True">Title</asp:label><asp:label
id="Label1" style="Z-INDEX: 103; LEFT: 560px; POSITION: absolute; TOP:
48px; TEXT-ALIGN: center"
runat="server" BorderStyle="Outset" Width="88px" Text="First Name"
BackColor="LightSkyBlue" Font-Bold="True">First Name</
asp:label><asp:label id="Label2" style="Z-INDEX: 102; LEFT: 424px;
POSITION: absolute; TOP: 48px; TEXT-ALIGN: center"
runat="server" BorderStyle="Outset" Width="137px" Text="Last Name"
BackColor="LightSkyBlue" Font-Bold="True">Last Name</asp:label>
</BODY>
</HTML>

ChigbuaUmuenu · Jun 14, 2008

Here is the vb.NET translation of that code
============================
Response.lear()
Response.AddHeader("content-disposition","attachment;filename=FileName.xls")
Response.Charset = ""
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = "application/vnd.xls"
Dim StringWriter As New System.IO.StringWriter()
Dim HTMLWriter As New System.Web.UI.HTMLTextWriter(StringWriter)
myDataGrid.RenderControl(HTMLWriter())
Response.End
Response.Write(StringWriter.ToString)

slinky · Jun 16, 2008

Got that fixed and the button and the textbox are displaying in the
<div></div> OK (would like them to be though outside in the main part
of the page (possible?). The main problem is, and it may be because I
had to alter the code somewhat is my Excel file opens but has no data.
<script language="VB" Runat="server">

Sub doExport(Source as Object, E as EventArgs)
RenderGridToExcelFormat(dgEmployees,txtFileName.text)
End Sub

Sub RenderGridToExcelFormat(grid As DataGrid, saveAsFile As
String)
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.ContentType = "application/vnd.ms-
excel"
HttpContext.Current.Response.AddHeader("content-disposition",
"attachment;filename=" & saveAsFile & ".xls")
HttpContext.Current.Response.Charset = ""
grid.EnableViewState = False
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
HttpContext.Current.Response.Write(tw.ToString())
HttpContext.Current.Response.End()
End Sub

datagrid data to an Excel file

slinky

David Wier

slinky

Steve C. Orr [MCSD, MVP, CSM, ASP Insider]

David Wier

ChigbuaUmuenu

slinky