public function getColumns()
{
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($this->spreadsheetId);
$feed = $currentWorksheet = $this->spreadsheetService->getCellFeed($query);
$columns = array();
$columnCount = intval($feed->getColumnCount()->getText());
for ($i = 0; $i < $columnCount; $i++) {
if ($feed->entries[$i]) {
$columns[$i] = $feed->entries[$i]->getCell()->getText();
}
}
return $columns;
}
public function getColumns()
{
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($this->spreadsheetKey);
$feed = $this->spreadsheetService->getCellFeed($query);
$columnCount = intval($feed->getColumnCount()->getText());
$columns = array();
for ($i = 0; $i < $columnCount; $i++) {
if ($feed->entries[$i]) {
$columnName = $feed->entries[$i]->getCell()->getText();
$columns[$i] = strtolower(str_replace(' ', "", $columnName));
}
}
return $columns;
}
public function testUpdateCell()
{
$this->gdata->updateCell(5, 1, 'updated data', $this->sprKey, $this->wksId);
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($this->sprKey);
$query->setCellId('R5C1');
$entry = $this->gdata->getCellEntry($query);
$this->assertTrue($entry instanceof Zend_Gdata_Spreadsheets_CellEntry);
$this->assertTrue($entry->cell->getText() == 'updated data');
$this->gdata->updateCell(5, 1, '', $this->sprKey, $this->wksId);
}
/**
* Returns the content of all cells as an associative array, indexed
* off the cell location (ie 'A1', 'D4', etc). Each element of
* the array is an associative array with a 'value' and a 'function'.
* Only non-empty cells are returned by default. 'range' is the
* value of the 'range' query parameter specified at:
* http://code.google.com/apis/spreadsheets/reference.html#cells_Parameters
*
* @param mixed $location A CellQuery, WorksheetEntry or a URL (w/o query string) specifying the feed location.
* @param string $range The range of cells to retrieve
* @param boolean $empty Whether to retrieve empty cells
* @return array An associative array of cells
*/
public function getSpreadsheetCellFeedContents($location, $range = null, $empty = false)
{
$cellQuery = null;
if ($location instanceof Zend_Gdata_Spreadsheets_CellQuery) {
$cellQuery = $location;
} else {
if ($location instanceof Zend_Gdata_Spreadsheets_WorksheetEntry) {
$url = $location->getLink(self::CELL_FEED_LINK_URI)->href;
$cellQuery = new Zend_Gdata_Spreadsheets_CellQuery($url);
} else {
$url = $location;
$cellQuery = new Zend_Gdata_Spreadsheets_CellQuery($url);
}
}
if ($range != null) {
$cellQuery->setRange($range);
}
$cellQuery->setReturnEmpty($empty);
$cellFeed = $this->getCellFeed($cellQuery);
$cellFeed = $this->retrieveAllEntriesForFeed($cellFeed);
$spreadsheetContents = array();
foreach ($cellFeed as $cellEntry) {
$cellContents = array();
$cell = $cellEntry->getCell();
$cellContents['formula'] = $cell->getInputValue();
$cellContents['value'] = $cell->getText();
$spreadsheetContents[$cellEntry->getTitle()->getText()] = $cellContents;
}
return $spreadsheetContents;
}
/**
* Updates an existing cell.
* @param int $row The row containing the cell to update
* @param int $col The column containing the cell to update
* @param int $inputValue The new value for the cell
* @param string $key The key for the spreadsheet to be updated
* @param string $wkshtId (optional) The worksheet to be updated
* @return CellEntry The updated cell entry.
*/
public function updateCell($row, $col, $inputValue, $key, $wkshtId = 'default')
{
$cell = 'R' . $row . 'C' . $col;
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($key);
$query->setWorksheetId($wkshtId);
$query->setCellId($cell);
$entry = $this->getCellEntry($query);
$entry->setCell(new Zend_Gdata_Spreadsheets_Extension_Cell(null, $row, $col, $inputValue));
$response = $entry->save();
return $response;
}
public function getRowAndColumnCount()
{
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($this->currKey);
$query->setWorksheetId($this->currWkshtId);
$feed = $this->gdClient->getCellFeed($query);
if ($feed instanceOf Zend_Gdata_Spreadsheets_CellFeed) {
$this->rowCount = $feed->getRowCount();
$this->columnCount = $feed->getColumnCount();
}
}
public function cellsGetAction()
{
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($this->currKey);
$query->setWorksheetId($this->currWkshtId);
$feed = $this->gdClient->getCellFeed($query);
$this->printFeed($feed);
}
/**
* @see Zend_Gdata_Docs
*/
Zend_Loader::loadClass('Zend_Gdata_Docs');
/**
* @see Zend_Gdata_ClientLogin
*/
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
/**
* @see Zend_Gdata_Spreadsheets
*/
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
$authService = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
$httpClient = Zend_Gdata_ClientLogin::getHttpClient('*****@*****.**', 'internet21', $authService);
$gdClient = new Zend_Gdata_Spreadsheets($httpClient);
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey('0AlkFQsJI6D_7dEZOVXlTeDNYOGsyNVNWalNLdWdDU2c');
$query->setWorksheetId(3);
$query->setMinCol(3);
$query->setMaxCol(3);
$query->setMinRow(17);
$query->setMaxRow(17);
$feed = $gdClient->getCellFeed($query);
$entry = $gdClient->updateCell(16, 4, $_GET['precio'], '0AlkFQsJI6D_7dEZOVXlTeDNYOGsyNVNWalNLdWdDU2c', 3);
$entry = $gdClient->updateCell(17, 4, $_GET['enganche'], '0AlkFQsJI6D_7dEZOVXlTeDNYOGsyNVNWalNLdWdDU2c', 3);
$entry = $gdClient->updateCell(13, 9, $_GET['anio'], '0AlkFQsJI6D_7dEZOVXlTeDNYOGsyNVNWalNLdWdDU2c', 3);
$entry = $gdClient->updateCell(13, 3, $_GET['marca'], '0AlkFQsJI6D_7dEZOVXlTeDNYOGsyNVNWalNLdWdDU2c', 3);
$entry = $gdClient->updateCell(14, 3, $_GET['linea'], '0AlkFQsJI6D_7dEZOVXlTeDNYOGsyNVNWalNLdWdDU2c', 3);
$i = 0;
foreach ($feed->entries as $entry) {
if ($entry instanceof Zend_Gdata_Spreadsheets_CellEntry) {
/**
* Retrieve a particular cell
* @param string $st_id Spreadsheet ID
* @param string $wt_id Worksheet ID
* @return Zend_Gdata_Spreadsheets_CellEntry $entry CellEntry object containing cell data
*/
public function getCell($st_id, $wt_id, $cell_id)
{
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($st_id);
$query->setWorksheetId($wt_id);
/*
* Parse cell id
*/
// min length is 4
if (strlen($cell_id) < 4) {
throw new Exception();
}
$matches = array();
preg_match("/^R(\\d+)C(\\d+)\$/", $cell_id, $matches);
$query->setMaxRow($matches[1])->setMinRow($matches[1])->setMaxCol($matches[2])->setMinCol($matches[2]);
return $this->getFeed($query->getQueryUrl());
}
function gdata_import($attrib)
{
if ($attrib["key"] == "" || $attrib["worksheet"] == "" || $attrib["user"] == "" || $attrib["pass"] == "") {
echo 'ERROR: SET ALL ATTRIBUTES i.e sheet, worksheet, user, pass and columns. ';
return;
}
// load Zend Gdata libraries
set_include_path(get_include_path() . PATH_SEPARATOR . WP_PLUGIN_DIR . "//gdata-importer//");
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
// set credentials for ClientLogin authentication
$user = $attrib["user"];
$pass = $attrib["pass"];
try {
// connect to API
$service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
$client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
$service = new Zend_Gdata_Spreadsheets($client);
// get spreadsheet entry
$ssEntry = $service->getSpreadsheetEntry('https://spreadsheets.google.com/feeds/spreadsheets/' . $attrib["key"]);
// get worksheets in this spreadsheet
$wsFeed = $ssEntry->getWorksheets($attrib["worksheet"]);
} catch (Exception $e) {
echo 'ERROR: ' . $e->getMessage();
return;
}
$content = <<<HTML
\t<table>
HTML;
foreach ($wsFeed as $wsEntry) {
if ($wsEntry->getTitle() == $attrib["worksheet"]) {
//get title
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($attrib["key"]);
$id = $wsEntry->getId();
$arr = explode('/', $id);
$query->setWorksheetId($arr[sizeof($arr) - 1]);
$query->setMinRow(1);
$query->setMaxRow(1);
$cellFeed = $service->getCellFeed($query);
$content = $content . "<tr>";
foreach ($cellFeed as $cellEntry) {
$content = $content . "<td>" . $cellEntry->getCell()->getText() . "</td>";
}
$content = $content . "</tr>";
//get content
$rows = $wsEntry->getContentsAsRows();
foreach ($rows as $row) {
$content = $content . "<tr>";
foreach ($row as $key => $value) {
$content = $content . "<td>" . $value . "</td>";
}
$content = $content . "</tr>";
}
}
}
$content = $content . <<<HTML
\t\t</tbody>
\t</table>
HTML;
return $content;
}
private function insertRow($rowData)
{
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($this->docKey);
$rowArray = $rowData;
try {
$entry = $this->spreadsheetAdapter->insertRow($rowArray, $this->docKey, 1);
if ($entry instanceof Zend_Gdata_Spreadsheets_ListEntry) {
return true;
}
} catch (Exception $exc) {
$this->errors[] = 'No write premissoin';
}
return false;
}
function process($destination)
{
if (!$this->configured) {
$this->redirect(array('action' => 'config'));
}
$this->destination = $destination;
$gdata_start = microtime(true);
$client = new Zend_Gdata_SpreadSheets($this->gdata_client);
$feed = $client->getSpreadsheetFeed();
$search = $this->destination;
$spreadsheetKey = null;
foreach ($feed->entries as $index => $entry) {
if ($entry->title->text == $search) {
$id = explode('/', $entry->id->text);
$spreadsheetKey = end($id);
}
}
if ($spreadsheetKey === null) {
die('見つからないよ。');
}
$query = new Zend_Gdata_Spreadsheets_DocumentQuery();
$query->setSpreadsheetKey($spreadsheetKey);
$feed = $client->getWorksheetFeed($query);
$tables = array();
foreach ($feed->entries as $entry) {
$worksheetName = $entry->title->text;
if ($worksheetName == '表紙') {
continue;
}
$worksheetId = end(explode('/', $entry->id->text));
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($spreadsheetKey);
$query->setWorksheetId($worksheetId);
$cellFeed = $client->getCellFeed($query);
$table = array();
foreach ($cellFeed->entries as $cellEntry) {
$text = $cellEntry->cell->text;
$row = $cellEntry->cell->row;
$col = $cellEntry->cell->col;
if ($row < 3) {
continue;
}
$table[$row][$col] = $text;
}
if (empty($table[3][2])) {
continue;
}
foreach ($table as $index => $row) {
if (empty($row[2])) {
unset($table[$index]);
}
}
if (empty($table)) {
continue;
}
$tables[$worksheetName] = $table;
}
$this->set('gdata_elapsed_time', microtime(true) - $gdata_start);
$database_start = microtime(true);
App::import('Model', 'ConnectionManager');
$db =& ConnectionManager::getDataSource('default');
$result = $db->query('SET FOREIGN_KEY_CHECKS=0');
$this->_incrementSuccess($result);
$this->set('countTables', count($tables));
foreach ($tables as $name => $table) {
$result = $db->query("DROP TABLE IF EXISTS `{$name}`");
$this->_incrementSuccess($result);
$sql = 'CREATE TABLE IF NOT EXISTS `' . trim($name) . "` (\n";
$fieldDefs = $foreignKeys = $indexColmuns = array();
$records = array();
foreach ($table as $index => $row) {
$fieldDef = '`' . $row[2] . '` ';
$fieldDef .= $this->_getType($row[3]);
$fieldDef .= $this->_getNull($row[4], $this->_getType($row[3]));
if (isset($row[5])) {
$fieldDef .= $this->_getDefault($row[5]);
}
if (!empty($row[6])) {
$fieldDef .= $this->_getOthers($row[6]);
}
if (!empty($row[7])) {
$fieldDef .= $this->_getComment($row[7]);
}
if (!empty($row[1])) {
$colmun_index = strtolower(trim($row[1]));
if ($colmun_index == 'primary') {
$fieldDef .= 'PRIMARY KEY ';
} elseif ($colmun_index == 'unique') {
$fieldDef .= ' UNIQUE ';
} elseif ($colmun_index == 'index') {
$indexColmuns[] = $row[2];
} elseif ($colmun_index == 'foreign') {
$parts = explode('_', $row[2]);
array_pop($parts);
array_push($parts, Inflector::pluralize(array_pop($parts)));
$ref = implode('_', $parts);
if (strpos($ref, '.') === false) {
$ref .= '.id';
}
$foreignKeys[$row[2]] = $ref;
} elseif (preg_match('/foreign\\((.+?)\\)/', $colmun_index, $matche)) {
$ref = $matche[1];
if (strpos($ref, '.') === false) {
$ref .= '.id';
}
$foreignKeys[$row[2]] = $ref;
}
}
$fieldDefs[$index] = $fieldDef;
if (isset($row[9])) {
$records[$row[2]] = $this->_getRecordRow($row);
}
}
$sql .= implode(",\n", $fieldDefs);
if (!empty($foreignKeys)) {
$sql .= ",\n";
foreach ($foreignKeys as $from => $to) {
list($toTable, $toField) = explode('.', $to);
$foreignKeys[$from] = "FOREIGN KEY ({$from}) REFERENCES {$toTable}({$toField})";
}
$sql .= implode(",\n", $foreignKeys);
}
if (!empty($indexColmuns)) {
$sql .= ",\n";
foreach ($indexColmuns as $i => $colmun) {
$indexColmuns[$i] = " INDEX (`{$colmun}`) ";
}
$sql .= implode(",\n", $indexColmuns);
}
$sql .= ") ENGINE=InnoDB DEFAULT CHARSET=utf8";
$result = $db->query($sql);
$this->_incrementSuccess($result);
$this->_incrementCreateTableSuccess($result);
if (!empty($records)) {
$fields = array_keys($records);
$recordsDef = array();
foreach ($records as $field => $values) {
foreach ($values as $index => $value) {
$recordsDef[$index][array_search($field, $fields)] = $value;
}
}
foreach ($recordsDef as $index => $values) {
$recordsDef[$index] = '(' . implode(', ', $recordsDef[$index]) . ')';
}
$db->insertMulti(trim($name), $fields, $recordsDef);
}
}
$result = $db->query('SET FOREIGN_KEY_CHECKS=1');
$this->_incrementSuccess($result);
$this->set('database_elapsed_time', microtime(true) - $database_start);
$this->set('countSuccessTables', $this->_countSuccessTables);
$this->set('countSuccessQueries', $this->_countSuccessQueries);
$this->set('countQueries', $this->_countQueries);
}
function displayWorksheetData($spreedSheetId, $worksheetId)
{
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($spreedSheetId);
$query->setWorksheetId($worksheetId);
$query->setMinRow(4);
$cellFeed = $this->client->getCellFeed($query);
$i = 1;
$row_previous_value = 0;
foreach ($cellFeed as $cellEntry) {
$column = intval($cellEntry->cell->getColumn());
$row = intval($cellEntry->cell->getRow());
if ($row_previous_value != $row) {
$i = 1;
$row_previous_value = $row;
}
while ($column != $i) {
$data[$row][$i] = '';
$i++;
}
$data[$row][$column] = $cellEntry->cell->getText();
$i++;
}
return $data;
}
