Upload Excel data into SharePoint Custom List

Introduction

In this post we will see how to upload excel data(using oledb provider) into SharePoint 2007 / SharePoint 2010 Custom List from object model(c#)
What are the points that are covered

• Get the sheet name from excel file rather than providing static name
• Builds connection string for .xls and .xlsx files
• Reads excel data and inserts into SharePoint custom list

Upload excel data

The following code reads data from contacts.xls and inserts into SharePoint Custom List ContactList which I have created.
According to the schema of the ContactList, the method InsertIntoList() in the following code has relevant code.
You can modify according to the schema of your Custom List and Excel file

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.IO; using System.Data; using System.Data.OleDb; using Microsoft.SharePoint;   namespace TestSharepointProject {     public class UploadExcelData     {         public void LoadExcelData()         {             string fileName = "@"C:\AdisGroup\Contacts\contacts.xls";             //if you are using file upload control in sharepoint get the full path as follows assuming fileUpload1 is control instance             //string fileName = fileUpload1.PostedFile.FileName               string fileExtension = Path.GetExtension(fileName).ToUpper();             string connectionString = "";               if (fileExtension == ".XLS")             {                 connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "'; Extended Properties='Excel 8.0;HDR=YES;'";             }             else if (fileExtension == ".XLSX")             {                 connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties='Excel 12.0 Xml;HDR=YES;'";             }             if (!(string.IsNullOrEmpty(connectionString)))             {                 string[] sheetNames = GetExcelSheetNames(connectionString);                 if ((sheetNames != null) && (sheetNames.Length > 0))                 {                     DataTable dt = null;                     OleDbConnection con = new OleDbConnection(connectionString);                     OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + sheetNames[0] + "]", con);                     dt = new DataTable();                     da.Fill(dt);                     InsertIntoList(dt,"ContactList");                 }             }         }           private string[] GetExcelSheetNames(string strConnection)         {             var connectionString = strConnection;             String[] excelSheets;             using (var connection = new OleDbConnection(connectionString))             {                 connection.Open();                 var dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);                 if (dt == null)                 {                     return null;                 }                 excelSheets = new String[dt.Rows.Count];                 int i = 0;                   // Add the sheet name to the string array.                 foreach (DataRow row in dt.Rows)                 {                     excelSheets[i] = row["TABLE_NAME"].ToString();                     i++;                 }             }             return excelSheets;         }           private void InsertIntoList(DataTable listTable, string contactListName)         {               SPWeb mySite = null;             try             {                   mySite = SPContext.Current.Web; //create web object if context is null                 mySite.AllowUnsafeUpdates = true;                 SPList contactList = mySite.Lists[contactListName];                 for (int iRow = 0; iRow < listTable.Rows.Count; iRow++)                 {                     SPListItem newContact = contactList.Items.Add();                     newContact["FirstName"] = Convert.ToString(listTable.Rows[iRow][0]);                     newContact["LastName"] = Convert.ToString(listTable.Rows[iRow][1]);                     newContact["FullName"] = Convert.ToString(listTable.Rows[iRow][2]);                     newContact["LoginID"] = Convert.ToString(listTable.Rows[iRow][3]);                     newContact["EmailAddress"] = Convert.ToString(listTable.Rows[iRow][4]);                     newContact["PhoneNumber"] = Convert.ToString(listTable.Rows[iRow][5]);                     newContact["Company"] = Convert.ToString(listTable.Rows[iRow][6]);                     newContact.Update();                 }                 mySite.AllowUnsafeUpdates = false;             }             catch (Exception ex)             {                 //log exception             }             finally             {                 if (mySite != null) //don't dispose if the site is from SPContext                 {                     mySite.AllowUnsafeUpdates = false;                 }             }           }       } }

Remarks

In the above code
LoadExcelData method takes data from first sheet name i.e sheetNames[0]. Change this if you want to load from another sheetName
InsertIntoList method uses SPContext to get the current web object. If you are using the above code where SPContext is not available then you have to create SPWeb object and dispose it in finally block