利用C#對Google Spreadsheet進行線上即時存取
今天發生了一個古怪的需求，就是需要利用程式的方式進去Google Spreadsheet，進行線上的資料存取。經過調查後發現，Google其實有釋出一些相關的API讓.NET陣營的存取，所以用C#的人有福了，基本上線上的文件非常的豐富，稍微有點經驗的程式設計師應該都可以看懂才對，在這邊將一些原始碼記錄下來，好日後翻找。
四大觀念
Google定義Spreadsheet API中有四大觀念，這個觀念請先建立後，再來看原始碼操作會比較有Feel！
- spreadsheet
Google Sheets document, created with the Google Sheets user interface or with the Google Drive API. - worksheet
Named collection of cells within a spreadsheet. All spreadsheets must have at least one worksheet, and have one worksheet by default. - list row
Row of cells in a worksheet, represented as a key-value pair, where each key is a column name, and each value is the cell value. The first row of a worksheet is always considered the header row when using the API, and therefore is the row that defines the keys represented in each row. - cell
Single piece of data in a worksheet.
下載Google Data API SDK包
請到下列網址下載，當然，SDK請用越新版本越好，畢竟Google很愛改API版本也是眾所皆知的事。
下載網址：https://code.google.com/p/google-gdata/downloads/list
下載並安裝完成後，開啟VS專案時，別忘了「參考」一下應該參考的DLL檔。
如何在Google Spreadsheet中插入一列資料
這隻程式能夠運行的前提是，你必須在Google Drive中有一個Spreadsheet檔案名字叫「TestSpreadsheet」，然後裡面有一個頁籤WorkSheet叫「測試」，這個「測試」頁籤中，有五個欄位，分别叫「姓氏」、「名稱」、「英文名稱」、「Email」、「手機」。
如果沒有上述的預設前提，那麼你自己要寫程式利用cell去建立起來，在此不討論。
程式跑在Console Mode下，DLL記得在VisualStudio參考完後，也是要Using一下喔！
using System;
using System.IO;
/* Google Data API */
using Google.GData.Client;
using Google.GData.Spreadsheets;
namespace GoogleData
{
class Program
{
static void Main(string[] args)
{
userData oUser = new userData();
oUser.cAcc = "您的Google帳號";
oUser.cPwd = "您的Google密碼";
oUser.cFile = "TestSpreadsheet";
oUser.cSheet = "測試";
//登入
Console.Write("登入中... ");
SpreadsheetsService myService;
SpreadsheetQuery query;
SpreadsheetFeed oSF;
try
{
myService = new SpreadsheetsService("MySpreadsheetIntegration");
myService.setUserCredentials(oUser.cAcc, oUser.cPwd);
//取得可用之工作表
query = new SpreadsheetQuery();
oSF = myService.Query(query);
Console.WriteLine("成功！");
}
catch
{
Console.WriteLine("失敗！");
return;
}
//如果有指定之工作表的話，就設定為這個指定工作表
string cSpreadsheetName = oUser.cFile;
SpreadsheetEntry oSS = new SpreadsheetEntry();
foreach (SpreadsheetEntry entry in oSF.Entries)
{ if (entry.Title.Text == cSpreadsheetName) { oSS = entry; } }
//如果沒有這張作業表（檔案），那就直接顯示找不到工作表，踢出去
if (string.IsNullOrEmpty(oSS.Title.Text))
{
Console.WriteLine(string.Format("找不到以「{0}」為名的Spreadsheet。", cSpreadsheetName));
return;
}
//如果這張作業表裡面，沒有指定的頁籤（工作表），那就踢出去
string cWorksheetName = oUser.cSheet;
WorksheetFeed oWF = oSS.Worksheets;
WorksheetEntry oWS = new WorksheetEntry();
foreach (WorksheetEntry entry in oWF.Entries)
{ if (entry.Title.Text == cWorksheetName) { oWS = entry; } }
//如果沒有這張頁籤，那就直接顯示找不到頁籤，踢出去
if (string.IsNullOrEmpty(oWS.Title.Text))
{
Console.WriteLine(string.Format("找不到以「{0}」為名的Worksheet。", cWorksheetName));
return;
}
//取得本頁籤之連結，準備進行後續操作
AtomLink oWSLink = oWS.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery oQuery = new ListQuery(oWSLink.HRef.ToString());
ListFeed oFeed = myService.Query(oQuery);
ListEntry oFirstRow = oFeed.Entries[0] as ListEntry;
ListEntry oNewRow = new ListEntry();
foreach (ListEntry.Custom oTemp in oFirstRow.Elements)
{
string cTemp = oTemp.LocalName;
string cValue = "";
if (cTemp.IndexOf("姓氏") != -1)
{
cValue = "王";
}
else if (cTemp.IndexOf("名稱") != -1)
{
cValue = "小明";
}
else if (cTemp.IndexOf("英文名稱") != -1)
{
cValue = "Ming, Wang.";
}
else if (cTemp.IndexOf("Email") != -1)
{
cValue = "XiaoMing@gmail.com";
}
else //手機
{
cValue = "0912345678";
}
//加入一個欄位進入列
oNewRow.Elements.Add(new ListEntry.Custom()
{
LocalName = cTemp,
Value = cValue
});
}
//插入資料
ListEntry insertedRow = oFeed.Insert(oNewRow);
Console.WriteLine("插入資料成功。");
}
}
//參數物件化
class userData
{
public string cAcc { get; set; }
public string cPwd { get; set; }
public string cFile { get; set; }
public string cSheet { get; set; }
}
}