使用 Google Data API 使用 C# 访问 Google 电子表格

Question

提问by blitzkriegz

I'm having some information in Google Spreadsheets as a single sheet. Is there any way by which I can read this information from .NET by providing the google credentials and spreadsheet address. Is it possible using Google Data APIs. Ultimately I need to get the information from Google spreadsheet in a DataTable. How can I do it? If anyone has attempted it, pls share some information.

我在 Google 电子表格中将一些信息作为单个工作表。有什么方法可以通过提供 google 凭据和电子表格地址从 .NET 读取此信息。是否可以使用 Google 数据 API。最终，我需要从 DataTable 中的 Google 电子表格中获取信息。我该怎么做？如果有人尝试过，请分享一些信息。

Answer 1

采纳答案by Kelly

According to the .NET user guide:

根据.NET 用户指南：

Download the .NET client library:

下载.NET 客户端库：

Add these using statements:

添加这些 using 语句：

using Google.GData.Client;
using Google.GData.Extensions;
using Google.GData.Spreadsheets;

Authenticate:

认证：

SpreadsheetsService myService = new SpreadsheetsService("exampleCo-exampleApp-1");
myService.setUserCredentials("[email protected]", "mypassword");

Get a list of spreadsheets:

获取电子表格列表：

SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = myService.Query(query);

Console.WriteLine("Your spreadsheets: ");
foreach (SpreadsheetEntry entry in feed.Entries)
{
    Console.WriteLine(entry.Title.Text);
}

Given a SpreadsheetEntry you've already retrieved, you can get a list of all worksheets in this spreadsheet as follows:

给定您已经检索到的电子表格条目，您可以获得此电子表格中所有工作表的列表，如下所示：

AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);

WorksheetQuery query = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed feed = service.Query(query);

foreach (WorksheetEntry worksheet in feed.Entries)
{
    Console.WriteLine(worksheet.Title.Text);
}

And get a cell based feed:

并获取基于单元格的提要：

AtomLink cellFeedLink = worksheetentry.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);

CellQuery query = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed feed = service.Query(query);

Console.WriteLine("Cells in this worksheet:");
foreach (CellEntry curCell in feed.Entries)
{
    Console.WriteLine("Row {0}, column {1}: {2}", curCell.Cell.Row,
        curCell.Cell.Column, curCell.Cell.Value);
}

Answer 2

回答by Steve

I'm pretty sure there'll be some C# SDKs / toolkits on Google Code for this. I found this one, but there may be others so it's worth having a browse around.

我很确定谷歌代码上会有一些 C# SDK/工具包。我找到了这个，但可能还有其他的，所以值得浏览一下。

Answer 3

回答by Tacoman667

http://code.google.com/apis/gdata/articles/dotnet_client_lib.html

This should get you started. I haven't played with it lately but I downloaded a very old version a while back and it seemed pretty solid. This one is updated to Visual Studio 2008 as well so check out the docs!

这应该让你开始。我最近没有玩过它，但不久前我下载了一个非常旧的版本，它看起来很可靠。这个也更新到了 Visual Studio 2008，所以请查看文档！

Answer 4

回答by tonys

You can do what you're asking several ways:

您可以通过以下几种方式完成您的要求：

