/// <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);
}
private static bool IsEmptyRow(IExcelDataReader 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);
}
/// <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);
}
private static DataTable AsDataTable(IExcelDataReader 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))
{
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);
row[i] = value;
}
result.Rows.Add(row);
}
result.EndLoadData();
return(result);
}
private static DataTable AsDataTable(IExcelDataReader self, ExcelDataTableConfiguration configuration, ExcelDataValidatorConfiguration validator, out DataTable errorTable, out DataTable targetTable)
{
var error = new DataTable {
TableName = self.Name + @" Error"
};
var target = new DataTable {
TableName = self.Name + @" Target"
};
var result = new DataTable {
TableName = self.Name
};
error.ExtendedProperties.Add("visiblestate", self.VisibleState);
target.ExtendedProperties.Add("visiblestate", self.VisibleState);
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);
}
if (self.FieldCount != configuration.dtFieldColumn.Rows.Count)
{
// return "Số lượng column không hợp lệ";
errorTable = error;
targetTable = target;
return(result);
}
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 (name.ToString() != configuration.dtFieldColumn.Rows[i][0].ToString())
{
//ten cot khong trung
errorTable = error;
targetTable = target;
return(result);
}
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_error = new DataColumn(columnName, typeof(object))
{
Caption = name
};
var column_target = new DataColumn(columnName, typeof(object))
{
Caption = name
};
var column = new DataColumn(columnName, typeof(object))
{
Caption = name
};
error.Columns.Add(column_error);
target.Columns.Add(column_target);
result.Columns.Add(column);
columnIndices.Add(i);
}
var column3 = new DataColumn(@"Error_Excel", typeof(string))
{
Caption = @"Error_Excel"
};
error.Columns.Add(column3);
error.BeginLoadData();
target.BeginLoadData();
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;
StringBuilder hasError = new StringBuilder(String.Empty);
var row = result.NewRow();
for (var i = 0; i < columnIndices.Count; i++)
{
var columnIndex = columnIndices[i];
var columnName = columnIndices[i];
var value = self.GetValue(columnIndex);
if (configuration.TransformValue != null)
{
var transformedValue = configuration.TransformValue(self, i, value);
if (transformedValue != null)
{
value = transformedValue;
}
}
var formatString = self.GetNumberFormatString(columnIndex);
if (value != null)
{
if (formatString != null)
{
var format = new NumberFormat(formatString);
value = format.Format(value, CultureInfo.InvariantCulture);
}
}
//else
//{
// value = Convert.ToString(value, CultureInfo.InvariantCulture);
//}
if (validator.ValidateCell != null)
{
try
{
var message = validator.ValidateCell(self, i, value);
if (!String.IsNullOrWhiteSpace(message))
{
hasError.Append($"Column {error.Columns[columnIndex].ColumnName} - {message}, ");
}
}
catch
{
var message = "Invalid Format";
hasError.Append($"Column {error.Columns[columnIndex].ColumnName} - {message}, ");
}
}
row[i] = value;
}
result.Rows.Add(row);
if (hasError.Length < 1)
{
var target_row = target.NewRow();
target_row.ItemArray = row.ItemArray;
target.Rows.Add(target_row);
}
else
{
var error_row = error.NewRow();
error_row.ItemArray = row.ItemArray;
error_row[columnIndices.Count] = hasError;
error.Rows.Add(error_row);
}
}
result.EndLoadData();
target.EndLoadData();
error.EndLoadData();
errorTable = error;
targetTable = target;
return(result);
}
