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