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":
 
Screen2.png
 
Create a table called Product:
  1. CREATE TABLE[dbo]. [Product](  
  2.     [product_id][int] IDENTITY(1, 1) NOT NULL,  
  3.     [product_name][nvarchar](maxNULL,  
  4.     [product_rate][money] NULL  
  5. ON[PRIMARY] TEXTIMAGE_ON[PRIMARY]  
  6. GO; 
Added some records:
 
AddedRecord1.png
 
Page design code:
  1. <form id="form1" runat="server">  
  2.      <div>  
  3.           <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None">  
  4.                <AlternatingRowStyle BackColor="White" />  
  5.                <Columns>  
  6.                     <asp:BoundField DataField="product_id" HeaderText="Product Id" InsertVisible="False" ReadOnly="True" SortExpression="product_id" />  
  7.                     <asp:BoundField DataField="product_name" HeaderText="Product Name" SortExpression="product_name" />  
  8.                     <asp:BoundField DataField="product_rate" HeaderText="Product Rate" SortExpression="product_rate" />  
  9.                </Columns>  
  10.                <EditRowStyle BackColor="#2461BF" />  
  11.                <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />  
  12.                <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />  
  13.                <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />  
  14.                <RowStyle BackColor="#EFF3FB" />  
  15.                <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />  
  16.                <SortedAscendingCellStyle BackColor="#F5F7FB" />  
  17.                <SortedAscendingHeaderStyle BackColor="#6D95E1" />  
  18.                <SortedDescendingCellStyle BackColor="#E9EBEF" />  
  19.                <SortedDescendingHeaderStyle BackColor="#4870BE" />  
  20.           </asp:GridView>  
  21.      </div>  
  22.      <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" SelectCommand="SELECT * FROM [Product]"></asp:SqlDataSource>  
  23.      <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Export to Excel" />  
  24. </form> 

Now here is the code to export the data using a StreamWriter object:

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.IO;  
  8. using System.Data;  
  9. using System.Data.SqlClient;  
  10. using System.Configuration;  
  11.   
  12. public partial class ExporttoExcel: System.Web.UI.Page {  
  13.     SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString);  
  14.     protected void Page_Load(object sender, EventArgs e) {  
  15.         GridView1.DataBind();  
  16.     }  
  17.     protected void Button1_Click(object sender, EventArgs e) {  
  18.         SqlCommand command = new SqlCommand();  
  19.         command.CommandText = "SELECT * FROM Product";  
  20.         command.CommandType = CommandType.Text;  
  21.         command.Connection = con;  
  22.         SqlDataAdapter da = new SqlDataAdapter(command);  
  23.         DataSet ds = new DataSet();  
  24.         da.Fill(ds, "ProductDetails");  
  25.         if (ds.Tables[0].Rows.Count < 0)  
  26.             return;  
  27.         var fpath = string.Empty;  
  28.         if (Directory.Exists(Server.MapPath("ExcelTemplates")) == false)  
  29.             Directory.CreateDirectory(Server.MapPath("ExcelTemplates"));  
  30.         fpath = Server.MapPath("ExcelTemplates/Reports-ProductDetails" + Session.SessionID + ".xls");  
  31.         if (File.Exists(fpath) == false)  
  32.             File.Create(fpath).Close();  
  33.         else  
  34.             File.Create(fpath).Close();  
  35.         if (fpath.Trim() != string.Empty)  
  36.             DataSetToExcel(ds, fpath, "");  
  37.     }  
  38.     void DataSetToExcel(DataSet dsExport, string path, string tableName) {  
  39.         if (path == string.Empty)  
  40.             return;  
  41.         StreamWriter SWriter = new StreamWriter(path);  
  42.         string str = string.Empty;  
  43.         Int32 colspan = dsExport.Tables[0].Columns.Count;  
  44.         str += "<Table border=2><TR><TD align='center' colspan=" + Convert.ToString(colspan) + ">" + tableName + "</TD></tr>";  
  45.         str += "<tr><TD align='left' bgcolor='#D1DAA7' style='font-size:18px' colspan=" + Convert.ToString(colspan) + ">" + tableName + "Product Details</TD></tr>";  
  46.         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>";  
  47.         foreach(DataColumn DBCol in dsExport.Tables[0].Columns) {  
  48.             str += "<TD bgcolor='808080'>" + DBCol.ColumnName + "</TD>";  
  49.         }  
  50.         str += "</TR>";  
  51.         foreach(DataRow DBRow in dsExport.Tables[0].Rows) {  
  52.             str += "<TR>";  
  53.             foreach(DataColumn DBCol in dsExport.Tables[0].Columns) {  
  54.                 str += "<TD>" + Convert.ToString(DBRow[DBCol.ColumnName]) + "</TD>";  
  55.             }  
  56.             str += "</TR>";  
  57.         }  
  58.         str += "</TABLE>";  
  59.         SWriter.WriteLine(str);  
  60.         SWriter.Flush();  
  61.         SWriter.Close();  
  62.         if (path.Length > 5)  
  63.             DownloadFile(path);  
  64.     }  
  65.     void DownloadFile(string FPath) {  
  66.         String strRequest = Request.QueryString["file"];  
  67.         FileInfo file = new FileInfo(FPath);  
  68.         if (file.Exists) {  
  69.             Response.Clear();  
  70.             Response.AddHeader("Content-Disposition""attachment; filename=" + file.Name);  
  71.             Response.AddHeader("Content-Length", file.Length.ToString());  
  72.             Response.ContentType = "application/octet-stream";  
  73.             Response.WriteFile(file.FullName);  
  74.             Response.End();  
  75.         } else {  
  76.             Response.Write("This file does not exist.");  
  77.         }  
  78.     }  

After exporting the data into Excel, the report looks like this:
 
FinalResult.png
 
Happy coding!


Similar Articles