Create XLS File in ASP.NET application

Class CreateXLS :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Drawing;
using System.Reflection;

public class CreateXLS
{
    #region Excel Variables
    private Microsoft.Office.Interop.Excel.Application oXL;
    private Microsoft.Office.Interop.Excel._Workbook oWB;
    private Microsoft.Office.Interop.Excel._Worksheet oSheet;

    private long iExcelRow = 2;
    private long iExcelCol = 2;
    private long iStartRow = 0;

    private string sFromCellNo = "";
    private string sToCellNo = "";

    private enum enumXLSFields
    {
        EmployeeName,
        Address,
        AccountNo,
        Earnings,
        Deduction,
        NetSalary
    }

    private float LogoLeft = 50;
    private float LogoTop = 15;
    private float LogoWidth = 52;
    private float LogoHeight = 52;
    #endregion

    #region Properties
    public string XLSPath { get; set; }
    public string XLSFileName { get; set; }
    public string XLSSheetName { get; set; }

    public List<EmployeeInfo> EmployeeList { get; set; }

    /// <summary>
    /// Logo Properties
    /// </summary>
    public string LogoFile { get; set; }
    #endregion

    /// <summary>
    /// Cunstructor
    /// </summary>
    public CreateXLS()
    {
        oXL = new Microsoft.Office.Interop.Excel.Application();
        oXL.Visible = false;
        oXL.UserControl = false;

        this.EmployeeList = new List<EmployeeInfo>();
    }

    #region Public Methods
    public void GenerateXLSFile()
    {
        try
        {
            #region GenrateXLSFile
            GC.Collect();
            oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
            //oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet));
            oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
            oSheet.Name = this.XLSSheetName;

            /*----------------Main Coding----------------*/
            InsertLogoFileToSheet();

            SetXLSTitle((int)enumXLSFields.EmployeeName, (int)enumXLSFields.NetSalary, "Employee Report", 10, true);

            DisplayEmployeeInfo();

            SheetAutoFit();
            /*-------------------------------------------*/

            #region XLS File Save into Server Path

            #region Check XLS File if Exists
            string sFileName = this.XLSPath + this.XLSFileName + ".XLS";
            if (File.Exists(sFileName))
            {
                File.Delete(sFileName);
            }
            #endregion

            //Create BillingXLS Directory
            if (!System.IO.Directory.Exists(XLSPath))
            {
                System.IO.Directory.CreateDirectory(XLSPath);
            }

            string strXLSFile = string.Format("{0}\\{1}.xls", this.XLSPath, this.XLSFileName);
            if (File.Exists(strXLSFile))
            {
                File.Delete(strXLSFile);
            }
            oWB.SaveAs(string.Format("{0}\\{1}", this.XLSPath, this.XLSFileName), XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
            oWB.Close(null, null, null);
            #endregion

            #endregion
        }
        catch
        {
            System.GC.Collect();
        }
        finally
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);

            oSheet = null;
            oWB = null;
        }
    }

    private void DisplayEmployeeInfo()
    {
        #region Header
        iExcelRow++;

        oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.EmployeeName] = "Employee Name";
        oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Address] = "Address";
        oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.AccountNo] = "Account No";
        oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Earnings] = "Earnings";
        oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Deduction] = "Deduction";
        oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.NetSalary] = "Net Salary";

        SetRangeBackColor(iExcelRow, iExcelRow, (int)enumXLSFields.EmployeeName, (int)enumXLSFields.NetSalary);
        iExcelRow++;
        #endregion

        #region Detail
        iStartRow = iExcelRow;
        foreach (EmployeeInfo Employee in EmployeeList)
        {
            oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.EmployeeName] = Employee.EmployeeName;
            oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Address] = Employee.Address;
            oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.AccountNo] = Employee.AccountNo;
            oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Earnings] = Employee.Earnings;
            oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.Deduction] = Employee.Deduction;
            oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.NetSalary] = Employee.NetSalary;
            iExcelRow++;
        }
        #endregion

        #region Total

        #region Print Total
        oSheet.Cells[iExcelRow, iExcelCol + (int)enumXLSFields.EmployeeName] = "Total";

        SetRangeTotal(iExcelRow, (int)enumXLSFields.Earnings, iStartRow, iExcelRow - 1, (int)enumXLSFields.Earnings);
        SetRangeTotal(iExcelRow, (int)enumXLSFields.Deduction, iStartRow, iExcelRow - 1, (int)enumXLSFields.Deduction);
        SetRangeTotal(iExcelRow, (int)enumXLSFields.NetSalary, iStartRow, iExcelRow - 1, (int)enumXLSFields.NetSalary);
        #endregion

        #region Range Format
        SetRangeNumberFormat(iStartRow, iExcelRow, (int)enumXLSFields.Earnings, (int)enumXLSFields.NetSalary);
        SetRangeBackColor(iExcelRow, iExcelRow, (int)enumXLSFields.EmployeeName, (int)enumXLSFields.NetSalary);
        SetRangeBorder(iStartRow, iExcelRow, (int)enumXLSFields.EmployeeName, (int)enumXLSFields.NetSalary);
        #endregion

        #endregion
    }
    #endregion

    #region Private Methods
    private void InsertLogoFileToSheet()
    {
        if (File.Exists(this.LogoFile))
        {
            oSheet.Shapes.AddPicture(this.LogoFile, Microsoft.Office.Core.MsoTriState.msoTrue, Microsoft.Office.Core.MsoTriState.msoTrue, LogoLeft, LogoTop, LogoWidth, LogoHeight);
        }
        SetXLSTitle((int)enumXLSFields.Address, (int)enumXLSFields.NetSalary, "ABC Inc.", 12, true);

        iExcelRow = 6;
    }

    private void SheetAutoFit()
    {
        Range oRng = oSheet.get_Range("A1", "Z1");
        oRng.EntireColumn.AutoFit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
    }

    private void SetXLSTitle(int StartColumn, int EndColumn, string ColumnHeading, int FontSize, bool MergeColumn)
    {
        sFromCellNo = ((char)(iExcelCol + StartColumn + 64)) + iExcelRow.ToString();
        sToCellNo = ((char)(iExcelCol + EndColumn + 64)) + iExcelRow.ToString();

        oSheet.Cells[iExcelRow, iExcelCol + StartColumn] = ColumnHeading;

        Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);
        oRng.Font.Name = "Arial";
        oRng.Font.Size = FontSize;
        oRng.Font.Bold = true;
        if (MergeColumn)
        {
            oRng.Merge(1);
            oRng.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            oRng.Font.Color = Color.White.ToArgb();
            oRng.Interior.Color = Color.FromArgb(128, 128, 128).ToArgb();
            oRng.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid;
            oRng.Borders.Value = 1;
            oRng.Borders.Color = Color.Black.ToArgb();
        }
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
    }

    private void SetRangeTotal(long DisplayRow, long DisplayColumn, long StartRow, long EndRow, long RangeColumn)
    {
        sFromCellNo = ((char)(iExcelCol + RangeColumn + 64)) + StartRow.ToString();
        sToCellNo = ((char)(iExcelCol + RangeColumn + 64)) + EndRow.ToString();

        oSheet.Cells[DisplayRow, iExcelCol + DisplayColumn] = "=SUM(" + sFromCellNo + ":" + sToCellNo + ")";
    }

    private void SetRangeNumberFormat(long StartRow, long EndRow, long StartColumn, long EndColumn)
    {
        sFromCellNo = ((char)(iExcelCol + StartColumn + 64)) + StartRow.ToString();
        sToCellNo = ((char)(iExcelCol + EndColumn + 64)) + EndRow.ToString();

        Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);
        oRng.NumberFormat = "###,###,##0.00";
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
    }

    private void SetRangeBorder(long StartRow, long EndRow, long StartColumn, long EndColumn)
    {
        sFromCellNo = ((char)(iExcelCol + StartColumn + 64)) + StartRow.ToString();
        sToCellNo = ((char)(iExcelCol + EndColumn + 64)) + EndRow.ToString();

        Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);
        oRng.Borders.Value = 1;
        oRng.Borders.Color = Color.Black.ToArgb();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
    }

    private void SetRangeBackColor(long StartRow, long EndRow, int StartColumn, int EndColumn)
    {
        sFromCellNo = ((char)(iExcelCol + StartColumn + 64)) + StartRow.ToString();
        sToCellNo = ((char)(iExcelCol + EndColumn + 64)) + EndRow.ToString();

        Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);
        oRng.Font.Name = "Arial";
        oRng.Font.Bold = true;
        oRng.Font.Color = Color.Black.ToArgb();
        oRng.Interior.Color = System.Drawing.Color.FromArgb(192, 192, 192).ToArgb();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
    }

    private void SetRangeBold(long StartRow, long EndRow, long StartColumn, long EndColumn)
    {
        sFromCellNo = ((char)(iExcelCol + StartColumn + 64)) + StartRow.ToString();
        sToCellNo = ((char)(iExcelCol + EndColumn + 64)) + EndRow.ToString();

        Range oRng = oSheet.get_Range(sFromCellNo, sToCellNo);
        oRng.Font.Name = "Arial";
        oRng.Font.Bold = true;
        oRng.Font.Color = Color.Black.ToArgb();
        oRng.Interior.Color = System.Drawing.Color.FromArgb(192, 192, 192).ToArgb();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
    }
    #endregion
}

