Importation d'une feuille Excel et validation des données imscopes avec couplage libre

J'essaie de développer un module qui va lire des feuilles excel (éventuellement à partir d'autres sources de données, donc devrait être légalement couplé) et les convertir en Entités afin d'économiser.

La logique sera la suivante:

La feuille excel peut être sous un format différent, par exemple, les noms de colonne dans la feuille Excel peuvent être différents, de sorte que mon système doit pouvoir mapper différents champs à mes entités.
Pour l'instant, je supposerai que le format défini ci-dessus sera identique et codé pour l'instant au lieu de venir de la database dynamicment après avoir configuré un mapping de configuration UI quelque chose.
Les données doivent être validées avant même d'être mappées. Donc, je devrais pouvoir le valider à l'avance contre quelque chose. Nous n'utilisons pas comme XSD ou autre chose, donc je devrais le valider contre la structure d'object que j'utilise comme model d'import.

Le problème est que j'ai rassemblé des choses set mais je ne dis pas que j'ai aimé ce que j'ai fait. Ma question est de savoir comment améliorer le code ci-dessous et rendre les choses plus modulaires et résoudre les problèmes de validation.

Le code ci-dessous est une maquette et ne devrait pas fonctionner, juste pour voir une certaine structure de la design.

C'est le code que j'ai trouvé jusqu'ici, et j'ai réalisé une chose dont j'ai besoin pour améliorer mes compétences en design, mais pour l'instant j'ai besoin de votre aide, si vous pouviez m'aider:

//The Controller, a placeholder class UploadController { //Somewhere here we call appropriate class and methods in order to convert //excel sheet to dataset }

Après avoir téléchargé un file à l'aide d'un controller MVC, il pourrait y avoir différents controllers spécialisés dans l'import de certains comportements. Dans cet exemple, je vais download des arrays liés aux personnes,

 interface IDataImporter { void Import(DataSet dataset); }

// Nous pouvons utiliser de nombreux autres importateurs en plus de la class PersonImporter PersonImporter: IDataImporter {// Nous divisons l'set de données pour approcher les arrays de données et appelons toutes les IImportActions // liées à l'import de données personnelles // Nous appelons l'insertion dans les fonctions de la database ici DataContext depuis cette path // nous pouvons faire less db aller-return.

 public ssortingng PersonTableName {get;set;} public ssortingng DemographicsTableName {get;set;} public Import(Dataset dataset) { CreatePerson(); CreateDemograhics(); } //We put different things in different methods to clear the field. High cohesion. private void CreatePerson(DataSet dataset) { var personDataTable = GetDataTable(dataset,PersonTableName); IImportAction addOrUpdatePerson = new AddOrUpdatePerson(); addOrUpdatePerson.MapEntity(personDataTable); } private void CreateDemograhics(DataSet dataset) { var demographicsDataTable = GetDataTable(dataset,DemographicsTableName); IImportAction demoAction = new AddOrUpdateDemographic(demographicsDataTable); demoAction.MapEntity(); } private DataTable GetDataTable(DataSet dataset, ssortingng tableName) { return dataset.Tables[tableName]; }

}

J'ai un IDataImporter et une class spécialisée de class PersonImporter . Cependant, je ne suis pas sûr que cela semble bien jusqu'à présent, car les choses devraient être SOLIDes, donc, fondamentalement, plus facile à développer plus tard dans le cycle du projet, ce sera une base pour les améliorations futures, nous allons continuer:

