Wednesday 14 April 2004

C# and Excel Automation : Google Groups to the rescue

I don't post to newsgroups that often (although I try to contribute as much as I can to asp.net), but whenever I do I'm always amazed (and thankful) at the speed and quality of responses.

Overnight my problem with Excel automation was solved
Google Groups: Looping in Excel XP with C# -- Ranges and SpecialCells - reducing processing time from 90 minutes to 3 minutes!

Here is the 'working' code; loops through the Areas returned by the SpecialCells method so we only process non-empty cells...

Excel.Range range = sheet.UsedRange;

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
}
}
}
FYI, the SpecialCells (object)3 parameter is the sum of relevant 'constants' below:
XlSpecialCellsValue 

xlErrors 16
xlLogical 4
xlNumbers 1
xlTextValues 2

No comments:

Post a Comment

Note: only a member of this blog may post a comment.