Read excel data from document library saving as list items using CSOM in SharePoint

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)

    3. DocumentFormat.OpenXml.dll

    #https://www.nuget.org/packages/DocumentFormat.OpenXml/

    Install-Package DocumentFormat.OpenXml -Version 2.8.1

5. Write below code and deploy (F5) to see results.
...................................................................................

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.