如何将Excel UsedRange内容复制到数组以提升读取性能？

Great question—this is a super common pain point when working with Excel interop, since repeated calls to the Excel object model are notoriously slow. Let's break down your two questions clearly:

1. Does the Value2 approach assume all cells are a "Value2 type"? #

Short answer: No—Value2 isn't a "cell type" at all. It's a property of the Range object that returns the raw underlying value of the cell, skipping any formatting-related conversions that the regular Value property does. Here's what that means:

• For numeric cells (including dates, which Excel stores as doubles), Value2 returns a double instead of a DateTime or Currency object (which Value might return).
• For text cells, it returns a string just like Value.
• For empty cells, it returns DBNull.Value.

The beauty of Value2 is that it works with all cell contents—it's just faster because it avoids the overhead of converting values to formatted types. The code snippet you found uses it precisely because it's the most efficient way to pull values from Excel into a .NET object array.

2. How to load the entire UsedRange into a local array for fast access #

This is the key optimization to replace slow逐行 reading. Instead of fetching one cell at a time, you can read the entire used range in a single call to Excel, then work with the in-memory array (which is orders of magnitude faster). Here's how to do it:

Step-by-Step Code Example #

using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

// Assume cSheet is your initialized Worksheet object
Worksheet cSheet = ...;

// Get the used range (Excel automatically detects cells with content)
Range usedRange = cSheet.UsedRange;

// Load the entire used range into a 2D object array in one go
object[,] usedRangeData = usedRange.Value2 as object[,];

// Now you can work with the array directly (no more Excel interop calls!)
if (usedRangeData != null)
{
    // Note: Excel arrays are 1-indexed, not 0-indexed!
    int totalRows = usedRangeData.GetLength(0);
    int totalCols = usedRangeData.GetLength(1);

    for (int row = 1; row <= totalRows; row++)
    {
        for (int col = 1; col <= totalCols; col++)
        {
            object cellValue = usedRangeData[row, col];
            
            // Handle empty cells
            if (cellValue != DBNull.Value)
            {
                // Process the value—cast to appropriate type if needed
                if (cellValue is double numericValue)
                {
                    // Handle numbers/dates (convert double to DateTime if needed)
                    DateTime? cellDate = DateTime.FromOADate(numericValue);
                }
                else if (cellValue is string textValue)
                {
                    // Handle text content
                }
            }
        }
    }
}

// Important: Clean up Excel objects to avoid memory leaks
Marshal.ReleaseComObject(usedRange);
Marshal.ReleaseComObject(cSheet);

Key Notes: #

• 1-indexed array: Excel's range arrays start at row 1, column 1 (unlike .NET's usual 0-indexing)—don't forget this or you'll get index out of bounds errors!
• Memory efficiency: Loading the entire range at once minimizes cross-process calls between your app and Excel (each interop call is expensive).
• Cleanup: Always release COM objects with Marshal.ReleaseComObject to prevent Excel from lingering in memory after your app closes.

Final Takeaway #

Using Value2 is the fastest way to read cell values because it skips unnecessary formatting, and loading the entire UsedRange into a local array eliminates the performance hit of repeated Excel interop calls. This combination is the standard optimization for Excel interop performance.

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