How to write GridData to Excel File using ASP.NET 2.0 (VB.NET)

To write GridData to Excel File is very simple. This tutorial will show you how to write GridData to an Excel file using ASP.NET 2.0 and VB.NET.

We can use ASP.NET 2.0 to write DataGrid data to Excel file. The method is to write the DataGrid data as stream to Html information, then use FileStream and BinaryWriter to create file and write information to the file.

First, you will need to import the System.IOnamespace.

The System.IO namespace contains the StringWriter ,FileStream and BinaryWriter Classes that we need for the sample .

Imports System.IO

If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.

We use the DataGrid to bind database, then write the DataGrid data as stream to Html information.
After then we use FileStream and BinaryWriter to create file and write information to the file.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

If TextBox1.Text <> "" Then

Dim filename, FilePathName, DocFileName, HtmlInfo As String
filename = TextBox1.Text
DataGrid1.Page.EnableViewState = False
Dim tw As New StringWriter
Dim hw As New HtmlTextWriter(tw)
DataGrid1.RenderControl(hw)
HtmlInfo = tw.ToString().Trim()
DocFileName = filename + ".xls"
FilePathName = Request.PhysicalPath
FilePathName = FilePathName.Substring(0, FilePathName.LastIndexOf("\"))
FilePathName = FilePathName + "\" + DocFileName
File.Delete(FilePathName)
Dim fs As New FileStream(FilePathName, FileMode.Create)
Dim BWriter As New BinaryWriter(fs, Encoding.GetEncoding("UTF-8"))
BWriter.Write(HtmlInfo)
BWriter.Close()
fs.Close()

Else

Response.Write("<script language="javascript">window.alert('Please enter filename!');</script>")

End If

End Sub

Try Server Intellect for Windows Server Hosting. Quality and Quantity!

The front Default.aspx page looks something like this:

<body>

<legend>DataToExcel</legend>
<asp:Button id="Button1" runat="server"
Text="ToExcel" OnClick="Button1_Click"></asp:Button>
  Save as filename:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:datagrid id="DataGrid1" runat="server" width="100%" DataSourceID="SqlDataSource1">
<ItemStyle HorizontalAlign="Center"></ItemStyle>
<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
<FooterStyle HorizontalAlign="Center"></FooterStyle>
<PagerStyle PageButtonCount="15" Mode="NumericPages"></PagerStyle>
</asp:datagrid><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [BirthDate], [City], [Address], [Country], [HomePhone] FROM [Employees]">
</asp:SqlDataSource>

</fieldset>

</form>

</body>

Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!

Please add the following code to Web.Config, and change to your User ID and Password accordingly.

</connectionStrings>

Download the Full Working Version of this Project written with Visual Studio.NET VB.NET 2005 Here!

Looking for the C#.NET 2005 Version? Click Here!

Looking for more ASP.NET Tutorials? Click Here!