Using Google's spreadsheet C# library (as in Tacoman667's answer) to fetch a ListFeed which can return a list of rows (ListEntry in Google parlance) each of which has a list of name-value pairs. The Google spreadsheet API (http://code.google.com/apis/spreadsheets/code.html) documentation has more than enough information to get you started.
Using the Google visualization API which lets you submit more sophisticated (almost like SQL) queries to fetch only the rows/columns you require.
The spreadsheet contents are returned as Atom feeds so you can use XPath or SAX parsing to extract the contents of a list feed. There is an example of doing it this way (in Java and Javascript only though I'm afraid) at http://gqlx.twyst.co.za.

使用 Google 的电子表格 C# 库（如 Tacoman667 的回答）来获取一个 ListFeed，它可以返回一个行列表（Google 用语是 ListEntry），每个行都有一个名称-值对列表。Google 电子表格 API ( http://code.google.com/apis/spreadsheets/code.html) 文档提供的信息足以让您入门。
使用 Google 可视化 API，您可以提交更复杂的（几乎类似于 SQL）查询以仅获取所需的行/列。
电子表格内容作为 Atom 提要返回，因此您可以使用 XPath 或 SAX 解析来提取列表提要的内容。在http://gqlx.twyst.co.za有一个这样做的例子（虽然我很害怕，但只有在 Java 和 Javascript 中）。

Answer 5

回答by Mauricio Scheffer

I wrote a simple wrapperaround Google's .Net client library, it exposes a simpler database-like interface, with strongly-typed record types. Here's some sample code:

我围绕Google 的 .Net 客户端库编写了一个简单的包装器，它公开了一个更简单的类似数据库的界面，具有强类型记录类型。这是一些示例代码：

public class Entity {
    public int IntProp { get; set; }
    public string StringProp { get; set; }
}

var e1 = new Entity { IntProp = 2 };
var e2 = new Entity { StringProp = "hello" };
var client = new DatabaseClient("[email protected]", "password");
const string dbName = "IntegrationTests";
Console.WriteLine("Opening or creating database");
db = client.GetDatabase(dbName) ?? client.CreateDatabase(dbName); // databases are spreadsheets
const string tableName = "IntegrationTests";
Console.WriteLine("Opening or creating table");
table = db.GetTable<Entity>(tableName) ?? db.CreateTable<Entity>(tableName); // tables are worksheets
table.DeleteAll();
table.Add(e1);
table.Add(e2);
var r1 = table.Get(1);

There's also a LINQ provider that translates to google's structured query operators:

还有一个 LINQ 提供程序可以转换为 google 的结构化查询运算符：

var q = from r in table.AsQueryable()
        where r.IntProp > -1000 && r.StringProp == "hello"
        orderby r.IntProp
        select r;

Answer 6

回答by wescpy

(Jun-Nov 2016)The question and its answers are now out-of-date as: 1) GData APIsare the previous generation of Google APIs. While not all GData APIs have been deprecated, all the latest Google APIsdo notuse the Google Data Protocol; and 2) there is a new Google Sheets API v4(also not GData).

（2016 年 6 月至 11 月）该问题及其答案现已过时，因为：1) GData API是上一代 Google API。虽然不是所有的GData API与已弃用，所有最新的谷歌的API都没有使用谷歌数据协议; 2) 有一个新的 Google Sheets API v4（也不是 GData）。

Moving forward from here, you need to get the Google APIs Client Library for .NETand use the latest Sheets API, which is much more powerful and flexible than any previous API. Here's a C# code sampleto help get you started. Also check the .NET reference docs for the Sheets APIand the .NET Google APIs Client Library developers guide.

从这里开始，您需要获取适用于 .NET 的 Google API 客户端库并使用最新的Sheets API，它比任何以前的 API 都更加强大和灵活。这是帮助您入门的C# 代码示例。另请查看Sheets API的.NET 参考文档和.NET Google API 客户端库开发人员指南。

If you're not allergic to Python (if you are, just pretend it's pseudocode ;) ), I made several videos with slightly longer, more "real-world" examples of using the API you can learn from and migrate to C# if desired:

如果您对 Python 不过敏（如果您是，请假装它是伪代码；）），我制作了几个视频，其中包含更长、更“真实”的 API 使用示例，您可以从中学习并迁移到 C#（如果需要）：

Migrating SQL data to a Sheet(code deep dive post)
Formatting text using the Sheets API(code deep dive post)
Generating slides from spreadsheet data(code deep dive post)
Those and others in the Sheets API video library

Answer 7

回答by JohnH

This Twilio blog page made on March 24, 2017 by Marcos Placona may be helpful.

这个由 Marcos Placona 于 2017 年 3 月 24 日创建的 Twilio 博客页面可能会有所帮助。

Google Spreadsheets and .NET Core

Google 电子表格和 .NET Core

It references Google.Api.Sheets.v4and OAuth2.

它引用了Google.Api.Sheets.v4和OAuth2。

Answer 8

回答by Ogglas

The most upvoted answer from @Kelly is no longer valid as @wescpy says. However after 2020-03-03 it will not work at all since the library used uses Google Sheets v3 API.

