How to open excel file using .Net with COM Interop

In my previous post , I showed how you can use ADO.Net to manipulate an Excel file. Everything went fine with my little project till I hit a brick wall. Now I needed to add some new columns to the worksheet. This was all fine, till it came to the point that certain cells had to be formatted in certain colors and styles etc. Well this is where ADO.Net model could not help much. That when I had to go back to using Microsoft Excel Interop object model. Yes, this is going to add limitation to your project that now you have to make sure that Microsoft Excel is installed on your machine where the application needs to be deployed. Anyhow, here is sample code that shows how you can open a workbook and list the worksheets contained in it. Notice the following namespaces that you will have to add at the top for Excel interop classes and interfaces.

using System;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace ExcelReportGenerator
{
 class Program
 {
   static void Main(string[] args)
   {
     var xl = new Microsoft.Office.Interop.Excel.Application();
     if (null == xl)
     {
       Console.WriteLine("Excel is not installed or could not be initialized");
       return;
     }

     var currentXlAppPath = xl.Path;
     xl.DefaultFilePath = System.Environment.CurrentDirectory;
     var reportFile = System.IO.Path.Combine(System.Environment.CurrentDirectory, "PriceData.xls");
     Workbook workbook = null;
     try
     {
        workbook = xl.Workbooks.Open(reportFile, 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);
        if (null == workbook)
        {
           Console.WriteLine("Workbook could not be opened");
           return;
        }
        // Check for work sheets.
        Console.WriteLine("There are {0} worksheets in this workbook", 
                          workbook.Worksheets.Count);
        foreach (Worksheet ws in workbook.Worksheets)
        {
          Console.WriteLine("Worksheet: {0}", ws.Name);
        }
       }
       catch (Exception ex)
       {
         Console.WriteLine(ex.Message);
         throw;
       }
       finally
       {
        if (null != workbook)
        {
          workbook.Close();
        }
       }
      }
   }
}

Excel File Path

When you specify excel file to open you have to ensure that you specify fully qualified path of the file. When you are working Application object, it is an instance of Excel application that has been initialized through COM automation. So when you call Open on the application, its default path will be from where Excel application is running. If you do not specify fully qualified path for Excel file, it will try to find that file in the folder where Excel is installed. So you have two options here.

  1. Specifify fully qualified path for Excel file to open.
  2. Or Set DefaultPath property on Application object to the folder where your Excel files are going to be.

In the code snippet I have shown both methods.

comments powered by Disqus

Blog Tags