private bool CanAddThisRow(IEnumerable <Cell> row, DataTableDelimiter delimiter, DataTable dt)
{
switch (delimiter.RowEscapeMode)
{
case DataTableDelimiter.EnumEscapeMode.FirstCellIsEmpty:
var cell = (row.First().CellValue as CellValue)?.InnerText;
return(!String.IsNullOrEmpty(cell));
case DataTableDelimiter.EnumEscapeMode.MaximumRowNUmberAchieved:
return(dt.Rows.Count < delimiter.MaxRowNumber);
default:
throw new NotImplementedException();
}
}
/// <summary>
/// extracts the dataset from table stored inside sheet name
/// </summary>
/// <param name="sheetName"></param>
/// <param name="delimiter">Deleimiter to read properly the sheet</param>
/// <returns></returns>
public ExcelContentEditor ReadTableFromSheet(string sheetName, DataTableDelimiter delimiter)
{
if (this._Data != null)
{
throw new InvalidOperationException($"Unable to read file: override pre-existent data is forbidden!");
}
if (String.IsNullOrEmpty(sheetName))
{
throw new ArgumentNullException(nameof(sheetName));
}
if (delimiter == null)
{
throw new ArgumentNullException(nameof(delimiter));
}
DataTable dt = new DataTable();
using (MemoryStream stream = new MemoryStream())
{
// copy source file
stream.Write(_DocumentContent, 0, (int)_DocumentContent.Length);
//start editing
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true))
{
//Read the first Sheet from Excel file.
Sheet sheet = doc.WorkbookPart.Workbook.Sheets.ChildElements.OfType <Sheet>().Single(x => x.Name == sheetName);
//Get the Worksheet instance.
Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
//Fetch all the rows present in the Worksheet.
IEnumerable <Row> rows = worksheet.GetFirstChild <SheetData>().Descendants <Row>();
//Loop through the Worksheet rows.
foreach (Row row in rows)
{
int rowIndex = Array.IndexOf(rows.ToArray(), row);
if (rowIndex < delimiter.StartRowIndex)
{
continue;
}
//Use the first row to add columns to DataTable.
if (rowIndex == delimiter.StartRowIndex)
{
CreateColumnSchema(row.Descendants <Cell>(), doc, dt, delimiter.MaxColumnNumber);
}
else
{
//Add rows to DataTable.
var cells = row.Descendants <Cell>();
if (!CanAddThisRow(cells, delimiter, dt))
{
break;
}
dt.Rows.Add();
int i = 0;
foreach (Cell cell in cells)
{
// check on maximum side
if (i == dt.Columns.Count)
{
break;
}
// fill the dataset
var targetColumn = dt.Columns[i];
dt.Rows[dt.Rows.Count - 1][i] = GetValue(doc, cell);
i++;
}
}
}
}
}
this._Data = dt;
return(this);
}
