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

Navigator : Home > Tutorials > File Tutorials > ...

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

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.

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

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

Imports System.IO

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

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>

We chose Server Intellect for its dedicated servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.

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

</connectionStrings>

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

Looking for more ASP.NET Tutorials? Click Here!

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.

Download Project Source - Enter your Email to be emailed a link to download the Full Source Project used in this Tutorial!

100% SPAM FREE! We will never sell or rent your email address!