public static function anyToRC($cell)
{
$cell = strtoupper($cell);
preg_match('#^R(\\d+)C(\\d+)$#', $cell, $m);
if (count($m) === 3) {
return array('r' => intval($m['1']), 'c' => intval($m['2']));
}
preg_match('#^([A-Z]+)(\\d+)$#', $cell, $m);
if (count($m) === 3) {
return array('r' => intval($m['2']), 'c' => CellConverter::toNumber($m['1']));
}
throw new Exception("Wrong decomposition of cell literal '{$cell}'");
}
public function testAnyToRC()
{
for ($r = 15; $r < 35; $r += 1) {
for ($c = 1; $c < 15; $c += 1) {
$tmp = CellConverter::anyToRC(CellConverter::toLetter($c) . $r);
$this->assertSame($tmp['r'], $r);
$this->assertSame($tmp['c'], $c);
$rc = CellConverter::anyToRC('R' . $tmp['r'] . 'C' . $tmp['c']);
$this->assertSame($rc['r'], $r);
$this->assertSame($rc['c'], $c);
}
}
$wrong = 'R45C67d';
try {
CellConverter::anyToRC($wrong);
} catch (Exception $e) {
return $this->assertSame("Wrong decomposition of cell literal '" . strtoupper($wrong) . "'", $e->getMessage());
}
$this->assertSame(true, false);
}
/**
* https://developers.google.com/google-apps/spreadsheets/data#retrieve_a_list-based_feed
*/
public function update($key, $wid, $data)
{
$xml = <<<xml
<feed xmlns="http://www.w3.org/2005/Atom"
xmlns:batch="http://schemas.google.com/gdata/batch"
xmlns:gs="http://schemas.google.com/spreadsheets/2006">
<id>{$this->endpoint}/feeds/cells/{$key}/{$wid}/private/full</id>
xml;
foreach ($data as $cell => $d) {
$rc = CellConverter::anyToRC($cell);
$c = 'R' . $rc['r'] . 'C' . $rc['c'];
$xml .= <<<xml
<entry>
<batch:id>{$c}</batch:id>
<batch:operation type="update"/>
<id>{$this->endpoint}/feeds/cells/{$key}/{$wid}/private/full/{$c}</id>
<link rel="edit" type="application/atom+xml" href="{$this->endpoint}/feeds/cells/{$key}/{$wid}/private/full/{$c}"/>
<gs:cell row="{$rc['r']}" col="{$rc['c']}" inputValue="{$d}"/>
</entry>
xml;
}
$xml .= '</feed>';
$xml = $this->api("/feeds/cells/{$key}/{$wid}/private/full/batch", 'post', $xml, array("Content-Type" => "application/atom+xml", "If-Match" => "*"));
$firstError = 200;
$data = array();
$xml = SimpleXMLElementHelper::parseString($xml);
if (isset($xml['xml']['nstags'])) {
$xml = $xml['xml']['nstags'];
foreach ($xml['atom'] as &$tag) {
if ($tag['name'] === 'entry') {
$x =& $tag['nstags'];
$title = $this->_findTag($x, 'atom', 'title');
$content = $this->_findTag($x, 'atom', 'content');
$status = $this->_findTag($x, 'batch', 'status');
$id = $this->_findTag($x, 'batch', 'id');
// if error then there is no gs:cell node
$cell = $this->_findTag($x, 'gs', 'cell');
$code = $this->_findTag($status, 'attributes', 'code');
$reason = $this->_findTag($status, 'attributes', 'reason');
if ($code && $reason) {
$code = (int) $code['val'];
if ($firstError === 200 && $code !== 200) {
$firstError = $code;
}
$row = array('a1' => $title['text'], 'status' => $code, 'reason' => $reason['val']);
if ($cell) {
$t = $this->_findTag($cell, 'attributes', 'col');
if ($t) {
$row['col'] = (int) $t['val'];
}
$t = $this->_findTag($cell, 'attributes', 'row');
if ($t) {
$row['row'] = (int) $t['val'];
}
$t = $this->_findTag($cell, 'attributes', 'numericValue');
$row['numericValue'] = $t ? $t['val'] : null;
$t = $this->_findTag($cell, 'attributes', 'inputValue');
$row['inputValue'] = $t ? $t['val'] : null;
$row['inputValue'] = $content['text'];
}
$data[$id['text']] = $row;
}
}
}
}
return array('status' => $firstError, 'data' => $data);
}
