Google Spreadsheets 是著名的基于云的电子表格应用程序。Web 应用程序开发人员可以通过 Google Spreadsheet Data API 从在线电子表格中访问和搜索数据。本文将介绍 Google Spreadsheets Data API 并在一个 PHP 应用程序中进行演示，解释如何添加、修改和删除电子表格内容的各种元素。
简介
在本文第一部分中，我向您介绍了 Google Spreadsheets Data API，一种允许开发人员使用 Google Spreadsheets 云中托管的用户空间电子表格数据轻松构建新应用程序的 REST API。我解释了电子表格、工作表、列表和单元格提要等基本知识，演示了如何对这些提要使用 Zend Framework 来快速、高效地将电子表格内容推入到一个 PHP 应用程序中。
然而，Google Spreadsheets Data API 不仅仅支持检索和搜索电子表格。在本文的最后一部分中，我将解释如何使用 Data API 远程操作电子表格内容，从一个远程 PHP 应用程序中添加、更新和删除行、单元格、工作表。本文将通过两个示例应用程序来演示所涉及的技巧，分别是使用 Data API 读取 RSS 提要和 SQL 结果集并将它们导入到一个 Google Spreadsheet。
添加工作表
每个电子表格都包含一个或多个工作表，Google Spreadsheets Data API 允许您通过编程方式访问和操作这些工作表。要了解其工作方式，请访问 Google Spreadsheets 服务，登录并创建一个空的电子表格。注意，电子表格键应当可以从 URL 中看到。您的空电子表格应当类似于 图 1。
图 1. 创建一个新的空电子表格
要向电子表格添加一个新的工作表，需要以 Atom 格式生成一个新的工作表条目，然后将该条目 POST 到指定电子表格的工作表 URL 中。考虑 清单 1，其中演示了这一过程。
清单 1. 清单 1. 添加一个新的工作表
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "1/DTD/xhtml1-transitional.dtd"><html xmlns="" xml:lang="en" lang="en"> <head> <title>Creating worksheets</title> <style> body { font-family: Verdana; } </style> </head> <body> <?php // load Zend Gdata libraries require_once ‘Zend/Loader.php’; Zend_Loader::loadClass(‘Zend_Gdata_Spreadsheets’); Zend_Loader::loadClass(‘Zend_Gdata_ClientLogin’); // set credentials for ClientLogin authentication $user = ""; $pass = "somepass"; try { // connect to API $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME; $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service); $service = new Zend_Gdata_Spreadsheets($client); // get spreadsheet entry $ssEntry = $service->getSpreadsheetEntry( ”); // get worksheet feed for this spreadsheet $wsFeed = $service->getWorksheetFeed($ssEntry); // create new entry $doc = new DOMDocument(); $doc->formatOutput = true; $entry = $doc->createElement(‘atom:entry’); $entry->setAttributeNS(‘>
首先需要注意的是，Zend_Gdata_Spreadsheets 组件没有提供本地方法来添加或更新工作表。但是仍然可以执行这些操作。您必须手动创建 XML 工作表元素，并使用通用的 insertEntry() 方法将其 POST 到正确的 URL。
清单 1 首先获得对指定电子表格条目的引用，然后获得该条目的工作表提要。接下来，创建一个 DOMDocument 对象来表示实际的条目，然后向其添加元素来指定工作表标题、行数和列数。最后，insertEntry() 方法向工作表提要的 URL 发送 XML 片段，以将修改提交给电子表格。
在执行本脚本并返回到 Google Spreadsheets GUI 后，将看到空的电子表格中出现了一个新的工作表，如 图 2 所示。
图 2. 图 2. 添加了新工作表的电子表格
完成这一任务的另一种方法是创建 Zend_Gdata_Spreadsheets_WorksheetEntry 对象的一个实例，设置它的属性，然后将该条目对象提交给工作表提要 URL。最终的结果是相同的，但是这一种方法更简单一些，因为 Zend_Gdata_Spreadsheets 将负责处理生成工作表条目 XML 等细节。考虑 清单 2，其中演示了生成与 清单 1 等效的输出的替代方法。
第 2 页 添加一个新的工作表
清单 2. 添加一个新的工作表
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "1/DTD/xhtml1-transitional.dtd"><html xmlns="" xml:lang="en" lang="en"> <head> <title>Creating worksheets</title> <style> body { font-family: Verdana; } </style> </head> <body> <?php // load Zend Gdata libraries require_once ‘Zend/Loader.php’; Zend_Loader::loadClass(‘Zend_Gdata_Spreadsheets’); Zend_Loader::loadClass(‘Zend_Gdata_ClientLogin’); // set credentials for ClientLogin authentication $user = ""; $pass = "somepass"; try { // connect to API $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME; $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service); $service = new Zend_Gdata_Spreadsheets($client); // get spreadsheet entry $ssEntry = $service->getSpreadsheetEntry( ”); // get worksheet feed for this spreadsheet $wsFeed = $service->getWorksheetFeed($ssEntry); // create new entry $wsEntry = new Zend_Gdata_Spreadsheets_WorksheetEntry(); $title = new Zend_Gdata_App_Extension_Title(‘Jan 2011′); $wsEntry->setTitle($title); $row = new Zend_Gdata_Spreadsheets_Extension_RowCount(’10’); $wsEntry->setRowCount($row); $col = new Zend_Gdata_Spreadsheets_Extension_ColCount(’10’); $wsEntry->setColumnCount($col); // insert entry $entryResult = $service->insertEntry($wsEntry, $wsFeed->getLink(‘self’)->getHref()); echo ‘The ID of the new worksheet entry is: ‘ . $entryResult->id; } catch (Exception $e) { die(‘ERROR: ‘ . $e->getMessage()); } ?> </body><html>
清单 2 为工作表标题、行计数和列计数创建了 Zend_Gdata_Spreadsheets_Extension 对象，然后将这些对象附着到基础的 Zend_Gdata_Spreadsheets_WorksheetEntry 对象。这种方式简化了理解，同时也使实现更简单。如前所述，最终的条目对象通过 insertEntry() 方法传递给工作表提要 URL。
第 3 页 更新和删除工作表
更新和删除工作表
除添加工作表以外，Google Spreadsheets Data API 还支持通过编程方式更新和移除工作表。这些操作都要求用户检索将要修改或删除的工作表条目，根据需要对条目作出修改，然后将其传递回服务器以完成执行。请求 —PUT 或 DELETE— 的性质决定是否更新或删除工作表条目。
要查看实际效果，考虑 清单 3，它更新了 清单 1 中创建的工作表的标题。注意，对于这个清单以及后续的清单，需要具有工作表键（可以从电子表格提要或 insertEntry() 方法返回的条目对象中获得）。
清单 3. 清单 3. 更新工作表
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "1/DTD/xhtml1-transitional.dtd"><html xmlns="" xml:lang="en" lang="en"> <head> <title>Updating worksheets</title> <style> body { font-family: Verdana; } table, td { border: 1px solid black; vertical-align: top; } </style> </head> <body> <?php // load Zend Gdata libraries require_once ‘Zend/Loader.php’; Zend_Loader::loadClass(‘Zend_Gdata_Spreadsheets’); Zend_Loader::loadClass(‘Zend_Gdata_ClientLogin’); // set credentials for ClientLogin authentication $user = ""; $pass = "somepass"; try { // connect to API $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME; $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service); $service = new Zend_Gdata_Spreadsheets($client); // get worksheet entry $query = new Zend_Gdata_Spreadsheets_DocumentQuery(); $query->setSpreadsheetKey(‘ssid’); $query->setWorksheetId(‘wsid’); $wsEntry = $service->getWorksheetEntry($query); $title = new Zend_Gdata_App_Extension_Title(‘Feb 2012’); $wsEntry->setTitle($title); // update entry $entryResult = $service->updateEntry($wsEntry, $wsEntry->getLink(‘edit’)->getHref()); echo ‘The ID of the updated worksheet entry is: ‘ . $entryResult->id; } catch (Exception $e) { die(‘ERROR: ‘ . $e->getMessage()); } ?> </body><html>
清单 3 使用前面示例中的 getWorksheetEntry() 方法，按照唯一标识符检索特定的工作表条目。
条目对象的 setTitle() 方法随后修改工作表标题，然后使用 updateEnry() 方法将修改后的条目传回。注意，修改后的条目必须发送给条目对象指定的编辑 URL。
完成这个过程后，可以在 图 3 的 Google Spreadsheets GUI 中看到修改后的工作表。
图 3. 图 3. 包含更新后的工作表的电子表格
要删除一个工作表，只需要调用相应的工作表条目的 delete() 方法。清单 4 演示了删除 清单 3 中的工作表。
清单 4. 清单 4. 删除一个工作表
如前所述，这个脚本检索工作表条目并调用 delete() 方法来将一个 DELETE 请求传递给条目的编辑 URL。这个请求由 Google API 服务器解释和执行，最终结果是从电子表格中删除工作表。这一步骤在 图 4 中显示得非常清楚，其中显示电子表格已恢复到它的最初状态。
图 4. 图 4. 删除新的工作表后的电子表格
添加工作表行
Google Spreadsheets Data API 还可以向工作表添加行，这一次，Zend_Gdata_Spreadsheets 组件提供了一个原生方法来执行此操作。要添加行，创建一个表示该行的新的 Zend_Gdata_Spreadsheets_ListEntry 对象，向其中填充值，然后将该对象 POST 到列表提要 URL。清单 5 给出了一个例子。
清单 5. 添加工作表行
图 5. 添加了新行的工作表
在以编程方式添加行时需注意以下问题：
目标工作表必须已经存在，并且必须已经定义了标题行的字段。 传递给 insertRow() 的行数组必须包含与目标工作表中的列对应的键；仅提供值是不够的，并会生成一个名称空间错误。 只有那些所包含的键与目标工作表中的标题行匹配的字段被插入。 行数组的键不应该使用大写格式，因为这种格式通常会生成一个服务器错误，导致行插入失败。
更新和删除工作表行
还可以使用 Data API 更新和删除行。为此，必须检索要进行修改的行条目，对其作出修改，然后传递回列表提要 URL。是否将该请求作为 PUT 或 DELETE 发送取决于是否从工作表中修改或删除了行。
清单 6 演示了更新工作表中的行的过程。
清单 6. 更新工作表行
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "1/DTD/xhtml1-transitional.dtd"><html xmlns="" xml:lang="en" lang="en"> <head> <title>Updating worksheet rows</title> <style> body { font-family: Verdana; } </style> </head> <body> <?php // load Zend Gdata libraries require_once ‘Zend/Loader.php’; Zend_Loader::loadClass(‘Zend_Gdata_Spreadsheets’); Zend_Loader::loadClass(‘Zend_Gdata_ClientLogin’); // set credentials for ClientLogin authentication $user = ""; $pass = "somepass"; try { // connect to API $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME; $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service); $service = new Zend_Gdata_Spreadsheets($client); // set target spreadsheet and worksheet $ssKey = ‘ssid’; $wsKey = ‘wsid’; // get rows matching query $query = new Zend_Gdata_Spreadsheets_ListQuery(); $query->setSpreadsheetKey($ssKey); $query->setWorksheetId($wsKey); $query->setSpreadsheetQuery(‘salesunits > 25000’) $listFeed = $service->getListFeed($query); // iterate over matching rows // increase sales units by 50% // write updated rows back to spreadsheet foreach ($listFeed as $listEntry) { $rowData = $listEntry->getCustom(); $newRow = array(); foreach($rowData as $field) { $newRow[$field->getColumnName()] = $field->getText(); if ($field->getColumnName() == ‘salesunits’) { $newRow[$field->getColumnName()] = $field->getText()*1.5; } } $entryResult = $service->updateRow($listEntry, $newRow); } echo ‘Rows successfully updated.’; } catch (Exception $e) { die(‘ERROR: ‘ . $e->getMessage()); } ?> </body><html>
清单 6 创建了一个列表查询，该查询以列表提要的形式返回工作表中销售额超过某个限值的所有行。然后遍历该提要，处理每个行并调整每个行中的某些单元格的值。更新后的行随后通过 updateRow() 方法写回到电子表格。
图 6 演示了更新之前和更新之后的电子表格。（查看 图 6 的大图）。
图 6. 更新了某些行的工作表
删除行的过程与此类似，区别在于不会调用服务对象的 updateRow() 方法，而是调用行数组的 delete() 方法。清单 7 演示了从指定的工作表删除最后一行的过程。
清单 7. 删除工作表行
图 7 演示了更新之前和更新之后的电子表格。（查看 图 7 的大图）。
图 7. 删除了行的工作表
示例应用程序：RSS 转换器
现在您已经了解了如何添加和删除行，接下来让我们尝试构建一个简单的应用程序来进行实际演示。您将创建一个 RSS 到电子表格的转换器，该转换器从一个远程 RSS 提要检索所有新的新闻，从提要中提取新闻元数据，并放置到电子表格的行和列中。
在开始之前，返回到 Google Spreadsheets GUI 并创建一个空的电子表格，其中标题行需要与典型 RSS 提要中的字段对应。结果应当类似于 图 8。
图 8. 空的工作表
本例中的示例提要是 BBC 的 United Kingdom (UK) 新闻提要，其中包含英国重要的新闻报道的简要描述。通过使用 Zend Framework 的 Zend_Feed 组件，可以很轻松地解析新闻提要，从中提取信息，然后使用 Data API 写回到 Google Spreadsheet。清单 8 显示了代码。
清单 8. 从 RSS 提要导入工作表行
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "1/DTD/xhtml1-transitional.dtd"><html xmlns="" xml:lang="en" lang="en"> <head> <title>Importing worksheet rows from an RSS feed</title> <style> body { font-family: Verdana; } </style> </head> <body> <?php // load Zend Gdata libraries require_once ‘Zend/Loader.php’; Zend_Loader::loadClass(‘Zend_Gdata_Spreadsheets’); Zend_Loader::loadClass(‘Zend_Gdata_ClientLogin’); Zend_Loader::loadClass(‘Zend_Feed_Rss’); // set credentials for ClientLogin authentication $user = ""; $pass = "somepass"; try { // connect to API $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME; $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service); $service = new Zend_Gdata_Spreadsheets($client); // set target spreadsheet and worksheet $ssKey = ‘ssid’; $wsKey = ‘wsid’; // consume RSS feed // insert each channel item as a spreadsheet row // if large feed, increase PHP script execution time $channel = new Zend_Feed_Rss(”); foreach ($channel as $item) { $row = array(); $row[‘title’] = $item->title(); $row[‘description’] = $item->description(); $row[‘link’] = $item->link(); $row[‘date’] = $item->pubDate(); $entryResult = $service->insertRow($row, $ssKey, $wsKey); } } catch (Exception $e) { die(‘ERROR: ‘ . $e->getMessage()); } ?> </body><html>
使用 Zend_Feed 组件可以很轻松地解析 RSS 提要：只需要将 RSS 提要 URL 传递给对象构造函数（constructor），构造函数将检索提要，解析它，然后将它转换为一系列可以遍历的对象。清单 8 演示了这个过程，它遍历提要中的各项并为每一项创建一个数组。该数组随后通过 insertRow() 方法作为一个行插入到电子表格。该过程将持续进行，直到处理完 RSS 提要中的所有项。
图 9 展示了结果。
图 9. 包含来自 RSS 提要的行的工作表
注意，Data API 目前不允许批量插入行数据。然而，如果行顺序无关紧要的话，可以考虑以并行方式发送请求，从而加快处理速度。
修改工作表单元格
正如您可以修改工作表行一样，您也可以修改工作表单元格。如您所料，要完成这种修改，需要向工作表的单元格提要发送一个修改后的条目，其中包含将要更新的单元格的细节。
服务对象的 updateCell() 方法更新单元格。该方法接受 5 个参数：行索引、列索引、单元格值、电子表格键和工作表键。行和列均从 1 开始索引。清单 9 解释了工作原理。
清单 9. 更新工作表单元格
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "1/DTD/xhtml1-transitional.dtd"><html xmlns="" xml:lang="en" lang="en"> <head> <title>Updating worksheet cells</title> <style> body { font-family: Verdana; } </style> </head> <body> <?php // load Zend Gdata libraries require_once ‘Zend/Loader.php’; Zend_Loader::loadClass(‘Zend_Gdata_Spreadsheets’); Zend_Loader::loadClass(‘Zend_Gdata_ClientLogin’); // set credentials for ClientLogin authentication $user = ""; $pass = "somepass"; try { // connect to API $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME; $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service); $service = new Zend_Gdata_Spreadsheets($client); // set target spreadsheet and worksheet $ssKey = ‘ssid’; $wsKey = ‘wsid’; // update cell at row 6, column 5 $entry = $service->updateCell(‘6’, ‘5’, ‘Hello, world’, $ssKey, $wsKey); echo ‘Updated cell ‘ . $entry->getTitle()->getText() . ”; // clear cell at row 1, column 1 $entry = $service->updateCell(‘1’, ‘1’, ”, $ssKey, $wsKey); echo ‘Cleared cell ‘ . $entry->getTitle()->getText(); } catch (Exception $e) { die(‘ERROR: ‘ . $e->getMessage()); } ?> </body><html>
对 updateCell() 的调用将返回一个单元格条目对象，解析后可以得到特定的信息。特别是，注意单元格条目对象的标题将返回被更新的单元格的字母数字坐标。
图 10 演示了脚本输出。
图 10. 图 10. 更新单元格的结果
图 11 显示了 Google Spreadsheets GUI 中的结果。
图 11. 更新单元格后的工作表
另一种方法是使用一个单元格查询来返回特定的单元格，然后使用 setInputValue() 方法更新单元格。注意，也可以输入公式，而不是静态的值。清单 10 对此进行了演示。
清单 10. 更新工作表单元格
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "1/DTD/xhtml1-transitional.dtd"><html xmlns="" xml:lang="en" lang="en"> <head> <title>Updating worksheet cells</title> <style> body { font-family: Verdana; } </style> </head> <body> <?php // load Zend Gdata libraries require_once ‘Zend/Loader.php’; Zend_Loader::loadClass(‘Zend_Gdata_Spreadsheets’); Zend_Loader::loadClass(‘Zend_Gdata_ClientLogin’); // set credentials for ClientLogin authentication $user = ""; $pass = "somepass"; try { // connect to API $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME; $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service); $service = new Zend_Gdata_Spreadsheets($client); // set target spreadsheet and worksheet $ssKey = ‘ssid’; $wsKey = ‘wsid’; // get cell feed // restrict feed to single cell E4 $query = new Zend_Gdata_Spreadsheets_CellQuery(); $query->setSpreadsheetKey($ssKey); $query->setWorksheetId($wsKey); $query->setMinRow(4); $query->setMaxRow(4); $query->setMinCol(5); $query->setMaxCol(5); $cellFeed = $service->getCellFeed($query); // get cell from query $cellEntry = $cellFeed->offsetGet(0); // update cell value and save back to spreadsheet $cellEntry->getCell()->setInputValue(‘=B2’); $service->updateEntry($cellEntry, $cellEntry->getLink(‘edit’)->getHref()); echo ‘Updated cell ‘ . $cellEntry->getTitle()->getText(); } catch (Exception $e) { die(‘ERROR: ‘ . $e->getMessage()); } ?> </body><html>
在本例中，单元格值被设置为引用了另一个单元格的公式，而且使用服务对象的 updateEntry() 方法将修改后的条目写回。
示例应用程序：数据库导入器
了解了这些内容后，现在来看下一个例子：从一个 MySQL 数据库导入电子表格数据。假设您具有如 图 12 所示的数据库结构（查看 图 12 的文字版本）。
图 12. 图 12. 示例数据库
执行 SQL 查询并从查询结果集的每个字段中创建电子表格单元格，实现这些操作很简单。考虑 清单 11，其中显示了一个有效示例。
清单 11. 从数据库导入工作表单元格
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "1/DTD/xhtml1-transitional.dtd"><html xmlns="" xml:lang="en" lang="en"> <head> <title>Importing worksheet cells from a database</title> <style> body { font-family: Verdana; } </style> </head> <body> <?php // load Zend Gdata libraries require_once ‘Zend/Loader.php’; Zend_Loader::loadClass(‘Zend_Gdata_Spreadsheets’); Zend_Loader::loadClass(‘Zend_Gdata_ClientLogin’); // set credentials for ClientLogin authentication $user = ""; $pass = "somepass"; // create PDO connection $dbh = new PDO(‘mysql:host=localhost;dbname=library’, ‘user’, ‘pass’); $sql = "SELECT title, author FROM library"; try { // connect to API $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME; $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service); $service = new Zend_Gdata_Spreadsheets($client); // set target spreadsheet and worksheet $ssKey = ‘ssid’; $wsKey = ‘wsid’; // get results from database // insert header row $rowCount = 1; $result = $dbh->query($sql, PDO::FETCH_ASSOC); for ($x=0; $x<$result->columnCount(); $x++) { $col = $result->getColumnMeta($x); $service->updateCell($rowCount, ($x+1), $col[‘name’], $ssKey, $wsKey); } $rowCount++; // insert each field of each row as a spreadsheet cell // if large result set, increase PHP script execution time foreach($result as $row) { $colCount=1; foreach ($row as $k=>$v) { $service->updateCell($rowCount, $colCount, $v, $ssKey, $wsKey); $colCount++; } $rowCount++; } } catch (Exception $e) { die(‘ERROR: ‘ . $e->getMessage()); } ?> </body><html>
包含必要的类并设置了 Data API 凭证。随后创建一个到 MySQL 数据库的 PDO 连接并形成一个查询，该查询返回一个作者和标题列表。它还连接到 Spreadsheets Data API 并创建了一个经过身份验证的连接。
接下来，脚本使用 PDO getColumnMeta() 方法返回结果集的列名，并使用 updateCell() 方法将这些列名作为标题行写入到电子表格。随后遍历结果集，将每个字段作为单独的单元格写入到电子表格，在完成后跳到下一个行。通过这种方式，整个结果集的内容被转移到电子表格。
图 13 展示了 Google Spreadsheets GUI 中的结果。
图 13. 图 13. 从数据库结果集生成了一个 Google 电子表格
结束语
Google Spreadsheets Data API 支持从 PHP 应用程序中修改 Google Spreadsheets 云中的数据。支持常见任务，如添加、修改和删除工作表、行和单元格，并可以通过 Zend_Gdata 完成。本文给出了所有此类任务的例子，以及两个示例应用程序：面向 RSS 提要和数据库查询结果的电子表格导入器。
正如这些示例展示的一样，Google Spreadsheets Data API 是一种强大的、灵活的工具，开发人员可以使用它围绕云中的电子表格数据构建创新的新应用程序。Data API 目前正处于开发阶段，因此未来将增加更多有趣的功能。
有理想在的地方，地狱就是天堂
