Sunday, October 30, 2005

Office Interop with .NET

{

Are you still doing VBA with Office even though you know you should be doing your coding with the .NET Framework? Probably like many I did things the old way rather than dealing with COM Interop, especially since this was particularly daunting with Office.

It isn't as of a while ago, but you, like me, may have been a bit too busy to notice.

First, you must download the Office Interop Libraries from here. The instructions are clear; run the register.bat from the VS.NET command prompt. After that you can make a reference to the application interop assembly of your choice, such as Microsoft.Office.Interop.Excel.dll.

Reference the namespace in your app:
using Excel = Microsoft.Office.Interop.Excel;


Code away:

Excel.Application app = new Excel.Application();
Excel.Workbook book = app.Workbooks.Open(@"c:\data\Daita.xls",Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Excel.Worksheet sheet = (Excel.Worksheet)book.Worksheets[1];
for(int i=1;i<10;i++) j="1;j<7;j++)" rg =" (Excel.Range)sheet.Cells[i,j];">

A few interesting things:
  • The Type.Missing passed for all those COM-esque optional parameters. Ugh.
  • Casting the cell you are on into a Excel.Range when you need to access its contents.

Finally:
Many more articles can be found
here, on MSDN.

}

1 comment:

PPP - PennyPencilPixel said...

Interesting post man... have you tried this with ASP.net? I do mostly web apps and it would be cool if I can use interop with ASP.net...