How to extract data from excel spreadsheet stored in Sharepoint document library?

SharePoint is a great platform for content management.  we can archive excel spreadsheet with version control enable in the SharePoint document library.

we can use the following steps to achieve the result.

step one. Upload the Excel spreadsheet to the  sharepoint document library using asp.net File Upload Control

  var spContext = SharePointContextProvider.Current.GetSharePointContext(Context);
                using (var clientAppContext = spContext.CreateUserClientContextForSPHost())
                {
                    List list = clientAppContext.Web.Lists.GetByTitle(sListTitle);
                    FileCreationInformation newFile = new FileCreationInformation();
                    newFile.Overwrite = true;
                    newFile.ContentStream = fileUploadDealerRules.PostedFile.InputStream;
                    string sFileName = System.IO.Path.GetFileName(fileUploadDealerRules.PostedFile.FileName);
                    sFileName = ReplaceIllegalChars(sFileName);
                    sNewFileName = string.Format("{0}_{1}{2}", System.IO.Path.GetFileNameWithoutExtension(fileUploadDealerRules.FileName), System.DateTime.Now.ToString("yyyy-MM-dd HHmmss"), System.IO.Path.GetExtension(fileUploadDealerRules.FileName));// Guid.NewGuid().ToString() + "_" + sFileName;
                    newFile.Url = sNewFileName;
                    Microsoft.SharePoint.Client.File fileToSp = list.RootFolder.Files.Add(newFile);
                    Microsoft.SharePoint.Client.ListItem item = fileToSp.ListItemAllFields;
                    clientAppContext.Load(item);
                    clientAppContext.Load(fileToSp);
                    clientAppContext.ExecuteQuery();
                }


step two. Read the excel spreadsheet content that stored in SP document library into the memory with C# memory stream

                using (var clientAppContext = spContext.CreateUserClientContextForSPHost())
                {
                    List list = clientAppContext.Web.Lists.GetByTitle(sListTitle);
                    clientAppContext.Load(list.RootFolder);
                    clientAppContext.ExecuteQuery();
                    string dealerRuleFile = list.RootFolder.ServerRelativeUrl + "/" + sNewFileName;
                    Microsoft.SharePoint.Client.File file = clientAppContext.Web.GetFileByServerRelativeUrl(dealerRuleFile);
                    ClientResult<System.IO.Stream> data = file.OpenBinaryStream();
                    clientAppContext.Load(file);
                    clientAppContext.ExecuteQuery();


step three. Read the content from the memory and store them in the data table using OpenXL library

using  DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;


using (var clientAppContext = spContext.CreateUserClientContextForSPHost())
                {
                    List list = clientAppContext.Web.Lists.GetByTitle(sListTitle);
                    clientAppContext.Load(list.RootFolder);
                    clientAppContext.ExecuteQuery();
                    string dealerRuleFile = list.RootFolder.ServerRelativeUrl + "/" + sNewFileName;
                    Microsoft.SharePoint.Client.File file = clientAppContext.Web.GetFileByServerRelativeUrl(dealerRuleFile);
                    ClientResult<System.IO.Stream> data = file.OpenBinaryStream();
                    clientAppContext.Load(file);
                    clientAppContext.ExecuteQuery();
                    using (System.IO.MemoryStream mStream = new System.IO.MemoryStream())
                    {
                        if (data != null)
                        {
                            data.Value.CopyTo(mStream);
                            using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument document = DocumentFormat.OpenXml.Packaging.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))
                                {
                                    dataTable.Columns.Add(GetCellValue(document, cell));
                                }

                                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(document, row.Descendants<Cell>().ElementAt(i));
                                        }
                                        if (!string.IsNullOrEmpty(Common.Utility.SubaruUtil.ConvertToString(dataRow[0])))
                                        {
                                            dataTable.Rows.Add(dataRow);
                                        }
                                    }
                                }
                                dataTable.Rows.RemoveAt(0);

                            }
                        }
                    }
                }
            }


private helper method to get each individual cell value of the excel spreadsheet.

 private string GetCellValue(SpreadsheetDocument document, Cell cell)
        {
            string value = string.Empty;
            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)
                        {
                            return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
                        }
                    }
                    else
                    {
                        return value;
                    }
                }
            }
            return string.Empty;
        }

Step Four. use bulk SqlBulkCopy to batch the data into SQL Server.