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
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
Conclusion
Hope you understand how to load excel data into Custom List in SharePoint using object model.
Export an Excel table to a SharePoint list
Export Excel Data into SharePoint List
Import Excel spread sheet to SharePoint list
Upload excel data into SharePoint list
Upload Spreadsheets into SharePoint Lists
June 14, 2012
В· 
Adi В· 
35 Comments
Tags: Export excel, Export excel data into SharePoint, Upload Excel data into SharePoint Custom List В· Posted in: Sharepoint 2007, Sharepoint 2010
35 Responses
Great Post…!! It saves lot of time for me..
.
Thanks alot.
I don’t understand how am I supposed to utilize this? as a console application? event receiver? visual web part?
Hi Billy Williams,
Its an utility functions which can be used any where
ex: If you want to use it in webpart, provide file path or use any file upload control. Once you get the path from the users, use the code as in the post
Is it possible to use this code in a workflow which will run daily?
Hi Chaney,
It is possible to use, there shouldn’t be a problem.
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
Where does this TABLE_NAME come from?
Hi Chaney,
In the method GetExcelSheetNames, the datatable ‘dt’ will be filled with connection.GetOleDBSchemaTable method
var dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
Oledb connection object return the table in a specific format
dt.Columns[0].ColumnName will be “TABLE_CATALOG”
dt.Columns[1].ColumnName will be “TABLE_SCHEMA”
dt.Columns[2].ColumnName will be “TABLE_NAME”
For our purpose we have to use the third column i.e. “TABLE_NAME”
Hope your query is resolved now
Hi Adi,
I implemente this as part of a webpart under a button clieck event. But the application is taking excel sheet from the server rather than the client machine. I am having the same path in both server and client machine. When i run the application, it is reading data from the shett existing in server side. Please reply me soon incase u see my mail, as i am in very urget need of this solution.
Hi Raghavendra,
If you are using it in the webpart, try taking the path from any fileupload control. See line 17 in the code how to take the file path from client machine. If you are using direct string with path, it will always check the path in the server not the client side.
Let me know if you are stuck.
Regards,
Adi
Hi Adi, Thanks a lot for immediate help. Actally i did the same, i am taking the path from file upload control, just like you told in 17 line. But the result is same, it is again looking for the selected path from control in the server.
I added file upload control, just to take the path when user browse and select the file. am not uploading any where. In the button (submit) clieck event, i have taken your code. But application is searching for the control returned path in server.
Will it be solved, if i use your other approach with interop method. Thanks in advance.
oops..I got the problem.
Method 1: Save the file to any default location in server. After all your process is completed try to delete the uploaded excel file if required
string filename = Path.GetFileName(fileUpload1.FileName); fileUpload1.SaveAs(Server.MapPath("~/") + filename);Method 2:
Without saving the file you can use file stream to build the datatable and create list out of it, for example
List<string> rows = new List<string>(); using (StreamReader reader = new StreamReader(fileUpload1.PostedFile.InputStream)) { string record = reader.ReadLine(); while (record != null) { rows.Add(record); record = reader.ReadLine(); } foreach (string s in rows) { //write code to prepate datatable out of the rows } }If you are still stuck, may be I can post some sample code later
Regards,
Adi
HI Adi,
I am using above code but not able to update excel item in list. I am using file upload control and saving it to temp location.
data from excel file is getting easily and able to bind in Grid.
fields are also coming in insertlist method but unable to upload in LISt. update into list method is working wihtout error but not inserting values of exel
please suggest me what to do
Hi Deepak,
The above code “InsertIntoList” method inserts new items to SharePoint list but will not update.
SPListItem newContact = contactList.Items.Add(); — this creates new item in the list
I did not get your point that “update into list method is working without error but not inserting values of excel”
Can you give somemore info so that I can help?
To make it simple for inserting item into list, following is the code (assuming FirstName column exists in your list)
SPListItem newContact = contactList.Items.Add();
newContact["FirstName"] = “Adi”;
newContact.Update()
Is the above code inserting item in your list? Let me know if you are stuck at any point.
Regards,
Adi
Thanks Adi,
For quick reply..
I found the error.. Silly mistake i have done.
I was using oList.Update();//list object
resolved by using item object : oListItem.Update();
cool, your issue got resolved
HI Adi,
for above example, How we can avoid duplicate entries in LIST.
If some one uploading excel sheet again with updated records.
Thanks in advance.
Hi Adi,
My requirement is when page load happens,I should fetch the latest data from excel to sharepoint list and subsequently we use Visio to display the data to end-users. Is it possible to use the above code to bring Excel data to Sharepoint list?
Thanks in advance for your help!
Thanks,
Balaji
Hi Balaji,
Yes you can use the code. Make sure you modify the InsertIntoList method with your respective list schema and also change excel file location.
Cheers,
Adi
Thanks Adi for your quick reply. I need to upload the excel data into list on page load without using any controls. I tried with Content Editor web part but it’s not working. I think am using wrong web part for this. It would be great and helpful if i get assistance from you on the usage of web part for this code.
Thanks,
Balaji
Hi Adi,
I have created a webpart with fileupload control which takes excel sheet and exports the excel data into the custom sharepoint list.I’m facing issue with the person lookup field while adding userid’s to it. I’m getting invalid lookup value error.Could you please provide some solution on how to add data to the person or group lookup field?I know one way of adding the data to it i.e., ’87#,Rama krishna Mekela’ , but it won’t be possible to mention id’s for all the users in excel column.
Please assist on this issue.Thanks in advance.
Usha.
Hi Balaji,
There is no outofbox webpart which can execute custom code on the fly. In your case you have to create a custom webpart with the above code and deploy it. Then in your page you add the custom webpart same like adding content editor webpart to the page.
After that whenever pageloads the code will get executed. Hope you understood the procedure. Let me know if you are still stuck up.
Thanks,
Adi
Hi Usha,
Person or group value should not be given as text unless you try tweaking with userid which is not suggestible. You should provide SPFieldUserValue to the Person or group column.
Consider Line 84 in the above code
This adds listTable.Rows[iRow][0] value to FirstName column, which is straight forward way.
newContact["FirstName"] = Convert.ToString(listTable.Rows[iRow][0]);Considering your list has “UserID” column, and your excel sheet column has value coming from listTable.Rows[iRow][7] (assuming value is Rama Krishna Mekela)
You introduce one more method for that.
newContact["UserID"] = GetUserValue(Convert.ToString(listTable.Rows[iRow][7]),mySite);
public SPFieldUserValue GetUserValue(string userid, SPWeb site)
{
SPUser requireduser = site.EnsureUser(userid);
SPFieldUserValue uservalue = new SPFieldUserValue(site, requireduser.ID, requireduser.LoginName);
return uservalue;
}
This will convert your string ‘Rama Krishna Mekela’ to respective user id. You update the code with this method and it should resolve your requirement.
One more point to re check:
I am assuming your excel sheet cell have only one user id. If you have multiple user ids in the cell i.e. listTable.Rows[iRow][7] gets user1;user2;user3; then you have to modify code to split and add SPFieldUserValue into SPFieldUserValueCollection
Hope this reply help you.
Cheers,
Adi
Hi Adi,
Thanks for your help.My issue was resolved.Many thanks.
Usha
Dear Adi,
I am getting dt.Rows.Count=0.Bcz of that it is not going inside the for loop. Please help me on this. Or can you send me the excel file for reference.
Regards,
Jeswin
Hi Jeswin,
There is no special in my excel. It is a normal one.
What I understood from your comment is that, you are unable to get the sheet names from the method used in Line 33.
string[] sheetNames = GetExcelSheetNames(connectionString);
Can you try giving the Sheet names in static like below? Comment Line 33 and use below one. Replace “Sheet1″,.. with your actual workbook names.
String[] excelSheets = new string[]{“Sheet1″,”Shee2″,”Sheet3″};
If it is working then we can narrow down your problem to getting sheet names from excel. Let me know the outcome.
Regards,
Adi
Dear Adi,
Thanks for the Quick Response. I figure out the issue. The issue is with the File Upload Control. So Using Jquery I passed the URL of File Upload control into One text box and passing that textbox value as file Name. So my issue resolved. Thanks. And I have one more doubt. There is any issue in Security of a file.Bcz i tried to access the file from Downloads folder, I am getting error in GetExcelSheetNames(connectionString); method.
Regards,
Jeswin
Hi Jeswin,
The issue might be due to the folder permission. I suppose there isn’t modification that can be done to the method to resolve folder level security.
Anyhow what is the error you are getting?
Regards,
Adi
Hi Adi,
The fileupload control in my webpart will store the excel documents in the C drive folder located on the server (like “@”C:\tracker”)and while inserting data into sharepoint list the excel file will be picked up from this location. The users are getting access denied error while uploading files using fileupload control. I think i’m not getting this error since i have access to the server. Could you please help me in resolving this issue?
Thanks in advance.
Usha.
Hi Usha,
You better run the code with runwithelevated previlages. This will run the code on Service Account credentials. Give permission to the folder for Service Account.
This should resolve access denied error as every user will uploading impersonated with Service Account
(Service Account is the user credentials which is set in your IIS application pool of the SharePoint Web Application)
Hope this helps.
Regards,
Adi
I am using the upload control in webpart. The code is running fine for the first time for file A. However when I try to upload aany other file B. The code is still referencing file A. I am using same code which is mentioned at the top of this article I have commented the hardcoded path uncommented this line mentioned below,
string fileName = fileUpload1.PostedFile.FileName
Can you please help me on this. Thanks in advance!
Hi Nakul,
Can you please try this one
String fileName = FileUpload1.FileName;
Let me know if you are still facing issues.
Regards,
Adi
@Adi, im currently using SP2013. And I have lookup values.
The list must also gets the lookup values when the excel is uploaded. I have another code and I’m just looking for alternation to make the lookup values saved. Do u have a suggestion for this ?
Hi Sandeep,
If it is lookup value, then you need to get the id of the value from lookup list. Then insert that value.
Ex:Suppose, in excel you have Country value “India” which is lookup value from the list ‘CountryList’. Before inserting the value “India” as new item, write a method that will give you the ID of the value “India” from the “CountryList”. Then insert that id. This will suit to your requirement.
Thanks and Regards,
Adi
Hello! I’m interested in the method 2 to use file stream, but I don’t know where to put the code to build the datatable and insert into the list.
It must be into foreach?
foreach (string s in rows)
{
string[] sheetNames = GetSheetNames(connectionString);
if((sheetNames != null) && (sheetNames.Length > 0))
{
DataTable dt = null;
…
dt = new DataTable();
da.Fill(dt);
InsertIntoList(dt, “ContactList”);
}
}
Please help me and thanks in advance!
My requirement is to upload a .xlsx file into document library of sharepoint 2010 and have to update the list item in another list.
I am uploading excel sheet(.xlsx) but when i am trying to fill the data table with excel value it is giving me error “External table is not in expected format”.
Kindly tell me how to resolve this issue.
Leave a Reply