0

SharePoint 2013 server, I'm site collection administrator.

I've copied an Excel file into documentlibrary, I like to read the excel file and update custom list with the Excel values.

I've got the connnectionstring from: https://www.connectionstrings.com/sharepoint/

When I do con.Open() I get permission error. The Visual Webpart is a sandbox solution and I try to read in the same site...

string Connection = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=http://sharepoint/scp/Files/;LIST={6400FE4B-1681-46DE-9CDF-4DDACE38032A}";
        OleDbConnection con = new OleDbConnection(Connection);
        con.Open();
        DataTable sheetsName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

        foreach (DataRow rSheet in sheetsName.Rows)

I receive the following error:

{"Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed."}

A first chance exception of type 'System.Security.SecurityException' occurred in System.Data.dll

Additional information: Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Please advice how can I read Excel file in a sandboxed solution

  • 1
    I don't think this is supported in sandbox solution. You would need to switch to farm solution or SharePoint add in. – Nisarg Shah Apr 7 '16 at 12:08
3

APPROACH 1

First add reference of two COM components.

  1. Microsoft Office Object Library

  2. Microsoft Excel Object Library

    excel = new ApplicationClass();
    excel.Visible = false;
    string workbookPath = url;
    Workbook excelWorkbook = excel.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);
    Sheets sheets = excelWorkbook.Worksheets;
    Worksheet worksheet = (Worksheet)sheets.get_Item(1);
    bool flag = false; ;
    for (int i = 1; i <= worksheet.Rows.Count; i++)
    {
             Range range = worksheet.get_Range("A" + i.ToString(), "I" + i.ToString());
            Array myvalues = (System.Array)range.Cells.Value2;
            string[] strArray = ConvertToStringArray(myvalues);
     }
    

APPROACH 2 - using OpenXML -

SPFile file = web.GetFile("http://servername:5454/ExcelDocuments//ExcelFilename.xlsx");
Stream dataStream = file.OpenBinaryStream();
SpreadsheetDocument document = SpreadsheetDocument.Open(dataStream, false);
Workbook workbook = document.WorkbookPart.Workbook;

you can find the complete implementation example using link

APPROACH 3 - Using REST API/ Excel services

Use this link

I'm not sure about the above approaches in Sandboxed solutions, have a try, happy coding :)

  • OpenXML should work in Sandbox solution, at least since 2.0 afaik – Anders Aune Apr 7 '16 at 12:49
  • ok I had to refer also to "WindowsBase .Net library." in reference. – user3080110 Apr 7 '16 at 13:03
  • @ ALL : I tried 2nd approach and SpreadsheetDocument.Open(...) gives error: {"File contains corrupted data."} Source: WindowsBase.... What could be wrong ? – user3080110 Apr 7 '16 at 13:11
  • This is the all code: error on -> .OPEN(....) SPWeb web = SPContext.Current.Web; SPFile file2 = web.GetFile("sharepoint/scp/Shared%20Documents/Busines%20Objects%20files/…); MemoryStream mStream = new MemoryStream(file.OpenBinary()); Stream dataStream = mStream; SpreadsheetDocument document = SpreadsheetDocument.Open(dataStream, false); Workbook workbook = document.WorkbookPart.Workbook; – user3080110 Apr 7 '16 at 13:13

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.