public class EmployeeInfo
{
    public string EmployeeName { get; set; }
    public string Address { get; set; }
    public string AccountNo { get; set; }
    public float Earnings { get; set; }
    public float Deduction { get; set; }
    public float NetSalary { get; set; }
}

ProcessXLS.ASPX :

public partial class ProcessXLS : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        GenrateXLS();
    }

    private void GenrateXLS()
    {
        CreateXLS objXLS = new CreateXLS();

        objXLS.EmployeeList.Add(new EmployeeInfo() { EmployeeName = "Eugene Malarky", Address = "Grove Street, Smithtown", AccountNo = "A001", Earnings = 1500, Deduction = 450, NetSalary = 1050 });
        objXLS.EmployeeList.Add(new EmployeeInfo() { EmployeeName = "Sam Adams", Address = "Main Street, Suite 1000, Ashville", AccountNo = "A002", Earnings = 1250, Deduction = 320, NetSalary = 930 });
        objXLS.EmployeeList.Add(new EmployeeInfo() { EmployeeName = "Loren Sandler", Address = "Main Street, Hometown", AccountNo = "A003", Earnings = 1800, Deduction = 550, NetSalary = 1250 });

        objXLS.XLSPath = Server.MapPath("XLSFiles");
        objXLS.XLSFileName = "EmployeeSalary";
        objXLS.XLSSheetName = "EmployeeList";

        objXLS.LogoFile = Server.MapPath("XLSFiles/JDLogo.jpeg");

        objXLS.GenerateXLSFile();
    }
}

Sample XLS File