The Interoperability services make it very easy to work with COM Capable Applications such as Word and Excel. You can also refer to my previous article on the topic: Real-time Stock Quotes in Excel using .NET for more information on accessing Excel via .NET. This article was written in response to a question asking How do I open and excel file and read it using .NET?
Figure 1 - Excel Spreadsheet read into a ListView
The first step is to create a reference in your project to Excel 9.0 Objects Library. This is done by right mouse clicking on the References folder in the Solution Explorer and choosing Add Reference. This brings up the Tab Dialog below. Choose the COM Tab and pick Microsoft Excel 9.0 Objects Library.
Figure 2 - Adding an Excel Reference
This action puts an Interop.Excel.dll and Interop.Office.dll into your bin directory so you can manipulate excel.
Now we can declare our Excel Application Object and the compiler will recognize it:
private Excel.Application ExcelObj = null;
Excel is launched and an Application reference is obtained in the constructor of our form. First an Excel Application object is constructed. Then we check to make sure Excel was actually started. If it was, we have a valid application object and we can now use it to open a file:
public Form1()
{
// Initialize the Windows Components
InitializeComponent();
ExcelObj = new Excel.Application();
// See if the Excel Application Object was successfully constructed
if (ExcelObj == null)
{
MessageBox.Show("ERROR: EXCEL couldn't be started!");
System.Windows.Forms.Application.Exit();
}
// Make the Application Visible
ExcelObj.Visible = true;
}
The code for opening the Excel file is shown below. The code uses the OpenFileDialog component to get the path name for the Excel file. The Excel file is opened using the WorkBooks collections' Open method. This method takes 15 parameters with the following definition.
Function Open(Filename As String, [UpdateLinks], [ReadOnly], [Format], [Password], [WriteResPassword], [IgnoreReadOnlyRecommended], [Origin], [Delimiter], [Editable], [Notify], [Converter], [AddToMenuRecentlyUsed]) As Workbook
We really are only interested in the FileName, but have added the other default parameters for your reference. There is also an OpenText method in Workbooks for opening tab or comma delimited text files.
private void menuItem2_Click(object sender, System.EventArgs e)
{
// prepare open file dialog to only search for excel files (had trouble setting this in design view)
this.openFileDialog1.FileName = "*.xls";if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
{
// Here is the call to Open a Workbook in Excel
// It uses most of the default values (except for the read-only which we set to true)
Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open( openFileDialog1.FileName, 0,
