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 that handle ODS are paid, and even the free versions often come with functional limitations. Therefore, a common solution is to generate an Excel file first and then use Microsoft.Office.Interop.Excel to read it via the Office Excel installed on the host, saving 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 Errors

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 they may use different execution environments, leading to differences 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 lead to successful compilation in Visual Studio while triggering the following error during 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. 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 Procedures

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.
   • Type 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.
   • Under "Launch and Activation Permissions", click "Edit".
   • Add IIS APPPOOL\{Application Pool Name} to the user list.
   • Set access permissions:
     • Local connection: Check "Local Access".
     • External IP connection: Check "Remote Access".

2. Excel File Access Issues

If you receive 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 it manually.

The location for 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.

Important Notes

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

Change Log

• 2024-10-21 Initial version created.