[Summary view] [Print] [Text view]

   1  <?php
   2  
   3  /**
   4   * Zend Framework
   5   *
   6   * LICENSE
   7   *
   8   * This source file is subject to the new BSD license that is bundled
   9   * with this package in the file LICENSE.txt.
  10   * It is also available through the world-wide-web at this URL:
  11   * http://framework.zend.com/license/new-bsd
  12   * If you did not receive a copy of the license and are unable to
  13   * obtain it through the world-wide-web, please send an email
  14   * to [email protected] so we can send you a copy immediately.
  15   *
  16   * @category     Zend
  17   * @package      Zend_Gdata
  18   * @subpackage   Spreadsheets
  19   * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
  20   * @license    http://framework.zend.com/license/new-bsd     New BSD License
  21   * @version    $Id$
  22   */
  23  
  24  /**
  25   * Zend_Gdata
  26   */
  27  require_once('Zend/Gdata.php');
  28  
  29  /**
  30   * Zend_Gdata_Spreadsheets_SpreadsheetFeed
  31   */
  32  require_once('Zend/Gdata/Spreadsheets/SpreadsheetFeed.php');
  33  
  34  /**
  35   * Zend_Gdata_Spreadsheets_WorksheetFeed
  36   */
  37  require_once('Zend/Gdata/Spreadsheets/WorksheetFeed.php');
  38  
  39  /**
  40   * Zend_Gdata_Spreadsheets_CellFeed
  41   */
  42  require_once('Zend/Gdata/Spreadsheets/CellFeed.php');
  43  
  44  /**
  45   * Zend_Gdata_Spreadsheets_ListFeed
  46   */
  47  require_once('Zend/Gdata/Spreadsheets/ListFeed.php');
  48  
  49  /**
  50   * Zend_Gdata_Spreadsheets_SpreadsheetEntry
  51   */
  52  require_once('Zend/Gdata/Spreadsheets/SpreadsheetEntry.php');
  53  
  54  /**
  55   * Zend_Gdata_Spreadsheets_WorksheetEntry
  56   */
  57  require_once('Zend/Gdata/Spreadsheets/WorksheetEntry.php');
  58  
  59  /**
  60   * Zend_Gdata_Spreadsheets_CellEntry
  61   */
  62  require_once('Zend/Gdata/Spreadsheets/CellEntry.php');
  63  
  64  /**
  65   * Zend_Gdata_Spreadsheets_ListEntry
  66   */
  67  require_once('Zend/Gdata/Spreadsheets/ListEntry.php');
  68  
  69  /**
  70   * Zend_Gdata_Spreadsheets_DocumentQuery
  71   */
  72  require_once('Zend/Gdata/Spreadsheets/DocumentQuery.php');
  73  
  74  /**
  75   * Zend_Gdata_Spreadsheets_ListQuery
  76   */
  77  require_once('Zend/Gdata/Spreadsheets/ListQuery.php');
  78  
  79  /**
  80   * Zend_Gdata_Spreadsheets_CellQuery
  81   */
  82  require_once('Zend/Gdata/Spreadsheets/CellQuery.php');
  83  
  84  /**
  85   * Gdata Spreadsheets
  86   *
  87   * @link http://code.google.com/apis/gdata/spreadsheets.html
  88   *
  89   * @category     Zend
  90   * @package      Zend_Gdata
  91   * @subpackage   Spreadsheets
  92   * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
  93   * @license    http://framework.zend.com/license/new-bsd     New BSD License
  94   */
  95  class Zend_Gdata_Spreadsheets extends Zend_Gdata
  96  {
  97      const SPREADSHEETS_FEED_URI = 'http://spreadsheets.google.com/feeds/spreadsheets';
  98      const SPREADSHEETS_POST_URI = 'http://spreadsheets.google.com/feeds/spreadsheets/private/full';
  99      const WORKSHEETS_FEED_LINK_URI = 'http://schemas.google.com/spreadsheets/2006#worksheetsfeed';
 100      const LIST_FEED_LINK_URI = 'http://schemas.google.com/spreadsheets/2006#listfeed';
 101      const CELL_FEED_LINK_URI = 'http://schemas.google.com/spreadsheets/2006#cellsfeed';
 102      const AUTH_SERVICE_NAME = 'wise';
 103  
 104      /**
 105       * Namespaces used for Zend_Gdata_Photos
 106       *
 107       * @var array
 108       */
 109      public static $namespaces = array(
 110          array('gs', 'http://schemas.google.com/spreadsheets/2006', 1, 0),
 111          array(
 112              'gsx', 'http://schemas.google.com/spreadsheets/2006/extended', 1, 0)
 113      );
 114  
 115      /**
 116       * Create Gdata_Spreadsheets object
 117       *
 118       * @param Zend_Http_Client $client (optional) The HTTP client to use when
 119       *          when communicating with the Google servers.
 120       * @param string $applicationId The identity of the app in the form of Company-AppName-Version
 121       */
 122      public function __construct($client = null, $applicationId = 'MyCompany-MyApp-1.0')
 123      {
 124          $this->registerPackage('Zend_Gdata_Spreadsheets');
 125          $this->registerPackage('Zend_Gdata_Spreadsheets_Extension');
 126          parent::__construct($client, $applicationId);
 127          $this->_httpClient->setParameterPost('service', self::AUTH_SERVICE_NAME);
 128          $this->_server = 'spreadsheets.google.com';
 129      }
 130  
 131      /**
 132       * Gets a spreadsheet feed.
 133       *
 134       * @param mixed $location A DocumentQuery or a string URI specifying the feed location.
 135       * @return Zend_Gdata_Spreadsheets_SpreadsheetFeed
 136       */
 137      public function getSpreadsheetFeed($location = null)
 138      {
 139          if ($location == null) {
 140              $uri = self::SPREADSHEETS_FEED_URI;
 141          } else if ($location instanceof Zend_Gdata_Spreadsheets_DocumentQuery) {
 142              if ($location->getDocumentType() == null) {
 143                  $location->setDocumentType('spreadsheets');
 144              }
 145              $uri = $location->getQueryUrl();
 146          } else {
 147              $uri = $location;
 148          }
 149  
 150          return parent::getFeed($uri, 'Zend_Gdata_Spreadsheets_SpreadsheetFeed');
 151      }
 152  
 153      /**
 154       * Gets a spreadsheet entry.
 155       *
 156       * @param string $location A DocumentQuery or a URI specifying the entry location.
 157       * @return SpreadsheetEntry
 158       */
 159      public function getSpreadsheetEntry($location)
 160      {
 161          if ($location instanceof Zend_Gdata_Spreadsheets_DocumentQuery) {
 162              if ($location->getDocumentType() == null) {
 163                  $location->setDocumentType('spreadsheets');
 164              }
 165              $uri = $location->getQueryUrl();
 166          } else {
 167              $uri = $location;
 168          }
 169  
 170          return parent::getEntry($uri, 'Zend_Gdata_Spreadsheets_SpreadsheetEntry');
 171      }
 172  
 173      /**
 174       * Gets a worksheet feed.
 175       *
 176       * @param mixed $location A DocumentQuery, SpreadsheetEntry, or a string URI
 177       * @return Zend_Gdata_Spreadsheets_WorksheetFeed The feed of worksheets
 178       */
 179      public function getWorksheetFeed($location)
 180      {
 181          if ($location instanceof Zend_Gdata_Spreadsheets_DocumentQuery) {
 182              if ($location->getDocumentType() == null) {
 183                  $location->setDocumentType('worksheets');
 184              }
 185              $uri = $location->getQueryUrl();
 186          } else if ($location instanceof Zend_Gdata_Spreadsheets_SpreadsheetEntry) {
 187              $uri = $location->getLink(self::WORKSHEETS_FEED_LINK_URI)->href;
 188          } else {
 189              $uri = $location;
 190          }
 191  
 192          return parent::getFeed($uri, 'Zend_Gdata_Spreadsheets_WorksheetFeed');
 193      }
 194  
 195      /**
 196       * Gets a worksheet entry.
 197       *
 198       * @param string $location A DocumentQuery or a URI specifying the entry location.
 199       * @return WorksheetEntry
 200       */
 201      public function GetWorksheetEntry($location)
 202      {
 203          if ($location instanceof Zend_Gdata_Spreadsheets_DocumentQuery) {
 204              if ($location->getDocumentType() == null) {
 205                  $location->setDocumentType('worksheets');
 206              }
 207              $uri = $location->getQueryUrl();
 208          } else {
 209              $uri = $location;
 210          }
 211  
 212          return parent::getEntry($uri, 'Zend_Gdata_Spreadsheets_WorksheetEntry');
 213      }
 214  
 215      /**
 216       * Gets a cell feed.
 217       *
 218       * @param string $location A CellQuery, WorksheetEntry or a URI specifying the feed location.
 219       * @return CellFeed
 220       */
 221      public function getCellFeed($location)
 222      {
 223          if ($location instanceof Zend_Gdata_Spreadsheets_CellQuery) {
 224              $uri = $location->getQueryUrl();
 225          } else if ($location instanceof Zend_Gdata_Spreadsheets_WorksheetEntry) {
 226              $uri = $location->getLink(self::CELL_FEED_LINK_URI)->href;
 227          } else {
 228              $uri = $location;
 229          }
 230          return parent::getFeed($uri, 'Zend_Gdata_Spreadsheets_CellFeed');
 231      }
 232  
 233      /**
 234       * Gets a cell entry.
 235       *
 236       * @param string $location A CellQuery or a URI specifying the entry location.
 237       * @return CellEntry
 238       */
 239      public function getCellEntry($location)
 240      {
 241          if ($location instanceof Zend_Gdata_Spreadsheets_CellQuery) {
 242              $uri = $location->getQueryUrl();
 243          } else {
 244              $uri = $location;
 245          }
 246  
 247          return parent::getEntry($uri, 'Zend_Gdata_Spreadsheets_CellEntry');
 248      }
 249  
 250      /**
 251       * Gets a list feed.
 252       *
 253       * @param mixed $location A ListQuery, WorksheetEntry or string URI specifying the feed location.
 254       * @return ListFeed
 255       */
 256      public function getListFeed($location)
 257      {
 258          if ($location instanceof Zend_Gdata_Spreadsheets_ListQuery) {
 259              $uri = $location->getQueryUrl();
 260          } else if ($location instanceof Zend_Gdata_Spreadsheets_WorksheetEntry) {
 261              $uri = $location->getLink(self::LIST_FEED_LINK_URI)->href;
 262          } else {
 263              $uri = $location;
 264          }
 265  
 266          return parent::getFeed($uri, 'Zend_Gdata_Spreadsheets_ListFeed');
 267      }
 268  
 269      /**
 270       * Gets a list entry.
 271       *
 272       * @param string $location A ListQuery or a URI specifying the entry location.
 273       * @return ListEntry
 274       */
 275      public function getListEntry($location)
 276      {
 277          if ($location instanceof Zend_Gdata_Spreadsheets_ListQuery) {
 278              $uri = $location->getQueryUrl();
 279          } else {
 280              $uri = $location;
 281          }
 282  
 283          return parent::getEntry($uri, 'Zend_Gdata_Spreadsheets_ListEntry');
 284      }
 285  
 286      /**
 287       * Updates an existing cell.
 288       *
 289       * @param int $row The row containing the cell to update
 290       * @param int $col The column containing the cell to update
 291       * @param int $inputValue The new value for the cell
 292       * @param string $key The key for the spreadsheet to be updated
 293       * @param string $wkshtId (optional) The worksheet to be updated
 294       * @return CellEntry The updated cell entry.
 295       */
 296      public function updateCell($row, $col, $inputValue, $key, $wkshtId = 'default')
 297      {
 298          $cell = 'R'.$row.'C'.$col;
 299  
 300          $query = new Zend_Gdata_Spreadsheets_CellQuery();
 301          $query->setSpreadsheetKey($key);
 302          $query->setWorksheetId($wkshtId);
 303          $query->setCellId($cell);
 304  
 305          $entry = $this->getCellEntry($query);
 306          $entry->setCell(new Zend_Gdata_Spreadsheets_Extension_Cell(null, $row, $col, $inputValue));
 307          $response = $entry->save();
 308          return $response;
 309      }
 310  
 311      /**
 312       * Inserts a new row with provided data.
 313       *
 314       * @param array $rowData An array of column header to row data
 315       * @param string $key The key of the spreadsheet to modify
 316       * @param string $wkshtId (optional) The worksheet to modify
 317       * @return ListEntry The inserted row
 318       */
 319      public function insertRow($rowData, $key, $wkshtId = 'default')
 320      {
 321          $newEntry = new Zend_Gdata_Spreadsheets_ListEntry();
 322          $newCustomArr = array();
 323          foreach ($rowData as $k => $v) {
 324              $newCustom = new Zend_Gdata_Spreadsheets_Extension_Custom();
 325              $newCustom->setText($v)->setColumnName($k);
 326              $newEntry->addCustom($newCustom);
 327          }
 328  
 329          $query = new Zend_Gdata_Spreadsheets_ListQuery();
 330          $query->setSpreadsheetKey($key);
 331          $query->setWorksheetId($wkshtId);
 332  
 333          $feed = $this->getListFeed($query);
 334          $editLink = $feed->getLink('http://schemas.google.com/g/2005#post');
 335  
 336          return $this->insertEntry($newEntry->saveXML(), $editLink->href, 'Zend_Gdata_Spreadsheets_ListEntry');
 337      }
 338  
 339      /**
 340       * Updates an existing row with provided data.
 341       *
 342       * @param ListEntry $entry The row entry to update
 343       * @param array $newRowData An array of column header to row data
 344       */
 345      public function updateRow($entry, $newRowData)
 346      {
 347          $newCustomArr = array();
 348          foreach ($newRowData as $k => $v) {
 349              $newCustom = new Zend_Gdata_Spreadsheets_Extension_Custom();
 350              $newCustom->setText($v)->setColumnName($k);
 351              $newCustomArr[] = $newCustom;
 352          }
 353          $entry->setCustom($newCustomArr);
 354  
 355          return $entry->save();
 356      }
 357  
 358      /**
 359       * Deletes an existing row .
 360       *
 361       * @param ListEntry $entry The row to delete
 362       */
 363      public function deleteRow($entry)
 364      {
 365          $entry->delete();
 366      }
 367  
 368      /**
 369       * Returns the content of all rows as an associative array
 370       *
 371       * @param mixed $location A ListQuery or string URI specifying the feed location.
 372       * @return array An array of rows.  Each element of the array is an associative array of data
 373       */
 374      public function getSpreadsheetListFeedContents($location)
 375      {
 376          $listFeed = $this->getListFeed($location);
 377          $listFeed = $this->retrieveAllEntriesForFeed($listFeed);
 378          $spreadsheetContents = array();
 379          foreach ($listFeed as $listEntry) {
 380              $rowContents = array();
 381              $customArray = $listEntry->getCustom();
 382              foreach ($customArray as $custom) {
 383                  $rowContents[$custom->getColumnName()] = $custom->getText();
 384              }
 385              $spreadsheetContents[] = $rowContents;
 386          }
 387          return $spreadsheetContents;
 388      }
 389  
 390      /**
 391       * Returns the content of all cells as an associative array, indexed
 392       * off the cell location  (ie 'A1', 'D4', etc).  Each element of
 393       * the array is an associative array with a 'value' and a 'function'.
 394       * Only non-empty cells are returned by default.  'range' is the
 395       * value of the 'range' query parameter specified at:
 396       * http://code.google.com/apis/spreadsheets/reference.html#cells_Parameters
 397       *
 398       * @param mixed $location A CellQuery, WorksheetEntry or a URL (w/o query string) specifying the feed location.
 399       * @param string $range The range of cells to retrieve
 400       * @param boolean $empty Whether to retrieve empty cells
 401       * @return array An associative array of cells
 402       */
 403      public function getSpreadsheetCellFeedContents($location, $range = null, $empty = false)
 404      {
 405          $cellQuery = null;
 406          if ($location instanceof Zend_Gdata_Spreadsheets_CellQuery) {
 407              $cellQuery = $location;
 408          } else if ($location instanceof Zend_Gdata_Spreadsheets_WorksheetEntry) {
 409              $url = $location->getLink(self::CELL_FEED_LINK_URI)->href;
 410              $cellQuery = new Zend_Gdata_Spreadsheets_CellQuery($url);
 411          } else {
 412              $url = $location;
 413              $cellQuery = new Zend_Gdata_Spreadsheets_CellQuery($url);
 414          }
 415  
 416          if ($range != null) {
 417              $cellQuery->setRange($range);
 418          }
 419          $cellQuery->setReturnEmpty($empty);
 420  
 421          $cellFeed = $this->getCellFeed($cellQuery);
 422          $cellFeed = $this->retrieveAllEntriesForFeed($cellFeed);
 423          $spreadsheetContents = array();
 424          foreach ($cellFeed as $cellEntry) {
 425              $cellContents = array();
 426              $cell = $cellEntry->getCell();
 427              $cellContents['formula'] = $cell->getInputValue();
 428              $cellContents['value'] = $cell->getText();
 429              $spreadsheetContents[$cellEntry->getTitle()->getText()] = $cellContents;
 430          }
 431          return $spreadsheetContents;
 432      }
 433  
 434      /**
 435       * Alias for getSpreadsheetFeed
 436       *
 437       * @param mixed $location A DocumentQuery or a string URI specifying the feed location.
 438       * @return Zend_Gdata_Spreadsheets_SpreadsheetFeed
 439       */
 440      public function getSpreadsheets($location = null)
 441      {
 442          return $this->getSpreadsheetFeed($location = null);
 443      }
 444  
 445  }