diff --git a/RS/ExcelCal/SimpleXLSX.php b/RS/ExcelCal/SimpleXLSX.php new file mode 100644 index 0000000..c9776fb --- /dev/null +++ b/RS/ExcelCal/SimpleXLSX.php @@ -0,0 +1,1224 @@ +rows() as $r) { + * print_r( $r ); + * } + * } else { + * echo SimpleXLSX::parseError(); + * } + * + * Example 2: html table + * if ( $xlsx = SimpleXLSX::parse('book.xlsx') ) { + * echo $xlsx->toHTML(); + * } else { + * echo SimpleXLSX::parseError(); + * } + * + * Example 3: rowsEx + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * foreach ( $xlsx->rowsEx() as $r ) { + * print_r( $r ); + * } + * + * Example 4: select worksheet + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * foreach( $xlsx->rows(1) as $r ) { // second worksheet + * print_t( $r ); + * } + * + * Example 5: IDs and worksheet names + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * print_r( $xlsx->sheetNames() ); // array( 0 => 'Sheet 1', 1 => 'Catalog' ); + * + * Example 6: get sheet name by index + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * echo 'Sheet Name 2 = '.$xlsx->sheetName(1); + * + * Example 7: getCell (very slow) + * echo $xlsx->getCell(1,'D12'); // reads D12 cell from second sheet + * + * Example 8: read data + * if ( $xlsx = SimpleXLSX::parseData( file_get_contents('http://www.example.com/example.xlsx') ) ) { + * $dim = $xlsx->dimension(1); + * $num_cols = $dim[0]; + * $num_rows = $dim[1]; + * echo $xlsx->sheetName(1).':'.$num_cols.'x'.$num_rows; + * } else { + * echo SimpleXLSX::parseError(); + * } + * + * Example 9: old style + * $xlsx = new SimpleXLSX('book.xlsx'); + * if ( $xlsx->success() ) { + * print_r( $xlsx->rows() ); + * } else { + * echo 'xlsx error: '.$xlsx->error(); + * } + */ +class SimpleXLSX +{ + // Don't remove this string! Created by Sergey Shuchkin sergey.shuchkin@gmail.com + public static $CF = [ // Cell formats + 0 => 'General', + 1 => '0', + 2 => '0.00', + 3 => '#,##0', + 4 => '#,##0.00', + 9 => '0%', + 10 => '0.00%', + 11 => '0.00E+00', + 12 => '# ?/?', + 13 => '# ??/??', + 14 => 'mm-dd-yy', + 15 => 'd-mmm-yy', + 16 => 'd-mmm', + 17 => 'mmm-yy', + 18 => 'h:mm AM/PM', + 19 => 'h:mm:ss AM/PM', + 20 => 'h:mm', + 21 => 'h:mm:ss', + 22 => 'm/d/yy h:mm', + + 37 => '#,##0 ;(#,##0)', + 38 => '#,##0 ;[Red](#,##0)', + 39 => '#,##0.00;(#,##0.00)', + 40 => '#,##0.00;[Red](#,##0.00)', + + 44 => '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)', + 45 => 'mm:ss', + 46 => '[h]:mm:ss', + 47 => 'mmss.0', + 48 => '##0.0E+0', + 49 => '@', + + 27 => '[$-404]e/m/d', + 30 => 'm/d/yy', + 36 => '[$-404]e/m/d', + 50 => '[$-404]e/m/d', + 57 => '[$-404]e/m/d', + + 59 => 't0', + 60 => 't0.00', + 61 => 't#,##0', + 62 => 't#,##0.00', + 67 => 't0%', + 68 => 't0.00%', + 69 => 't# ?/?', + 70 => 't# ??/??', + ]; + public $nf = []; // number formats + public $cellFormats = []; // cellXfs + public $datetimeFormat = 'Y-m-d H:i:s'; + public $debug; + public $activeSheet = 0; + public $rowsExReader; + + /* @var SimpleXMLElement[] $sheets */ + protected $sheets; + protected $sheetNames = []; + protected $sheetFiles = []; + // scheme + public $styles; + protected $hyperlinks; + /* @var array[] $package */ + protected $package; + protected $sharedstrings; + protected $date1904 = 0; + + + /* + private $date_formats = array( + 0xe => "d/m/Y", + 0xf => "d-M-Y", + 0x10 => "d-M", + 0x11 => "M-Y", + 0x12 => "h:i a", + 0x13 => "h:i:s a", + 0x14 => "H:i", + 0x15 => "H:i:s", + 0x16 => "d/m/Y H:i", + 0x2d => "i:s", + 0x2e => "H:i:s", + 0x2f => "i:s.S" + ); + private $number_formats = array( + 0x1 => "%1.0f", // "0" + 0x2 => "%1.2f", // "0.00", + 0x3 => "%1.0f", //"#,##0", + 0x4 => "%1.2f", //"#,##0.00", + 0x5 => "%1.0f", //"$#,##0;($#,##0)", + 0x6 => '$%1.0f', //"$#,##0;($#,##0)", + 0x7 => '$%1.2f', //"$#,##0.00;($#,##0.00)", + 0x8 => '$%1.2f', //"$#,##0.00;($#,##0.00)", + 0x9 => '%1.0f%%', //"0%" + 0xa => '%1.2f%%', //"0.00%" + 0xb => '%1.2f', //"0.00E00", + 0x25 => '%1.0f', //"#,##0;(#,##0)", + 0x26 => '%1.0f', //"#,##0;(#,##0)", + 0x27 => '%1.2f', //"#,##0.00;(#,##0.00)", + 0x28 => '%1.2f', //"#,##0.00;(#,##0.00)", + 0x29 => '%1.0f', //"#,##0;(#,##0)", + 0x2a => '$%1.0f', //"$#,##0;($#,##0)", + 0x2b => '%1.2f', //"#,##0.00;(#,##0.00)", + 0x2c => '$%1.2f', //"$#,##0.00;($#,##0.00)", + 0x30 => '%1.0f'); //"##0.0E0"; + // }}} + */ + protected $errno = 0; + protected $error = false; + /** + * @var false|SimpleXMLElement + */ + public $theme; + + + public function __construct($filename = null, $is_data = null, $debug = null) + { + if ($debug !== null) { + $this->debug = $debug; + } + $this->package = [ + 'filename' => '', + 'mtime' => 0, + 'size' => 0, + 'comment' => '', + 'entries' => [] + ]; + if ($filename && $this->_unzip($filename, $is_data)) { + $this->_parse(); + } + } + + protected function _unzip($filename, $is_data = false) + { + + if ($is_data) { + $this->package['filename'] = 'default.xlsx'; + $this->package['mtime'] = time(); + $this->package['size'] = $this->_strlen($filename); + + $vZ = $filename; + } else { + if (!is_readable($filename)) { + $this->error(1, 'File not found ' . $filename); + + return false; + } + + // Package information + $this->package['filename'] = $filename; + $this->package['mtime'] = filemtime($filename); + $this->package['size'] = filesize($filename); + + // Read file + $vZ = file_get_contents($filename); + } + // Cut end of central directory + /* $aE = explode("\x50\x4b\x05\x06", $vZ); + + if (count($aE) == 1) { + $this->error('Unknown format'); + return false; + } + */ + // Explode to each part + $aE = explode("\x50\x4b\x03\x04", $vZ); + array_shift($aE); + + $aEL = count($aE); + if ($aEL === 0) { + $this->error(2, 'Unknown archive format'); + + return false; + } + // Search central directory end record + $last = $aE[$aEL - 1]; + $last = explode("\x50\x4b\x05\x06", $last); + if (count($last) !== 2) { + $this->error(2, 'Unknown archive format'); + + return false; + } + // Search central directory + $last = explode("\x50\x4b\x01\x02", $last[0]); + if (count($last) < 2) { + $this->error(2, 'Unknown archive format'); + + return false; + } + $aE[$aEL - 1] = $last[0]; + + // Loop through the entries + foreach ($aE as $vZ) { + $aI = []; + $aI['E'] = 0; + $aI['EM'] = ''; + // Retrieving local file header information +// $aP = unpack('v1VN/v1GPF/v1CM/v1FT/v1FD/V1CRC/V1CS/V1UCS/v1FNL', $vZ); + $aP = unpack('v1VN/v1GPF/v1CM/v1FT/v1FD/V1CRC/V1CS/V1UCS/v1FNL/v1EFL', $vZ); + + // Check if data is encrypted +// $bE = ($aP['GPF'] && 0x0001) ? TRUE : FALSE; +// $bE = false; + $nF = $aP['FNL']; + $mF = $aP['EFL']; + + // Special case : value block after the compressed data + if ($aP['GPF'] & 0x0008) { + $aP1 = unpack('V1CRC/V1CS/V1UCS', $this->_substr($vZ, -12)); + + $aP['CRC'] = $aP1['CRC']; + $aP['CS'] = $aP1['CS']; + $aP['UCS'] = $aP1['UCS']; + // 2013-08-10 + $vZ = $this->_substr($vZ, 0, -12); + if ($this->_substr($vZ, -4) === "\x50\x4b\x07\x08") { + $vZ = $this->_substr($vZ, 0, -4); + } + } + + // Getting stored filename + $aI['N'] = $this->_substr($vZ, 26, $nF); + $aI['N'] = str_replace('\\', '/', $aI['N']); + + if ($this->_substr($aI['N'], -1) === '/') { + // is a directory entry - will be skipped + continue; + } + + // Truncate full filename in path and filename + $aI['P'] = dirname($aI['N']); + $aI['P'] = ($aI['P'] === '.') ? '' : $aI['P']; + $aI['N'] = basename($aI['N']); + + $vZ = $this->_substr($vZ, 26 + $nF + $mF); + + if ($this->_strlen($vZ) !== (int)$aP['CS']) { // check only if availabled + $aI['E'] = 1; + $aI['EM'] = 'Compressed size is not equal with the value in header information.'; + } +// } elseif ( $bE ) { +// $aI['E'] = 5; +// $aI['EM'] = 'File is encrypted, which is not supported from this class.'; +/* } else { + switch ($aP['CM']) { + case 0: // Stored + // Here is nothing to do, the file ist flat. + break; + case 8: // Deflated + $vZ = gzinflate($vZ); + break; + case 12: // BZIP2 + if (extension_loaded('bz2')) { + $vZ = bzdecompress($vZ); + } else { + $aI['E'] = 7; + $aI['EM'] = 'PHP BZIP2 extension not available.'; + } + break; + default: + $aI['E'] = 6; + $aI['EM'] = "De-/Compression method {$aP['CM']} is not supported."; + } + if (!$aI['E']) { + if ($vZ === false) { + $aI['E'] = 2; + $aI['EM'] = 'Decompression of data failed.'; + } elseif ($this->_strlen($vZ) !== (int)$aP['UCS']) { + $aI['E'] = 3; + $aI['EM'] = 'Uncompressed size is not equal with the value in header information.'; + } elseif (crc32($vZ) !== $aP['CRC']) { + $aI['E'] = 4; + $aI['EM'] = 'CRC32 checksum is not equal with the value in header information.'; + } + } + } +*/ + + // DOS to UNIX timestamp + $aI['T'] = mktime( + ($aP['FT'] & 0xf800) >> 11, + ($aP['FT'] & 0x07e0) >> 5, + ($aP['FT'] & 0x001f) << 1, + ($aP['FD'] & 0x01e0) >> 5, + $aP['FD'] & 0x001f, + (($aP['FD'] & 0xfe00) >> 9) + 1980 + ); + + $this->package['entries'][] = [ + 'data' => $vZ, + 'ucs' => (int)$aP['UCS'], // ucompresses size + 'cm' => $aP['CM'], // compressed method + 'cs' => isset($aP['CS']) ? (int) $aP['CS'] : 0, // compresses size + 'crc' => $aP['CRC'], + 'error' => $aI['E'], + 'error_msg' => $aI['EM'], + 'name' => $aI['N'], + 'path' => $aI['P'], + 'time' => $aI['T'] + ]; + } // end for each entries + + return true; + } + + protected function _strlen($str) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_strlen($str, '8bit') : strlen($str); + } + + public function error($num = null, $str = null) + { + if ($num) { + $this->errno = $num; + $this->error = $str; + if ($this->debug) { + trigger_error(__CLASS__ . ': ' . $this->error, E_USER_WARNING); + } + } + + return $this->error; + } + + protected function _substr($str, $start, $length = null) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_substr($str, $start, ($length === null) ? mb_strlen($str, '8bit') : $length, '8bit') : substr($str, $start, ($length === null) ? strlen($str) : $length); + } + + protected function _parse() + { + // Document data holders + $this->sharedstrings = []; + $this->sheets = []; +// $this->styles = array(); +// $m1 = 0; // memory_get_peak_usage( true ); + // Read relations and search for officeDocument + if ($relations = $this->getEntryXML('_rels/.rels')) { + foreach ($relations->Relationship as $rel) { + $rel_type = basename(trim((string)$rel['Type'])); // officeDocument + $rel_target = $this->_getTarget('', (string)$rel['Target']); // /xl/workbook.xml or xl/workbook.xml + + if ($rel_type === 'officeDocument' && $workbook = $this->getEntryXML($rel_target)) { + $index_rId = []; // [0 => rId1] + + $index = 0; + foreach ($workbook->sheets->sheet as $s) { + $this->sheetNames[$index] = (string)$s['name']; + $index_rId[$index] = (string)$s['id']; + $index++; + } + if ((int)$workbook->workbookPr['date1904'] === 1) { + $this->date1904 = 1; + } + + + if ($workbookRelations = $this->getEntryXML(dirname($rel_target) . '/_rels/workbook.xml.rels')) { + // Loop relations for workbook and extract sheets... + foreach ($workbookRelations->Relationship as $workbookRelation) { + $wrel_type = basename(trim((string)$workbookRelation['Type'])); // worksheet + $wrel_path = $this->_getTarget(dirname($rel_target), (string)$workbookRelation['Target']); + if (!$this->entryExists($wrel_path)) { + continue; + } + + + if ($wrel_type === 'worksheet') { // Sheets + if ($sheet = $this->getEntryXML($wrel_path)) { + $index = array_search((string)$workbookRelation['Id'], $index_rId, true); + $this->sheets[$index] = $sheet; + $this->sheetFiles[$index] = $wrel_path; + } + } elseif ($wrel_type === 'sharedStrings') { + if ($sharedStrings = $this->getEntryXML($wrel_path)) { + foreach ($sharedStrings->si as $val) { + if (isset($val->t)) { + $this->sharedstrings[] = (string)$val->t; + } elseif (isset($val->r)) { + $this->sharedstrings[] = $this->_parseRichText($val); + } + } + } + } elseif ($wrel_type === 'styles') { + $this->styles = $this->getEntryXML($wrel_path); + + // number formats + $this->nf = []; + if (isset($this->styles->numFmts->numFmt)) { + foreach ($this->styles->numFmts->numFmt as $v) { + $this->nf[(int)$v['numFmtId']] = (string)$v['formatCode']; + } + } + + $this->cellFormats = []; + if (isset($this->styles->cellXfs->xf)) { + foreach ($this->styles->cellXfs->xf as $v) { + $x = [ + 'format' => null + ]; + foreach ($v->attributes() as $k1 => $v1) { + $x[ $k1 ] = (int) $v1; + } + if (isset($x['numFmtId'])) { + if (isset($this->nf[$x['numFmtId']])) { + $x['format'] = $this->nf[$x['numFmtId']]; + } elseif (isset(self::$CF[$x['numFmtId']])) { + $x['format'] = self::$CF[$x['numFmtId']]; + } + } + + $this->cellFormats[] = $x; + } + } + } elseif ($wrel_type === 'theme') { + $this->theme = $this->getEntryXML($wrel_path); + } + } + + break; + } + // reptile hack :: find active sheet from workbook.xml + foreach ($workbook->bookViews->workbookView as $s) { + if (!empty($s['activeTab'])) { + $this->activeSheet = (int)$s['activeTab']; + } + } + } + } + } + +// $m2 = memory_get_peak_usage(true); +// echo __FUNCTION__.' M='.round( ($m2-$m1) / 1048576, 2).'MB'.PHP_EOL; + + if (count($this->sheets)) { + // Sort sheets + ksort($this->sheets); + + return true; + } + + return false; + } + + public function getEntryXML($name) + { + if ($entry_xml = $this->getEntryData($name)) { + $this->deleteEntry($name); // economy memory + // dirty remove namespace prefixes and empty rows + $entry_xml = preg_replace('/xmlns[^=]*="[^"]*"/i', '', $entry_xml); // remove namespaces + $entry_xml .= ' '; // force run garbage collector + $entry_xml = preg_replace('/[a-zA-Z0-9]+:([a-zA-Z0-9]+="[^"]+")/', '$1', $entry_xml); // remove namespaced attrs + $entry_xml .= ' '; + $entry_xml = preg_replace('/<[a-zA-Z0-9]+:([^>]+)>/', '<$1>', $entry_xml); // fix namespaced openned tags + $entry_xml .= ' '; + $entry_xml = preg_replace('/<\/[a-zA-Z0-9]+:([^>]+)>/', '', $entry_xml); // fix namespaced closed tags + $entry_xml .= ' '; + + if (strpos($name, '/sheet')) { // dirty skip empty rows + // remove + $entry_xml = preg_replace('/]+>\s*(\s*)+<\/row>/', '', $entry_xml, -1, $cnt); + $entry_xml .= ' '; + // remove + $entry_xml = preg_replace('/]*\/>/', '', $entry_xml, -1, $cnt2); + $entry_xml .= ' '; + // remove + $entry_xml = preg_replace('/]*><\/row>/', '', $entry_xml, -1, $cnt3); + $entry_xml .= ' '; + if ($cnt || $cnt2 || $cnt3) { + $entry_xml = preg_replace('//', '', $entry_xml); + $entry_xml .= ' '; + } +// file_put_contents( basename( $name ), $entry_xml ); // @to do comment!!! + } + $entry_xml = trim($entry_xml); + +// $m1 = memory_get_usage(); + // XML External Entity (XXE) Prevention, libxml_disable_entity_loader deprecated in PHP 8 + if (LIBXML_VERSION < 20900 && function_exists('libxml_disable_entity_loader')) { + $_old = libxml_disable_entity_loader(); + } + + $_old_uie = libxml_use_internal_errors(true); + + $entry_xmlobj = simplexml_load_string($entry_xml, 'SimpleXMLElement', LIBXML_COMPACT | LIBXML_PARSEHUGE); + + libxml_use_internal_errors($_old_uie); + + if (LIBXML_VERSION < 20900 && function_exists('libxml_disable_entity_loader')) { + /** @noinspection PhpUndefinedVariableInspection */ + libxml_disable_entity_loader($_old); + } + +// $m2 = memory_get_usage(); +// echo round( ($m2-$m1) / (1024 * 1024), 2).' MB'.PHP_EOL; + + if ($entry_xmlobj) { + return $entry_xmlobj; + } + $e = libxml_get_last_error(); + if ($e) { + $this->error(3, 'XML-entry ' . $name . ' parser error ' . $e->message . ' line ' . $e->line); + } + } else { + $this->error(4, 'XML-entry not found ' . $name); + } + + return false; + } + + // sheets numeration: 1,2,3.... + + public function getEntryData($name) + { + $name = ltrim(str_replace('\\', '/', $name), '/'); + $dir = $this->_strtoupper(dirname($name)); + $name = $this->_strtoupper(basename($name)); + foreach ($this->package['entries'] as &$entry) { + if ($this->_strtoupper($entry['path']) === $dir && $this->_strtoupper($entry['name']) === $name) { + if ($entry['error']) { + return false; + } + switch ($entry['cm']) { + case -1: + case 0: // Stored + // Here is nothing to do, the file ist flat. + break; + case 8: // Deflated + $entry['data'] = gzinflate($entry['data']); + break; + case 12: // BZIP2 + if (extension_loaded('bz2')) { + $entry['data'] = bzdecompress($entry['data']); + } else { + $entry['error'] = 7; + $entry['error_message'] = 'PHP BZIP2 extension not available.'; + } + break; + default: + $entry['error'] = 6; + $entry['error_msg'] = 'De-/Compression method '.$entry['cm'].' is not supported.'; + } + if (!$entry['error'] && $entry['cm'] > -1) { + $entry['cm'] = -1; + if ($entry['data'] === false) { + $entry['error'] = 2; + $entry['error_msg'] = 'Decompression of data failed.'; + } elseif ($this->_strlen($entry['data']) !== (int)$entry['ucs']) { + $entry['error'] = 3; + $entry['error_msg'] = 'Uncompressed size is not equal with the value in header information.'; + } elseif (crc32($entry['data']) !== $entry['crc']) { + $entry['error'] = 4; + $entry['error_msg'] = 'CRC32 checksum is not equal with the value in header information.'; + } + } + + return $entry['data']; + } + } + unset($entry); + $this->error(5, 'Entry not found ' . ($dir ? $dir . '/' : '') . $name); + + return false; + } + public function deleteEntry($name) + { + $name = ltrim(str_replace('\\', '/', $name), '/'); + $dir = $this->_strtoupper(dirname($name)); + $name = $this->_strtoupper(basename($name)); + foreach ($this->package['entries'] as $k => $entry) { + if ($this->_strtoupper($entry['path']) === $dir && $this->_strtoupper($entry['name']) === $name) { + unset($this->package['entries'][$k]); + return true; + } + } + return false; + } + + protected function _strtoupper($str) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_strtoupper($str, '8bit') : strtoupper($str); + } + + protected function _getTarget($base, $target) + { + $target = trim($target); + if (strpos($target, '/') === 0) { + return $this->_substr($target, 1); + } + $target = ($base ? $base . '/' : '') . $target; + // a/b/../c -> a/c + $parts = explode('/', $target); + $abs = []; + foreach ($parts as $p) { + if ('.' === $p) { + continue; + } + if ('..' === $p) { + array_pop($abs); + } else { + $abs[] = $p; + } + } + return implode('/', $abs); + } + + /* + * @param string $name Filename in archive + * @return SimpleXMLElement|bool + */ + + public function entryExists($name) + { + // 0.6.6 + $dir = $this->_strtoupper(dirname($name)); + $name = $this->_strtoupper(basename($name)); + foreach ($this->package['entries'] as $entry) { + if ($this->_strtoupper($entry['path']) === $dir && $this->_strtoupper($entry['name']) === $name) { + return true; + } + } + + return false; + } + + protected function _parseRichText($is = null) + { + $value = []; + + if (isset($is->t)) { + $value[] = (string)$is->t; + } elseif (isset($is->r)) { + foreach ($is->r as $run) { + $value[] = (string)$run->t; + } + } + + return implode('', $value); + } + + public static function parseFile($filename, $debug = false) + { + return self::parse($filename, false, $debug); + } + + public static function parse($filename, $is_data = false, $debug = false) + { + $xlsx = new self(); + $xlsx->debug = $debug; + if ($xlsx->_unzip($filename, $is_data)) { + $xlsx->_parse(); + } + if ($xlsx->success()) { + return $xlsx; + } + self::parseError($xlsx->error()); + self::parseErrno($xlsx->errno()); + + return false; + } + + public function success() + { + return !$this->error; + } + + // https://github.com/shuchkin/simplexlsx#gets-extend-cell-info-by--rowsex + + public static function parseError($set = false) + { + static $error = false; + + return $set ? $error = $set : $error; + } + + public static function parseErrno($set = false) + { + static $errno = false; + + return $set ? $errno = $set : $errno; + } + + public function errno() + { + return $this->errno; + } + + public static function parseData($data, $debug = false) + { + return self::parse($data, true, $debug); + } + + + + public function worksheet($worksheetIndex = 0) + { + + + if (isset($this->sheets[$worksheetIndex])) { + $ws = $this->sheets[$worksheetIndex]; + + if (!isset($this->hyperlinks[$worksheetIndex]) && isset($ws->hyperlinks)) { + $this->hyperlinks[$worksheetIndex] = []; + $sheet_rels = str_replace('worksheets', 'worksheets/_rels', $this->sheetFiles[$worksheetIndex]) . '.rels'; + $link_ids = []; + + if ($rels = $this->getEntryXML($sheet_rels)) { + // hyperlink +// $rel_base = dirname( $sheet_rels ); + foreach ($rels->Relationship as $rel) { + $rel_type = basename(trim((string)$rel['Type'])); + if ($rel_type === 'hyperlink') { + $rel_id = (string)$rel['Id']; + $rel_target = (string)$rel['Target']; + $link_ids[$rel_id] = $rel_target; + } + } + } + foreach ($ws->hyperlinks->hyperlink as $hyperlink) { + $ref = (string)$hyperlink['ref']; + if ($this->_strpos($ref, ':') > 0) { // A1:A8 -> A1 + $ref = explode(':', $ref); + $ref = $ref[0]; + } +// $this->hyperlinks[ $worksheetIndex ][ $ref ] = (string) $hyperlink['display']; + $loc = (string)$hyperlink['location']; + $id = (string)$hyperlink['id']; + if ($id) { + $href = $link_ids[$id] . ($loc ? '#' . $loc : ''); + } else { + $href = $loc; + } + $this->hyperlinks[$worksheetIndex][$ref] = $href; + } + } + + return $ws; + } + $this->error(6, 'Worksheet not found ' . $worksheetIndex); + + return false; + } + + protected function _strpos($haystack, $needle, $offset = 0) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_strpos($haystack, $needle, $offset, '8bit') : strpos($haystack, $needle, $offset); + } + + /** + * returns [numCols,numRows] of worksheet + * + * @param int $worksheetIndex + * + * @return array + */ + public function dimension($worksheetIndex = 0) + { + + if (($ws = $this->worksheet($worksheetIndex)) === false) { + return [0, 0]; + } + /* @var SimpleXMLElement $ws */ + + $ref = (string)$ws->dimension['ref']; + + if ($this->_strpos($ref, ':') !== false) { + $d = explode(':', $ref); + $idx = $this->getIndex($d[1]); + + return [$idx[0] + 1, $idx[1] + 1]; + } + /* + if ( $ref !== '' ) { // 0.6.8 + $index = $this->getIndex( $ref ); + + return [ $index[0] + 1, $index[1] + 1 ]; + } + */ + + // slow method + $maxC = $maxR = 0; + foreach ($ws->sheetData->row as $row) { + foreach ($row->c as $c) { + $idx = $this->getIndex((string)$c['r']); + $x = $idx[0]; + $y = $idx[1]; + if ($x > 0) { + if ($x > $maxC) { + $maxC = $x; + } + if ($y > $maxR) { + $maxR = $y; + } + } + } + } + + return [$maxC + 1, $maxR + 1]; + } + + public function getIndex($cell = 'A1') + { + + if (preg_match('/([A-Z]+)(\d+)/', $cell, $m)) { + $col = $m[1]; + $row = $m[2]; + + $colLen = $this->_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]; + } + +// $this->error( 'Invalid cell index ' . $cell ); + + return [-1, -1]; + } + + public function value($cell) + { + // Determine data type + $dataType = (string)$cell['t']; + + if ($dataType === '' || $dataType === 'n') { // number + $s = (int)$cell['s']; + if ($s > 0 && isset($this->cellFormats[$s])) { + if (array_key_exists('format', $this->cellFormats[$s])) { + $format = $this->cellFormats[$s]['format']; + if (preg_match('/(m|AM|PM)/', preg_replace('/\"[^"]+\"/', '', $format))) { // [mm]onth,AM|PM + $dataType = 'D'; + } + } else { + $dataType = 'n'; + } + } + } + + $value = ''; + + switch ($dataType) { + case 's': + // Value is a shared string + if ((string)$cell->v !== '') { + $value = $this->sharedstrings[(int)$cell->v]; + } + break; + + case 'str': // formula? + if ((string)$cell->v !== '') { + $value = (string)$cell->v; + } + break; + + case 'b': + // Value is boolean + $value = (string)$cell->v; + if ($value === '0') { + $value = false; + } elseif ($value === '1') { + $value = true; + } else { + $value = (bool)$cell->v; + } + + break; + + case 'inlineStr': + // Value is rich text inline + $value = $this->_parseRichText($cell->is); + + break; + + case 'e': + // Value is an error message + if ((string)$cell->v !== '') { + $value = (string)$cell->v; + } + break; + + case 'D': + // Date as float + if (!empty($cell->v)) { + $value = $this->datetimeFormat ? gmdate($this->datetimeFormat, $this->unixstamp((float)$cell->v)) : (float)$cell->v; + } + break; + + case 'd': + // Date as ISO YYYY-MM-DD + if ((string)$cell->v !== '') { + $value = (string)$cell->v; + } + break; + + default: + // Value is a string + $value = (string)$cell->v; + + // Check for numeric values + if (is_numeric($value)) { + /** @noinspection TypeUnsafeComparisonInspection */ + if ($value == (int)$value) { + $value = (int)$value; + } /** @noinspection TypeUnsafeComparisonInspection */ elseif ($value == (float)$value) { + $value = (float)$value; + } + } + } + + return $value; + } + + public function unixstamp($excelDateTime) + { + + $d = floor($excelDateTime); // days since 1900 or 1904 + $t = $excelDateTime - $d; + + if ($this->date1904) { + $d += 1462; + } + + $t = (abs($d) > 0) ? ($d - 25569) * 86400 + round($t * 86400) : round($t * 86400); + + return (int)$t; + } + + public function href($worksheetIndex, $cell) + { + $ref = (string)$cell['r']; + return isset($this->hyperlinks[$worksheetIndex][$ref]) ? $this->hyperlinks[$worksheetIndex][$ref] : ''; + } + + public function toHTML($worksheetIndex = 0) + { + $s = ''; + foreach ($this->readRows($worksheetIndex) as $r) { + $s .= ''; + foreach ($r as $c) { + $s .= ''; + } + $s .= "\r\n"; + } + $s .= '
' . ($c === '' ? ' ' : htmlspecialchars($c, ENT_QUOTES)) . '
'; + + return $s; + } + public function toHTMLEx($worksheetIndex = 0) + { + $s = ''; + $y = 0; + foreach ($this->readRowsEx($worksheetIndex) as $r) { + $s .= ''; + $x = 0; + foreach ($r as $c) { + $tag = 'td'; + $css = $c['css']; + if ($y === 0) { + $tag = 'th'; + $css .= $c['width'] ? 'width: '.round($c['width'] * 0.47, 2).'em;' : ''; + } + + if ($x === 0 && $c['height']) { + $css .= 'height: '.round($c['height'] * 1.3333).'px;'; + } + $s .= '<'.$tag.' style="'.$css.'" nowrap>' . ($c['value'] === '' ? ' ' : htmlspecialchars($c['value'], ENT_QUOTES)) . ''; + $x++; + } + $s .= "\r\n"; + $y++; + } + $s .= '
'; + + return $s; + } + public function rows($worksheetIndex = 0, $limit = 0) + { + return iterator_to_array($this->readRows($worksheetIndex, $limit), false); + } + // thx Gonzo + /** + * @param $worksheetIndex + * @param $limit + * @return \Generator + */ + public function readRows($worksheetIndex = 0, $limit = 0) + { + + if (($ws = $this->worksheet($worksheetIndex)) === false) { + return; + } + $dim = $this->dimension($worksheetIndex); + $numCols = $dim[0]; + $numRows = $dim[1]; + + $emptyRow = []; + for ($i = 0; $i < $numCols; $i++) { + $emptyRow[] = ''; + } + + $curR = 0; + $_limit = $limit; + /* @var SimpleXMLElement $ws */ + foreach ($ws->sheetData->row as $row) { + $r = $emptyRow; + $curC = 0; + foreach ($row->c as $c) { + // detect skipped cols + $idx = $this->getIndex((string)$c['r']); + $x = $idx[0]; + $y = $idx[1]; + + if ($x > -1) { + $curC = $x; + while ($curR < $y) { + yield $emptyRow; + $curR++; + $_limit--; + if ($_limit === 0) { + return; + } + } + } + $r[$curC] = $this->value($c); + $curC++; + } + yield $r; + + $curR++; + $_limit--; + if ($_limit === 0) { + return; + } + } + while ($curR < $numRows) { + yield $emptyRow; + $curR++; + $_limit--; + if ($_limit === 0) { + return; + } + } + } + + public function rowsEx($worksheetIndex = 0, $limit = 0) + { + return iterator_to_array($this->readRowsEx($worksheetIndex, $limit), false); + } + // https://github.com/shuchkin/simplexlsx#gets-extend-cell-info-by--rowsex + /** + * @param $worksheetIndex + * @param $limit + * @return \Generator|null + */ + public function readRowsEx($worksheetIndex = 0, $limit = 0) + { + if (!$this->rowsExReader) { + require_once __DIR__ . '/SimpleXLSXEx.php'; + $this->rowsExReader = new SimpleXLSXEx($this); + } + return $this->rowsExReader->readRowsEx($worksheetIndex, $limit); + } + + /** + * Returns cell value + * VERY SLOW! Use ->rows() or ->rowsEx() + * + * @param int $worksheetIndex + * @param string|array $cell ref or coords, D12 or [3,12] + * + * @return mixed Returns NULL if not found + */ + public function getCell($worksheetIndex = 0, $cell = 'A1') + { + + if (($ws = $this->worksheet($worksheetIndex)) === false) { + return false; + } + if (is_array($cell)) { + $cell = $this->_num2name($cell[0]) . $cell[1];// [3,21] -> D21 + } + if (is_string($cell)) { + $result = $ws->sheetData->xpath("row/c[@r='" . $cell . "']"); + if (count($result)) { + return $this->value($result[0]); + } + } + + return null; + } + + protected function _num2name($num) + { + $numeric = ($num - 1) % 26; + $letter = chr(65 + $numeric); + $num2 = (int)(($num - 1) / 26); + if ($num2 > 0) { + return $this->_num2name($num2) . $letter; + } + return $letter; + } + + public function getSheets() + { + return $this->sheets; + } + + public function sheetsCount() + { + return count($this->sheets); + } + + public function sheetName($worksheetIndex) + { + if (isset($this->sheetNames[$worksheetIndex])) { + return $this->sheetNames[$worksheetIndex]; + } + + return false; + } + + public function sheetNames() + { + + return $this->sheetNames; + } + + public function getStyles() + { + return $this->styles; + } + + public function getPackage() + { + return $this->package; + } + + public function setDateTimeFormat($value) + { + $this->datetimeFormat = is_string($value) ? $value : false; + } +} diff --git a/RS/ExcelCal/SimpleXLSX.php b/RS/ExcelCal/SimpleXLSX.php new file mode 100644 index 0000000..c9776fb --- /dev/null +++ b/RS/ExcelCal/SimpleXLSX.php @@ -0,0 +1,1224 @@ +rows() as $r) { + * print_r( $r ); + * } + * } else { + * echo SimpleXLSX::parseError(); + * } + * + * Example 2: html table + * if ( $xlsx = SimpleXLSX::parse('book.xlsx') ) { + * echo $xlsx->toHTML(); + * } else { + * echo SimpleXLSX::parseError(); + * } + * + * Example 3: rowsEx + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * foreach ( $xlsx->rowsEx() as $r ) { + * print_r( $r ); + * } + * + * Example 4: select worksheet + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * foreach( $xlsx->rows(1) as $r ) { // second worksheet + * print_t( $r ); + * } + * + * Example 5: IDs and worksheet names + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * print_r( $xlsx->sheetNames() ); // array( 0 => 'Sheet 1', 1 => 'Catalog' ); + * + * Example 6: get sheet name by index + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * echo 'Sheet Name 2 = '.$xlsx->sheetName(1); + * + * Example 7: getCell (very slow) + * echo $xlsx->getCell(1,'D12'); // reads D12 cell from second sheet + * + * Example 8: read data + * if ( $xlsx = SimpleXLSX::parseData( file_get_contents('http://www.example.com/example.xlsx') ) ) { + * $dim = $xlsx->dimension(1); + * $num_cols = $dim[0]; + * $num_rows = $dim[1]; + * echo $xlsx->sheetName(1).':'.$num_cols.'x'.$num_rows; + * } else { + * echo SimpleXLSX::parseError(); + * } + * + * Example 9: old style + * $xlsx = new SimpleXLSX('book.xlsx'); + * if ( $xlsx->success() ) { + * print_r( $xlsx->rows() ); + * } else { + * echo 'xlsx error: '.$xlsx->error(); + * } + */ +class SimpleXLSX +{ + // Don't remove this string! Created by Sergey Shuchkin sergey.shuchkin@gmail.com + public static $CF = [ // Cell formats + 0 => 'General', + 1 => '0', + 2 => '0.00', + 3 => '#,##0', + 4 => '#,##0.00', + 9 => '0%', + 10 => '0.00%', + 11 => '0.00E+00', + 12 => '# ?/?', + 13 => '# ??/??', + 14 => 'mm-dd-yy', + 15 => 'd-mmm-yy', + 16 => 'd-mmm', + 17 => 'mmm-yy', + 18 => 'h:mm AM/PM', + 19 => 'h:mm:ss AM/PM', + 20 => 'h:mm', + 21 => 'h:mm:ss', + 22 => 'm/d/yy h:mm', + + 37 => '#,##0 ;(#,##0)', + 38 => '#,##0 ;[Red](#,##0)', + 39 => '#,##0.00;(#,##0.00)', + 40 => '#,##0.00;[Red](#,##0.00)', + + 44 => '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)', + 45 => 'mm:ss', + 46 => '[h]:mm:ss', + 47 => 'mmss.0', + 48 => '##0.0E+0', + 49 => '@', + + 27 => '[$-404]e/m/d', + 30 => 'm/d/yy', + 36 => '[$-404]e/m/d', + 50 => '[$-404]e/m/d', + 57 => '[$-404]e/m/d', + + 59 => 't0', + 60 => 't0.00', + 61 => 't#,##0', + 62 => 't#,##0.00', + 67 => 't0%', + 68 => 't0.00%', + 69 => 't# ?/?', + 70 => 't# ??/??', + ]; + public $nf = []; // number formats + public $cellFormats = []; // cellXfs + public $datetimeFormat = 'Y-m-d H:i:s'; + public $debug; + public $activeSheet = 0; + public $rowsExReader; + + /* @var SimpleXMLElement[] $sheets */ + protected $sheets; + protected $sheetNames = []; + protected $sheetFiles = []; + // scheme + public $styles; + protected $hyperlinks; + /* @var array[] $package */ + protected $package; + protected $sharedstrings; + protected $date1904 = 0; + + + /* + private $date_formats = array( + 0xe => "d/m/Y", + 0xf => "d-M-Y", + 0x10 => "d-M", + 0x11 => "M-Y", + 0x12 => "h:i a", + 0x13 => "h:i:s a", + 0x14 => "H:i", + 0x15 => "H:i:s", + 0x16 => "d/m/Y H:i", + 0x2d => "i:s", + 0x2e => "H:i:s", + 0x2f => "i:s.S" + ); + private $number_formats = array( + 0x1 => "%1.0f", // "0" + 0x2 => "%1.2f", // "0.00", + 0x3 => "%1.0f", //"#,##0", + 0x4 => "%1.2f", //"#,##0.00", + 0x5 => "%1.0f", //"$#,##0;($#,##0)", + 0x6 => '$%1.0f', //"$#,##0;($#,##0)", + 0x7 => '$%1.2f', //"$#,##0.00;($#,##0.00)", + 0x8 => '$%1.2f', //"$#,##0.00;($#,##0.00)", + 0x9 => '%1.0f%%', //"0%" + 0xa => '%1.2f%%', //"0.00%" + 0xb => '%1.2f', //"0.00E00", + 0x25 => '%1.0f', //"#,##0;(#,##0)", + 0x26 => '%1.0f', //"#,##0;(#,##0)", + 0x27 => '%1.2f', //"#,##0.00;(#,##0.00)", + 0x28 => '%1.2f', //"#,##0.00;(#,##0.00)", + 0x29 => '%1.0f', //"#,##0;(#,##0)", + 0x2a => '$%1.0f', //"$#,##0;($#,##0)", + 0x2b => '%1.2f', //"#,##0.00;(#,##0.00)", + 0x2c => '$%1.2f', //"$#,##0.00;($#,##0.00)", + 0x30 => '%1.0f'); //"##0.0E0"; + // }}} + */ + protected $errno = 0; + protected $error = false; + /** + * @var false|SimpleXMLElement + */ + public $theme; + + + public function __construct($filename = null, $is_data = null, $debug = null) + { + if ($debug !== null) { + $this->debug = $debug; + } + $this->package = [ + 'filename' => '', + 'mtime' => 0, + 'size' => 0, + 'comment' => '', + 'entries' => [] + ]; + if ($filename && $this->_unzip($filename, $is_data)) { + $this->_parse(); + } + } + + protected function _unzip($filename, $is_data = false) + { + + if ($is_data) { + $this->package['filename'] = 'default.xlsx'; + $this->package['mtime'] = time(); + $this->package['size'] = $this->_strlen($filename); + + $vZ = $filename; + } else { + if (!is_readable($filename)) { + $this->error(1, 'File not found ' . $filename); + + return false; + } + + // Package information + $this->package['filename'] = $filename; + $this->package['mtime'] = filemtime($filename); + $this->package['size'] = filesize($filename); + + // Read file + $vZ = file_get_contents($filename); + } + // Cut end of central directory + /* $aE = explode("\x50\x4b\x05\x06", $vZ); + + if (count($aE) == 1) { + $this->error('Unknown format'); + return false; + } + */ + // Explode to each part + $aE = explode("\x50\x4b\x03\x04", $vZ); + array_shift($aE); + + $aEL = count($aE); + if ($aEL === 0) { + $this->error(2, 'Unknown archive format'); + + return false; + } + // Search central directory end record + $last = $aE[$aEL - 1]; + $last = explode("\x50\x4b\x05\x06", $last); + if (count($last) !== 2) { + $this->error(2, 'Unknown archive format'); + + return false; + } + // Search central directory + $last = explode("\x50\x4b\x01\x02", $last[0]); + if (count($last) < 2) { + $this->error(2, 'Unknown archive format'); + + return false; + } + $aE[$aEL - 1] = $last[0]; + + // Loop through the entries + foreach ($aE as $vZ) { + $aI = []; + $aI['E'] = 0; + $aI['EM'] = ''; + // Retrieving local file header information +// $aP = unpack('v1VN/v1GPF/v1CM/v1FT/v1FD/V1CRC/V1CS/V1UCS/v1FNL', $vZ); + $aP = unpack('v1VN/v1GPF/v1CM/v1FT/v1FD/V1CRC/V1CS/V1UCS/v1FNL/v1EFL', $vZ); + + // Check if data is encrypted +// $bE = ($aP['GPF'] && 0x0001) ? TRUE : FALSE; +// $bE = false; + $nF = $aP['FNL']; + $mF = $aP['EFL']; + + // Special case : value block after the compressed data + if ($aP['GPF'] & 0x0008) { + $aP1 = unpack('V1CRC/V1CS/V1UCS', $this->_substr($vZ, -12)); + + $aP['CRC'] = $aP1['CRC']; + $aP['CS'] = $aP1['CS']; + $aP['UCS'] = $aP1['UCS']; + // 2013-08-10 + $vZ = $this->_substr($vZ, 0, -12); + if ($this->_substr($vZ, -4) === "\x50\x4b\x07\x08") { + $vZ = $this->_substr($vZ, 0, -4); + } + } + + // Getting stored filename + $aI['N'] = $this->_substr($vZ, 26, $nF); + $aI['N'] = str_replace('\\', '/', $aI['N']); + + if ($this->_substr($aI['N'], -1) === '/') { + // is a directory entry - will be skipped + continue; + } + + // Truncate full filename in path and filename + $aI['P'] = dirname($aI['N']); + $aI['P'] = ($aI['P'] === '.') ? '' : $aI['P']; + $aI['N'] = basename($aI['N']); + + $vZ = $this->_substr($vZ, 26 + $nF + $mF); + + if ($this->_strlen($vZ) !== (int)$aP['CS']) { // check only if availabled + $aI['E'] = 1; + $aI['EM'] = 'Compressed size is not equal with the value in header information.'; + } +// } elseif ( $bE ) { +// $aI['E'] = 5; +// $aI['EM'] = 'File is encrypted, which is not supported from this class.'; +/* } else { + switch ($aP['CM']) { + case 0: // Stored + // Here is nothing to do, the file ist flat. + break; + case 8: // Deflated + $vZ = gzinflate($vZ); + break; + case 12: // BZIP2 + if (extension_loaded('bz2')) { + $vZ = bzdecompress($vZ); + } else { + $aI['E'] = 7; + $aI['EM'] = 'PHP BZIP2 extension not available.'; + } + break; + default: + $aI['E'] = 6; + $aI['EM'] = "De-/Compression method {$aP['CM']} is not supported."; + } + if (!$aI['E']) { + if ($vZ === false) { + $aI['E'] = 2; + $aI['EM'] = 'Decompression of data failed.'; + } elseif ($this->_strlen($vZ) !== (int)$aP['UCS']) { + $aI['E'] = 3; + $aI['EM'] = 'Uncompressed size is not equal with the value in header information.'; + } elseif (crc32($vZ) !== $aP['CRC']) { + $aI['E'] = 4; + $aI['EM'] = 'CRC32 checksum is not equal with the value in header information.'; + } + } + } +*/ + + // DOS to UNIX timestamp + $aI['T'] = mktime( + ($aP['FT'] & 0xf800) >> 11, + ($aP['FT'] & 0x07e0) >> 5, + ($aP['FT'] & 0x001f) << 1, + ($aP['FD'] & 0x01e0) >> 5, + $aP['FD'] & 0x001f, + (($aP['FD'] & 0xfe00) >> 9) + 1980 + ); + + $this->package['entries'][] = [ + 'data' => $vZ, + 'ucs' => (int)$aP['UCS'], // ucompresses size + 'cm' => $aP['CM'], // compressed method + 'cs' => isset($aP['CS']) ? (int) $aP['CS'] : 0, // compresses size + 'crc' => $aP['CRC'], + 'error' => $aI['E'], + 'error_msg' => $aI['EM'], + 'name' => $aI['N'], + 'path' => $aI['P'], + 'time' => $aI['T'] + ]; + } // end for each entries + + return true; + } + + protected function _strlen($str) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_strlen($str, '8bit') : strlen($str); + } + + public function error($num = null, $str = null) + { + if ($num) { + $this->errno = $num; + $this->error = $str; + if ($this->debug) { + trigger_error(__CLASS__ . ': ' . $this->error, E_USER_WARNING); + } + } + + return $this->error; + } + + protected function _substr($str, $start, $length = null) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_substr($str, $start, ($length === null) ? mb_strlen($str, '8bit') : $length, '8bit') : substr($str, $start, ($length === null) ? strlen($str) : $length); + } + + protected function _parse() + { + // Document data holders + $this->sharedstrings = []; + $this->sheets = []; +// $this->styles = array(); +// $m1 = 0; // memory_get_peak_usage( true ); + // Read relations and search for officeDocument + if ($relations = $this->getEntryXML('_rels/.rels')) { + foreach ($relations->Relationship as $rel) { + $rel_type = basename(trim((string)$rel['Type'])); // officeDocument + $rel_target = $this->_getTarget('', (string)$rel['Target']); // /xl/workbook.xml or xl/workbook.xml + + if ($rel_type === 'officeDocument' && $workbook = $this->getEntryXML($rel_target)) { + $index_rId = []; // [0 => rId1] + + $index = 0; + foreach ($workbook->sheets->sheet as $s) { + $this->sheetNames[$index] = (string)$s['name']; + $index_rId[$index] = (string)$s['id']; + $index++; + } + if ((int)$workbook->workbookPr['date1904'] === 1) { + $this->date1904 = 1; + } + + + if ($workbookRelations = $this->getEntryXML(dirname($rel_target) . '/_rels/workbook.xml.rels')) { + // Loop relations for workbook and extract sheets... + foreach ($workbookRelations->Relationship as $workbookRelation) { + $wrel_type = basename(trim((string)$workbookRelation['Type'])); // worksheet + $wrel_path = $this->_getTarget(dirname($rel_target), (string)$workbookRelation['Target']); + if (!$this->entryExists($wrel_path)) { + continue; + } + + + if ($wrel_type === 'worksheet') { // Sheets + if ($sheet = $this->getEntryXML($wrel_path)) { + $index = array_search((string)$workbookRelation['Id'], $index_rId, true); + $this->sheets[$index] = $sheet; + $this->sheetFiles[$index] = $wrel_path; + } + } elseif ($wrel_type === 'sharedStrings') { + if ($sharedStrings = $this->getEntryXML($wrel_path)) { + foreach ($sharedStrings->si as $val) { + if (isset($val->t)) { + $this->sharedstrings[] = (string)$val->t; + } elseif (isset($val->r)) { + $this->sharedstrings[] = $this->_parseRichText($val); + } + } + } + } elseif ($wrel_type === 'styles') { + $this->styles = $this->getEntryXML($wrel_path); + + // number formats + $this->nf = []; + if (isset($this->styles->numFmts->numFmt)) { + foreach ($this->styles->numFmts->numFmt as $v) { + $this->nf[(int)$v['numFmtId']] = (string)$v['formatCode']; + } + } + + $this->cellFormats = []; + if (isset($this->styles->cellXfs->xf)) { + foreach ($this->styles->cellXfs->xf as $v) { + $x = [ + 'format' => null + ]; + foreach ($v->attributes() as $k1 => $v1) { + $x[ $k1 ] = (int) $v1; + } + if (isset($x['numFmtId'])) { + if (isset($this->nf[$x['numFmtId']])) { + $x['format'] = $this->nf[$x['numFmtId']]; + } elseif (isset(self::$CF[$x['numFmtId']])) { + $x['format'] = self::$CF[$x['numFmtId']]; + } + } + + $this->cellFormats[] = $x; + } + } + } elseif ($wrel_type === 'theme') { + $this->theme = $this->getEntryXML($wrel_path); + } + } + + break; + } + // reptile hack :: find active sheet from workbook.xml + foreach ($workbook->bookViews->workbookView as $s) { + if (!empty($s['activeTab'])) { + $this->activeSheet = (int)$s['activeTab']; + } + } + } + } + } + +// $m2 = memory_get_peak_usage(true); +// echo __FUNCTION__.' M='.round( ($m2-$m1) / 1048576, 2).'MB'.PHP_EOL; + + if (count($this->sheets)) { + // Sort sheets + ksort($this->sheets); + + return true; + } + + return false; + } + + public function getEntryXML($name) + { + if ($entry_xml = $this->getEntryData($name)) { + $this->deleteEntry($name); // economy memory + // dirty remove namespace prefixes and empty rows + $entry_xml = preg_replace('/xmlns[^=]*="[^"]*"/i', '', $entry_xml); // remove namespaces + $entry_xml .= ' '; // force run garbage collector + $entry_xml = preg_replace('/[a-zA-Z0-9]+:([a-zA-Z0-9]+="[^"]+")/', '$1', $entry_xml); // remove namespaced attrs + $entry_xml .= ' '; + $entry_xml = preg_replace('/<[a-zA-Z0-9]+:([^>]+)>/', '<$1>', $entry_xml); // fix namespaced openned tags + $entry_xml .= ' '; + $entry_xml = preg_replace('/<\/[a-zA-Z0-9]+:([^>]+)>/', '', $entry_xml); // fix namespaced closed tags + $entry_xml .= ' '; + + if (strpos($name, '/sheet')) { // dirty skip empty rows + // remove + $entry_xml = preg_replace('/]+>\s*(\s*)+<\/row>/', '', $entry_xml, -1, $cnt); + $entry_xml .= ' '; + // remove + $entry_xml = preg_replace('/]*\/>/', '', $entry_xml, -1, $cnt2); + $entry_xml .= ' '; + // remove + $entry_xml = preg_replace('/]*><\/row>/', '', $entry_xml, -1, $cnt3); + $entry_xml .= ' '; + if ($cnt || $cnt2 || $cnt3) { + $entry_xml = preg_replace('//', '', $entry_xml); + $entry_xml .= ' '; + } +// file_put_contents( basename( $name ), $entry_xml ); // @to do comment!!! + } + $entry_xml = trim($entry_xml); + +// $m1 = memory_get_usage(); + // XML External Entity (XXE) Prevention, libxml_disable_entity_loader deprecated in PHP 8 + if (LIBXML_VERSION < 20900 && function_exists('libxml_disable_entity_loader')) { + $_old = libxml_disable_entity_loader(); + } + + $_old_uie = libxml_use_internal_errors(true); + + $entry_xmlobj = simplexml_load_string($entry_xml, 'SimpleXMLElement', LIBXML_COMPACT | LIBXML_PARSEHUGE); + + libxml_use_internal_errors($_old_uie); + + if (LIBXML_VERSION < 20900 && function_exists('libxml_disable_entity_loader')) { + /** @noinspection PhpUndefinedVariableInspection */ + libxml_disable_entity_loader($_old); + } + +// $m2 = memory_get_usage(); +// echo round( ($m2-$m1) / (1024 * 1024), 2).' MB'.PHP_EOL; + + if ($entry_xmlobj) { + return $entry_xmlobj; + } + $e = libxml_get_last_error(); + if ($e) { + $this->error(3, 'XML-entry ' . $name . ' parser error ' . $e->message . ' line ' . $e->line); + } + } else { + $this->error(4, 'XML-entry not found ' . $name); + } + + return false; + } + + // sheets numeration: 1,2,3.... + + public function getEntryData($name) + { + $name = ltrim(str_replace('\\', '/', $name), '/'); + $dir = $this->_strtoupper(dirname($name)); + $name = $this->_strtoupper(basename($name)); + foreach ($this->package['entries'] as &$entry) { + if ($this->_strtoupper($entry['path']) === $dir && $this->_strtoupper($entry['name']) === $name) { + if ($entry['error']) { + return false; + } + switch ($entry['cm']) { + case -1: + case 0: // Stored + // Here is nothing to do, the file ist flat. + break; + case 8: // Deflated + $entry['data'] = gzinflate($entry['data']); + break; + case 12: // BZIP2 + if (extension_loaded('bz2')) { + $entry['data'] = bzdecompress($entry['data']); + } else { + $entry['error'] = 7; + $entry['error_message'] = 'PHP BZIP2 extension not available.'; + } + break; + default: + $entry['error'] = 6; + $entry['error_msg'] = 'De-/Compression method '.$entry['cm'].' is not supported.'; + } + if (!$entry['error'] && $entry['cm'] > -1) { + $entry['cm'] = -1; + if ($entry['data'] === false) { + $entry['error'] = 2; + $entry['error_msg'] = 'Decompression of data failed.'; + } elseif ($this->_strlen($entry['data']) !== (int)$entry['ucs']) { + $entry['error'] = 3; + $entry['error_msg'] = 'Uncompressed size is not equal with the value in header information.'; + } elseif (crc32($entry['data']) !== $entry['crc']) { + $entry['error'] = 4; + $entry['error_msg'] = 'CRC32 checksum is not equal with the value in header information.'; + } + } + + return $entry['data']; + } + } + unset($entry); + $this->error(5, 'Entry not found ' . ($dir ? $dir . '/' : '') . $name); + + return false; + } + public function deleteEntry($name) + { + $name = ltrim(str_replace('\\', '/', $name), '/'); + $dir = $this->_strtoupper(dirname($name)); + $name = $this->_strtoupper(basename($name)); + foreach ($this->package['entries'] as $k => $entry) { + if ($this->_strtoupper($entry['path']) === $dir && $this->_strtoupper($entry['name']) === $name) { + unset($this->package['entries'][$k]); + return true; + } + } + return false; + } + + protected function _strtoupper($str) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_strtoupper($str, '8bit') : strtoupper($str); + } + + protected function _getTarget($base, $target) + { + $target = trim($target); + if (strpos($target, '/') === 0) { + return $this->_substr($target, 1); + } + $target = ($base ? $base . '/' : '') . $target; + // a/b/../c -> a/c + $parts = explode('/', $target); + $abs = []; + foreach ($parts as $p) { + if ('.' === $p) { + continue; + } + if ('..' === $p) { + array_pop($abs); + } else { + $abs[] = $p; + } + } + return implode('/', $abs); + } + + /* + * @param string $name Filename in archive + * @return SimpleXMLElement|bool + */ + + public function entryExists($name) + { + // 0.6.6 + $dir = $this->_strtoupper(dirname($name)); + $name = $this->_strtoupper(basename($name)); + foreach ($this->package['entries'] as $entry) { + if ($this->_strtoupper($entry['path']) === $dir && $this->_strtoupper($entry['name']) === $name) { + return true; + } + } + + return false; + } + + protected function _parseRichText($is = null) + { + $value = []; + + if (isset($is->t)) { + $value[] = (string)$is->t; + } elseif (isset($is->r)) { + foreach ($is->r as $run) { + $value[] = (string)$run->t; + } + } + + return implode('', $value); + } + + public static function parseFile($filename, $debug = false) + { + return self::parse($filename, false, $debug); + } + + public static function parse($filename, $is_data = false, $debug = false) + { + $xlsx = new self(); + $xlsx->debug = $debug; + if ($xlsx->_unzip($filename, $is_data)) { + $xlsx->_parse(); + } + if ($xlsx->success()) { + return $xlsx; + } + self::parseError($xlsx->error()); + self::parseErrno($xlsx->errno()); + + return false; + } + + public function success() + { + return !$this->error; + } + + // https://github.com/shuchkin/simplexlsx#gets-extend-cell-info-by--rowsex + + public static function parseError($set = false) + { + static $error = false; + + return $set ? $error = $set : $error; + } + + public static function parseErrno($set = false) + { + static $errno = false; + + return $set ? $errno = $set : $errno; + } + + public function errno() + { + return $this->errno; + } + + public static function parseData($data, $debug = false) + { + return self::parse($data, true, $debug); + } + + + + public function worksheet($worksheetIndex = 0) + { + + + if (isset($this->sheets[$worksheetIndex])) { + $ws = $this->sheets[$worksheetIndex]; + + if (!isset($this->hyperlinks[$worksheetIndex]) && isset($ws->hyperlinks)) { + $this->hyperlinks[$worksheetIndex] = []; + $sheet_rels = str_replace('worksheets', 'worksheets/_rels', $this->sheetFiles[$worksheetIndex]) . '.rels'; + $link_ids = []; + + if ($rels = $this->getEntryXML($sheet_rels)) { + // hyperlink +// $rel_base = dirname( $sheet_rels ); + foreach ($rels->Relationship as $rel) { + $rel_type = basename(trim((string)$rel['Type'])); + if ($rel_type === 'hyperlink') { + $rel_id = (string)$rel['Id']; + $rel_target = (string)$rel['Target']; + $link_ids[$rel_id] = $rel_target; + } + } + } + foreach ($ws->hyperlinks->hyperlink as $hyperlink) { + $ref = (string)$hyperlink['ref']; + if ($this->_strpos($ref, ':') > 0) { // A1:A8 -> A1 + $ref = explode(':', $ref); + $ref = $ref[0]; + } +// $this->hyperlinks[ $worksheetIndex ][ $ref ] = (string) $hyperlink['display']; + $loc = (string)$hyperlink['location']; + $id = (string)$hyperlink['id']; + if ($id) { + $href = $link_ids[$id] . ($loc ? '#' . $loc : ''); + } else { + $href = $loc; + } + $this->hyperlinks[$worksheetIndex][$ref] = $href; + } + } + + return $ws; + } + $this->error(6, 'Worksheet not found ' . $worksheetIndex); + + return false; + } + + protected function _strpos($haystack, $needle, $offset = 0) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_strpos($haystack, $needle, $offset, '8bit') : strpos($haystack, $needle, $offset); + } + + /** + * returns [numCols,numRows] of worksheet + * + * @param int $worksheetIndex + * + * @return array + */ + public function dimension($worksheetIndex = 0) + { + + if (($ws = $this->worksheet($worksheetIndex)) === false) { + return [0, 0]; + } + /* @var SimpleXMLElement $ws */ + + $ref = (string)$ws->dimension['ref']; + + if ($this->_strpos($ref, ':') !== false) { + $d = explode(':', $ref); + $idx = $this->getIndex($d[1]); + + return [$idx[0] + 1, $idx[1] + 1]; + } + /* + if ( $ref !== '' ) { // 0.6.8 + $index = $this->getIndex( $ref ); + + return [ $index[0] + 1, $index[1] + 1 ]; + } + */ + + // slow method + $maxC = $maxR = 0; + foreach ($ws->sheetData->row as $row) { + foreach ($row->c as $c) { + $idx = $this->getIndex((string)$c['r']); + $x = $idx[0]; + $y = $idx[1]; + if ($x > 0) { + if ($x > $maxC) { + $maxC = $x; + } + if ($y > $maxR) { + $maxR = $y; + } + } + } + } + + return [$maxC + 1, $maxR + 1]; + } + + public function getIndex($cell = 'A1') + { + + if (preg_match('/([A-Z]+)(\d+)/', $cell, $m)) { + $col = $m[1]; + $row = $m[2]; + + $colLen = $this->_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]; + } + +// $this->error( 'Invalid cell index ' . $cell ); + + return [-1, -1]; + } + + public function value($cell) + { + // Determine data type + $dataType = (string)$cell['t']; + + if ($dataType === '' || $dataType === 'n') { // number + $s = (int)$cell['s']; + if ($s > 0 && isset($this->cellFormats[$s])) { + if (array_key_exists('format', $this->cellFormats[$s])) { + $format = $this->cellFormats[$s]['format']; + if (preg_match('/(m|AM|PM)/', preg_replace('/\"[^"]+\"/', '', $format))) { // [mm]onth,AM|PM + $dataType = 'D'; + } + } else { + $dataType = 'n'; + } + } + } + + $value = ''; + + switch ($dataType) { + case 's': + // Value is a shared string + if ((string)$cell->v !== '') { + $value = $this->sharedstrings[(int)$cell->v]; + } + break; + + case 'str': // formula? + if ((string)$cell->v !== '') { + $value = (string)$cell->v; + } + break; + + case 'b': + // Value is boolean + $value = (string)$cell->v; + if ($value === '0') { + $value = false; + } elseif ($value === '1') { + $value = true; + } else { + $value = (bool)$cell->v; + } + + break; + + case 'inlineStr': + // Value is rich text inline + $value = $this->_parseRichText($cell->is); + + break; + + case 'e': + // Value is an error message + if ((string)$cell->v !== '') { + $value = (string)$cell->v; + } + break; + + case 'D': + // Date as float + if (!empty($cell->v)) { + $value = $this->datetimeFormat ? gmdate($this->datetimeFormat, $this->unixstamp((float)$cell->v)) : (float)$cell->v; + } + break; + + case 'd': + // Date as ISO YYYY-MM-DD + if ((string)$cell->v !== '') { + $value = (string)$cell->v; + } + break; + + default: + // Value is a string + $value = (string)$cell->v; + + // Check for numeric values + if (is_numeric($value)) { + /** @noinspection TypeUnsafeComparisonInspection */ + if ($value == (int)$value) { + $value = (int)$value; + } /** @noinspection TypeUnsafeComparisonInspection */ elseif ($value == (float)$value) { + $value = (float)$value; + } + } + } + + return $value; + } + + public function unixstamp($excelDateTime) + { + + $d = floor($excelDateTime); // days since 1900 or 1904 + $t = $excelDateTime - $d; + + if ($this->date1904) { + $d += 1462; + } + + $t = (abs($d) > 0) ? ($d - 25569) * 86400 + round($t * 86400) : round($t * 86400); + + return (int)$t; + } + + public function href($worksheetIndex, $cell) + { + $ref = (string)$cell['r']; + return isset($this->hyperlinks[$worksheetIndex][$ref]) ? $this->hyperlinks[$worksheetIndex][$ref] : ''; + } + + public function toHTML($worksheetIndex = 0) + { + $s = ''; + foreach ($this->readRows($worksheetIndex) as $r) { + $s .= ''; + foreach ($r as $c) { + $s .= ''; + } + $s .= "\r\n"; + } + $s .= '
' . ($c === '' ? ' ' : htmlspecialchars($c, ENT_QUOTES)) . '
'; + + return $s; + } + public function toHTMLEx($worksheetIndex = 0) + { + $s = ''; + $y = 0; + foreach ($this->readRowsEx($worksheetIndex) as $r) { + $s .= ''; + $x = 0; + foreach ($r as $c) { + $tag = 'td'; + $css = $c['css']; + if ($y === 0) { + $tag = 'th'; + $css .= $c['width'] ? 'width: '.round($c['width'] * 0.47, 2).'em;' : ''; + } + + if ($x === 0 && $c['height']) { + $css .= 'height: '.round($c['height'] * 1.3333).'px;'; + } + $s .= '<'.$tag.' style="'.$css.'" nowrap>' . ($c['value'] === '' ? ' ' : htmlspecialchars($c['value'], ENT_QUOTES)) . ''; + $x++; + } + $s .= "\r\n"; + $y++; + } + $s .= '
'; + + return $s; + } + public function rows($worksheetIndex = 0, $limit = 0) + { + return iterator_to_array($this->readRows($worksheetIndex, $limit), false); + } + // thx Gonzo + /** + * @param $worksheetIndex + * @param $limit + * @return \Generator + */ + public function readRows($worksheetIndex = 0, $limit = 0) + { + + if (($ws = $this->worksheet($worksheetIndex)) === false) { + return; + } + $dim = $this->dimension($worksheetIndex); + $numCols = $dim[0]; + $numRows = $dim[1]; + + $emptyRow = []; + for ($i = 0; $i < $numCols; $i++) { + $emptyRow[] = ''; + } + + $curR = 0; + $_limit = $limit; + /* @var SimpleXMLElement $ws */ + foreach ($ws->sheetData->row as $row) { + $r = $emptyRow; + $curC = 0; + foreach ($row->c as $c) { + // detect skipped cols + $idx = $this->getIndex((string)$c['r']); + $x = $idx[0]; + $y = $idx[1]; + + if ($x > -1) { + $curC = $x; + while ($curR < $y) { + yield $emptyRow; + $curR++; + $_limit--; + if ($_limit === 0) { + return; + } + } + } + $r[$curC] = $this->value($c); + $curC++; + } + yield $r; + + $curR++; + $_limit--; + if ($_limit === 0) { + return; + } + } + while ($curR < $numRows) { + yield $emptyRow; + $curR++; + $_limit--; + if ($_limit === 0) { + return; + } + } + } + + public function rowsEx($worksheetIndex = 0, $limit = 0) + { + return iterator_to_array($this->readRowsEx($worksheetIndex, $limit), false); + } + // https://github.com/shuchkin/simplexlsx#gets-extend-cell-info-by--rowsex + /** + * @param $worksheetIndex + * @param $limit + * @return \Generator|null + */ + public function readRowsEx($worksheetIndex = 0, $limit = 0) + { + if (!$this->rowsExReader) { + require_once __DIR__ . '/SimpleXLSXEx.php'; + $this->rowsExReader = new SimpleXLSXEx($this); + } + return $this->rowsExReader->readRowsEx($worksheetIndex, $limit); + } + + /** + * Returns cell value + * VERY SLOW! Use ->rows() or ->rowsEx() + * + * @param int $worksheetIndex + * @param string|array $cell ref or coords, D12 or [3,12] + * + * @return mixed Returns NULL if not found + */ + public function getCell($worksheetIndex = 0, $cell = 'A1') + { + + if (($ws = $this->worksheet($worksheetIndex)) === false) { + return false; + } + if (is_array($cell)) { + $cell = $this->_num2name($cell[0]) . $cell[1];// [3,21] -> D21 + } + if (is_string($cell)) { + $result = $ws->sheetData->xpath("row/c[@r='" . $cell . "']"); + if (count($result)) { + return $this->value($result[0]); + } + } + + return null; + } + + protected function _num2name($num) + { + $numeric = ($num - 1) % 26; + $letter = chr(65 + $numeric); + $num2 = (int)(($num - 1) / 26); + if ($num2 > 0) { + return $this->_num2name($num2) . $letter; + } + return $letter; + } + + public function getSheets() + { + return $this->sheets; + } + + public function sheetsCount() + { + return count($this->sheets); + } + + public function sheetName($worksheetIndex) + { + if (isset($this->sheetNames[$worksheetIndex])) { + return $this->sheetNames[$worksheetIndex]; + } + + return false; + } + + public function sheetNames() + { + + return $this->sheetNames; + } + + public function getStyles() + { + return $this->styles; + } + + public function getPackage() + { + return $this->package; + } + + public function setDateTimeFormat($value) + { + $this->datetimeFormat = is_string($value) ? $value : false; + } +} diff --git a/RS/ExcelCal/SimpleXLSXEx.php b/RS/ExcelCal/SimpleXLSXEx.php new file mode 100644 index 0000000..9c9e257 --- /dev/null +++ b/RS/ExcelCal/SimpleXLSXEx.php @@ -0,0 +1,601 @@ + '000000', + 1 => 'FFFFFF', + 2 => 'FF0000', + 3 => '00FF00', + 4 => '0000FF', + 5 => 'FFFF00', + 6 => 'FF00FF', + 7 => '00FFFF', + 8 => '000000', + 9 => 'FFFFFF', + 10 => 'FF0000', + 11 => '00FF00', + 12 => '0000FF', + 13 => 'FFFF00', + 14 => 'FF00FF', + 15 => '00FFFF', + 16 => '800000', + 17 => '008000', + 18 => '000080', + 19 => '808000', + 20 => '800080', + 21 => '008080', + 22 => 'C0C0C0', + 23 => '808080', + 24 => '9999FF', + 25 => '993366', + 26 => 'FFFFCC', + 27 => 'CCFFFF', + 28 => '660066', + 29 => 'FF8080', + 30 => '0066CC', + 31 => 'CCCCFF', + 32 => '000080', + 33 => 'FF00FF', + 34 => 'FFFF00', + 35 => '00FFFF', + 36 => '800080', + 37 => '800000', + 38 => '008080', + 39 => '0000FF', + 40 => '00CCFF', + 41 => 'CCFFFF', + 42 => 'CCFFCC', + 43 => 'FFFF99', + 44 => '99CCFF', + 45 => 'FF99CC', + 46 => 'CC99FF', + 47 => 'FFCC99', + 48 => '3366FF', + 49 => '33CCCC', + 50 => '99CC00', + 51 => 'FFCC00', + 52 => 'FF9900', + 53 => 'FF6600', + 54 => '666699', + 55 => '969696', + 56 => '003366', + 57 => '339966', + 58 => '003300', + 59 => '333300', + 60 => '993300', + 61 => '993366', + 62 => '333399', + 63 => '333333', + 64 => '000000', // System Foreground + 65 => 'FFFFFF', // System Background' + ]; + public static $CH = [ + 0 => 'ANSI_CHARSET', + 1 => 'DEFAULT_CHARSET', + 2 => 'SYMBOL_CHARSET', + 77 => 'MAC_CHARSET', + 128 => 'SHIFTJIS_CHARSET', + //129 => 'HANGEUL_CHARSET', + 129 => 'HANGUL_CHARSET', + 130 => 'JOHAB_CHARSET', + 134 => 'GB2312_CHARSET', + 136 => 'CHINESEBIG5_CHARSET', + 161 => 'GREEK_CHARSET', + 162 => 'TURKISH_CHARSET', + 163 => 'VIETNAMESE_CHARSET', + 177 => 'HEBREW_CHARSET', + 178 => 'ARABIC_CHARSET', + 186 => 'BALTIC_CHARSET', + 204 => 'RUSSIAN_CHARSET', + 222 => 'THAI_CHARSET', + 238 => 'EASTEUROPE_CHARSET', + 255 => 'OEM_CHARSET' + ]; + public $xlsx; + public $themeColors; + public $fonts; + public $fills; + public $borders; + public $cellStyles; + public $css; + + public function __construct(SimpleXLSX $xlsx) + { + $this->xlsx = $xlsx; + $this->readThemeColors(); + $this->readFonts(); + $this->readFills(); + $this->readBorders(); + $this->readXfs(); + } + public function readThemeColors() + { + $this->themeColors = []; + if (isset($this->xlsx->theme->themeElements->clrScheme)) { + $colors12 = ['lt1', 'dk1', 'lt2', 'dk2','accent1','accent2','accent3','accent4','accent5', + 'accent6','hlink','folHlink']; + foreach ($colors12 as $c) { + $v = $this->xlsx->theme->themeElements->clrScheme->{$c}; + if (isset($v->sysClr)) { + $this->themeColors[] = (string) $v->sysClr['lastClr']; + } elseif (isset($v->srgbClr)) { + $this->themeColors[] = (string) $v->srgbClr['val']; + } else { + $this->themeColors[] = null; + } + } + } + } + + + public function readFonts() + { + // fonts + $this->fonts = []; + if (isset($this->xlsx->styles->fonts->font)) { + foreach ($this->xlsx->styles->fonts->font as $v) { + $u = ''; + if (isset($v->u)) { + $u = isset($v->u['val']) ? (string) $v->u['val'] : 'single'; + } + $this->fonts[] = [ + 'b' => isset($v->b) && ($v->b['val'] === null || $v->b['val']), + 'i' => isset($v->i) && ($v->i['val'] === null || $v->i['val']), + 'u' => $u, + 'strike' => isset($v->strike) && ($v->strike['val'] === null || $v->strike['val']), + 'sz' => isset($v->sz['val']) ? (int) $v->sz['val'] : 11, + 'color' => $this->getColorValue($v->color), + 'name' => isset($v->name['val']) ? (string) $v->name['val'] : 'Calibri', + 'family' => isset($v->family['val']) ? (int) $v->family['val'] : 2, + 'charset' => isset($v->charset['val']) ? (int) $v->charset['val'] : 1, + 'scheme' => isset($v->scheme['val']) ? (string) $v->scheme['val'] : 'minor' + ]; + } + } + } + public function readFills() + { + // fills + $this->fills = []; + if (isset($this->xlsx->styles->fills->fill)) { + foreach ($this->xlsx->styles->fills->fill as $v) { + if (isset($v->patternFill)) { + $this->fills[] = [ + 'pattern' => isset($v->patternFill['patternType']) ? (string) $v->patternFill['patternType'] : 'none', + 'fgcolor' => $this->getColorValue($v->patternFill->fgColor), + 'bgcolor' => $this->getColorValue($v->patternFill->bgColor) + ]; + } + } + } + } + public function readBorders() + { + $this->borders = []; + if (isset($this->xlsx->styles->borders->border)) { + foreach ($this->xlsx->styles->borders->border as $v) { + $this->borders[] = [ + 'left' => [ + 'style' => (string) $v->left['style'], + 'color' => $this->getColorValue($v->left->color) + ], + 'right' => [ + 'style' => (string) $v->right['style'], + 'color' => $this->getColorValue($v->right->color) + ], + 'top' => [ + 'style' => (string) $v->top['style'], + 'color' => $this->getColorValue($v->top->color) + ], + 'bottom' => [ + 'style' => (string) $v->bottom['style'], + 'color' => $this->getColorValue($v->bottom->color) + ], + 'diagonal' => [ + 'style' => (string) $v->diagonal['style'], + 'color' => $this->getColorValue($v->diagonal->color) + ], + 'horizontal' => [ + 'style' => (string) $v->horizontal['style'], + 'color' => $this->getColorValue($v->horizontal->color) + ], + 'vertical' => [ + 'style' => (string) $v->vertical['style'], + 'color' => $this->getColorValue($v->vertical->color) + ], + 'diagonalUp' => (bool) $v['diagonalUp'], + 'diagonalDown' => (bool) $v['diagonalDown'], + 'outline' => !(isset($v['outline'])) || $v['outline'] + ]; + } + } + } + + public function readXfs() + { + // cellStyles + $this->cellStyles = []; + if (isset($this->xlsx->styles->cellStyleXfs->xf)) { + foreach ($this->xlsx->styles->cellStyleXfs->xf as $v) { + $x = []; + foreach ($v->attributes() as $k1 => $v1) { + $x[ $k1 ] = (int) $v1; + } + if (isset($v->alignment)) { + foreach ($v->alignment->attributes() as $k1 => $v1) { + $x['alignment'][$k1] = (string) $v1; + } + } + $this->cellStyles[] = $x; + } + } + // css + $this->css = []; + // xf + if (isset($this->xlsx->styles->cellXfs->xf)) { + $k = 0; + foreach ($this->xlsx->styles->cellXfs->xf as $v) { + $cf = &$this->xlsx->cellFormats[$k]; + + // alignment + $alignment = []; + if (isset($v->alignment)) { + foreach ($v->alignment->attributes() as $k1 => $v1) { + $alignment[$k1] = (string)$v1; + } + } + + if (isset($cf['xfId'], $this->cellStyles[ $cf['xfId'] ])) { + $s = $this->cellStyles[$cf['xfId']]; + if (!empty($s['applyNumberFormat'])) { + $cf['numFmtId'] = $s['numFmtId']; + } + if (!empty($s['applyFont'])) { + $cf['fontId'] = $s['fontId']; + } + if (!empty($s['applyBorder'])) { + $cf['borderId'] = $s['borderId']; + } + if (!empty($s['applyAlignment'])) { + $alignment = $s['alignment']; + } + } + $cf['alignment'] = $alignment; + + $align = null; + if (isset($alignment['horizontal'])) { + $align = $alignment['horizontal']; + if ($align === 'centerContinuous') { + $align = 'center'; + } + if ($align === 'distributed') { + $align = 'justify'; + } + if ($align === 'general') { + $align = null; + } + } + $cf['align'] = $align; + + $valign = null; + if (isset($alignment['vertical'])) { + $valign = $alignment['vertical']; + if ($valign === 'center' || $valign === 'distributed' || $valign === 'justify') { + $valign = 'middle'; + } + } + $cf['valign'] = $valign; + + // font + $cf['font'] = $this->fonts[ $cf['fontId'] ]['name']; + $cf['color'] = $this->fonts[ $cf['fontId'] ]['color']; + $cf['f-size'] = $this->fonts[ $cf['fontId'] ]['sz']; + $cf['f-b'] = $this->fonts[ $cf['fontId'] ]['b']; + $cf['f-i'] = $this->fonts[ $cf['fontId'] ]['i']; + $cf['f-u'] = $this->fonts[ $cf['fontId'] ]['u']; + $cf['f-strike'] = $this->fonts[ $cf['fontId'] ]['strike']; + + // fill + $cf['bgcolor'] = $this->fills[ $cf['fillId'] ]['fgcolor']; + + // borders + if (isset($this->borders[ $cf['borderId'] ])) { + $border = $this->borders[ $cf['borderId'] ]; + + $borders = ['left', 'right', 'top', 'bottom']; + foreach ($borders as $b) { + $cf['b-' . $b.'-color'] = $border[$b]['color']; + if ($border[$b]['style'] === '' || $border[$b]['style'] === 'none') { + $cf['b-' . $b.'-style'] = ''; + $cf['b-' . $b.'-color'] = ''; + } elseif ($border[$b]['style'] === 'dashDot' + || $border[$b]['style'] === 'dashDotDot' + || $border[$b]['style'] === 'dashed' + ) { + $cf['b-' . $b.'-style'] = 'dashed'; + } else { + $cf['b-' . $b.'-style'] = 'solid'; + } + } + } + + $css = ''; + + if ($cf['color']) { + $css .= 'color: #'.$cf['color'].';'; + } + if ($cf['font']) { + $css .= 'font-family: '.$cf['font'].';'; + } + if ($cf['f-size']) { +// $css .= 'font-size: '.($cf['f-size'] * 0.352806).'mm;'; + $css .= 'font-size: '.(round($cf['f-size'] * 1.3333) + 2).'px;'; + } + if ($cf['f-b']) { + $css .= 'font-weight: bold;'; + } + if ($cf['f-i']) { + $css .= 'font-style: italic;'; + } + if ($cf['f-u']) { + $css .= 'text-decoration: underline;'; + } + if ($cf['f-strike']) { + $css .= 'text-decoration: line-through;'; + } + if ($cf['bgcolor']) { + $css .= 'background-color: #' . $cf['bgcolor'] . ';'; + } + if ($cf['align']) { + $css .= 'text-align: '.$cf['align'].';'; + } + if ($cf['valign']) { + $css .= 'vertical-align: '.$cf['valign'].';'; + } + if ($cf['b-top-style']) { + $css .= 'border-top-style: '.$cf['b-top-style'].';'; + $css .= 'border-top-color: #'.$cf['b-top-color'].';'; + $css .= 'border-top-width: thin;'; + } + if ($cf['b-right-style']) { + $css .= 'border-right-style: '.$cf['b-right-style'].';'; + $css .= 'border-right-color: #'.$cf['b-right-color'].';'; + $css .= 'border-right-width: thin;'; + } + if ($cf['b-bottom-style']) { + $css .= 'border-bottom-style: '.$cf['b-bottom-style'].';'; + $css .= 'border-bottom-color: #'.$cf['b-bottom-color'].';'; + $css .= 'border-bottom-width: thin;'; + } + if ($cf['b-left-style']) { + $css .= 'border-left-style: '.$cf['b-left-style'].';'; + $css .= 'border-left-color: #'.$cf['b-left-color'].';'; + $css .= 'border-left-width: thin;'; + } + $this->css[$k] = $css; + + $k++; + } + } + } + + public function readRowsEx($worksheetIndex = 0, $limit = 0) + { + if (($ws = $this->xlsx->worksheet($worksheetIndex)) === false) { + return; + } + + $dim = $this->xlsx->dimension($worksheetIndex); + $numCols = $dim[0]; + $numRows = $dim[1]; + + /*$emptyRow = array(); + for ($i = 0; $i < $numCols; $i++) { + $emptyRow[] = null; + } + */ + $cols = []; + for ($i = 0; $i < $numCols; $i++) { + $cols[] = ['s' => 0, 'hidden' => false, 'width' => 0]; + } +// $hiddenCols = []; + /* @var SimpleXMLElement $ws */ + if (isset($ws->cols)) { + foreach ($ws->cols->col as $col) { + $min = (int)$col['min']; + $max = (int)$col['max']; + if (($max-$min) > 100) { + $max = $min; + } + for ($i = $min; $i <= $max; $i++) { + $cols[$i-1] = [ + 's' => (int)$col['style'], + 'hidden' => (bool)$col['hidden'], + 'width' => $col['customWidth'] ? (float) $col['width'] : 0 + ]; + } + } + } + + $curR = 0; + $_limit = $limit; + + foreach ($ws->sheetData->row as $row) { + $curC = 0; + + $r_idx = (int)$row['r']; + $r_style = ['s' => 0, 'hidden' => (bool)$row['hidden'], 'height' => 0]; + if ($row['customFormat']) { + $r_style['s'] = (int)$row['s']; + } + if ($row['customHeight']) { + $r_style['height'] = (int)$row['ht']; + } + + $cells = []; + for ($i = 0; $i < $numCols; $i++) { + $cells[] = null; + } + + foreach ($row->c as $c) { + $r = (string)$c['r']; + $t = (string)$c['t']; + $s = (int)$c['s']; + + $idx = $this->xlsx->getIndex($r); + $x = $idx[0]; + $y = $idx[1]; + + if ($x > -1) { + $curC = $x; + if ($curC >= $numCols) { + $numCols = $curC + 1; + } + while ($curR < $y) { + $emptyRow = []; + for ($i = 0; $i < $numCols; $i++) { + $emptyRow[] = $this->valueEx($cols[$i], $i, $curR); + } + yield $emptyRow; + $curR++; + + $_limit--; + if ($_limit === 0) { + return; + } + } + } + $data = [ + 'type' => $t, + 'name' => $r, + 'value' => $this->xlsx->value($c), + 'href' => $this->xlsx->href($worksheetIndex, $c), + 'f' => (string)$c->f, + 'r' => $r_idx, + 's' => ($s > 0) ? $s : $cols[$curC]['s'], + 'hidden' => $r_style['hidden'] || $cols[$curC]['hidden'], + 'width' => $cols[$curC]['width'], + 'height' => $r_style['height'] + ]; + $cells[$curC] = $this->valueEx($data, $curC, $curR); + + $curC++; + } + // check empty cells + for ($i = 0; $i < $numCols; $i++) { + if ($cells[$i] === null) { + if ($r_style['s'] > 0) { + $data = $r_style; + } else { + $data = $cols[$i]; + } + $data['width'] = $cols[$i]['width']; + $data['height'] = $r_style['height']; + $cells[$i] = $this->valueEx($data, $i, $curR); + } + } + + yield $cells; + + $curR++; + $_limit--; + if ($_limit === 0) { + break; + } + } + + while ($curR < $numRows) { + $emptyRow = []; + for ($i = 0; $i < $numCols; $i++) { + $data = $cols[$i]; + $emptyRow[] = $this->valueEx($data, $i, $curR); + } + yield $emptyRow; + $curR++; + $_limit--; + if ($_limit === 0) { + return; + } + } + } + + protected function valueEx($data, $x = null, $y = null) + { + + $r = [ + 'type' => '', + 'name' => '', + 'value' => '', + 'href' => '', + 'f' => '', + 'format' => '', + 's' => 0, + 'css' => '', + 'r' => '', + 'hidden' => false, + 'width' => 0, + 'height' => 0 + ]; + foreach ($data as $k => $v) { + if (isset($r[$k])) { + $r[$k] = $v; + } + } + $st = &$this->xlsx->cellFormats[$r['s']]; + $r['format'] = $st['format']; + $r['css'] = &$this->css[ $r['s'] ]; + if ($r['value'] !== '' && !$st['align'] && !in_array($r['type'], ['s','str','inlineStr','e'], true)) { + $r['css'] .= 'text-align: right;'; + } + + if (!$r['name']) { + $c = ''; + for ($k = $x; $k >= 0; $k = (int)($k / 26) - 1) { + $c = chr($k % 26 + 65) . $c; + } + $r['name'] = $c . ($y + 1); + $r['r'] = $y+1; + } + return $r; + } + public function getColorValue(SimpleXMLElement $a = null, $default = '') + { + if ($a === null) { + return $default; + } + $c = $default; // auto + if ($a['rgb'] !== null) { + $c = substr((string) $a['rgb'], 2); // FFCCBBAA -> CCBBAA + } elseif ($a['indexed'] !== null && isset(static::$IC[ (int) $a['indexed'] ])) { + $c = static::$IC[ (int) $a['indexed'] ]; + } elseif ($a['theme'] !== null && isset($this->themeColors[ (int) $a['theme'] ])) { + $c = $this->themeColors[ (int) $a['theme'] ]; + } + if ($a['tint'] !== null) { + list($r,$g,$b) = array_map('hexdec', str_split($c, 2)); + $tint = (float) $a['tint']; + if ($tint > 0) { + $r += (255 - $r) * $tint; + $g += (255 - $g) * $tint; + $b += (255 - $b) * $tint; + } else { + $r += $r * $tint; + $g += $g * $tint; + $b += $b * $tint; + } + $c = strtoupper( + str_pad(dechex((int) $r), 2, '0', 0) . + str_pad(dechex((int) $g), 2, '0', 0) . + str_pad(dechex((int) $b), 2, '0', 0) + ); + } + return $c; + } +} diff --git a/RS/ExcelCal/SimpleXLSX.php b/RS/ExcelCal/SimpleXLSX.php new file mode 100644 index 0000000..c9776fb --- /dev/null +++ b/RS/ExcelCal/SimpleXLSX.php @@ -0,0 +1,1224 @@ +rows() as $r) { + * print_r( $r ); + * } + * } else { + * echo SimpleXLSX::parseError(); + * } + * + * Example 2: html table + * if ( $xlsx = SimpleXLSX::parse('book.xlsx') ) { + * echo $xlsx->toHTML(); + * } else { + * echo SimpleXLSX::parseError(); + * } + * + * Example 3: rowsEx + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * foreach ( $xlsx->rowsEx() as $r ) { + * print_r( $r ); + * } + * + * Example 4: select worksheet + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * foreach( $xlsx->rows(1) as $r ) { // second worksheet + * print_t( $r ); + * } + * + * Example 5: IDs and worksheet names + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * print_r( $xlsx->sheetNames() ); // array( 0 => 'Sheet 1', 1 => 'Catalog' ); + * + * Example 6: get sheet name by index + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * echo 'Sheet Name 2 = '.$xlsx->sheetName(1); + * + * Example 7: getCell (very slow) + * echo $xlsx->getCell(1,'D12'); // reads D12 cell from second sheet + * + * Example 8: read data + * if ( $xlsx = SimpleXLSX::parseData( file_get_contents('http://www.example.com/example.xlsx') ) ) { + * $dim = $xlsx->dimension(1); + * $num_cols = $dim[0]; + * $num_rows = $dim[1]; + * echo $xlsx->sheetName(1).':'.$num_cols.'x'.$num_rows; + * } else { + * echo SimpleXLSX::parseError(); + * } + * + * Example 9: old style + * $xlsx = new SimpleXLSX('book.xlsx'); + * if ( $xlsx->success() ) { + * print_r( $xlsx->rows() ); + * } else { + * echo 'xlsx error: '.$xlsx->error(); + * } + */ +class SimpleXLSX +{ + // Don't remove this string! Created by Sergey Shuchkin sergey.shuchkin@gmail.com + public static $CF = [ // Cell formats + 0 => 'General', + 1 => '0', + 2 => '0.00', + 3 => '#,##0', + 4 => '#,##0.00', + 9 => '0%', + 10 => '0.00%', + 11 => '0.00E+00', + 12 => '# ?/?', + 13 => '# ??/??', + 14 => 'mm-dd-yy', + 15 => 'd-mmm-yy', + 16 => 'd-mmm', + 17 => 'mmm-yy', + 18 => 'h:mm AM/PM', + 19 => 'h:mm:ss AM/PM', + 20 => 'h:mm', + 21 => 'h:mm:ss', + 22 => 'm/d/yy h:mm', + + 37 => '#,##0 ;(#,##0)', + 38 => '#,##0 ;[Red](#,##0)', + 39 => '#,##0.00;(#,##0.00)', + 40 => '#,##0.00;[Red](#,##0.00)', + + 44 => '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)', + 45 => 'mm:ss', + 46 => '[h]:mm:ss', + 47 => 'mmss.0', + 48 => '##0.0E+0', + 49 => '@', + + 27 => '[$-404]e/m/d', + 30 => 'm/d/yy', + 36 => '[$-404]e/m/d', + 50 => '[$-404]e/m/d', + 57 => '[$-404]e/m/d', + + 59 => 't0', + 60 => 't0.00', + 61 => 't#,##0', + 62 => 't#,##0.00', + 67 => 't0%', + 68 => 't0.00%', + 69 => 't# ?/?', + 70 => 't# ??/??', + ]; + public $nf = []; // number formats + public $cellFormats = []; // cellXfs + public $datetimeFormat = 'Y-m-d H:i:s'; + public $debug; + public $activeSheet = 0; + public $rowsExReader; + + /* @var SimpleXMLElement[] $sheets */ + protected $sheets; + protected $sheetNames = []; + protected $sheetFiles = []; + // scheme + public $styles; + protected $hyperlinks; + /* @var array[] $package */ + protected $package; + protected $sharedstrings; + protected $date1904 = 0; + + + /* + private $date_formats = array( + 0xe => "d/m/Y", + 0xf => "d-M-Y", + 0x10 => "d-M", + 0x11 => "M-Y", + 0x12 => "h:i a", + 0x13 => "h:i:s a", + 0x14 => "H:i", + 0x15 => "H:i:s", + 0x16 => "d/m/Y H:i", + 0x2d => "i:s", + 0x2e => "H:i:s", + 0x2f => "i:s.S" + ); + private $number_formats = array( + 0x1 => "%1.0f", // "0" + 0x2 => "%1.2f", // "0.00", + 0x3 => "%1.0f", //"#,##0", + 0x4 => "%1.2f", //"#,##0.00", + 0x5 => "%1.0f", //"$#,##0;($#,##0)", + 0x6 => '$%1.0f', //"$#,##0;($#,##0)", + 0x7 => '$%1.2f', //"$#,##0.00;($#,##0.00)", + 0x8 => '$%1.2f', //"$#,##0.00;($#,##0.00)", + 0x9 => '%1.0f%%', //"0%" + 0xa => '%1.2f%%', //"0.00%" + 0xb => '%1.2f', //"0.00E00", + 0x25 => '%1.0f', //"#,##0;(#,##0)", + 0x26 => '%1.0f', //"#,##0;(#,##0)", + 0x27 => '%1.2f', //"#,##0.00;(#,##0.00)", + 0x28 => '%1.2f', //"#,##0.00;(#,##0.00)", + 0x29 => '%1.0f', //"#,##0;(#,##0)", + 0x2a => '$%1.0f', //"$#,##0;($#,##0)", + 0x2b => '%1.2f', //"#,##0.00;(#,##0.00)", + 0x2c => '$%1.2f', //"$#,##0.00;($#,##0.00)", + 0x30 => '%1.0f'); //"##0.0E0"; + // }}} + */ + protected $errno = 0; + protected $error = false; + /** + * @var false|SimpleXMLElement + */ + public $theme; + + + public function __construct($filename = null, $is_data = null, $debug = null) + { + if ($debug !== null) { + $this->debug = $debug; + } + $this->package = [ + 'filename' => '', + 'mtime' => 0, + 'size' => 0, + 'comment' => '', + 'entries' => [] + ]; + if ($filename && $this->_unzip($filename, $is_data)) { + $this->_parse(); + } + } + + protected function _unzip($filename, $is_data = false) + { + + if ($is_data) { + $this->package['filename'] = 'default.xlsx'; + $this->package['mtime'] = time(); + $this->package['size'] = $this->_strlen($filename); + + $vZ = $filename; + } else { + if (!is_readable($filename)) { + $this->error(1, 'File not found ' . $filename); + + return false; + } + + // Package information + $this->package['filename'] = $filename; + $this->package['mtime'] = filemtime($filename); + $this->package['size'] = filesize($filename); + + // Read file + $vZ = file_get_contents($filename); + } + // Cut end of central directory + /* $aE = explode("\x50\x4b\x05\x06", $vZ); + + if (count($aE) == 1) { + $this->error('Unknown format'); + return false; + } + */ + // Explode to each part + $aE = explode("\x50\x4b\x03\x04", $vZ); + array_shift($aE); + + $aEL = count($aE); + if ($aEL === 0) { + $this->error(2, 'Unknown archive format'); + + return false; + } + // Search central directory end record + $last = $aE[$aEL - 1]; + $last = explode("\x50\x4b\x05\x06", $last); + if (count($last) !== 2) { + $this->error(2, 'Unknown archive format'); + + return false; + } + // Search central directory + $last = explode("\x50\x4b\x01\x02", $last[0]); + if (count($last) < 2) { + $this->error(2, 'Unknown archive format'); + + return false; + } + $aE[$aEL - 1] = $last[0]; + + // Loop through the entries + foreach ($aE as $vZ) { + $aI = []; + $aI['E'] = 0; + $aI['EM'] = ''; + // Retrieving local file header information +// $aP = unpack('v1VN/v1GPF/v1CM/v1FT/v1FD/V1CRC/V1CS/V1UCS/v1FNL', $vZ); + $aP = unpack('v1VN/v1GPF/v1CM/v1FT/v1FD/V1CRC/V1CS/V1UCS/v1FNL/v1EFL', $vZ); + + // Check if data is encrypted +// $bE = ($aP['GPF'] && 0x0001) ? TRUE : FALSE; +// $bE = false; + $nF = $aP['FNL']; + $mF = $aP['EFL']; + + // Special case : value block after the compressed data + if ($aP['GPF'] & 0x0008) { + $aP1 = unpack('V1CRC/V1CS/V1UCS', $this->_substr($vZ, -12)); + + $aP['CRC'] = $aP1['CRC']; + $aP['CS'] = $aP1['CS']; + $aP['UCS'] = $aP1['UCS']; + // 2013-08-10 + $vZ = $this->_substr($vZ, 0, -12); + if ($this->_substr($vZ, -4) === "\x50\x4b\x07\x08") { + $vZ = $this->_substr($vZ, 0, -4); + } + } + + // Getting stored filename + $aI['N'] = $this->_substr($vZ, 26, $nF); + $aI['N'] = str_replace('\\', '/', $aI['N']); + + if ($this->_substr($aI['N'], -1) === '/') { + // is a directory entry - will be skipped + continue; + } + + // Truncate full filename in path and filename + $aI['P'] = dirname($aI['N']); + $aI['P'] = ($aI['P'] === '.') ? '' : $aI['P']; + $aI['N'] = basename($aI['N']); + + $vZ = $this->_substr($vZ, 26 + $nF + $mF); + + if ($this->_strlen($vZ) !== (int)$aP['CS']) { // check only if availabled + $aI['E'] = 1; + $aI['EM'] = 'Compressed size is not equal with the value in header information.'; + } +// } elseif ( $bE ) { +// $aI['E'] = 5; +// $aI['EM'] = 'File is encrypted, which is not supported from this class.'; +/* } else { + switch ($aP['CM']) { + case 0: // Stored + // Here is nothing to do, the file ist flat. + break; + case 8: // Deflated + $vZ = gzinflate($vZ); + break; + case 12: // BZIP2 + if (extension_loaded('bz2')) { + $vZ = bzdecompress($vZ); + } else { + $aI['E'] = 7; + $aI['EM'] = 'PHP BZIP2 extension not available.'; + } + break; + default: + $aI['E'] = 6; + $aI['EM'] = "De-/Compression method {$aP['CM']} is not supported."; + } + if (!$aI['E']) { + if ($vZ === false) { + $aI['E'] = 2; + $aI['EM'] = 'Decompression of data failed.'; + } elseif ($this->_strlen($vZ) !== (int)$aP['UCS']) { + $aI['E'] = 3; + $aI['EM'] = 'Uncompressed size is not equal with the value in header information.'; + } elseif (crc32($vZ) !== $aP['CRC']) { + $aI['E'] = 4; + $aI['EM'] = 'CRC32 checksum is not equal with the value in header information.'; + } + } + } +*/ + + // DOS to UNIX timestamp + $aI['T'] = mktime( + ($aP['FT'] & 0xf800) >> 11, + ($aP['FT'] & 0x07e0) >> 5, + ($aP['FT'] & 0x001f) << 1, + ($aP['FD'] & 0x01e0) >> 5, + $aP['FD'] & 0x001f, + (($aP['FD'] & 0xfe00) >> 9) + 1980 + ); + + $this->package['entries'][] = [ + 'data' => $vZ, + 'ucs' => (int)$aP['UCS'], // ucompresses size + 'cm' => $aP['CM'], // compressed method + 'cs' => isset($aP['CS']) ? (int) $aP['CS'] : 0, // compresses size + 'crc' => $aP['CRC'], + 'error' => $aI['E'], + 'error_msg' => $aI['EM'], + 'name' => $aI['N'], + 'path' => $aI['P'], + 'time' => $aI['T'] + ]; + } // end for each entries + + return true; + } + + protected function _strlen($str) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_strlen($str, '8bit') : strlen($str); + } + + public function error($num = null, $str = null) + { + if ($num) { + $this->errno = $num; + $this->error = $str; + if ($this->debug) { + trigger_error(__CLASS__ . ': ' . $this->error, E_USER_WARNING); + } + } + + return $this->error; + } + + protected function _substr($str, $start, $length = null) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_substr($str, $start, ($length === null) ? mb_strlen($str, '8bit') : $length, '8bit') : substr($str, $start, ($length === null) ? strlen($str) : $length); + } + + protected function _parse() + { + // Document data holders + $this->sharedstrings = []; + $this->sheets = []; +// $this->styles = array(); +// $m1 = 0; // memory_get_peak_usage( true ); + // Read relations and search for officeDocument + if ($relations = $this->getEntryXML('_rels/.rels')) { + foreach ($relations->Relationship as $rel) { + $rel_type = basename(trim((string)$rel['Type'])); // officeDocument + $rel_target = $this->_getTarget('', (string)$rel['Target']); // /xl/workbook.xml or xl/workbook.xml + + if ($rel_type === 'officeDocument' && $workbook = $this->getEntryXML($rel_target)) { + $index_rId = []; // [0 => rId1] + + $index = 0; + foreach ($workbook->sheets->sheet as $s) { + $this->sheetNames[$index] = (string)$s['name']; + $index_rId[$index] = (string)$s['id']; + $index++; + } + if ((int)$workbook->workbookPr['date1904'] === 1) { + $this->date1904 = 1; + } + + + if ($workbookRelations = $this->getEntryXML(dirname($rel_target) . '/_rels/workbook.xml.rels')) { + // Loop relations for workbook and extract sheets... + foreach ($workbookRelations->Relationship as $workbookRelation) { + $wrel_type = basename(trim((string)$workbookRelation['Type'])); // worksheet + $wrel_path = $this->_getTarget(dirname($rel_target), (string)$workbookRelation['Target']); + if (!$this->entryExists($wrel_path)) { + continue; + } + + + if ($wrel_type === 'worksheet') { // Sheets + if ($sheet = $this->getEntryXML($wrel_path)) { + $index = array_search((string)$workbookRelation['Id'], $index_rId, true); + $this->sheets[$index] = $sheet; + $this->sheetFiles[$index] = $wrel_path; + } + } elseif ($wrel_type === 'sharedStrings') { + if ($sharedStrings = $this->getEntryXML($wrel_path)) { + foreach ($sharedStrings->si as $val) { + if (isset($val->t)) { + $this->sharedstrings[] = (string)$val->t; + } elseif (isset($val->r)) { + $this->sharedstrings[] = $this->_parseRichText($val); + } + } + } + } elseif ($wrel_type === 'styles') { + $this->styles = $this->getEntryXML($wrel_path); + + // number formats + $this->nf = []; + if (isset($this->styles->numFmts->numFmt)) { + foreach ($this->styles->numFmts->numFmt as $v) { + $this->nf[(int)$v['numFmtId']] = (string)$v['formatCode']; + } + } + + $this->cellFormats = []; + if (isset($this->styles->cellXfs->xf)) { + foreach ($this->styles->cellXfs->xf as $v) { + $x = [ + 'format' => null + ]; + foreach ($v->attributes() as $k1 => $v1) { + $x[ $k1 ] = (int) $v1; + } + if (isset($x['numFmtId'])) { + if (isset($this->nf[$x['numFmtId']])) { + $x['format'] = $this->nf[$x['numFmtId']]; + } elseif (isset(self::$CF[$x['numFmtId']])) { + $x['format'] = self::$CF[$x['numFmtId']]; + } + } + + $this->cellFormats[] = $x; + } + } + } elseif ($wrel_type === 'theme') { + $this->theme = $this->getEntryXML($wrel_path); + } + } + + break; + } + // reptile hack :: find active sheet from workbook.xml + foreach ($workbook->bookViews->workbookView as $s) { + if (!empty($s['activeTab'])) { + $this->activeSheet = (int)$s['activeTab']; + } + } + } + } + } + +// $m2 = memory_get_peak_usage(true); +// echo __FUNCTION__.' M='.round( ($m2-$m1) / 1048576, 2).'MB'.PHP_EOL; + + if (count($this->sheets)) { + // Sort sheets + ksort($this->sheets); + + return true; + } + + return false; + } + + public function getEntryXML($name) + { + if ($entry_xml = $this->getEntryData($name)) { + $this->deleteEntry($name); // economy memory + // dirty remove namespace prefixes and empty rows + $entry_xml = preg_replace('/xmlns[^=]*="[^"]*"/i', '', $entry_xml); // remove namespaces + $entry_xml .= ' '; // force run garbage collector + $entry_xml = preg_replace('/[a-zA-Z0-9]+:([a-zA-Z0-9]+="[^"]+")/', '$1', $entry_xml); // remove namespaced attrs + $entry_xml .= ' '; + $entry_xml = preg_replace('/<[a-zA-Z0-9]+:([^>]+)>/', '<$1>', $entry_xml); // fix namespaced openned tags + $entry_xml .= ' '; + $entry_xml = preg_replace('/<\/[a-zA-Z0-9]+:([^>]+)>/', '', $entry_xml); // fix namespaced closed tags + $entry_xml .= ' '; + + if (strpos($name, '/sheet')) { // dirty skip empty rows + // remove + $entry_xml = preg_replace('/]+>\s*(\s*)+<\/row>/', '', $entry_xml, -1, $cnt); + $entry_xml .= ' '; + // remove + $entry_xml = preg_replace('/]*\/>/', '', $entry_xml, -1, $cnt2); + $entry_xml .= ' '; + // remove + $entry_xml = preg_replace('/]*><\/row>/', '', $entry_xml, -1, $cnt3); + $entry_xml .= ' '; + if ($cnt || $cnt2 || $cnt3) { + $entry_xml = preg_replace('//', '', $entry_xml); + $entry_xml .= ' '; + } +// file_put_contents( basename( $name ), $entry_xml ); // @to do comment!!! + } + $entry_xml = trim($entry_xml); + +// $m1 = memory_get_usage(); + // XML External Entity (XXE) Prevention, libxml_disable_entity_loader deprecated in PHP 8 + if (LIBXML_VERSION < 20900 && function_exists('libxml_disable_entity_loader')) { + $_old = libxml_disable_entity_loader(); + } + + $_old_uie = libxml_use_internal_errors(true); + + $entry_xmlobj = simplexml_load_string($entry_xml, 'SimpleXMLElement', LIBXML_COMPACT | LIBXML_PARSEHUGE); + + libxml_use_internal_errors($_old_uie); + + if (LIBXML_VERSION < 20900 && function_exists('libxml_disable_entity_loader')) { + /** @noinspection PhpUndefinedVariableInspection */ + libxml_disable_entity_loader($_old); + } + +// $m2 = memory_get_usage(); +// echo round( ($m2-$m1) / (1024 * 1024), 2).' MB'.PHP_EOL; + + if ($entry_xmlobj) { + return $entry_xmlobj; + } + $e = libxml_get_last_error(); + if ($e) { + $this->error(3, 'XML-entry ' . $name . ' parser error ' . $e->message . ' line ' . $e->line); + } + } else { + $this->error(4, 'XML-entry not found ' . $name); + } + + return false; + } + + // sheets numeration: 1,2,3.... + + public function getEntryData($name) + { + $name = ltrim(str_replace('\\', '/', $name), '/'); + $dir = $this->_strtoupper(dirname($name)); + $name = $this->_strtoupper(basename($name)); + foreach ($this->package['entries'] as &$entry) { + if ($this->_strtoupper($entry['path']) === $dir && $this->_strtoupper($entry['name']) === $name) { + if ($entry['error']) { + return false; + } + switch ($entry['cm']) { + case -1: + case 0: // Stored + // Here is nothing to do, the file ist flat. + break; + case 8: // Deflated + $entry['data'] = gzinflate($entry['data']); + break; + case 12: // BZIP2 + if (extension_loaded('bz2')) { + $entry['data'] = bzdecompress($entry['data']); + } else { + $entry['error'] = 7; + $entry['error_message'] = 'PHP BZIP2 extension not available.'; + } + break; + default: + $entry['error'] = 6; + $entry['error_msg'] = 'De-/Compression method '.$entry['cm'].' is not supported.'; + } + if (!$entry['error'] && $entry['cm'] > -1) { + $entry['cm'] = -1; + if ($entry['data'] === false) { + $entry['error'] = 2; + $entry['error_msg'] = 'Decompression of data failed.'; + } elseif ($this->_strlen($entry['data']) !== (int)$entry['ucs']) { + $entry['error'] = 3; + $entry['error_msg'] = 'Uncompressed size is not equal with the value in header information.'; + } elseif (crc32($entry['data']) !== $entry['crc']) { + $entry['error'] = 4; + $entry['error_msg'] = 'CRC32 checksum is not equal with the value in header information.'; + } + } + + return $entry['data']; + } + } + unset($entry); + $this->error(5, 'Entry not found ' . ($dir ? $dir . '/' : '') . $name); + + return false; + } + public function deleteEntry($name) + { + $name = ltrim(str_replace('\\', '/', $name), '/'); + $dir = $this->_strtoupper(dirname($name)); + $name = $this->_strtoupper(basename($name)); + foreach ($this->package['entries'] as $k => $entry) { + if ($this->_strtoupper($entry['path']) === $dir && $this->_strtoupper($entry['name']) === $name) { + unset($this->package['entries'][$k]); + return true; + } + } + return false; + } + + protected function _strtoupper($str) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_strtoupper($str, '8bit') : strtoupper($str); + } + + protected function _getTarget($base, $target) + { + $target = trim($target); + if (strpos($target, '/') === 0) { + return $this->_substr($target, 1); + } + $target = ($base ? $base . '/' : '') . $target; + // a/b/../c -> a/c + $parts = explode('/', $target); + $abs = []; + foreach ($parts as $p) { + if ('.' === $p) { + continue; + } + if ('..' === $p) { + array_pop($abs); + } else { + $abs[] = $p; + } + } + return implode('/', $abs); + } + + /* + * @param string $name Filename in archive + * @return SimpleXMLElement|bool + */ + + public function entryExists($name) + { + // 0.6.6 + $dir = $this->_strtoupper(dirname($name)); + $name = $this->_strtoupper(basename($name)); + foreach ($this->package['entries'] as $entry) { + if ($this->_strtoupper($entry['path']) === $dir && $this->_strtoupper($entry['name']) === $name) { + return true; + } + } + + return false; + } + + protected function _parseRichText($is = null) + { + $value = []; + + if (isset($is->t)) { + $value[] = (string)$is->t; + } elseif (isset($is->r)) { + foreach ($is->r as $run) { + $value[] = (string)$run->t; + } + } + + return implode('', $value); + } + + public static function parseFile($filename, $debug = false) + { + return self::parse($filename, false, $debug); + } + + public static function parse($filename, $is_data = false, $debug = false) + { + $xlsx = new self(); + $xlsx->debug = $debug; + if ($xlsx->_unzip($filename, $is_data)) { + $xlsx->_parse(); + } + if ($xlsx->success()) { + return $xlsx; + } + self::parseError($xlsx->error()); + self::parseErrno($xlsx->errno()); + + return false; + } + + public function success() + { + return !$this->error; + } + + // https://github.com/shuchkin/simplexlsx#gets-extend-cell-info-by--rowsex + + public static function parseError($set = false) + { + static $error = false; + + return $set ? $error = $set : $error; + } + + public static function parseErrno($set = false) + { + static $errno = false; + + return $set ? $errno = $set : $errno; + } + + public function errno() + { + return $this->errno; + } + + public static function parseData($data, $debug = false) + { + return self::parse($data, true, $debug); + } + + + + public function worksheet($worksheetIndex = 0) + { + + + if (isset($this->sheets[$worksheetIndex])) { + $ws = $this->sheets[$worksheetIndex]; + + if (!isset($this->hyperlinks[$worksheetIndex]) && isset($ws->hyperlinks)) { + $this->hyperlinks[$worksheetIndex] = []; + $sheet_rels = str_replace('worksheets', 'worksheets/_rels', $this->sheetFiles[$worksheetIndex]) . '.rels'; + $link_ids = []; + + if ($rels = $this->getEntryXML($sheet_rels)) { + // hyperlink +// $rel_base = dirname( $sheet_rels ); + foreach ($rels->Relationship as $rel) { + $rel_type = basename(trim((string)$rel['Type'])); + if ($rel_type === 'hyperlink') { + $rel_id = (string)$rel['Id']; + $rel_target = (string)$rel['Target']; + $link_ids[$rel_id] = $rel_target; + } + } + } + foreach ($ws->hyperlinks->hyperlink as $hyperlink) { + $ref = (string)$hyperlink['ref']; + if ($this->_strpos($ref, ':') > 0) { // A1:A8 -> A1 + $ref = explode(':', $ref); + $ref = $ref[0]; + } +// $this->hyperlinks[ $worksheetIndex ][ $ref ] = (string) $hyperlink['display']; + $loc = (string)$hyperlink['location']; + $id = (string)$hyperlink['id']; + if ($id) { + $href = $link_ids[$id] . ($loc ? '#' . $loc : ''); + } else { + $href = $loc; + } + $this->hyperlinks[$worksheetIndex][$ref] = $href; + } + } + + return $ws; + } + $this->error(6, 'Worksheet not found ' . $worksheetIndex); + + return false; + } + + protected function _strpos($haystack, $needle, $offset = 0) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_strpos($haystack, $needle, $offset, '8bit') : strpos($haystack, $needle, $offset); + } + + /** + * returns [numCols,numRows] of worksheet + * + * @param int $worksheetIndex + * + * @return array + */ + public function dimension($worksheetIndex = 0) + { + + if (($ws = $this->worksheet($worksheetIndex)) === false) { + return [0, 0]; + } + /* @var SimpleXMLElement $ws */ + + $ref = (string)$ws->dimension['ref']; + + if ($this->_strpos($ref, ':') !== false) { + $d = explode(':', $ref); + $idx = $this->getIndex($d[1]); + + return [$idx[0] + 1, $idx[1] + 1]; + } + /* + if ( $ref !== '' ) { // 0.6.8 + $index = $this->getIndex( $ref ); + + return [ $index[0] + 1, $index[1] + 1 ]; + } + */ + + // slow method + $maxC = $maxR = 0; + foreach ($ws->sheetData->row as $row) { + foreach ($row->c as $c) { + $idx = $this->getIndex((string)$c['r']); + $x = $idx[0]; + $y = $idx[1]; + if ($x > 0) { + if ($x > $maxC) { + $maxC = $x; + } + if ($y > $maxR) { + $maxR = $y; + } + } + } + } + + return [$maxC + 1, $maxR + 1]; + } + + public function getIndex($cell = 'A1') + { + + if (preg_match('/([A-Z]+)(\d+)/', $cell, $m)) { + $col = $m[1]; + $row = $m[2]; + + $colLen = $this->_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]; + } + +// $this->error( 'Invalid cell index ' . $cell ); + + return [-1, -1]; + } + + public function value($cell) + { + // Determine data type + $dataType = (string)$cell['t']; + + if ($dataType === '' || $dataType === 'n') { // number + $s = (int)$cell['s']; + if ($s > 0 && isset($this->cellFormats[$s])) { + if (array_key_exists('format', $this->cellFormats[$s])) { + $format = $this->cellFormats[$s]['format']; + if (preg_match('/(m|AM|PM)/', preg_replace('/\"[^"]+\"/', '', $format))) { // [mm]onth,AM|PM + $dataType = 'D'; + } + } else { + $dataType = 'n'; + } + } + } + + $value = ''; + + switch ($dataType) { + case 's': + // Value is a shared string + if ((string)$cell->v !== '') { + $value = $this->sharedstrings[(int)$cell->v]; + } + break; + + case 'str': // formula? + if ((string)$cell->v !== '') { + $value = (string)$cell->v; + } + break; + + case 'b': + // Value is boolean + $value = (string)$cell->v; + if ($value === '0') { + $value = false; + } elseif ($value === '1') { + $value = true; + } else { + $value = (bool)$cell->v; + } + + break; + + case 'inlineStr': + // Value is rich text inline + $value = $this->_parseRichText($cell->is); + + break; + + case 'e': + // Value is an error message + if ((string)$cell->v !== '') { + $value = (string)$cell->v; + } + break; + + case 'D': + // Date as float + if (!empty($cell->v)) { + $value = $this->datetimeFormat ? gmdate($this->datetimeFormat, $this->unixstamp((float)$cell->v)) : (float)$cell->v; + } + break; + + case 'd': + // Date as ISO YYYY-MM-DD + if ((string)$cell->v !== '') { + $value = (string)$cell->v; + } + break; + + default: + // Value is a string + $value = (string)$cell->v; + + // Check for numeric values + if (is_numeric($value)) { + /** @noinspection TypeUnsafeComparisonInspection */ + if ($value == (int)$value) { + $value = (int)$value; + } /** @noinspection TypeUnsafeComparisonInspection */ elseif ($value == (float)$value) { + $value = (float)$value; + } + } + } + + return $value; + } + + public function unixstamp($excelDateTime) + { + + $d = floor($excelDateTime); // days since 1900 or 1904 + $t = $excelDateTime - $d; + + if ($this->date1904) { + $d += 1462; + } + + $t = (abs($d) > 0) ? ($d - 25569) * 86400 + round($t * 86400) : round($t * 86400); + + return (int)$t; + } + + public function href($worksheetIndex, $cell) + { + $ref = (string)$cell['r']; + return isset($this->hyperlinks[$worksheetIndex][$ref]) ? $this->hyperlinks[$worksheetIndex][$ref] : ''; + } + + public function toHTML($worksheetIndex = 0) + { + $s = ''; + foreach ($this->readRows($worksheetIndex) as $r) { + $s .= ''; + foreach ($r as $c) { + $s .= ''; + } + $s .= "\r\n"; + } + $s .= '
' . ($c === '' ? ' ' : htmlspecialchars($c, ENT_QUOTES)) . '
'; + + return $s; + } + public function toHTMLEx($worksheetIndex = 0) + { + $s = ''; + $y = 0; + foreach ($this->readRowsEx($worksheetIndex) as $r) { + $s .= ''; + $x = 0; + foreach ($r as $c) { + $tag = 'td'; + $css = $c['css']; + if ($y === 0) { + $tag = 'th'; + $css .= $c['width'] ? 'width: '.round($c['width'] * 0.47, 2).'em;' : ''; + } + + if ($x === 0 && $c['height']) { + $css .= 'height: '.round($c['height'] * 1.3333).'px;'; + } + $s .= '<'.$tag.' style="'.$css.'" nowrap>' . ($c['value'] === '' ? ' ' : htmlspecialchars($c['value'], ENT_QUOTES)) . ''; + $x++; + } + $s .= "\r\n"; + $y++; + } + $s .= '
'; + + return $s; + } + public function rows($worksheetIndex = 0, $limit = 0) + { + return iterator_to_array($this->readRows($worksheetIndex, $limit), false); + } + // thx Gonzo + /** + * @param $worksheetIndex + * @param $limit + * @return \Generator + */ + public function readRows($worksheetIndex = 0, $limit = 0) + { + + if (($ws = $this->worksheet($worksheetIndex)) === false) { + return; + } + $dim = $this->dimension($worksheetIndex); + $numCols = $dim[0]; + $numRows = $dim[1]; + + $emptyRow = []; + for ($i = 0; $i < $numCols; $i++) { + $emptyRow[] = ''; + } + + $curR = 0; + $_limit = $limit; + /* @var SimpleXMLElement $ws */ + foreach ($ws->sheetData->row as $row) { + $r = $emptyRow; + $curC = 0; + foreach ($row->c as $c) { + // detect skipped cols + $idx = $this->getIndex((string)$c['r']); + $x = $idx[0]; + $y = $idx[1]; + + if ($x > -1) { + $curC = $x; + while ($curR < $y) { + yield $emptyRow; + $curR++; + $_limit--; + if ($_limit === 0) { + return; + } + } + } + $r[$curC] = $this->value($c); + $curC++; + } + yield $r; + + $curR++; + $_limit--; + if ($_limit === 0) { + return; + } + } + while ($curR < $numRows) { + yield $emptyRow; + $curR++; + $_limit--; + if ($_limit === 0) { + return; + } + } + } + + public function rowsEx($worksheetIndex = 0, $limit = 0) + { + return iterator_to_array($this->readRowsEx($worksheetIndex, $limit), false); + } + // https://github.com/shuchkin/simplexlsx#gets-extend-cell-info-by--rowsex + /** + * @param $worksheetIndex + * @param $limit + * @return \Generator|null + */ + public function readRowsEx($worksheetIndex = 0, $limit = 0) + { + if (!$this->rowsExReader) { + require_once __DIR__ . '/SimpleXLSXEx.php'; + $this->rowsExReader = new SimpleXLSXEx($this); + } + return $this->rowsExReader->readRowsEx($worksheetIndex, $limit); + } + + /** + * Returns cell value + * VERY SLOW! Use ->rows() or ->rowsEx() + * + * @param int $worksheetIndex + * @param string|array $cell ref or coords, D12 or [3,12] + * + * @return mixed Returns NULL if not found + */ + public function getCell($worksheetIndex = 0, $cell = 'A1') + { + + if (($ws = $this->worksheet($worksheetIndex)) === false) { + return false; + } + if (is_array($cell)) { + $cell = $this->_num2name($cell[0]) . $cell[1];// [3,21] -> D21 + } + if (is_string($cell)) { + $result = $ws->sheetData->xpath("row/c[@r='" . $cell . "']"); + if (count($result)) { + return $this->value($result[0]); + } + } + + return null; + } + + protected function _num2name($num) + { + $numeric = ($num - 1) % 26; + $letter = chr(65 + $numeric); + $num2 = (int)(($num - 1) / 26); + if ($num2 > 0) { + return $this->_num2name($num2) . $letter; + } + return $letter; + } + + public function getSheets() + { + return $this->sheets; + } + + public function sheetsCount() + { + return count($this->sheets); + } + + public function sheetName($worksheetIndex) + { + if (isset($this->sheetNames[$worksheetIndex])) { + return $this->sheetNames[$worksheetIndex]; + } + + return false; + } + + public function sheetNames() + { + + return $this->sheetNames; + } + + public function getStyles() + { + return $this->styles; + } + + public function getPackage() + { + return $this->package; + } + + public function setDateTimeFormat($value) + { + $this->datetimeFormat = is_string($value) ? $value : false; + } +} diff --git a/RS/ExcelCal/SimpleXLSXEx.php b/RS/ExcelCal/SimpleXLSXEx.php new file mode 100644 index 0000000..9c9e257 --- /dev/null +++ b/RS/ExcelCal/SimpleXLSXEx.php @@ -0,0 +1,601 @@ + '000000', + 1 => 'FFFFFF', + 2 => 'FF0000', + 3 => '00FF00', + 4 => '0000FF', + 5 => 'FFFF00', + 6 => 'FF00FF', + 7 => '00FFFF', + 8 => '000000', + 9 => 'FFFFFF', + 10 => 'FF0000', + 11 => '00FF00', + 12 => '0000FF', + 13 => 'FFFF00', + 14 => 'FF00FF', + 15 => '00FFFF', + 16 => '800000', + 17 => '008000', + 18 => '000080', + 19 => '808000', + 20 => '800080', + 21 => '008080', + 22 => 'C0C0C0', + 23 => '808080', + 24 => '9999FF', + 25 => '993366', + 26 => 'FFFFCC', + 27 => 'CCFFFF', + 28 => '660066', + 29 => 'FF8080', + 30 => '0066CC', + 31 => 'CCCCFF', + 32 => '000080', + 33 => 'FF00FF', + 34 => 'FFFF00', + 35 => '00FFFF', + 36 => '800080', + 37 => '800000', + 38 => '008080', + 39 => '0000FF', + 40 => '00CCFF', + 41 => 'CCFFFF', + 42 => 'CCFFCC', + 43 => 'FFFF99', + 44 => '99CCFF', + 45 => 'FF99CC', + 46 => 'CC99FF', + 47 => 'FFCC99', + 48 => '3366FF', + 49 => '33CCCC', + 50 => '99CC00', + 51 => 'FFCC00', + 52 => 'FF9900', + 53 => 'FF6600', + 54 => '666699', + 55 => '969696', + 56 => '003366', + 57 => '339966', + 58 => '003300', + 59 => '333300', + 60 => '993300', + 61 => '993366', + 62 => '333399', + 63 => '333333', + 64 => '000000', // System Foreground + 65 => 'FFFFFF', // System Background' + ]; + public static $CH = [ + 0 => 'ANSI_CHARSET', + 1 => 'DEFAULT_CHARSET', + 2 => 'SYMBOL_CHARSET', + 77 => 'MAC_CHARSET', + 128 => 'SHIFTJIS_CHARSET', + //129 => 'HANGEUL_CHARSET', + 129 => 'HANGUL_CHARSET', + 130 => 'JOHAB_CHARSET', + 134 => 'GB2312_CHARSET', + 136 => 'CHINESEBIG5_CHARSET', + 161 => 'GREEK_CHARSET', + 162 => 'TURKISH_CHARSET', + 163 => 'VIETNAMESE_CHARSET', + 177 => 'HEBREW_CHARSET', + 178 => 'ARABIC_CHARSET', + 186 => 'BALTIC_CHARSET', + 204 => 'RUSSIAN_CHARSET', + 222 => 'THAI_CHARSET', + 238 => 'EASTEUROPE_CHARSET', + 255 => 'OEM_CHARSET' + ]; + public $xlsx; + public $themeColors; + public $fonts; + public $fills; + public $borders; + public $cellStyles; + public $css; + + public function __construct(SimpleXLSX $xlsx) + { + $this->xlsx = $xlsx; + $this->readThemeColors(); + $this->readFonts(); + $this->readFills(); + $this->readBorders(); + $this->readXfs(); + } + public function readThemeColors() + { + $this->themeColors = []; + if (isset($this->xlsx->theme->themeElements->clrScheme)) { + $colors12 = ['lt1', 'dk1', 'lt2', 'dk2','accent1','accent2','accent3','accent4','accent5', + 'accent6','hlink','folHlink']; + foreach ($colors12 as $c) { + $v = $this->xlsx->theme->themeElements->clrScheme->{$c}; + if (isset($v->sysClr)) { + $this->themeColors[] = (string) $v->sysClr['lastClr']; + } elseif (isset($v->srgbClr)) { + $this->themeColors[] = (string) $v->srgbClr['val']; + } else { + $this->themeColors[] = null; + } + } + } + } + + + public function readFonts() + { + // fonts + $this->fonts = []; + if (isset($this->xlsx->styles->fonts->font)) { + foreach ($this->xlsx->styles->fonts->font as $v) { + $u = ''; + if (isset($v->u)) { + $u = isset($v->u['val']) ? (string) $v->u['val'] : 'single'; + } + $this->fonts[] = [ + 'b' => isset($v->b) && ($v->b['val'] === null || $v->b['val']), + 'i' => isset($v->i) && ($v->i['val'] === null || $v->i['val']), + 'u' => $u, + 'strike' => isset($v->strike) && ($v->strike['val'] === null || $v->strike['val']), + 'sz' => isset($v->sz['val']) ? (int) $v->sz['val'] : 11, + 'color' => $this->getColorValue($v->color), + 'name' => isset($v->name['val']) ? (string) $v->name['val'] : 'Calibri', + 'family' => isset($v->family['val']) ? (int) $v->family['val'] : 2, + 'charset' => isset($v->charset['val']) ? (int) $v->charset['val'] : 1, + 'scheme' => isset($v->scheme['val']) ? (string) $v->scheme['val'] : 'minor' + ]; + } + } + } + public function readFills() + { + // fills + $this->fills = []; + if (isset($this->xlsx->styles->fills->fill)) { + foreach ($this->xlsx->styles->fills->fill as $v) { + if (isset($v->patternFill)) { + $this->fills[] = [ + 'pattern' => isset($v->patternFill['patternType']) ? (string) $v->patternFill['patternType'] : 'none', + 'fgcolor' => $this->getColorValue($v->patternFill->fgColor), + 'bgcolor' => $this->getColorValue($v->patternFill->bgColor) + ]; + } + } + } + } + public function readBorders() + { + $this->borders = []; + if (isset($this->xlsx->styles->borders->border)) { + foreach ($this->xlsx->styles->borders->border as $v) { + $this->borders[] = [ + 'left' => [ + 'style' => (string) $v->left['style'], + 'color' => $this->getColorValue($v->left->color) + ], + 'right' => [ + 'style' => (string) $v->right['style'], + 'color' => $this->getColorValue($v->right->color) + ], + 'top' => [ + 'style' => (string) $v->top['style'], + 'color' => $this->getColorValue($v->top->color) + ], + 'bottom' => [ + 'style' => (string) $v->bottom['style'], + 'color' => $this->getColorValue($v->bottom->color) + ], + 'diagonal' => [ + 'style' => (string) $v->diagonal['style'], + 'color' => $this->getColorValue($v->diagonal->color) + ], + 'horizontal' => [ + 'style' => (string) $v->horizontal['style'], + 'color' => $this->getColorValue($v->horizontal->color) + ], + 'vertical' => [ + 'style' => (string) $v->vertical['style'], + 'color' => $this->getColorValue($v->vertical->color) + ], + 'diagonalUp' => (bool) $v['diagonalUp'], + 'diagonalDown' => (bool) $v['diagonalDown'], + 'outline' => !(isset($v['outline'])) || $v['outline'] + ]; + } + } + } + + public function readXfs() + { + // cellStyles + $this->cellStyles = []; + if (isset($this->xlsx->styles->cellStyleXfs->xf)) { + foreach ($this->xlsx->styles->cellStyleXfs->xf as $v) { + $x = []; + foreach ($v->attributes() as $k1 => $v1) { + $x[ $k1 ] = (int) $v1; + } + if (isset($v->alignment)) { + foreach ($v->alignment->attributes() as $k1 => $v1) { + $x['alignment'][$k1] = (string) $v1; + } + } + $this->cellStyles[] = $x; + } + } + // css + $this->css = []; + // xf + if (isset($this->xlsx->styles->cellXfs->xf)) { + $k = 0; + foreach ($this->xlsx->styles->cellXfs->xf as $v) { + $cf = &$this->xlsx->cellFormats[$k]; + + // alignment + $alignment = []; + if (isset($v->alignment)) { + foreach ($v->alignment->attributes() as $k1 => $v1) { + $alignment[$k1] = (string)$v1; + } + } + + if (isset($cf['xfId'], $this->cellStyles[ $cf['xfId'] ])) { + $s = $this->cellStyles[$cf['xfId']]; + if (!empty($s['applyNumberFormat'])) { + $cf['numFmtId'] = $s['numFmtId']; + } + if (!empty($s['applyFont'])) { + $cf['fontId'] = $s['fontId']; + } + if (!empty($s['applyBorder'])) { + $cf['borderId'] = $s['borderId']; + } + if (!empty($s['applyAlignment'])) { + $alignment = $s['alignment']; + } + } + $cf['alignment'] = $alignment; + + $align = null; + if (isset($alignment['horizontal'])) { + $align = $alignment['horizontal']; + if ($align === 'centerContinuous') { + $align = 'center'; + } + if ($align === 'distributed') { + $align = 'justify'; + } + if ($align === 'general') { + $align = null; + } + } + $cf['align'] = $align; + + $valign = null; + if (isset($alignment['vertical'])) { + $valign = $alignment['vertical']; + if ($valign === 'center' || $valign === 'distributed' || $valign === 'justify') { + $valign = 'middle'; + } + } + $cf['valign'] = $valign; + + // font + $cf['font'] = $this->fonts[ $cf['fontId'] ]['name']; + $cf['color'] = $this->fonts[ $cf['fontId'] ]['color']; + $cf['f-size'] = $this->fonts[ $cf['fontId'] ]['sz']; + $cf['f-b'] = $this->fonts[ $cf['fontId'] ]['b']; + $cf['f-i'] = $this->fonts[ $cf['fontId'] ]['i']; + $cf['f-u'] = $this->fonts[ $cf['fontId'] ]['u']; + $cf['f-strike'] = $this->fonts[ $cf['fontId'] ]['strike']; + + // fill + $cf['bgcolor'] = $this->fills[ $cf['fillId'] ]['fgcolor']; + + // borders + if (isset($this->borders[ $cf['borderId'] ])) { + $border = $this->borders[ $cf['borderId'] ]; + + $borders = ['left', 'right', 'top', 'bottom']; + foreach ($borders as $b) { + $cf['b-' . $b.'-color'] = $border[$b]['color']; + if ($border[$b]['style'] === '' || $border[$b]['style'] === 'none') { + $cf['b-' . $b.'-style'] = ''; + $cf['b-' . $b.'-color'] = ''; + } elseif ($border[$b]['style'] === 'dashDot' + || $border[$b]['style'] === 'dashDotDot' + || $border[$b]['style'] === 'dashed' + ) { + $cf['b-' . $b.'-style'] = 'dashed'; + } else { + $cf['b-' . $b.'-style'] = 'solid'; + } + } + } + + $css = ''; + + if ($cf['color']) { + $css .= 'color: #'.$cf['color'].';'; + } + if ($cf['font']) { + $css .= 'font-family: '.$cf['font'].';'; + } + if ($cf['f-size']) { +// $css .= 'font-size: '.($cf['f-size'] * 0.352806).'mm;'; + $css .= 'font-size: '.(round($cf['f-size'] * 1.3333) + 2).'px;'; + } + if ($cf['f-b']) { + $css .= 'font-weight: bold;'; + } + if ($cf['f-i']) { + $css .= 'font-style: italic;'; + } + if ($cf['f-u']) { + $css .= 'text-decoration: underline;'; + } + if ($cf['f-strike']) { + $css .= 'text-decoration: line-through;'; + } + if ($cf['bgcolor']) { + $css .= 'background-color: #' . $cf['bgcolor'] . ';'; + } + if ($cf['align']) { + $css .= 'text-align: '.$cf['align'].';'; + } + if ($cf['valign']) { + $css .= 'vertical-align: '.$cf['valign'].';'; + } + if ($cf['b-top-style']) { + $css .= 'border-top-style: '.$cf['b-top-style'].';'; + $css .= 'border-top-color: #'.$cf['b-top-color'].';'; + $css .= 'border-top-width: thin;'; + } + if ($cf['b-right-style']) { + $css .= 'border-right-style: '.$cf['b-right-style'].';'; + $css .= 'border-right-color: #'.$cf['b-right-color'].';'; + $css .= 'border-right-width: thin;'; + } + if ($cf['b-bottom-style']) { + $css .= 'border-bottom-style: '.$cf['b-bottom-style'].';'; + $css .= 'border-bottom-color: #'.$cf['b-bottom-color'].';'; + $css .= 'border-bottom-width: thin;'; + } + if ($cf['b-left-style']) { + $css .= 'border-left-style: '.$cf['b-left-style'].';'; + $css .= 'border-left-color: #'.$cf['b-left-color'].';'; + $css .= 'border-left-width: thin;'; + } + $this->css[$k] = $css; + + $k++; + } + } + } + + public function readRowsEx($worksheetIndex = 0, $limit = 0) + { + if (($ws = $this->xlsx->worksheet($worksheetIndex)) === false) { + return; + } + + $dim = $this->xlsx->dimension($worksheetIndex); + $numCols = $dim[0]; + $numRows = $dim[1]; + + /*$emptyRow = array(); + for ($i = 0; $i < $numCols; $i++) { + $emptyRow[] = null; + } + */ + $cols = []; + for ($i = 0; $i < $numCols; $i++) { + $cols[] = ['s' => 0, 'hidden' => false, 'width' => 0]; + } +// $hiddenCols = []; + /* @var SimpleXMLElement $ws */ + if (isset($ws->cols)) { + foreach ($ws->cols->col as $col) { + $min = (int)$col['min']; + $max = (int)$col['max']; + if (($max-$min) > 100) { + $max = $min; + } + for ($i = $min; $i <= $max; $i++) { + $cols[$i-1] = [ + 's' => (int)$col['style'], + 'hidden' => (bool)$col['hidden'], + 'width' => $col['customWidth'] ? (float) $col['width'] : 0 + ]; + } + } + } + + $curR = 0; + $_limit = $limit; + + foreach ($ws->sheetData->row as $row) { + $curC = 0; + + $r_idx = (int)$row['r']; + $r_style = ['s' => 0, 'hidden' => (bool)$row['hidden'], 'height' => 0]; + if ($row['customFormat']) { + $r_style['s'] = (int)$row['s']; + } + if ($row['customHeight']) { + $r_style['height'] = (int)$row['ht']; + } + + $cells = []; + for ($i = 0; $i < $numCols; $i++) { + $cells[] = null; + } + + foreach ($row->c as $c) { + $r = (string)$c['r']; + $t = (string)$c['t']; + $s = (int)$c['s']; + + $idx = $this->xlsx->getIndex($r); + $x = $idx[0]; + $y = $idx[1]; + + if ($x > -1) { + $curC = $x; + if ($curC >= $numCols) { + $numCols = $curC + 1; + } + while ($curR < $y) { + $emptyRow = []; + for ($i = 0; $i < $numCols; $i++) { + $emptyRow[] = $this->valueEx($cols[$i], $i, $curR); + } + yield $emptyRow; + $curR++; + + $_limit--; + if ($_limit === 0) { + return; + } + } + } + $data = [ + 'type' => $t, + 'name' => $r, + 'value' => $this->xlsx->value($c), + 'href' => $this->xlsx->href($worksheetIndex, $c), + 'f' => (string)$c->f, + 'r' => $r_idx, + 's' => ($s > 0) ? $s : $cols[$curC]['s'], + 'hidden' => $r_style['hidden'] || $cols[$curC]['hidden'], + 'width' => $cols[$curC]['width'], + 'height' => $r_style['height'] + ]; + $cells[$curC] = $this->valueEx($data, $curC, $curR); + + $curC++; + } + // check empty cells + for ($i = 0; $i < $numCols; $i++) { + if ($cells[$i] === null) { + if ($r_style['s'] > 0) { + $data = $r_style; + } else { + $data = $cols[$i]; + } + $data['width'] = $cols[$i]['width']; + $data['height'] = $r_style['height']; + $cells[$i] = $this->valueEx($data, $i, $curR); + } + } + + yield $cells; + + $curR++; + $_limit--; + if ($_limit === 0) { + break; + } + } + + while ($curR < $numRows) { + $emptyRow = []; + for ($i = 0; $i < $numCols; $i++) { + $data = $cols[$i]; + $emptyRow[] = $this->valueEx($data, $i, $curR); + } + yield $emptyRow; + $curR++; + $_limit--; + if ($_limit === 0) { + return; + } + } + } + + protected function valueEx($data, $x = null, $y = null) + { + + $r = [ + 'type' => '', + 'name' => '', + 'value' => '', + 'href' => '', + 'f' => '', + 'format' => '', + 's' => 0, + 'css' => '', + 'r' => '', + 'hidden' => false, + 'width' => 0, + 'height' => 0 + ]; + foreach ($data as $k => $v) { + if (isset($r[$k])) { + $r[$k] = $v; + } + } + $st = &$this->xlsx->cellFormats[$r['s']]; + $r['format'] = $st['format']; + $r['css'] = &$this->css[ $r['s'] ]; + if ($r['value'] !== '' && !$st['align'] && !in_array($r['type'], ['s','str','inlineStr','e'], true)) { + $r['css'] .= 'text-align: right;'; + } + + if (!$r['name']) { + $c = ''; + for ($k = $x; $k >= 0; $k = (int)($k / 26) - 1) { + $c = chr($k % 26 + 65) . $c; + } + $r['name'] = $c . ($y + 1); + $r['r'] = $y+1; + } + return $r; + } + public function getColorValue(SimpleXMLElement $a = null, $default = '') + { + if ($a === null) { + return $default; + } + $c = $default; // auto + if ($a['rgb'] !== null) { + $c = substr((string) $a['rgb'], 2); // FFCCBBAA -> CCBBAA + } elseif ($a['indexed'] !== null && isset(static::$IC[ (int) $a['indexed'] ])) { + $c = static::$IC[ (int) $a['indexed'] ]; + } elseif ($a['theme'] !== null && isset($this->themeColors[ (int) $a['theme'] ])) { + $c = $this->themeColors[ (int) $a['theme'] ]; + } + if ($a['tint'] !== null) { + list($r,$g,$b) = array_map('hexdec', str_split($c, 2)); + $tint = (float) $a['tint']; + if ($tint > 0) { + $r += (255 - $r) * $tint; + $g += (255 - $g) * $tint; + $b += (255 - $b) * $tint; + } else { + $r += $r * $tint; + $g += $g * $tint; + $b += $b * $tint; + } + $c = strtoupper( + str_pad(dechex((int) $r), 2, '0', 0) . + str_pad(dechex((int) $g), 2, '0', 0) . + str_pad(dechex((int) $b), 2, '0', 0) + ); + } + return $c; + } +} diff --git a/RS/ExcelCal/xls2ical.php b/RS/ExcelCal/xls2ical.php new file mode 100644 index 0000000..b8688fd --- /dev/null +++ b/RS/ExcelCal/xls2ical.php @@ -0,0 +1,126 @@ +sheetNames()); $i++){ + //echo $i." : ".$xlsx->sheetName($i)."\n"; + if($xlsx->sheetName($i) == "User Map"){ + $usermap = $i; + //echo "Users sheet: $usermap\n"; + } + } + //echo "User: ".$user."\n"; + $ical = "BEGIN:VCALENDAR +VERSION:2.0 +PRODID:-//$user, Inc.//EN"; + + + + // all other sheets != usermap + for($i = 0; $i <= sizeof($xlsx->sheetNames())-1; $i++){ + if($xlsx->sheetName($i) != "User Map"){ + + // populate map of jobs > 1 day long + foreach( $xlsx->worksheet($i)->mergeCells->mergeCell as $c ) { + $vals = explode(":", $c['ref']); + $concatMap[$i.'-'.$vals[0]] = $vals[1]; + } + + //echo "### ".$xlsx->sheetName($i)." ###\n"; + + foreach( $xlsx->rowsEx($i) as $rowsEx){ + for($j=1; $j<= sizeof($rowsEx)-2; $j++){ + + if(trim($rowsEx[0]['value']) == $user){ // found user + + + if(trim($rowsEx[$j]['value']) <> "" && trim($rowsEx[$j]['value']) <> "."){ // if contains data + //echo $j." ".$xlsx->sheetName($i)." : " .$rowsEx[$j]['value']."\n"; + //print_r($rowsEx[$j]); + $diff = 1; + if(isset($concatMap[$i."-" .$rowsEx[$j]['name']])){ + //echo "more than 1 day! \n"; + $diff += getDiff($i."-" .$rowsEx[$j]['name'], $concatMap[$i."-" .$rowsEx[$j]['name']]); + //echo $diff."\n"; + } + + $ical .= " +BEGIN:VEVENT +UID:" .md5(uniqid(mt_rand(), true))." +DTSTAMP:".gmdate("Ymd")."T".gmdate("His")."Z +DTSTART:".date("Ymd", strtotime($j." ".$xlsx->sheetName($i))); + +if($diff >= 1) + $ical .= "\nDTEND:".date("Ymd", strtotime($j." ".$xlsx->sheetName($i) ." +$diff day")); + +$ical .= " +SUMMARY:Booking for ".$rowsEx[$j]['value']." +END:VEVENT"; + + + } + + } + } + } + } + } + + + $ical .= " +END:VCALENDAR"; + +header("Content-type: text/calendar; charset=utf-8"); +header("Content-Disposition: inline; filename=calendar.ics"); +echo $ical; + +} else { + echo SimpleXLSX::parseError(); +} + +function getDiff($start, $end){ + $start = preg_replace("/[^A-Z]+/", "", $start); + $end = preg_replace("/[^A-Z]+/", "", $end); + $s1 = alphabet_to_number($start); + $e1 = alphabet_to_number($end); + return $e1 - $s1; + //return 2; +} +function alphabet_to_number($string) { + $string = strtoupper($string); + $length = strlen($string); + $number = 0; + $level = 1; + while ($length >= $level ) { + $char = $string[$length - $level]; + $c = ord($char) - 64; + $number += $c * (26 ** ($level-1)); + $level++; + } + return $number; +} +?> \ No newline at end of file diff --git a/RS/ExcelCal/SimpleXLSX.php b/RS/ExcelCal/SimpleXLSX.php new file mode 100644 index 0000000..c9776fb --- /dev/null +++ b/RS/ExcelCal/SimpleXLSX.php @@ -0,0 +1,1224 @@ +rows() as $r) { + * print_r( $r ); + * } + * } else { + * echo SimpleXLSX::parseError(); + * } + * + * Example 2: html table + * if ( $xlsx = SimpleXLSX::parse('book.xlsx') ) { + * echo $xlsx->toHTML(); + * } else { + * echo SimpleXLSX::parseError(); + * } + * + * Example 3: rowsEx + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * foreach ( $xlsx->rowsEx() as $r ) { + * print_r( $r ); + * } + * + * Example 4: select worksheet + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * foreach( $xlsx->rows(1) as $r ) { // second worksheet + * print_t( $r ); + * } + * + * Example 5: IDs and worksheet names + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * print_r( $xlsx->sheetNames() ); // array( 0 => 'Sheet 1', 1 => 'Catalog' ); + * + * Example 6: get sheet name by index + * $xlsx = SimpleXLSX::parse('book.xlsx'); + * echo 'Sheet Name 2 = '.$xlsx->sheetName(1); + * + * Example 7: getCell (very slow) + * echo $xlsx->getCell(1,'D12'); // reads D12 cell from second sheet + * + * Example 8: read data + * if ( $xlsx = SimpleXLSX::parseData( file_get_contents('http://www.example.com/example.xlsx') ) ) { + * $dim = $xlsx->dimension(1); + * $num_cols = $dim[0]; + * $num_rows = $dim[1]; + * echo $xlsx->sheetName(1).':'.$num_cols.'x'.$num_rows; + * } else { + * echo SimpleXLSX::parseError(); + * } + * + * Example 9: old style + * $xlsx = new SimpleXLSX('book.xlsx'); + * if ( $xlsx->success() ) { + * print_r( $xlsx->rows() ); + * } else { + * echo 'xlsx error: '.$xlsx->error(); + * } + */ +class SimpleXLSX +{ + // Don't remove this string! Created by Sergey Shuchkin sergey.shuchkin@gmail.com + public static $CF = [ // Cell formats + 0 => 'General', + 1 => '0', + 2 => '0.00', + 3 => '#,##0', + 4 => '#,##0.00', + 9 => '0%', + 10 => '0.00%', + 11 => '0.00E+00', + 12 => '# ?/?', + 13 => '# ??/??', + 14 => 'mm-dd-yy', + 15 => 'd-mmm-yy', + 16 => 'd-mmm', + 17 => 'mmm-yy', + 18 => 'h:mm AM/PM', + 19 => 'h:mm:ss AM/PM', + 20 => 'h:mm', + 21 => 'h:mm:ss', + 22 => 'm/d/yy h:mm', + + 37 => '#,##0 ;(#,##0)', + 38 => '#,##0 ;[Red](#,##0)', + 39 => '#,##0.00;(#,##0.00)', + 40 => '#,##0.00;[Red](#,##0.00)', + + 44 => '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)', + 45 => 'mm:ss', + 46 => '[h]:mm:ss', + 47 => 'mmss.0', + 48 => '##0.0E+0', + 49 => '@', + + 27 => '[$-404]e/m/d', + 30 => 'm/d/yy', + 36 => '[$-404]e/m/d', + 50 => '[$-404]e/m/d', + 57 => '[$-404]e/m/d', + + 59 => 't0', + 60 => 't0.00', + 61 => 't#,##0', + 62 => 't#,##0.00', + 67 => 't0%', + 68 => 't0.00%', + 69 => 't# ?/?', + 70 => 't# ??/??', + ]; + public $nf = []; // number formats + public $cellFormats = []; // cellXfs + public $datetimeFormat = 'Y-m-d H:i:s'; + public $debug; + public $activeSheet = 0; + public $rowsExReader; + + /* @var SimpleXMLElement[] $sheets */ + protected $sheets; + protected $sheetNames = []; + protected $sheetFiles = []; + // scheme + public $styles; + protected $hyperlinks; + /* @var array[] $package */ + protected $package; + protected $sharedstrings; + protected $date1904 = 0; + + + /* + private $date_formats = array( + 0xe => "d/m/Y", + 0xf => "d-M-Y", + 0x10 => "d-M", + 0x11 => "M-Y", + 0x12 => "h:i a", + 0x13 => "h:i:s a", + 0x14 => "H:i", + 0x15 => "H:i:s", + 0x16 => "d/m/Y H:i", + 0x2d => "i:s", + 0x2e => "H:i:s", + 0x2f => "i:s.S" + ); + private $number_formats = array( + 0x1 => "%1.0f", // "0" + 0x2 => "%1.2f", // "0.00", + 0x3 => "%1.0f", //"#,##0", + 0x4 => "%1.2f", //"#,##0.00", + 0x5 => "%1.0f", //"$#,##0;($#,##0)", + 0x6 => '$%1.0f', //"$#,##0;($#,##0)", + 0x7 => '$%1.2f', //"$#,##0.00;($#,##0.00)", + 0x8 => '$%1.2f', //"$#,##0.00;($#,##0.00)", + 0x9 => '%1.0f%%', //"0%" + 0xa => '%1.2f%%', //"0.00%" + 0xb => '%1.2f', //"0.00E00", + 0x25 => '%1.0f', //"#,##0;(#,##0)", + 0x26 => '%1.0f', //"#,##0;(#,##0)", + 0x27 => '%1.2f', //"#,##0.00;(#,##0.00)", + 0x28 => '%1.2f', //"#,##0.00;(#,##0.00)", + 0x29 => '%1.0f', //"#,##0;(#,##0)", + 0x2a => '$%1.0f', //"$#,##0;($#,##0)", + 0x2b => '%1.2f', //"#,##0.00;(#,##0.00)", + 0x2c => '$%1.2f', //"$#,##0.00;($#,##0.00)", + 0x30 => '%1.0f'); //"##0.0E0"; + // }}} + */ + protected $errno = 0; + protected $error = false; + /** + * @var false|SimpleXMLElement + */ + public $theme; + + + public function __construct($filename = null, $is_data = null, $debug = null) + { + if ($debug !== null) { + $this->debug = $debug; + } + $this->package = [ + 'filename' => '', + 'mtime' => 0, + 'size' => 0, + 'comment' => '', + 'entries' => [] + ]; + if ($filename && $this->_unzip($filename, $is_data)) { + $this->_parse(); + } + } + + protected function _unzip($filename, $is_data = false) + { + + if ($is_data) { + $this->package['filename'] = 'default.xlsx'; + $this->package['mtime'] = time(); + $this->package['size'] = $this->_strlen($filename); + + $vZ = $filename; + } else { + if (!is_readable($filename)) { + $this->error(1, 'File not found ' . $filename); + + return false; + } + + // Package information + $this->package['filename'] = $filename; + $this->package['mtime'] = filemtime($filename); + $this->package['size'] = filesize($filename); + + // Read file + $vZ = file_get_contents($filename); + } + // Cut end of central directory + /* $aE = explode("\x50\x4b\x05\x06", $vZ); + + if (count($aE) == 1) { + $this->error('Unknown format'); + return false; + } + */ + // Explode to each part + $aE = explode("\x50\x4b\x03\x04", $vZ); + array_shift($aE); + + $aEL = count($aE); + if ($aEL === 0) { + $this->error(2, 'Unknown archive format'); + + return false; + } + // Search central directory end record + $last = $aE[$aEL - 1]; + $last = explode("\x50\x4b\x05\x06", $last); + if (count($last) !== 2) { + $this->error(2, 'Unknown archive format'); + + return false; + } + // Search central directory + $last = explode("\x50\x4b\x01\x02", $last[0]); + if (count($last) < 2) { + $this->error(2, 'Unknown archive format'); + + return false; + } + $aE[$aEL - 1] = $last[0]; + + // Loop through the entries + foreach ($aE as $vZ) { + $aI = []; + $aI['E'] = 0; + $aI['EM'] = ''; + // Retrieving local file header information +// $aP = unpack('v1VN/v1GPF/v1CM/v1FT/v1FD/V1CRC/V1CS/V1UCS/v1FNL', $vZ); + $aP = unpack('v1VN/v1GPF/v1CM/v1FT/v1FD/V1CRC/V1CS/V1UCS/v1FNL/v1EFL', $vZ); + + // Check if data is encrypted +// $bE = ($aP['GPF'] && 0x0001) ? TRUE : FALSE; +// $bE = false; + $nF = $aP['FNL']; + $mF = $aP['EFL']; + + // Special case : value block after the compressed data + if ($aP['GPF'] & 0x0008) { + $aP1 = unpack('V1CRC/V1CS/V1UCS', $this->_substr($vZ, -12)); + + $aP['CRC'] = $aP1['CRC']; + $aP['CS'] = $aP1['CS']; + $aP['UCS'] = $aP1['UCS']; + // 2013-08-10 + $vZ = $this->_substr($vZ, 0, -12); + if ($this->_substr($vZ, -4) === "\x50\x4b\x07\x08") { + $vZ = $this->_substr($vZ, 0, -4); + } + } + + // Getting stored filename + $aI['N'] = $this->_substr($vZ, 26, $nF); + $aI['N'] = str_replace('\\', '/', $aI['N']); + + if ($this->_substr($aI['N'], -1) === '/') { + // is a directory entry - will be skipped + continue; + } + + // Truncate full filename in path and filename + $aI['P'] = dirname($aI['N']); + $aI['P'] = ($aI['P'] === '.') ? '' : $aI['P']; + $aI['N'] = basename($aI['N']); + + $vZ = $this->_substr($vZ, 26 + $nF + $mF); + + if ($this->_strlen($vZ) !== (int)$aP['CS']) { // check only if availabled + $aI['E'] = 1; + $aI['EM'] = 'Compressed size is not equal with the value in header information.'; + } +// } elseif ( $bE ) { +// $aI['E'] = 5; +// $aI['EM'] = 'File is encrypted, which is not supported from this class.'; +/* } else { + switch ($aP['CM']) { + case 0: // Stored + // Here is nothing to do, the file ist flat. + break; + case 8: // Deflated + $vZ = gzinflate($vZ); + break; + case 12: // BZIP2 + if (extension_loaded('bz2')) { + $vZ = bzdecompress($vZ); + } else { + $aI['E'] = 7; + $aI['EM'] = 'PHP BZIP2 extension not available.'; + } + break; + default: + $aI['E'] = 6; + $aI['EM'] = "De-/Compression method {$aP['CM']} is not supported."; + } + if (!$aI['E']) { + if ($vZ === false) { + $aI['E'] = 2; + $aI['EM'] = 'Decompression of data failed.'; + } elseif ($this->_strlen($vZ) !== (int)$aP['UCS']) { + $aI['E'] = 3; + $aI['EM'] = 'Uncompressed size is not equal with the value in header information.'; + } elseif (crc32($vZ) !== $aP['CRC']) { + $aI['E'] = 4; + $aI['EM'] = 'CRC32 checksum is not equal with the value in header information.'; + } + } + } +*/ + + // DOS to UNIX timestamp + $aI['T'] = mktime( + ($aP['FT'] & 0xf800) >> 11, + ($aP['FT'] & 0x07e0) >> 5, + ($aP['FT'] & 0x001f) << 1, + ($aP['FD'] & 0x01e0) >> 5, + $aP['FD'] & 0x001f, + (($aP['FD'] & 0xfe00) >> 9) + 1980 + ); + + $this->package['entries'][] = [ + 'data' => $vZ, + 'ucs' => (int)$aP['UCS'], // ucompresses size + 'cm' => $aP['CM'], // compressed method + 'cs' => isset($aP['CS']) ? (int) $aP['CS'] : 0, // compresses size + 'crc' => $aP['CRC'], + 'error' => $aI['E'], + 'error_msg' => $aI['EM'], + 'name' => $aI['N'], + 'path' => $aI['P'], + 'time' => $aI['T'] + ]; + } // end for each entries + + return true; + } + + protected function _strlen($str) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_strlen($str, '8bit') : strlen($str); + } + + public function error($num = null, $str = null) + { + if ($num) { + $this->errno = $num; + $this->error = $str; + if ($this->debug) { + trigger_error(__CLASS__ . ': ' . $this->error, E_USER_WARNING); + } + } + + return $this->error; + } + + protected function _substr($str, $start, $length = null) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_substr($str, $start, ($length === null) ? mb_strlen($str, '8bit') : $length, '8bit') : substr($str, $start, ($length === null) ? strlen($str) : $length); + } + + protected function _parse() + { + // Document data holders + $this->sharedstrings = []; + $this->sheets = []; +// $this->styles = array(); +// $m1 = 0; // memory_get_peak_usage( true ); + // Read relations and search for officeDocument + if ($relations = $this->getEntryXML('_rels/.rels')) { + foreach ($relations->Relationship as $rel) { + $rel_type = basename(trim((string)$rel['Type'])); // officeDocument + $rel_target = $this->_getTarget('', (string)$rel['Target']); // /xl/workbook.xml or xl/workbook.xml + + if ($rel_type === 'officeDocument' && $workbook = $this->getEntryXML($rel_target)) { + $index_rId = []; // [0 => rId1] + + $index = 0; + foreach ($workbook->sheets->sheet as $s) { + $this->sheetNames[$index] = (string)$s['name']; + $index_rId[$index] = (string)$s['id']; + $index++; + } + if ((int)$workbook->workbookPr['date1904'] === 1) { + $this->date1904 = 1; + } + + + if ($workbookRelations = $this->getEntryXML(dirname($rel_target) . '/_rels/workbook.xml.rels')) { + // Loop relations for workbook and extract sheets... + foreach ($workbookRelations->Relationship as $workbookRelation) { + $wrel_type = basename(trim((string)$workbookRelation['Type'])); // worksheet + $wrel_path = $this->_getTarget(dirname($rel_target), (string)$workbookRelation['Target']); + if (!$this->entryExists($wrel_path)) { + continue; + } + + + if ($wrel_type === 'worksheet') { // Sheets + if ($sheet = $this->getEntryXML($wrel_path)) { + $index = array_search((string)$workbookRelation['Id'], $index_rId, true); + $this->sheets[$index] = $sheet; + $this->sheetFiles[$index] = $wrel_path; + } + } elseif ($wrel_type === 'sharedStrings') { + if ($sharedStrings = $this->getEntryXML($wrel_path)) { + foreach ($sharedStrings->si as $val) { + if (isset($val->t)) { + $this->sharedstrings[] = (string)$val->t; + } elseif (isset($val->r)) { + $this->sharedstrings[] = $this->_parseRichText($val); + } + } + } + } elseif ($wrel_type === 'styles') { + $this->styles = $this->getEntryXML($wrel_path); + + // number formats + $this->nf = []; + if (isset($this->styles->numFmts->numFmt)) { + foreach ($this->styles->numFmts->numFmt as $v) { + $this->nf[(int)$v['numFmtId']] = (string)$v['formatCode']; + } + } + + $this->cellFormats = []; + if (isset($this->styles->cellXfs->xf)) { + foreach ($this->styles->cellXfs->xf as $v) { + $x = [ + 'format' => null + ]; + foreach ($v->attributes() as $k1 => $v1) { + $x[ $k1 ] = (int) $v1; + } + if (isset($x['numFmtId'])) { + if (isset($this->nf[$x['numFmtId']])) { + $x['format'] = $this->nf[$x['numFmtId']]; + } elseif (isset(self::$CF[$x['numFmtId']])) { + $x['format'] = self::$CF[$x['numFmtId']]; + } + } + + $this->cellFormats[] = $x; + } + } + } elseif ($wrel_type === 'theme') { + $this->theme = $this->getEntryXML($wrel_path); + } + } + + break; + } + // reptile hack :: find active sheet from workbook.xml + foreach ($workbook->bookViews->workbookView as $s) { + if (!empty($s['activeTab'])) { + $this->activeSheet = (int)$s['activeTab']; + } + } + } + } + } + +// $m2 = memory_get_peak_usage(true); +// echo __FUNCTION__.' M='.round( ($m2-$m1) / 1048576, 2).'MB'.PHP_EOL; + + if (count($this->sheets)) { + // Sort sheets + ksort($this->sheets); + + return true; + } + + return false; + } + + public function getEntryXML($name) + { + if ($entry_xml = $this->getEntryData($name)) { + $this->deleteEntry($name); // economy memory + // dirty remove namespace prefixes and empty rows + $entry_xml = preg_replace('/xmlns[^=]*="[^"]*"/i', '', $entry_xml); // remove namespaces + $entry_xml .= ' '; // force run garbage collector + $entry_xml = preg_replace('/[a-zA-Z0-9]+:([a-zA-Z0-9]+="[^"]+")/', '$1', $entry_xml); // remove namespaced attrs + $entry_xml .= ' '; + $entry_xml = preg_replace('/<[a-zA-Z0-9]+:([^>]+)>/', '<$1>', $entry_xml); // fix namespaced openned tags + $entry_xml .= ' '; + $entry_xml = preg_replace('/<\/[a-zA-Z0-9]+:([^>]+)>/', '', $entry_xml); // fix namespaced closed tags + $entry_xml .= ' '; + + if (strpos($name, '/sheet')) { // dirty skip empty rows + // remove + $entry_xml = preg_replace('/]+>\s*(\s*)+<\/row>/', '', $entry_xml, -1, $cnt); + $entry_xml .= ' '; + // remove + $entry_xml = preg_replace('/]*\/>/', '', $entry_xml, -1, $cnt2); + $entry_xml .= ' '; + // remove + $entry_xml = preg_replace('/]*><\/row>/', '', $entry_xml, -1, $cnt3); + $entry_xml .= ' '; + if ($cnt || $cnt2 || $cnt3) { + $entry_xml = preg_replace('//', '', $entry_xml); + $entry_xml .= ' '; + } +// file_put_contents( basename( $name ), $entry_xml ); // @to do comment!!! + } + $entry_xml = trim($entry_xml); + +// $m1 = memory_get_usage(); + // XML External Entity (XXE) Prevention, libxml_disable_entity_loader deprecated in PHP 8 + if (LIBXML_VERSION < 20900 && function_exists('libxml_disable_entity_loader')) { + $_old = libxml_disable_entity_loader(); + } + + $_old_uie = libxml_use_internal_errors(true); + + $entry_xmlobj = simplexml_load_string($entry_xml, 'SimpleXMLElement', LIBXML_COMPACT | LIBXML_PARSEHUGE); + + libxml_use_internal_errors($_old_uie); + + if (LIBXML_VERSION < 20900 && function_exists('libxml_disable_entity_loader')) { + /** @noinspection PhpUndefinedVariableInspection */ + libxml_disable_entity_loader($_old); + } + +// $m2 = memory_get_usage(); +// echo round( ($m2-$m1) / (1024 * 1024), 2).' MB'.PHP_EOL; + + if ($entry_xmlobj) { + return $entry_xmlobj; + } + $e = libxml_get_last_error(); + if ($e) { + $this->error(3, 'XML-entry ' . $name . ' parser error ' . $e->message . ' line ' . $e->line); + } + } else { + $this->error(4, 'XML-entry not found ' . $name); + } + + return false; + } + + // sheets numeration: 1,2,3.... + + public function getEntryData($name) + { + $name = ltrim(str_replace('\\', '/', $name), '/'); + $dir = $this->_strtoupper(dirname($name)); + $name = $this->_strtoupper(basename($name)); + foreach ($this->package['entries'] as &$entry) { + if ($this->_strtoupper($entry['path']) === $dir && $this->_strtoupper($entry['name']) === $name) { + if ($entry['error']) { + return false; + } + switch ($entry['cm']) { + case -1: + case 0: // Stored + // Here is nothing to do, the file ist flat. + break; + case 8: // Deflated + $entry['data'] = gzinflate($entry['data']); + break; + case 12: // BZIP2 + if (extension_loaded('bz2')) { + $entry['data'] = bzdecompress($entry['data']); + } else { + $entry['error'] = 7; + $entry['error_message'] = 'PHP BZIP2 extension not available.'; + } + break; + default: + $entry['error'] = 6; + $entry['error_msg'] = 'De-/Compression method '.$entry['cm'].' is not supported.'; + } + if (!$entry['error'] && $entry['cm'] > -1) { + $entry['cm'] = -1; + if ($entry['data'] === false) { + $entry['error'] = 2; + $entry['error_msg'] = 'Decompression of data failed.'; + } elseif ($this->_strlen($entry['data']) !== (int)$entry['ucs']) { + $entry['error'] = 3; + $entry['error_msg'] = 'Uncompressed size is not equal with the value in header information.'; + } elseif (crc32($entry['data']) !== $entry['crc']) { + $entry['error'] = 4; + $entry['error_msg'] = 'CRC32 checksum is not equal with the value in header information.'; + } + } + + return $entry['data']; + } + } + unset($entry); + $this->error(5, 'Entry not found ' . ($dir ? $dir . '/' : '') . $name); + + return false; + } + public function deleteEntry($name) + { + $name = ltrim(str_replace('\\', '/', $name), '/'); + $dir = $this->_strtoupper(dirname($name)); + $name = $this->_strtoupper(basename($name)); + foreach ($this->package['entries'] as $k => $entry) { + if ($this->_strtoupper($entry['path']) === $dir && $this->_strtoupper($entry['name']) === $name) { + unset($this->package['entries'][$k]); + return true; + } + } + return false; + } + + protected function _strtoupper($str) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_strtoupper($str, '8bit') : strtoupper($str); + } + + protected function _getTarget($base, $target) + { + $target = trim($target); + if (strpos($target, '/') === 0) { + return $this->_substr($target, 1); + } + $target = ($base ? $base . '/' : '') . $target; + // a/b/../c -> a/c + $parts = explode('/', $target); + $abs = []; + foreach ($parts as $p) { + if ('.' === $p) { + continue; + } + if ('..' === $p) { + array_pop($abs); + } else { + $abs[] = $p; + } + } + return implode('/', $abs); + } + + /* + * @param string $name Filename in archive + * @return SimpleXMLElement|bool + */ + + public function entryExists($name) + { + // 0.6.6 + $dir = $this->_strtoupper(dirname($name)); + $name = $this->_strtoupper(basename($name)); + foreach ($this->package['entries'] as $entry) { + if ($this->_strtoupper($entry['path']) === $dir && $this->_strtoupper($entry['name']) === $name) { + return true; + } + } + + return false; + } + + protected function _parseRichText($is = null) + { + $value = []; + + if (isset($is->t)) { + $value[] = (string)$is->t; + } elseif (isset($is->r)) { + foreach ($is->r as $run) { + $value[] = (string)$run->t; + } + } + + return implode('', $value); + } + + public static function parseFile($filename, $debug = false) + { + return self::parse($filename, false, $debug); + } + + public static function parse($filename, $is_data = false, $debug = false) + { + $xlsx = new self(); + $xlsx->debug = $debug; + if ($xlsx->_unzip($filename, $is_data)) { + $xlsx->_parse(); + } + if ($xlsx->success()) { + return $xlsx; + } + self::parseError($xlsx->error()); + self::parseErrno($xlsx->errno()); + + return false; + } + + public function success() + { + return !$this->error; + } + + // https://github.com/shuchkin/simplexlsx#gets-extend-cell-info-by--rowsex + + public static function parseError($set = false) + { + static $error = false; + + return $set ? $error = $set : $error; + } + + public static function parseErrno($set = false) + { + static $errno = false; + + return $set ? $errno = $set : $errno; + } + + public function errno() + { + return $this->errno; + } + + public static function parseData($data, $debug = false) + { + return self::parse($data, true, $debug); + } + + + + public function worksheet($worksheetIndex = 0) + { + + + if (isset($this->sheets[$worksheetIndex])) { + $ws = $this->sheets[$worksheetIndex]; + + if (!isset($this->hyperlinks[$worksheetIndex]) && isset($ws->hyperlinks)) { + $this->hyperlinks[$worksheetIndex] = []; + $sheet_rels = str_replace('worksheets', 'worksheets/_rels', $this->sheetFiles[$worksheetIndex]) . '.rels'; + $link_ids = []; + + if ($rels = $this->getEntryXML($sheet_rels)) { + // hyperlink +// $rel_base = dirname( $sheet_rels ); + foreach ($rels->Relationship as $rel) { + $rel_type = basename(trim((string)$rel['Type'])); + if ($rel_type === 'hyperlink') { + $rel_id = (string)$rel['Id']; + $rel_target = (string)$rel['Target']; + $link_ids[$rel_id] = $rel_target; + } + } + } + foreach ($ws->hyperlinks->hyperlink as $hyperlink) { + $ref = (string)$hyperlink['ref']; + if ($this->_strpos($ref, ':') > 0) { // A1:A8 -> A1 + $ref = explode(':', $ref); + $ref = $ref[0]; + } +// $this->hyperlinks[ $worksheetIndex ][ $ref ] = (string) $hyperlink['display']; + $loc = (string)$hyperlink['location']; + $id = (string)$hyperlink['id']; + if ($id) { + $href = $link_ids[$id] . ($loc ? '#' . $loc : ''); + } else { + $href = $loc; + } + $this->hyperlinks[$worksheetIndex][$ref] = $href; + } + } + + return $ws; + } + $this->error(6, 'Worksheet not found ' . $worksheetIndex); + + return false; + } + + protected function _strpos($haystack, $needle, $offset = 0) + { + return (ini_get('mbstring.func_overload') & 2) ? mb_strpos($haystack, $needle, $offset, '8bit') : strpos($haystack, $needle, $offset); + } + + /** + * returns [numCols,numRows] of worksheet + * + * @param int $worksheetIndex + * + * @return array + */ + public function dimension($worksheetIndex = 0) + { + + if (($ws = $this->worksheet($worksheetIndex)) === false) { + return [0, 0]; + } + /* @var SimpleXMLElement $ws */ + + $ref = (string)$ws->dimension['ref']; + + if ($this->_strpos($ref, ':') !== false) { + $d = explode(':', $ref); + $idx = $this->getIndex($d[1]); + + return [$idx[0] + 1, $idx[1] + 1]; + } + /* + if ( $ref !== '' ) { // 0.6.8 + $index = $this->getIndex( $ref ); + + return [ $index[0] + 1, $index[1] + 1 ]; + } + */ + + // slow method + $maxC = $maxR = 0; + foreach ($ws->sheetData->row as $row) { + foreach ($row->c as $c) { + $idx = $this->getIndex((string)$c['r']); + $x = $idx[0]; + $y = $idx[1]; + if ($x > 0) { + if ($x > $maxC) { + $maxC = $x; + } + if ($y > $maxR) { + $maxR = $y; + } + } + } + } + + return [$maxC + 1, $maxR + 1]; + } + + public function getIndex($cell = 'A1') + { + + if (preg_match('/([A-Z]+)(\d+)/', $cell, $m)) { + $col = $m[1]; + $row = $m[2]; + + $colLen = $this->_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]; + } + +// $this->error( 'Invalid cell index ' . $cell ); + + return [-1, -1]; + } + + public function value($cell) + { + // Determine data type + $dataType = (string)$cell['t']; + + if ($dataType === '' || $dataType === 'n') { // number + $s = (int)$cell['s']; + if ($s > 0 && isset($this->cellFormats[$s])) { + if (array_key_exists('format', $this->cellFormats[$s])) { + $format = $this->cellFormats[$s]['format']; + if (preg_match('/(m|AM|PM)/', preg_replace('/\"[^"]+\"/', '', $format))) { // [mm]onth,AM|PM + $dataType = 'D'; + } + } else { + $dataType = 'n'; + } + } + } + + $value = ''; + + switch ($dataType) { + case 's': + // Value is a shared string + if ((string)$cell->v !== '') { + $value = $this->sharedstrings[(int)$cell->v]; + } + break; + + case 'str': // formula? + if ((string)$cell->v !== '') { + $value = (string)$cell->v; + } + break; + + case 'b': + // Value is boolean + $value = (string)$cell->v; + if ($value === '0') { + $value = false; + } elseif ($value === '1') { + $value = true; + } else { + $value = (bool)$cell->v; + } + + break; + + case 'inlineStr': + // Value is rich text inline + $value = $this->_parseRichText($cell->is); + + break; + + case 'e': + // Value is an error message + if ((string)$cell->v !== '') { + $value = (string)$cell->v; + } + break; + + case 'D': + // Date as float + if (!empty($cell->v)) { + $value = $this->datetimeFormat ? gmdate($this->datetimeFormat, $this->unixstamp((float)$cell->v)) : (float)$cell->v; + } + break; + + case 'd': + // Date as ISO YYYY-MM-DD + if ((string)$cell->v !== '') { + $value = (string)$cell->v; + } + break; + + default: + // Value is a string + $value = (string)$cell->v; + + // Check for numeric values + if (is_numeric($value)) { + /** @noinspection TypeUnsafeComparisonInspection */ + if ($value == (int)$value) { + $value = (int)$value; + } /** @noinspection TypeUnsafeComparisonInspection */ elseif ($value == (float)$value) { + $value = (float)$value; + } + } + } + + return $value; + } + + public function unixstamp($excelDateTime) + { + + $d = floor($excelDateTime); // days since 1900 or 1904 + $t = $excelDateTime - $d; + + if ($this->date1904) { + $d += 1462; + } + + $t = (abs($d) > 0) ? ($d - 25569) * 86400 + round($t * 86400) : round($t * 86400); + + return (int)$t; + } + + public function href($worksheetIndex, $cell) + { + $ref = (string)$cell['r']; + return isset($this->hyperlinks[$worksheetIndex][$ref]) ? $this->hyperlinks[$worksheetIndex][$ref] : ''; + } + + public function toHTML($worksheetIndex = 0) + { + $s = ''; + foreach ($this->readRows($worksheetIndex) as $r) { + $s .= ''; + foreach ($r as $c) { + $s .= ''; + } + $s .= "\r\n"; + } + $s .= '
' . ($c === '' ? ' ' : htmlspecialchars($c, ENT_QUOTES)) . '
'; + + return $s; + } + public function toHTMLEx($worksheetIndex = 0) + { + $s = ''; + $y = 0; + foreach ($this->readRowsEx($worksheetIndex) as $r) { + $s .= ''; + $x = 0; + foreach ($r as $c) { + $tag = 'td'; + $css = $c['css']; + if ($y === 0) { + $tag = 'th'; + $css .= $c['width'] ? 'width: '.round($c['width'] * 0.47, 2).'em;' : ''; + } + + if ($x === 0 && $c['height']) { + $css .= 'height: '.round($c['height'] * 1.3333).'px;'; + } + $s .= '<'.$tag.' style="'.$css.'" nowrap>' . ($c['value'] === '' ? ' ' : htmlspecialchars($c['value'], ENT_QUOTES)) . ''; + $x++; + } + $s .= "\r\n"; + $y++; + } + $s .= '
'; + + return $s; + } + public function rows($worksheetIndex = 0, $limit = 0) + { + return iterator_to_array($this->readRows($worksheetIndex, $limit), false); + } + // thx Gonzo + /** + * @param $worksheetIndex + * @param $limit + * @return \Generator + */ + public function readRows($worksheetIndex = 0, $limit = 0) + { + + if (($ws = $this->worksheet($worksheetIndex)) === false) { + return; + } + $dim = $this->dimension($worksheetIndex); + $numCols = $dim[0]; + $numRows = $dim[1]; + + $emptyRow = []; + for ($i = 0; $i < $numCols; $i++) { + $emptyRow[] = ''; + } + + $curR = 0; + $_limit = $limit; + /* @var SimpleXMLElement $ws */ + foreach ($ws->sheetData->row as $row) { + $r = $emptyRow; + $curC = 0; + foreach ($row->c as $c) { + // detect skipped cols + $idx = $this->getIndex((string)$c['r']); + $x = $idx[0]; + $y = $idx[1]; + + if ($x > -1) { + $curC = $x; + while ($curR < $y) { + yield $emptyRow; + $curR++; + $_limit--; + if ($_limit === 0) { + return; + } + } + } + $r[$curC] = $this->value($c); + $curC++; + } + yield $r; + + $curR++; + $_limit--; + if ($_limit === 0) { + return; + } + } + while ($curR < $numRows) { + yield $emptyRow; + $curR++; + $_limit--; + if ($_limit === 0) { + return; + } + } + } + + public function rowsEx($worksheetIndex = 0, $limit = 0) + { + return iterator_to_array($this->readRowsEx($worksheetIndex, $limit), false); + } + // https://github.com/shuchkin/simplexlsx#gets-extend-cell-info-by--rowsex + /** + * @param $worksheetIndex + * @param $limit + * @return \Generator|null + */ + public function readRowsEx($worksheetIndex = 0, $limit = 0) + { + if (!$this->rowsExReader) { + require_once __DIR__ . '/SimpleXLSXEx.php'; + $this->rowsExReader = new SimpleXLSXEx($this); + } + return $this->rowsExReader->readRowsEx($worksheetIndex, $limit); + } + + /** + * Returns cell value + * VERY SLOW! Use ->rows() or ->rowsEx() + * + * @param int $worksheetIndex + * @param string|array $cell ref or coords, D12 or [3,12] + * + * @return mixed Returns NULL if not found + */ + public function getCell($worksheetIndex = 0, $cell = 'A1') + { + + if (($ws = $this->worksheet($worksheetIndex)) === false) { + return false; + } + if (is_array($cell)) { + $cell = $this->_num2name($cell[0]) . $cell[1];// [3,21] -> D21 + } + if (is_string($cell)) { + $result = $ws->sheetData->xpath("row/c[@r='" . $cell . "']"); + if (count($result)) { + return $this->value($result[0]); + } + } + + return null; + } + + protected function _num2name($num) + { + $numeric = ($num - 1) % 26; + $letter = chr(65 + $numeric); + $num2 = (int)(($num - 1) / 26); + if ($num2 > 0) { + return $this->_num2name($num2) . $letter; + } + return $letter; + } + + public function getSheets() + { + return $this->sheets; + } + + public function sheetsCount() + { + return count($this->sheets); + } + + public function sheetName($worksheetIndex) + { + if (isset($this->sheetNames[$worksheetIndex])) { + return $this->sheetNames[$worksheetIndex]; + } + + return false; + } + + public function sheetNames() + { + + return $this->sheetNames; + } + + public function getStyles() + { + return $this->styles; + } + + public function getPackage() + { + return $this->package; + } + + public function setDateTimeFormat($value) + { + $this->datetimeFormat = is_string($value) ? $value : false; + } +} diff --git a/RS/ExcelCal/SimpleXLSXEx.php b/RS/ExcelCal/SimpleXLSXEx.php new file mode 100644 index 0000000..9c9e257 --- /dev/null +++ b/RS/ExcelCal/SimpleXLSXEx.php @@ -0,0 +1,601 @@ + '000000', + 1 => 'FFFFFF', + 2 => 'FF0000', + 3 => '00FF00', + 4 => '0000FF', + 5 => 'FFFF00', + 6 => 'FF00FF', + 7 => '00FFFF', + 8 => '000000', + 9 => 'FFFFFF', + 10 => 'FF0000', + 11 => '00FF00', + 12 => '0000FF', + 13 => 'FFFF00', + 14 => 'FF00FF', + 15 => '00FFFF', + 16 => '800000', + 17 => '008000', + 18 => '000080', + 19 => '808000', + 20 => '800080', + 21 => '008080', + 22 => 'C0C0C0', + 23 => '808080', + 24 => '9999FF', + 25 => '993366', + 26 => 'FFFFCC', + 27 => 'CCFFFF', + 28 => '660066', + 29 => 'FF8080', + 30 => '0066CC', + 31 => 'CCCCFF', + 32 => '000080', + 33 => 'FF00FF', + 34 => 'FFFF00', + 35 => '00FFFF', + 36 => '800080', + 37 => '800000', + 38 => '008080', + 39 => '0000FF', + 40 => '00CCFF', + 41 => 'CCFFFF', + 42 => 'CCFFCC', + 43 => 'FFFF99', + 44 => '99CCFF', + 45 => 'FF99CC', + 46 => 'CC99FF', + 47 => 'FFCC99', + 48 => '3366FF', + 49 => '33CCCC', + 50 => '99CC00', + 51 => 'FFCC00', + 52 => 'FF9900', + 53 => 'FF6600', + 54 => '666699', + 55 => '969696', + 56 => '003366', + 57 => '339966', + 58 => '003300', + 59 => '333300', + 60 => '993300', + 61 => '993366', + 62 => '333399', + 63 => '333333', + 64 => '000000', // System Foreground + 65 => 'FFFFFF', // System Background' + ]; + public static $CH = [ + 0 => 'ANSI_CHARSET', + 1 => 'DEFAULT_CHARSET', + 2 => 'SYMBOL_CHARSET', + 77 => 'MAC_CHARSET', + 128 => 'SHIFTJIS_CHARSET', + //129 => 'HANGEUL_CHARSET', + 129 => 'HANGUL_CHARSET', + 130 => 'JOHAB_CHARSET', + 134 => 'GB2312_CHARSET', + 136 => 'CHINESEBIG5_CHARSET', + 161 => 'GREEK_CHARSET', + 162 => 'TURKISH_CHARSET', + 163 => 'VIETNAMESE_CHARSET', + 177 => 'HEBREW_CHARSET', + 178 => 'ARABIC_CHARSET', + 186 => 'BALTIC_CHARSET', + 204 => 'RUSSIAN_CHARSET', + 222 => 'THAI_CHARSET', + 238 => 'EASTEUROPE_CHARSET', + 255 => 'OEM_CHARSET' + ]; + public $xlsx; + public $themeColors; + public $fonts; + public $fills; + public $borders; + public $cellStyles; + public $css; + + public function __construct(SimpleXLSX $xlsx) + { + $this->xlsx = $xlsx; + $this->readThemeColors(); + $this->readFonts(); + $this->readFills(); + $this->readBorders(); + $this->readXfs(); + } + public function readThemeColors() + { + $this->themeColors = []; + if (isset($this->xlsx->theme->themeElements->clrScheme)) { + $colors12 = ['lt1', 'dk1', 'lt2', 'dk2','accent1','accent2','accent3','accent4','accent5', + 'accent6','hlink','folHlink']; + foreach ($colors12 as $c) { + $v = $this->xlsx->theme->themeElements->clrScheme->{$c}; + if (isset($v->sysClr)) { + $this->themeColors[] = (string) $v->sysClr['lastClr']; + } elseif (isset($v->srgbClr)) { + $this->themeColors[] = (string) $v->srgbClr['val']; + } else { + $this->themeColors[] = null; + } + } + } + } + + + public function readFonts() + { + // fonts + $this->fonts = []; + if (isset($this->xlsx->styles->fonts->font)) { + foreach ($this->xlsx->styles->fonts->font as $v) { + $u = ''; + if (isset($v->u)) { + $u = isset($v->u['val']) ? (string) $v->u['val'] : 'single'; + } + $this->fonts[] = [ + 'b' => isset($v->b) && ($v->b['val'] === null || $v->b['val']), + 'i' => isset($v->i) && ($v->i['val'] === null || $v->i['val']), + 'u' => $u, + 'strike' => isset($v->strike) && ($v->strike['val'] === null || $v->strike['val']), + 'sz' => isset($v->sz['val']) ? (int) $v->sz['val'] : 11, + 'color' => $this->getColorValue($v->color), + 'name' => isset($v->name['val']) ? (string) $v->name['val'] : 'Calibri', + 'family' => isset($v->family['val']) ? (int) $v->family['val'] : 2, + 'charset' => isset($v->charset['val']) ? (int) $v->charset['val'] : 1, + 'scheme' => isset($v->scheme['val']) ? (string) $v->scheme['val'] : 'minor' + ]; + } + } + } + public function readFills() + { + // fills + $this->fills = []; + if (isset($this->xlsx->styles->fills->fill)) { + foreach ($this->xlsx->styles->fills->fill as $v) { + if (isset($v->patternFill)) { + $this->fills[] = [ + 'pattern' => isset($v->patternFill['patternType']) ? (string) $v->patternFill['patternType'] : 'none', + 'fgcolor' => $this->getColorValue($v->patternFill->fgColor), + 'bgcolor' => $this->getColorValue($v->patternFill->bgColor) + ]; + } + } + } + } + public function readBorders() + { + $this->borders = []; + if (isset($this->xlsx->styles->borders->border)) { + foreach ($this->xlsx->styles->borders->border as $v) { + $this->borders[] = [ + 'left' => [ + 'style' => (string) $v->left['style'], + 'color' => $this->getColorValue($v->left->color) + ], + 'right' => [ + 'style' => (string) $v->right['style'], + 'color' => $this->getColorValue($v->right->color) + ], + 'top' => [ + 'style' => (string) $v->top['style'], + 'color' => $this->getColorValue($v->top->color) + ], + 'bottom' => [ + 'style' => (string) $v->bottom['style'], + 'color' => $this->getColorValue($v->bottom->color) + ], + 'diagonal' => [ + 'style' => (string) $v->diagonal['style'], + 'color' => $this->getColorValue($v->diagonal->color) + ], + 'horizontal' => [ + 'style' => (string) $v->horizontal['style'], + 'color' => $this->getColorValue($v->horizontal->color) + ], + 'vertical' => [ + 'style' => (string) $v->vertical['style'], + 'color' => $this->getColorValue($v->vertical->color) + ], + 'diagonalUp' => (bool) $v['diagonalUp'], + 'diagonalDown' => (bool) $v['diagonalDown'], + 'outline' => !(isset($v['outline'])) || $v['outline'] + ]; + } + } + } + + public function readXfs() + { + // cellStyles + $this->cellStyles = []; + if (isset($this->xlsx->styles->cellStyleXfs->xf)) { + foreach ($this->xlsx->styles->cellStyleXfs->xf as $v) { + $x = []; + foreach ($v->attributes() as $k1 => $v1) { + $x[ $k1 ] = (int) $v1; + } + if (isset($v->alignment)) { + foreach ($v->alignment->attributes() as $k1 => $v1) { + $x['alignment'][$k1] = (string) $v1; + } + } + $this->cellStyles[] = $x; + } + } + // css + $this->css = []; + // xf + if (isset($this->xlsx->styles->cellXfs->xf)) { + $k = 0; + foreach ($this->xlsx->styles->cellXfs->xf as $v) { + $cf = &$this->xlsx->cellFormats[$k]; + + // alignment + $alignment = []; + if (isset($v->alignment)) { + foreach ($v->alignment->attributes() as $k1 => $v1) { + $alignment[$k1] = (string)$v1; + } + } + + if (isset($cf['xfId'], $this->cellStyles[ $cf['xfId'] ])) { + $s = $this->cellStyles[$cf['xfId']]; + if (!empty($s['applyNumberFormat'])) { + $cf['numFmtId'] = $s['numFmtId']; + } + if (!empty($s['applyFont'])) { + $cf['fontId'] = $s['fontId']; + } + if (!empty($s['applyBorder'])) { + $cf['borderId'] = $s['borderId']; + } + if (!empty($s['applyAlignment'])) { + $alignment = $s['alignment']; + } + } + $cf['alignment'] = $alignment; + + $align = null; + if (isset($alignment['horizontal'])) { + $align = $alignment['horizontal']; + if ($align === 'centerContinuous') { + $align = 'center'; + } + if ($align === 'distributed') { + $align = 'justify'; + } + if ($align === 'general') { + $align = null; + } + } + $cf['align'] = $align; + + $valign = null; + if (isset($alignment['vertical'])) { + $valign = $alignment['vertical']; + if ($valign === 'center' || $valign === 'distributed' || $valign === 'justify') { + $valign = 'middle'; + } + } + $cf['valign'] = $valign; + + // font + $cf['font'] = $this->fonts[ $cf['fontId'] ]['name']; + $cf['color'] = $this->fonts[ $cf['fontId'] ]['color']; + $cf['f-size'] = $this->fonts[ $cf['fontId'] ]['sz']; + $cf['f-b'] = $this->fonts[ $cf['fontId'] ]['b']; + $cf['f-i'] = $this->fonts[ $cf['fontId'] ]['i']; + $cf['f-u'] = $this->fonts[ $cf['fontId'] ]['u']; + $cf['f-strike'] = $this->fonts[ $cf['fontId'] ]['strike']; + + // fill + $cf['bgcolor'] = $this->fills[ $cf['fillId'] ]['fgcolor']; + + // borders + if (isset($this->borders[ $cf['borderId'] ])) { + $border = $this->borders[ $cf['borderId'] ]; + + $borders = ['left', 'right', 'top', 'bottom']; + foreach ($borders as $b) { + $cf['b-' . $b.'-color'] = $border[$b]['color']; + if ($border[$b]['style'] === '' || $border[$b]['style'] === 'none') { + $cf['b-' . $b.'-style'] = ''; + $cf['b-' . $b.'-color'] = ''; + } elseif ($border[$b]['style'] === 'dashDot' + || $border[$b]['style'] === 'dashDotDot' + || $border[$b]['style'] === 'dashed' + ) { + $cf['b-' . $b.'-style'] = 'dashed'; + } else { + $cf['b-' . $b.'-style'] = 'solid'; + } + } + } + + $css = ''; + + if ($cf['color']) { + $css .= 'color: #'.$cf['color'].';'; + } + if ($cf['font']) { + $css .= 'font-family: '.$cf['font'].';'; + } + if ($cf['f-size']) { +// $css .= 'font-size: '.($cf['f-size'] * 0.352806).'mm;'; + $css .= 'font-size: '.(round($cf['f-size'] * 1.3333) + 2).'px;'; + } + if ($cf['f-b']) { + $css .= 'font-weight: bold;'; + } + if ($cf['f-i']) { + $css .= 'font-style: italic;'; + } + if ($cf['f-u']) { + $css .= 'text-decoration: underline;'; + } + if ($cf['f-strike']) { + $css .= 'text-decoration: line-through;'; + } + if ($cf['bgcolor']) { + $css .= 'background-color: #' . $cf['bgcolor'] . ';'; + } + if ($cf['align']) { + $css .= 'text-align: '.$cf['align'].';'; + } + if ($cf['valign']) { + $css .= 'vertical-align: '.$cf['valign'].';'; + } + if ($cf['b-top-style']) { + $css .= 'border-top-style: '.$cf['b-top-style'].';'; + $css .= 'border-top-color: #'.$cf['b-top-color'].';'; + $css .= 'border-top-width: thin;'; + } + if ($cf['b-right-style']) { + $css .= 'border-right-style: '.$cf['b-right-style'].';'; + $css .= 'border-right-color: #'.$cf['b-right-color'].';'; + $css .= 'border-right-width: thin;'; + } + if ($cf['b-bottom-style']) { + $css .= 'border-bottom-style: '.$cf['b-bottom-style'].';'; + $css .= 'border-bottom-color: #'.$cf['b-bottom-color'].';'; + $css .= 'border-bottom-width: thin;'; + } + if ($cf['b-left-style']) { + $css .= 'border-left-style: '.$cf['b-left-style'].';'; + $css .= 'border-left-color: #'.$cf['b-left-color'].';'; + $css .= 'border-left-width: thin;'; + } + $this->css[$k] = $css; + + $k++; + } + } + } + + public function readRowsEx($worksheetIndex = 0, $limit = 0) + { + if (($ws = $this->xlsx->worksheet($worksheetIndex)) === false) { + return; + } + + $dim = $this->xlsx->dimension($worksheetIndex); + $numCols = $dim[0]; + $numRows = $dim[1]; + + /*$emptyRow = array(); + for ($i = 0; $i < $numCols; $i++) { + $emptyRow[] = null; + } + */ + $cols = []; + for ($i = 0; $i < $numCols; $i++) { + $cols[] = ['s' => 0, 'hidden' => false, 'width' => 0]; + } +// $hiddenCols = []; + /* @var SimpleXMLElement $ws */ + if (isset($ws->cols)) { + foreach ($ws->cols->col as $col) { + $min = (int)$col['min']; + $max = (int)$col['max']; + if (($max-$min) > 100) { + $max = $min; + } + for ($i = $min; $i <= $max; $i++) { + $cols[$i-1] = [ + 's' => (int)$col['style'], + 'hidden' => (bool)$col['hidden'], + 'width' => $col['customWidth'] ? (float) $col['width'] : 0 + ]; + } + } + } + + $curR = 0; + $_limit = $limit; + + foreach ($ws->sheetData->row as $row) { + $curC = 0; + + $r_idx = (int)$row['r']; + $r_style = ['s' => 0, 'hidden' => (bool)$row['hidden'], 'height' => 0]; + if ($row['customFormat']) { + $r_style['s'] = (int)$row['s']; + } + if ($row['customHeight']) { + $r_style['height'] = (int)$row['ht']; + } + + $cells = []; + for ($i = 0; $i < $numCols; $i++) { + $cells[] = null; + } + + foreach ($row->c as $c) { + $r = (string)$c['r']; + $t = (string)$c['t']; + $s = (int)$c['s']; + + $idx = $this->xlsx->getIndex($r); + $x = $idx[0]; + $y = $idx[1]; + + if ($x > -1) { + $curC = $x; + if ($curC >= $numCols) { + $numCols = $curC + 1; + } + while ($curR < $y) { + $emptyRow = []; + for ($i = 0; $i < $numCols; $i++) { + $emptyRow[] = $this->valueEx($cols[$i], $i, $curR); + } + yield $emptyRow; + $curR++; + + $_limit--; + if ($_limit === 0) { + return; + } + } + } + $data = [ + 'type' => $t, + 'name' => $r, + 'value' => $this->xlsx->value($c), + 'href' => $this->xlsx->href($worksheetIndex, $c), + 'f' => (string)$c->f, + 'r' => $r_idx, + 's' => ($s > 0) ? $s : $cols[$curC]['s'], + 'hidden' => $r_style['hidden'] || $cols[$curC]['hidden'], + 'width' => $cols[$curC]['width'], + 'height' => $r_style['height'] + ]; + $cells[$curC] = $this->valueEx($data, $curC, $curR); + + $curC++; + } + // check empty cells + for ($i = 0; $i < $numCols; $i++) { + if ($cells[$i] === null) { + if ($r_style['s'] > 0) { + $data = $r_style; + } else { + $data = $cols[$i]; + } + $data['width'] = $cols[$i]['width']; + $data['height'] = $r_style['height']; + $cells[$i] = $this->valueEx($data, $i, $curR); + } + } + + yield $cells; + + $curR++; + $_limit--; + if ($_limit === 0) { + break; + } + } + + while ($curR < $numRows) { + $emptyRow = []; + for ($i = 0; $i < $numCols; $i++) { + $data = $cols[$i]; + $emptyRow[] = $this->valueEx($data, $i, $curR); + } + yield $emptyRow; + $curR++; + $_limit--; + if ($_limit === 0) { + return; + } + } + } + + protected function valueEx($data, $x = null, $y = null) + { + + $r = [ + 'type' => '', + 'name' => '', + 'value' => '', + 'href' => '', + 'f' => '', + 'format' => '', + 's' => 0, + 'css' => '', + 'r' => '', + 'hidden' => false, + 'width' => 0, + 'height' => 0 + ]; + foreach ($data as $k => $v) { + if (isset($r[$k])) { + $r[$k] = $v; + } + } + $st = &$this->xlsx->cellFormats[$r['s']]; + $r['format'] = $st['format']; + $r['css'] = &$this->css[ $r['s'] ]; + if ($r['value'] !== '' && !$st['align'] && !in_array($r['type'], ['s','str','inlineStr','e'], true)) { + $r['css'] .= 'text-align: right;'; + } + + if (!$r['name']) { + $c = ''; + for ($k = $x; $k >= 0; $k = (int)($k / 26) - 1) { + $c = chr($k % 26 + 65) . $c; + } + $r['name'] = $c . ($y + 1); + $r['r'] = $y+1; + } + return $r; + } + public function getColorValue(SimpleXMLElement $a = null, $default = '') + { + if ($a === null) { + return $default; + } + $c = $default; // auto + if ($a['rgb'] !== null) { + $c = substr((string) $a['rgb'], 2); // FFCCBBAA -> CCBBAA + } elseif ($a['indexed'] !== null && isset(static::$IC[ (int) $a['indexed'] ])) { + $c = static::$IC[ (int) $a['indexed'] ]; + } elseif ($a['theme'] !== null && isset($this->themeColors[ (int) $a['theme'] ])) { + $c = $this->themeColors[ (int) $a['theme'] ]; + } + if ($a['tint'] !== null) { + list($r,$g,$b) = array_map('hexdec', str_split($c, 2)); + $tint = (float) $a['tint']; + if ($tint > 0) { + $r += (255 - $r) * $tint; + $g += (255 - $g) * $tint; + $b += (255 - $b) * $tint; + } else { + $r += $r * $tint; + $g += $g * $tint; + $b += $b * $tint; + } + $c = strtoupper( + str_pad(dechex((int) $r), 2, '0', 0) . + str_pad(dechex((int) $g), 2, '0', 0) . + str_pad(dechex((int) $b), 2, '0', 0) + ); + } + return $c; + } +} diff --git a/RS/ExcelCal/xls2ical.php b/RS/ExcelCal/xls2ical.php new file mode 100644 index 0000000..b8688fd --- /dev/null +++ b/RS/ExcelCal/xls2ical.php @@ -0,0 +1,126 @@ +sheetNames()); $i++){ + //echo $i." : ".$xlsx->sheetName($i)."\n"; + if($xlsx->sheetName($i) == "User Map"){ + $usermap = $i; + //echo "Users sheet: $usermap\n"; + } + } + //echo "User: ".$user."\n"; + $ical = "BEGIN:VCALENDAR +VERSION:2.0 +PRODID:-//$user, Inc.//EN"; + + + + // all other sheets != usermap + for($i = 0; $i <= sizeof($xlsx->sheetNames())-1; $i++){ + if($xlsx->sheetName($i) != "User Map"){ + + // populate map of jobs > 1 day long + foreach( $xlsx->worksheet($i)->mergeCells->mergeCell as $c ) { + $vals = explode(":", $c['ref']); + $concatMap[$i.'-'.$vals[0]] = $vals[1]; + } + + //echo "### ".$xlsx->sheetName($i)." ###\n"; + + foreach( $xlsx->rowsEx($i) as $rowsEx){ + for($j=1; $j<= sizeof($rowsEx)-2; $j++){ + + if(trim($rowsEx[0]['value']) == $user){ // found user + + + if(trim($rowsEx[$j]['value']) <> "" && trim($rowsEx[$j]['value']) <> "."){ // if contains data + //echo $j." ".$xlsx->sheetName($i)." : " .$rowsEx[$j]['value']."\n"; + //print_r($rowsEx[$j]); + $diff = 1; + if(isset($concatMap[$i."-" .$rowsEx[$j]['name']])){ + //echo "more than 1 day! \n"; + $diff += getDiff($i."-" .$rowsEx[$j]['name'], $concatMap[$i."-" .$rowsEx[$j]['name']]); + //echo $diff."\n"; + } + + $ical .= " +BEGIN:VEVENT +UID:" .md5(uniqid(mt_rand(), true))." +DTSTAMP:".gmdate("Ymd")."T".gmdate("His")."Z +DTSTART:".date("Ymd", strtotime($j." ".$xlsx->sheetName($i))); + +if($diff >= 1) + $ical .= "\nDTEND:".date("Ymd", strtotime($j." ".$xlsx->sheetName($i) ." +$diff day")); + +$ical .= " +SUMMARY:Booking for ".$rowsEx[$j]['value']." +END:VEVENT"; + + + } + + } + } + } + } + } + + + $ical .= " +END:VCALENDAR"; + +header("Content-type: text/calendar; charset=utf-8"); +header("Content-Disposition: inline; filename=calendar.ics"); +echo $ical; + +} else { + echo SimpleXLSX::parseError(); +} + +function getDiff($start, $end){ + $start = preg_replace("/[^A-Z]+/", "", $start); + $end = preg_replace("/[^A-Z]+/", "", $end); + $s1 = alphabet_to_number($start); + $e1 = alphabet_to_number($end); + return $e1 - $s1; + //return 2; +} +function alphabet_to_number($string) { + $string = strtoupper($string); + $length = strlen($string); + $number = 0; + $level = 1; + while ($length >= $level ) { + $char = $string[$length - $level]; + $c = ord($char) - 64; + $number += $c * (26 ** ($level-1)); + $level++; + } + return $number; +} +?> \ No newline at end of file diff --git a/RS/README.md b/RS/README.md new file mode 100644 index 0000000..e086780 --- /dev/null +++ b/RS/README.md @@ -0,0 +1,7 @@ +This folder will only be useful to RS staff, if you dont know what that is then it's not you! + +Without warranty or documentation. + +If you can't read the code then this isn't for you. + +Please do not ask for help regarding anything here, you will be ignored