如何用C#高效读取合并大数据Excel工作表并验证数据

Hey Brian, your current Excel Interop approach is slow because every cell access is a cross-process COM call—super inefficient for 25k-row sheets. Let's switch to a .NET library built for bulk Excel operations, like EPPlus (for .xlsx files, free for non-commercial use) or NPOI (supports .xls/.xlsx, open-source). Below is a complete, optimized solution covering all your requirements:

1. Define Proper Entity Classes #

First, fix the mismatched type conversions in your original code (e.g., InfoDesc was incorrectly parsed as int) and create a combined class for merged data:

// Sheet1 data model
public class ImportSheet1
{
    public string Info { get; set; }
    public int ID { get; set; }
    public string InfoDesc { get; set; }
    public string InfoType { get; set; }
    public string DataType { get; set; }
    public string RateFormat { get; set; }
    public string InfoID => $"{Info}{ID}"; // Auto-generated composite key
}

// Sheet2 data model
public class ImportSheet2
{
    public string Info { get; set; }
    public int ID { get; set; }
    public int CertNum { get; set; }
    public decimal CertVal { get; set; } // Use decimal for currency values
    public string InfoID => $"{Info}{ID}";
}

// Merged data model with validation errors
public class CombinedData
{
    public string Info { get; set; }
    public int ID { get; set; }
    public string InfoDesc { get; set; }
    public string InfoType { get; set; }
    public string DataType { get; set; }
    public string RateFormat { get; set; }
    public int CertNum { get; set; }
    public decimal CertVal { get; set; }
    public List<string> ValidationErrors { get; set; } = new List<string>();
}

2. Bulk Read Excel Data #

EPPlus reads the entire worksheet into an in-memory 2D array, eliminating slow per-cell COM calls:

using OfficeOpenXml;
using System.IO;

public List<ImportSheet1> ReadSheet1(string filePath)
{
    var sheet1List = new List<ImportSheet1>();
    
    using (var package = new ExcelPackage(new FileInfo(filePath)))
    {
        var worksheet = package.Workbook.Worksheets["Sheet1"];
        var dataRange = worksheet.Dimension;
        if (dataRange == null) return sheet1List;
        
        // Load all data into a 2D array in one go
        var data = worksheet.Cells[1, 1, dataRange.Rows, dataRange.Columns].Value as object[,];
        
        // Skip header row (start at row 2)
        for (int row = 2; row <= dataRange.Rows; row++)
        {
            sheet1List.Add(new ImportSheet1
            {
                Info = Convert.ToString(data[row, 1]),
                ID = TryParseInt(data[row, 2], out int id) ? id : 0,
                InfoDesc = Convert.ToString(data[row, 3]),
                InfoType = Convert.ToString(data[row, 4]),
                DataType = Convert.ToString(data[row, 5]),
                RateFormat = data[row, 6] != null ? Convert.ToString(data[row, 6]) : null
            });
        }
    }
    return sheet1List;
}

public List<ImportSheet2> ReadSheet2(string filePath)
{
    var sheet2List = new List<ImportSheet2>();
    
    using (var package = new ExcelPackage(new FileInfo(filePath)))
    {
        var worksheet = package.Workbook.Worksheets["SHEET2"];
        var dataRange = worksheet.Dimension;
        if (dataRange == null) return sheet2List;
        
        var data = worksheet.Cells[1, 1, dataRange.Rows, dataRange.Columns].Value as object[,];
        
        for (int row = 2; row <= dataRange.Rows; row++)
        {
            sheet2List.Add(new ImportSheet2
            {
                Info = Convert.ToString(data[row, 1]),
                ID = TryParseInt(data[row, 2], out int id) ? id : 0,
                CertNum = TryParseInt(data[row, 3], out int certNum) ? certNum : 0,
                CertVal = TryParseDecimal(data[row, 4], out decimal certVal) ? certVal : 0
            });
        }
    }
    return sheet2List;
}

// Helper methods to avoid conversion exceptions
private bool TryParseInt(object value, out int result)
{
    result = 0;
    return value != null && int.TryParse(value.ToString(), out result);
}

private bool TryParseDecimal(object value, out decimal result)
{
    result = 0;
    return value != null && decimal.TryParse(value.ToString(), out result);
}

