XLSXWriter.class.php 47 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892
  1. <?php
  2. /*
  3. * @license MIT License
  4. * */
  5. class XLSXWriter
  6. {
  7. //http://www.ecma-international.org/publications/standards/Ecma-376.htm
  8. //http://officeopenxml.com/SSstyles.php
  9. //------------------------------------------------------------------
  10. //http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx
  11. const EXCEL_2007_MAX_ROW=1048576;
  12. const EXCEL_2007_MAX_COL=16384;
  13. //------------------------------------------------------------------
  14. protected $title;
  15. protected $subject;
  16. protected $author;
  17. protected $isRightToLeft;
  18. protected $company;
  19. protected $description;
  20. protected $keywords = array();
  21. protected $current_sheet;
  22. protected $sheets = array();
  23. protected $temp_files = array();
  24. protected $cell_styles = array();
  25. protected $number_formats = array();
  26. public function __construct()
  27. {
  28. defined('ENT_XML1') or define('ENT_XML1',16);//for php 5.3, avoid fatal error
  29. date_default_timezone_get() or date_default_timezone_set('UTC');//php.ini missing tz, avoid warning
  30. is_writeable($this->tempFilename()) or self::log("Warning: tempdir ".sys_get_temp_dir()." not writeable, use ->setTempDir()");
  31. class_exists('ZipArchive') or self::log("Error: ZipArchive class does not exist");
  32. $this->addCellStyle($number_format='GENERAL', $style_string=null);
  33. }
  34. public function setTitle($title='') { $this->title=$title; }
  35. public function setSubject($subject='') { $this->subject=$subject; }
  36. public function setAuthor($author='') { $this->author=$author; }
  37. public function setCompany($company='') { $this->company=$company; }
  38. public function setKeywords($keywords='') { $this->keywords=$keywords; }
  39. public function setDescription($description='') { $this->description=$description; }
  40. public function setTempDir($tempdir='') { $this->tempdir=$tempdir; }
  41. public function setRightToLeft($isRightToLeft=false){ $this->isRightToLeft=$isRightToLeft; }
  42. public function __destruct()
  43. {
  44. if (!empty($this->temp_files)) {
  45. foreach($this->temp_files as $temp_file) {
  46. @unlink($temp_file);
  47. }
  48. }
  49. }
  50. protected function tempFilename()
  51. {
  52. $tempdir = !empty($this->tempdir) ? $this->tempdir : sys_get_temp_dir();
  53. $filename = tempnam($tempdir, "xlsx_writer_");
  54. $this->temp_files[] = $filename;
  55. return $filename;
  56. }
  57. public function writeToStdOut()
  58. {
  59. $temp_file = $this->tempFilename();
  60. self::writeToFile($temp_file);
  61. readfile($temp_file);
  62. }
  63. public function writeToString()
  64. {
  65. $temp_file = $this->tempFilename();
  66. self::writeToFile($temp_file);
  67. $string = file_get_contents($temp_file);
  68. return $string;
  69. }
  70. public function writeToFile($filename)
  71. {
  72. foreach($this->sheets as $sheet_name => $sheet) {
  73. self::finalizeSheet($sheet_name);//making sure all footers have been written
  74. }
  75. if ( file_exists( $filename ) ) {
  76. if ( is_writable( $filename ) ) {
  77. @unlink( $filename ); //if the zip already exists, remove it
  78. } else {
  79. self::log( "Error in " . __CLASS__ . "::" . __FUNCTION__ . ", file is not writeable." );
  80. return;
  81. }
  82. }
  83. $zip = new ZipArchive();
  84. if (empty($this->sheets)) { self::log("Error in ".__CLASS__."::".__FUNCTION__.", no worksheets defined."); return; }
  85. if (!$zip->open($filename, ZipArchive::CREATE)) { self::log("Error in ".__CLASS__."::".__FUNCTION__.", unable to create zip."); return; }
  86. $zip->addEmptyDir("docProps/");
  87. $zip->addFromString("docProps/app.xml" , self::buildAppXML() );
  88. $zip->addFromString("docProps/core.xml", self::buildCoreXML());
  89. $zip->addEmptyDir("_rels/");
  90. $zip->addFromString("_rels/.rels", self::buildRelationshipsXML());
  91. $zip->addEmptyDir("xl/worksheets/");
  92. foreach($this->sheets as $sheet) {
  93. $zip->addFile($sheet->filename, "xl/worksheets/".$sheet->xmlname );
  94. }
  95. $zip->addFromString("xl/workbook.xml" , self::buildWorkbookXML() );
  96. $zip->addFile($this->writeStylesXML(), "xl/styles.xml" ); //$zip->addFromString("xl/styles.xml" , self::buildStylesXML() );
  97. $zip->addFromString("[Content_Types].xml" , self::buildContentTypesXML() );
  98. $zip->addEmptyDir("xl/_rels/");
  99. $zip->addFromString("xl/_rels/workbook.xml.rels", self::buildWorkbookRelsXML() );
  100. $zip->close();
  101. }
  102. protected function initializeSheet($sheet_name, $col_widths=array(), $auto_filter=false, $freeze_rows=false, $freeze_columns=false )
  103. {
  104. //if already initialized
  105. if ($this->current_sheet==$sheet_name || isset($this->sheets[$sheet_name]))
  106. return;
  107. $sheet_filename = $this->tempFilename();
  108. $sheet_xmlname = 'sheet' . (count($this->sheets) + 1).".xml";
  109. $this->sheets[$sheet_name] = (object)array(
  110. 'filename' => $sheet_filename,
  111. 'sheetname' => $sheet_name,
  112. 'xmlname' => $sheet_xmlname,
  113. 'row_count' => 0,
  114. 'file_writer' => new XLSXWriter_BuffererWriter($sheet_filename),
  115. 'columns' => array(),
  116. 'merge_cells' => array(),
  117. 'max_cell_tag_start' => 0,
  118. 'max_cell_tag_end' => 0,
  119. 'auto_filter' => $auto_filter,
  120. 'freeze_rows' => $freeze_rows,
  121. 'freeze_columns' => $freeze_columns,
  122. 'finalized' => false,
  123. );
  124. $rightToLeftValue = $this->isRightToLeft ? 'true' : 'false';
  125. $sheet = &$this->sheets[$sheet_name];
  126. $tabselected = count($this->sheets) == 1 ? 'true' : 'false';//only first sheet is selected
  127. $max_cell=XLSXWriter::xlsCell(self::EXCEL_2007_MAX_ROW, self::EXCEL_2007_MAX_COL);//XFE1048577
  128. $sheet->file_writer->write('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n");
  129. $sheet->file_writer->write('<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">');
  130. $sheet->file_writer->write( '<sheetPr filterMode="false">');
  131. $sheet->file_writer->write( '<pageSetUpPr fitToPage="false"/>');
  132. $sheet->file_writer->write( '</sheetPr>');
  133. $sheet->max_cell_tag_start = $sheet->file_writer->ftell();
  134. $sheet->file_writer->write('<dimension ref="A1:' . $max_cell . '"/>');
  135. $sheet->max_cell_tag_end = $sheet->file_writer->ftell();
  136. $sheet->file_writer->write( '<sheetViews>');
  137. $sheet->file_writer->write( '<sheetView colorId="64" defaultGridColor="true" rightToLeft="'.$rightToLeftValue.'" 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">');
  138. if ($sheet->freeze_rows && $sheet->freeze_columns) {
  139. $sheet->file_writer->write( '<pane ySplit="'.$sheet->freeze_rows.'" xSplit="'.$sheet->freeze_columns.'" topLeftCell="'.self::xlsCell($sheet->freeze_rows, $sheet->freeze_columns).'" activePane="bottomRight" state="frozen"/>');
  140. $sheet->file_writer->write( '<selection activeCell="'.self::xlsCell($sheet->freeze_rows, 0).'" activeCellId="0" pane="topRight" sqref="'.self::xlsCell($sheet->freeze_rows, 0).'"/>');
  141. $sheet->file_writer->write( '<selection activeCell="'.self::xlsCell(0, $sheet->freeze_columns).'" activeCellId="0" pane="bottomLeft" sqref="'.self::xlsCell(0, $sheet->freeze_columns).'"/>');
  142. $sheet->file_writer->write( '<selection activeCell="'.self::xlsCell($sheet->freeze_rows, $sheet->freeze_columns).'" activeCellId="0" pane="bottomRight" sqref="'.self::xlsCell($sheet->freeze_rows, $sheet->freeze_columns).'"/>');
  143. }
  144. elseif ($sheet->freeze_rows) {
  145. $sheet->file_writer->write( '<pane ySplit="'.$sheet->freeze_rows.'" topLeftCell="'.self::xlsCell($sheet->freeze_rows, 0).'" activePane="bottomLeft" state="frozen"/>');
  146. $sheet->file_writer->write( '<selection activeCell="'.self::xlsCell($sheet->freeze_rows, 0).'" activeCellId="0" pane="bottomLeft" sqref="'.self::xlsCell($sheet->freeze_rows, 0).'"/>');
  147. }
  148. elseif ($sheet->freeze_columns) {
  149. $sheet->file_writer->write( '<pane xSplit="'.$sheet->freeze_columns.'" topLeftCell="'.self::xlsCell(0, $sheet->freeze_columns).'" activePane="topRight" state="frozen"/>');
  150. $sheet->file_writer->write( '<selection activeCell="'.self::xlsCell(0, $sheet->freeze_columns).'" activeCellId="0" pane="topRight" sqref="'.self::xlsCell(0, $sheet->freeze_columns).'"/>');
  151. }
  152. else { // not frozen
  153. $sheet->file_writer->write( '<selection activeCell="A1" activeCellId="0" pane="topLeft" sqref="A1"/>');
  154. }
  155. $sheet->file_writer->write( '</sheetView>');
  156. $sheet->file_writer->write( '</sheetViews>');
  157. $sheet->file_writer->write( '<cols>');
  158. $i=0;
  159. if (!empty($col_widths)) {
  160. foreach($col_widths as $column_width) {
  161. $sheet->file_writer->write( '<col collapsed="false" hidden="false" max="'.($i+1).'" min="'.($i+1).'" style="0" customWidth="true" width="'.floatval($column_width).'"/>');
  162. $i++;
  163. }
  164. }
  165. $sheet->file_writer->write( '<col collapsed="false" hidden="false" max="1024" min="'.($i+1).'" style="0" customWidth="false" width="11.5"/>');
  166. $sheet->file_writer->write( '</cols>');
  167. $sheet->file_writer->write( '<sheetData>');
  168. }
  169. private function addCellStyle($number_format, $cell_style_string)
  170. {
  171. $number_format_idx = self::add_to_list_get_index($this->number_formats, $number_format);
  172. $lookup_string = $number_format_idx.";".$cell_style_string;
  173. $cell_style_idx = self::add_to_list_get_index($this->cell_styles, $lookup_string);
  174. return $cell_style_idx;
  175. }
  176. private function initializeColumnTypes($header_types)
  177. {
  178. $column_types = array();
  179. foreach($header_types as $v)
  180. {
  181. $number_format = self::numberFormatStandardized($v);
  182. $number_format_type = self::determineNumberFormatType($number_format);
  183. $cell_style_idx = $this->addCellStyle($number_format, $style_string=null);
  184. $column_types[] = array('number_format' => $number_format,//contains excel format like 'YYYY-MM-DD HH:MM:SS'
  185. 'number_format_type' => $number_format_type, //contains friendly format like 'datetime'
  186. 'default_cell_style' => $cell_style_idx,
  187. );
  188. }
  189. return $column_types;
  190. }
  191. public function writeSheetHeader($sheet_name, array $header_types, $col_options = null)
  192. {
  193. if (empty($sheet_name) || empty($header_types) || !empty($this->sheets[$sheet_name]))
  194. return;
  195. $suppress_row = isset($col_options['suppress_row']) ? intval($col_options['suppress_row']) : false;
  196. if (is_bool($col_options))
  197. {
  198. self::log( "Warning! passing $suppress_row=false|true to writeSheetHeader() is deprecated, this will be removed in a future version." );
  199. $suppress_row = intval($col_options);
  200. }
  201. $style = &$col_options;
  202. $col_widths = isset($col_options['widths']) ? (array)$col_options['widths'] : array();
  203. $auto_filter = isset($col_options['auto_filter']) ? intval($col_options['auto_filter']) : false;
  204. $freeze_rows = isset($col_options['freeze_rows']) ? intval($col_options['freeze_rows']) : false;
  205. $freeze_columns = isset($col_options['freeze_columns']) ? intval($col_options['freeze_columns']) : false;
  206. self::initializeSheet($sheet_name, $col_widths, $auto_filter, $freeze_rows, $freeze_columns);
  207. $sheet = &$this->sheets[$sheet_name];
  208. $sheet->columns = $this->initializeColumnTypes($header_types);
  209. if (!$suppress_row)
  210. {
  211. $header_row = array_keys($header_types);
  212. $sheet->file_writer->write('<row collapsed="false" customFormat="false" customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="' . (1) . '">');
  213. foreach ($header_row as $c => $v) {
  214. $cell_style_idx = empty($style) ? $sheet->columns[$c]['default_cell_style'] : $this->addCellStyle( 'GENERAL', json_encode(isset($style[0]) ? $style[$c] : $style) );
  215. $this->writeCell($sheet->file_writer, 0, $c, $v, $number_format_type='n_string', $cell_style_idx);
  216. }
  217. $sheet->file_writer->write('</row>');
  218. $sheet->row_count++;
  219. }
  220. $this->current_sheet = $sheet_name;
  221. }
  222. public function writeSheetRow($sheet_name, array $row, $row_options=null)
  223. {
  224. if (empty($sheet_name))
  225. return;
  226. $this->initializeSheet($sheet_name);
  227. $sheet = &$this->sheets[$sheet_name];
  228. if (count($sheet->columns) < count($row)) {
  229. $default_column_types = $this->initializeColumnTypes( array_fill($from=0, $until=count($row), 'GENERAL') );//will map to n_auto
  230. $sheet->columns = array_merge((array)$sheet->columns, $default_column_types);
  231. }
  232. if (!empty($row_options))
  233. {
  234. $ht = isset($row_options['height']) ? floatval($row_options['height']) : 12.1;
  235. $customHt = isset($row_options['height']) ? true : false;
  236. $hidden = isset($row_options['hidden']) ? (bool)($row_options['hidden']) : false;
  237. $collapsed = isset($row_options['collapsed']) ? (bool)($row_options['collapsed']) : false;
  238. $sheet->file_writer->write('<row collapsed="'.($collapsed).'" customFormat="false" customHeight="'.($customHt).'" hidden="'.($hidden).'" ht="'.($ht).'" outlineLevel="0" r="' . ($sheet->row_count + 1) . '">');
  239. }
  240. else
  241. {
  242. $sheet->file_writer->write('<row collapsed="false" customFormat="false" customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="' . ($sheet->row_count + 1) . '">');
  243. }
  244. $style = &$row_options;
  245. $c=0;
  246. foreach ($row as $v) {
  247. $number_format = $sheet->columns[$c]['number_format'];
  248. $number_format_type = $sheet->columns[$c]['number_format_type'];
  249. $cell_style_idx = empty($style) ? $sheet->columns[$c]['default_cell_style'] : $this->addCellStyle( $number_format, json_encode(isset($style[0]) ? $style[$c] : $style) );
  250. $this->writeCell($sheet->file_writer, $sheet->row_count, $c, $v, $number_format_type, $cell_style_idx);
  251. $c++;
  252. }
  253. $sheet->file_writer->write('</row>');
  254. $sheet->row_count++;
  255. $this->current_sheet = $sheet_name;
  256. }
  257. public function countSheetRows($sheet_name = '')
  258. {
  259. $sheet_name = $sheet_name ?: $this->current_sheet;
  260. return array_key_exists($sheet_name, $this->sheets) ? $this->sheets[$sheet_name]->row_count : 0;
  261. }
  262. protected function finalizeSheet($sheet_name)
  263. {
  264. if (empty($sheet_name) || $this->sheets[$sheet_name]->finalized)
  265. return;
  266. $sheet = &$this->sheets[$sheet_name];
  267. $sheet->file_writer->write( '</sheetData>');
  268. if (!empty($sheet->merge_cells)) {
  269. $sheet->file_writer->write( '<mergeCells>');
  270. foreach ($sheet->merge_cells as $range) {
  271. $sheet->file_writer->write( '<mergeCell ref="' . $range . '"/>');
  272. }
  273. $sheet->file_writer->write( '</mergeCells>');
  274. }
  275. $max_cell = self::xlsCell($sheet->row_count - 1, count($sheet->columns) - 1);
  276. if ($sheet->auto_filter) {
  277. $sheet->file_writer->write( '<autoFilter ref="A1:' . $max_cell . '"/>');
  278. }
  279. $sheet->file_writer->write( '<printOptions headings="false" gridLines="false" gridLinesSet="true" horizontalCentered="false" verticalCentered="false"/>');
  280. $sheet->file_writer->write( '<pageMargins left="0.5" right="0.5" top="1.0" bottom="1.0" header="0.5" footer="0.5"/>');
  281. $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"/>');
  282. $sheet->file_writer->write( '<headerFooter differentFirst="false" differentOddEven="false">');
  283. $sheet->file_writer->write( '<oddHeader>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12&amp;A</oddHeader>');
  284. $sheet->file_writer->write( '<oddFooter>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12Page &amp;P</oddFooter>');
  285. $sheet->file_writer->write( '</headerFooter>');
  286. $sheet->file_writer->write('</worksheet>');
  287. $max_cell_tag = '<dimension ref="A1:' . $max_cell . '"/>';
  288. $padding_length = $sheet->max_cell_tag_end - $sheet->max_cell_tag_start - strlen($max_cell_tag);
  289. $sheet->file_writer->fseek($sheet->max_cell_tag_start);
  290. $sheet->file_writer->write($max_cell_tag.str_repeat(" ", $padding_length));
  291. $sheet->file_writer->close();
  292. $sheet->finalized=true;
  293. }
  294. public function markMergedCell($sheet_name, $start_cell_row, $start_cell_column, $end_cell_row, $end_cell_column)
  295. {
  296. if (empty($sheet_name) || $this->sheets[$sheet_name]->finalized)
  297. return;
  298. self::initializeSheet($sheet_name);
  299. $sheet = &$this->sheets[$sheet_name];
  300. $startCell = self::xlsCell($start_cell_row, $start_cell_column);
  301. $endCell = self::xlsCell($end_cell_row, $end_cell_column);
  302. $sheet->merge_cells[] = $startCell . ":" . $endCell;
  303. }
  304. public function writeSheet(array $data, $sheet_name='', array $header_types=array(), $options=null)
  305. {
  306. $sheet_name = empty($sheet_name) ? 'Sheet1' : $sheet_name;
  307. $data = empty($data) ? array(array('')) : $data;
  308. if (!empty($header_types))
  309. {
  310. $this->writeSheetHeader($sheet_name, $header_types, (!empty($options) && !empty($options['header']) ? $options['header'] : null));
  311. }
  312. foreach($data as $i=>$row)
  313. {
  314. $this->writeSheetRow($sheet_name, $row, (!empty($options) && !empty($options['content']) ? $options['content'] : null));
  315. }
  316. $this->finalizeSheet($sheet_name);
  317. }
  318. protected function writeCell(XLSXWriter_BuffererWriter &$file, $row_number, $column_number, $value, $num_format_type, $cell_style_idx)
  319. {
  320. $cell_name = self::xlsCell($row_number, $column_number);
  321. if (!is_scalar($value) || $value==='') { //objects, array, empty
  322. $file->write('<c r="'.$cell_name.'" s="'.$cell_style_idx.'"/>');
  323. } elseif (is_string($value) && $value[0]=='='){
  324. $file->write('<c r="'.$cell_name.'" s="'.$cell_style_idx.'" t="s"><f>'.self::xmlspecialchars($value).'</f></c>');
  325. } elseif ($num_format_type=='n_date') {
  326. $file->write('<c r="'.$cell_name.'" s="'.$cell_style_idx.'" t="n"><v>'.intval(self::convert_date_time($value)).'</v></c>');
  327. } elseif ($num_format_type=='n_datetime') {
  328. $file->write('<c r="'.$cell_name.'" s="'.$cell_style_idx.'" t="n"><v>'.self::convert_date_time($value).'</v></c>');
  329. } elseif ($num_format_type=='n_numeric') {
  330. $file->write('<c r="'.$cell_name.'" s="'.$cell_style_idx.'" t="n"><v>'.self::xmlspecialchars($value).'</v></c>');//int,float,currency
  331. } elseif ($num_format_type=='n_string') {
  332. $file->write('<c r="'.$cell_name.'" s="'.$cell_style_idx.'" t="inlineStr"><is><t>'.self::xmlspecialchars($value).'</t></is></c>');
  333. } elseif ($num_format_type=='n_auto' || 1) { //auto-detect unknown column types
  334. if (!is_string($value) || $value=='0' || ($value[0]!='0' && ctype_digit($value)) || preg_match("/^\-?(0|[1-9][0-9]*)(\.[0-9]+)?$/", $value)){
  335. $file->write('<c r="'.$cell_name.'" s="'.$cell_style_idx.'" t="n"><v>'.self::xmlspecialchars($value).'</v></c>');//int,float,currency
  336. } else { //implied: ($cell_format=='string')
  337. $file->write('<c r="'.$cell_name.'" s="'.$cell_style_idx.'" t="inlineStr"><is><t>'.self::xmlspecialchars($value).'</t></is></c>');
  338. }
  339. }
  340. }
  341. protected function styleFontIndexes()
  342. {
  343. static $border_allowed = array('left','right','top','bottom');
  344. static $border_style_allowed = array('thin','medium','thick','dashDot','dashDotDot','dashed','dotted','double','hair','mediumDashDot','mediumDashDotDot','mediumDashed','slantDashDot');
  345. static $horizontal_allowed = array('general','left','right','justify','center');
  346. static $vertical_allowed = array('bottom','center','distributed','top');
  347. $default_font = array('size'=>'10','name'=>'Arial','family'=>'2');
  348. $fills = array('','');//2 placeholders for static xml later
  349. $fonts = array('','','','');//4 placeholders for static xml later
  350. $borders = array('');//1 placeholder for static xml later
  351. $style_indexes = array();
  352. foreach($this->cell_styles as $i=>$cell_style_string)
  353. {
  354. $semi_colon_pos = strpos($cell_style_string,";");
  355. $number_format_idx = substr($cell_style_string, 0, $semi_colon_pos);
  356. $style_json_string = substr($cell_style_string, $semi_colon_pos+1);
  357. $style = @json_decode($style_json_string, $as_assoc=true);
  358. $style_indexes[$i] = array('num_fmt_idx'=>$number_format_idx);//initialize entry
  359. if (isset($style['border']) && is_string($style['border']))//border is a comma delimited str
  360. {
  361. $border_value['side'] = array_intersect(explode(",", $style['border']), $border_allowed);
  362. if (isset($style['border-style']) && in_array($style['border-style'],$border_style_allowed))
  363. {
  364. $border_value['style'] = $style['border-style'];
  365. }
  366. if (isset($style['border-color']) && is_string($style['border-color']) && $style['border-color'][0]=='#')
  367. {
  368. $v = substr($style['border-color'],1,6);
  369. $v = strlen($v)==3 ? $v[0].$v[0].$v[1].$v[1].$v[2].$v[2] : $v;// expand cf0 => ccff00
  370. $border_value['color'] = "FF".strtoupper($v);
  371. }
  372. $style_indexes[$i]['border_idx'] = self::add_to_list_get_index($borders, json_encode($border_value));
  373. }
  374. if (isset($style['fill']) && is_string($style['fill']) && $style['fill'][0]=='#')
  375. {
  376. $v = substr($style['fill'],1,6);
  377. $v = strlen($v)==3 ? $v[0].$v[0].$v[1].$v[1].$v[2].$v[2] : $v;// expand cf0 => ccff00
  378. $style_indexes[$i]['fill_idx'] = self::add_to_list_get_index($fills, "FF".strtoupper($v) );
  379. }
  380. if (isset($style['halign']) && in_array($style['halign'],$horizontal_allowed))
  381. {
  382. $style_indexes[$i]['alignment'] = true;
  383. $style_indexes[$i]['halign'] = $style['halign'];
  384. }
  385. if (isset($style['valign']) && in_array($style['valign'],$vertical_allowed))
  386. {
  387. $style_indexes[$i]['alignment'] = true;
  388. $style_indexes[$i]['valign'] = $style['valign'];
  389. }
  390. if (isset($style['wrap_text']))
  391. {
  392. $style_indexes[$i]['alignment'] = true;
  393. $style_indexes[$i]['wrap_text'] = (bool)$style['wrap_text'];
  394. }
  395. $font = $default_font;
  396. if (isset($style['font-size']))
  397. {
  398. $font['size'] = floatval($style['font-size']);//floatval to allow "10.5" etc
  399. }
  400. if (isset($style['font']) && is_string($style['font']))
  401. {
  402. if ($style['font']=='Comic Sans MS') { $font['family']=4; }
  403. if ($style['font']=='Times New Roman') { $font['family']=1; }
  404. if ($style['font']=='Courier New') { $font['family']=3; }
  405. $font['name'] = strval($style['font']);
  406. }
  407. if (isset($style['font-style']) && is_string($style['font-style']))
  408. {
  409. if (strpos($style['font-style'], 'bold')!==false) { $font['bold'] = true; }
  410. if (strpos($style['font-style'], 'italic')!==false) { $font['italic'] = true; }
  411. if (strpos($style['font-style'], 'strike')!==false) { $font['strike'] = true; }
  412. if (strpos($style['font-style'], 'underline')!==false) { $font['underline'] = true; }
  413. }
  414. if (isset($style['color']) && is_string($style['color']) && $style['color'][0]=='#' )
  415. {
  416. $v = substr($style['color'],1,6);
  417. $v = strlen($v)==3 ? $v[0].$v[0].$v[1].$v[1].$v[2].$v[2] : $v;// expand cf0 => ccff00
  418. $font['color'] = "FF".strtoupper($v);
  419. }
  420. if ($font!=$default_font)
  421. {
  422. $style_indexes[$i]['font_idx'] = self::add_to_list_get_index($fonts, json_encode($font) );
  423. }
  424. }
  425. return array('fills'=>$fills,'fonts'=>$fonts,'borders'=>$borders,'styles'=>$style_indexes );
  426. }
  427. protected function writeStylesXML()
  428. {
  429. $r = self::styleFontIndexes();
  430. $fills = $r['fills'];
  431. $fonts = $r['fonts'];
  432. $borders = $r['borders'];
  433. $style_indexes = $r['styles'];
  434. $temporary_filename = $this->tempFilename();
  435. $file = new XLSXWriter_BuffererWriter($temporary_filename);
  436. $file->write('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'."\n");
  437. $file->write('<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">');
  438. $file->write('<numFmts count="'.count($this->number_formats).'">');
  439. foreach($this->number_formats as $i=>$v) {
  440. $file->write('<numFmt numFmtId="'.(164+$i).'" formatCode="'.self::xmlspecialchars($v).'" />');
  441. }
  442. //$file->write( '<numFmt formatCode="GENERAL" numFmtId="164"/>');
  443. //$file->write( '<numFmt formatCode="[$$-1009]#,##0.00;[RED]\-[$$-1009]#,##0.00" numFmtId="165"/>');
  444. //$file->write( '<numFmt formatCode="YYYY-MM-DD\ HH:MM:SS" numFmtId="166"/>');
  445. //$file->write( '<numFmt formatCode="YYYY-MM-DD" numFmtId="167"/>');
  446. $file->write('</numFmts>');
  447. $file->write('<fonts count="'.(count($fonts)).'">');
  448. $file->write( '<font><name val="Arial"/><charset val="1"/><family val="2"/><sz val="10"/></font>');
  449. $file->write( '<font><name val="Arial"/><family val="0"/><sz val="10"/></font>');
  450. $file->write( '<font><name val="Arial"/><family val="0"/><sz val="10"/></font>');
  451. $file->write( '<font><name val="Arial"/><family val="0"/><sz val="10"/></font>');
  452. foreach($fonts as $font) {
  453. if (!empty($font)) { //fonts have 4 empty placeholders in array to offset the 4 static xml entries above
  454. $f = json_decode($font,true);
  455. $file->write('<font>');
  456. $file->write( '<name val="'.htmlspecialchars($f['name']).'"/><charset val="1"/><family val="'.intval($f['family']).'"/>');
  457. $file->write( '<sz val="'.intval($f['size']).'"/>');
  458. if (!empty($f['color'])) { $file->write('<color rgb="'.strval($f['color']).'"/>'); }
  459. if (!empty($f['bold'])) { $file->write('<b val="true"/>'); }
  460. if (!empty($f['italic'])) { $file->write('<i val="true"/>'); }
  461. if (!empty($f['underline'])) { $file->write('<u val="single"/>'); }
  462. if (!empty($f['strike'])) { $file->write('<strike val="true"/>'); }
  463. $file->write('</font>');
  464. }
  465. }
  466. $file->write('</fonts>');
  467. $file->write('<fills count="'.(count($fills)).'">');
  468. $file->write( '<fill><patternFill patternType="none"/></fill>');
  469. $file->write( '<fill><patternFill patternType="gray125"/></fill>');
  470. foreach($fills as $fill) {
  471. if (!empty($fill)) { //fills have 2 empty placeholders in array to offset the 2 static xml entries above
  472. $file->write('<fill><patternFill patternType="solid"><fgColor rgb="'.strval($fill).'"/><bgColor indexed="64"/></patternFill></fill>');
  473. }
  474. }
  475. $file->write('</fills>');
  476. $file->write('<borders count="'.(count($borders)).'">');
  477. $file->write( '<border diagonalDown="false" diagonalUp="false"><left/><right/><top/><bottom/><diagonal/></border>');
  478. foreach($borders as $border) {
  479. if (!empty($border)) { //fonts have an empty placeholder in the array to offset the static xml entry above
  480. $pieces = json_decode($border,true);
  481. $border_style = !empty($pieces['style']) ? $pieces['style'] : 'hair';
  482. $border_color = !empty($pieces['color']) ? '<color rgb="'.strval($pieces['color']).'"/>' : '';
  483. $file->write('<border diagonalDown="false" diagonalUp="false">');
  484. foreach (array('left', 'right', 'top', 'bottom') as $side)
  485. {
  486. $show_side = in_array($side,$pieces['side']) ? true : false;
  487. $file->write($show_side ? "<$side style=\"$border_style\">$border_color</$side>" : "<$side/>");
  488. }
  489. $file->write( '<diagonal/>');
  490. $file->write('</border>');
  491. }
  492. }
  493. $file->write('</borders>');
  494. $file->write('<cellStyleXfs count="20">');
  495. $file->write( '<xf applyAlignment="true" applyBorder="true" applyFont="true" applyProtection="true" borderId="0" fillId="0" fontId="0" numFmtId="164">');
  496. $file->write( '<alignment horizontal="general" indent="0" shrinkToFit="false" textRotation="0" vertical="bottom" wrapText="false"/>');
  497. $file->write( '<protection hidden="false" locked="true"/>');
  498. $file->write( '</xf>');
  499. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="0"/>');
  500. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="0"/>');
  501. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="2" numFmtId="0"/>');
  502. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="2" numFmtId="0"/>');
  503. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>');
  504. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>');
  505. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>');
  506. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>');
  507. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>');
  508. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>');
  509. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>');
  510. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>');
  511. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>');
  512. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>');
  513. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="43"/>');
  514. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="41"/>');
  515. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="44"/>');
  516. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="42"/>');
  517. $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="9"/>');
  518. $file->write('</cellStyleXfs>');
  519. $file->write('<cellXfs count="'.(count($style_indexes)).'">');
  520. //$file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="false" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="164" xfId="0"/>');
  521. //$file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="false" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="165" xfId="0"/>');
  522. //$file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="false" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="166" xfId="0"/>');
  523. //$file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="false" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="167" xfId="0"/>');
  524. foreach($style_indexes as $v)
  525. {
  526. $applyAlignment = isset($v['alignment']) ? 'true' : 'false';
  527. $wrapText = !empty($v['wrap_text']) ? 'true' : 'false';
  528. $horizAlignment = isset($v['halign']) ? $v['halign'] : 'general';
  529. $vertAlignment = isset($v['valign']) ? $v['valign'] : 'bottom';
  530. $applyBorder = isset($v['border_idx']) ? 'true' : 'false';
  531. $applyFont = 'true';
  532. $borderIdx = isset($v['border_idx']) ? intval($v['border_idx']) : 0;
  533. $fillIdx = isset($v['fill_idx']) ? intval($v['fill_idx']) : 0;
  534. $fontIdx = isset($v['font_idx']) ? intval($v['font_idx']) : 0;
  535. //$file->write('<xf applyAlignment="'.$applyAlignment.'" applyBorder="'.$applyBorder.'" applyFont="'.$applyFont.'" applyProtection="false" borderId="'.($borderIdx).'" fillId="'.($fillIdx).'" fontId="'.($fontIdx).'" numFmtId="'.(164+$v['num_fmt_idx']).'" xfId="0"/>');
  536. $file->write('<xf applyAlignment="'.$applyAlignment.'" applyBorder="'.$applyBorder.'" applyFont="'.$applyFont.'" applyProtection="false" borderId="'.($borderIdx).'" fillId="'.($fillIdx).'" fontId="'.($fontIdx).'" numFmtId="'.(164+$v['num_fmt_idx']).'" xfId="0">');
  537. $file->write(' <alignment horizontal="'.$horizAlignment.'" vertical="'.$vertAlignment.'" textRotation="0" wrapText="'.$wrapText.'" indent="0" shrinkToFit="false"/>');
  538. $file->write(' <protection locked="true" hidden="false"/>');
  539. $file->write('</xf>');
  540. }
  541. $file->write('</cellXfs>');
  542. $file->write( '<cellStyles count="6">');
  543. $file->write( '<cellStyle builtinId="0" customBuiltin="false" name="Normal" xfId="0"/>');
  544. $file->write( '<cellStyle builtinId="3" customBuiltin="false" name="Comma" xfId="15"/>');
  545. $file->write( '<cellStyle builtinId="6" customBuiltin="false" name="Comma [0]" xfId="16"/>');
  546. $file->write( '<cellStyle builtinId="4" customBuiltin="false" name="Currency" xfId="17"/>');
  547. $file->write( '<cellStyle builtinId="7" customBuiltin="false" name="Currency [0]" xfId="18"/>');
  548. $file->write( '<cellStyle builtinId="5" customBuiltin="false" name="Percent" xfId="19"/>');
  549. $file->write( '</cellStyles>');
  550. $file->write('</styleSheet>');
  551. $file->close();
  552. return $temporary_filename;
  553. }
  554. protected function buildAppXML()
  555. {
  556. $app_xml="";
  557. $app_xml.='<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'."\n";
  558. $app_xml.='<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">';
  559. $app_xml.='<TotalTime>0</TotalTime>';
  560. $app_xml.='<Company>'.self::xmlspecialchars($this->company).'</Company>';
  561. $app_xml.='</Properties>';
  562. return $app_xml;
  563. }
  564. protected function buildCoreXML()
  565. {
  566. $core_xml="";
  567. $core_xml.='<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'."\n";
  568. $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">';
  569. $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';
  570. $core_xml.='<dc:title>'.self::xmlspecialchars($this->title).'</dc:title>';
  571. $core_xml.='<dc:subject>'.self::xmlspecialchars($this->subject).'</dc:subject>';
  572. $core_xml.='<dc:creator>'.self::xmlspecialchars($this->author).'</dc:creator>';
  573. if (!empty($this->keywords)) {
  574. $core_xml.='<cp:keywords>'.self::xmlspecialchars(implode (", ", (array)$this->keywords)).'</cp:keywords>';
  575. }
  576. $core_xml.='<dc:description>'.self::xmlspecialchars($this->description).'</dc:description>';
  577. $core_xml.='<cp:revision>0</cp:revision>';
  578. $core_xml.='</cp:coreProperties>';
  579. return $core_xml;
  580. }
  581. protected function buildRelationshipsXML()
  582. {
  583. $rels_xml="";
  584. $rels_xml.='<?xml version="1.0" encoding="UTF-8"?>'."\n";
  585. $rels_xml.='<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">';
  586. $rels_xml.='<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>';
  587. $rels_xml.='<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>';
  588. $rels_xml.='<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>';
  589. $rels_xml.="\n";
  590. $rels_xml.='</Relationships>';
  591. return $rels_xml;
  592. }
  593. protected function buildWorkbookXML()
  594. {
  595. $i=0;
  596. $workbook_xml="";
  597. $workbook_xml.='<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'."\n";
  598. $workbook_xml.='<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">';
  599. $workbook_xml.='<fileVersion appName="Calc"/><workbookPr backupFile="false" showObjects="all" date1904="false"/><workbookProtection/>';
  600. $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>';
  601. $workbook_xml.='<sheets>';
  602. foreach($this->sheets as $sheet_name=>$sheet) {
  603. $sheetname = self::sanitize_sheetname($sheet->sheetname);
  604. $workbook_xml.='<sheet name="'.self::xmlspecialchars($sheetname).'" sheetId="'.($i+1).'" state="visible" r:id="rId'.($i+2).'"/>';
  605. $i++;
  606. }
  607. $workbook_xml.='</sheets>';
  608. $workbook_xml.='<definedNames>';
  609. foreach($this->sheets as $sheet_name=>$sheet) {
  610. if ($sheet->auto_filter) {
  611. $sheetname = self::sanitize_sheetname($sheet->sheetname);
  612. $workbook_xml.='<definedName name="_xlnm._FilterDatabase" localSheetId="0" hidden="1">\''.self::xmlspecialchars($sheetname).'\'!$A$1:' . self::xlsCell($sheet->row_count - 1, count($sheet->columns) - 1, true) . '</definedName>';
  613. $i++;
  614. }
  615. }
  616. $workbook_xml.='</definedNames>';
  617. $workbook_xml.='<calcPr iterateCount="100" refMode="A1" iterate="false" iterateDelta="0.001"/></workbook>';
  618. return $workbook_xml;
  619. }
  620. protected function buildWorkbookRelsXML()
  621. {
  622. $i=0;
  623. $wkbkrels_xml="";
  624. $wkbkrels_xml.='<?xml version="1.0" encoding="UTF-8"?>'."\n";
  625. $wkbkrels_xml.='<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">';
  626. $wkbkrels_xml.='<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>';
  627. foreach($this->sheets as $sheet_name=>$sheet) {
  628. $wkbkrels_xml.='<Relationship Id="rId'.($i+2).'" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/'.($sheet->xmlname).'"/>';
  629. $i++;
  630. }
  631. $wkbkrels_xml.="\n";
  632. $wkbkrels_xml.='</Relationships>';
  633. return $wkbkrels_xml;
  634. }
  635. protected function buildContentTypesXML()
  636. {
  637. $content_types_xml="";
  638. $content_types_xml.='<?xml version="1.0" encoding="UTF-8"?>'."\n";
  639. $content_types_xml.='<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">';
  640. $content_types_xml.='<Override PartName="/_rels/.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>';
  641. $content_types_xml.='<Override PartName="/xl/_rels/workbook.xml.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>';
  642. foreach($this->sheets as $sheet_name=>$sheet) {
  643. $content_types_xml.='<Override PartName="/xl/worksheets/'.($sheet->xmlname).'" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
  644. }
  645. $content_types_xml.='<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>';
  646. $content_types_xml.='<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>';
  647. $content_types_xml.='<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>';
  648. $content_types_xml.='<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>';
  649. $content_types_xml.="\n";
  650. $content_types_xml.='</Types>';
  651. return $content_types_xml;
  652. }
  653. //------------------------------------------------------------------
  654. /*
  655. * @param $row_number int, zero based
  656. * @param $column_number int, zero based
  657. * @param $absolute bool
  658. * @return Cell label/coordinates, ex: A1, C3, AA42 (or if $absolute==true: $A$1, $C$3, $AA$42)
  659. * */
  660. public static function xlsCell($row_number, $column_number, $absolute=false)
  661. {
  662. $n = $column_number;
  663. for($r = ""; $n >= 0; $n = intval($n / 26) - 1) {
  664. $r = chr($n%26 + 0x41) . $r;
  665. }
  666. if ($absolute) {
  667. return '$' . $r . '$' . ($row_number+1);
  668. }
  669. return $r . ($row_number+1);
  670. }
  671. //------------------------------------------------------------------
  672. public static function log($string)
  673. {
  674. //file_put_contents("php://stderr", date("Y-m-d H:i:s:").rtrim(is_array($string) ? json_encode($string) : $string)."\n");
  675. error_log(date("Y-m-d H:i:s:").rtrim(is_array($string) ? json_encode($string) : $string)."\n");
  676. }
  677. //------------------------------------------------------------------
  678. public static function sanitize_filename($filename) //http://msdn.microsoft.com/en-us/library/aa365247%28VS.85%29.aspx
  679. {
  680. $nonprinting = array_map('chr', range(0,31));
  681. $invalid_chars = array('<', '>', '?', '"', ':', '|', '\\', '/', '*', '&');
  682. $all_invalids = array_merge($nonprinting,$invalid_chars);
  683. return str_replace($all_invalids, "", $filename);
  684. }
  685. //------------------------------------------------------------------
  686. public static function sanitize_sheetname($sheetname)
  687. {
  688. static $badchars = '\\/?*:[]';
  689. static $goodchars = ' ';
  690. $sheetname = strtr($sheetname, $badchars, $goodchars);
  691. $sheetname = function_exists('mb_substr') ? mb_substr($sheetname, 0, 31) : substr($sheetname, 0, 31);
  692. $sheetname = trim(trim(trim($sheetname),"'"));//trim before and after trimming single quotes
  693. return !empty($sheetname) ? $sheetname : 'Sheet'.((rand()%900)+100);
  694. }
  695. //------------------------------------------------------------------
  696. public static function xmlspecialchars($val)
  697. {
  698. //note, badchars does not include \t\n\r (\x09\x0a\x0d)
  699. static $badchars = "\x00\x01\x02\x03\x04\x05\x06\x07\x08\x0b\x0c\x0e\x0f\x10\x11\x12\x13\x14\x15\x16\x17\x18\x19\x1a\x1b\x1c\x1d\x1e\x1f\x7f";
  700. static $goodchars = " ";
  701. return strtr(htmlspecialchars($val, ENT_QUOTES | ENT_XML1), $badchars, $goodchars);//strtr appears to be faster than str_replace
  702. }
  703. //------------------------------------------------------------------
  704. public static function array_first_key(array $arr)
  705. {
  706. reset($arr);
  707. $first_key = key($arr);
  708. return $first_key;
  709. }
  710. //------------------------------------------------------------------
  711. private static function determineNumberFormatType($num_format)
  712. {
  713. $num_format = preg_replace("/\[(Black|Blue|Cyan|Green|Magenta|Red|White|Yellow)\]/i", "", $num_format);
  714. if ($num_format=='GENERAL') return 'n_auto';
  715. if ($num_format=='@') return 'n_string';
  716. if ($num_format=='0') return 'n_numeric';
  717. if (preg_match('/[H]{1,2}:[M]{1,2}(?![^"]*+")/i', $num_format)) return 'n_datetime';
  718. if (preg_match('/[M]{1,2}:[S]{1,2}(?![^"]*+")/i', $num_format)) return 'n_datetime';
  719. if (preg_match('/[Y]{2,4}(?![^"]*+")/i', $num_format)) return 'n_date';
  720. if (preg_match('/[D]{1,2}(?![^"]*+")/i', $num_format)) return 'n_date';
  721. if (preg_match('/[M]{1,2}(?![^"]*+")/i', $num_format)) return 'n_date';
  722. if (preg_match('/$(?![^"]*+")/', $num_format)) return 'n_numeric';
  723. if (preg_match('/%(?![^"]*+")/', $num_format)) return 'n_numeric';
  724. if (preg_match('/0(?![^"]*+")/', $num_format)) return 'n_numeric';
  725. return 'n_auto';
  726. }
  727. //------------------------------------------------------------------
  728. private static function numberFormatStandardized($num_format)
  729. {
  730. if ($num_format=='money') { $num_format='dollar'; }
  731. if ($num_format=='number') { $num_format='integer'; }
  732. if ($num_format=='string') $num_format='@';
  733. else if ($num_format=='integer') $num_format='0';
  734. else if ($num_format=='date') $num_format='YYYY-MM-DD';
  735. else if ($num_format=='datetime') $num_format='YYYY-MM-DD HH:MM:SS';
  736. else if ($num_format=='time') $num_format='HH:MM:SS';
  737. else if ($num_format=='price') $num_format='#,##0.00';
  738. else if ($num_format=='dollar') $num_format='[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00';
  739. else if ($num_format=='euro') $num_format='#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]';
  740. $ignore_until='';
  741. $escaped = '';
  742. for($i=0,$ix=strlen($num_format); $i<$ix; $i++)
  743. {
  744. $c = $num_format[$i];
  745. if ($ignore_until=='' && $c=='[')
  746. $ignore_until=']';
  747. else if ($ignore_until=='' && $c=='"')
  748. $ignore_until='"';
  749. else if ($ignore_until==$c)
  750. $ignore_until='';
  751. if ($ignore_until=='' && ($c==' ' || $c=='-' || $c=='(' || $c==')') && ($i==0 || $num_format[$i-1]!='_'))
  752. $escaped.= "\\".$c;
  753. else
  754. $escaped.= $c;
  755. }
  756. return $escaped;
  757. }
  758. //------------------------------------------------------------------
  759. public static function add_to_list_get_index(&$haystack, $needle)
  760. {
  761. $existing_idx = array_search($needle, $haystack, $strict=true);
  762. if ($existing_idx===false)
  763. {
  764. $existing_idx = count($haystack);
  765. $haystack[] = $needle;
  766. }
  767. return $existing_idx;
  768. }
  769. //------------------------------------------------------------------
  770. public static function convert_date_time($date_input) //thanks to Excel::Writer::XLSX::Worksheet.pm (perl)
  771. {
  772. $days = 0; # Number of days since epoch
  773. $seconds = 0; # Time expressed as fraction of 24h hours in seconds
  774. $year=$month=$day=0;
  775. $hour=$min =$sec=0;
  776. $date_time = $date_input;
  777. if (preg_match("/(\d{4})\-(\d{2})\-(\d{2})/", $date_time, $matches))
  778. {
  779. list($junk,$year,$month,$day) = $matches;
  780. }
  781. if (preg_match("/(\d+):(\d{2}):(\d{2})/", $date_time, $matches))
  782. {
  783. list($junk,$hour,$min,$sec) = $matches;
  784. $seconds = ( $hour * 60 * 60 + $min * 60 + $sec ) / ( 24 * 60 * 60 );
  785. }
  786. //using 1900 as epoch, not 1904, ignoring 1904 special case
  787. # Special cases for Excel.
  788. if ("$year-$month-$day"=='1899-12-31') return $seconds ; # Excel 1900 epoch
  789. if ("$year-$month-$day"=='1900-01-00') return $seconds ; # Excel 1900 epoch
  790. if ("$year-$month-$day"=='1900-02-29') return 60 + $seconds ; # Excel false leapday
  791. # We calculate the date by calculating the number of days since the epoch
  792. # and adjust for the number of leap days. We calculate the number of leap
  793. # days by normalising the year in relation to the epoch. Thus the year 2000
  794. # becomes 100 for 4 and 100 year leapdays and 400 for 400 year leapdays.
  795. $epoch = 1900;
  796. $offset = 0;
  797. $norm = 300;
  798. $range = $year - $epoch;
  799. # Set month days and check for leap year.
  800. $leap = (($year % 400 == 0) || (($year % 4 == 0) && ($year % 100)) ) ? 1 : 0;
  801. $mdays = array( 31, ($leap ? 29 : 28), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 );
  802. # Some boundary checks
  803. if ($year!=0 || $month !=0 || $day!=0)
  804. {
  805. if($year < $epoch || $year > 9999) return 0;
  806. if($month < 1 || $month > 12) return 0;
  807. if($day < 1 || $day > $mdays[ $month - 1 ]) return 0;
  808. }
  809. # Accumulate the number of days since the epoch.
  810. $days = $day; # Add days for current month
  811. $days += array_sum( array_slice($mdays, 0, $month-1 ) ); # Add days for past months
  812. $days += $range * 365; # Add days for past years
  813. $days += intval( ( $range ) / 4 ); # Add leapdays
  814. $days -= intval( ( $range + $offset ) / 100 ); # Subtract 100 year leapdays
  815. $days += intval( ( $range + $offset + $norm ) / 400 ); # Add 400 year leapdays
  816. $days -= $leap; # Already counted above
  817. # Adjust for Excel erroneously treating 1900 as a leap year.
  818. if ($days > 59) { $days++;}
  819. return $days + $seconds;
  820. }
  821. //------------------------------------------------------------------
  822. }
  823. class XLSXWriter_BuffererWriter
  824. {
  825. protected $fd=null;
  826. protected $buffer='';
  827. protected $check_utf8=false;
  828. public function __construct($filename, $fd_fopen_flags='w', $check_utf8=false)
  829. {
  830. $this->check_utf8 = $check_utf8;
  831. $this->fd = fopen($filename, $fd_fopen_flags);
  832. if ($this->fd===false) {
  833. XLSXWriter::log("Unable to open $filename for writing.");
  834. }
  835. }
  836. public function write($string)
  837. {
  838. $this->buffer.=$string;
  839. if (isset($this->buffer[8191])) {
  840. $this->purge();
  841. }
  842. }
  843. protected function purge()
  844. {
  845. if ($this->fd) {
  846. if ($this->check_utf8 && !self::isValidUTF8($this->buffer)) {
  847. XLSXWriter::log("Error, invalid UTF8 encoding detected.");
  848. $this->check_utf8 = false;
  849. }
  850. fwrite($this->fd, $this->buffer);
  851. $this->buffer='';
  852. }
  853. }
  854. public function close()
  855. {
  856. $this->purge();
  857. if ($this->fd) {
  858. fclose($this->fd);
  859. $this->fd=null;
  860. }
  861. }
  862. public function __destruct()
  863. {
  864. $this->close();
  865. }
  866. public function ftell()
  867. {
  868. if ($this->fd) {
  869. $this->purge();
  870. return ftell($this->fd);
  871. }
  872. return -1;
  873. }
  874. public function fseek($pos)
  875. {
  876. if ($this->fd) {
  877. $this->purge();
  878. return fseek($this->fd, $pos);
  879. }
  880. return -1;
  881. }
  882. protected static function isValidUTF8($string)
  883. {
  884. if (function_exists('mb_check_encoding'))
  885. {
  886. return mb_check_encoding($string, 'UTF-8') ? true : false;
  887. }
  888. return preg_match("//u", $string) ? true : false;
  889. }
  890. }