Create Excel with Data in ASP.Net

Create Excel in ASP.Net with Dynamic data .

The following function takes the following parameter :
    "DataTable"  : which holds the data from Database.
    "strFileName" : It is specify the Path with File Name .i.e: "D:\MyExcel.xls"

 

Use the following NameSpace :

    using System.IO;
    using System.Reflection;
    using Microsoft.Office.Interop.Excel;

Code :

    /// <summary>
    /// Creates the excel file from the provided dataset.
    /// </summary>
    /// <param name="dtSource">The dt source.</param>
    /// <param name="strFileName">Name of the STR file.</param>
    private void GenerateExcel(System.Data.DataTable dtSource, string strFileName)
    {
        Application oXL;
        Workbook oWB;
        Worksheet oSheet;
        Range oRange;

        // Start Excel and get Application object. 
        oXL = new Application();

        // Set some properties 
        oXL.Visible = false;
        oXL.DisplayAlerts = false;

        // Get a new workbook. 
        oWB = oXL.Workbooks.Add(Missing.Value);

        // Get the active sheet 
        oSheet = (Worksheet)oWB.ActiveSheet;
        oSheet.Name = "LMS Users";
        int rowCount = 1;

        // Add Headrer to Excel
        for (int i = 1; i < dtSource.Columns.Count + 1; i++)
        {
            oSheet.Cells[1, i] = dtSource.Columns[i - 1].ColumnName;
            oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, i];
            oRange.Font.Bold = true;
        }

        // Add the Data Rows to the Excel
        if (dtSource.Rows.Count > 0)
        {
            foreach (DataRow dr in dtSource.Rows)
            {
                rowCount += 1;
                for (int i = 1; i < dtSource.Columns.Count + 1; i++)
                {
                    if (rowCount > 1)
                    {
                        oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                    }
                }
            }
        }
        
        // Resize the columns 
        oRange = oSheet.get_Range(oSheet.Cells[1, 1],
                      oSheet.Cells[rowCount, dtSource.Columns.Count]);
        oRange.EntireColumn.AutoFit();

        // Save the sheet and close 
        oSheet = null;
        oRange = null;
        oWB.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal,
            Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            XlSaveAsAccessMode.xlExclusive,
            Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);
        oWB.Close(Missing.Value, Missing.Value, Missing.Value);
        oWB = null;
        oXL.Quit();

        // Clean up 
        // NOTE: When in release mode, this does the trick 
        GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();
    }