diff options
Diffstat (limited to 'library/Fileshipper')
| -rw-r--r-- | library/Fileshipper/ProvidedHook/Director/ImportSource.php | 613 | ||||
| -rw-r--r-- | library/Fileshipper/ProvidedHook/Director/ShipConfigFiles.php | 78 | ||||
| -rwxr-xr-x | library/Fileshipper/Xlsx/Utils.php | 37 | ||||
| -rwxr-xr-x | library/Fileshipper/Xlsx/Workbook.php | 300 | ||||
| -rwxr-xr-x | library/Fileshipper/Xlsx/Worksheet.php | 245 |
5 files changed, 1273 insertions, 0 deletions
diff --git a/library/Fileshipper/ProvidedHook/Director/ImportSource.php b/library/Fileshipper/ProvidedHook/Director/ImportSource.php new file mode 100644 index 0000000..c0d5ab5 --- /dev/null +++ b/library/Fileshipper/ProvidedHook/Director/ImportSource.php @@ -0,0 +1,613 @@ +<?php + +namespace Icinga\Module\Fileshipper\ProvidedHook\Director; + +use DirectoryIterator; +use Icinga\Application\Config; +use Icinga\Exception\ConfigurationError; +use Icinga\Exception\IcingaException; +use Icinga\Module\Director\Exception\JsonException; +use Icinga\Module\Director\Hook\ImportSourceHook; +use Icinga\Module\Director\Web\Form\QuickForm; +use Icinga\Module\Fileshipper\Xlsx\Workbook; +use RuntimeException; +use Symfony\Component\Yaml\Yaml; + +class ImportSource extends ImportSourceHook +{ + protected $db; + + protected $haveSymfonyYaml; + + public function getName() + { + return 'Import from files (fileshipper)'; + } + + /** + * @return object[] + * @throws ConfigurationError + * @throws IcingaException + */ + public function fetchData() + { + $basedir = $this->getSetting('basedir'); + $filename = $this->getSetting('file_name'); + $format = $this->getSetting('file_format'); + + if ($filename === '*') { + return $this->fetchFiles($basedir, $format); + } + + return (array) $this->fetchFile($basedir, $filename, $format); + } + + /** + * @return array + * @throws ConfigurationError + * @throws IcingaException + */ + public function listColumns() + { + return array_keys((array) current($this->fetchData())); + } + + /** + * @param QuickForm $form + * @return \Icinga\Module\Director\Forms\ImportSourceForm|QuickForm + * @throws \Zend_Form_Exception + */ + public static function addSettingsFormFields(QuickForm $form) + { + $form->addElement('select', 'file_format', array( + 'label' => $form->translate('File format'), + 'description' => $form->translate( + 'Available file formats, usually CSV, JSON, YAML and XML. Whether' + . ' all of those are available eventually depends on various' + . ' libraries installed on your system. Please have a look at' + . ' the documentation in case your list is not complete.' + ), + 'required' => true, + 'class' => 'autosubmit', + 'multiOptions' => $form->optionalEnum( + static::listAvailableFormats($form) + ), + )); + + /** @var \Icinga\Module\Director\Forms\ImportSourceForm $form */ + $format = $form->getSentOrObjectSetting('file_format'); + + try { + $configFile = Config::module('fileshipper', 'imports')->getConfigFile(); + $directories = static::listBaseDirectories(); + $ignored = static::listIgnoredBaseDirectories(); + $e = null; + } catch (\Throwable $e) { + $configFile = null; + $directories = []; + $ignored = []; + } catch (\Exception $e) { + $configFile = null; + $directories = []; + $ignored = []; + } + $form->addElement('select', 'basedir', array( + 'label' => $form->translate('Base directory'), + 'description' => sprintf( + $form->translate( + 'This import rule will only work with files relative to this' + . ' directory. The content of this list depends on your' + . ' configuration in "%s"' + ), + $configFile + ), + 'required' => true, + 'class' => 'autosubmit', + 'multiOptions' => $form->optionalEnum($directories), + )); + if ($configFile === null) { + if ($e) { + $form->getElement('basedir')->addError(sprintf( + $form->translate( + 'Failed to get directories from Fileshipper configuration: %s' + ), + $e->getMessage() + )); + } + } elseif (empty($directories)) { + $dirElement = $form->getElement('basedir'); + if (! @file_exists($configFile)) { + $dirElement->addError(\sprintf( + 'The file "%s" does not exist or is not accessible', + $configFile + )); + } + } + + if (! empty($ignored)) { + $list = []; + foreach ($ignored as $ignoredDirName => $section) { + $list[] = "$section: $ignoredDirName"; + } + $ignoredString = \implode(', ', $list); + if (count($list) === 1) { + $errorString = 'The following directory has been ignored: %s'; + } else { + $errorString = 'The following directories have been ignored: %s'; + } + $form->addHtmlHint(\sprintf($errorString, $ignoredString)); + } + + if (! ($basedir = $form->getSentOrObjectSetting('basedir'))) { + return $form; + } + + $form->addElement('select', 'file_name', array( + 'label' => $form->translate('File name'), + 'description' => $form->translate( + 'Choose a file from the above directory or * to import all files' + . ' from there at once' + ), + 'required' => true, + 'class' => 'autosubmit', + 'multiOptions' => $form->optionalEnum(self::enumFiles($basedir, $form)), + )); + + $basedir = $form->getSentOrObjectSetting('basedir'); + $basename = $form->getSentOrObjectSetting('file_name'); + if ($basedir === null || $basename === null) { + return $form; + } + + $filename = sprintf('%s/%s', $basedir, $basename); + switch ($format) { + case 'csv': + static::addCsvElements($form); + break; + + case 'xslx': + static::addXslxElements($form, $filename); + break; + } + + return $form; + } + + /** + * @param QuickForm $form + * @throws \Zend_Form_Exception + */ + protected static function addCsvElements(QuickForm $form) + { + $form->addElement('text', 'csv_delimiter', array( + 'label' => $form->translate('Field delimiter'), + 'description' => $form->translate( + 'This sets the field delimiter. One character only, defaults' + . ' to comma: ,' + ), + 'value' => ',', + 'required' => true, + )); + + $form->addElement('text', 'csv_enclosure', array( + 'label' => $form->translate('Value enclosure'), + 'description' => $form->translate( + 'This sets the field enclosure character. One character only,' + . ' defaults to double quote: "' + ), + 'value' => '"', + 'required' => true, + )); + + /* + // Not configuring escape as it behaves strangely. "te""st" works fine. + // Seems that even in case we use \, it must be "manually" removed later + // on + $form->addElement('text', 'csv_escape', array( + 'label' => $form->translate('Escape character'), + 'description' => $form->translate( + 'This sets the escaping character. One character only,' + . ' defaults to backslash: \\' + ), + 'value' => '\\', + 'required' => true, + )); + */ + } + + /** + * @param QuickForm $form + * @param $filename + * @throws \Zend_Form_Exception + */ + protected static function addXslxElements(QuickForm $form, $filename) + { + $form->addElement('select', 'worksheet_addressing', array( + 'label' => $form->translate('Choose worksheet'), + 'description' => $form->translate('How to choose a worksheet'), + 'multiOptions' => array( + 'by_position' => $form->translate('by position'), + 'by_name' => $form->translate('by name'), + ), + 'value' => 'by_position', + 'class' => 'autosubmit', + 'required' => true, + )); + + /** @var \Icinga\Module\Director\Forms\ImportSourceForm $form */ + $addressing = $form->getSentOrObjectSetting('worksheet_addressing'); + switch ($addressing) { + case 'by_name': + $file = static::loadXslxFile($filename); + $names = $file->getSheetNames(); + $names = array_combine($names, $names); + $form->addElement('select', 'worksheet_name', array( + 'label' => $form->translate('Name'), + 'required' => true, + 'value' => $file->getFirstSheetName(), + 'multiOptions' => $names, + )); + break; + + case 'by_position': + default: + $form->addElement('text', 'worksheet_position', array( + 'label' => $form->translate('Position'), + 'required' => true, + 'value' => '1', + )); + break; + } + } + + /** + * @param $basedir + * @param $format + * @return array + * @throws ConfigurationError + * @throws IcingaException + */ + protected function fetchFiles($basedir, $format) + { + $result = array(); + foreach (static::listFiles($basedir) as $file) { + $result[$file] = (object) $this->fetchFile($basedir, $file, $format); + } + + return $result; + } + + /** + * @param $basedir + * @param $file + * @param $format + * @return object[] + * @throws ConfigurationError + * @throws IcingaException + */ + protected function fetchFile($basedir, $file, $format) + { + $filename = $basedir . '/' . $file; + + switch ($format) { + case 'yaml': + return $this->readYamlFile($filename); + case 'json': + return $this->readJsonFile($filename); + case 'csv': + return $this->readCsvFile($filename); + case 'xslx': + return $this->readXslxFile($filename); + case 'xml': + libxml_disable_entity_loader(true); + return $this->readXmlFile($filename); + default: + throw new ConfigurationError( + 'Unsupported file format: %s', + $format + ); + } + } + + /** + * @param $filename + * @return Workbook + */ + protected static function loadXslxFile($filename) + { + return new Workbook($filename); + } + + /** + * @param $filename + * @return array + */ + protected function readXslxFile($filename) + { + $xlsx = new Workbook($filename); + if ($this->getSetting('worksheet_addressing') === 'by_name') { + $sheet = $xlsx->getSheetByName($this->getSetting('worksheet_name')); + } else { + $sheet = $xlsx->getSheet((int) $this->getSetting('worksheet_position')); + } + + $data = $sheet->getData(); + + $headers = null; + $result = []; + foreach ($data as $line) { + if ($headers === null) { + $hasValue = false; + foreach ($line as $value) { + if ($value !== null) { + $hasValue = true; + break; + } + // For now, no value in the first column means this is no header + break; + } + if ($hasValue) { + $headers = $line; + } + + continue; + } + + $row = []; + foreach ($line as $key => $val) { + if (empty($headers[$key])) { + continue; + } + $row[$headers[$key]] = $val; + } + + $result[] = (object) $row; + } + + return $result; + } + + /** + * @param $filename + * @return object[] + */ + protected function readCsvFile($filename) + { + $fh = fopen($filename, 'r'); + $lines = array(); + $delimiter = $this->getSetting('csv_delimiter'); + $enclosure = $this->getSetting('csv_enclosure'); + // $escape = $this->getSetting('csv_escape'); + + $headers = fgetcsv($fh, 0, $delimiter, $enclosure/*, $escape*/); + $row = 1; + while ($line = fgetcsv($fh, 0, $delimiter, $enclosure/*, $escape*/)) { + if (empty($line)) { + continue; + } + if (count($headers) !== count($line)) { + throw new RuntimeException(sprintf( + 'Column count in row %d does not match columns in header row', + $row + )); + } + + $line = array_combine($headers, $line); + foreach ($line as $key => & $value) { + if ($value === '') { + $value = null; + } + } + unset($value); + $lines[] = (object) $line; + + $row ++; + } + fclose($fh); + + return $lines; + } + + /** + * @param $filename + * @return object[] + */ + protected function readJsonFile($filename) + { + $content = @file_get_contents($filename); + if ($content === false) { + throw new RuntimeException(sprintf( + 'Unable to read JSON file "%s"', + $filename + )); + } + + $data = @json_decode($content); + if ($data === null) { + throw JsonException::forLastJsonError('Unable to load JSON data'); + } + + return $data; + } + + /** + * @param $file + * @return object[] + */ + protected function readXmlFile($file) + { + $lines = array(); + $content = file_get_contents($file); + foreach (simplexml_load_string($content) as $entry) { + $line = null; + $lines[] = $this->normalizeSimpleXML($entry); + } + + return $lines; + } + + /** + * @param $object + * @return object + */ + protected function normalizeSimpleXML($object) + { + $data = $object; + if (is_object($data)) { + $data = (object) get_object_vars($data); + } + + if (is_object($data)) { + foreach ($data as $key => $value) { + $data->$key = $this->normalizeSimpleXml($value); + } + } + + if (is_array($data)) { + foreach ($data as $key => $value) { + $data[$key] = $this->normalizeSimpleXml($value); + } + } + + return $data; + } + + /** + * @param $file + * @return object[] + */ + protected function readYamlFile($file) + { + return $this->fixYamlObjects( + yaml_parse_file($file) + ); + } + + /** + * @param $what + * @return object[] + */ + protected function fixYamlObjects($what) + { + if (is_array($what)) { + foreach (array_keys($what) as $key) { + if (! is_int($key)) { + $what = (object) $what; + break; + } + } + } + + if (is_array($what) || is_object($what)) { + foreach ($what as $k => $v) { + if (! empty($v)) { + if (is_object($what)) { + $what->$k = $this->fixYamlObjects($v); + } elseif (is_array($what)) { + $what[$k] = $this->fixYamlObjects($v); + } + } + } + } + + return $what; + } + + /** + * @param QuickForm $form + * @return array + */ + protected static function listAvailableFormats(QuickForm $form) + { + $formats = array( + 'csv' => $form->translate('CSV (Comma Separated Value)'), + 'json' => $form->translate('JSON (JavaScript Object Notation)'), + ); + + if (class_exists('\\ZipArchive')) { + $formats['xslx'] = $form->translate('XSLX (Microsoft Excel 2007+)'); + } + + if (function_exists('simplexml_load_file')) { + $formats['xml'] = $form->translate('XML (Extensible Markup Language)'); + } + + if (function_exists('yaml_parse_file')) { + $formats['yaml'] = $form->translate('YAML (Ain\'t Markup Language)'); + } + + return $formats; + } + + /** + * @return array + */ + protected static function listBaseDirectories() + { + $dirs = array(); + + foreach (Config::module('fileshipper', 'imports') as $key => $section) { + if (($dir = $section->get('basedir')) && @is_dir($dir)) { + $dirs[$dir] = $key; + } + } + + return $dirs; + } + + /** + * @return array + */ + protected static function listIgnoredBaseDirectories() + { + $dirs = array(); + + foreach (Config::module('fileshipper', 'imports') as $key => $section) { + if (($dir = $section->get('basedir')) && @is_dir($dir)) { + // Ignore them + } else { + $dirs[$dir] = $key; + } + } + + return $dirs; + } + + /** + * @param $basedir + * @param QuickForm $form + * @return array + */ + protected static function enumFiles($basedir, QuickForm $form) + { + return array_merge( + array( + '*' => sprintf('* (%s)', $form->translate('all files')) + ), + static::listFiles($basedir) + ); + } + + /** + * @param $basedir + * @return array + */ + protected static function listFiles($basedir) + { + $files = array(); + + $dir = new DirectoryIterator($basedir); + foreach ($dir as $file) { + if ($file->isFile()) { + $filename = $file->getBasename(); + if ($filename[0] !== '.') { + $files[$filename] = $filename; + } + } + } + + ksort($files); + + return $files; + } +} diff --git a/library/Fileshipper/ProvidedHook/Director/ShipConfigFiles.php b/library/Fileshipper/ProvidedHook/Director/ShipConfigFiles.php new file mode 100644 index 0000000..43f6f38 --- /dev/null +++ b/library/Fileshipper/ProvidedHook/Director/ShipConfigFiles.php @@ -0,0 +1,78 @@ +<?php + +namespace Icinga\Module\Fileshipper\ProvidedHook\Director; + +use Exception; +use Icinga\Application\Config; +use Icinga\Module\Director\Hook\ShipConfigFilesHook; +use RecursiveDirectoryIterator; +use RecursiveIteratorIterator; +use RegexIterator; + +class ShipConfigFiles extends ShipConfigFilesHook +{ + /** + * @return array + */ + public function fetchFiles() + { + $files = []; + foreach ($this->getDirectories() as $key => $cfg) { + $target = $cfg->get('target'); + try { + foreach ($this->listFiles($cfg->get('source'), $cfg->get('extensions')) as $file) { + try { + $files["$target/$file"] = file_get_contents($cfg->get('source') . '/' . $file); + } catch (Exception $e) { + $files["$target/$file"] = '/* ' . $e->getMessage() . ' */'; + } + } + } catch (Exception $e) { + $files["$target/ERROR.txt"] = '/* ' . $e->getMessage() . ' */'; + } + } + + return $files; + } + + /** + * @param $folder + * @param $extensions + * @return array + */ + protected function listFiles($folder, $extensions) + { + if (! $extensions) { + $pattern = '/^[^\.].+\.conf$/'; + } else { + $exts = []; + foreach (preg_split('/\s+/', $extensions, -1, PREG_SPLIT_NO_EMPTY) as $ext) { + $exts[] = preg_quote($ext, '/'); + } + + $pattern = '/^[^\.].+(?:' . implode('|', $exts) . ')$/'; + } + + $dir = new RecursiveDirectoryIterator($folder); + $ite = new RecursiveIteratorIterator($dir); + $files = new RegexIterator($ite, $pattern, RegexIterator::GET_MATCH); + $fileList = []; + $start = strlen($folder) + 1; + + foreach ($files as $file) { + foreach ($file as $f) { + $fileList[] = substr($f, $start); + } + } + + return $fileList; + } + + /** + * @return Config + */ + protected function getDirectories() + { + return Config::module('fileshipper', 'directories'); + } +} diff --git a/library/Fileshipper/Xlsx/Utils.php b/library/Fileshipper/Xlsx/Utils.php new file mode 100755 index 0000000..3ab9563 --- /dev/null +++ b/library/Fileshipper/Xlsx/Utils.php @@ -0,0 +1,37 @@ +<?php + +namespace Icinga\Module\Fileshipper\Xlsx; + +class Utils +{ + /** + * Extract text content from a rich text or inline string field + * @param null $is + * @return string + */ + public static function parseRichText($is = null) + { + $value = []; + if (isset($is->t)) { + $value[] = (string)$is->t; + } else { + foreach ($is->r as $run) { + $value[] = (string)$run->t; + } + } + + return implode(' ', $value); + } + + // converts an Excel date field (a number) to a unix timestamp (granularity: seconds) + public static function toUnixTimeStamp($excelDateTime) + { + if (! is_numeric($excelDateTime)) { + return $excelDateTime; + } + $d = floor($excelDateTime); // seconds since 1900 + $t = $excelDateTime - $d; + + return ($d > 0) ? ( $d - 25569 ) * 86400 + $t * 86400 : $t * 86400; + } +} diff --git a/library/Fileshipper/Xlsx/Workbook.php b/library/Fileshipper/Xlsx/Workbook.php new file mode 100755 index 0000000..9c20d95 --- /dev/null +++ b/library/Fileshipper/Xlsx/Workbook.php @@ -0,0 +1,300 @@ +<?php + +namespace Icinga\Module\Fileshipper\Xlsx; + +use RuntimeException; +use ZipArchive; + +/** + * Classes in this namespace have been built roughly based on various OSS + * XLSXReader implementations + */ +class Workbook +{ + // XML schemas + const SCHEMA_OFFICEDOCUMENT = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument'; + const SCHEMA_OFFICEDOCUMENT_RELATIONSHIP = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'; + const SCHEMA_RELATIONSHIP = 'http://schemas.openxmlformats.org/package/2006/relationships'; + const SCHEMA_SHAREDSTRINGS = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings'; + const SCHEMA_WORKSHEETRELATION = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet'; + + protected static $zipErrors = [ + ZipArchive::ER_EXISTS => 'File already exists', + ZipArchive::ER_INCONS => 'Zip archive inconsistent', + ZipArchive::ER_INVAL => 'Invalid argument', + ZipArchive::ER_MEMORY => 'Malloc failure', + ZipArchive::ER_NOENT => 'No such file', + ZipArchive::ER_NOZIP => 'Not a zip archive', + ZipArchive::ER_OPEN => 'Can\'t open file', + ZipArchive::ER_READ => 'Read error', + ZipArchive::ER_SEEK => 'Seek error', + ]; + + /** @var Worksheet[] */ + protected $sheets = []; + + public $sharedStrings = []; + + protected $sheetInfo; + + protected $sheetNameIndex; + + /** @var ZipArchive */ + protected $zip; + + public $config = [ + 'removeTrailingRows' => true + ]; + + protected $mainRelation; + + public function __construct($filename, $config = []) + { + $this->config = array_merge($this->config, $config); + $this->initialize($filename); + } + + protected function initialize($filename) + { + $this->zip = new ZipArchive(); + if (true === ($result = $this->zip->open($filename))) { + $this->parse(); + } else { + throw new RuntimeException(sprintf( + 'Failed to open %s : %s', + $filename, + $this->getZipErrorString($result) + )); + } + } + + protected function getZipErrorString($errorCode) + { + if (array_key_exists($errorCode, self::$zipErrors)) { + return self::$zipErrors[$errorCode]; + } else { + return "Unknown ZIP error code $errorCode"; + } + } + + // get a file from the zip + protected function extractFile($name) + { + $data = $this->zip->getFromName($name); + if ($data === false) { + throw new RuntimeException(sprintf( + "File %s does not exist in the Excel file", + $name + )); + } else { + return $data; + } + } + + protected function loadPackageRelationshipXml() + { + return simplexml_load_string($this->extractFile('_rels/.rels')); + } + + /** + * @return \SimpleXMLElement[] + */ + protected function getPackageRelationships() + { + return $this->loadPackageRelationshipXml()->Relationship; + } + + // workbookXML + protected function getMainDocumentRelation() + { + if ($this->mainRelation === null) { + foreach ($this->getPackageRelationships() as $relation) { + if ($relation['Type'] == self::SCHEMA_OFFICEDOCUMENT) { + $this->mainRelation = $relation; + break; + } + } + + if ($this->mainRelation === null) { + throw new RuntimeException( + 'Got invalid Excel file, found no main document' + ); + } + } + + return $this->mainRelation; + } + + protected function getWorkbookXml() + { + return simplexml_load_string( + $this->extractFile( + $this->getMainDocumentRelation()['Target'] + ) + ); + } + + protected function getWorkbookDir() + { + return dirname($this->getMainDocumentRelation()['Target']); + } + + /** + * @return \SimpleXMLElement[] + */ + protected function getWorkbookRelationShips() + { + $wbDir = $this->getWorkbookDir(); + $target = basename($this->getMainDocumentRelation()['Target']); + + return simplexml_load_string( + $this->extractFile("$wbDir/_rels/$target.rels") + )->Relationship; + } + + // extract the shared string and the list of sheets + protected function parse() + { + $sheets = []; + /** @var \SimpleXMLElement $sheet */ + $pos = 0; + foreach ($this->getWorkbookXml()->sheets->sheet as $sheet) { + $pos++; + $rId = (string) $sheet->attributes('r', true)->id; + // $sheets[$pos] = [ --> check docs + $sheets[$rId] = [ + 'rId' => $rId, + 'sheetId' => (int)$sheet['sheetId'], + 'name' => (string)$sheet['name'], + ]; + } + + $workbookDir = $this->getWorkbookDir() . '/'; + foreach ($this->getWorkbookRelationShips() as $relation) { + switch ($relation['Type']) { + case self::SCHEMA_WORKSHEETRELATION: + $sheets[(string) $relation['Id']]['path'] = $workbookDir . (string)$relation['Target']; + break; + + case self::SCHEMA_SHAREDSTRINGS: + $sharedStringsXML = simplexml_load_string( + $this->extractFile($workbookDir . $relation['Target']) + ); + + foreach ($sharedStringsXML->si as $val) { + if (isset($val->t)) { + $this->sharedStrings[] = (string)$val->t; + } elseif (isset($val->r)) { + $this->sharedStrings[] = Utils::parseRichText($val); + } + } + + break; + } + } + + $this->sheetInfo = []; + foreach ($sheets as $rid => $info) { + if (! array_key_exists('path', $info)) { + var_dump($sheets); + exit; + } + $this->sheetInfo[$info['name']] = [ + 'sheetId' => $info['sheetId'], + 'rid' => $rid, + 'path' => $info['path'] + ]; + } + } + + // returns an array of sheet names, indexed by sheetId + public function getSheetNames() + { + $res = []; + foreach ($this->sheetInfo as $sheetName => $info) { + $res[$info['sheetId']] = $sheetName; + // $res[$info['rid']] = $sheetName; + } + + return $res; + } + + public function getSheetCount() + { + return count($this->sheetInfo); + } + + // instantiates a sheet object (if needed) and returns an array of its data + public function getSheetData($sheetNameOrId) + { + $sheet = $this->getSheet($sheetNameOrId); + + return $sheet->getData(); + } + + // instantiates a sheet object (if needed) and returns the sheet object + public function getSheet($sheet) + { + if (is_numeric($sheet)) { + $sheet = $this->getSheetNameById($sheet); + } elseif (!is_string($sheet)) { + throw new RuntimeException("Sheet must be a string or a sheet Id"); + } + if (!array_key_exists($sheet, $this->sheets)) { + $this->sheets[$sheet] = new Worksheet($this->getSheetXML($sheet), $sheet, $this); + } + + return $this->sheets[$sheet]; + } + + public function getSheetByName($name) + { + if (!array_key_exists($name, $this->sheets)) { + $this->sheets[$name] = new Worksheet($this->getSheetXML($name), $name, $this); + } + + return $this->sheets[$name]; + } + + public function enumRidNames() + { + $res = []; + foreach ($this->sheetInfo as $name => $info) { + $res[$name] = $name; + } + + return $res; + } + + public function getSheetNameById($sheetId) + { + foreach ($this->sheetInfo as $sheetName => $sheetInfo) { + if ($sheetInfo['sheetId'] === $sheetId) { + return $sheetName; + } + } + + throw new RuntimeException(sprintf( + "Sheet ID %s does not exist in the Excel file", + $sheetId + )); + } + + public function getFirstSheetName() + { + if (empty($this->sheetInfo)) { + throw new RuntimeException('Workbook contains no sheets'); + } + + foreach ($this->sheetInfo as $sheetName => $sheetInfo) { + return $sheetName; + } + } + + protected function getSheetXML($name) + { + return simplexml_load_string( + $this->extractFile($this->sheetInfo[$name]['path']) + ); + } +} 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; + } +} |