C# Export To Helper Class using Microsoft Office Interop library

Hi Dosto,

Below is ExcelExportHelper class for exporting DataTable to Excel Format using Microsoft Interop library.

public static class ExcelExpoterHelper
    {
        static Application oXL;
        static Workbook oWB;
        static Worksheet oSheet;
        static int noRows;
        public static void ExportDataTableToExcel(System.Data.DataTable tbl, 

                       string strReportName, out string filename)
        {
            filename = "";
            noRows = tbl.Rows.Count;
            Range oRange;
            //Start Excel and get Application object.
            oXL = new Application();
           
 
            // Set some properties
            oXL.Visible = true;
            oXL.DisplayAlerts = false;
            oXL.Workbooks.Add();
 
            // Get a new workbook.
            oWB = oXL.Workbooks.Add(Missing.Value);
 
            // Get the Active sheet
            oSheet = (Worksheet)oWB.ActiveSheet;
            oSheet.Name = "Data1";
 
            int rowCount = 1;
 
            foreach (DataRow dr in tbl.Rows)
            {
                rowCount += 1;
 
                for (int i = 1; i < tbl.Columns.Count + 1; i++)
                {
                    // Add the header the first time through
                    if (rowCount == 2)
                    {
                        oSheet.Cells[1, i] = tbl.Columns[i - 1].ColumnName;
                    }
                    oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
 
 
                }
            }
 
            //  Code patch added to fix the "get_Range" issue
            //  Resize the columns
            Microsoft.Office.Interop.Excel.Range c1 = oSheet.Cells[1, 1];
            Microsoft.Office.Interop.Excel.Range c2 = oSheet.Cells[rowCount, tbl.Columns.Count];
            oRange = (Microsoft.Office.Interop.Excel.Range)oSheet.get_Range(c1, c2);
            oRange.EntireColumn.AutoFit();          
            
            // Save the sheet and close
            oSheet = null;
            oRange = null;
            filename = strReportName + DateTime.Now.ToString("_yyyyMMddHHmmssfff") + ".xls";
            oWB.SaveAs(ConfigurationManager.AppSettings["File_Path"] + filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);
            oWB.Close(Missing.Value, Missing.Value, Missing.Value);
            oWB = null;
            oXL.Quit();
 
 
        }
        #region "Common fuctions"
 
        private static void setBold(string strRange)
        {
            if (!string.IsNullOrEmpty(strRange))
            {
                oSheet.get_Range(strRange).Font.Bold = true;
            }
        }
        private static void setIndianFormat(string strRange)
        {
            if (!string.IsNullOrEmpty(strRange))
            {
                oSheet.get_Range(strRange).NumberFormat = "@";
                oSheet.get_Range(strRange).NumberFormat = "[>=10000000]##\\,##\\,##\\,##0.00;[>=100000]##\\,##\\,##0.00;##,##0.00";
            }
        }
        private static void setIndianFormat4(string strRange)
        {
            if (!string.IsNullOrEmpty(strRange))
            {
                oSheet.get_Range(strRange).NumberFormat = "@";
                oSheet.get_Range(strRange).NumberFormat = "[>=10000000]##\\,##\\,##\\,##0.0000;[>=100000]##\\,##\\,##0.0000;##,##0.0000";
            }
        }
 
        private static void setCustomDateFormat(string strRange, string customFormat)
        {
            if (!string.IsNullOrEmpty(strRange))
            {
                oSheet.get_Range(strRange).NumberFormat = customFormat;
 
            }
        }
 
        private static void setExcelAutoSize()
        {
            oSheet.Cells.EntireRow.AutoFit();
            oSheet.Cells.EntireColumn.AutoFit();
        }
        # endregion
 
    }