SpreadsheetGear处理IWorksheet性能优化求助：11500行数据表处理过慢

Hey there, let's work through this performance bottleneck you're hitting with your 11,500-row spreadsheet. 300 rows taking 8 seconds is definitely slower than it needs to be, so let's break down the common culprits and actionable fixes:

1. Lean Into the String Array Approach (It’s a Game-Changer!) #

Your instinct to ditch DataTable for a raw string array is spot-on. SpreadsheetGear’s Range.GetText() method lets you pull a 2D string array directly from the worksheet, skipping the heavy abstraction and overhead of converting every row/column into a DataRow/DataColumn. DataTable adds significant lookup and instantiation cost per row—cutting out this middleman alone will give you a massive speed boost.

Here’s how to implement it:

// Grab the full used range from your worksheet
IRange usedRange = worksheet.UsedRange;
// Pull raw text values directly into a 2D array
string[,] rawData = usedRange.GetText(SpreadsheetGear.ValueFlags.None);

// Iterate over the array directly (no DataTable overhead!)
int totalRows = rawData.GetLength(0);
int totalCols = rawData.GetLength(1);

for (int row = 0; row < totalRows; row++)
{
    for (int col = 0; col < totalCols; col++)
    {
        string cellValue = rawData[row, col];
        // Your processing logic here
    }
}

2. Fix Class Instantiation Bloat #

If you’re creating new class instances inside your loop (e.g., var processor = new RowProcessor(); for every single row), you’re triggering frequent garbage collection cycles that slow everything down. Instead:

• Reuse objects: Create one instance outside the loop and reset its properties for each row.
• Use object pooling: For more complex types, use a pool to grab pre-allocated instances instead of spinning up new ones each time.

Example of object reuse:

// Create your processor once, outside the loop
RowProcessor processor = new RowProcessor();

for (int row = 0; row < totalRows; row++)
{
    // Reset state instead of creating a new instance
    processor.Reset();
    processor.Process(rawData[row, 0], rawData[row, 1], ...);
}

3. Streamline Conditional Logic #

If your processing has nested or repeated checks, optimize them to cut down on unnecessary work:

• Cache repeated calculations: If you’re deriving a value from cell data and checking it multiple times, compute it once and store it in a variable.
• Simplify branching: Replace deep nested if statements with switch statements (where applicable) or early returns to reduce overhead.
• Avoid inline string operations: Skip + concatenation in loops—use StringBuilder if you need to build strings during processing.

4. SpreadsheetGear-Specific Tweaks #

• Disable automatic calculation: If your worksheet has formulas, turn off auto-calculation before reading data to prevent unnecessary recalculations:

  worksheet.WindowInfo.Calculation = SpreadsheetGear.Calculation.Manual;
  

• Skip empty rows/columns: If your sheet has blank rows, filter them out before processing to avoid wasted cycles.

Final Thought #

Switching to a raw string array will eliminate one of the biggest performance drags. Combine that with optimizing instance creation and conditional logic, and you should see your processing speed jump from ~37 rows/sec to hundreds (or even thousands) of rows per second.

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