Populate SharePoint's SPList with records from MSExcel file

In this post, I will focus on the way to retrieve the records from MS Excel file and then populate it in SPList of SharePoint.


Code snippet(comments are self-explainatory)...

int count = 0;
try
{
    SPUserToken sysToken = SPContext.Current.Site.SystemAccount.UserToken;
    using (var spSite = new SPSite(SPContext.Current.Site.ID, sysToken))
    {
        using (var spWeb = spSite.OpenWeb(SPContext.Current.Web.ID))
        {
            //Get Interviewers SPList
            SPList oList = spWeb.Lists["ListName"];

            #region uploading Excel

            //Here for saving file in FileSystem, creating time stamp
            string strFileName = DateTime.Now.ToString("ddMMyyyy_HHmmss");
            //FileUpload file;
            string strFileType 
                   = System.IO.Path.GetExtension(file.PostedFile.FileName)
                     .ToString().ToLower();

            //Check file type
            if (strFileType == ".xls" || strFileType == ".xlsx")
            {
                //Create Directory specified in parameter for downloading
                string directoryPath 
                       = HttpContext.Current.Server.MapPath("~/UploadedExcel");

                if (!(System.IO.Directory.Exists(directoryPath)))
                {
                    System.IO.Directory.CreateDirectory(directoryPath);
                }

                //Save file in FileSystem  
                file.SaveAs(HttpContext.Current.Server.
                   MapPath("~/UploadedExcel/" + strFileName + strFileType));
             }
             else
             {
                 throw new FormatException("Only excel file is allowed");
             }

             string strNewPath 
                    = HttpContext.Current.Server.MapPath("~/UploadedExcel/" +
                                 strFileName + strFileType);

             //Connection String to Excel Workbook
             string connectionString = string.Empty;
             if (strFileType.Trim() == ".xls")
             {
                 connectionString 
                     = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
                       strNewPath + 
                       ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
             }
             else if (strFileType.Trim() == ".xlsx")
             {
                 connectionString 
                      = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        strNewPath + 
                        ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
             }


             OleDbConnection conn = new OleDbConnection(connectionString);
             //This will open excel file
             conn.Open();
             //Get the Excel Sheet name from Config
             string sheetName = "Sheet1";
             if (!string.IsNullOrEmpty(System.Configuration.ConfigurationSettings.
                    AppSettings["ExcelSheetName"]))
             {
                 sheetName = System.Configuration.ConfigurationSettings.
                             AppSettings["ExcelSheetName"];
             }

             string strSQL = "SELECT * FROM [" + sheetName + "$]";

             OleDbCommand cmd = new OleDbCommand(strSQL, conn);
             DataSet ds = new DataSet();
             ds.Locale = CultureInfo.InvariantCulture;
             OleDbDataAdapter da = new OleDbDataAdapter(cmd);
             da.Fill(ds);

             spWeb.AllowUnsafeUpdates = true;
             SPListItem listItem = null;

             foreach (DataRow dr in ds.Tables[0].Rows)
             {
                 //Create new SPListItem
                 listItem = oList.Items.Add();

                 listItem["FirstName"] 
                    = dr["First Name"] != null ? dr["First Name"].ToString() : null;
                 listItem["LastName"] 
                    = dr["Last Name"] != null ? dr["LastName"].ToString() : null;
                            
                 listItem.Update();

                 listItem = null;
             }

             spWeb.AllowUnsafeUpdates = false;

             conn.Close();
             //Delete the saved excel file from FileSystem
             System.IO.File.Delete(strNewPath);

             #endregion

             //Increment the record count
             count = oList.Items.Count;
         }
     }

     return count;
}
catch (SPException)
{
    throw;
}