library/Zend/Gdata/Spreadsheets.php

   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 license@zend.com so we can send you a copy immediately.
  15  *
  16  * @category     Zend
  17  * @package      Zend_Gdata
  18  * @subpackage   Spreadsheets
  19  * @copyright    Copyright (c) 2005-2009 Zend Technologies USA Inc. (http://www.zend.com)
  20  * @license    http://framework.zend.com/license/new-bsd     New BSD License
  21  * @version    $Id: Spreadsheets.php 16971 2009-07-22 18:05:45Z mikaelkael $
  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-2009 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 }