And now to something completely different... the Excel automation. Every now and then you need to open up an Excel file and do stuff with it. In this case we needed to create and read from named ranges in Excel, and it's not overy easy to get information about how to do that, so I thought I might as well post some sample code here.
To get started with Excel automation, there is a good KB-article on MSDN: http://support.microsoft.com/kb/q302094
Now, here's a console app in .NET 3.5 using VB.NET (I prefer to work with VB.NET with Office Interop and VSTO), which opens Excel, adds a new workbook, creates a (3,5) string array with values, fills the a range with these values, creates a named range and gets the values again to see that it worked properly.
Imports Microsoft.Office.Interop Module Module1 Sub Main() Dim application = New Excel.Application application.Visible = True Dim workbook = application.Workbooks.Add() Dim worksheet As Excel._Worksheet = workbook.ActiveSheet Dim saRet(3, 5) As String For iRow = 0 To 3 For iCol = 0 To 5 saRet(iRow, iCol) = iRow.ToString() + "|" + iCol.ToString() Next iCol Next iRow
'get a range, alternative 1 'Dim range As Excel.Range = worksheet.Range("A1:E3", Reflection.Missing.Value) 'get a range, alternative 2 Dim range As Excel.Range = worksheet.Range("A1", Reflection.Missing.Value) range = range.Resize(3, 5) 'set value of range to that of the string array range.Value = saRet 'name the range, explicitly (using dollar $ sign) workbook.Names.Add("NamedRange", "=$A$1:$E$3")
'clear range range = Nothing 'get the values of the named range range = worksheet.Range("NamedRange") Dim values(,) As Object = range.Value Console.WriteLine("rows:" & values.GetUpperBound(0)) Console.WriteLine("cols:" & values.GetUpperBound(1)) Console.WriteLine("value of row 2, column 4 (D4) = " & values(2, 4)) Console.WriteLine("Press key to exit...") Console.Read() workbook.Close(False) application.Quit() End Sub End Module
Hope this helps someone.