public static void LoadRowsFromExcel(this DataTable dt, Stream st, LoadRowsFromSpreadsheetSettings settings)
{
using (var sd = SpreadsheetDocument.Open(st, false))
{
dt.LoadRowsFromExcel(sd, settings ?? new LoadRowsFromSpreadsheetSettings {
SheetNumber = 0
});
}
}
public LoadRowsFromSpreadsheetSettings(LoadRowsFromSpreadsheetSettings other)
: base(other)
{
if (other == null)
{
return;
}
this.UseSheetNameForTableName = other.UseSheetNameForTableName;
this.SheetNumber = other.SheetNumber;
this.SheetName = other.SheetName;
this.SkipRawRows = other.SkipRawRows;
this.SkipWhileTester = other.SkipWhileTester;
this.TreatAllValuesAsText = other.TreatAllValuesAsText;
}
private static bool LoadRowsFromExcel(this DataTable dt, SpreadsheetDocument sd, LoadRowsFromSpreadsheetSettings settings)
{
DataTableHelpers.RequiresZeroRows(dt, nameof(dt));
Requires.NonNull(sd, nameof(sd));
Requires.NonNull(settings, nameof(settings));
var rows = new List <IList <object> >();
var sharedStringDictionary = ConvertSharedStringTableToDictionary(sd);
var sheets = sd.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
int sheetNumber = 0;
foreach (var sheet in sheets)
{
if (sheetNumber == settings.SheetNumber || 0 == string.Compare(settings.SheetName, sheet.Name, true))
{
if (settings.UseSheetNameForTableName)
{
dt.TableName = sheet.Name;
}
string relationshipId = sheet.Id.Value;
var worksheetPart = (WorksheetPart)sd.WorkbookPart.GetPartById(relationshipId);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
IEnumerable <Row> eRows = sheetData.Descendants <Row>();
foreach (Row erow in eRows)
{
CreateRow:
var row = new List <object>();
rows.Add(row);
foreach (var cell in erow.Descendants <Cell>())
{
var cr = GetColRowFromCellReference(cell.CellReference);
if (rows.Count <= cr.Item2)
{
goto CreateRow;
}
while (row.Count < cr.Item1)
{
row.Add(null);
}
Debug.Assert(row.Count == cr.Item1);
var val = GetCellValue(sd, cell, settings.TreatAllValuesAsText, sharedStringDictionary);
row.Add(val);
}
}
GC.Collect();
IEnumerable <IList <object> > positionnedRows;
if (settings.SkipRawRows.HasValue)
{
positionnedRows = rows.Skip(settings.SkipRawRows.Value);
}
else if (settings.SkipWhileTester != null)
{
positionnedRows = rows.SkipWhile(settings.SkipWhileTester);
}
else
{
positionnedRows = rows;
}
dt.LoadRows(positionnedRows, settings);
return(true);
}
++sheetNumber;
}
if (settings.ThrowOnMissingSheet)
{
throw new Exception(string.Format(
"Sheet [{0}] was not found",
(object)settings.SheetNumber ?? (object)settings.SheetName));
}
return(false);
}