导入Excel工作表并使用松散耦合验证导入的数据

我在一个项目上也做过同样的事情。不同的是，我不必导入Excel工作表，而是导入CSV文件。我创建了一个CSVValueProvider。因此，CSV数据自动绑定到我的IEnumerable模型。

至于验证，我认为遍历所有行和单元格并逐一验证它们不是很有效，尤其是当CSV文件有数千条记录时。因此，我所做的是创建了一些验证方法，这些方法逐列而不是逐行地检查CSV数据，并对每列执行linq查询，返回包含无效数据的单元格的行号。然后，将无效的行号/列名添加到ModelState中。

更新：

以下是我所做的。。。

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 string ESCAPE_SPLIT_REGEX = "({1}[^{1}]*{1})*(?<Separator>{0})({1}[^{1}]*{1})*";
    // String array to hold the headers (column names)
    private string[] _headers;
    // List of string arrays to hold the data in the CSV file. Each string array in the list represents one line (row).
    private List<string[]> _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<string[]>();
        string[] 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 string[] GetEscapedSVs(string data)
    {
        if (!data.EndsWith(","))
            data = data + ",";
        return GetEscapedSVs(data, ",", "'"");
    }
    // Parses each row by using the given separator and escape characters
    private string[] GetEscapedSVs(string data, string separator, string escape)
    {
        string[] result = null;
        int priorMatchIndex = 0;
        MatchCollection matches = Regex.Matches(data, string.Format(ESCAPE_SPLIT_REGEX, separator, escape));
        // Skip empty rows...
        if (matches.Count > 0) 
        {
            result = new string[matches.Count];
            for (int index = 0; index <= result.Length - 2; index++)
            {
                result[index] = data.Substring(priorMatchIndex, matches[index].Groups["Separator"].Index - priorMatchIndex);
                priorMatchIndex = matches[index].Groups["Separator"].Index + separator.Length;
            }
            result[result.Length - 1] = data.Substring(priorMatchIndex, data.Length - priorMatchIndex - 1);
            for (int index = 0; index <= result.Length - 1; index++)
            {
                if (Regex.IsMatch(result[index], string.Format("^{0}.*[^{0}]{0}$", escape))) 
                    result[index] = result[index].Substring(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(string 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(string columnName)
    {
        int index = -1;
        for (int i = 0; i < _headers.Length; i++)
        {
            if (_headers[i].Replace(" ","").Equals(columnName, StringComparison.CurrentCultureIgnoreCase))
            {
                index = i;
                return index;
            }
        }
        return index;
    }
}

CSV值提供者工厂类别：

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", StringComparison.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;
    }
}

CSV值提供程序类：

// 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(string 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(string 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.ToString(), CultureInfo.CurrentCulture);
    }
}

对于验证，正如我之前提到的，我认为使用DataAnnotation属性进行验证是无效的。对于具有数千行的CSV文件，逐行验证数据将花费很长时间。因此，我决定在模型绑定完成后验证控制器中的数据。我还应该提到，我需要根据数据库中的一些数据验证CSV文件中的数据。如果你只需要验证电子邮件地址或电话号码之类的东西，你还可以使用DataAnnotation。

以下是验证电子邮件地址列的示例方法：

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

更新2:

对于使用DataAnnotaion的验证，您只需在CSVViewModel中使用DataAnnotation属性，如下所示（CSVViewModel是您的CSV数据将在控制器操作中绑定到的类）：

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