2006.09.25 07:24 PM
Enumeration of Excel.Range Cells via .NET Interop
When using C# or VB.NET 2002/2003 (.NET 1.0/1.1) and either the Office XP or Office 2003 Primary Interop Assemblies from Microsoft to automate Excel, a System.Runtime.InteropServices.COMException "Member not found" error will be encountered when trying to enumerate the cells of a Range using foreach in C# or For Each in VB.NET. This issue was documented by Microsoft back in August 2004 in KB328347; however, the workaround they provided is insufficient for dealing with all Ranges.
What Microsoft's workaround doesn't take into account is that every Range includes at least one Area, itself a Range, which represents a single cell or a set of contiguous cells, and many Ranges include multiple Areas, such as "A1:B5,C1" or "A1:B5,B2:C3". So, in order to work correctly for all Range objects, Microsoft's workaround needs to include an Area enumeration as well.
Here's the Microsoft workaround, originally given in VB.NET, with an Area enumeration added to it:
Dim oApp As New Excel.Application() oApp.Visible = True oApp.UserControl = True Dim oSheet As Excel.Worksheet oSheet = oApp.Workbooks.Add.Worksheets.Item(1) Dim oRng As Excel.Range, oAreaRng As Excel.Range Dim nRows As Long, nCols As Long, nAreas as Long oRng = oSheet.Range("A1:B5,C1") ' Multiple Areas For nAreas = 1 To oRng.Areas.Count oAreaRng = oRng.Areas(nAreas) For nRows = 1 To oAreaRng.Rows.Count For nCols = 1 To oAreaRng.Columns.Count oAreaRng.Cells(nRows, nCols).Value = "test" Next Next Next
Even by Microsoft's historically weak sample standards that's some pretty ugly code. Plural enumeration variables? Requiring Option Strict Off in order to compile? Yuck.
Here's the same code in C#:
System.Reflection.Missing missing = System.Reflection.Missing.Value; Excel.Application oApp = new Excel.ApplicationClass(); oApp.Visible = true; oApp.UserControl = true; Excel.Worksheet oSheet; oSheet = (Excel.Worksheet) oApp.Workbooks.Add(missing).Worksheets[1]; Excel.Range oRng, oAreaRng; oRng = oSheet.get_Range("A1:B5,C1", missing); // Multiple Areas for (int nAreas = 1; nAreas <= oRng.Areas.Count; nAreas++) { oAreaRng = oRng.Areas[nAreas]; for (int nRows = 1; nRows <= oAreaRng.Rows.Count; nRows++) { for (int nCols = 1; nCols <= oAreaRng.Columns.Count; nCols++) { ((Excel.Range) oAreaRng.Cells[nRows, nCols]).Value2 = "test"; } } }
Ugly VB.NET Office automation code just looks worse in C#.
As far as I can tell, foreach/For Each enumeration of Range cells is not an issue in C# or VB.NET 2005 (.NET 2.0).
Comments
Thanks. This Article is very important for our.
Jorge | 2007.09.22 02:59 PM
Thanks. The code is very important for our too.
Calderas | 2009.11.04 01:37 AM
TrackBack
TrackBack URL: https://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d83534817953ef
Listed below are links to weblogs that reference Enumeration of Excel.Range Cells via .NET Interop: