This project has moved and is read-only. For the latest updates, please go here.

Sharepoint 2010 & ClosedXML sdk. Reading from library

May 27, 2014 at 12:16 PM
Edited May 27, 2014 at 12:35 PM
I am running MS Sharepoint Server 2010 and apparently Ive been stuck with probably dumb issue:

All I want is to take an instance of the xlsx file from document library, edit it with ClosedXML and save back as a new copy of a file. (Actually I've done this before with OpenXML but it takes too much time and implementation)

So, the constructor of XLWorkbook class doesnt want to take a Stream object, which I obtain from SPFile. Once the compilation is done, it shows me "Error Occurred in Workflow1". The filepath constructor that takes string doesnt work as well.

This is the code:
using (SPSite sourceSite = new SPSite(workflowProperties.SiteUrl))
            {
                SPFile sourceFile = sourceSite.RootWeb.GetFile(fileSample);

                using (SPSite destinationSite = new SPSite(destinationSitePath))
                {
                    using (SPWeb web = destinationSite.OpenWeb())
                    {
                        if (sourceFile != null)
                        {
SPFolder destinationDocumentLibrary = web.Folders[destinationDocumentLibraryName];
                            
using (Stream sourceFileStream = sourceFile.OpenBinaryStream())
                           {

var wb = new XLWorkbook(sourceFileStream); //this doesnt work

//var ws = wb.worksheets.add("contacts"); ...etc....
                      
destinationDocumentLibrary.Files.Add("created.xlsx", sourceFileStream, true);//this is //fine
                          
                            }

                        }
                    }
                }
            }
Please, could yout tell me some ideas or walkarounds of how to load the Sharepoint file to apply ClosedXML methods?

Thanks & Regards
May 27, 2014 at 2:08 PM
If it can't compile then it's because sourceFile.OpenBinaryStream() doesn't return a System.IO.Stream.
May 27, 2014 at 7:10 PM
Thanks for reply,

Actually sourceFile.OpenBinaryStream() does return a System.IO.Stream. The compilation is done properly, however the thing is when I run the workflow from the web interface, I certainly get a workflow error in an Item status. I'm pretty sure the issue is in this,
var wb = new XLWorkbook(sourceFileStream);
because when I just have:
var wb = new XLWorkbook();
the workflow shows no errors and eventually completes. But this isnt good for me since I need to read the file from http (sharepoint webapp) not just having an empty instance of XLWorkbook.

Appreciate your support, and still hoping to hear from you
:)
May 28, 2014 at 6:32 AM
Edited May 28, 2014 at 10:03 AM
I have fixed this,

It was giving me the following exception:
 System.Security.SecurityException: Request for the permission of type 'Microsoft.SharePoint.Security.SharePointPermission, Microsoft.SharePoint.Security, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' failed.
   at Microsoft.SharePoint.SPFileStream.get_CanWrite()
   at System.IO.Packaging.Package.ValidateModeAndAccess(Stream s, FileMode mode, FileAccess access)
   at System.IO.Packaging.Package.Open(Stream stream, FileMode packageMode, FileAccess packageAccess, Boolean streaming)
   at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.OpenCore(Stream stream, Boolean readWriteMode)
   at DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(Stream stream, Boolean isEditable, OpenSettings openSettings)
   at ClosedXML.Excel.XLWorkbook.LoadSheets(Stream stream)
   at NDTMonthlyReport2014.Workflow1.Workflow1.ExecuteWorkflow_ExecuteCode(Object sender, EventArgs e)
The action that failed was:
Demand
The type of the first permission that failed was:
Microsoft.SharePoint.Security.SharePointPermission
The first permission that failed was:
<IPermission class="Microsoft.SharePoint.Security.SharePointPermission, Microsoft.SharePoint.Security, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"
version="1"
ObjectModel="True"/>

The demand was for:
<PermissionSet class="System.Security.PermissionSet"
version="1">
<IPermission class="Microsoft.SharePoint.Security.SharePointPermission, Microsoft.SharePoint.Security, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"
version="1"
ObjectModel="True"/>
</PermissionSet>

The granted set of the failing assembly was:
<PermissionSet class="System.Security.PermissionSet"
version="1">
<IPermission class="System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Flags="Execution"/>
<IPermission class="System.Security.Permissions.StrongNameIdentityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100A1FB8BA59167FE734D64128CA73D32C45CB8A117246D09C95C8769DB88FE332B0A3396BEDD0EA48EE42B0E5796FEC0798CA5CB628A9A6DE80D35D6C67B936CA1670347B3D4F2B769C8CE2DDCF959DBAC6BCD88E6C08751EA1FFFA0522DE3507193E7035305A8AA008D6C88CCA1341B3120FA9C347AB3F97E2D772E2709277DA5"
Name="ClosedXML"
AssemblyVersion="0.65.2.0"/>
<IPermission class="System.Security.Permissions.UrlIdentityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Url="file:///C:/inetpub/wwwroot/wss/VirtualDirectories/80/bin/ClosedXML.DLL"/>
<IPermission class="System.Security.Permissions.ZoneIdentityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Zone="MyComputer"/>
<IPermission class="System.Web.AspNetHostingPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
Level="Minimal"/>
<IPermission class="Microsoft.SharePoint.Security.WebPartPermission, Microsoft.SharePoint.Security, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"
version="1"
Connections="True"/>
</PermissionSet>

The assembly or AppDomain that failed was:
ClosedXML, Version=0.65.2.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b
The method that caused the failure was:
Void LoadSheets(System.IO.Stream)
The Zone of the assembly that failed was:
MyComputer
The Url of the assembly that failed was:
file:///C:/inetpub/wwwroot/wss/VirtualDirectories/80/bin/ClosedXML.DLL
!!!
So I gotta change the assembly properties: Check the GlobalAssemblyCache checkbox (instead of WebApplication) in advanced properties of Solution package, ie Package.package file.

Everything is working fine now

Thanks!