using Microsoft.Azure.WebJobs.Host;
using Microsoft.SharePoint.Client;
using System.Data;
using System.IO;
using System;
using System.Collections.Generic;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using System.Linq;
namespace Helper
{
public static class ConvertExcelDataToList
{
public static List<string> ReadExcelData(ClientContext clientContext, string fileName, int rowNoToStart)
{
var strErrorMsg = string.Empty;
var globalList = new List<string>();
const string lstDocName = "DocumentList";
try
{
var list = clientContext.Web.Lists.GetByTitle(lstDocName);
clientContext.Load(list.RootFolder);
clientContext.ExecuteQuery();
var fileServerRelativeUrl = $"{list.RootFolder.ServerRelativeUrl}/{fileName}";
Microsoft.SharePoint.Client.File file = clientContext.Web.GetFileByServerRelativeUrl(fileServerRelativeUrl);
ClientResult<Stream> data = file.OpenBinaryStream();
clientContext.Load(file);
clientContext.ExecuteQuery();
using (var mStream = new MemoryStream())
{
if (data != null)
{
data.Value.CopyTo(mStream);
using (var document = SpreadsheetDocument.Open(mStream, false))
{
var workbookPart = document.WorkbookPart;
var sstpart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
var sst = sstpart.SharedStringTable;
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;
var rows = workSheet.Descendants<Row>().ToList();
for (var a = rowNoToStart-1; a < rows.Count; a++)
{
log.Info($"Start of row {a}...");
var rowElement = rows.ElementAt(a);
var rowData = GetColumnValues(log, rowElement, sst);
globalList.Add(string.Join(",", rowData));
log.Info($"{string.Join(",", rowData)}");
log.Info($"End of row {a}.");
}
}
}
}
}
catch (Exception e)
{
strErrorMsg = e.Message;
log.Error(e.ToString());
log.Error($"ERROR: {strErrorMsg}");
}
finally
{
foreach (var item in globalList)
{
log.Info($"Rows: {item}");
}
}
return globalList;
}
/// <summary>
/// Get the cell value
/// </summary>
/// <param name="rowElement">The specific row to check</param>
/// <param name="sst">The string table to use for the value lookup</param>
/// <return>All the column values in the specific row</return>
private static List<string> GetColumnValues(TraceWriter log, Row rowElement, SharedStringTable sst)
{
log.Info($"Getting column values.");
var rowData = new List<string>();
int numberOfColumns = rowElement.Descendants<Cell>().Count();
for (var columnNumber = 0; columnNumber < numberOfColumns; columnNumber++)
{
var cell = rowElement.ElementAtOrDefault(columnNumber);
// End of the values
if (cell == null)
{
break;
}
var cellReference = GetCellNumber(log, cell);
var cellText = GetCellValue(log, cell, sst);
var index = GetIndexValue(log, cellReference);
EnsureListHasPriorItems(log, ref rowData, index, "NULL");
rowData.Add(cellText);
}
return rowData;
}
/// <summary>
/// Ensures the list has the number of prior items already in the list.
/// </summary>
/// <param name="currentList">REFERENCE of the current list you need adjusted</param>
/// <param name="numberOfUnits">The number of units you need available within the list - 1. If you pass 5 it will ensure the list has 4 items.</param>
/// <param name="defaultValue">The default value to pass when creating the 'empty' items</param>
private static void EnsureListHasPriorItems(TraceWriter log, ref List<string> currentList, int numberOfUnits, string defaultValue = "NULL")
{
log.Info($"Ensuring the list size to be {numberOfUnits}.");
while (currentList.Count < numberOfUnits)
{
currentList.Add(defaultValue);
}
}
/// <summary>
/// Get the cell value
/// </summary>
/// <param name="cell">The cell to check</param>
/// <param name="sst">The string table to use for the value lookup</param>
/// <return>The cell value</return>
private static string GetCellValue(TraceWriter log, OpenXmlElement cell, SharedStringTable sst)
{
log.Info("Getting the cell value.");
var cellText = cell.InnerText;
int ssid;
if ((cell is Cell excelCell && excelCell.DataType != null && excelCell.DataType.Value == CellValues.SharedString) &&
(int.TryParse(cellText, out ssid) && sst.ChildElements.Count > ssid))
{
cellText = sst.ChildElements[ssid].InnerText;
}
log.Info($"Cell value is {cellText}.");
return cellText;
}
/// <summary>
/// Get the cell number, eg A1 or BC24
/// </summary>
/// <param name="cell">The cell to check</param>
/// <return>The cell number</return>
private static string GetCellNumber(TraceWriter log, OpenXmlElement cell)
{
log.Info("Getting the cell location.");
// Perhaps use .CellReference property
return cell.OuterXml.Split(' ')[1].Split('\"')[1];
}
/// <summary>
/// Get the Index value of the current cell
/// </summary>
/// <param name="cellNumber">The cell number you require the index value for</param>
/// <return>An integer representing the cell number</return>
private static int GetIndexValue(TraceWriter log, string cellNumber)
{
log.Info($"Building the index value for cell {cellNumber}.");
var totalNumber = 0;
foreach (var character in cellNumber)
{
if (char.IsLetter(character))
{
totalNumber += char.ToUpper(character) - 65;
}
else
{
break;
}
}
log.Info($"The index value of {cellNumber} is {totalNumber}.");
return totalNumber;
}
}
}
