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);
}
public static DataSet ExecuteReadDataSet(this SqlCommand command, string tableNamePattern = null)
{
var conn = command.Connection;
var ds = new DataSet();
var tableNameExpr = new Regex(tableNamePattern ?? @"\Wtable:(\w+)\s*$", RegexOptions.IgnoreCase);
string lastInfoMessage = null;
SqlInfoMessageEventHandler infoMessageHandler = (s, e) => lastInfoMessage = e.Message;
conn.InfoMessage += infoMessageHandler;
using (var reader = command.ExecuteReader())
{
try
{
ReadTable:
DataTable dt = null;
for (;;)
{
bool hasRow = reader.Read();
if (dt == null)
{
var tableName = string.Format("Table{0}", ds.Tables.Count);
if (lastInfoMessage != null)
{
var m = tableNameExpr.Match(lastInfoMessage);
if (m.Success)
{
tableName = m.Groups[1].Value;
}
lastInfoMessage = null;
}
dt = new DataTable(tableName);
ds.Tables.Add(dt);
var colsSeen = new HashSet <string>(Comparers.CaseInsensitiveStringComparer);
for (int z = 0; z < reader.FieldCount; ++z)
{
var c = new DataColumn(reader.GetName(z), reader.GetFieldType(z));
if (colsSeen.Contains(c.ColumnName))
{
c.ColumnName = DataTableHelpers.OnDuplicateAppendSeqeuntialNumber(dt, c.ColumnName);
}
colsSeen.Add(c.ColumnName);
dt.Columns.Add(c);
}
}
if (!hasRow)
{
break;
}
var vals = new object[reader.FieldCount];
reader.GetValues(vals);
dt.Rows.Add(vals);
if (dt.Rows.Count % 1000 == 0)
{
Trace.WriteLine(string.Format("Exported {0} rows from table [{1}]...",
dt.Rows.Count,
dt.TableName
));
}
}
if (dt != null)
{
Trace.WriteLine(string.Format("Exported {0} rows from table [{1}]", dt.Rows.Count, dt.TableName));
if (reader.NextResult())
{
goto ReadTable;
}
}
return(ds);
}
finally
{
conn.InfoMessage -= infoMessageHandler;
}
}
}