3. Merge Data Efficiently #

Use a dictionary for O(1) lookups instead of nested loops or slow LINQ joins:

public List<CombinedData> CombineData(List<ImportSheet1> sheet1, List<ImportSheet2> sheet2)
{
    // Create a dictionary for fast Sheet2 lookups
    var sheet2Lookup = sheet2.ToDictionary(item => item.InfoID);
    var combinedList = new List<CombinedData>();
    
    // Merge Sheet1 items with matching Sheet2 data
    foreach (var sheet1Item in sheet1)
    {
        var combined = new CombinedData
        {
            Info = sheet1Item.Info,
            ID = sheet1Item.ID,
            InfoDesc = sheet1Item.InfoDesc,
            InfoType = sheet1Item.InfoType,
            DataType = sheet1Item.DataType,
            RateFormat = sheet1Item.RateFormat
        };
        
        if (sheet2Lookup.TryGetValue(sheet1Item.InfoID, out var sheet2Item))
        {
            combined.CertNum = sheet2Item.CertNum;
            combined.CertVal = sheet2Item.CertVal;
        }
        else
        {
            combined.ValidationErrors.Add($"No matching Sheet2 entry for InfoID: {sheet1Item.InfoID}");
        }
        
        ValidateCombinedData(combined);
        combinedList.Add(combined);
    }
    
    // Add Sheet2 items that don't exist in Sheet1
    foreach (var sheet2Item in sheet2)
    {
        if (!sheet1.Any(item => item.InfoID == sheet2Item.InfoID))
        {
            var combined = new CombinedData
            {
                Info = sheet2Item.Info,
                ID = sheet2Item.ID,
                CertNum = sheet2Item.CertNum,
                CertVal = sheet2Item.CertVal,
                ValidationErrors = { $"No matching Sheet1 entry for InfoID: {sheet2Item.InfoID}" }
            };
            
            ValidateCombinedData(combined);
            combinedList.Add(combined);
        }
    }
    
    return combinedList;
}

4. Validate Row Data #

Implement your custom validation rules to check for empty fields, invalid data types, etc.:

private void ValidateCombinedData(CombinedData data)
{
    // Check required fields
    if (string.IsNullOrEmpty(data.Info))
    {
        data.ValidationErrors.Add("Info field cannot be empty");
    }
    
    if (data.ID <= 0)
    {
        data.ValidationErrors.Add("ID must be a positive integer");
    }
    
    // Validate data type consistency
    if (!string.IsNullOrEmpty(data.DataType) && data.DataType.Equals("Numeric", StringComparison.OrdinalIgnoreCase))
    {
        if (string.IsNullOrEmpty(data.RateFormat) || !data.RateFormat.Contains("$$"))
        {
            data.ValidationErrors.Add($"Invalid RateFormat for Numeric DataType: {data.RateFormat}");
        }
        
        if (data.CertVal <= 0)
        {
            data.ValidationErrors.Add("CertVal must be greater than 0 for Numeric DataType");
        }
    }
    
    if (data.CertNum <= 0)
    {
        data.ValidationErrors.Add("CertNum must be a positive integer");
    }
}

5. Usage Example #

string excelFilePath = @"C:\your-file-path.xlsx";
var sheet1Data = ReadSheet1(excelFilePath);
var sheet2Data = ReadSheet2(excelFilePath);
var mergedData = CombineData(sheet1Data, sheet2Data);

// Print rows with validation errors
var errorRows = mergedData.Where(row => row.ValidationErrors.Any());
foreach (var row in errorRows)
{
    Console.WriteLine($"InfoID: {row.Info}{row.ID} | Errors: {string.Join(", ", row.ValidationErrors)}");
}

Why This Is Faster #

• Bulk Data Loading: Reads the entire worksheet into memory in one operation, avoiding thousands of slow COM calls.
• Dictionary Lookups: Merging uses hash tables for O(1) lookups, reducing time complexity from O(n*m) to O(n+m).
• Safe Type Conversions: Helper methods prevent costly exceptions during parsing.

Notes #

• For .xls files, replace EPPlus with NPOI (open-source, supports both formats).
• EPPlus requires a commercial license for business use; check their licensing terms if needed.

内容的提问来源于stack exchange，提问作者Brian