Dynamic Excel Data Management in C# windows application

Codebehind C#:

 private void btnBrowse_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog fdlg = new OpenFileDialog();

                fdlg.Title = "Select file";

                fdlg.InitialDirectory = @"c:\";

                fdlg.FileName = txtFileName.Text;

                fdlg.Filter = "Excel Sheet(*.xls)|*.xls|All Files(*.*)|*.*";

                fdlg.FilterIndex = 1;

                fdlg.RestoreDirectory = true;

                if (fdlg.ShowDialog() == DialogResult.OK)
                {

                    txtFileName.Text = fdlg.FileName;

                    Import();

                    Application.DoEvents();

                }
            }
            catch (Exception ex)
            {
                ErrorLog.WriteErrorLog(ErrorLog.GetLogFilePath(), ex);
            }
        }

        private void Import()
        {
            if (txtFileName.Text.Trim() != string.Empty)
            {
                try
                {
                    string[] strTables = GetTableExcel(txtFileName.Text);

                    frmSelectTables objSelectTable = new frmSelectTables(strTables);
                    objSelectTable.ShowDialog(this);
                    objSelectTable.Dispose();
                    if ((SelectedTable != string.Empty) && (SelectedTable != null))
                    {

                        dt = GetDataTableExcel(txtFileName.Text, SelectedTable);

                        // Initialize the DataGridView.
                        dgvUsers.DataSource = null;
                        dgvUsers.Rows.Clear();
                        dgvUsers.Columns.Clear();
                        dgvUsers.AutoGenerateColumns = false;
                        dgvUsers.AutoSize = true;
                        dgvUsers.DataSource = dt.DefaultView;
                        lblMsg.Text = "Total Number :" + dt.Rows.Count;
                        count = dt.Rows.Count;
                        lblMsg.Visible = true;
                        if (count > 240)
                        {
                            txtserialnos.Text = "0-240";
                        }
                        else
                        {
                            txtserialnos.Text = "0-" + (count).ToString();
                        }

                        for (int i = 0; i <= dt.Columns.Count; i++)
                        {
                            if (i == 0)
                            {
                                DataGridViewCheckBoxColumn CBColumn = new DataGridViewCheckBoxColumn();
                                CBColumn.HeaderText = "";
                                CBColumn.FalseValue = "0";
                                CBColumn.TrueValue = "1";
                                dgvUsers.Columns.Insert(0, CBColumn);
                            }
                            else
                            {
                                // Initialize and add a text box column.
                                DataGridViewColumn column = new DataGridViewTextBoxColumn();
                                column.DataPropertyName = dt.Columns[i - 1].ColumnName;
                                column.Name = dt.Columns[i - 1].ColumnName;
                                dgvUsers.Columns.Insert(i, column);
                            }
                        }

                       
                    }
                }
                catch (Exception ex)
                {
                    ErrorLog.WriteErrorLog(ErrorLog.GetLogFilePath(), ex);
                }
            }
        }

        public static DataTable GetDataTableExcel(string strFileName, string Table)
        {
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 12.0 Xml;HDR=Yes;IMEX=1\";");
            conn.Open();
            string strQuery = "SELECT * FROM [" + Table + "]";
            //
            OleDbDataAdapter adapter = new OleDbDataAdapter(strQuery, conn);
            System.Data.DataSet ds = new System.Data.DataSet();
            // DataTable dt = new DataTable();
            adapter.Fill(ds);

            return ds.Tables[0];
        }


        public static string[] GetTableExcel(string strFileName)
        {
            string[] strTables = new string[100];
            Catalog oCatlog = new Catalog();
            ADOX.Table oTable = new ADOX.Table();
            ADODB.Connection oConn = new ADODB.Connection();
            oConn.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 12.0 Xml;HDR=Yes;IMEX=1\";", "", "", 0);
            oCatlog.ActiveConnection = oConn;
            if (oCatlog.Tables.Count > 0)
            {
                int item = 0;
                foreach (ADOX.Table tab in oCatlog.Tables)
                {
                    if (tab.Type == "TABLE")
                    {
                        strTables[item] = tab.Name;
                        item++;
                    }
                }
            }
            return strTables;
        }

        private void ExcelLabel_Load(object sender, EventArgs e)
        {

            this.reportViewer1.RefreshReport();
        }

        private void dgvUsers_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                Point cur = new Point(e.ColumnIndex, e.RowIndex);

                // Change the diagonal checkbox to the opposite state            
                if ((bool)(dgvUsers[cur.X, cur.Y].Selected) == true && (dgvUsers[cur.X, cur.Y].Value.ToString() != "0"))
                {
                    dgvUsers[cur.X, cur.Y].Selected = true;
                    dgvUsers[cur.X, cur.Y].Value = true;
                    flag = true;
                }
                else
                {
                    dgvUsers[cur.X, cur.Y].Selected = false;
                    dgvUsers[cur.X, cur.Y].Value = false;
                    flag = true;
                }
            }
            catch (Exception ex)
            {
                ErrorLog.WriteErrorLog(ErrorLog.GetLogFilePath(), ex);
            }
        }

        private void dgvUsers_CurrentCellDirtyStateChanged(object sender, EventArgs e)
        {
            try
            {
                if (dgvUsers.IsCurrentCellDirty)
                {
                    dgvUsers.CommitEdit(DataGridViewDataErrorContexts.Commit);
                }
            }
            catch (Exception ex)
            {
                ErrorLog.WriteErrorLog(ErrorLog.GetLogFilePath(), ex);
            }
        }

        private void btnReport_Click(object sender, EventArgs e)
        {
            try
            {

               
                DataTable dt1 = null;
                dt1 = dt.Clone();
                DataRow dr = null;
                int ii = 0;
                int jj = 0;
                dt1.Rows.Clear();

                if (chkSerialNos.Checked == true)
                {
                    if (txtserialnos.Text != "")
                    {
                        string[] Nos = txtserialnos.Text.Split('-');

                        for (ii = int.Parse(Nos[0]); ii <= int.Parse(Nos[1]) - 1; ii++)
                        {
                            if (flag == false)
                            {
                                dr = dt1.NewRow();
                                for (jj = 0; jj <= dgvUsers.Columns.Count - 2; jj++)
                                {
                                    dr[jj] = dgvUsers.Rows[ii].Cells[jj + 1].Value;
                                }
                                dt1.Rows.Add(dr);
                            }
                            else
                            {
                                if (dgvUsers.Rows[ii].Cells[0].Value != null)
                                {
                                    if (dgvUsers.Rows[ii].Cells[0].Value.ToString() != "0")
                                    {
                                        if ((bool)dgvUsers.Rows[ii].Cells[0].Value == true)
                                        {
                                            dr = dt1.NewRow();
                                            for (jj = 0; jj <= dgvUsers.Columns.Count - 2; jj++)
                                            {
                                                dr[jj] = dgvUsers.Rows[ii].Cells[jj + 1].Value;
                                            }
                                            dt1.Rows.Add(dr);
                                        }
                                    }
                                }
                            }
                        }
                        if ((int.Parse(Nos[1]) + 240) > count)
                        {
                            if (int.Parse(Nos[1]) + 1 < count)
                            {
                                txtserialnos.Text = (int.Parse(Nos[1]) + 1).ToString() + "-" + count.ToString();
                            }
                        }                      
                        else
                        {
                            txtserialnos.Text = (int.Parse(Nos[1]) + 1).ToString() + "-" + (int.Parse(Nos[1]) + 240).ToString();
                        }
                    }
                }
                else
                {
                    for (ii = 0; ii <= dgvUsers.Rows.Count - 1; ii++)
                    {
                        if (flag == false)
                        {
                            dr = dt1.NewRow();
                            for (jj = 0; jj <= dgvUsers.Columns.Count - 2; jj++)
                            {
                                dr[jj] = dgvUsers.Rows[ii].Cells[jj + 1].Value;
                            }
                            dt1.Rows.Add(dr);
                        }
                        else
                        {
                            if (dgvUsers.Rows[ii].Cells[0].Value != null)
                            {
                                dr = dt1.NewRow();
                                for (jj = 0; jj <= dgvUsers.Columns.Count - 2; jj++)
                                {
                                    dr[jj] = dgvUsers.Rows[ii].Cells[jj + 1].Value;
                                }
                                dt1.Rows.Add(dr);
                            }
                        }
                    }

                 
                }

                dt1.Columns[0].ColumnName = "COL1";
                dt1.Columns[1].ColumnName = "COL2";
                dt1.Columns[2].ColumnName = "COL3";
                dt1.Columns[3].ColumnName = "COL4";
                dt1.Columns[4].ColumnName = "COL5";
                dt1.Columns[5].ColumnName = "COL6";
                dt1.Columns[6].ColumnName = "COL7";
                dt1.Columns[7].ColumnName = "COL8";
                dt1.AcceptChanges();

                FillReport(dt1);
              
            }
            catch (Exception ex)
            {
                ErrorLog.WriteErrorLog(ErrorLog.GetLogFilePath(), ex);
            }
        }

        private void FillReport(DataTable dt)
        {
            reportViewer1.Clear();
            reportViewer1.ProcessingMode = ProcessingMode.Local;
            reportViewer1.LocalReport.ReportPath = System.IO.Path.GetDirectoryName(
                System.Reflection.Assembly.GetExecutingAssembly().Location) + "\\ExcelLabels.rdlc";
            reportViewer1.LocalReport.DataSources.Clear();
            reportViewer1.LocalReport.DataSources.Add(new ReportDataSource(reportViewer1.LocalReport.GetDataSourceNames()[0], dt));
            this.reportViewer1.RefreshReport();
            tabControl1.SelectTab("tabPage2");
            tabControl1.SelectedIndex = 1;
        }

        private void chkAll_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                if (chkAll.Checked == true)
                {
                    for (int ii = 0; ii <= dgvUsers.Rows.Count - 1; ii++)
                    {
                        dgvUsers[0, ii].Selected = true;
                        dgvUsers[0, ii].Value = true;
                    }
                }
                else
                {
                    for (int ii = 0; ii <= dgvUsers.Rows.Count - 1; ii++)
                    {
                        dgvUsers[0, ii].Selected = false;
                        dgvUsers[0, ii].Value = false;
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorLog.WriteErrorLog(ErrorLog.GetLogFilePath(), ex);
            }
        }

