Exporting data into Excel is a common requirement. In this article, I will explain the exportation of data into Excel in a pre-defined Excel template.
For exporting data into a pre-defined Excel sheet, we will create an Excel sheet (as in the following) in the folder "ExcelTemplates" with the file name "Reports-ProductDetails.xls":
Create a table called Product:
CREATE TABLE [dbo].[Product](
[product_id] [int] IDENTITY(1,1) NOT NULL,
[product_name] [nvarchar](max) NULL,
[product_rate] [money] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO;
Added some records:
Page design code:
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="product_id" HeaderText="Product Id" InsertVisible="False" ReadOnly="True" SortExpression="product_id" />
<asp:BoundField DataField="product_name" HeaderText="Product Name" SortExpression="product_name" />
<asp:BoundField DataField="product_rate" HeaderText="Product Rate" SortExpression="product_rate" />
</Columns>
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1" />
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
</div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" SelectCommand="SELECT * FROM [Product]"></asp:SqlDataSource>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Export to Excel" />
</form>
Now here is the code to export the data using a StreamWriter object:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class ExporttoExcel : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT * FROM Product";
command.CommandType = CommandType.Text;
command.Connection = con;
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds, "ProductDetails");
if (ds.Tables[0].Rows.Count < 0)
return;
var fpath = string.Empty;
if (Directory.Exists(Server.MapPath("ExcelTemplates")) == false)
Directory.CreateDirectory(Server.MapPath("ExcelTemplates"));
fpath = Server.MapPath("ExcelTemplates/Reports-ProductDetails" + Session.SessionID + ".xls");
if (File.Exists(fpath) == false)
File.Create(fpath).Close();
else
File.Create(fpath).Close();
if (fpath.Trim() != string.Empty)
DataSetToExcel(ds, fpath, "");
}
void DataSetToExcel(DataSet dsExport, string path, string tableName)
{
if (path == string.Empty)
return;
StreamWriter SWriter = new StreamWriter(path);
string str = string.Empty;
Int32 colspan = dsExport.Tables[0].Columns.Count;
str += "<Table border=2><TR><TD align='center' colspan=" + Convert.ToString(colspan) + ">" + tableName + "</TD></tr>";
str += "<tr><TD align='left' bgcolor='#D1DAA7' style='font-size:18px' colspan=" + Convert.ToString(colspan) + ">" + tableName + "Product Details</TD></tr>";
str += "<tr></tr><tr><TD align='left' bgcolor='#D1DAA7' style='font-size:10px' colspan=" + Convert.ToString(colspan) + ">" + tableName + " Printed On " + DateTime.Now.Date + "</TD></tr><tr></TR>";
foreach (DataColumn DBCol in dsExport.Tables[0].Columns)
{
str += "<TD bgcolor='808080'>" + DBCol.ColumnName + "</TD>";
}
str += "</TR>";
foreach (DataRow DBRow in dsExport.Tables[0].Rows)
{
str += "<TR>";
foreach (DataColumn DBCol in dsExport.Tables[0].Columns)
{
str += "<TD>" + Convert.ToString(DBRow[DBCol.ColumnName]) + "</TD>";
}
str += "</TR>";
}
str += "</TABLE>";
SWriter.WriteLine(str);
SWriter.Flush();
SWriter.Close();
if (path.Length > 5)
DownloadFile(path);
}
void DownloadFile(string FPath)
{
String strRequest = Request.QueryString["file"];
FileInfo file = new FileInfo(FPath);
if (file.Exists)
{
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(file.FullName);
Response.End();
}
else
{
Response.Write("This file does not exist.");
}
}
}
After exporting the data into Excel, the report looks like this:
Happy coding!