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); }