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-2010 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 20096 2010-01-06 02:05:09Z bkarwin $

    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 }