CODEWITHSUNDEEP
excelperformancedelphi
user1009073
user1009073

Reputation: 3238

Delphi - Excel - How to speed up operation

I am writing a plug-in for Excel in Delphi XE6. I am having to "copy" rows from one sheet to another sheet. The challenge is that my operation is taking a lot longer than I think it should. For example, to copy 500 rows (23 columns) from one sheet to another takes 28 seconds or so...

Part of the challenge is that I have to process row by row. I have an array of INT, one INT per row. The INT value tells me if I need to copy that row...

...for loop i ... through entire array....
...if row I am interested in...

      // Set the Original Sheet Active
      MasterOrigSheet.Select;

      // Build my row numbers
      SourceRangeCell := 'A' + IntToStr(i);
      DestRangeCell := 'A' + IntToStr(DestRowNumber);

      // Copy the row from source to destination
      MasterOrigSheet.Range[SourceRangeCell, SourceRangeCell].EntireRow.Copy
        (NewSheet.Range[DestRangeCell, DestRangeCell].EntireRow);
      Inc(DestRowNumber);

Is there a faster way to do this? I am copying the entire row. I am not using the COPY/PASTE buffer... Any help or ideas appreciated.

Upvotes: 2

Views: 1990

Answers (1)

Rob Kennedy
Rob Kennedy

Reputation: 163357

The goal is to limit the number of calls you need to make to Excel.

To that end, take advantage of the fact that the Worksheet.Range property accepts multiple cells in a single string. Use the union operator (comma) to describe multiple cells at once. That is, in your loop, build up a comma-separated list of the cells you want to copy. Then call Range to get a range reference, and use EntireRow.Copy on that single range.

Also, avoid repeating work that doesn't need to be done. For example, the origin sheet should remain selected throughout your loop, so you don't need to keep on telling Excel to select it on each iteration. Even though the operation has no net effect, your program still needs to go through the work of packing up the COM call and delivering it to Excel.

Upvotes: 9

Related Questions