addCellStyle($number_format='GENERAL', $style_string=null); $this->addCellStyle($number_format='GENERAL', $style_string=null); $this->addCellStyle($number_format='GENERAL', $style_string=null); $this->addCellStyle($number_format='GENERAL', $style_string=null); } public function setTitle($title='') { $this->title=$title; } public function setSubject($subject='') { $this->subject=$subject; } public function setAuthor($author='') { $this->author=$author; } public function setCompany($company='') { $this->company=$company; } public function setKeywords($keywords='') { $this->keywords=$keywords; } public function setDescription($description='') { $this->description=$description; } public function setTempDir($tempdir='') { $this->tempdir=$tempdir; } public function __destruct() { if (!empty($this->temp_files)) { foreach($this->temp_files as $temp_file) { @unlink($temp_file); } } } protected function tempFilename() { $tempdir = !empty($this->tempdir) ? $this->tempdir : sys_get_temp_dir(); $filename = tempnam($tempdir, "xlsx_writer_"); $this->temp_files[] = $filename; return $filename; } public function writeToStdOut() { $temp_file = $this->tempFilename(); self::writeToFile($temp_file); readfile($temp_file); } public function writeToString() { $temp_file = $this->tempFilename(); self::writeToFile($temp_file); $string = file_get_contents($temp_file); return $string; } public function writeToFile($filename) { foreach($this->sheets as $sheet_name => $sheet) { self::finalizeSheet($sheet_name);//making sure all footers have been written } if ( file_exists( $filename ) ) { if ( is_writable( $filename ) ) { @unlink( $filename ); //if the zip already exists, remove it } else { self::log( "Error in " . __CLASS__ . "::" . __FUNCTION__ . ", file is not writeable." ); return; } } $zip = new ZipArchive(); if (empty($this->sheets)) { self::log("Error in ".__CLASS__."::".__FUNCTION__.", no worksheets defined."); return; } if (!$zip->open($filename, ZipArchive::CREATE)) { self::log("Error in ".__CLASS__."::".__FUNCTION__.", unable to create zip."); return; } $zip->addEmptyDir("docProps/"); $zip->addFromString("docProps/app.xml" , self::buildAppXML() ); $zip->addFromString("docProps/core.xml", self::buildCoreXML()); $zip->addEmptyDir("_rels/"); $zip->addFromString("_rels/.rels", self::buildRelationshipsXML()); $zip->addEmptyDir("xl/worksheets/"); foreach($this->sheets as $sheet) { $zip->addFile($sheet->filename, "xl/worksheets/".$sheet->xmlname ); } $zip->addFromString("xl/workbook.xml" , self::buildWorkbookXML() ); $zip->addFile($this->writeStylesXML(), "xl/styles.xml" ); //$zip->addFromString("xl/styles.xml" , self::buildStylesXML() ); $zip->addFromString("[Content_Types].xml" , self::buildContentTypesXML() ); $zip->addEmptyDir("xl/_rels/"); $zip->addFromString("xl/_rels/workbook.xml.rels", self::buildWorkbookRelsXML() ); $zip->close(); } protected function initializeSheet($sheet_name, $col_widths=array(), $auto_filter=false, $freeze_rows=false, $freeze_columns=false ) { //if already initialized if ($this->current_sheet==$sheet_name || isset($this->sheets[$sheet_name])) return; $sheet_filename = $this->tempFilename(); $sheet_xmlname = 'sheet' . (count($this->sheets) + 1).".xml"; $this->sheets[$sheet_name] = (object)array( 'filename' => $sheet_filename, 'sheetname' => $sheet_name, 'xmlname' => $sheet_xmlname, 'row_count' => 0, 'file_writer' => new XLSXWriter_BuffererWriter($sheet_filename), 'columns' => array(), 'merge_cells' => array(), 'max_cell_tag_start' => 0, 'max_cell_tag_end' => 0, 'auto_filter' => $auto_filter, 'freeze_rows' => $freeze_rows, 'freeze_columns' => $freeze_columns, 'finalized' => false, ); $sheet = &$this->sheets[$sheet_name]; $tabselected = count($this->sheets) == 1 ? 'true' : 'false';//only first sheet is selected $max_cell=XLSXWriter::xlsCell(self::EXCEL_2007_MAX_ROW, self::EXCEL_2007_MAX_COL);//XFE1048577 $sheet->file_writer->write('' . "\n"); $sheet->file_writer->write(''); $sheet->file_writer->write( ''); $sheet->file_writer->write( ''); $sheet->file_writer->write( ''); $sheet->max_cell_tag_start = $sheet->file_writer->ftell(); $sheet->file_writer->write(''); $sheet->max_cell_tag_end = $sheet->file_writer->ftell(); $sheet->file_writer->write( ''); $sheet->file_writer->write( ''); if ($sheet->freeze_rows && $sheet->freeze_columns) { $sheet->file_writer->write( ''); $sheet->file_writer->write( ''); $sheet->file_writer->write( ''); $sheet->file_writer->write( ''); } elseif ($sheet->freeze_rows) { $sheet->file_writer->write( ''); $sheet->file_writer->write( ''); } elseif ($sheet->freeze_columns) { $sheet->file_writer->write( ''); $sheet->file_writer->write( ''); } else { // not frozen $sheet->file_writer->write( ''); } $sheet->file_writer->write( ''); $sheet->file_writer->write( ''); $sheet->file_writer->write( ''); $i=0; if (!empty($col_widths)) { foreach($col_widths as $column_width) { $sheet->file_writer->write( ''); $i++; } } $sheet->file_writer->write( ''); $sheet->file_writer->write( ''); $sheet->file_writer->write( ''); } private function addCellStyle($number_format, $cell_style_string) { $number_format_idx = self::add_to_list_get_index($this->number_formats, $number_format); $lookup_string = $number_format_idx.";".$cell_style_string; $cell_style_idx = self::add_to_list_get_index($this->cell_styles, $lookup_string); return $cell_style_idx; } private function initializeColumnTypes($header_types) { $column_types = array(); foreach($header_types as $v) { $number_format = self::numberFormatStandardized($v); $number_format_type = self::determineNumberFormatType($number_format); $cell_style_idx = $this->addCellStyle($number_format, $style_string=null); $column_types[] = array('number_format' => $number_format,//contains excel format like 'YYYY-MM-DD HH:MM:SS' 'number_format_type' => $number_format_type, //contains friendly format like 'datetime' 'default_cell_style' => $cell_style_idx, ); } return $column_types; } public function writeSheetHeader($sheet_name, array $header_types, $col_options = null) { if (empty($sheet_name) || empty($header_types) || !empty($this->sheets[$sheet_name])) return; $suppress_row = isset($col_options['suppress_row']) ? intval($col_options['suppress_row']) : false; if (is_bool($col_options)) { self::log( "Warning! passing $suppress_row=false|true to writeSheetHeader() is deprecated, this will be removed in a future version." ); $suppress_row = intval($col_options); } $style = &$col_options; $col_widths = isset($col_options['widths']) ? (array)$col_options['widths'] : array(); $auto_filter = isset($col_options['auto_filter']) ? intval($col_options['auto_filter']) : false; $freeze_rows = isset($col_options['freeze_rows']) ? intval($col_options['freeze_rows']) : false; $freeze_columns = isset($col_options['freeze_columns']) ? intval($col_options['freeze_columns']) : false; self::initializeSheet($sheet_name, $col_widths, $auto_filter, $freeze_rows, $freeze_columns); $sheet = &$this->sheets[$sheet_name]; $sheet->columns = $this->initializeColumnTypes($header_types); if (!$suppress_row) { $header_row = array_keys($header_types); $sheet->file_writer->write(''); $sheet->row_count++; } $this->current_sheet = $sheet_name; } public function writeSheetRow($sheet_name, array $row, $row_options=null) { if (empty($sheet_name)) return; self::initializeSheet($sheet_name); $sheet = &$this->sheets[$sheet_name]; if (count($sheet->columns) < count($row)) { $default_column_types = $this->initializeColumnTypes( array_fill($from=0, $until=count($row), 'GENERAL') );//will map to n_auto $sheet->columns = array_merge((array)$sheet->columns, $default_column_types); } if (!empty($row_options)) { $ht = isset($row_options['height']) ? floatval($row_options['height']) : 12.1; $customHt = isset($row_options['height']) ? true : false; $hidden = isset($row_options['hidden']) ? (bool)($row_options['hidden']) : false; $collapsed = isset($row_options['collapsed']) ? (bool)($row_options['collapsed']) : false; $sheet->file_writer->write(''); if (!empty($sheet->merge_cells)) { $sheet->file_writer->write( ''); foreach ($sheet->merge_cells as $range) { $sheet->file_writer->write( ''); } $sheet->file_writer->write( ''); } $max_cell = self::xlsCell($sheet->row_count - 1, count($sheet->columns) - 1); if ($sheet->auto_filter) { $sheet->file_writer->write( ''); } $sheet->file_writer->write( ''); $sheet->file_writer->write( ''); $sheet->file_writer->write( ''); $sheet->file_writer->write( ''); $sheet->file_writer->write( '&C&"Times New Roman,Regular"&12&A'); $sheet->file_writer->write( '&C&"Times New Roman,Regular"&12Page &P'); $sheet->file_writer->write( ''); $sheet->file_writer->write(''); $max_cell_tag = ''; $padding_length = $sheet->max_cell_tag_end - $sheet->max_cell_tag_start - strlen($max_cell_tag); $sheet->file_writer->fseek($sheet->max_cell_tag_start); $sheet->file_writer->write($max_cell_tag.str_repeat(" ", $padding_length)); $sheet->file_writer->close(); $sheet->finalized=true; } public function markMergedCell($sheet_name, $start_cell_row, $start_cell_column, $end_cell_row, $end_cell_column) { if (empty($sheet_name) || $this->sheets[$sheet_name]->finalized) return; self::initializeSheet($sheet_name); $sheet = &$this->sheets[$sheet_name]; $startCell = self::xlsCell($start_cell_row, $start_cell_column); $endCell = self::xlsCell($end_cell_row, $end_cell_column); $sheet->merge_cells[] = $startCell . ":" . $endCell; } public function writeSheet(array $data, $sheet_name='', array $header_types=array(), $style=array()) { $sheet_name = empty($sheet_name) ? 'Sheet1' : $sheet_name; $data = empty($data) ? array(array('')) : $data; if (!empty($header_types)) isset($style['header']) ? $this->writeSheetHeader($sheet_name, $header_types, $style['header']) : $this->writeSheetHeader($sheet_name, $header_types); foreach($data as $i=>$row) isset($style['content']) ? $this->writeSheetRow($sheet_name, $row, $style['content']) : $this->writeSheetRow($sheet_name, $row); $this->finalizeSheet($sheet_name); } protected function writeCell(XLSXWriter_BuffererWriter &$file, $row_number, $column_number, $value, $num_format_type, $cell_style_idx) { $cell_name = self::xlsCell($row_number, $column_number); if (!is_scalar($value) || $value==='') { //objects, array, empty $file->write(''); } elseif (is_string($value) && $value{0}=='='){ $file->write(''.self::xmlspecialchars($value).''); } elseif ($num_format_type=='n_date') { $file->write(''.intval(self::convert_date_time($value)).''); } elseif ($num_format_type=='n_datetime') { $file->write(''.self::convert_date_time($value).''); } elseif ($num_format_type=='n_numeric') { $file->write(''.self::xmlspecialchars($value).'');//int,float,currency } elseif ($num_format_type=='n_string') { $file->write(''.self::xmlspecialchars($value).''); } elseif ($num_format_type=='n_auto' || 1) { //auto-detect unknown column types if (!is_string($value) || $value=='0' || ($value[0]!='0' && ctype_digit($value)) || preg_match("/^\-?(0|[1-9][0-9]*)(\.[0-9]+)?$/", $value)){ $file->write(''.self::xmlspecialchars($value).'');//int,float,currency } else { //implied: ($cell_format=='string') $file->write(''.self::xmlspecialchars($value).''); } } } protected function styleFontIndexes() { static $border_allowed = array('left','right','top','bottom'); static $border_style_allowed = array('thin','medium','thick','dashDot','dashDotDot','dashed','dotted','double','hair','mediumDashDot','mediumDashDotDot','mediumDashed','slantDashDot'); static $horizontal_allowed = array('general','left','right','justify','center'); static $vertical_allowed = array('bottom','center','distributed','top'); $default_font = array('size'=>'10','name'=>'Arial','family'=>'2'); $fills = array('','');//2 placeholders for static xml later $fonts = array('','','','');//4 placeholders for static xml later $borders = array('');//1 placeholder for static xml later $style_indexes = array(); foreach($this->cell_styles as $i=>$cell_style_string) { $semi_colon_pos = strpos($cell_style_string,";"); $number_format_idx = substr($cell_style_string, 0, $semi_colon_pos); $style_json_string = substr($cell_style_string, $semi_colon_pos+1); $style = @json_decode($style_json_string, $as_assoc=true); $style_indexes[$i] = array('num_fmt_idx'=>$number_format_idx);//initialize entry if (isset($style['border']) && is_string($style['border']))//border is a comma delimited str { $border_value['side'] = array_intersect(explode(",", $style['border']), $border_allowed); if (isset($style['border-style']) && in_array($style['border-style'],$border_style_allowed)) { $border_value['style'] = $style['border-style']; } if (isset($style['border-color']) && is_string($style['border-color']) && $style['border-color'][0]=='#') { $v = substr($style['border-color'],1,6); $v = strlen($v)==3 ? $v[0].$v[0].$v[1].$v[1].$v[2].$v[2] : $v;// expand cf0 => ccff00 $border_value['color'] = "FF".strtoupper($v); } $style_indexes[$i]['border_idx'] = self::add_to_list_get_index($borders, json_encode($border_value)); } if (isset($style['fill']) && is_string($style['fill']) && $style['fill'][0]=='#') { $v = substr($style['fill'],1,6); $v = strlen($v)==3 ? $v[0].$v[0].$v[1].$v[1].$v[2].$v[2] : $v;// expand cf0 => ccff00 $style_indexes[$i]['fill_idx'] = self::add_to_list_get_index($fills, "FF".strtoupper($v) ); } if (isset($style['halign']) && in_array($style['halign'],$horizontal_allowed)) { $style_indexes[$i]['alignment'] = true; $style_indexes[$i]['halign'] = $style['halign']; } if (isset($style['valign']) && in_array($style['valign'],$vertical_allowed)) { $style_indexes[$i]['alignment'] = true; $style_indexes[$i]['valign'] = $style['valign']; } if (isset($style['wrap_text'])) { $style_indexes[$i]['alignment'] = true; $style_indexes[$i]['wrap_text'] = (bool)$style['wrap_text']; } $font = $default_font; if (isset($style['font-size'])) { $font['size'] = floatval($style['font-size']);//floatval to allow "10.5" etc } if (isset($style['font']) && is_string($style['font'])) { if ($style['font']=='Comic Sans MS') { $font['family']=4; } if ($style['font']=='Times New Roman') { $font['family']=1; } if ($style['font']=='Courier New') { $font['family']=3; } $font['name'] = strval($style['font']); } if (isset($style['font-style']) && is_string($style['font-style'])) { if (strpos($style['font-style'], 'bold')!==false) { $font['bold'] = true; } if (strpos($style['font-style'], 'italic')!==false) { $font['italic'] = true; } if (strpos($style['font-style'], 'strike')!==false) { $font['strike'] = true; } if (strpos($style['font-style'], 'underline')!==false) { $font['underline'] = true; } } if (isset($style['color']) && is_string($style['color']) && $style['color'][0]=='#' ) { $v = substr($style['color'],1,6); $v = strlen($v)==3 ? $v[0].$v[0].$v[1].$v[1].$v[2].$v[2] : $v;// expand cf0 => ccff00 $font['color'] = "FF".strtoupper($v); } if ($font!=$default_font) { $style_indexes[$i]['font_idx'] = self::add_to_list_get_index($fonts, json_encode($font) ); } } return array('fills'=>$fills,'fonts'=>$fonts,'borders'=>$borders,'styles'=>$style_indexes ); } protected function writeStylesXML() { $r = self::styleFontIndexes(); $fills = $r['fills']; $fonts = $r['fonts']; $borders = $r['borders']; $style_indexes = $r['styles']; $temporary_filename = $this->tempFilename(); $file = new XLSXWriter_BuffererWriter($temporary_filename); $file->write(''."\n"); $file->write(''); $file->write(''); foreach($this->number_formats as $i=>$v) { $file->write(''); } //$file->write( ''); //$file->write( ''); //$file->write( ''); //$file->write( ''); $file->write(''); $file->write(''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); foreach($fonts as $font) { if (!empty($font)) { //fonts have 4 empty placeholders in array to offset the 4 static xml entries above $f = json_decode($font,true); $file->write(''); $file->write( ''); $file->write( ''); if (!empty($f['color'])) { $file->write(''); } if (!empty($f['bold'])) { $file->write(''); } if (!empty($f['italic'])) { $file->write(''); } if (!empty($f['underline'])) { $file->write(''); } if (!empty($f['strike'])) { $file->write(''); } $file->write(''); } } $file->write(''); $file->write(''); $file->write( ''); $file->write( ''); foreach($fills as $fill) { if (!empty($fill)) { //fills have 2 empty placeholders in array to offset the 2 static xml entries above $file->write(''); } } $file->write(''); $file->write(''); $file->write( ''); foreach($borders as $border) { if (!empty($border)) { //fonts have an empty placeholder in the array to offset the static xml entry above $pieces = json_decode($border,true); $border_style = !empty($pieces['style']) ? $pieces['style'] : 'hair'; $border_color = !empty($pieces['color']) ? '' : ''; $file->write(''); foreach (array('left', 'right', 'top', 'bottom') as $side) { $show_side = in_array($side,$pieces['side']) ? true : false; $file->write($show_side ? "<$side style=\"$border_style\">$border_color" : "<$side/>"); } $file->write( ''); $file->write(''); } } $file->write(''); $file->write(''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write(''); $file->write(''); //$file->write( ''); //$file->write( ''); //$file->write( ''); //$file->write( ''); foreach($style_indexes as $v) { $applyAlignment = isset($v['alignment']) ? 'true' : 'false'; $wrapText = !empty($v['wrap_text']) ? 'true' : 'false'; $horizAlignment = isset($v['halign']) ? $v['halign'] : 'general'; $vertAlignment = isset($v['valign']) ? $v['valign'] : 'bottom'; $applyBorder = isset($v['border_idx']) ? 'true' : 'false'; $applyFont = 'true'; $borderIdx = isset($v['border_idx']) ? intval($v['border_idx']) : 0; $fillIdx = isset($v['fill_idx']) ? intval($v['fill_idx']) : 0; $fontIdx = isset($v['font_idx']) ? intval($v['font_idx']) : 0; //$file->write(''); $file->write(''); $file->write(' '); $file->write(' '); } $file->write(''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write( ''); $file->write(''); $file->close(); return $temporary_filename; } protected function buildAppXML() { $app_xml=""; $app_xml.=''."\n"; $app_xml.=''; $app_xml.='0'; $app_xml.=''.self::xmlspecialchars($this->company).''; $app_xml.=''; return $app_xml; } protected function buildCoreXML() { $core_xml=""; $core_xml.=''."\n"; $core_xml.=''; $core_xml.=''.date("Y-m-d\TH:i:s.00\Z").'';//$date_time = '2014-10-25T15:54:37.00Z'; $core_xml.=''.self::xmlspecialchars($this->title).''; $core_xml.=''.self::xmlspecialchars($this->subject).''; $core_xml.=''.self::xmlspecialchars($this->author).''; if (!empty($this->keywords)) { $core_xml.=''.self::xmlspecialchars(implode (", ", (array)$this->keywords)).''; } $core_xml.=''.self::xmlspecialchars($this->description).''; $core_xml.='0'; $core_xml.=''; return $core_xml; } protected function buildRelationshipsXML() { $rels_xml=""; $rels_xml.=''."\n"; $rels_xml.=''; $rels_xml.=''; $rels_xml.=''; $rels_xml.=''; $rels_xml.="\n"; $rels_xml.=''; return $rels_xml; } protected function buildWorkbookXML() { $i=0; $workbook_xml=""; $workbook_xml.=''."\n"; $workbook_xml.=''; $workbook_xml.=''; $workbook_xml.=''; $workbook_xml.=''; foreach($this->sheets as $sheet_name=>$sheet) { $sheetname = self::sanitize_sheetname($sheet->sheetname); $workbook_xml.=''; $i++; } $workbook_xml.=''; $workbook_xml.=''; foreach($this->sheets as $sheet_name=>$sheet) { if ($sheet->auto_filter) { $sheetname = self::sanitize_sheetname($sheet->sheetname); $workbook_xml.=''; $i++; } } $workbook_xml.=''; $workbook_xml.=''; return $workbook_xml; } protected function buildWorkbookRelsXML() { $i=0; $wkbkrels_xml=""; $wkbkrels_xml.=''."\n"; $wkbkrels_xml.=''; $wkbkrels_xml.=''; foreach($this->sheets as $sheet_name=>$sheet) { $wkbkrels_xml.=''; $i++; } $wkbkrels_xml.="\n"; $wkbkrels_xml.=''; return $wkbkrels_xml; } protected function buildContentTypesXML() { $content_types_xml=""; $content_types_xml.=''."\n"; $content_types_xml.=''; $content_types_xml.=''; $content_types_xml.=''; foreach($this->sheets as $sheet_name=>$sheet) { $content_types_xml.=''; } $content_types_xml.=''; $content_types_xml.=''; $content_types_xml.=''; $content_types_xml.=''; $content_types_xml.="\n"; $content_types_xml.=''; return $content_types_xml; } //------------------------------------------------------------------ /* * @param $row_number int, zero based * @param $column_number int, zero based * @param $absolute bool * @return Cell label/coordinates, ex: A1, C3, AA42 (or if $absolute==true: $A$1, $C$3, $AA$42) * */ public static function xlsCell($row_number, $column_number, $absolute=false) { $n = $column_number; for($r = ""; $n >= 0; $n = intval($n / 26) - 1) { $r = chr($n%26 + 0x41) . $r; } if ($absolute) { return '$' . $r . '$' . ($row_number+1); } return $r . ($row_number+1); } //------------------------------------------------------------------ public static function log($string) { file_put_contents("php://stderr", date("Y-m-d H:i:s:").rtrim(is_array($string) ? json_encode($string) : $string)."\n"); } //------------------------------------------------------------------ public static function sanitize_filename($filename) //http://msdn.microsoft.com/en-us/library/aa365247%28VS.85%29.aspx { $nonprinting = array_map('chr', range(0,31)); $invalid_chars = array('<', '>', '?', '"', ':', '|', '\\', '/', '*', '&'); $all_invalids = array_merge($nonprinting,$invalid_chars); return str_replace($all_invalids, "", $filename); } //------------------------------------------------------------------ public static function sanitize_sheetname($sheetname) { static $badchars = '\\/?*:[]'; static $goodchars = ' '; $sheetname = strtr($sheetname, $badchars, $goodchars); $sheetname = substr($sheetname, 0, 31); $sheetname = trim(trim(trim($sheetname),"'"));//trim before and after trimming single quotes return !empty($sheetname) ? $sheetname : 'Sheet'.((rand()%900)+100); } //------------------------------------------------------------------ public static function xmlspecialchars($val) { //note, badchars does not include \t\n\r (\x09\x0a\x0d) 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"; static $goodchars = " "; return strtr(htmlspecialchars($val, ENT_QUOTES | ENT_XML1), $badchars, $goodchars);//strtr appears to be faster than str_replace } //------------------------------------------------------------------ public static function array_first_key(array $arr) { reset($arr); $first_key = key($arr); return $first_key; } //------------------------------------------------------------------ private static function determineNumberFormatType($num_format) { $num_format = preg_replace("/\[(Black|Blue|Cyan|Green|Magenta|Red|White|Yellow)\]/i", "", $num_format); if ($num_format=='GENERAL') return 'n_auto'; if ($num_format=='@') return 'n_string'; if ($num_format=='0') return 'n_numeric'; if (preg_match("/[H]{1,2}:[M]{1,2}/i", $num_format)) return 'n_datetime'; if (preg_match("/[M]{1,2}:[S]{1,2}/i", $num_format)) return 'n_datetime'; if (preg_match("/[Y]{2,4}/i", $num_format)) return 'n_date'; if (preg_match("/[D]{1,2}/i", $num_format)) return 'n_date'; if (preg_match("/[M]{1,2}/i", $num_format)) return 'n_date'; if (preg_match("/$/", $num_format)) return 'n_numeric'; if (preg_match("/%/", $num_format)) return 'n_numeric'; if (preg_match("/0/", $num_format)) return 'n_numeric'; return 'n_auto'; } //------------------------------------------------------------------ private static function numberFormatStandardized($num_format) { if ($num_format=='money') { $num_format='dollar'; } if ($num_format=='number') { $num_format='integer'; } if ($num_format=='string') $num_format='@'; else if ($num_format=='integer') $num_format='0'; else if ($num_format=='date') $num_format='YYYY-MM-DD'; else if ($num_format=='datetime') $num_format='YYYY-MM-DD HH:MM:SS'; else if ($num_format=='price') $num_format='#,##0.00'; else if ($num_format=='dollar') $num_format='[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00'; else if ($num_format=='euro') $num_format='#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]'; $ignore_until=''; $escaped = ''; for($i=0,$ix=strlen($num_format); $i<$ix; $i++) { $c = $num_format[$i]; if ($ignore_until=='' && $c=='[') $ignore_until=']'; else if ($ignore_until=='' && $c=='"') $ignore_until='"'; else if ($ignore_until==$c) $ignore_until=''; if ($ignore_until=='' && ($c==' ' || $c=='-' || $c=='(' || $c==')') && ($i==0 || $num_format[$i-1]!='_')) $escaped.= "\\".$c; else $escaped.= $c; } return $escaped; } //------------------------------------------------------------------ public static function add_to_list_get_index(&$haystack, $needle) { $existing_idx = array_search($needle, $haystack, $strict=true); if ($existing_idx===false) { $existing_idx = count($haystack); $haystack[] = $needle; } return $existing_idx; } //------------------------------------------------------------------ public static function convert_date_time($date_input) //thanks to Excel::Writer::XLSX::Worksheet.pm (perl) { $days = 0; # Number of days since epoch $seconds = 0; # Time expressed as fraction of 24h hours in seconds $year=$month=$day=0; $hour=$min =$sec=0; $date_time = $date_input; if (preg_match("/(\d{4})\-(\d{2})\-(\d{2})/", $date_time, $matches)) { list($junk,$year,$month,$day) = $matches; } if (preg_match("/(\d+):(\d{2}):(\d{2})/", $date_time, $matches)) { list($junk,$hour,$min,$sec) = $matches; $seconds = ( $hour * 60 * 60 + $min * 60 + $sec ) / ( 24 * 60 * 60 ); } //using 1900 as epoch, not 1904, ignoring 1904 special case # Special cases for Excel. if ("$year-$month-$day"=='1899-12-31') return $seconds ; # Excel 1900 epoch if ("$year-$month-$day"=='1900-01-00') return $seconds ; # Excel 1900 epoch if ("$year-$month-$day"=='1900-02-29') return 60 + $seconds ; # Excel false leapday # We calculate the date by calculating the number of days since the epoch # and adjust for the number of leap days. We calculate the number of leap # days by normalising the year in relation to the epoch. Thus the year 2000 # becomes 100 for 4 and 100 year leapdays and 400 for 400 year leapdays. $epoch = 1900; $offset = 0; $norm = 300; $range = $year - $epoch; # Set month days and check for leap year. $leap = (($year % 400 == 0) || (($year % 4 == 0) && ($year % 100)) ) ? 1 : 0; $mdays = array( 31, ($leap ? 29 : 28), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 ); # Some boundary checks if($year < $epoch || $year > 9999) return 0; if($month < 1 || $month > 12) return 0; if($day < 1 || $day > $mdays[ $month - 1 ]) return 0; # Accumulate the number of days since the epoch. $days = $day; # Add days for current month $days += array_sum( array_slice($mdays, 0, $month-1 ) ); # Add days for past months $days += $range * 365; # Add days for past years $days += intval( ( $range ) / 4 ); # Add leapdays $days -= intval( ( $range + $offset ) / 100 ); # Subtract 100 year leapdays $days += intval( ( $range + $offset + $norm ) / 400 ); # Add 400 year leapdays $days -= $leap; # Already counted above # Adjust for Excel erroneously treating 1900 as a leap year. if ($days > 59) { $days++;} return $days + $seconds; } //------------------------------------------------------------------ } class XLSXWriter_BuffererWriter { protected $fd=null; protected $buffer=''; protected $check_utf8=false; public function __construct($filename, $fd_fopen_flags='w', $check_utf8=false) { $this->check_utf8 = $check_utf8; $this->fd = fopen($filename, $fd_fopen_flags); if ($this->fd===false) { XLSXWriter::log("Unable to open $filename for writing."); } } public function write($string) { $this->buffer.=$string; if (isset($this->buffer[8191])) { $this->purge(); } } protected function purge() { if ($this->fd) { if ($this->check_utf8 && !self::isValidUTF8($this->buffer)) { XLSXWriter::log("Error, invalid UTF8 encoding detected."); $this->check_utf8 = false; } fwrite($this->fd, $this->buffer); $this->buffer=''; } } public function close() { $this->purge(); if ($this->fd) { fclose($this->fd); $this->fd=null; } } public function __destruct() { $this->close(); } public function ftell() { if ($this->fd) { $this->purge(); return ftell($this->fd); } return -1; } public function fseek($pos) { if ($this->fd) { $this->purge(); return fseek($this->fd, $pos); } return -1; } protected static function isValidUTF8($string) { if (function_exists('mb_check_encoding')) { return mb_check_encoding($string, 'UTF-8') ? true : false; } return preg_match("//u", $string) ? true : false; } }