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":

ExcelTemplates

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

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.

Excel

Happy coding!