Visual Web part for read data from Excel Sheet and Insert into Data base

This blog is for read data from excel sheet and insert into database.

Step of reading data from excel sheet and insert into database.

     Step 1: Open .Net and Select New Project=> and Select SharePoint 2010=>Visual Web Part.

     Step 2: Open the .cs  file and put this code.

This code is for generate Xml for insert in databse:-

        private string GetXMLFromExcel(System.Web.HttpPostedFile file)
        {
            string savePath = string.Empty;
            string xmlData = string.Empty;
            string dirPath = string.Empty;
            dirPath = Path.Combine(Request.PhysicalApplicationPath, "BAUBUG");
            savePath = Path.Combine(dirPath, file.FileName);
            if (!Directory.Exists(dirPath))
            {
                 Directory.CreateDirectory(dirPath);
            }

            FUExcelSheet.PostedFile.SaveAs(savePath);
            xmlData = ReadDataFromExcel(savePath, file.FileName);
            System.IO.File.Delete(savePath);
            return xmlData;
        }

 This code return xml String code of Excel:-

        public string ReadDataFromExcel(string filePath, string fileName)
        {
            DataSet dataSet = null;
            OleDbConnection oledbConn = null;
            OleDbCommand command = null;
            OleDbDataAdapter dataAdapter = null;
            string strConnectionString = null;
            try
            {

                strConnectionString = ExcelProvider(fileName).ToString() + "Data Source=" + filePath + ExtendedProperties(fileName);
                oledbConn = new OleDbConnection(strConnectionString);
                // Opens OleDb Connection
                oledbConn.Open();
                string excelSheetName = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)
                    .Rows[0]["TABLE_NAME"].ToString().Replace("'", "");
                // Create OleDbCommand object and select data from worksheet Sheet1
                command = new OleDbCommand("SELECT * FROM [" + excelSheetName + "]", oledbConn);
                // Create new OleDbDataAdapter
                dataAdapter = new OleDbDataAdapter(command);
                // Create a DataSet which will hold the data extracted from the worksheet.
                dataSet = new DataSet("Root");
                // Fill the DataSet from the data extracted from the worksheet.
                dataAdapter.Fill(dataSet, "Detail");
                //foreach (DataRow dr in dataSet.Tables[0].Rows)
                //{

                //    string path = dr[13].ToString();
               //    dataSet.Tables[0].AcceptChanges();
              //}
           }
           catch (Exception)
            {
                // Throws the exception
                throw;
            }
            finally
            {
                oledbConn.Close();
                command.Dispose();
                dataAdapter.Dispose();
            }
            //return the value
            return dataSet.GetXml();
        }
        /// <summary>
        /// Excel provider
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        private string ExcelProvider(string FileName)
        {
            if (null != IsOpenXMLFormat(FileName))
            {
                if (Convert.ToBoolean(IsOpenXMLFormat(FileName)))
                {
                    return "Provider=Microsoft.ACE.OLEDB.12.0;";
                }
                else
                {
                    return "Provider=Microsoft.Jet.OLEDB.4.0;";
                }
            }
            else
            {
                return null;
            }
          }
         /// <summary>
        ///
        /// </summary>
        /// <param name="FileName">Check Excel file Type</param>
        /// <returns></returns>
        private bool? IsOpenXMLFormat(string FileName)
        {
            string[] splitByDots = FileName.Split(new char[1] { '.' });
            //Excel 97-2003 file
            if (splitByDots[splitByDots.Length - 1] == "xls")
                return false;
            //Excel 2007 file
            if (splitByDots[splitByDots.Length - 1] == "xlsx")
                return true;
            //Not an Excel Sheet
            return null;
        }
        private string ExtendedProperties(string FileName)
        {
            if (null != IsOpenXMLFormat(FileName))
            {
                if (Convert.ToBoolean(IsOpenXMLFormat(FileName)))
                {
                    return ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
                }
                else
                {
                    return ";Extended Properties=Excel 8.0;";
                }
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// Bind Bug Details Grid
        /// </summary>
        public void DsGetBugDetails()
        {
            try
            {
                gvBugDetails.DataSource = ExecuteDataSet("CusTableBAUIssue", "SpGetBugDetails");
                gvBugDetails.DataBind();
            }
            catch (Exception Ex)
            {
                throw;
            }
        }
     #region DataBaseOperation
        /// <summary>
        /// Open Sql conncetion
        /// </summary>
        private void OpenConnection()
        {
            try
            {
                connectionString = "Data Source=P2010;Initial Catalog=DataBaseName;integrated security=true;";
                connection = new SqlConnection(connectionString);
                if (connection.State != ConnectionState.Open) connection.Open();
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message + "OpenDBConnection");
            }
        }
        /// <summary>
        /// Close the connection
        /// </summary>
        public void ClosedbConnection()
        {
            try
            {
                connection.Dispose();
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message + " CloseDBConnection");
            }
        }
        /// <summary>
        /// Return DataSet
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="procedureName"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string tableName, string procedureName)
        {
            DataSet dsReturn;
            try
            {
                OpenConnection();
                sqlComm = new SqlCommand();
                sqlComm.Parameters.Clear();
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.CommandText = procedureName;
                dsReturn = new DataSet();
                sqlComm.Connection = connection;
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlComm);
                sqlDataAdapter.Fill(dsReturn, tableName);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
            finally
            {
                ClosedbConnection();
            }
            return dsReturn;
        }
 #endregion