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