Looping in Excel XP with C# -- Ranges and SpecialCells

  • Thread starter Thread starter craigd
  • Start date Start date
C

craigd

I'm writing to code that needs to check ALL text in a Workbook. The
initial solution - looping through the 'UsedRange' - works fine:

range = sheet.UsedRange;
for (int row = 1; row <= range.Rows.Count; row++) {
for (int col = 1; col <= range.Columns.Count; col++){
cell = (Excel.Range)range.Cells[row, col];
// do stuff with cell.Value2
}
}

BUT then when I tried it with a *complex* XLS (9 sheets, lots of
cells) it takes *forever* (almost 90 minutes) to process.

So I started investigating the 'SpecialCells' property which *should*
return a range with only 'matching' cells and not blanks.

Excel.Range newrange =
range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants,
(object)3 );

To give you some idea - on Sheet 1 of my XLS, UsedRange.Count = 22,000
but SpecialCells returned newrange.Count = 192 (the data is quite
spread out). I'm thinking that will *significantly* reduce my
processing time...

HOWEVER, the range returned by SpecialCells has Rows=1 and Columns=1
(even though Count=192) which means I cannot iterate through it using
the loop shown above. I also can't iterate through it using FOREACH
because of http://support.microsoft.com/?kbid=328347
It looks like the range returned by SpecialCells is a combination of
the first matching cell, with the Cells collection containing the rest
- but I can't iterate through them!
The get_Item() method requires row & column parameters, but I can't
determine them either.

Any suggestions MUCH appreciated.

(e-mail address removed)
 
Hi Craigd,
To give you some idea - on Sheet 1 of my XLS, UsedRange.Count = 22,000
but SpecialCells returned newrange.Count = 192 (the data is quite
spread out). I'm thinking that will *significantly* reduce my
processing time...
Click to expand...


Note that there is a bug that limits the amount of cells returned by the
specialcells method to only 8192 cells!
HOWEVER, the range returned by SpecialCells has Rows=1 and Columns=1
(even though Count=192) which means I cannot iterate through it using
the loop shown above. I also can't iterate through it using FOREACH
because of http://support.microsoft.com/?kbid=328347
It looks like the range returned by SpecialCells is a combination of
the first matching cell, with the Cells collection containing the rest
- but I can't iterate through them!
The get_Item() method requires row & column parameters, but I can't
determine them either.
Click to expand...

The specialcells method may return a collection of areas (non-contiguous
ranges) which each contain a collection of cells. You must therefore
loop through the cells in each area separately.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
Note that there is a bug that limits the amount of cells returned by the
specialcells method to only 8192 cells!
Click to expand...

I believe you mean 8192 areas. Each area can be multiple cells, so it can
return more then 8192 cells.
 
Jan, *thanks* for the quick reply.
The specialcells method may return a collection of areas (non-contiguous
ranges) which each contain a collection of cells. You must therefore
loop through the cells in each area separately.
Click to expand...

You are correct - I didn't understand that from the documentation.

The following code (looping through SpecialCells:Areas:Ranges:Cells
rather than ALL the UsedRange cells) reduced the 'processing time' for
my test spreadsheet from 90 minutes to under 3 minutes!

Excel.Range newrange =
range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeConstants,
(object)3 );
for (int areaid = 1; areaid <= newrange.Areas.Count; areaid++){
Excel.Range arearange = newrange.Areas.get_Item(areaid);
for (int row = 1; row <= arearange.Rows.Count; row++){
for (int col = 1; col <= arearange.Columns.Count; col++){
cell = (Excel.Range)arearange.Cells[row, col];
// do stuff with cell.Value2
}
}
}

My test doc (which is intentionally quite complex) doesn't approach
the 8192-range-limit; if that happens in production then we've
probably got other problems too!

Thanks again
(e-mail address removed)
 
Back
Top