{"action":"created","issue":{"url":"https://api.github.com/repos/mk-j/PHP_XLSXWriter/issues/61","repository_url":"https://api.github.com/repos/mk-j/PHP_XLSXWriter","labels_url":"https://api.github.com/repos/mk-j/PHP_XLSXWriter/issues/61/labels{/name}","comments_url":"https://api.github.com/repos/mk-j/PHP_XLSXWriter/issues/61/comments","events_url":"https://api.github.com/repos/mk-j/PHP_XLSXWriter/issues/61/events","html_url":"https://github.com/mk-j/PHP_XLSXWriter/issues/61","id":145997129,"number":61,"title":"AutoFilter","user":{"login":"damienlagae","id":2233003,"avatar_url":"https://avatars.githubusercontent.com/u/2233003?v=3","gravatar_id":"","url":"https://api.github.com/users/damienlagae","html_url":"https://github.com/damienlagae","followers_url":"https://api.github.com/users/damienlagae/followers","following_url":"https://api.github.com/users/damienlagae/following{/other_user}","gists_url":"https://api.github.com/users/damienlagae/gists{/gist_id}","starred_url":"https://api.github.com/users/damienlagae/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/damienlagae/subscriptions","organizations_url":"https://api.github.com/users/damienlagae/orgs","repos_url":"https://api.github.com/users/damienlagae/repos","events_url":"https://api.github.com/users/damienlagae/events{/privacy}","received_events_url":"https://api.github.com/users/damienlagae/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":3,"created_at":"2016-04-05T13:57:17Z","updated_at":"2016-11-28T16:20:00Z","closed_at":null,"body":"Hi,\n\nGreat job!\nCan you add the autofilter function like PHPExcel. (https://github.com/cystbear/PHPExcel/blob/master/Tests/10autofilter.php)\n"},"comment":{"url":"https://api.github.com/repos/mk-j/PHP_XLSXWriter/issues/comments/263315964","html_url":"https://github.com/mk-j/PHP_XLSXWriter/issues/61#issuecomment-263315964","issue_url":"https://api.github.com/repos/mk-j/PHP_XLSXWriter/issues/61","id":263315964,"user":{"login":"cgaudelet","id":1092125,"avatar_url":"https://avatars.githubusercontent.com/u/1092125?v=3","gravatar_id":"","url":"https://api.github.com/users/cgaudelet","html_url":"https://github.com/cgaudelet","followers_url":"https://api.github.com/users/cgaudelet/followers","following_url":"https://api.github.com/users/cgaudelet/following{/other_user}","gists_url":"https://api.github.com/users/cgaudelet/gists{/gist_id}","starred_url":"https://api.github.com/users/cgaudelet/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cgaudelet/subscriptions","organizations_url":"https://api.github.com/users/cgaudelet/orgs","repos_url":"https://api.github.com/users/cgaudelet/repos","events_url":"https://api.github.com/users/cgaudelet/events{/privacy}","received_events_url":"https://api.github.com/users/cgaudelet/received_events","type":"User","site_admin":false},"created_at":"2016-11-28T16:20:00Z","updated_at":"2016-11-28T16:20:00Z","body":"``<?php\r\n/*\r\n * @license MIT License\r\n * */\r\n\r\nif (!class_exists('ZipArchive')) { throw new Exception('ZipArchive not found'); }\r\n\r\nclass XLSXWriter\r\n{\r\n\t//------------------------------------------------------------------\r\n\t//http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx\r\n\tconst EXCEL_2007_MAX_ROW=1048576;\r\n\tconst EXCEL_2007_MAX_COL=16384;\r\n\t//------------------------------------------------------------------\r\n\tprotected $author ='Doc Author';\r\n\tprotected $sheets = array();\r\n\tprotected $shared_strings = array();//unique set\r\n\tprotected $shared_string_count = 0;//count of non-unique references to the unique set\r\n\tprotected $temp_files = array();\r\n\tprotected $cell_formats = array();//contains excel format like YYYY-MM-DD HH:MM:SS\r\n\tprotected $cell_types = array();//contains friendly format like datetime\r\n\r\n\tprotected $current_sheet = '';\r\n\tprotected $temp_dir = NULL;\r\n\r\n\tpublic function __construct()\r\n\t{\r\n\t\tif(!ini_get('date.timezone'))\r\n\t\t{\r\n\t\t\t//using date functions can kick out warning if this isn't set\r\n\t\t\tdate_default_timezone_set('UTC');\r\n\t\t}\r\n\t\t$this->addCellFormat($cell_format='GENERAL');\r\n\t}\r\n\r\n\tpublic function setAuthor($author='') { $this->author=$author; }\r\n\r\n\tpublic function __destruct()\r\n\t{\r\n\t\tif (!empty($this->temp_files)) {\r\n\t\t\tforeach($this->temp_files as $temp_file) {\r\n\t\t\t\t@unlink($temp_file);\r\n\t\t\t}\r\n\t\t}\r\n\t}\r\n\t\r\n\tpublic function setTempDir($dir)\r\n\t{\r\n\t\t$this->temp_dir = $dir;\r\n\t}\r\n\t\r\n\tprotected function tempFilename()\r\n\t{\r\n\t\t$temp_dir = is_null($this->temp_dir) ? sys_get_temp_dir() : $this->temp_dir;\r\n\t\t$filename = tempnam($temp_dir, \"xlsx_writer_\");\r\n\t\t$this->temp_files[] = $filename;\r\n\t\treturn $filename;\r\n\t}\r\n\r\n\tpublic function writeToStdOut()\r\n\t{\r\n\t\t$temp_file = $this->tempFilename();\r\n\t\tself::writeToFile($temp_file);\r\n\t\treadfile($temp_file);\r\n\t}\r\n\r\n\tpublic function writeToString()\r\n\t{\r\n\t\t$temp_file = $this->tempFilename();\r\n\t\tself::writeToFile($temp_file);\r\n\t\t$string = file_get_contents($temp_file);\r\n\t\treturn $string;\r\n\t}\r\n\r\n\tpublic function writeToFile($filename)\r\n\t{\r\n\t\tforeach($this->sheets as $sheet_name => $sheet) {\r\n\t\t\tself::finalizeSheet($sheet_name);//making sure all footers have been written\r\n\t\t}\r\n\r\n\t\tif ( file_exists( $filename ) ) {\r\n\t\t\tif ( is_writable( $filename ) ) {\r\n\t\t\t\t@unlink( $filename ); //if the zip already exists, remove it\r\n\t\t\t} else {\r\n\t\t\t\tself::log( \"Error in \" . __CLASS__ . \"::\" . __FUNCTION__ . \", file is not writeable.\" );\r\n\t\t\t\treturn;\r\n\t\t\t}\r\n\t\t}\r\n\t\t$zip = new ZipArchive();\r\n\t\tif (empty($this->sheets))                       { self::log(\"Error in \".__CLASS__.\"::\".__FUNCTION__.\", no worksheets defined.\"); return; }\r\n\t\tif (!$zip->open($filename, ZipArchive::CREATE)) { self::log(\"Error in \".__CLASS__.\"::\".__FUNCTION__.\", unable to create zip.\"); return; }\r\n\r\n\t\t$zip->addEmptyDir(\"docProps/\");\r\n\t\t$zip->addFromString(\"docProps/app.xml\" , self::buildAppXML() );\r\n\t\t$zip->addFromString(\"docProps/core.xml\", self::buildCoreXML());\r\n\r\n\t\t$zip->addEmptyDir(\"_rels/\");\r\n\t\t$zip->addFromString(\"_rels/.rels\", self::buildRelationshipsXML());\r\n\r\n\t\t$zip->addEmptyDir(\"xl/worksheets/\");\r\n\t\tforeach($this->sheets as $sheet) {\r\n\t\t\t$zip->addFile($sheet->filename, \"xl/worksheets/\".$sheet->xmlname );\r\n\t\t}\r\n\t\tif (!empty($this->shared_strings)) {\r\n\t\t\t$zip->addFile($this->writeSharedStringsXML(), \"xl/sharedStrings.xml\" );  //$zip->addFromString(\"xl/sharedStrings.xml\",     self::buildSharedStringsXML() );\r\n\t\t}\r\n\t\t$zip->addFromString(\"xl/workbook.xml\"         , self::buildWorkbookXML() );\r\n\t\t$zip->addFile($this->writeStylesXML(), \"xl/styles.xml\" );  //$zip->addFromString(\"xl/styles.xml\"           , self::buildStylesXML() );\r\n\t\t$zip->addFromString(\"[Content_Types].xml\"     , self::buildContentTypesXML() );\r\n\r\n\t\t$zip->addEmptyDir(\"xl/_rels/\");\r\n\t\t$zip->addFromString(\"xl/_rels/workbook.xml.rels\", self::buildWorkbookRelsXML() );\r\n\t\t$zip->close();\r\n\t}\r\n\r\n\tprotected function initializeSheet($sheet_name,array $sheet_options=array())\r\n\t{\r\n\t\t//if already initialized\r\n\t\tif ($this->current_sheet==$sheet_name || isset($this->sheets[$sheet_name]))\r\n\t\t\treturn;\r\n\r\n\t\t$sheet_filename = $this->tempFilename();\r\n\t\t$sheet_xmlname = 'sheet' . (count($this->sheets) + 1).\".xml\";\r\n\t\t$this->sheets[$sheet_name] = (object)array(\r\n\t\t\t'filename' => $sheet_filename,\r\n\t\t\t'sheetname' => $sheet_name,\r\n\t\t\t'xmlname' => $sheet_xmlname,\r\n\t\t\t'row_count' => 0,\r\n\t\t\t'file_writer' => new XLSXWriter_BuffererWriter($sheet_filename),\r\n\t\t\t'columns' => array(),\r\n\t\t\t'merge_cells' => array(),\r\n\t\t\t'max_cell_tag_start' => 0,\r\n\t\t\t'max_cell_tag_end' => 0,\r\n\t\t\t'finalized' => false,\r\n\t\t    //custom\r\n\t\t    'autofilter' => (isset($sheet_options['autofilter']))?$sheet_options['autofilter']:false,\r\n\t\t    'freeze_pane' => (isset($sheet_options['freeze_pane']))?$sheet_options['freeze_pane']:false,\r\n\t\t);\r\n\t\t$sheet = &$this->sheets[$sheet_name];\r\n\t\t\r\n\t\t$tabselected = count($this->sheets) == 1 ? 'true' : 'false';//only first sheet is selected\r\n\t\t$max_cell=XLSXWriter::xlsCell(self::EXCEL_2007_MAX_ROW, self::EXCEL_2007_MAX_COL);//XFE1048577\r\n\t\t$sheet->file_writer->write('<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>' . \"\\n\");\r\n\t\t$sheet->file_writer->write('<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\">');\t\r\n\t\t$sheet->file_writer->write(  '<sheetPr filterMode=\"1\">');\r\n\t\t//\r\n\t\t$sheet->file_writer->write(    '<outlinePr summaryBelow=\"1\" summaryRight=\"1\" />');\r\n\t\t$sheet->file_writer->write(    '<pageSetUpPr fitToPage=\"false\"/>');\r\n\t\t$sheet->file_writer->write(  '</sheetPr>');\r\n\t\t$sheet->max_cell_tag_start = $sheet->file_writer->ftell();\r\n\t\t$sheet->file_writer->write('<dimension ref=\"A1:' . $max_cell . '\"/>');\r\n\t\t$sheet->max_cell_tag_end = $sheet->file_writer->ftell();\t\t\r\n\t\t$sheet->file_writer->write(  '<sheetViews>');\r\n\t\t$sheet->file_writer->write(    '<sheetView colorId=\"64\" defaultGridColor=\"true\" rightToLeft=\"false\" showFormulas=\"false\" showGridLines=\"true\" showOutlineSymbols=\"true\" showRowColHeaders=\"true\" showZeros=\"true\" tabSelected=\"' . $tabselected . '\" topLeftCell=\"A1\" view=\"normal\" windowProtection=\"false\" workbookViewId=\"0\" zoomScale=\"100\" zoomScaleNormal=\"100\" zoomScalePageLayoutView=\"100\">');\r\n\t\tif(!empty($sheet->freeze_pane)) {\r\n\t\t    $cell = XLSXWriter::xlsCell($sheet->freeze_pane[0],$sheet->freeze_pane[1]);\r\n\t\t    $ySplit = $sheet->freeze_pane[0];\r\n\t\t    $xSplit = $sheet->freeze_pane[1];\r\n\t\t    \r\n    \t\t$sheet->file_writer->write('<pane state=\"frozen\" activePane=\"bottomRight\" topLeftCell=\"'.$cell.'\" ySplit=\"'.$ySplit.'\" xSplit=\"'.$xSplit.'\"/>');\r\n    \t\t$sheet->file_writer->write('<selection pane=\"topRight\"/>');\r\n    \t\t$sheet->file_writer->write('<selection pane=\"bottomLeft\"/>');\r\n    \t\t$sheet->file_writer->write('<selection pane=\"bottomRight\" sqref=\"'.$cell.'\" activeCell=\"'.$cell.'\"/>');\r\n\t\t}\r\n\t\telse {\r\n\t\t    $sheet->file_writer->write(      '<selection activeCell=\"A1\" activeCellId=\"0\" pane=\"topLeft\" sqref=\"A1\"/>');\r\n\t\t}\r\n\t\t$sheet->file_writer->write(    '</sheetView>');\r\n\t\t$sheet->file_writer->write(  '</sheetViews>');\t\r\n\t\t$sheet->file_writer->write(  '<cols>');\r\n\t\t$sheet->file_writer->write(    '<col collapsed=\"false\" hidden=\"false\" max=\"1025\" min=\"1\" style=\"0\" width=\"11.5\"/>');\r\n\t\t$sheet->file_writer->write(  '</cols>');\r\n\t\t\r\n\t\t$sheet->file_writer->write(  '<sheetData>');\r\n\t}\r\n\r\n\tprivate function determineCellType($cell_format)\r\n\t{\r\n\t\t$cell_format = str_replace(\"[RED]\", \"\", $cell_format);\r\n\t\tif ($cell_format=='GENERAL') return 'string';\r\n\t\tif ($cell_format=='0') return 'numeric';\r\n\t\tif (preg_match(\"/[H]{1,2}:[M]{1,2}/\", $cell_format)) return 'datetime';\r\n\t\tif (preg_match(\"/[M]{1,2}:[S]{1,2}/\", $cell_format)) return 'datetime';\r\n\t\tif (preg_match(\"/[YY]{2,4}/\", $cell_format)) return 'date';\r\n\t\tif (preg_match(\"/[D]{1,2}/\", $cell_format)) return 'date';\r\n\t\tif (preg_match(\"/[M]{1,2}/\", $cell_format)) return 'date';\r\n\t\tif (preg_match(\"/$/\", $cell_format)) return 'currency';\r\n\t\tif (preg_match(\"/%/\", $cell_format)) return 'percent';\r\n\t\tif (preg_match(\"/0/\", $cell_format)) return 'numeric';\r\n\t\treturn 'string';\r\n\t}\r\n\r\n\tprivate function escapeCellFormat($cell_format)\r\n\t{\r\n\t\t$ignore_until='';\r\n\t\t$escaped = '';\r\n\t\tfor($i=0,$ix=strlen($cell_format); $i<$ix; $i++)\r\n\t\t{\r\n\t\t\t$c = $cell_format[$i];\r\n\t\t\tif ($ignore_until=='' && $c=='[')\r\n\t\t\t\t$ignore_until=']';\r\n\t\t\telse if ($ignore_until=='' && $c=='\"')\r\n\t\t\t\t$ignore_until='\"';\r\n\t\t\telse if ($ignore_until==$c)\r\n\t\t\t\t$ignore_until='';\r\n\t\t\tif ($ignore_until=='' && ($c==' ' || $c=='-'  || $c=='('  || $c==')') && ($i==0 || $cell_format[$i-1]!='_'))\r\n\t\t\t\t$escaped.= \"\\\\\".$c;\r\n\t\t\telse\r\n\t\t\t\t$escaped.= $c;\r\n\t\t}\r\n\t\treturn $escaped;\r\n\t\t//return str_replace( array(\" \",\"-\", \"(\", \")\"), array(\"\\ \",\"\\-\", \"\\(\", \"\\)\"), $cell_format);//TODO, needs more escaping\r\n\t}\r\n\r\n\tprivate function addCellFormat($cell_format)\r\n\t{\r\n\t\t//for backwards compatibility, to handle older versions\r\n\t\tif      ($cell_format=='string')   $cell_format='GENERAL';\r\n\t\telse if ($cell_format=='integer')  $cell_format='0';\r\n\t\telse if ($cell_format=='date')     $cell_format='YYYY-MM-DD';\r\n\t\telse if ($cell_format=='datetime') $cell_format='YYYY-MM-DD HH:MM:SS';\r\n\t\telse if ($cell_format=='dollar')   $cell_format='[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00';\r\n\t\telse if ($cell_format=='money')    $cell_format='[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00';\r\n\t\telse if ($cell_format=='euro')     $cell_format='#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]';\r\n\t\telse if ($cell_format=='NN')       $cell_format='DDD';\r\n\t\telse if ($cell_format=='NNN')      $cell_format='DDDD';\r\n\t\telse if ($cell_format=='NNNN')     $cell_format='DDDD\", \"';\r\n\r\n\t\t$cell_format = strtoupper($cell_format);\r\n\t\t$position = array_search($cell_format, $this->cell_formats, $strict=true);\r\n\t\tif ($position===false)\r\n\t\t{\r\n\t\t\t$position = count($this->cell_formats);\r\n\t\t\t$this->cell_formats[] = $this->escapeCellFormat($cell_format);\r\n\t\t\t$this->cell_types[] = $this->determineCellType($cell_format);\r\n\t\t}\r\n\t\treturn $position;\r\n\t}\r\n\r\n\tpublic function writeSheetHeader($sheet_name, array $header_types, $suppress_row = false,array $sheet_options = array())\r\n\t{\r\n\t\tif (empty($sheet_name) || empty($header_types) || !empty($this->sheets[$sheet_name]))\r\n\t\t\treturn;\r\n\r\n\t\tself::initializeSheet($sheet_name,$sheet_options);\r\n\t\t$sheet = &$this->sheets[$sheet_name];\r\n\t\t$sheet->columns = array();\r\n\t\tforeach($header_types as $v)\r\n\t\t{\r\n\t\t\t$sheet->columns[] = $this->addCellFormat($v);\r\n\t\t}\r\n        if (!$suppress_row)\r\n        {\r\n\t\t\t$header_row = array_keys($header_types);\r\n\r\n\t\t\t$sheet->file_writer->write('<row collapsed=\"false\" customFormat=\"false\" customHeight=\"false\" hidden=\"false\" ht=\"12.1\" outlineLevel=\"0\" r=\"' . (1) . '\">');\r\n\t\t\tforeach ($header_row as $k => $v) {\r\n\t\t\t\t$this->writeCell($sheet->file_writer, 0, $k, $v, $cell_format_index = '0');//'0'=>'string'\r\n\t\t\t}\r\n\t\t\t$sheet->file_writer->write('</row>');\r\n\t\t\t$sheet->row_count++;\r\n\t\t}\r\n\t\t$this->current_sheet = $sheet_name;\r\n\t}\r\n\r\n\tpublic function writeSheetRow($sheet_name, array $row,array $sheet_options = array())\r\n\t{\r\n\t\tif (empty($sheet_name) || empty($row))\r\n\t\t\treturn;\r\n\r\n\t\tself::initializeSheet($sheet_name,$sheet_options);\r\n\t\t$sheet = &$this->sheets[$sheet_name];\r\n\t\tif (empty($sheet->columns))\r\n\t\t{\r\n\t\t\t$sheet->columns = array_fill($from=0, $until=count($row), '0');//'0'=>'string'\r\n\t\t}\r\n\r\n\t\t$sheet->file_writer->write('<row collapsed=\"false\" customFormat=\"false\" customHeight=\"false\" hidden=\"false\" ht=\"12.1\" outlineLevel=\"0\" r=\"' . ($sheet->row_count + 1) . '\">');\r\n\t\t$column_count=0;\r\n\t\tforeach ($row as $k => $v) {\r\n\t\t\t$this->writeCell($sheet->file_writer, $sheet->row_count, $column_count, $v, $sheet->columns[$column_count]);\r\n\t\t\t$column_count++;\r\n\t\t}\r\n\t\t$sheet->file_writer->write('</row>');\r\n\t\t$sheet->row_count++;\r\n\t\t$this->current_sheet = $sheet_name;\r\n\t}\r\n\r\n\tprotected function finalizeSheet($sheet_name)\r\n\t{\r\n\t\tif (empty($sheet_name) || $this->sheets[$sheet_name]->finalized)\r\n\t\t\treturn;\r\n\r\n\t\t$sheet = &$this->sheets[$sheet_name];\r\n\r\n\t\t$sheet->file_writer->write(    '</sheetData>');\r\n\r\n\t\tif (!empty($sheet->merge_cells)) {\r\n\t\t\t$sheet->file_writer->write(    '<mergeCells>');\r\n\t\t\tforeach ($sheet->merge_cells as $range) {\r\n\t\t\t\t$sheet->file_writer->write(        '<mergeCell ref=\"' . $range . '\"/>');\r\n\t\t\t}\r\n\t\t\t$sheet->file_writer->write(    '</mergeCells>');\r\n\t\t}\r\n\r\n\t\t$max_cell = self::xlsCell($sheet->row_count - 1, count($sheet->columns) - 1);\r\n\t\t\r\n\t\tif($sheet->autofilter) {\r\n\t\t  $sheet->file_writer->write('<autoFilter ref=\"A1:'.$max_cell.'\" />');\r\n\t\t}\r\n\t\t$sheet->file_writer->write(    '<printOptions headings=\"false\" gridLines=\"false\" gridLinesSet=\"true\" horizontalCentered=\"false\" verticalCentered=\"false\"/>');\r\n\t\t$sheet->file_writer->write(    '<pageMargins left=\"0.5\" right=\"0.5\" top=\"1.0\" bottom=\"1.0\" header=\"0.5\" footer=\"0.5\"/>');\r\n\t\t$sheet->file_writer->write(    '<pageSetup blackAndWhite=\"false\" cellComments=\"none\" copies=\"1\" draft=\"false\" firstPageNumber=\"1\" fitToHeight=\"1\" fitToWidth=\"1\" horizontalDpi=\"300\" orientation=\"portrait\" pageOrder=\"downThenOver\" paperSize=\"1\" scale=\"100\" useFirstPageNumber=\"true\" usePrinterDefaults=\"false\" verticalDpi=\"300\"/>');\r\n\t\t$sheet->file_writer->write(    '<headerFooter differentFirst=\"false\" differentOddEven=\"false\">');\r\n\t\t$sheet->file_writer->write(        '<oddHeader>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12&amp;A</oddHeader>');\r\n\t\t$sheet->file_writer->write(        '<oddFooter>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12Page &amp;P</oddFooter>');\r\n\t\t$sheet->file_writer->write(    '</headerFooter>');\r\n\t\t$sheet->file_writer->write('</worksheet>');\r\n\r\n\t\t\r\n\t\t$max_cell_tag = '<dimension ref=\"A1:' . $max_cell . '\"/>';\r\n\t\t$padding_length = $sheet->max_cell_tag_end - $sheet->max_cell_tag_start - strlen($max_cell_tag);\r\n\t\t$sheet->file_writer->fseek($sheet->max_cell_tag_start);\r\n\t\t$sheet->file_writer->write($max_cell_tag.str_repeat(\" \", $padding_length));\r\n\t\t$sheet->file_writer->close();\r\n\t\t$sheet->finalized=true;\r\n\t}\r\n\t\r\n\tpublic function markMergedCell($sheet_name, $start_cell_row, $start_cell_column, $end_cell_row, $end_cell_column)\r\n\t{\r\n\t\tif (empty($sheet_name) || $this->sheets[$sheet_name]->finalized)\r\n\t\t\treturn;\r\n\r\n\t\tself::initializeSheet($sheet_name);\r\n\t\t$sheet = &$this->sheets[$sheet_name];\r\n\r\n\t\t$startCell = self::xlsCell($start_cell_row, $start_cell_column);\r\n\t\t$endCell = self::xlsCell($end_cell_row, $end_cell_column);\r\n\t\t$sheet->merge_cells[] = $startCell . \":\" . $endCell;\r\n\t}\r\n\r\n\tpublic function writeSheet(array $data, $sheet_name='', array $header_types=array(), array $sheet_options=array())\r\n\t{\r\n\t\t$sheet_name = empty($sheet_name) ? 'Sheet1' : $sheet_name;\r\n\t\t$data = empty($data) ? array(array('')) : $data;\r\n\t\tif (!empty($header_types))\r\n\t\t{\r\n\t\t\t$this->writeSheetHeader($sheet_name, $header_types,false,$sheet_options);\r\n\t\t}\r\n\t\tforeach($data as $i=>$row)\r\n\t\t{\r\n\t\t\t$this->writeSheetRow($sheet_name, $row,$sheet_options);\r\n\t\t}\r\n\t\t$this->finalizeSheet($sheet_name);\r\n\t}\r\n\r\n\tprotected function writeCell(XLSXWriter_BuffererWriter &$file, $row_number, $column_number, $value, $cell_format_index)\r\n\t{\r\n\t\t$cell_type = $this->cell_types[$cell_format_index];\r\n\t\t$cell_name = self::xlsCell($row_number, $column_number);\r\n\r\n\t\tif (!is_scalar($value) || $value==='') { //objects, array, empty\r\n\t\t\t$file->write('<c r=\"'.$cell_name.'\" s=\"'.$cell_format_index.'\"/>');\r\n\t\t} elseif (is_string($value) && $value{0}=='='){\r\n\t\t\t$file->write('<c r=\"'.$cell_name.'\" s=\"'.$cell_format_index.'\" t=\"s\"><f>'.self::xmlspecialchars($value).'</f></c>');\r\n\t\t} elseif ($cell_type=='date') {\r\n\t\t\t$file->write('<c r=\"'.$cell_name.'\" s=\"'.$cell_format_index.'\" t=\"n\"><v>'.intval(self::convert_date_time($value)).'</v></c>');\r\n\t\t} elseif ($cell_type=='datetime') {\r\n\t\t\t$file->write('<c r=\"'.$cell_name.'\" s=\"'.$cell_format_index.'\" t=\"n\"><v>'.self::convert_date_time($value).'</v></c>');\r\n\t\t} elseif ($cell_type=='currency' || $cell_type=='percent' || $cell_type=='numeric') {\r\n\t\t\t$file->write('<c r=\"'.$cell_name.'\" s=\"'.$cell_format_index.'\" t=\"n\"><v>'.self::xmlspecialchars($value).'</v></c>');//int,float,currency\r\n\t\t} else if (!is_string($value)){\r\n\t\t\t$file->write('<c r=\"'.$cell_name.'\" s=\"'.$cell_format_index.'\" t=\"n\"><v>'.($value*1).'</v></c>');\r\n\t\t} else if ($value{0}!='0' && $value{0}!='+' && filter_var($value, FILTER_VALIDATE_INT, array('options'=>array('max_range'=>2147483647)))){\r\n\t\t\t$file->write('<c r=\"'.$cell_name.'\" s=\"'.$cell_format_index.'\" t=\"n\"><v>'.($value*1).'</v></c>');\r\n\t\t} else { //implied: ($cell_format=='string')\r\n\t\t\t$file->write('<c r=\"'.$cell_name.'\" s=\"'.$cell_format_index.'\" t=\"s\"><v>'.self::xmlspecialchars($this->setSharedString($value)).'</v></c>');\r\n\t\t}\r\n\t}//\r\n\r\n\tprotected function writeStylesXML()\r\n\t{\r\n\t\t$temporary_filename = $this->tempFilename();\r\n\t\t$file = new XLSXWriter_BuffererWriter($temporary_filename);\r\n\t\t$file->write('<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>'.\"\\n\");\r\n\t\t$file->write('<styleSheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">');\r\n\t\t$file->write('<numFmts count=\"'.count($this->cell_formats).'\">');\r\n\t\tforeach($this->cell_formats as $i=>$v)\r\n\t\t{\r\n\t\t\t$file->write('<numFmt numFmtId=\"'.(164+$i).'\" formatCode=\"'.self::xmlspecialchars($v).'\" />');\r\n\t\t}\r\n\t\t//$file->write(\t\t'<numFmt formatCode=\"GENERAL\" numFmtId=\"164\"/>');\r\n\t\t//$file->write(\t\t'<numFmt formatCode=\"[$$-1009]#,##0.00;[RED]\\-[$$-1009]#,##0.00\" numFmtId=\"165\"/>');\r\n\t\t//$file->write(\t\t'<numFmt formatCode=\"YYYY-MM-DD\\ HH:MM:SS\" numFmtId=\"166\"/>');\r\n\t\t//$file->write(\t\t'<numFmt formatCode=\"YYYY-MM-DD\" numFmtId=\"167\"/>');\r\n\t\t$file->write('</numFmts>');\r\n\t\t$file->write('<fonts count=\"4\">');\r\n\t\t$file->write(\t\t'<font><name val=\"Arial\"/><charset val=\"1\"/><family val=\"2\"/><sz val=\"10\"/></font>');\r\n\t\t$file->write(\t\t'<font><name val=\"Arial\"/><family val=\"0\"/><sz val=\"10\"/></font>');\r\n\t\t$file->write(\t\t'<font><name val=\"Arial\"/><family val=\"0\"/><sz val=\"10\"/></font>');\r\n\t\t$file->write(\t\t'<font><name val=\"Arial\"/><family val=\"0\"/><sz val=\"10\"/></font>');\r\n\t\t$file->write('</fonts>');\r\n\t\t$file->write('<fills count=\"2\"><fill><patternFill patternType=\"none\"/></fill><fill><patternFill patternType=\"gray125\"/></fill></fills>');\r\n\t\t$file->write('<borders count=\"1\"><border diagonalDown=\"false\" diagonalUp=\"false\"><left/><right/><top/><bottom/><diagonal/></border></borders>');\r\n\t\t$file->write(\t'<cellStyleXfs count=\"20\">');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"true\" applyBorder=\"true\" applyFont=\"true\" applyProtection=\"true\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"164\">');\r\n\t\t$file->write(\t\t'<alignment horizontal=\"general\" indent=\"0\" shrinkToFit=\"false\" textRotation=\"0\" vertical=\"bottom\" wrapText=\"false\"/>');\r\n\t\t$file->write(\t\t'<protection hidden=\"false\" locked=\"true\"/>');\r\n\t\t$file->write(\t\t'</xf>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"1\" numFmtId=\"0\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"1\" numFmtId=\"0\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"2\" numFmtId=\"0\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"2\" numFmtId=\"0\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"0\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"0\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"0\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"0\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"0\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"0\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"0\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"0\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"0\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"0\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"1\" numFmtId=\"43\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"1\" numFmtId=\"41\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"1\" numFmtId=\"44\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"1\" numFmtId=\"42\"/>');\r\n\t\t$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"true\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"1\" numFmtId=\"9\"/>');\r\n\t\t$file->write(\t'</cellStyleXfs>');\r\n\r\n\t\t$file->write(\t'<cellXfs count=\"'.count($this->cell_formats).'\">');\r\n\t\tforeach($this->cell_formats as $i=>$v)\r\n\t\t{\r\n\t\t\t$file->write('<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"false\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"'.(164+$i).'\" xfId=\"0\"/>');\r\n\t\t}\r\n\t\t$file->write(\t'</cellXfs>');\r\n\t\t//$file->write(\t'<cellXfs count=\"4\">');\r\n\t\t//$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"false\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"164\" xfId=\"0\"/>');\r\n\t\t//$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"false\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"165\" xfId=\"0\"/>');\r\n\t\t//$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"false\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"166\" xfId=\"0\"/>');\r\n\t\t//$file->write(\t\t'<xf applyAlignment=\"false\" applyBorder=\"false\" applyFont=\"false\" applyProtection=\"false\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"167\" xfId=\"0\"/>');\r\n\t\t//$file->write(\t'</cellXfs>');\r\n\t\t$file->write(\t'<cellStyles count=\"6\">');\r\n\t\t$file->write(\t\t'<cellStyle builtinId=\"0\" customBuiltin=\"false\" name=\"Normal\" xfId=\"0\"/>');\r\n\t\t$file->write(\t\t'<cellStyle builtinId=\"3\" customBuiltin=\"false\" name=\"Comma\" xfId=\"15\"/>');\r\n\t\t$file->write(\t\t'<cellStyle builtinId=\"6\" customBuiltin=\"false\" name=\"Comma [0]\" xfId=\"16\"/>');\r\n\t\t$file->write(\t\t'<cellStyle builtinId=\"4\" customBuiltin=\"false\" name=\"Currency\" xfId=\"17\"/>');\r\n\t\t$file->write(\t\t'<cellStyle builtinId=\"7\" customBuiltin=\"false\" name=\"Currency [0]\" xfId=\"18\"/>');\r\n\t\t$file->write(\t\t'<cellStyle builtinId=\"5\" customBuiltin=\"false\" name=\"Percent\" xfId=\"19\"/>');\r\n\t\t$file->write(\t'</cellStyles>');\r\n\t\t$file->write('</styleSheet>');\r\n\t\t$file->close();\r\n\t\treturn $temporary_filename;\r\n\t}\r\n\r\n\tprotected function setSharedString($v)\r\n\t{\r\n\t\tif (isset($this->shared_strings[$v]))\r\n\t\t{\r\n\t\t\t$string_value = $this->shared_strings[$v];\r\n\t\t}\r\n\t\telse\r\n\t\t{\r\n\t\t\t$string_value = count($this->shared_strings);\r\n\t\t\t$this->shared_strings[$v] = $string_value;\r\n\t\t}\r\n\t\t$this->shared_string_count++;//non-unique count\r\n\t\treturn $string_value;\r\n\t}\r\n\r\n\tprotected function writeSharedStringsXML()\r\n\t{\r\n\t\t$temporary_filename = $this->tempFilename();\r\n\t\t$file = new XLSXWriter_BuffererWriter($temporary_filename, $fd_flags='w', $check_utf8=true);\r\n\t\t$file->write('<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>'.\"\\n\");\r\n\t\t$file->write('<sst count=\"'.($this->shared_string_count).'\" uniqueCount=\"'.count($this->shared_strings).'\" xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">');\r\n\t\tforeach($this->shared_strings as $s=>$c)\r\n\t\t{\r\n\t\t\t$file->write('<si><t>'.self::xmlspecialchars($s).'</t></si>');\r\n\t\t}\r\n\t\t$file->write('</sst>');\r\n\t\t$file->close();\r\n\r\n\t\treturn $temporary_filename;\r\n\t}\r\n\r\n\tprotected function buildAppXML()\r\n\t{\r\n\t\t$app_xml=\"\";\r\n\t\t$app_xml.='<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>'.\"\\n\";\r\n\t\t$app_xml.='<Properties xmlns=\"http://schemas.openxmlformats.org/officeDocument/2006/extended-properties\" xmlns:vt=\"http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes\"><TotalTime>0</TotalTime></Properties>';\r\n\t\treturn $app_xml;\r\n\t}\r\n\r\n\tprotected function buildCoreXML()\r\n\t{\r\n\t\t$core_xml=\"\";\r\n\t\t$core_xml.='<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>'.\"\\n\";\r\n\t\t$core_xml.='<cp:coreProperties xmlns:cp=\"http://schemas.openxmlformats.org/package/2006/metadata/core-properties\" xmlns:dc=\"http://purl.org/dc/elements/1.1/\" xmlns:dcmitype=\"http://purl.org/dc/dcmitype/\" xmlns:dcterms=\"http://purl.org/dc/terms/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">';\r\n\t\t$core_xml.='<dcterms:created xsi:type=\"dcterms:W3CDTF\">'.date(\"Y-m-d\\TH:i:s.00\\Z\").'</dcterms:created>';//$date_time = '2014-10-25T15:54:37.00Z';\r\n\t\t$core_xml.='<dc:creator>'.self::xmlspecialchars($this->author).'</dc:creator>';\r\n\t\t$core_xml.='<cp:revision>0</cp:revision>';\r\n\t\t$core_xml.='</cp:coreProperties>';\r\n\t\treturn $core_xml;\r\n\t}\r\n\r\n\tprotected function buildRelationshipsXML()\r\n\t{\r\n\t\t$rels_xml=\"\";\r\n\t\t$rels_xml.='<?xml version=\"1.0\" encoding=\"UTF-8\"?>'.\"\\n\";\r\n\t\t$rels_xml.='<Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\">';\r\n\t\t$rels_xml.='<Relationship Id=\"rId1\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument\" Target=\"xl/workbook.xml\"/>';\r\n\t\t$rels_xml.='<Relationship Id=\"rId2\" Type=\"http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties\" Target=\"docProps/core.xml\"/>';\r\n\t\t$rels_xml.='<Relationship Id=\"rId3\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties\" Target=\"docProps/app.xml\"/>';\r\n\t\t$rels_xml.=\"\\n\";\r\n\t\t$rels_xml.='</Relationships>';\r\n\t\treturn $rels_xml;\r\n\t}\r\n\r\n\tprotected function buildWorkbookXML()\r\n\t{\r\n\t\t$i=0;\r\n\t\t$workbook_xml=\"\";\r\n\t\t$workbook_xml.='<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>'.\"\\n\";\r\n\t\t$workbook_xml.='<workbook xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\">';\r\n\t\t$workbook_xml.='<fileVersion appName=\"Calc\"/><workbookPr backupFile=\"false\" showObjects=\"all\" date1904=\"false\"/><workbookProtection/>';\r\n\t\t$workbook_xml.='<bookViews><workbookView activeTab=\"0\" firstSheet=\"0\" showHorizontalScroll=\"true\" showSheetTabs=\"true\" showVerticalScroll=\"true\" tabRatio=\"212\" windowHeight=\"8192\" windowWidth=\"16384\" xWindow=\"0\" yWindow=\"0\"/></bookViews>';\r\n\t\t$workbook_xml.='<sheets>';\r\n\t\tforeach($this->sheets as $sheet_name=>$sheet) {\r\n\t\t\t$workbook_xml.='<sheet name=\"'.self::xmlspecialchars($sheet->sheetname).'\" sheetId=\"'.($i+1).'\" state=\"visible\" r:id=\"rId'.($i+2).'\"/>';\r\n\t\t\t$i++;\r\n\t\t}\r\n\t\t$workbook_xml.='</sheets>';\r\n\t\t$workbook_xml.='<calcPr iterateCount=\"100\" refMode=\"A1\" iterate=\"false\" iterateDelta=\"0.001\"/></workbook>';\r\n\t\treturn $workbook_xml;\r\n\t}\r\n\r\n\tprotected function buildWorkbookRelsXML()\r\n\t{\r\n\t\t$i=0;\r\n\t\t$wkbkrels_xml=\"\";\r\n\t\t$wkbkrels_xml.='<?xml version=\"1.0\" encoding=\"UTF-8\"?>'.\"\\n\";\r\n\t\t$wkbkrels_xml.='<Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\">';\r\n\t\t$wkbkrels_xml.='<Relationship Id=\"rId1\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles\" Target=\"styles.xml\"/>';\r\n\t\tforeach($this->sheets as $sheet_name=>$sheet) {\r\n\t\t\t$wkbkrels_xml.='<Relationship Id=\"rId'.($i+2).'\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet\" Target=\"worksheets/'.($sheet->xmlname).'\"/>';\r\n\t\t\t$i++;\r\n\t\t}\r\n\t\tif (!empty($this->shared_strings)) {\r\n\t\t\t$wkbkrels_xml.='<Relationship Id=\"rId'.(count($this->sheets)+2).'\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings\" Target=\"sharedStrings.xml\"/>';\r\n\t\t}\r\n\t\t$wkbkrels_xml.=\"\\n\";\r\n\t\t$wkbkrels_xml.='</Relationships>';\r\n\t\treturn $wkbkrels_xml;\r\n\t}\r\n\r\n\tprotected function buildContentTypesXML()\r\n\t{\r\n\t\t$content_types_xml=\"\";\r\n\t\t$content_types_xml.='<?xml version=\"1.0\" encoding=\"UTF-8\"?>'.\"\\n\";\r\n\t\t$content_types_xml.='<Types xmlns=\"http://schemas.openxmlformats.org/package/2006/content-types\">';\r\n\t\t$content_types_xml.='<Override PartName=\"/_rels/.rels\" ContentType=\"application/vnd.openxmlformats-package.relationships+xml\"/>';\r\n\t\t$content_types_xml.='<Override PartName=\"/xl/_rels/workbook.xml.rels\" ContentType=\"application/vnd.openxmlformats-package.relationships+xml\"/>';\r\n\t\tforeach($this->sheets as $sheet_name=>$sheet) {\r\n\t\t\t$content_types_xml.='<Override PartName=\"/xl/worksheets/'.($sheet->xmlname).'\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml\"/>';\r\n\t\t}\r\n\t\tif (!empty($this->shared_strings)) {\r\n\t\t\t$content_types_xml.='<Override PartName=\"/xl/sharedStrings.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml\"/>';\r\n\t\t}\r\n\t\t$content_types_xml.='<Override PartName=\"/xl/workbook.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml\"/>';\r\n\t\t$content_types_xml.='<Override PartName=\"/xl/styles.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml\"/>';\r\n\t\t$content_types_xml.='<Override PartName=\"/docProps/app.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.extended-properties+xml\"/>';\r\n\t\t$content_types_xml.='<Override PartName=\"/docProps/core.xml\" ContentType=\"application/vnd.openxmlformats-package.core-properties+xml\"/>';\r\n\t\t$content_types_xml.=\"\\n\";\r\n\t\t$content_types_xml.='</Types>';\r\n\t\treturn $content_types_xml;\r\n\t}\r\n\r\n\t//------------------------------------------------------------------\r\n\t/*\r\n\t * @param $row_number int, zero based\r\n\t * @param $column_number int, zero based\r\n\t * @return Cell label/coordinates, ex: A1, C3, AA42\r\n\t * */\r\n\tpublic static function xlsCell($row_number, $column_number)\r\n\t{\r\n\t\t$n = $column_number;\r\n\t\tfor($r = \"\"; $n >= 0; $n = intval($n / 26) - 1) {\r\n\t\t\t$r = chr($n%26 + 0x41) . $r;\r\n\t\t}\r\n\t\treturn $r . ($row_number+1);\r\n\t}\r\n\t//------------------------------------------------------------------\r\n\tpublic static function log($string)\r\n\t{\r\n\t\tfile_put_contents(\"php://stderr\", date(\"Y-m-d H:i:s:\").rtrim(is_array($string) ? json_encode($string) : $string).\"\\n\");\r\n\t}\r\n\t//------------------------------------------------------------------\r\n\tpublic static function sanitize_filename($filename) //http://msdn.microsoft.com/en-us/library/aa365247%28VS.85%29.aspx\r\n\t{\r\n\t\t$nonprinting = array_map('chr', range(0,31));\r\n\t\t$invalid_chars = array('<', '>', '?', '\"', ':', '|', '\\\\', '/', '*', '&');\r\n\t\t$all_invalids = array_merge($nonprinting,$invalid_chars);\r\n\t\treturn str_replace($all_invalids, \"\", $filename);\r\n\t}\r\n\t//------------------------------------------------------------------\r\n\tpublic static function xmlspecialchars($val)\r\n\t{\r\n\t\treturn str_replace(\"'\", \"&#39;\", htmlspecialchars($val));\r\n\t}\r\n\t//------------------------------------------------------------------\r\n\tpublic static function array_first_key(array $arr)\r\n\t{\r\n\t\treset($arr);\r\n\t\t$first_key = key($arr);\r\n\t\treturn $first_key;\r\n\t}\r\n\t//------------------------------------------------------------------\r\n\tpublic static function convert_date_time($date_input) //thanks to Excel::Writer::XLSX::Worksheet.pm (perl)\r\n\t{\r\n\t\t$days    = 0;    # Number of days since epoch\r\n\t\t$seconds = 0;    # Time expressed as fraction of 24h hours in seconds\r\n\t\t$year=$month=$day=0;\r\n\t\t$hour=$min  =$sec=0;\r\n\r\n\t\t$date_time = $date_input;\r\n\t\tif (preg_match(\"/(\\d{4})\\-(\\d{2})\\-(\\d{2})/\", $date_time, $matches))\r\n\t\t{\r\n\t\t\tlist($junk,$year,$month,$day) = $matches;\r\n\t\t}\r\n\t\tif (preg_match(\"/(\\d{2}):(\\d{2}):(\\d{2})/\", $date_time, $matches))\r\n\t\t{\r\n\t\t\tlist($junk,$hour,$min,$sec) = $matches;\r\n\t\t\t$seconds = ( $hour * 60 * 60 + $min * 60 + $sec ) / ( 24 * 60 * 60 );\r\n\t\t}\r\n\r\n\t\t//using 1900 as epoch, not 1904, ignoring 1904 special case\r\n\r\n\t\t# Special cases for Excel.\r\n\t\tif (\"$year-$month-$day\"=='1899-12-31')  return $seconds      ;    # Excel 1900 epoch\r\n\t\tif (\"$year-$month-$day\"=='1900-01-00')  return $seconds      ;    # Excel 1900 epoch\r\n\t\tif (\"$year-$month-$day\"=='1900-02-29')  return 60 + $seconds ;    # Excel false leapday\r\n\r\n\t\t# We calculate the date by calculating the number of days since the epoch\r\n\t\t# and adjust for the number of leap days. We calculate the number of leap\r\n\t\t# days by normalising the year in relation to the epoch. Thus the year 2000\r\n\t\t# becomes 100 for 4 and 100 year leapdays and 400 for 400 year leapdays.\r\n\t\t$epoch  = 1900;\r\n\t\t$offset = 0;\r\n\t\t$norm   = 300;\r\n\t\t$range  = $year - $epoch;\r\n\r\n\t\t# Set month days and check for leap year.\r\n\t\t$leap = (($year % 400 == 0) || (($year % 4 == 0) && ($year % 100)) ) ? 1 : 0;\r\n\t\t$mdays = array( 31, ($leap ? 29 : 28), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 );\r\n\r\n\t\t# Some boundary checks\r\n\t\tif($year < $epoch || $year > 9999) return 0;\r\n\t\tif($month < 1     || $month > 12)  return 0;\r\n\t\tif($day < 1       || $day > $mdays[ $month - 1 ]) return 0;\r\n\r\n\t\t# Accumulate the number of days since the epoch.\r\n\t\t$days = $day;    # Add days for current month\r\n\t\t$days += array_sum( array_slice($mdays, 0, $month-1 ) );    # Add days for past months\r\n\t\t$days += $range * 365;                      # Add days for past years\r\n\t\t$days += intval( ( $range ) / 4 );             # Add leapdays\r\n\t\t$days -= intval( ( $range + $offset ) / 100 ); # Subtract 100 year leapdays\r\n\t\t$days += intval( ( $range + $offset + $norm ) / 400 );  # Add 400 year leapdays\r\n\t\t$days -= $leap;                                      # Already counted above\r\n\r\n\t\t# Adjust for Excel erroneously treating 1900 as a leap year.\r\n\t\tif ($days > 59) { $days++;}\r\n\r\n\t\treturn $days + $seconds;\r\n\t}\r\n\t//------------------------------------------------------------------\r\n}\r\n\r\nclass XLSXWriter_BuffererWriter\r\n{\r\n\tprotected $fd=null;\r\n\tprotected $buffer='';\r\n\tprotected $check_utf8=false;\r\n\r\n\tpublic function __construct($filename, $fd_fopen_flags='w', $check_utf8=false)\r\n\t{\r\n\t\t$this->check_utf8 = $check_utf8;\r\n\t\t$this->fd = fopen($filename, $fd_fopen_flags);\r\n\t\tif ($this->fd===false) {\r\n\t\t\tXLSXWriter::log(\"Unable to open $filename for writing.\");\r\n\t\t}\r\n\t}\r\n\r\n\tpublic function write($string)\r\n\t{\r\n\t\t$this->buffer.=$string;\r\n\t\tif (isset($this->buffer[8191])) {\r\n\t\t\t$this->purge();\r\n\t\t}\r\n\t}\r\n\r\n\tprotected function purge()\r\n\t{\r\n\t\tif ($this->fd) {\r\n\t\t\tif ($this->check_utf8 && !self::isValidUTF8($this->buffer)) {\r\n\t\t\t\tXLSXWriter::log(\"Error, invalid UTF8 encoding detected.\");\r\n\t\t\t\t$this->check_utf8 = false;\r\n\t\t\t}\r\n\t\t\tfwrite($this->fd, $this->buffer);\r\n\t\t\t$this->buffer='';\r\n\t\t}\r\n\t}\r\n\r\n\tpublic function close()\r\n\t{\r\n\t\t$this->purge();\r\n\t\tif ($this->fd) {\r\n\t\t\tfclose($this->fd);\r\n\t\t\t$this->fd=null;\r\n\t\t}\r\n\t}\r\n\r\n\tpublic function __destruct()\r\n\t{\r\n\t\t$this->close();\r\n\t}\r\n\r\n\tpublic function ftell()\r\n\t{\r\n\t\tif ($this->fd) {\r\n\t\t\t$this->purge();\r\n\t\t\treturn ftell($this->fd);\r\n\t\t}\r\n\t\treturn -1;\r\n\t}\r\n\r\n\tpublic function fseek($pos)\r\n\t{\r\n\t\tif ($this->fd) {\r\n\t\t\t$this->purge();\r\n\t\t\treturn fseek($this->fd, $pos);\r\n\t\t}\r\n\t\treturn -1;\r\n\t}\r\n\r\n\tprotected static function isValidUTF8($string)\r\n\t{\r\n\t\tif (function_exists('mb_check_encoding'))\r\n\t\t{\r\n\t\t\treturn mb_check_encoding($string, 'UTF-8') ? true : false;\r\n\t\t}\r\n\t\treturn preg_match(\"//u\", $string) ? true : false;\r\n\t}\r\n}\r\n\r\n\r\n\r\n// vim: set filetype=php expandtab tabstop=4 shiftwidth=4 autoindent smartindent:\r\n``"}}
{"actor":{"display_login":"cgaudelet"}}