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