/// <summary>
/// Converts all sheets to a DataSet
/// </summary>
/// <param name="self">The IExcelDataReader instance</param>
/// <param name="configuration">An optional configuration object to modify the behavior of the conversion</param>
/// <returns>A dataset with all workbook contents</returns>
public static DataSet AsDataSet(this IExcelDataReader self, ExcelDataSetConfiguration configuration = null)
{
if (configuration == null)
{
configuration = new ExcelDataSetConfiguration();
}
self.Reset();
var tableIndex = -1;
var result = new DataSet();
do
{
tableIndex++;
if (configuration.FilterSheet != null && !configuration.FilterSheet(self, tableIndex))
{
continue;
}
var tableConfiguration = configuration.ConfigureDataTable != null
? configuration.ConfigureDataTable(self)
: null;
var validatorConfiguration = configuration.ConfigureDataValidator != null
? configuration.ConfigureDataValidator(self)
: null;
if (tableConfiguration == null)
{
tableConfiguration = new ExcelDataTableConfiguration();
}
if (validatorConfiguration == null)
{
validatorConfiguration = new ExcelDataValidatorConfiguration();
}
DataTable errorTable, targetTable;
var table = AsDataTable(self, tableConfiguration, validatorConfiguration, out errorTable, out targetTable);
result.Tables.Add(table);
result.Tables.Add(errorTable);
result.Tables.Add(targetTable);
}while (self.NextResult());
result.AcceptChanges();
if (configuration.UseColumnDataType)
{
FixDataTypes(result);
}
self.Reset();
return(result);
}
public List <string> ReadWorksheetNames()
{
List <string> list = new();
_reader.Reset();
do
{
list.Add(_reader.Name);
} while (_reader.NextResult());
return(list);
}
public IEnumerator <T> GetEnumerator()
{
_dataReader.Reset();
EnsureCorrectSheetSelected();
EnsureColumnNamesResolved();
while (_dataReader.Read())
{
var instance = Activator.CreateInstance <T>();
foreach (var propertyMap in _configuration.PropertyMaps.Where(p => !p.Ignored))
{
var valueFromExcel = propertyMap.MapStrategy == ExcelIteratorPropertyMapStrategy.ByName
? _dataReader.GetValue(ResolveIndexByName(propertyMap))
: _dataReader.GetValue(propertyMap.ColumnIndex);
propertyMap.Property.SetValue(instance,
propertyMap.SourceValueConverter.ConvertValue(valueFromExcel));
}
yield return(instance);
}
}
private static void MoveToSheet(IExcelDataReader xlsReader, Func <string, bool> namePred)
{
xlsReader.Reset();
while (!namePred(xlsReader.Name))
{
if (!xlsReader.NextResult())
{
throw new Exception("Cannot find sheet");
}
}
}
public T ReadAllWorksheets <T>(IExcelDataReader reader, FluentConfig config = null) where T : class, new()
{
var dataFromExcel = new T();
var sheetProcessors = new List <SheetProcessingData>();
List <TablePropertyData> tables = null;
if (config != null)
{
tables = config.Tables;
}
else
{
tables = GetTableProperties(typeof(T));
}
foreach (var table in tables)
{
sheetProcessors.Add(new SheetProcessingData(table, CreateSetPropertyAction(typeof(T), table.PropertyName))); //table.ExcelSheetName
}
var hasNextResult = false;
var readerWasReset = false;
do
{
readerWasReset = false;
var worksheetName = reader.Name;
var currentSheetTables = sheetProcessors.Where(m => m.TablePropertyData.ExcelSheetName.Equals(worksheetName, StringComparison.OrdinalIgnoreCase) && m.Processed == false).ToList();
if (currentSheetTables.Count > 0)
{
var processor = currentSheetTables.First();
var result = ProcessTable(reader, processor.TablePropertyData);
processor.PropertySetter(dataFromExcel, result);
processor.Processed = true;
// handling cases (experimental), where multiple tables (classes) are bound to one excel sheet
if (currentSheetTables.Count > 1)
{
reader.Reset(); // ToDo: this will kill performance on large files. Reading should be refactored to be able to read one sheet to different tables simultaneously...
readerWasReset = true;
}
}
if (!readerWasReset)
{
hasNextResult = reader.NextResult();
}
} while (hasNextResult);
return(dataFromExcel);
}
public static DataSet AsDataSet(this IExcelDataReader self, bool convertOADate)
{
self.ConvertOaDate = convertOADate;
self.Reset();
var result = new DataSet();
do
{
var table = AsDataTable(self);
if (table.Rows.Count > 0)
{
result.Tables.Add(table);
}
} while (self.NextResult());
result.AcceptChanges();
FixDataTypes(result);
self.Reset();
return(result);
}
/// <summary>
/// Converts all sheets to a DataSet
/// </summary>
/// <param name="self">The IExcelDataReader instance</param>
/// <param name="configuration">An optional configuration object to modify the behavior of the conversion</param>
/// <returns>A dataset with all workbook contents</returns>
public static DataSet AsDataSet(this IExcelDataReader self, ExcelDataSetConfiguration configuration = null)
{
if (configuration == null)
{
configuration = new ExcelDataSetConfiguration();
}
self.Reset();
var result = new DataSet();
do
{
var tableConfiguration = configuration.ConfigureDataTable != null
? configuration.ConfigureDataTable(self)
: null;
if (tableConfiguration == null)
{
tableConfiguration = new ExcelDataTableConfiguration();
}
var table = AsDataTable(self, tableConfiguration);
result.Tables.Add(table);
}while (self.NextResult());
result.AcceptChanges();
if (configuration.UseColumnDataType)
{
FixDataTypes(result);
}
self.Reset();
return(result);
}
/// <summary>
/// Changes to using the passed in sheet. Note that changing to a new sheet automatically resets the
/// internal row counter used by GetRecords.
/// </summary>
/// <param name="sheet">Sheet to change to (0 to TotalSheets - 1)</param>
/// <returns>True on success, false if the sheet is out of range</returns>
public bool ChangeSheet(
int sheet)
{
if (sheet >= _reader.ResultsCount)
{
return(false);
}
_reader.Reset();
for (var i = 0; i < sheet; i++)
{
_reader.NextResult();
}
_row = 0;
return(true);
}