Export Data Into Excel in a Pre-defined Template Using StreamWriter
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>
</form>
Now here is the code to export the data using a StreamWriter object:
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); }
After exporting the data into Excel, the report looks like this:
