Write the Data to Excel Template

Hello all,

In this article, I will explain how to write data into an Excel template.

Step 1. First, Create one Page with one button.

Aspx Page

HTML / XML

<asp:Button Text="Excel" ID="btnExcel" OnClick="btnExcel_Click" CssClass="btn btn-success button-62" runat="server" />

Step 2. With the click of this button, we will write the data into an Excel file.

Code Behind

protected void btnExcel_Click(object sender, EventArgs e)
{
    string[] sheetName = { "Sheet1" }; //Name of the sheet

    strQuery = @"select COl1,COl2,COl3,COl4 from yourTable";  // this is the query of table
    DataSet DS = dbContext.GetDataSet(strQuery);  //here we are getting dataset values
    if (DS.Tables[0].Rows.Count > 0) //here we check dataset count
    {

       //This function is used to write the data into template

        csExcel.XlsGenerateByTemplete_OnExistingTemplate(sheetName, "ReportName.xls", "TemplateName.xls", DS, "1", 1);

      //This function is used to open the file from location in pop-up format

        FileInfo file = new FileInfo(Server.MapPath(@"~\Excel\ReportName.xls"));
        if (file.Exists)
        {
            Response.Clear();
            Response.ClearHeaders();
            Response.ClearContent();
            Response.AddHeader("content-disposition", "attachment; filename=ReportName.xls");
            Response.AddHeader("Content-Type", "application/Excel");
            Response.ContentType = "application/vnd.xls";
            Response.AddHeader("Content-Length", file.Length.ToString());
            Response.WriteFile(file.FullName);
        }

        else { }
      
    }
    else
    {
       // display the message you want to...
        
    }
}

Step 3. Calling the Excel write function. Create a separate class file.

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Security.Cryptography;
using System.Text;
using System.Web;
using System.Web.UI;
using Excel = Microsoft.Office.Interop.Excel;

public class Utility
{
    Excel.Application ObjExcel;
    Excel._Workbook ObjBook;
    Excel._Worksheet ObjSheet;

    public bool XlsGenerateByTemplete_OnExistingTemplate(string[] Sheetname, string TempleteName, string ReportName, DataSet ObjDset, string first, int sheetnumber)
    {
        Int32 RowID = 1, ColumnID = 0;
        // Report Store Path
        string workbookPath = System.Web.HttpContext.Current.Server.MapPath(@"~\Template\" + TempleteName);
        string FileStorePath = System.Web.HttpContext.Current.Server.MapPath(@"~\Excel\" + ReportName);

        if (File.Exists(FileStorePath))
        {
            if (first == "1")
            {
                File.Delete(FileStorePath);
                File.Copy(workbookPath, FileStorePath);
            }
            else
            {

            }

        }
        else
        {
            File.Copy(workbookPath, FileStorePath);
        }

        try
        {
            //Start Excel and get Application object.
            ObjExcel = new Excel.ApplicationClass();

            //Get a new workbook.            
            //ObjBook = (Excel.Workbook)ObjExcel.Workbooks.Open(FileStorePath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);            
            ObjExcel = new Excel.ApplicationClass();
            ObjBook = (Excel.Workbook)ObjExcel.Workbooks.Add(FileStorePath);
            ObjSheet = (Excel.Worksheet)ObjBook.Sheets[1];

            for (int i = 0; i < ObjDset.Tables[0].Rows.Count; i++)
            {

                for (int j = 0; j < ObjDset.Tables[0].Columns.Count; j++)
                {
                    //RowID = Convert.ToInt32(ObjDset.Tables[0].Rows[i][0].ToString());
                    //ColumnID = Convert.ToInt32(ObjDset.Tables[0].Rows[i][1].ToString());
                    RowID = i + 2;
                    ColumnID = j + 1;
                    ObjSheet.Cells[RowID, ColumnID] = Convert.ToString(ObjDset.Tables[0].Rows[i][j]);
                }
            }

            ObjBook.SaveCopyAs(FileStorePath);

            return true;
        }
        catch (Exception ex)
        {
            ex.Message.ToString();
            return false;
        }
        finally
        {
            RemoveProcess_Instance(ObjExcel);
            releaseObject(ObjSheet);
            releaseObject(ObjBook);
            releaseObject(ObjExcel);
        }
    }

    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            Console.WriteLine("Exception Occured while releasing object " + ex.ToString());
        }
        finally { GC.Collect(); }
    }

    [DllImport("user32.dll")]
    private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);

    public void RemoveProcess_Instance(Excel.Application objExcel)
    {
        GC.Collect();
        GC.WaitForPendingFinalizers();

        if (objExcel != null)
        {
            objExcel.Quit();
            int hWnd = objExcel.Application.Hwnd;
            uint processID; GetWindowThreadProcessId((IntPtr)hWnd, out processID);
            System.Diagnostics.Process[] procs = System.Diagnostics.Process.GetProcessesByName("EXCEL");
            foreach (System.Diagnostics.Process p in procs)
            {
                if (p.Id == processID)
                    p.Kill();
            }
            Marshal.FinalReleaseComObject(objExcel);
        }
    }
}

// end of code

Please note. You have to add the.dll files

microsoft.office.interop.excel.dll
microsoft.vbe.interop.dll
office.dll

Also, please create one .xls template and store it in the Template folder and create an Excel folder for report storage.