/// <summary>
/// Converts all sheets to a DataSet
/// </summary>
/// <param name="self">The IExcel 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 IExcel 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;
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);
}
private static bool IsEmptyRow(IExcel reader, ExcelDataTableConfiguration configuration)
{
for (var i = 0; i < reader.FieldCount; i++)
{
var value = reader.GetValue(i);
if (configuration.TransformValue != null)
{
var transformedValue = configuration.TransformValue(reader, i, value);
if (transformedValue != null)
{
value = transformedValue;
}
}
if (value != null)
{
return(false);
}
}
return(true);
}
private static DataTable AsDataTable(IExcel self, ExcelDataTableConfiguration configuration)
{
var result = new DataTable {
TableName = self.Name
};
result.ExtendedProperties.Add("visiblestate", self.VisibleState);
var first = true;
var emptyRows = 0;
var columnIndices = new List <int>();
while (self.Read())
{
if (first)
{
if (configuration.UseHeaderRow && configuration.ReadHeaderRow != null)
{
configuration.ReadHeaderRow(self);
}
for (var i = 0; i < self.FieldCount; i++)
{
if (configuration.FilterColumn != null && !configuration.FilterColumn(self, i))
{
continue;
}
var name = configuration.UseHeaderRow
? Convert.ToString(self.GetValue(i))
: null;
if (string.IsNullOrEmpty(name))
{
name = configuration.EmptyColumnNamePrefix + i;
}
// if a column already exists with the name append _i to the duplicates
var columnName = GetUniqueColumnName(result, name);
var column = new DataColumn(columnName, typeof(object))
{
Caption = name
};
result.Columns.Add(column);
columnIndices.Add(i);
}
result.BeginLoadData();
first = false;
if (configuration.UseHeaderRow)
{
continue;
}
}
if (configuration.FilterRow != null && !configuration.FilterRow(self))
{
continue;
}
if (IsEmptyRow(self, configuration))
{
emptyRows++;
continue;
}
for (var i = 0; i < emptyRows; i++)
{
result.Rows.Add(result.NewRow());
}
emptyRows = 0;
var row = result.NewRow();
for (var i = 0; i < columnIndices.Count; i++)
{
var columnIndex = columnIndices[i];
var value = self.GetValue(columnIndex);
if (configuration.TransformValue != null)
{
var transformedValue = configuration.TransformValue(self, i, value);
if (transformedValue != null)
{
value = transformedValue;
}
}
row[i] = value;
}
result.Rows.Add(row);
}
result.EndLoadData();
return(result);
}