读取存储在 SharePoint 文档库中的 Excel 文件 [英] Read excel file stored in SharePoint Document Library

问题描述

我在 SharePoint 文档库中存储了一个 Excel 文件.我需要读取这个excel文件并从文件路径中以编程方式获取数据

例如:SharePoint 站点:http://servername :1000

Excel 文件路径:http://servername:1000/ExcelDocs//ExcelFile.xlsx

正如我们所见，我有存储在 SharePoint 文档库中的 Excel 文件的路径.我需要从这个文件中获取数据.有什么想法吗?

谢谢

解决方案

事实上，您可以在不涉及任何第三方组件的情况下读取 Excel 文件内容，仅使用 SharePoint 功能.

该解决方案演示了如何使用 SharePoint REST 服务读取 Excel 数据.关于此方法的另一点是，由于根本没有对 SharePoint 库的任何依赖，因此既没有引用服务器端也没有引用客户端程序集.

<块引用>

先决条件:)

图表

在将文件上传到 SharePoint 库之前，转到 File ->浏览视图选项 ->选择工作簿中的项目，如下所示.然后保存文件并将其上传到 SharePoint Documents 库.

如何通过 Excel Services 2010 REST API 读取 Excel 数据

以下示例演示如何使用 Excel 服务读取 excel 表格内容2010 REST API:

使用系统；使用 System.Net；命名空间 SharePoint.Client.Office{公共类 ExcelClient : IDisposable{公共 ExcelClient(Uri webUri，ICredentials 凭证){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, 表名, 格式类型);返回 _client.DownloadString(endpointUrl);}公共无效处置(){_client.Dispose();GC.SuppressFinalize(this);}公共 Uri WebUri { get;私人订制；}私有只读 WebClient _client;}}

使用

示例演示了如何使用 JSON 格式读取表格内容:

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

参考文献

• 使用 Excel Services 2010 RESTAPI
• 在工作表中创建或删除 Excel 表格

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

解决方案

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

that contains the following items in the workbook:

• table (how to Create or delete an Excel table in a worksheet)
• chart

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.

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

• Using the Excel Services 2010 REST API
• Create or delete an Excel table in a worksheet

这篇关于读取存储在 SharePoint 文档库中的 Excel 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！