There are many ways to upload data from excel in SharePoint. We can divide them in two categories
- SharePoint on premise: There is a good article which describes what are the ways available without any programming you can refer to it here: https://sharepointmaven.com/3-ways-import-excel-sharepoint/ . There are challenges in these methods, the biggest is that it does not allow all Column Types such as Metadata , Choice etc. Out of three explained there Import from excel is best but it will not allow Metadata column to be created
- SharePoint Online: Recently in SharePoint Online native support has been provided by Microsoft on uploading data from excel to SharePoint, there are few limitations but it works pretty well, you can refer to this article which has great details https://sharepointmaven.com/how-to-import-an-excel-spreadsheet-to-a-sharepoint-custom-list/
My blog talks about how you can use C# to upload data from excel to SharePoint.
Step 1: Read data from excel in C#: To do this the code is very simple but you need to have excel installed on the machine you are running the code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;
//Instantiate excel app and if not installed give a message
Application excelApp = new Application();
if (excelApp == null)
{
Console.WriteLine("Excel is not installed!!");
return;
}
//load the workbook from where data needs to be read
Workbook excelBook = excelApp.Workbooks.Open(@"path of excel file including file name");
_Worksheet excelSheet = excelBook.Sheets[1];
Range excelRange = excelSheet.UsedRange;
//get the number of rows and columns
int rows = excelRange.Rows.Count;
int cols = excelRange.Columns.Count;
//loop through them and print
for (int i = 1; i <= rows; i++)
{
for (int j = 1; j <= cols; j++)
{
//write the console
if (excelRange.Cells[i, j] != null && excelRange.Cells[i, j].Value2 != null)
{
Console.Write(excelRange.Cells[i, j].Value2.ToString() + "\t");
}
}
Step 2: Add data to SharePoint list: Note that there are different column types supported by SharePoint but my main focus area is to how to insert data in column type Choice, Number, Metadata, Multi line text
string siteUrl = "site url where data needs to be uploaded"
//create a context
ClientContext clientContext = new ClientContext(siteUrl);
//get the list in which data needs to be inserted
Microsoft.SharePoint.Client.List list = clientContext.Web.Lists.GetByTitle("name of the list");
//preparing list item which will be added
ListItemCreationInformation itemInfo = new ListItemCreationInformation();
ListItem listItemCreation = list.AddItem(itemInfo);
//this is needed for taxonomy / metadata field
var taxonomySession = TaxonomySession.GetTaxonomySession(clientContext);
var termStore = taxonomySession.GetDefaultSiteCollectionTermStore();
Once above is done we have to combine both so rest of the insert data code will be under 2nd for loop in read excel data part. Here we will assume that 1st column is a number, 2nd column a multi line text, 3rd a Taxonomy / Managed Metadata column, fourth a number column and fifth a choice column, please check the complete code below
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Client;
using Microsoft.SharePoint.Client.Taxonomy;
using System.Net.NetworkInformation;
using System.IO;
using System.Net;
namespace UploadDatatoSPfromExcel
{
class Program
{
static void Main(string[] args)
{
string siteUrl = "site url where data needs to be uploaded"
//create a context
ClientContext clientContext = new ClientContext(siteUrl);
//get the list in which data needs to be inserted
Microsoft.SharePoint.Client.List list = clientContext.Web.Lists.GetByTitle("name of the list");
//preparing list item which will be added
ListItemCreationInformation itemInfo = new ListItemCreationInformation();
ListItem listItemCreation = list.AddItem(itemInfo);
//this is needed for taxonomy / metadata field
var taxonomySession = TaxonomySession.GetTaxonomySession(clientContext);
var termStore = taxonomySession.GetDefaultSiteCollectionTermStore();
//Instantiate excel app and if not installed give a message
Application excelApp = new Application();
if (excelApp == null)
{
Console.WriteLine("Excel is not installed!!");
return;
}
//load the workbook from where data needs to be read
Workbook excelBook = excelApp.Workbooks.Open(@"path of excel file including file name");
_Worksheet excelSheet = excelBook.Sheets[1];
Range excelRange = excelSheet.UsedRange;
//get the number of rows and columns
int rows = excelRange.Rows.Count;
int cols = excelRange.Columns.Count;
//loop through them and print
for (int i = 1; i <= rows; i++)
{
//we have to define field / column name for each column in which data will //be inserted, note that each name should be internal name of SharePoint //column
string fieldNameNumber = "FieldNumber";
string fieldNameMultiLineText = "FieldMultilineText";
string fieldNameTaxonomy = "FieldTaxonmy";
string fieldNameNumber = "FieldNumber";
string fieldNameChoice = "FieldChoice"
for (int j = 1; j <= cols; j++)
{
//write the console
if (excelRange.Cells[i, j] != null && excelRange.Cells[i, j].Value2 != null)
{
Console.Write(excelRange.Cells[i, j].Value2.ToString() + "\t");
//I am skipping first row as it contains column name
if (j == 1 && i!=1)
{
//setting number column
listItemCreation [fieldNameNumber ] = excelRange.Cells[i, j].Value2.ToString();
}
if (j == 2 && i!=1)
{
//setting multi line text column
listItemCreation [fieldNameMultiLineText] = excelRange.Cells[i, j].Value2.ToString();
}
if (j == 3 && i!=1)
{
//setting fieldNameTaxonomy column
string fieldTaxonomyValue = excelRange.Cells[i, j].Value2.ToString();
//create variable to store GUID value of metadata
Guid CustomertermIdGuid = Guid.Empty;
//Important to set correct term set name here
var allTerms = termStore.GetTermSetsByName("<<name of term set>>", 1033);
var termSet = allTerms.GetByName("<<name of term set>>");
var terms = termSet.GetAllTerms();
clientContext.Load(terms, includes => includes.Include(k => k.Id, k => k.Name, k => k.Parent, k => k.Parent.Id, k => k.Parent.Name, k => k.IsRoot, k => k.LocalCustomProperties));
clientContext.ExecuteQuery();
foreach (var term in terms)
{
if (term.Name == Customer)
{
CustomertermIdGuid = term.Id;
}
}
fieldTaxonomyValue = Customer + '|' + CustomertermIdGuid.ToString();
}
if (j == 4 && i!=1)
{
//setting number column
listItemCreation [fieldNameNumber] = excelRange.Cells[i, j].Value2.ToString();
}
if (j == 5 && i!=1)
{
//setting chpice column
listItemCreation [fieldNameChoice] = excelRange.Cells[i, j].Value2.ToString();
//since this is the last column we should insert value in SharePoint as well
listItemCreation.Update();
clientContext.Load(listItemCreation);
clientContext.ExecuteQuery();
}
}
}
}
}
}
Important point to note is how I have set the Managed Metadata column, first we we need to get all terms from term set and then find correct GUID else it will not work.
Code is not structured very well but you can refer main parts to write your own code.
Subscribe to my YouTube Channel https://www.youtube.com/channel/UC6vLzWN-3aFG8dgTgEOlx5g
Connect with me on Twitter or Linked In and follow YouTube Channel or my blog https://twitter.com/SinhaKislay
https://www.linkedin.com/in/kislaysinha
/https://synkventures.com
/https://www.youtube.com/channel/UC6vL…
Kislay Sinha 