Dojo DataGrid is a JavaScript library with spreadsheet view function , it supports datastores like Csv, Json, XML, Googlefeeds... etc. However, there is no Google Spreadsheets datastore for Dojo yet. In order to let Dojo Grid easily read Google Spreadsheets cells data, I created a PHP program to generate the CSV file from Google Spreadsheet as a dojox.data.CsvStore.
The PHP Zend Gdata 1.10.0 library is used to talk to the Google Data API, it takes care the client login, file listing, spread sheet files and worksheet tokens retrieving and cells data feed. retrieve the spreadsheets file list and key tokens, once user decide which file to read,
The first part of the PHP is for client login :
<div style="color: blue;">
<span style="font-size: x-small;"><i>set_include_path(get_include_path() . PATH_SEPARATOR . '/var/www/html/ZendGdata-1.10.0/library');
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Http_Client');
Zend_Loader::loadClass('Zend_Gdata');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');</i></span>
<span style="font-size: x-small;"><i>
$user = $_GET["id"];
$pass = $_GET["pass"];
if(isset($_GET["key"]))
$key = $_GET["key"];
else
$key="";</i></span></div>
To read the spreadsheets list :<div style="color: blue;">
<span style="font-size: x-small;"><i>function promptForSpreadsheet1($gdClient)
{
$feed =$gdClient->getSpreadsheetFeed();
$i=0;
$arr = array();
foreach( $feed->entries as $entry) {
$currKey = split('/', $entry->id->text);
$token = $currKey[5];
$arr[] = array("name" => $entry->title->text, "id" => $token);
$i++;
}
$jsonStr = json_encode($arr);
echo "{'identifier':'id','label':'name','items':$jsonStr}";
}</i></span></div>
To read the worksheets list of select spreadsheet :<span style="color: blue; font-size: x-small;"><i>function getFormWorksheet($gdClient)
{
global $key, $wkid;
$query = new Zend_Gdata_Spreadsheets_DocumentQuery();
$query->setSpreadsheetKey($key);
$feed = $gdClient->getWorksheetFeed($query);
echo printFeed($feed);
$input = getInput("\nSelection"); */
$currWkshtId = split('/', $feed->entries[0]->id->text);
$wkid = $currWkshtId[8];
}</i></span>
To read the header and row data and convert them into a CSV formatted file :<span style="color: blue; font-size: x-small;"><i>function cellsGetCsv()
{
global $gdClient, $key, $wkid;
$arr = array();
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($key);
$query->setWorksheetId($wkid);
$feed = $gdClient->getSpreadsheetCellFeedContents($query);
$jsonStr = json_encode($feed);
$pre_key="A";
$columns=0;
$rows=0;
$header = array();
foreach($feed as $key=>$entry){
if (substr($key, -1) <> "1") {
$columns = convertAlphabetToInt(substr($pre_key, 0, -1));
$jsonStr = json_encode($header);
break;
}
$header[] = $entry["value"];
$pre_key=$key;
}
$lastkey = end(array_keys($feed));
for ($k=1; $k<=strlen($lastkey); $k++){
if ( is_numeric(substr($lastkey, strlen($lastkey)-$k, 1))==false){
$rows=(int)substr($lastkey, strlen($lastkey)-$k+1);
}
}
$head = "";
$data = "";
for ($i = 0; $i< $columns; $i++){
$head .= $header[$i] . ",";
}
$arr = array();
for ($j=2; $j<=$rows; $j++){
$line = '';
for ($m=1; $m<=$columns; $m++){
/* $value = $feed[convertIntToAlphabet($m)+$j]->value; */
if(isset($feed[convertIntToAlphabet($m). (string)$j]))
$value = $feed[convertIntToAlphabet($m). (string)$j]["value"];
else
$value = "";
if ((!isset($value)) OR ($value == "")) {
$value = ",";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . ",";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r", "", $data);
echo "$head\n$data";
}
function convertAlphabetToInt($alpha_string) {
$int_wert=0;
$potenzcounter=0;
for ($i=strlen($alpha_string);$i>0;$i--) {
$ordinalwert=(ord(substr($alpha_string,$i-1,1))-64);
$int_wert+=$ordinalwert*pow(26,$potenzcounter);
$potenzcounter++;
}
return $int_wert;
}
function convertIntToAlphabet($int_wert) {
$alpha_string="";
if($int_wert%26>=1) {
$alpha_string=chr(($int_wert%26)+64).$alpha_string;
$alpha_string=convertIntToAlphabet($int_wert/26).$alpha_string;
}
return $alpha_string;
}</i></span>
The demo program - MAP123.html which interacts with the PHP file (ssheet.php) thru Ajax will need your GMail(or Google Apps account) id and password -
The bottom part of the program is to display the Google map with the locations(addresses) information on the spreadsheet.As long as there is a header called "ADDRESS" the Google Map will be triggered and display all addresses on the "ADDRESS" column. Click at any row, it will diplay the information windows for the selected row on the Google Map.
You can try the demo program here, or you can download the whole files here.