IImportActions est l'endroit où la magie arrive le plus souvent. Au lieu de concevoir des arrays basés sur des tables, je développe le comportement en fonction de manière à pouvoir appeler l'un d'entre eux pour importer des choses dans un model plus modulaire. Par exemple, un tableau peut avoir 2 actions différentes.

 interface IImportAction { void MapEntity(DataTable table); } //A sample import action, AddOrUpdatePerson class AddOrUpdatePerson : IImportAction { //Consider using default values as well? public ssortingng FirstName {get;set;} public ssortingng LastName {get;set;} public ssortingng EmployeeId {get;set;} public ssortingng Email {get;set;} public void MapEntity(DataTable table) { //Each action is producing its own data context since they use //different actions. using(var dataContext = new DataContext()) { foreach(DataRow row in table.Rows) { if(!emailValidate(row[Email])) { LoggingService.LogWarning(emailValidate.ValidationMessage); } var person = new Person(){ FirstName = row[FirstName], LastName = row[LastName], EmployeeId = row[EmployeeId], Email = row[Email] }; dataContext.SaveObject(person); } dataContext.SaveChangesToDatabase(); } } } class AddOrUpdateDemographic: IImportAction { static ssortingng Name {get;set;} static ssortingng EmployeeId {get;set;} //So here for example, we will need to save dataContext first before passing it in //to get the PersonId from Person (we're assuming that we need PersonId for Demograhics) public void MapEntity(DataTable table) { using(var dataContext = new DataCOntext()) { foreach(DataRow row in table.Rows) { var demograhic = new Demographic(){ Name = row[Name], PersonId = dataContext.People.First(t => t.EmployeeId = int.Parse(row["EmpId"])) }; dataContext.SaveObject(person); } dataContext.SaveChangesToDatabase(); } } } }; interface IImportAction { void MapEntity(DataTable table); } //A sample import action, AddOrUpdatePerson class AddOrUpdatePerson : IImportAction { //Consider using default values as well? public ssortingng FirstName {get;set;} public ssortingng LastName {get;set;} public ssortingng EmployeeId {get;set;} public ssortingng Email {get;set;} public void MapEntity(DataTable table) { //Each action is producing its own data context since they use //different actions. using(var dataContext = new DataContext()) { foreach(DataRow row in table.Rows) { if(!emailValidate(row[Email])) { LoggingService.LogWarning(emailValidate.ValidationMessage); } var person = new Person(){ FirstName = row[FirstName], LastName = row[LastName], EmployeeId = row[EmployeeId], Email = row[Email] }; dataContext.SaveObject(person); } dataContext.SaveChangesToDatabase(); } } } class AddOrUpdateDemographic: IImportAction { static ssortingng Name {get;set;} static ssortingng EmployeeId {get;set;} //So here for example, we will need to save dataContext first before passing it in //to get the PersonId from Person (we're assuming that we need PersonId for Demograhics) public void MapEntity(DataTable table) { using(var dataContext = new DataCOntext()) { foreach(DataRow row in table.Rows) { var demograhic = new Demographic(){ Name = row[Name], PersonId = dataContext.People.First(t => t.EmployeeId = int.Parse(row["EmpId"])) }; dataContext.SaveObject(person); } dataContext.SaveChangesToDatabase(); } } } }; interface IImportAction { void MapEntity(DataTable table); } //A sample import action, AddOrUpdatePerson class AddOrUpdatePerson : IImportAction { //Consider using default values as well? public ssortingng FirstName {get;set;} public ssortingng LastName {get;set;} public ssortingng EmployeeId {get;set;} public ssortingng Email {get;set;} public void MapEntity(DataTable table) { //Each action is producing its own data context since they use //different actions. using(var dataContext = new DataContext()) { foreach(DataRow row in table.Rows) { if(!emailValidate(row[Email])) { LoggingService.LogWarning(emailValidate.ValidationMessage); } var person = new Person(){ FirstName = row[FirstName], LastName = row[LastName], EmployeeId = row[EmployeeId], Email = row[Email] }; dataContext.SaveObject(person); } dataContext.SaveChangesToDatabase(); } } } class AddOrUpdateDemographic: IImportAction { static ssortingng Name {get;set;} static ssortingng EmployeeId {get;set;} //So here for example, we will need to save dataContext first before passing it in //to get the PersonId from Person (we're assuming that we need PersonId for Demograhics) public void MapEntity(DataTable table) { using(var dataContext = new DataCOntext()) { foreach(DataRow row in table.Rows) { var demograhic = new Demographic(){ Name = row[Name], PersonId = dataContext.People.First(t => t.EmployeeId = int.Parse(row["EmpId"])) }; dataContext.SaveObject(person); } dataContext.SaveChangesToDatabase(); } } }

