diff options
Diffstat (limited to 'library/Fileshipper/Xlsx/Worksheet.php')
| -rwxr-xr-x | library/Fileshipper/Xlsx/Worksheet.php | 245 |
1 files changed, 245 insertions, 0 deletions
diff --git a/library/Fileshipper/Xlsx/Worksheet.php b/library/Fileshipper/Xlsx/Worksheet.php new file mode 100755 index 0000000..1d9fc51 --- /dev/null +++ b/library/Fileshipper/Xlsx/Worksheet.php @@ -0,0 +1,245 @@ +<?php + +namespace Icinga\Module\Fileshipper\Xlsx; + +use RuntimeException; + +class Worksheet +{ + /** @var Workbook */ + protected $workbook; + + /** @var string */ + public $name; + + /** @var array */ + protected $data; + + /** @var int */ + public $rowCount; + + /** @var int */ + public $colCount; + + /** @var array */ + protected $config; + + /** @var array */ + protected $mergeTarget; + + public function __construct($xml, $sheetName, Workbook $workbook) + { + $this->config = $workbook->config; + $this->name = $sheetName; + $this->workbook = $workbook; + $this->parse($xml); + } + + // returns an array of the data from the sheet + public function getData() + { + return $this->data; + } + + protected function parse($xml) + { + $this->parseDimensions($xml->dimension); + $this->parseMergeCells($xml->mergeCells); + $this->parseData($xml->sheetData); + } + + protected function parseDimensions($dimensions) + { + $range = (string) $dimensions['ref']; + $cells = explode(':', $range); + $maxValues = $this->getColumnIndex($cells[1]); + $this->colCount = $maxValues[0] + 1; + $this->rowCount = $maxValues[1] + 1; + } + + protected function parseMergeCells($merges) + { + $result = []; + + if ($merges->mergeCell === null) { + $this->mergeTarget = $result; + return; + } + + foreach ($merges->mergeCell as $merge) { + $range = (string) $merge['ref']; + $cells = explode(':', $range); + $fromName = $cells[0]; + list($fromCol, $fromRow) = $this->getColumnIndex($fromName); + list($toCol, $toRow) = $this->getColumnIndex($cells[1]); + for ($i = $fromCol; $i <= $toCol; $i++) { + for ($j = $fromRow; $j <= $toRow; $j++) { + if ($i !== $fromCol || $j !== $fromRow) { + $result[$j][$i] = [$fromRow, $fromCol]; + } + } + } + } + + $this->mergeTarget = $result; + } + + protected function parseData($sheetData) + { + $rows = []; + $curR = 0; + $lastDataRow = -1; + + foreach ($sheetData->row as $row) { + $rowNum = (int) $row['r']; + if ($rowNum != ($curR + 1)) { + $missingRows = $rowNum - ($curR + 1); + for ($i = 0; $i < $missingRows; $i++) { + $rows[$curR] = array_pad([], $this->colCount, null); + $curR++; + } + } + $curC = 0; + $rowData = []; + + foreach ($row->c as $c) { + list($cellIndex,) = $this->getColumnIndex((string) $c['r']); + if ($cellIndex !== $curC) { + $missingCols = $cellIndex - $curC; + for ($i = 0; $i < $missingCols; $i++) { + $rowData[$curC] = null; + $curC++; + } + } + $val = $this->parseCellValue($c); + + if (!is_null($val)) { + $lastDataRow = $curR; + } + $rowData[$curC] = $val; + $curC++; + } + $rows[$curR] = array_pad($rowData, $this->colCount, null); + + // We clone merged cells, all of them will return the same value + // This behavior might eventually become optional with a related + // Config flag + if (array_key_exists($curR, $this->mergeTarget)) { + foreach ($this->mergeTarget[$curR] as $col => $cell) { + if ($rowData[$col] === null) { + $rows[$curR][$col] = $rows[$cell[0]][$cell[1]]; + } else { + throw new RuntimeException(sprintf( + '%s should merge into %s, but %s has a value: %s', + $this->makeCellName($cell[0], $cell[1]), + $this->makeCellName($curR, $col), + $this->makeCellName($curR, $col), + $rowData[$col] + )); + } + } + } + + $curR++; + } + + if ($this->config['removeTrailingRows']) { + $this->data = array_slice($rows, 0, $lastDataRow + 1); + $this->rowCount = count($this->data); + } else { + $this->data = $rows; + } + } + + protected function getColumnIndex($cell = 'A1') + { + if (preg_match('/([A-Z]+)(\d+)/', $cell, $matches)) { + $col = $matches[1]; + $row = $matches[2]; + $colLen = strlen($col); + $index = 0; + + for ($i = $colLen-1; $i >= 0; $i--) { + $index += (ord($col[$i]) - 64) * pow(26, $colLen - $i - 1); + } + + return [$index - 1, $row - 1]; + } + + throw new RuntimeException(sprintf('Invalid cell index %s', $cell)); + } + + protected function makeCellName($column, $row) + { + $str = ''; + + $rem = $column + 1; + while ($rem > 0) { + $mod = $rem % 26; + $str = chr($mod + 64) . $str; + $rem = ($rem - $mod) / 26; + } + + return $str . (string) ($row + 1); + } + + protected function parseCellValue($cell) + { + // t is the cell type + switch ((string) $cell['t']) { + // Shared string + case 's': + if ((string) $cell->v !== '') { + $value = $this->workbook->sharedStrings[intval($cell->v)]; + } else { + $value = ''; + } + break; + + // Boolean + case 'b': + $value = (string) $cell->v; + if ($value === '0') { + $value = false; + } elseif ($value === '1') { + $value = true; + } else { + $value = (bool) $cell->v; + } + break; + + // Inline rich text + case 'inlineStr': + $value = Utils::parseRichText($cell->is); + break; + + // Error message + case 'e': + if ((string) $cell->v !== '') { + $value = (string)$cell->v; + } else { + $value = ''; + } + break; + + default: + if (!isset($cell->v)) { + return null; + } + $value = (string) $cell->v; + + // Check for numeric values + if (is_numeric($value)) { + if ($value == (int) $value) { + $value = (int) $value; + } elseif ($value == (float) $value) { + $value = (float) $value; + } elseif ($value == (double) $value) { + $value = (double) $value; + } + } + } + + return $value; + } +} |