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>

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!