On one of our recent project, we need to process lots of Excel files in windows service. Basic requirement was: we need to read excel file from specific folders, perform some operations, do additional calculations on data which we read from excel file and then write some additional data to existing excel file.

We started with using OLEDB as first and safe choice. But issue with OLEDB is, you cannot alter existing file structure. That means in our case we cannot add new columns to the existing excel file. So, we left OLEDB option.

Now, the only solution available is to use Excel Application object which is part of Office Interop assemblies. Using this you can modify existing Excel files.

But, issue with this is: even though you execute code ‘Quit()’ method on Excel Application object, .Net does not dispose that Excel Application object. It will remain there in memory until you close your application. In our application, we are handling excel processing through windows service, so if we process 10 files then 10 excel objects will be created and they will reside in the memory. One can see those objects in Task Manager. Those objects will remain there until we stop Windows Service which creates these objects.

This is scary because we can’t stop Windows Service. If we don’t stop Windows Service then Excel objects will pile in the memory and at some point application will throw insufficient memory exception.

Another thing that surprised us is Microsoft suggests not to use Office objects in Automation. Here is what they say “Developers can use Automation in Microsoft Office to build custom solutions that use the capabilities and the features that are built into the Office product. Although such programmatic development can be implemented on a client system with relative ease, a number of complications can occur if Automation takes place from server-side code such as Microsoft Active Server Pages (ASP), ASP.NET, DCOM, or a Windows NT service.” in article http://support.microsoft.com/kb/257757

But for us using Excel Application object is the only option, so finally we come with following solution to dispose Excel Application object:

   1: public bool UpdateExcelFile(string filePath)

   2: {

   3:     Workbook workbook = null;

   4:     Worksheet worksheet = null;

   5:     Range worksheetCells = null;

   6:     Range usedRange = null;

   7:     Range cells = null;

   8:     Range columns = null;

   9:  

  10:     var excelApplication = new Application { DisplayAlerts = false };

  11:     

  12:     try

  13:     {

  14:         if (!File.Exists(filePath))

  15:             throw new ApplicationException("File does not exists");

  16:  

  17:         workbook = Open(excelApplication, filePath);

  18:         if (workbook == null)

  19:             throw new ApplicationException("File cannot be opened");

  20:  

  21:         var sheetIndex = GetSheetIndex(workbook, "SOV INPUT");

  22:         worksheet = workbook.Sheets[sheetIndex];

  23:         worksheetCells = worksheet.Cells;

  24:         usedRange = worksheet.UsedRange;

  25:         cells = usedRange.Cells;

  26:         columns = cells.Columns;

  27:  

  28:         // Perform file operations like adding new columns, 

  29:         // set values for new columns

  30:         

  31:         return true;

  32:     }

  33:     catch (Exception ex)

  34:     {

  35:         Logger.ErrorFormat("Error occurred while updating excel file {0} {1} : {2}", filePath, ex.Message, ex.StackTrace);

  36:         return false;

  37:     }

  38:     finally

  39:     {

  40:         GC.Collect();

  41:         GC.WaitForPendingFinalizers();

  42:         if (workbook != null)

  43:         {

  44:             if (columns != null) Marshal.ReleaseComObject(columns);

  45:             if (cells != null) Marshal.ReleaseComObject(cells);

  46:             if (usedRange != null) Marshal.ReleaseComObject(usedRange);

  47:             if (worksheetCells != null) Marshal.ReleaseComObject(worksheetCells);

  48:             if (worksheet != null) Marshal.FinalReleaseComObject(worksheet);

  49:             workbook.Close();

  50:             Marshal.FinalReleaseComObject(workbook);

  51:         }

  52:  

  53:         excelApplication.Quit();

  54:         Marshal.FinalReleaseComObject(excelApplication);

  55:     }

  56: }

As shown in solution: (Check ‘finally’ section)

• You need to explicitly release reference for COM objects that you have created like
  1. Workbook
  2. Worksheet
  3. Worksheet cells

Only then ‘Quit()’ method on Excel Application object will dispose Excel Application object immediately.

Now, we can use Excel Application object safely within Windows Service. Thank you to my team member Selva for doing research on this.

Happy Programming!