Accessing Excel using Visual Studio Tools for the Microsoft Office system
Using Visual Tools for Office enables the developer to access office documents such as Word or Excel. The example below illustrates the way in which you can export data to Excel by generating an Excel document from within Visual Studio .NET 2003.
As well as Microsoft Visual Studio .NET 2003 and Microsoft Office 2003, you must also install Visual Studio Tools for the Microsoft Office System.
The first step is to create a reference in the project to the Excel x.x Object Library (the version number depends on the installed version of Microsoft Office). Right-click on the References folder in the Solution Explorer and choose Add Reference. Choose the COM tab and pick Microsoft Excel x.x Object Library (Figure 1).
Figure 1 Adding an Excel reference
In this example, the DataMonitor application is modified so that it generates an MS-Excel document. Taking the DataMonitor (Oatc.OpenMI.Tools.Datamonitor.csproj) as a starting point, you need to change some of the methods:
- Add 'using' statements.
- Declare workbooks, sheets etc.
- Add source to the Initialize method in order to create a workbook and a worksheet.
- Modify the OnEvent code for the DataMonitor so that it writes the values to the spreadsheet.
The difficult part is to determine the cell co-ordinates for the spreadsheet. Excel uses ranges such as (A1:C1). In our example, such a range is being created for each value in the ValueSet (B3:B3) based on the row number and position in the 'subitems' array.
The code shows how to incorporate Excel documents in .NET code. This code needs to be completed by saving the Excel document to a specified filename (either using an argument from the OMI file or by user input) and closing the file.