Read excel file stored in SharePoint Document Library

1
votes

I have an excel file stored in SharePoint document library. I need to read this excel file and get the data programmatically from the file path

For Example: SharePoint Site: http:// servername :1000

Excel file path :http://servername:1000/ExcelDocs//ExcelFile.xlsx

As we see I have path to excel file stored in SharePoint document library. I need to get data from this file. Any ideas?

Thanks

2
c#sharepoint-2010
What have you done so far? - Dmitry Dovgopoly
I have stored the excel file from the url into a temporary folder inside SharePoint root directory(layouts folder) but for some reasons the temp folder is not create(access denied). I need a method to directly get the data from the file url without having to store in temp location - sharmila

2 Answers

5
votes

In fact, you could read Excel file content without any third-party components involved, using SharePoint capabilities only.

The solution demonstrates how to consume SharePoint REST service to read excel data. Another point about this approach, since there is no any dependencies to SharePoint libraries at all, neither server side nor client side assemblies are referenced.

Prerequisite: Excel Services service application has to be configured

Prepare excel data

Assume the following excel file

enter image description here

that contains the following items in the workbook:

Before uploading file into SharePoint library, go to File -> Browse View Options -> choose Items in the Workbook as demonstrated below. Then save file and upload it into SharePoint Documents library.

enter image description here

How to read excel data via Excel Services 2010 REST API

The following example demonstrates how to read excel table content using Excel Services 2010 REST API:

using System;
using System.Net;

namespace SharePoint.Client.Office
{
    public class ExcelClient : IDisposable
    {

        public ExcelClient(Uri webUri, ICredentials credentials)
        {
            WebUri = webUri;
            _client = new WebClient {Credentials = credentials};
        }


        public string ReadTable(string libraryName,string fileName, string tableName,string formatType)
        {
            var endpointUrl = string.Format("{0}/_vti_bin/ExcelRest.aspx/{1}/{2}/Model/Tables('{3}')?$format={4}", WebUri,libraryName,fileName, tableName, formatType);
            return _client.DownloadString(endpointUrl);
        }


        public void Dispose()
        {
            _client.Dispose();
            GC.SuppressFinalize(this);
        }

        public Uri WebUri { get; private set; }

        private readonly WebClient _client;

    }
}

Usage

The example demonstrates how to read table content using JSON format: 

var credentials = new NetworkCredential(userName,password,domain);  
var client = new ExcelClient(webUri, credentials);
var tableData = client.ReadTable("Documents","ciscoexpo.xlsx", "CiscoSalesTable","html");

References

1
votes

You can obtain the binary data from SPFile object and then open it in third party library ClosedXML

SPFile file = web.GetFile("http://servername:1000/ExcelDocs//ExcelFile.xlsx");
Stream dataStream = file.OpenBinaryStream();
XLWorkbook workbook = new XLWorkbook(dataStream);

or you can use OpenXML, which is Microsoft SDK. 

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

Here is the example of using OpenXML