Followers

My Visual Studio Achievements

Twitter

Tuesday, July 30, 2013

Office Interop in .Net

Office interop cannot be avoided, if you are developing applications for Windows platform. It is 90% sure that your customers will ask for MS Office integration when the project grows or your marketing people will add office integration regardless the client actually needs or not.

Before Open XML era (docx,xlsx etc...) developers had only one way to interact with office files from .Net or any other applications. Yes the Office interop APIs. In .Net it can be done by Adding reference to the interop assemblies.Only issue I had faced is the non type safe APIs provided. Another issue is the memory leak due to unmanaged objects but that can be avoided by using Marshal.ReleaseComObject method.

If you are working in .Net for 2 years and don't know what is Office interop and how to use it to create an Excel file programatically, just google for it and try at least once. Let me know, if you are not able to get any result in google

Adding formula to Excel cells from C#.Net

Formula in Excel should be known to everyone who uses Windows operating system seriously. It can be added from the Excel application by prefixing a '=' symbol to mathematical or logical expressions when we edit cells. For example if you want to display the sum of values in cells E1 to E9 in E10 cell, write the below into E10 cell

=SUM(E1:E9)

The same can be done from our .Net application as well. Below is an code which adds little more complicated formulae to the cell

This simply fails with a messageA first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dllAdditional information: Exception from HRESULT: 0x800A03EC

Any idea? It doesn't talk about what happened inside the code.Or at least what is the actual error?

This is the time to show the your practical debugging skills. Most of the developers complain here that, its just not working. But the intelligent developers think in a different way. Is it because of long message? Can't I assign long text via interop? or Excel itself just don't accept this much big message?

Most of them ends up in trying out the same scenario in Excel. Technically this is called as "reproducing the issue in a stripped down environment". Whoever try it in Excel will get the actual problem.

Text values in formulas are limited to 255 characters. To create text values longer than 255 characters in a formula, use the CONCATENATE function or the concatenation operator (&)

Yes there is a limitation in Excel and it itself giving the solution. So lets modify our code to have concatenation.