Error Log in asp.net

            try
            {
              
            }
            catch (Exception ex)
            {
                ErrorLog.WriteErrorLog(ErrorLog.GetLogFilePath(), ex);
            }



public class ErrorLog
    {
        protected static string strLogFilePath = string.Empty;
        private static StreamWriter sw = null;
        /// <summary>
        /// Setting LogFile path. If the logfile path is null then it will update error info into LogFile.txt under
        /// application directory.
        /// </summary>
        public static string LogFilePath
        {
            set
            {
                strLogFilePath = value;
            }
            get
            {
                return strLogFilePath;
            }
        }
        /// <summary>
        /// Empty Constructor
        /// </summary>
        public ErrorLog() { }
        /// <summary>
        /// Write error log entry for window event if the bLogType is true. Otherwise, write the log entry to
        /// customized text-based text file
        /// </summary>
        /// <param name="bLogType"></param>
        /// <param name="objException"></param>
        /// <returns>false if the problem persists</returns>
        public static bool ErrorRoutine(bool bLogType, Exception objException)
        {
            try
            {
                //Check whether logging is enabled or not
                bool bLoggingEnabled = false;
                bLoggingEnabled = CheckLoggingEnabled();

                //Don't process more if the logging
                if (false == bLoggingEnabled)
                    return true;

                //Write to Windows event log
                if (true == bLogType)
                {
                    string EventLogName = "ErrorSample";

                    if (!EventLog.SourceExists(EventLogName))
                        EventLog.CreateEventSource(objException.Message, EventLogName);

                    // Inserting into event log
                    EventLog Log = new EventLog();
                    Log.Source = EventLogName;
                    Log.WriteEntry(objException.Message, EventLogEntryType.Error);
                }
                //Custom text-based event log
                else
                {
                    if (true != CustomErrorRoutine(objException))
                        return false;
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        /// <summary>
        /// Check Logginstatus config file is exist. If exist read the value set the loggig status
        /// </summary>
        public static bool CheckLoggingEnabled()
        {
            string strLoggingStatusConfig = string.Empty;

            strLoggingStatusConfig = GetLoggingStatusConfigFileName();

            //If it's empty then enable the logging status
            if (strLoggingStatusConfig.Equals(string.Empty))
            {
                return true;
            }
            else
            {

                //Read the value from xml and set the logging status
                bool bTemp = GetValueFromXml(strLoggingStatusConfig);
                return bTemp;
            }
        }
        /// <summary>
        /// Check the Logginstatus config under debug or release folder. If not exist, check under
        /// project folder. If not exist again, return empty string
        /// </summary>
        /// <returns>empty string if file not exists</returns>
        public static string GetLoggingStatusConfigFileName()
        {
            string strCheckinBaseDirecotry = AppDomain.CurrentDomain.BaseDirectory + "LoggingStatus.Config";

            if (File.Exists(strCheckinBaseDirecotry))
                return strCheckinBaseDirecotry;
            else
            {
                string strCheckinApplicationDirecotry = GetApplicationPath() + "LoggingStatus.Config";

                if (File.Exists(strCheckinApplicationDirecotry))
                    return strCheckinApplicationDirecotry;
                else
                    return string.Empty;
            }
        }
        /// <summary>
        /// Read the xml file and getthe logging status
        /// </summary>
        /// <param name="strXmlPath"></param>
        /// <returns></returns>
        public static bool GetValueFromXml(string strXmlPath)
        {
            try
            {
                //Open a FileStream on the Xml file
                FileStream docIn = new FileStream(strXmlPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);

                XmlDocument contactDoc = new XmlDocument();
                //Load the Xml Document
                contactDoc.Load(docIn);

                //Get a node
                XmlNodeList UserList = contactDoc.GetElementsByTagName("LoggingEnabled");

                //get the value
                string strGetValue = UserList.Item(0).InnerText.ToString();

                if (strGetValue.Equals("0"))
                    return false;
                else if (strGetValue.Equals("1"))
                    return true;
                else
                    return false;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// If the LogFile path is empty then, it will write the log entry to LogFile.txt under application directory.
        /// If the LogFile.txt is not availble it will create it
        /// If the Log File path is not empty but the file is not availble it will create it.
        /// </summary>
        /// <param name="objException"></param>
        /// <returns>false if the problem persists</returns>
        public static bool CustomErrorRoutine(Exception objException)
        {
            string strPathName = string.Empty;
            if (strLogFilePath.Equals(string.Empty))
            {
                //Get Default log file path "LogFile.txt"
                strPathName = GetLogFilePath();
            }
            else
            {

                //If the log file path is not empty but the file is not available it will create it
                if (false == File.Exists(strLogFilePath))
                {
                    if (false == CheckDirectory(strLogFilePath))
                        return false;

                    FileStream fs = new FileStream(strLogFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                    fs.Close();
                }
                strPathName = strLogFilePath;

            }

            bool bReturn = true;
            // write the error log to that text file
            if (true != WriteErrorLog(strPathName, objException))
            {
                bReturn = false;
            }
            return bReturn;
        }
        /// <summary>
        /// Write Source,method,date,time,computer,error and stack trace information to the text file
        /// </summary>
        /// <param name="strPathName"></param>
        /// <param name="objException"></param>
        ///
        /// <returns>false if the problem persists</returns>
        public static bool WriteErrorLog(string strPathName, Exception objException)
        {
            bool bReturn = false;
            string strException = string.Empty;
            try
            {
                sw = new StreamWriter(strPathName, true);
                sw.WriteLine("Source        : " + objException.Source.ToString().Trim());
                sw.WriteLine("Method        : " + objException.TargetSite.Name.ToString());
                sw.WriteLine("Date        : " + DateTime.Now.ToLongTimeString());
                sw.WriteLine("Time        : " + DateTime.Now.ToShortDateString());
                sw.WriteLine("Computer    : " + Dns.GetHostName().ToString());
                sw.WriteLine("Error        : " + objException.Message.ToString().Trim());
                sw.WriteLine("Stack Trace    : " + objException.StackTrace.ToString().Trim());
                sw.WriteLine("^^-------------------------------------------------------------------^^");
                sw.Flush();
                sw.Close();
                send_email("Membership Log", strPathName);
                bReturn = true;
            }
            catch (Exception)
            {
                bReturn = false;
            }
            return bReturn;
        }

        /// <summary>
        /// Send mail error log text file
        /// </summary>
        /// <returns>Mail send</returns>
        public static void send_email(string body, string file)
        {

            MailMessage msg = new MailMessage();
            msg.IsBodyHtml = true;
            msg.From = new MailAddress("xxxx@gmail.com", "xxxx");
            msg.To.Add(new MailAddress("xxxx@gmail.com"));
            msg.To.Add(new MailAddress("xxxx@gmail.com"));


            msg.Subject = "Membership Error Log";
            msg.Body = body;
            msg.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure;
            Attachment at = new Attachment(file);
            msg.Attachments.Add(at);
            SmtpClient smtp = new SmtpClient("smtp.gmail.com");
            smtp.Credentials = new System.Net.NetworkCredential("xxxxx@gmail.com", "xxxxxx");
            smtp.EnableSsl = true;
            smtp.Send(msg);

        }

        /// <summary>
        /// Check the log file in applcation directory. If it is not available, creae it
        /// </summary>
        /// <returns>Log file path</returns>
        public static string GetLogFilePath()
        {
            try
            {
                // get the base directory
                string baseDir = AppDomain.CurrentDomain.BaseDirectory + AppDomain.CurrentDomain.RelativeSearchPath;

                // search the file below the current directory
                string retFilePath = baseDir + "//" + "LogFile.txt";

                // if exists, return the path
                if (File.Exists(retFilePath) == true)
                    return retFilePath;
                //create a text file
                else
                {
                    if (false == CheckDirectory(retFilePath))
                        return string.Empty;

                    FileStream fs = new FileStream(retFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                    fs.Close();
                }

                return retFilePath;
            }
            catch (Exception)
            {
                return string.Empty;
            }
        }
        /// <summary>
        /// Create a directory if not exists
        /// </summary>
        /// <param name="strLogPath"></param>
        /// <returns></returns>
        public static bool CheckDirectory(string strLogPath)
        {
            try
            {
                int nFindSlashPos = strLogPath.Trim().LastIndexOf("\\");
                string strDirectoryname = strLogPath.Trim().Substring(0, nFindSlashPos);

                if (false == Directory.Exists(strDirectoryname))
                    Directory.CreateDirectory(strDirectoryname);

                return true;
            }
            catch (Exception)
            {
                return false;

            }
        }

        public static string GetApplicationPath()
        {
            try
            {
                string strBaseDirectory = AppDomain.CurrentDomain.BaseDirectory.ToString();
                int nFirstSlashPos = strBaseDirectory.LastIndexOf("\\");
                string strTemp = string.Empty;

                if (0 < nFirstSlashPos)
                    strTemp = strBaseDirectory.Substring(0, nFirstSlashPos);

                int nSecondSlashPos = strTemp.LastIndexOf("\\");
                string strTempAppPath = string.Empty;
                if (0 < nSecondSlashPos)
                    strTempAppPath = strTemp.Substring(0, nSecondSlashPos);

                string strAppPath = strTempAppPath.Replace("bin", "");
                return strAppPath;
            }
            catch (Exception)
            {
                return string.Empty;
            }
        }
    }