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

526
6
Jump to solution
11-29-2023 09:37 AM
AndreasMarth
New Contributor II

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

0 Kudos
1 Solution

Accepted Solutions
AzharIbrahim
New Contributor III

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. 

View solution in original post

0 Kudos
6 Replies
GKmieliauskas
Esri Regular Contributor

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

0 Kudos
AndreasMarth
New Contributor II

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.  

0 Kudos
AzharIbrahim
New Contributor III

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.

0 Kudos
AndreasMarth
New Contributor II

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.
 
 
0 Kudos
AzharIbrahim
New Contributor III

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. 

0 Kudos
AndreasMarth
New Contributor II

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

0 Kudos