Esempio n. 1
0
 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;
 }
Esempio n. 2
0
 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);
 }
Esempio n. 4
0
 /**
  * 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;
 }
Esempio n. 5
0
 /**
  * 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);
 }
Esempio n. 8
0
/**
 * @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) {
Esempio n. 9
0
 /**
  * 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());
 }
Esempio n. 10
0
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;
 }
Esempio n. 12
0
 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);
 }
Esempio n. 13
0
 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;
 }