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.
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();
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>.
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);
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.
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.