如何优化ExcelDataReader读取Excel的速度？DataSet方案是否可行？

Great question—let’s break down how to speed up your Excel reading process and address those slow GetXXXData methods.

First: Yes, Using a DataSet Will Boost Performance #

Loading the entire Excel file into a DataSet first is absolutely a better approach than reading row-by-row and checking each entry. This reduces file I/O overhead (since you read the file once instead of incrementally) and lets you use efficient LINQ queries to locate your target row instead of looping through every entry.

Here’s a refactored version of your method using this approach:

public static async Task<Poste> ReadData(string posteNumber) {
    var cleanedPosteNumber = posteNumber.Replace(" ", string.Empty);
    Poste poste = null;

    using (var stream = await posteFile.OpenStreamForReadAsync()) {
        // Configure reader to skip non-spreadsheet data for faster loading
        var readerConfig = new ExcelReaderConfiguration {
            ReadSpreadsheetOnly = true,
            LeaveOpen = false
        };

        using (var reader = ExcelReaderFactory.CreateReader(stream, readerConfig)) {
            // Load only the columns you need to minimize memory usage and load time
            var dataSetConfig = new ExcelDataSetConfiguration {
                ConfigureDataTable = (_) => new ExcelDataTableConfiguration {
                    FilterColumn = (columnIndex, _) => 
                        columnIndex is 2 or 3 or 6 or 7 or 8 or 12 or 13 or 14 or 15 or 20 or 23 or 24 or 27 or 33 or 34 or 35
                }
            };

            var dataSet = reader.AsDataSet(dataSetConfig);
            var targetTable = dataSet.Tables[0]; // Assume your data is in the first sheet

            // Find the matching row with LINQ (far faster than manual looping)
            var targetRow = targetTable.AsEnumerable()
                .FirstOrDefault(row => {
                    var code = row.Field<string>(2);
                    return code != null && code.Replace(" ", string.Empty).Equals(cleanedPosteNumber);
                });

            if (targetRow != null) {
                poste = MapRowToPoste(targetRow);

                if (poste.CodePoste != null) {
                    // Run all async data fetch methods in parallel instead of sequentially
                    await Task.WhenAll(
                        GetTFMData(posteNumber, poste),
                        GetDLDData(posteNumber, poste),
                        GetAMMData(posteNumber, poste),
                        GetCompteurData(posteNumber, poste),
                        GetHTAData(posteNumber, poste),
                        GetBTData(posteNumber, poste)
                    );
                }
            }
        }
    }

    return poste;
}

// Helper method to clean up property mapping
private static Poste MapRowToPoste(DataRow row) {
    return new Poste {
        CodePoste = row.Field<string>(2),
        Denomination = row.Field<string>(3),
        Constructeur = row.Field<string>(27),
        DateMiseEnService = row.Field<string>(23),
        DateMiseHorsService = row.Field<string>(14),
        Etat = row.Field<string>(6),
        Exploitant = row.Field<string>(13),
        LibelleCommune = row.Field<string>(4),
        Nature = row.Field<string>(7),
        CodeSchema = row.Field<string>(20),
        TypeBatiment = row.Field<string>(8),
        CodeTournee = row.Field<string>(15),
        Acces = row.Field<string>(24),
        Tmni = row.Field<string>(12),
        Adresse = row.Field<string>(33),
        Coord = {
            Latitude = row.Field<string>(34),
            Longitude = row.Field<string>(35)
        }
    };
}

Additional Optimization Strategies #

1. Parallelize Async Data Fetching #

Your original code runs GetTFMData, GetDLDData, etc., sequentially with await each time. Using Task.WhenAll runs them in parallel, cutting down total time from the sum of all method durations to the longest single method’s duration.

2. Cache Frequently Queried Poste Objects #

If the same posteNumber is requested multiple times, cache the fully populated Poste object to avoid re-reading the Excel file and re-running those async methods. Use MemoryCache for this:

private static readonly MemoryCache _posteCache = new MemoryCache(new MemoryCacheOptions());

public static async Task<Poste> ReadData(string posteNumber) {
    var cleanedPosteNumber = posteNumber.Replace(" ", string.Empty);
    
    // Check cache first
    if (_posteCache.TryGetValue(cleanedPosteNumber, out Poste cachedPoste)) {
        // Return a copy to preserve data binding integrity
        return new Poste {
            CodePoste = cachedPoste.CodePoste,
            Denomination = cachedPoste.Denomination,
            // Copy all other properties...
            Coord = new Coord { Latitude = cachedPoste.Coord.Latitude, Longitude = cachedPoste.Coord.Longitude }
        };
    }

    // Existing loading logic...

    // Cache the result if found
    if (poste != null) {
        _posteCache.Set(cleanedPosteNumber, poste, new MemoryCacheEntryOptions {
            AbsoluteExpirationRelativeToNow = TimeSpan.FromHours(1) // Adjust expiration as needed
        });
    }

    return poste;
}

3. Preload Excel Data on Startup #

For high-frequency queries, load the entire Excel dataset into memory when your application starts. This eliminates file I/O entirely during runtime queries:

private static DataSet _preloadedExcelData;

// Call this once at app startup
public static async Task PreloadExcelData() {
    using (var stream = await posteFile.OpenStreamForReadAsync()) {
        using (var reader = ExcelReaderFactory.CreateReader(stream, new ExcelReaderConfiguration { ReadSpreadsheetOnly = true })) {
            _preloadedExcelData = reader.AsDataSet();
        }
    }
}

// Then in ReadData, use _preloadedExcelData instead of reloading the file

4. Optimize the GetXXXData Methods #

Since these methods follow the same Excel-reading pattern, apply the same optimizations:

• Load their target Excel files into DataSet once, not per call
• Use LINQ for fast row lookups
• Cache their results
• Run them in parallel where possible

5. Consider a Better Data Store #

If your Excel file is large or queries are frequent, import the data into a lightweight database like SQLite. Databases are optimized for fast lookups (especially with indexes on your code column) and will outperform Excel file reads by a wide margin.

Notes on Data Binding #

Your requirement to maintain data binding is satisfied here: we’re creating a new Poste instance each time (or a copy of the cached instance), which ensures your UI binding behaves as expected. If your Poste class implements INotifyPropertyChanged, updates to properties from the async methods will also reflect in the UI automatically.

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