Programmatically read Excel file in SharePoint 2013 sandbox solution gives permission error

Question

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

Answer 1

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 :)