Editing Excel files in AddIn with SpreadsheetLight and OpenXml leads to an System.TypeLoadException error

[AndreasMarth]

Hi all,

I developed an AddIn in C# and ArcGIS-Pro 3.1.2, where a table object gets exported to an excel file via the geoprocessing tool "conversion.TableToExcel". Afterwards I need to add some additional columns to the excel file. On the client there is no Office Interop Excel library available therefor I tried to use
the Open Source tool SpreadsheetLight to edit the exported xslx file. I tested the library in a simple c# Winforms project, where I added the reference to the library via the NuGet manager and in this testproject everything works as expected. But when I added the SpreadsheetLight package to my AddIn project and try to open a xlsx file I get the following error:

"System.TypeLoadException": Could not load type 'DocumentFormat.OpenXml.OpenXmlComparableSimpleReference`1' from assembly 'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'

By the way NuGet packet manager installed SpreadsheetLight version 3.5.0 and DocumentFormat.OpenXml version 2.11.3

Any ideas what causes this error? Or is there a configuration/setting in the AddIn project to fix this error?

Thx in advance for your answers

[GKmieliauskas]

Hi,

Both of yours nuget packages doesn't support .NET 6.0.

You can get back from excel to table using "conversion.ExcelToTable", then add fields to table and back to Excel with "conversion.TableToExcel". To add fields you can use geoprocessing too

Add Fields (multiple) (Data Management)—ArcGIS Pro | Documentation

[AndreasMarth]

Hi Gintautas,

I have to add fields in Excel with formulas and I have to do some conditional formatting afterwards . And that is - as far as I know - not possible with Geoprocessing tools. 

On the other side, I can integrate and use the SpreadsheetLight packages in an standard class library, which uses .NET6.0 as a target framework without any errors. Seems the .NET 6.0 is not the reason for this error.  

[AzharIbrahim]

ArcGIS Pro comes with it's own version of DocumentFormat.OpenXml (the 2.0.5022.0 version). If you can find the spreadsheet light version that uses the same version as ArcGIS Pro, it may work, but I haven't tested it. We dropped what we were trying to do and just used the version that comes with ArcGIS Pro.

[AndreasMarth]

Hi Azharlbrahim,

PREVIEW

thanks for your response, that was also one of my first ideas what causes this error. Therefor I tried to create a c# class library only with the SpreadsheetLight components inside and use this library for the excel formating. As far as I understand, this additionals class library can not reference the ArcGIS-Pro libraries, but uses the libraries integrated in the Class DLL. Unfortunately this leads to the same error.

PREVIEW

[AzharIbrahim · Accepted solution]

Here's a link to my issue with ClosedXML using DocumentFormat.OpenXml: Solved: ClosedXML in Addin - Esri Community

Referencing the ArcGIS version of DocumentFormat.OpenXML and not including it anywhere else solved the issue. I did have to drop back several versions of ClosedXml to get it working. 

[AndreasMarth]

I think you're right. So unfortunately there is no solution to my problem, because I need a current version of SpreadSheetLight. It looks like there is no way to use components that use a different version of DocumentFormat.OpenXML in ArcGIS Pro than the one ArcGIS Pro implements. Thanks