Disposing Excel Application object correctly

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:

public bool UpdateExcelFile(string filePath) { Workbook workbook = null; Worksheet worksheet = null; Range worksheetCells = null; Range usedRange = null; Range cells = null; Range columns = null; var excelApplication = new Application { DisplayAlerts = false }; try { if (!File.Exists(filePath)) throw new ApplicationException("File does not exists"); workbook = Open(excelApplication, filePath); if (workbook == null) throw new ApplicationException("File cannot be opened"); var sheetIndex = GetSheetIndex(workbook, "SOV INPUT"); worksheet = workbook.Sheets[sheetIndex]; worksheetCells = worksheet.Cells; usedRange = worksheet.UsedRange; cells = usedRange.Cells; columns = cells.Columns; // Perform file operations like adding new columns, // set values for new columns return true; } catch (Exception ex) { Logger.ErrorFormat("Error occurred while updating excel file {0} {1} : {2}", filePath, ex.Message, ex.StackTrace); return false; } finally { GC.Collect(); GC.WaitForPendingFinalizers(); if (workbook != null) { if (columns != null) Marshal.ReleaseComObject(columns); if (cells != null) Marshal.ReleaseComObject(cells); if (usedRange != null) Marshal.ReleaseComObject(usedRange); if (worksheetCells != null) Marshal.ReleaseComObject(worksheetCells); if (worksheet != null) Marshal.FinalReleaseComObject(worksheet); workbook.Close(); Marshal.FinalReleaseComObject(workbook); } excelApplication.Quit(); Marshal.FinalReleaseComObject(excelApplication); } }

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!

About these ads

One thought on “Disposing Excel Application object correctly

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s