How to ensure that the OpenXml assembly doesn't cause a conflict with SpreadsheetLight?

Question

I Nugot SpreadsheetLight. To subsequently use it, I need to add the following usings:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using SpreadsheetLight;

For the first two ("DocumentFormat") to be recognized, I needed to also NuGet Microsoft's "Open XML Format SDK"

I got the latest version of that, 2.5

However, even then, I got an err msg about needing a reference to it:

The type 'DocumentFormat.OpenXml.Spreadsheet.InlineString' is defined in an assembly that is not referenced. You must add a reference to assembly 'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.

This line of SpreadsheetLight code provoked that msg:

sl.SetCellValue("A1", true); // "sl" is an SLDocument

So, I removed the reference that I had NuGot (version 2.6.0.0, Runtime Version v4.0.30319) from my project, and then added back the reference by browsing to C:\Program Files(x86)\Open XML SDK\V2.0\lib and selecting "DocumentFormat.OpenXml.dll"

I then got a compiler Warning:

Found conflicts between different versions of the same dependent assembly. Please set the "AutoGenerateBindingRedirects" property to true in the project file. For more information, see http://go.microsoft.com/fwlink/?LinkId=294190.

I noticed that the DLL I added from the file system was version 2.5.5631.0, and the one that had been NuGot and installed as a reference that way was version 2.6.0.0 The Runtime Version was different, too (v4.0.30319 was installed by NuGetting "Open XML Format SDK", but the version of the DLL I manually added is 2.5.5631.0, Runtime Version v4.0.30319

According to this, I gathered that I should edit the .csproj file by changing <AutoGenerateBindingRedirects>false</AutoGenerateBindingRedirects> to true - but AutoGenerateBindingRedirects does not exist there.

I don't know whether I should add it, and if so (in which "block"). I prefer to play it safe and assuage the Warning engine. How can I ensure that the OpenXml assembly doesn't cause a conflict?

Answer 1

Assuaging that Warning (so that it rides off into the sunset) is a matter of downgrading the version of DocumentFormat.OpenXML to Version 2.0.5022.0 (Runtime Version v2.0.50727)

I found this out because this code from the "Hello World" example here.

SLDocument sl = new SLDocument();
sl.SetCellValue("A1", true);
. . .

...failed on the first line with, "Could not load file or assembly 'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies"

And so, since it's expecting version 2.0, I removed my 2.5.5631.0 of that file and then NuGot "OpenXML SDK 2.0" in its stead. That is Version 2.0.5022.0 and Runtime Version v2.0.50727

So: No need to update the project file with an arcane boolean property after all.

It kind of gives me the fantods, though, to have to use an older version of an assembly.

UPDATE

The need to "go retro" with DocumentFormat.OpenXml is corroborated here.

Answer 2

One can solve the problem by redirection of DocumentFormat.OpenXml from Version 2.0.5022.0 to more recent version, for example to version 2.5.5631.0. To do this one should add in web.config the new <dependentAssembly> item:

<configuration>
  ...
  <runtime>
      <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
         ...
         <dependentAssembly>
            <assemblyIdentity name="DocumentFormat.OpenXml" publicKeyToken="31bf3856ad364e35"/>
            <bindingRedirect oldVersion="1.0.0.0-2.0.5022.0" newVersion="2.5.5631.0"/>
         </dependentAssembly>
      </assemblyBinding>
  </runtime>
  ...
<configuration>

Answer 3

Spreadsheetlight works with DocumentFormat.OpenXml 2.5 since version 3.4.5:

"Version 3.4.5 - SmartTags is now removed from consideration (not so smart now, are you? ;). Which means the code is now ready for Open XML SDK 2.5! And yes, it now works with Open XML SDK 2.5 (have I mentioned that? lol)"

quote from: https://www.nuget.org/packages/SpreadsheetLight/