Common issues when using Microsoft.Office.Interop.Excel in .NET projects

I've been swamped with overtime lately and only just found time to catch up on my notes. I'm a bit too lazy to take screenshots, though.

Many government agencies require Excel export features to support the ODS format as well. In the .NET ecosystem, most packages for handling ODS are paid, and even free versions often have functional limitations. Therefore, a common solution is to generate an Excel file first, then use Microsoft.Office.Interop.Excel to read it via the Office Excel installed on the host and save the file as an ODS format.

However, while this approach might work fine during local development, it often encounters issues after deployment to a server environment. Below are some of the problems I have encountered.

dotnet publish compilation exceptions

Due to the nature of COM components, this approach can only be used in a Windows environment. Visual Studio's publish feature and the dotnet publish command are both based on MSBuild, but the execution environments they use may differ, leading to discrepancies in behavior and output. Visual Studio uses the MSBuild installed within Visual Studio, while dotnet publish uses the MSBuild version included with the .NET SDK. Combined with potential differences in project settings and parameter handling, this can cause the project to compile successfully in Visual Studio but throw the following error when using dotnet publish:

error MSB4803: The .NET Core version of MSBuild does not support the task "ResolveComReference". Please use the .NET Framework version of MSBuild.

I haven't found a solution for this issue. My current approach is to create a .NET Framework Web API. When the system needs to convert a file to ODS format, it calls this dedicated API service to handle the conversion and return the result.

Runtime error handling process

1. Insufficient DCOM component permissions

If you encounter the following error message during execution:

System.UnauthorizedAccessException: Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (0x80070005 (E_ACCESSDENIED)).

This means you need to configure DCOM permissions: In a Windows Server environment, access to COM components is managed by the DCOM (Distributed COM) security mechanism. To allow the IIS application to control Excel, we need to explicitly grant access permissions:

1. Open the DCOM configuration tool:

   • Press Windows + R.
   • Enter dcomcnfg.
   • Expand in order: Component Services > Computers > My Computer > DCOM Config.

2. Configure Microsoft Excel Application:

   • Find "Microsoft Excel Application" in the list:
   • Right-click and select "Properties".
   • Switch to the "Security" tab.
   • In the "Launch and Activation Permissions" section, click "Customize".
   • Add IIS APPPOOL\{Application Pool Name} to the user list.
   • Set access permissions:
     • Localhost connection: Check Local Access.
     • External IP connection: Check Remote Access.

2. Excel file access issues

If you encounter an error message stating that the Excel file cannot be found during execution, it is because the Excel COM component attempts to access the user's desktop path at runtime. In a Windows Server environment, system service accounts do not have a Desktop folder by default, so we need to create one manually.

The location for creating the Desktop folder depends on the Microsoft Office version:

• 32-bit Office: C:\Windows\SysWOW64\config\systemprofile\Desktop.
• 64-bit Office: C:\Windows\System32\config\systemprofile\Desktop.

Notes

1. This configuration is only applicable to Windows environments.
2. Microsoft Office must be installed on the server.
3. Permission settings may need to be re-checked after every Windows update.

Change Log

• 2024-10-21 Initial documentation created.