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:
As shown in solution: (Check ‘finally’ section)
You need to explicitly release reference for COM objects that you have created like
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.