***********************************************************************
UPDATE: 12/10/2003:
Related article: http://codebetter.com/blogs/mark.digiovanni/archive/2003/12/03/4199.aspx
Sample Code: http://www.weprogram.net/Materials/OfficeInterop.zip
***********************************************************************
I haven't found much information out there that addresses Excel automation for my needs. Here is the scenario:
Standard reports that currently exist in Excel will need to be reused when migrating to the new .NET application. My goal is to allow new reports to be added to the system without having to make any changes to the Excel output engine that I am creating. If the customer later decides to add a report, all they should have to do is create it in Excel, name the cells, and add the Excel spreadsheet to the database. Then call the report from wherever they decide.
The data database will have a table of report names and associated information and a related table to hold the cell names and the location of the cell's value. The engine will take the spreadsheet name as a parameter, pull the spreadsheet information from the database, get the data to populate the sheet, and either output the results or maintain the results to output later after several spreadsheets are produced and inserted into a single workbook.
Here is my solution:
First, download the Office PIA's and set a reference in the project. Then use the following code:
Imports msE = Microsoft.Office.Interop
Public Class OutputEngine
Public Sub AutomationSample(ByVal spreadSheetName As String)
Dim excelApp As New msE.Excel.ApplicationClass()
'Get the workbook that contains the sheet to work with.
'Change the Open method to point to the location of the spreadsheet
excelApp.Workbooks.Open("C:\Projects\ExcelTest\testNamed.xls", System.Reflection.Missing.Value, _
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, _
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, _
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, _
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, _
System.Reflection.Missing.Value)
Dim wkbook As msE.Excel.WorkbookClass = excelApp.Workbooks(1)
'Open the first worksheet.
Dim wksheet As msE.Excel.Worksheet
wksheet = wkbook.Sheets.Item(1)
'Assuming that the cells are properly named, set the their values to their numerical equivalent.
'The below code will be replaced with code to retrieve the data to place in the named cells.
wksheet.Range("NamedCell1").Value = 1
wksheet.Range("NamedCell2").Value = 2
wksheet.Range("NamedCell3").Value = 3
'Delete row 17 because we can :)
wksheet.Rows(17).Delete(msE.Excel.XlDeleteShiftDirection.xlShiftUp)
'This will launch the workbook into Excel. Saving of the spreadsheet will have to be done
'manually if the user decides to save it.
excelApp.Visible = True
End Sub
End Class
This allows the most flexibility when making changes to the spreadsheet or adding a new one to the system. The customer can change the font, column width, etc. without the need of a developer on hand.