7

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?

B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862

4 Answers4

5

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.

B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862
  • 2
    I was wrestling with this yesterday. I did try a few different versions so I think your answer is, in fact, correct -- downgrade to non-latest. But, it did not work for me until I modified my app.config file too. It had a dependentAssembly element that mapped DocumentFormat.OpenXml versions 0.0.0.0-2.5.5631.0 to 2.5.5631.0. I assume that got added by one of the various nuget operations I did. After removing that element, my code then worked. (BTW fantods, nugot and assuaging are great words!) – steve Apr 28 '16 at 15:57
  • "Fantods" is from Twain; can't recall if it's in "Tom Sawyer" or "Huck Finn" or both. – B. Clay Shannon-B. Crow Raven Jul 28 '16 at 17:58
4

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>
Oleg
  • 220,925
  • 34
  • 403
  • 798
  • That saddly does not solve the problem :( when generating the file it throws errors in some commands, for me it fails when doing "myExcelFile.SaveAs(", the only solution I found is to "go retro" and work with OpenXML SDK 2.0 like B. clay did. – JCO9 Mar 30 '17 at 14:38
  • @JCO9: Are you sure that you tried with the correct 32/64-bit assembly? What error exactly you get? Which .Net runtime is installed? Do you tried to use [Fusion Log Viewer](https://msdn.microsoft.com/en-us/library/e74a18c4(v=vs.110).aspx) to debug the problem? One can have different reason of the problem. Debugging isn't simple. – Oleg Mar 30 '17 at 14:53
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/

Millstream30
  • 687
  • 6
  • 8
  • 1
    For future searchers who are using NuGet (which only goes up to SpreadsheetLight v3.4.11), ONLY DocumentFormat.OpenXml 2.5 works. I tried all later versions of DocumentFormat.OpenXml and all failed with the same error. – spork Nov 13 '19 at 16:49
0

As of 2023, this issue seems to be resolved with SpreadsheetLight 3.5.0 and DocumentFormat.OpenXml 2.20.0

Edit: while it compiles, it produces corrupt .XLSX files. Back to DocumentFormat.OpenXml 2.5.0!

Vbguru
  • 31
  • 3