正如@wescpy 所说，@Kelly 最受好评的答案不再有效。但是在 2020-03-03 之后它根本无法工作，因为使用的库使用Google Sheets v3 API.

The Google Sheets v3 API will be shut down on March 3, 2020

Google Sheets v3 API 将于 2020 年 3 月 3 日关闭

https://developers.google.com/sheets/api/v3

This was announced 2019-09-10 by Google:

这是谷歌在 2019-09-10 宣布的：

https://cloud.google.com/blog/products/g-suite/migrate-your-apps-use-latest-sheets-api

New code sample for Google Sheets v4 API:

的新代码示例Google Sheets v4 API：

Go to

去

https://developers.google.com/sheets/api/quickstart/dotnet

and generate credentials.json. Then install Google.Apis.Sheets.v4NuGet and try the following sample:

并生成credentials.json. 然后安装Google.Apis.Sheets.v4NuGet 并尝试以下示例：

Note that I got the error Unable to parse range: Class Data!A2:Ewith the example code but with my spreadsheet. Changing to Sheet1!A2:Eworked however since my sheet was named that. Also worked with only A2:E.

请注意，我Unable to parse range: Class Data!A2:E的示例代码出现了错误，但我的电子表格出现了错误。更改为Sheet1!A2:E工作但是因为我的工作表被命名为。也只与A2:E.

using Google.Apis.Auth.OAuth2;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Services;
using Google.Apis.Util.Store;
using System;
using System.Collections.Generic;
using System.IO;
using System.Threading;

namespace SheetsQuickstart
{
    class Program
    {
        // If modifying these scopes, delete your previously saved credentials
        // at ~/.credentials/sheets.googleapis.com-dotnet-quickstart.json
        static string[] Scopes = { SheetsService.Scope.SpreadsheetsReadonly };
        static string ApplicationName = "Google Sheets API .NET Quickstart";

        static void Main(string[] args)
        {
            UserCredential credential;

            using (var stream =
                new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
            {
                // The file token.json stores the user's access and refresh tokens, and is created
                // automatically when the authorization flow completes for the first time.
                string credPath = "token.json";
                credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
                    GoogleClientSecrets.Load(stream).Secrets,
                    Scopes,
                    "user",
                    CancellationToken.None,
                    new FileDataStore(credPath, true)).Result;
                Console.WriteLine("Credential file saved to: " + credPath);
            }

            // Create Google Sheets API service.
            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = ApplicationName,
            });

            // Define request parameters.
            String spreadsheetId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms";
            String range = "Class Data!A2:E";
            SpreadsheetsResource.ValuesResource.GetRequest request =
                    service.Spreadsheets.Values.Get(spreadsheetId, range);

            // Prints the names and majors of students in a sample spreadsheet:
            // https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
            ValueRange response = request.Execute();
            IList<IList<Object>> values = response.Values;
            if (values != null && values.Count > 0)
            {
                Console.WriteLine("Name, Major");
                foreach (var row in values)
                {
                    // Print columns A and E, which correspond to indices 0 and 4.
                    Console.WriteLine("{0}, {1}", row[0], row[4]);
                }
            }
            else
            {
                Console.WriteLine("No data found.");
            }
            Console.Read();
        }
    }
}

使用 Google Data API 使用 C# 访问 Google 电子表格

提问by blitzkriegz

采纳答案by Kelly

回答by Steve

回答by Tacoman667

回答by tonys

回答by Mauricio Scheffer

回答by wescpy

回答by JohnH

回答by Ogglas

相关推荐

最近更新

标签

使用 Google Data API 使用 C# 访问 Google 电子表格

提问by blitzkriegz

采纳答案by Kelly

回答by Steve

回答by Tacoman667

回答by tonys

回答by Mauricio Scheffer

回答by wescpy

回答by JohnH

回答by Ogglas

相关推荐

c# - 如何遍历类字段并设置属性

C# .net 中的流式 XML 序列化

C# 如何根据 List<string> 字段数对 List<List<string>> 进行排序？

C# 文件更改时通知？

相关推荐

最近更新

标签