PhpRiot
Become Zend Certified

Prepare for the ZCE exam using our quizzes (web or iPad/iPhone). More info...


When you're ready get 7.5% off your exam voucher using voucher CJQNOV23 at the Zend Store

Interacting With List-based Feeds

A given worksheet generally contains multiple rows, each containing multiple cells. You can request data from the worksheet either as a list-based feed, in which each entry represents a row, or as a cell-based feed, in which each entry represents a single cell. For information on cell-based feeds, see Interacting with cell-based feeds.

The following sections describe how to get a list-based feed, add a row to a worksheet, and send queries with various query parameters.

The list feed makes some assumptions about how the data is laid out in the spreadsheet.

In particular, the list feed treats the first row of the worksheet as a header row; Spreadsheets dynamically creates XML elements named after the contents of header-row cells. Users who want to provide Gdata feeds should not put any data other than column headers in the first row of a worksheet.

The list feed contains all rows after the first row up to the first blank row. The first blank row terminates the data set. If expected data isn't appearing in a feed, check the worksheet manually to see whether there's an unexpected blank row in the middle of the data. In particular, if the second row of the spreadsheet is blank, then the list feed will contain no data.

A row in a list feed is as many columns wide as the worksheet itself.

Get a List-based Feed

To retrieve a worksheet's list feed, use the getListFeed() method of the Spreadsheets service.

<?php
$query 
= new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($spreadsheetKey);
$query->setWorksheetId($worksheetId);
$listFeed $spreadsheetService->getListFeed($query);

The resulting Zend_Gdata_Spreadsheets_ListFeed object $listfeed represents a response from the server. Among other things, this feed contains an array of Zend_Gdata_Spreadsheets_ListEntry objects ($listFeed->entries), each of which represents a single row in a worksheet.

Each Zend_Gdata_Spreadsheets_ListEntry contains an array, custom, which contains the data for that row. You can extract and display this array:

<?php
$rowData 
$listFeed->entries[1]->getCustom();
foreach(
$rowData as $customEntry) {
  echo 
$customEntry->getColumnName() . " = " $customEntry->getText();
}

An alternate version of this array, customByName, allows direct access to an entry's cells by name. This is convenient when trying to access a specific header:

<?php
$customEntry 
$listFeed->entries[1]->getCustomByName('my_heading');
echo 
$customEntry->getColumnName() . " = " $customEntry->getText();

Reverse-sort Rows

By default, rows in the feed appear in the same order as the corresponding rows in the GUI; that is, they're in order by row number. To get rows in reverse order, set the reverse properties of the Zend_Gdata_Spreadsheets_ListQuery object to TRUE:

<?php
$query 
= new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($spreadsheetKey);
$query->setWorksheetId($worksheetId);
$query->setReverse('true');
$listFeed $spreadsheetService->getListFeed($query);

Note that if you want to order (or reverse sort) by a particular column, rather than by position in the worksheet, you can set the orderby value of the Zend_Gdata_Spreadsheets_ListQuery object to column:<the header of that column>.

Send a Structured Query

You can set a Zend_Gdata_Spreadsheets_ListQuery's sq value to produce a feed with entries that meet the specified criteria. For example, suppose you have a worksheet containing personnel data, in which each row represents information about a single person. You wish to retrieve all rows in which the person's name is "John" and the person's age is over 25. To do so, you would set sq as follows:

<?php
$query 
= new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($spreadsheetKey);
$query->setWorksheetId($worksheetId);
$query->setSpreadsheetQuery('name=John and age>25');
$listFeed $spreadsheetService->getListFeed($query);

Add a Row

Rows can be added to a spreadsheet by using the insertRow() method of the Spreadsheet service.

<?php
$insertedListEntry 
$spreadsheetService->insertRow($rowData,
                                                    
$spreadsheetKey,
                                                    
$worksheetId);

The $rowData parameter contains an array of column keys to data values. The method returns a Zend_Gdata_Spreadsheets_SpreadsheetsEntry object which represents the inserted row.

Spreadsheets inserts the new row immediately after the last row that appears in the list-based feed, which is to say immediately before the first entirely blank row.

Edit a Row

Once a Zend_Gdata_Spreadsheets_ListEntry object is fetched, its rows can be updated by using the updateRow() method of the Spreadsheet service.

<?php
$updatedListEntry 
$spreadsheetService->updateRow($oldListEntry,
                                                   
$newRowData);

The $oldListEntry parameter contains the list entry to be updated. $newRowData contains an array of column keys to data values, to be used as the new row data. The method returns a Zend_Gdata_Spreadsheets_SpreadsheetsEntry object which represents the updated row.

Delete a Row

To delete a row, simply invoke deleteRow() on the Zend_Gdata_Spreadsheets object with the existing entry to be deleted:

<?php
$spreadsheetService
->deleteRow($listEntry);

Alternatively, you can call the delete() method of the entry itself:

<?php
$listEntry
->delete();

Zend Framework