Read excel data from document library saving as list items using CSOM in SharePoint.
1. Upload an excel file into Document library.
This is sample excel file with sample Employee data.
Upload Employee data excel file into Document Library.
2. Create an empty custom list.
3. Open visual studio File -> New -> Console Application (C#)
4. Add below .dll to our solution.
1. Microsoft.SharePoint.Client.dll (15.0.0.0)
2. Microsoft.SharePoint.Client.Runtime.dll (15.0.0.0)
5. Write below code and deploy (F5) to see results.
...................................................................................
6. Check the Employee list, Excel data should be created as items in this list.
1. Upload an excel file into Document library.
This is sample excel file with sample Employee data.
1. Microsoft.SharePoint.Client.dll (15.0.0.0)
2. Microsoft.SharePoint.Client.Runtime.dll (15.0.0.0)
3. DocumentFormat.OpenXml.dll
#https://www.nuget.org/packages/DocumentFormat.OpenXml/
Install-Package DocumentFormat.OpenXml -Version 2.8.1
...................................................................................
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SharePoint.Client;
using SP = Microsoft.SharePoint.Client;
using System.Security;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using System.Data;
using System.Configuration;
using System.Net.Mail;
using Microsoft.SharePoint.Client.Utilities;
namespace ReadExcelDataFromDocumentLibrary
{
class Program
{
static void Main(string[] args)
{
const string webUrl = "https://sharepointonline01.sharepoint.com/sites/dev2/";
const string USER = "sreekanth@sharepointonline01.onmicrosoft.com";
const string PWD = "Password";
const string Domain = "domain";
//Authentication
for on premises SharePoint
//clientContext.Credentials
= new System.Net.NetworkCredential(USER, PWD, Domain);
using (ClientContext clientContext = new ClientContext(webUrl))
{
//Authentication
for SharePoint Online
SecureString passWord = new SecureString();
foreach (char c in PWD.ToCharArray())
{
passWord.AppendChar(c);
}
clientContext.Credentials = new SharePointOnlineCredentials(USER,
passWord);
Microsoft.SharePoint.Client.List spList =
clientContext.Web.Lists.GetByTitle("Documents");
ReadFileName(clientContext);
}
Console.WriteLine("Please press
any key to exit.");
Console.ReadKey();
}
private static void ReadFileName(ClientContext clientContext)
{
string fileName = string.Empty;
bool isError = true;
const string fldTitle = "LinkFilename";
const string lstDocName = "Documents";
const string strFolderServerRelativeUrl = "/sites/dev2/Shared Documents";
string strErrorMsg = string.Empty;
try
{
List list = clientContext.Web.Lists.GetByTitle(lstDocName);
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = @"<View
Scope='Recursive'><Query></Query></View>";
camlQuery.FolderServerRelativeUrl = strFolderServerRelativeUrl;
SP.ListItemCollection
listItems = list.GetItems(camlQuery);
clientContext.Load(listItems,
items => items.Include(i => i[fldTitle]));
clientContext.ExecuteQuery();
for (int i = 0; i < listItems.Count; i++)
{
SP.ListItem itemOfInterest =
listItems[i];
if (itemOfInterest[fldTitle] !=
null)
{
fileName =
itemOfInterest[fldTitle].ToString();
if (i == 0)
{
ReadExcelData(clientContext, itemOfInterest[fldTitle].ToString());
}
}
}
isError = false;
}
catch (Exception e)
{
isError = true;
strErrorMsg = e.Message;
}
finally
{
if (isError)
{
//Logging
}
}
}
private static void ReadExcelData(ClientContext clientContext, string fileName)
{
bool isError = true;
string strErrorMsg = string.Empty;
const string lstDocName = "Documents";
try
{
DataTable dataTable = new DataTable("EmployeeExcelDataTable");
List list = clientContext.Web.Lists.GetByTitle(lstDocName);
clientContext.Load(list.RootFolder);
clientContext.ExecuteQuery();
string fileServerRelativeUrl = list.RootFolder.ServerRelativeUrl
+ "/" + fileName;
Microsoft.SharePoint.Client.File file =
clientContext.Web.GetFileByServerRelativeUrl(fileServerRelativeUrl);
ClientResult<System.IO.Stream> data = file.OpenBinaryStream();
clientContext.Load(file);
clientContext.ExecuteQuery();
using (System.IO.MemoryStream mStream = new System.IO.MemoryStream())
{
if (data != null)
{
data.Value.CopyTo(mStream);
using (SpreadsheetDocument
document = SpreadsheetDocument.Open(mStream, false))
{
WorkbookPart
workbookPart = document.WorkbookPart;
IEnumerable<Sheet> sheets =
document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
string relationshipId =
sheets.First().Id.Value;
WorksheetPart
worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet =
worksheetPart.Worksheet;
SheetData sheetData =
workSheet.GetFirstChild<SheetData>();
IEnumerable<Row> rows =
sheetData.Descendants<Row>();
foreach (Cell cell in rows.ElementAt(0))
{
string str =
GetCellValue(clientContext, document, cell);
dataTable.Columns.Add(str);
}
foreach (Row row in rows)
{
if (row != null)
{
DataRow dataRow =
dataTable.NewRow();
for (int i = 0; i <
row.Descendants<Cell>().Count(); i++)
{
dataRow[i] = GetCellValue(clientContext, document, row.Descendants<Cell>().ElementAt(i));
}
dataTable.Rows.Add(dataRow);
}
}
dataTable.Rows.RemoveAt(0);
}
}
}
UpdateSPList(clientContext,
dataTable, fileName);
isError = false;
}
catch (Exception e)
{
isError = true;
strErrorMsg = e.Message;
}
finally
{
if (isError)
{
//Logging
}
}
}
private static void UpdateSPList(ClientContext clientContext, DataTable dataTable, string fileName)
{
bool isError = true;
string strErrorMsg = string.Empty;
Int32 count = 0;
const string lstName = "EmployeesData";
const string lstColTitle = "Title";
const string lstColAddress = "Address";
try
{
string fileExtension = ".xlsx";
string fileNameWithOutExtension = fileName.Substring(0,
fileName.Length - fileExtension.Length);
if (fileNameWithOutExtension.Trim() == lstName)
{
SP.List oList =
clientContext.Web.Lists.GetByTitle(fileNameWithOutExtension);
foreach (DataRow row in dataTable.Rows)
{
ListItemCreationInformation
itemCreateInfo = new ListItemCreationInformation();
ListItem oListItem =
oList.AddItem(itemCreateInfo);
oListItem[lstColTitle]
= row[0];
oListItem[lstColAddress] = row[1];
oListItem.Update();
clientContext.ExecuteQuery();
count++;
}
}
else
{
count = 0;
}
if (count == 0)
{
Console.Write("Error: List: '" +
fileNameWithOutExtension + "' is not
found in SharePoint.");
}
isError = false;
}
catch (Exception e)
{
isError = true;
strErrorMsg = e.Message;
}
finally
{
if (isError)
{
//Logging
}
}
}
private static string GetCellValue(ClientContext clientContext, SpreadsheetDocument document, Cell cell)
{
bool isError = true;
string strErrorMsg = string.Empty;
string value = string.Empty;
try
{
if (cell != null)
{
SharedStringTablePart
stringTablePart = document.WorkbookPart.SharedStringTablePart;
if (cell.CellValue != null)
{
value =
cell.CellValue.InnerXml;
if (cell.DataType != null &&
cell.DataType.Value == CellValues.SharedString)
{
if (stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)] != null)
{
isError = false;
return
stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
}
}
else
{
isError = false;
return value;
}
}
}
isError = false;
return string.Empty;
}
catch (Exception e)
{
isError = true;
strErrorMsg = e.Message;
}
finally
{
if (isError)
{
//Logging
}
}
return value;
}
}
}
...................................................................................6. Check the Employee list, Excel data should be created as items in this list.
