Write GridData to Excel File using ASP.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 C#.

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.IO namespace.

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

Imports System.IO
We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!

We use the Button1_Click event to do the work.

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 void Button1_Click(object sender, EventArgs e)
{
if (TextBox1.Text == "")
{
Response.Write("");
return;
}
else
{
string filename = TextBox1.Text;

this.DataGrid1.Page.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
this.DataGrid1.RenderControl(hw);
string HtmlInfo = tw.ToString().Trim();

string DocFileName = filename+".xls";
string FilePathName = Request.PhysicalPath;
FilePathName = FilePathName.Substring(0, FilePathName.LastIndexOf("\\"));

FilePathName = FilePathName + "\\" + DocFileName;
File.Delete(FilePathName);
FileStream Fs = new FileStream(FilePathName, FileMode.Create);
BinaryWriter BWriter = new BinaryWriter(Fs, Encoding.GetEncoding("UTF-8"));

BWriter.Write(HtmlInfo);
BWriter.Close();
Fs.Close();
}
}
We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!

The front Default.aspx page looks something like this:

<form id="Form1" method="post" runat="server">
<fieldset>
<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="">">
SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [BirthDate], [City], [Address], [Country], [HomePhone] FROM [Employees]">
</asp:sqldatasource>
</fieldset>
</form>

Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.

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

<connectionstrings>
<add name="NorthwindConnectionString" connectionstring="Data Source=localhost;Initial Catalog=Northwind;User ID=sa" providername="System.Data.SqlClient">
</add></connectionstrings>