Et la validation, qui, surtout, où je suce malheureusement. La validation doit être facile à étendre et à être lâchement couplée, et je dois pouvoir appeler cette validation au préalable au lieu d'append tout.

 public static class ValidationFactory { public static Lazy<IFieldValidation> PhoneValidation = new Lazy<IFieldValidation>(()=>new PhoneNumberValidation()); public static Lazy<IFieldValidation> EmailValidation = new Lazy<IFieldValidation>(()=>new EmailValidation()); //etc. } interface IFieldValidation { ssortingng ValidationMesage{get;set;} bool Validate(object value); } class PhoneNumberValidation : IFieldValidation { public ssortingng ValidationMesage{get;set;} public bool Validate(object value) { var validated = true; //lets say... var innerValue = (ssortingng) value; //validate innerValue using Regex or something //if validation fails, then set ValidationMessage propert for logging. return validated; } } class EmailValidation : IFieldValidation { public ssortingng ValidationMesage{get;set;} public bool Validate(object value) { var validated = true; //lets say... var innerValue = (ssortingng) value; //validate innerValue using Regex or something //if validation fails, then set ValidationMessage propert for logging. return validated; } }

J'ai fait la même chose sur un projet. La différence est que je n'ai pas eu à importer des feuilles Excel, mais des files CSV. J'ai créé CSVValueProvider. Et, par conséquent, datatables CSV étaient liées à mon model IEnumerable automatiquement.

En ce qui concerne la validation, j'ai pensé que passer toutes les lignes et les cellules, et les valider une par une n'est pas très efficace, surtout lorsque le file CSV contient des milliers d'loggings. Donc, ce que j'ai fait, c'est que j'ai créé des methods de validation qui ont traversé la colonne de données CSV par colonne, au lieu d'une ligne par ligne, et ont effectué une requête linq sur chaque colonne et renvoyé les nombres de lignes des cellules avec des données non valides. Ensuite, a ajouté les noms de ligne / colonne invalides à ModelState.

METTRE À JOUR:

Voici ce que j'ai fait …

Classe CSVReader:

 // A class that can read and parse the data in a CSV file. public class CSVReader { // Regex expression that's used to parse the data in a line of a CSV file private const ssortingng ESCAPE_SPLIT_REGEX = "({1}[^{1}]*{1})*(?<Separator>{0})({1}[^{1}]*{1})*"; // Ssortingng array to hold the headers (column names) private ssortingng[] _headers; // List of ssortingng arrays to hold the data in the CSV file. Each ssortingng array in the list represents one line (row). private List<ssortingng[]> _rows; // The StreamReader class that's used to read the CSV file. private StreamReader _reader; public CSVReader(StreamReader reader) { _reader = reader; Parse(); } // Reads and parses the data from the CSV file private void Parse() { _rows = new List<ssortingng[]>(); ssortingng[] row; int rowNumber = 1; var headerLine = "RowNumber," + _reader.ReadLine(); _headers = GetEscapedSVs(headerLine); rowNumber++; while (!_reader.EndOfStream) { var line = rowNumber + "," + _reader.ReadLine(); row = GetEscapedSVs(line); _rows.Add(row); rowNumber++; } _reader.Close(); } private ssortingng[] GetEscapedSVs(ssortingng data) { if (!data.EndsWith(",")) data = data + ","; return GetEscapedSVs(data, ",", "\""); } // Parses each row by using the given separator and escape characters private ssortingng[] GetEscapedSVs(ssortingng data, ssortingng separator, ssortingng escape) { ssortingng[] result = null; int priorMatchIndex = 0; MatchCollection matches = Regex.Matches(data, ssortingng.Format(ESCAPE_SPLIT_REGEX, separator, escape)); // Skip empty rows... if (matches.Count > 0) { result = new ssortingng[matches.Count]; for (int index = 0; index <= result.Length - 2; index++) { result[index] = data.Subssortingng(priorMatchIndex, matches[index].Groups["Separator"].Index - priorMatchIndex); priorMatchIndex = matches[index].Groups["Separator"].Index + separator.Length; } result[result.Length - 1] = data.Subssortingng(priorMatchIndex, data.Length - priorMatchIndex - 1); for (int index = 0; index <= result.Length - 1; index++) { if (Regex.IsMatch(result[index], ssortingng.Format("^{0}.*[^{0}]{0}$", escape))) result[index] = result[index].Subssortingng(1, result[index].Length - 2); result[index] = result[index].Replace(escape + escape, escape); if (result[index] == null || result[index] == escape) result[index] = ""; } } return result; } // Returns the number of rows public int RowCount { get { if (_rows == null) return 0; return _rows.Count; } } // Returns the number of headers (columns) public int HeaderCount { get { if (_headers == null) return 0; return _headers.Length; } } // Returns the value in a given column name and row index public object GetValue(ssortingng columnName, int rowIndex) { if (rowIndex >= _rows.Count) { return null; } var row = _rows[rowIndex]; int colIndex = GetColumnIndex(columnName); if (colIndex == -1 || colIndex >= row.Length) { return null; } var value = row[colIndex]; return value; } // Returns the column index of the provided column name public int GetColumnIndex(ssortingng columnName) { int index = -1; for (int i = 0; i < _headers.Length; i++) { if (_headers[i].Replace(" ","").Equals(columnName, SsortingngComparison.CurrentCultureIgnoreCase)) { index = i; return index; } } return index; } } return 0; // A class that can read and parse the data in a CSV file. public class CSVReader { // Regex expression that's used to parse the data in a line of a CSV file private const ssortingng ESCAPE_SPLIT_REGEX = "({1}[^{1}]*{1})*(?<Separator>{0})({1}[^{1}]*{1})*"; // Ssortingng array to hold the headers (column names) private ssortingng[] _headers; // List of ssortingng arrays to hold the data in the CSV file. Each ssortingng array in the list represents one line (row). private List<ssortingng[]> _rows; // The StreamReader class that's used to read the CSV file. private StreamReader _reader; public CSVReader(StreamReader reader) { _reader = reader; Parse(); } // Reads and parses the data from the CSV file private void Parse() { _rows = new List<ssortingng[]>(); ssortingng[] row; int rowNumber = 1; var headerLine = "RowNumber," + _reader.ReadLine(); _headers = GetEscapedSVs(headerLine); rowNumber++; while (!_reader.EndOfStream) { var line = rowNumber + "," + _reader.ReadLine(); row = GetEscapedSVs(line); _rows.Add(row); rowNumber++; } _reader.Close(); } private ssortingng[] GetEscapedSVs(ssortingng data) { if (!data.EndsWith(",")) data = data + ","; return GetEscapedSVs(data, ",", "\""); } // Parses each row by using the given separator and escape characters private ssortingng[] GetEscapedSVs(ssortingng data, ssortingng separator, ssortingng escape) { ssortingng[] result = null; int priorMatchIndex = 0; MatchCollection matches = Regex.Matches(data, ssortingng.Format(ESCAPE_SPLIT_REGEX, separator, escape)); // Skip empty rows... if (matches.Count > 0) { result = new ssortingng[matches.Count]; for (int index = 0; index <= result.Length - 2; index++) { result[index] = data.Subssortingng(priorMatchIndex, matches[index].Groups["Separator"].Index - priorMatchIndex); priorMatchIndex = matches[index].Groups["Separator"].Index + separator.Length; } result[result.Length - 1] = data.Subssortingng(priorMatchIndex, data.Length - priorMatchIndex - 1); for (int index = 0; index <= result.Length - 1; index++) { if (Regex.IsMatch(result[index], ssortingng.Format("^{0}.*[^{0}]{0}$", escape))) result[index] = result[index].Subssortingng(1, result[index].Length - 2); result[index] = result[index].Replace(escape + escape, escape); if (result[index] == null || result[index] == escape) result[index] = ""; } } return result; } // Returns the number of rows public int RowCount { get { if (_rows == null) return 0; return _rows.Count; } } // Returns the number of headers (columns) public int HeaderCount { get { if (_headers == null) return 0; return _headers.Length; } } // Returns the value in a given column name and row index public object GetValue(ssortingng columnName, int rowIndex) { if (rowIndex >= _rows.Count) { return null; } var row = _rows[rowIndex]; int colIndex = GetColumnIndex(columnName); if (colIndex == -1 || colIndex >= row.Length) { return null; } var value = row[colIndex]; return value; } // Returns the column index of the provided column name public int GetColumnIndex(ssortingng columnName) { int index = -1; for (int i = 0; i < _headers.Length; i++) { if (_headers[i].Replace(" ","").Equals(columnName, SsortingngComparison.CurrentCultureIgnoreCase)) { index = i; return index; } } return index; } } return 0; // A class that can read and parse the data in a CSV file. public class CSVReader { // Regex expression that's used to parse the data in a line of a CSV file private const ssortingng ESCAPE_SPLIT_REGEX = "({1}[^{1}]*{1})*(?<Separator>{0})({1}[^{1}]*{1})*"; // Ssortingng array to hold the headers (column names) private ssortingng[] _headers; // List of ssortingng arrays to hold the data in the CSV file. Each ssortingng array in the list represents one line (row). private List<ssortingng[]> _rows; // The StreamReader class that's used to read the CSV file. private StreamReader _reader; public CSVReader(StreamReader reader) { _reader = reader; Parse(); } // Reads and parses the data from the CSV file private void Parse() { _rows = new List<ssortingng[]>(); ssortingng[] row; int rowNumber = 1; var headerLine = "RowNumber," + _reader.ReadLine(); _headers = GetEscapedSVs(headerLine); rowNumber++; while (!_reader.EndOfStream) { var line = rowNumber + "," + _reader.ReadLine(); row = GetEscapedSVs(line); _rows.Add(row); rowNumber++; } _reader.Close(); } private ssortingng[] GetEscapedSVs(ssortingng data) { if (!data.EndsWith(",")) data = data + ","; return GetEscapedSVs(data, ",", "\""); } // Parses each row by using the given separator and escape characters private ssortingng[] GetEscapedSVs(ssortingng data, ssortingng separator, ssortingng escape) { ssortingng[] result = null; int priorMatchIndex = 0; MatchCollection matches = Regex.Matches(data, ssortingng.Format(ESCAPE_SPLIT_REGEX, separator, escape)); // Skip empty rows... if (matches.Count > 0) { result = new ssortingng[matches.Count]; for (int index = 0; index <= result.Length - 2; index++) { result[index] = data.Subssortingng(priorMatchIndex, matches[index].Groups["Separator"].Index - priorMatchIndex); priorMatchIndex = matches[index].Groups["Separator"].Index + separator.Length; } result[result.Length - 1] = data.Subssortingng(priorMatchIndex, data.Length - priorMatchIndex - 1); for (int index = 0; index <= result.Length - 1; index++) { if (Regex.IsMatch(result[index], ssortingng.Format("^{0}.*[^{0}]{0}$", escape))) result[index] = result[index].Subssortingng(1, result[index].Length - 2); result[index] = result[index].Replace(escape + escape, escape); if (result[index] == null || result[index] == escape) result[index] = ""; } } return result; } // Returns the number of rows public int RowCount { get { if (_rows == null) return 0; return _rows.Count; } } // Returns the number of headers (columns) public int HeaderCount { get { if (_headers == null) return 0; return _headers.Length; } } // Returns the value in a given column name and row index public object GetValue(ssortingng columnName, int rowIndex) { if (rowIndex >= _rows.Count) { return null; } var row = _rows[rowIndex]; int colIndex = GetColumnIndex(columnName); if (colIndex == -1 || colIndex >= row.Length) { return null; } var value = row[colIndex]; return value; } // Returns the column index of the provided column name public int GetColumnIndex(ssortingng columnName) { int index = -1; for (int i = 0; i < _headers.Length; i++) { if (_headers[i].Replace(" ","").Equals(columnName, SsortingngComparison.CurrentCultureIgnoreCase)) { index = i; return index; } } return index; } } returnner nulle; // A class that can read and parse the data in a CSV file. public class CSVReader { // Regex expression that's used to parse the data in a line of a CSV file private const ssortingng ESCAPE_SPLIT_REGEX = "({1}[^{1}]*{1})*(?<Separator>{0})({1}[^{1}]*{1})*"; // Ssortingng array to hold the headers (column names) private ssortingng[] _headers; // List of ssortingng arrays to hold the data in the CSV file. Each ssortingng array in the list represents one line (row). private List<ssortingng[]> _rows; // The StreamReader class that's used to read the CSV file. private StreamReader _reader; public CSVReader(StreamReader reader) { _reader = reader; Parse(); } // Reads and parses the data from the CSV file private void Parse() { _rows = new List<ssortingng[]>(); ssortingng[] row; int rowNumber = 1; var headerLine = "RowNumber," + _reader.ReadLine(); _headers = GetEscapedSVs(headerLine); rowNumber++; while (!_reader.EndOfStream) { var line = rowNumber + "," + _reader.ReadLine(); row = GetEscapedSVs(line); _rows.Add(row); rowNumber++; } _reader.Close(); } private ssortingng[] GetEscapedSVs(ssortingng data) { if (!data.EndsWith(",")) data = data + ","; return GetEscapedSVs(data, ",", "\""); } // Parses each row by using the given separator and escape characters private ssortingng[] GetEscapedSVs(ssortingng data, ssortingng separator, ssortingng escape) { ssortingng[] result = null; int priorMatchIndex = 0; MatchCollection matches = Regex.Matches(data, ssortingng.Format(ESCAPE_SPLIT_REGEX, separator, escape)); // Skip empty rows... if (matches.Count > 0) { result = new ssortingng[matches.Count]; for (int index = 0; index <= result.Length - 2; index++) { result[index] = data.Subssortingng(priorMatchIndex, matches[index].Groups["Separator"].Index - priorMatchIndex); priorMatchIndex = matches[index].Groups["Separator"].Index + separator.Length; } result[result.Length - 1] = data.Subssortingng(priorMatchIndex, data.Length - priorMatchIndex - 1); for (int index = 0; index <= result.Length - 1; index++) { if (Regex.IsMatch(result[index], ssortingng.Format("^{0}.*[^{0}]{0}$", escape))) result[index] = result[index].Subssortingng(1, result[index].Length - 2); result[index] = result[index].Replace(escape + escape, escape); if (result[index] == null || result[index] == escape) result[index] = ""; } } return result; } // Returns the number of rows public int RowCount { get { if (_rows == null) return 0; return _rows.Count; } } // Returns the number of headers (columns) public int HeaderCount { get { if (_headers == null) return 0; return _headers.Length; } } // Returns the value in a given column name and row index public object GetValue(ssortingng columnName, int rowIndex) { if (rowIndex >= _rows.Count) { return null; } var row = _rows[rowIndex]; int colIndex = GetColumnIndex(columnName); if (colIndex == -1 || colIndex >= row.Length) { return null; } var value = row[colIndex]; return value; } // Returns the column index of the provided column name public int GetColumnIndex(ssortingng columnName) { int index = -1; for (int i = 0; i < _headers.Length; i++) { if (_headers[i].Replace(" ","").Equals(columnName, SsortingngComparison.CurrentCultureIgnoreCase)) { index = i; return index; } } return index; } } returnner nulle; // A class that can read and parse the data in a CSV file. public class CSVReader { // Regex expression that's used to parse the data in a line of a CSV file private const ssortingng ESCAPE_SPLIT_REGEX = "({1}[^{1}]*{1})*(?<Separator>{0})({1}[^{1}]*{1})*"; // Ssortingng array to hold the headers (column names) private ssortingng[] _headers; // List of ssortingng arrays to hold the data in the CSV file. Each ssortingng array in the list represents one line (row). private List<ssortingng[]> _rows; // The StreamReader class that's used to read the CSV file. private StreamReader _reader; public CSVReader(StreamReader reader) { _reader = reader; Parse(); } // Reads and parses the data from the CSV file private void Parse() { _rows = new List<ssortingng[]>(); ssortingng[] row; int rowNumber = 1; var headerLine = "RowNumber," + _reader.ReadLine(); _headers = GetEscapedSVs(headerLine); rowNumber++; while (!_reader.EndOfStream) { var line = rowNumber + "," + _reader.ReadLine(); row = GetEscapedSVs(line); _rows.Add(row); rowNumber++; } _reader.Close(); } private ssortingng[] GetEscapedSVs(ssortingng data) { if (!data.EndsWith(",")) data = data + ","; return GetEscapedSVs(data, ",", "\""); } // Parses each row by using the given separator and escape characters private ssortingng[] GetEscapedSVs(ssortingng data, ssortingng separator, ssortingng escape) { ssortingng[] result = null; int priorMatchIndex = 0; MatchCollection matches = Regex.Matches(data, ssortingng.Format(ESCAPE_SPLIT_REGEX, separator, escape)); // Skip empty rows... if (matches.Count > 0) { result = new ssortingng[matches.Count]; for (int index = 0; index <= result.Length - 2; index++) { result[index] = data.Subssortingng(priorMatchIndex, matches[index].Groups["Separator"].Index - priorMatchIndex); priorMatchIndex = matches[index].Groups["Separator"].Index + separator.Length; } result[result.Length - 1] = data.Subssortingng(priorMatchIndex, data.Length - priorMatchIndex - 1); for (int index = 0; index <= result.Length - 1; index++) { if (Regex.IsMatch(result[index], ssortingng.Format("^{0}.*[^{0}]{0}$", escape))) result[index] = result[index].Subssortingng(1, result[index].Length - 2); result[index] = result[index].Replace(escape + escape, escape); if (result[index] == null || result[index] == escape) result[index] = ""; } } return result; } // Returns the number of rows public int RowCount { get { if (_rows == null) return 0; return _rows.Count; } } // Returns the number of headers (columns) public int HeaderCount { get { if (_headers == null) return 0; return _headers.Length; } } // Returns the value in a given column name and row index public object GetValue(ssortingng columnName, int rowIndex) { if (rowIndex >= _rows.Count) { return null; } var row = _rows[rowIndex]; int colIndex = GetColumnIndex(columnName); if (colIndex == -1 || colIndex >= row.Length) { return null; } var value = row[colIndex]; return value; } // Returns the column index of the provided column name public int GetColumnIndex(ssortingng columnName) { int index = -1; for (int i = 0; i < _headers.Length; i++) { if (_headers[i].Replace(" ","").Equals(columnName, SsortingngComparison.CurrentCultureIgnoreCase)) { index = i; return index; } } return index; } } indice = i; // A class that can read and parse the data in a CSV file. public class CSVReader { // Regex expression that's used to parse the data in a line of a CSV file private const ssortingng ESCAPE_SPLIT_REGEX = "({1}[^{1}]*{1})*(?<Separator>{0})({1}[^{1}]*{1})*"; // Ssortingng array to hold the headers (column names) private ssortingng[] _headers; // List of ssortingng arrays to hold the data in the CSV file. Each ssortingng array in the list represents one line (row). private List<ssortingng[]> _rows; // The StreamReader class that's used to read the CSV file. private StreamReader _reader; public CSVReader(StreamReader reader) { _reader = reader; Parse(); } // Reads and parses the data from the CSV file private void Parse() { _rows = new List<ssortingng[]>(); ssortingng[] row; int rowNumber = 1; var headerLine = "RowNumber," + _reader.ReadLine(); _headers = GetEscapedSVs(headerLine); rowNumber++; while (!_reader.EndOfStream) { var line = rowNumber + "," + _reader.ReadLine(); row = GetEscapedSVs(line); _rows.Add(row); rowNumber++; } _reader.Close(); } private ssortingng[] GetEscapedSVs(ssortingng data) { if (!data.EndsWith(",")) data = data + ","; return GetEscapedSVs(data, ",", "\""); } // Parses each row by using the given separator and escape characters private ssortingng[] GetEscapedSVs(ssortingng data, ssortingng separator, ssortingng escape) { ssortingng[] result = null; int priorMatchIndex = 0; MatchCollection matches = Regex.Matches(data, ssortingng.Format(ESCAPE_SPLIT_REGEX, separator, escape)); // Skip empty rows... if (matches.Count > 0) { result = new ssortingng[matches.Count]; for (int index = 0; index <= result.Length - 2; index++) { result[index] = data.Subssortingng(priorMatchIndex, matches[index].Groups["Separator"].Index - priorMatchIndex); priorMatchIndex = matches[index].Groups["Separator"].Index + separator.Length; } result[result.Length - 1] = data.Subssortingng(priorMatchIndex, data.Length - priorMatchIndex - 1); for (int index = 0; index <= result.Length - 1; index++) { if (Regex.IsMatch(result[index], ssortingng.Format("^{0}.*[^{0}]{0}$", escape))) result[index] = result[index].Subssortingng(1, result[index].Length - 2); result[index] = result[index].Replace(escape + escape, escape); if (result[index] == null || result[index] == escape) result[index] = ""; } } return result; } // Returns the number of rows public int RowCount { get { if (_rows == null) return 0; return _rows.Count; } } // Returns the number of headers (columns) public int HeaderCount { get { if (_headers == null) return 0; return _headers.Length; } } // Returns the value in a given column name and row index public object GetValue(ssortingng columnName, int rowIndex) { if (rowIndex >= _rows.Count) { return null; } var row = _rows[rowIndex]; int colIndex = GetColumnIndex(columnName); if (colIndex == -1 || colIndex >= row.Length) { return null; } var value = row[colIndex]; return value; } // Returns the column index of the provided column name public int GetColumnIndex(ssortingng columnName) { int index = -1; for (int i = 0; i < _headers.Length; i++) { if (_headers[i].Replace(" ","").Equals(columnName, SsortingngComparison.CurrentCultureIgnoreCase)) { index = i; return index; } } return index; } }

CSVValueProviderFactory Classe:

 public class CSVValueProviderFactory : ValueProviderFactory { public override IValueProvider GetValueProvider(ControllerContext controllerContext) { var uploadedFiles = controllerContext.HttpContext.Request.Files; if (uploadedFiles.Count > 0) { var file = uploadedFiles[0]; var extension = file.FileName.Split('.').Last(); if (extension.Equals("csv", SsortingngComparison.CurrentCultureIgnoreCase)) { if (file.ContentLength > 0) { var stream = file.InputStream; var csvReader = new CSVReader(new StreamReader(stream, Encoding.Default, true)); return new CSVValueProvider(controllerContext, csvReader); } } } return null; } } returnner nulle; public class CSVValueProviderFactory : ValueProviderFactory { public override IValueProvider GetValueProvider(ControllerContext controllerContext) { var uploadedFiles = controllerContext.HttpContext.Request.Files; if (uploadedFiles.Count > 0) { var file = uploadedFiles[0]; var extension = file.FileName.Split('.').Last(); if (extension.Equals("csv", SsortingngComparison.CurrentCultureIgnoreCase)) { if (file.ContentLength > 0) { var stream = file.InputStream; var csvReader = new CSVReader(new StreamReader(stream, Encoding.Default, true)); return new CSVValueProvider(controllerContext, csvReader); } } } return null; } }

CSVValueProvider Classe:

 // Represents a value provider for the data in an uploaded CSV file. public class CSVValueProvider : IValueProvider { private CSVReader _csvReader; public CSVValueProvider(ControllerContext controllerContext, CSVReader csvReader) { if (controllerContext == null) { throw new ArgumentNullException("controllerContext"); } if (csvReader == null) { throw new ArgumentNullException("csvReader"); } _csvReader = csvReader; } public bool ContainsPrefix(ssortingng prefix) { if (prefix.Contains('[') && prefix.Contains(']')) { if (prefix.Contains('.')) { var header = prefix.Split('.').Last(); if (_csvReader.GetColumnIndex(header) == -1) { return false; } } int index = int.Parse(prefix.Split('[').Last().Split(']').First()); if (index >= _csvReader.RowCount) { return false; } } return true; } public ValueProviderResult GetValue(ssortingng key) { if (!key.Contains('[') || !key.Contains(']') || !key.Contains('.')) { return null; } object value = null; var header = key.Split('.').Last(); int index = int.Parse(key.Split('[').Last().Split(']').First()); value = _csvReader.GetValue(header, index); if (value == null) { return null; } return new ValueProviderResult(value, value.ToSsortingng(), CultureInfo.CurrentCulture); } } returnner nulle; // Represents a value provider for the data in an uploaded CSV file. public class CSVValueProvider : IValueProvider { private CSVReader _csvReader; public CSVValueProvider(ControllerContext controllerContext, CSVReader csvReader) { if (controllerContext == null) { throw new ArgumentNullException("controllerContext"); } if (csvReader == null) { throw new ArgumentNullException("csvReader"); } _csvReader = csvReader; } public bool ContainsPrefix(ssortingng prefix) { if (prefix.Contains('[') && prefix.Contains(']')) { if (prefix.Contains('.')) { var header = prefix.Split('.').Last(); if (_csvReader.GetColumnIndex(header) == -1) { return false; } } int index = int.Parse(prefix.Split('[').Last().Split(']').First()); if (index >= _csvReader.RowCount) { return false; } } return true; } public ValueProviderResult GetValue(ssortingng key) { if (!key.Contains('[') || !key.Contains(']') || !key.Contains('.')) { return null; } object value = null; var header = key.Split('.').Last(); int index = int.Parse(key.Split('[').Last().Split(']').First()); value = _csvReader.GetValue(header, index); if (value == null) { return null; } return new ValueProviderResult(value, value.ToSsortingng(), CultureInfo.CurrentCulture); } } returnner nulle; // Represents a value provider for the data in an uploaded CSV file. public class CSVValueProvider : IValueProvider { private CSVReader _csvReader; public CSVValueProvider(ControllerContext controllerContext, CSVReader csvReader) { if (controllerContext == null) { throw new ArgumentNullException("controllerContext"); } if (csvReader == null) { throw new ArgumentNullException("csvReader"); } _csvReader = csvReader; } public bool ContainsPrefix(ssortingng prefix) { if (prefix.Contains('[') && prefix.Contains(']')) { if (prefix.Contains('.')) { var header = prefix.Split('.').Last(); if (_csvReader.GetColumnIndex(header) == -1) { return false; } } int index = int.Parse(prefix.Split('[').Last().Split(']').First()); if (index >= _csvReader.RowCount) { return false; } } return true; } public ValueProviderResult GetValue(ssortingng key) { if (!key.Contains('[') || !key.Contains(']') || !key.Contains('.')) { return null; } object value = null; var header = key.Split('.').Last(); int index = int.Parse(key.Split('[').Last().Split(']').First()); value = _csvReader.GetValue(header, index); if (value == null) { return null; } return new ValueProviderResult(value, value.ToSsortingng(), CultureInfo.CurrentCulture); } }

Pour la validation, comme je l'ai mentionné précédemment, j'ai pensé qu'il ne serait pas efficace de le faire en utilisant les attributes DataAnnotation. Une validation par rangée par ligne des données prendrait beaucoup de time pour les files CSV avec des milliers de lignes. Donc, j'ai décidé de valider datatables dans le controller après la fin du model. Je devrais également mentionner que je devais valider datatables dans le file CSV contre certaines données de la database. Si vous avez juste besoin de valider des choses comme l'adresse e-mail ou le numéro de téléphone, vous pouvez également utiliser DataAnnotation.

Voici un exemple de méthode pour valider la colonne Adresse email:

 private void ValidateEmailAddress(IEnumerable<CSVViewModel> csvData) { var invalidRows = csvData.Where(d => ValidEmail(d.EmailAddress) == false).ToList(); foreach (var invalidRow in invalidRows) { var key = ssortingng.Format("csvData[{0}].{1}", invalidRow.RowNumber - 2, "EmailAddress"); ModelState.AddModelError(key, "Invalid Email Address"); } } private static bool ValidEmail(ssortingng email) { if(email == "") return false; else return new System.Text.RegularExpressions.Regex(@"^[\w-\.]+@([\w-]+\.)+[\w-]{2,6}$").IsMatch(email); }

MISE À JOUR 2:

Pour une validation à l'aide de DataAnnotaion, vous utilisez simplement les attributes DataAnnotation dans votre CSVViewModel comme ci-dessous (CSVViewModel est la class dans laquelle vos données CSV seront liées dans votre Action Controller):

 public class CSVViewModel { // User proper names for your CSV columns, these are just examples... [Required] public int Column1 { get; set; } [Required] [SsortingngLength(30)] public ssortingng Column2 { get; set; } }