Idiorm
3 years ago
Paris
3 years ago
Sudzy
3 years ago
pquery
3 years ago
CSS.php
3 years ago
XLSXWriter.php
3 years ago
index.php
3 years ago
XLSXWriter.php
588 lines
| 1 | <?php // phpcs:ignore SlevomatCodingStandard.TypeHints.DeclareStrictTypes.DeclareStrictTypesMissing |
| 2 | |
| 3 | namespace MailPoetVendor; |
| 4 | |
| 5 | if (!defined('ABSPATH')) exit; |
| 6 | |
| 7 | |
| 8 | /* |
| 9 | * @license MIT License |
| 10 | * */ |
| 11 | |
| 12 | if (!class_exists('ZipArchive')) { throw new \Exception('ZipArchive not found'); |
| 13 | } |
| 14 | |
| 15 | class XLSXWriter |
| 16 | { |
| 17 | //------------------------------------------------------------------ |
| 18 | //http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx |
| 19 | const EXCEL_2007_MAX_ROW = 1048576; |
| 20 | const EXCEL_2007_MAX_COL = 16384; |
| 21 | //------------------------------------------------------------------ |
| 22 | protected $author = 'MailPoet'; |
| 23 | protected $sheets = []; |
| 24 | protected $shared_strings = [];//unique set |
| 25 | protected $shared_string_count = 0;//count of non-unique references to the unique set |
| 26 | protected $temp_files = []; |
| 27 | protected $current_sheet = ''; |
| 28 | public $rtl = false; |
| 29 | |
| 30 | public function __construct() { |
| 31 | if (!ini_get('date.timezone')) |
| 32 | { |
| 33 | //using date functions can kick out warning if this isn't set |
| 34 | date_default_timezone_set('UTC'); |
| 35 | } |
| 36 | } |
| 37 | |
| 38 | public function setAuthor($author='') { |
| 39 | $this->author = $author; |
| 40 | } |
| 41 | |
| 42 | public function __destruct() { |
| 43 | if (!empty($this->temp_files)) { |
| 44 | foreach ($this->temp_files as $temp_file) { |
| 45 | @unlink($temp_file); |
| 46 | } |
| 47 | } |
| 48 | } |
| 49 | |
| 50 | protected function tempFilename() { |
| 51 | $filename = tempnam(sys_get_temp_dir(), "xlsx_writer_"); |
| 52 | $this->temp_files[] = $filename; |
| 53 | return $filename; |
| 54 | } |
| 55 | |
| 56 | public function writeToStdOut() { |
| 57 | $temp_file = $this->tempFilename(); |
| 58 | self::writeToFile($temp_file); |
| 59 | readfile($temp_file); |
| 60 | } |
| 61 | |
| 62 | public function writeToString() { |
| 63 | $temp_file = $this->tempFilename(); |
| 64 | self::writeToFile($temp_file); |
| 65 | $string = file_get_contents($temp_file); |
| 66 | return $string; |
| 67 | } |
| 68 | |
| 69 | public function writeToFile($filename) { |
| 70 | foreach ($this->sheets as $sheet_name => $sheet) { |
| 71 | self::finalizeSheet($sheet_name);//making sure all footers have been written |
| 72 | } |
| 73 | |
| 74 | @unlink($filename);//if the zip already exists, overwrite it |
| 75 | $zip = new \ZipArchive(); |
| 76 | if (empty($this->sheets)) { self::log("Error in " . __CLASS__ . "::" . __FUNCTION__ . ", no worksheets defined."); |
| 77 | return; |
| 78 | } |
| 79 | if (!$zip->open($filename, \ZipArchive::CREATE)) { self::log("Error in " . __CLASS__ . "::" . __FUNCTION__ . ", unable to create zip."); |
| 80 | return; |
| 81 | } |
| 82 | |
| 83 | $zip->addEmptyDir("docProps/"); |
| 84 | $zip->addFromString("docProps/app.xml", self::buildAppXML() ); |
| 85 | $zip->addFromString("docProps/core.xml", self::buildCoreXML()); |
| 86 | |
| 87 | $zip->addEmptyDir("_rels/"); |
| 88 | $zip->addFromString("_rels/.rels", self::buildRelationshipsXML()); |
| 89 | |
| 90 | $zip->addEmptyDir("xl/worksheets/"); |
| 91 | foreach ($this->sheets as $sheet) { |
| 92 | $zip->addFile($sheet->filename, "xl/worksheets/" . $sheet->xmlname ); |
| 93 | } |
| 94 | if (!empty($this->shared_strings)) { |
| 95 | $zip->addFile($this->writeSharedStringsXML(), "xl/sharedStrings.xml" ); //$zip->addFromString("xl/sharedStrings.xml", self::buildSharedStringsXML() ); |
| 96 | } |
| 97 | $zip->addFromString("xl/workbook.xml", self::buildWorkbookXML() ); |
| 98 | $zip->addFile($this->writeStylesXML(), "xl/styles.xml" ); //$zip->addFromString("xl/styles.xml" , self::buildStylesXML() ); |
| 99 | $zip->addFromString("[Content_Types].xml", self::buildContentTypesXML() ); |
| 100 | |
| 101 | $zip->addEmptyDir("xl/_rels/"); |
| 102 | $zip->addFromString("xl/_rels/workbook.xml.rels", self::buildWorkbookRelsXML() ); |
| 103 | $zip->close(); |
| 104 | } |
| 105 | |
| 106 | protected function initializeSheet($sheet_name) { |
| 107 | //if already initialized |
| 108 | if ($this->current_sheet == $sheet_name || isset($this->sheets[$sheet_name])) |
| 109 | return; |
| 110 | |
| 111 | $sheet_filename = $this->tempFilename(); |
| 112 | $sheet_xmlname = 'sheet' . (count($this->sheets) + 1) . ".xml"; |
| 113 | $this->sheets[$sheet_name] = (object)[ |
| 114 | 'filename' => $sheet_filename, |
| 115 | 'sheetname' => $sheet_name, |
| 116 | 'xmlname' => $sheet_xmlname, |
| 117 | 'row_count' => 0, |
| 118 | 'file_writer' => new XLSXWriter_BuffererWriter($sheet_filename), |
| 119 | 'cell_formats' => [], |
| 120 | 'max_cell_tag_start' => 0, |
| 121 | 'max_cell_tag_end' => 0, |
| 122 | 'finalized' => false, |
| 123 | ]; |
| 124 | $sheet = &$this->sheets[$sheet_name]; |
| 125 | $tabselected = count($this->sheets) == 1 ? 'true' : 'false';//only first sheet is selected |
| 126 | $max_cell = XLSXWriter::xlsCell(self::EXCEL_2007_MAX_ROW, self::EXCEL_2007_MAX_COL);//XFE1048577 |
| 127 | $sheet->file_writer->write('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"); |
| 128 | $sheet->file_writer->write('<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'); |
| 129 | $sheet->file_writer->write( '<sheetPr filterMode="false">'); |
| 130 | $sheet->file_writer->write( '<pageSetUpPr fitToPage="false"/>'); |
| 131 | $sheet->file_writer->write( '</sheetPr>'); |
| 132 | $sheet->max_cell_tag_start = $sheet->file_writer->ftell(); |
| 133 | $sheet->file_writer->write('<dimension ref="A1:' . $max_cell . '"/>'); |
| 134 | $sheet->max_cell_tag_end = $sheet->file_writer->ftell(); |
| 135 | $sheet->file_writer->write( '<sheetViews>'); |
| 136 | $sheet->file_writer->write( '<sheetView colorId="64" defaultGridColor="true" rightToLeft="' . $this->rtl . '" 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">'); |
| 137 | $sheet->file_writer->write( '<selection activeCell="A1" activeCellId="0" pane="topLeft" sqref="A1"/>'); |
| 138 | $sheet->file_writer->write( '</sheetView>'); |
| 139 | $sheet->file_writer->write( '</sheetViews>'); |
| 140 | $sheet->file_writer->write( '<cols>'); |
| 141 | $sheet->file_writer->write( '<col collapsed="false" hidden="false" max="1025" min="1" style="0" width="11.5"/>'); |
| 142 | $sheet->file_writer->write( '</cols>'); |
| 143 | $sheet->file_writer->write( '<sheetData>'); |
| 144 | } |
| 145 | |
| 146 | public function writeSheetHeader($sheet_name, array $header_types) { |
| 147 | if (empty($sheet_name) || empty($header_types) || !empty($this->sheets[$sheet_name])) |
| 148 | return; |
| 149 | |
| 150 | self::initializeSheet($sheet_name); |
| 151 | $sheet = &$this->sheets[$sheet_name]; |
| 152 | $sheet->cell_formats = array_values($header_types); |
| 153 | $header_row = array_keys($header_types); |
| 154 | |
| 155 | $sheet->file_writer->write('<row collapsed="false" customFormat="false" customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="' . (1) . '">'); |
| 156 | foreach ($header_row as $k => $v) { |
| 157 | $this->writeCell($sheet->file_writer, 0, $k, $v, $cell_format = 'string'); |
| 158 | } |
| 159 | $sheet->file_writer->write('</row>'); |
| 160 | $sheet->row_count++; |
| 161 | $this->current_sheet = $sheet_name; |
| 162 | } |
| 163 | |
| 164 | public function writeSheetRow($sheet_name, array $row) { |
| 165 | if (empty($sheet_name) || empty($row)) |
| 166 | return; |
| 167 | |
| 168 | self::initializeSheet($sheet_name); |
| 169 | $sheet = &$this->sheets[$sheet_name]; |
| 170 | if (empty($sheet->cell_formats)) |
| 171 | { |
| 172 | $sheet->cell_formats = array_fill(0, count($row), 'string'); |
| 173 | } |
| 174 | |
| 175 | $sheet->file_writer->write('<row collapsed="false" customFormat="false" customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="' . ($sheet->row_count + 1) . '">'); |
| 176 | foreach ($row as $k => $v) { |
| 177 | $this->writeCell($sheet->file_writer, $sheet->row_count, $k, $v, $sheet->cell_formats[$k]); |
| 178 | } |
| 179 | $sheet->file_writer->write('</row>'); |
| 180 | $sheet->row_count++; |
| 181 | $this->current_sheet = $sheet_name; |
| 182 | } |
| 183 | |
| 184 | protected function finalizeSheet($sheet_name) { |
| 185 | if (empty($sheet_name) || $this->sheets[$sheet_name]->finalized) |
| 186 | return; |
| 187 | |
| 188 | $sheet = &$this->sheets[$sheet_name]; |
| 189 | |
| 190 | $sheet->file_writer->write( '</sheetData>'); |
| 191 | $sheet->file_writer->write( '<printOptions headings="false" gridLines="false" gridLinesSet="true" horizontalCentered="false" verticalCentered="false"/>'); |
| 192 | $sheet->file_writer->write( '<pageMargins left="0.5" right="0.5" top="1.0" bottom="1.0" header="0.5" footer="0.5"/>'); |
| 193 | $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"/>'); |
| 194 | $sheet->file_writer->write( '<headerFooter differentFirst="false" differentOddEven="false">'); |
| 195 | $sheet->file_writer->write( '<oddHeader>&C&"Times New Roman,Regular"&12&A</oddHeader>'); |
| 196 | $sheet->file_writer->write( '<oddFooter>&C&"Times New Roman,Regular"&12Page &P</oddFooter>'); |
| 197 | $sheet->file_writer->write( '</headerFooter>'); |
| 198 | $sheet->file_writer->write('</worksheet>'); |
| 199 | |
| 200 | $max_cell = self::xlsCell($sheet->row_count - 1, count($sheet->cell_formats) - 1); |
| 201 | $max_cell_tag = '<dimension ref="A1:' . $max_cell . '"/>'; |
| 202 | $padding_length = $sheet->max_cell_tag_end - $sheet->max_cell_tag_start - strlen($max_cell_tag); |
| 203 | $sheet->file_writer->fseek($sheet->max_cell_tag_start); |
| 204 | $sheet->file_writer->write($max_cell_tag . str_repeat(" ", (int)$padding_length)); |
| 205 | $sheet->file_writer->close(); |
| 206 | $sheet->finalized = true; |
| 207 | } |
| 208 | |
| 209 | public function writeSheet(array $data, $sheet_name='', array $header_types=[] ) { |
| 210 | $sheet_name = empty($sheet_name) ? 'Sheet1' : $sheet_name; |
| 211 | $data = empty($data) ? [['']] : $data; |
| 212 | if (!empty($header_types)) |
| 213 | { |
| 214 | $this->writeSheetHeader($sheet_name, $header_types); |
| 215 | } |
| 216 | foreach ($data as $i => $row) |
| 217 | { |
| 218 | $this->writeSheetRow($sheet_name, $row); |
| 219 | } |
| 220 | $this->finalizeSheet($sheet_name); |
| 221 | } |
| 222 | |
| 223 | protected function writeCell(XLSXWriter_BuffererWriter &$file, $row_number, $column_number, $value, $cell_format) { |
| 224 | static $styles = ['money' => 1,'dollar' => 1,'datetime' => 2,'date' => 3,'string' => 0]; |
| 225 | $cell = self::xlsCell($row_number, $column_number); |
| 226 | $s = isset($styles[$cell_format]) ? $styles[$cell_format] : '0'; |
| 227 | |
| 228 | if (!is_scalar($value) || $value == '') { //objects, array, empty |
| 229 | $file->write('<c r="' . $cell . '" s="' . $s . '"/>'); |
| 230 | } elseif ($cell_format == 'date') { |
| 231 | $file->write('<c r="' . $cell . '" s="' . $s . '" t="n"><v>' . intval(self::convert_date_time($value)) . '</v></c>'); |
| 232 | } elseif ($cell_format == 'datetime') { |
| 233 | $file->write('<c r="' . $cell . '" s="' . $s . '" t="n"><v>' . self::convert_date_time($value) . '</v></c>'); |
| 234 | } elseif (!is_string($value)) { |
| 235 | $file->write('<c r="' . $cell . '" s="' . $s . '" t="n"><v>' . ($value * 1) . '</v></c>');//int,float, etc |
| 236 | } elseif ($value[0] != '0' && filter_var($value, FILTER_VALIDATE_INT)){ //excel wants to trim leading zeros |
| 237 | $file->write('<c r="' . $cell . '" s="' . $s . '" t="n"><v>' . ($value) . '</v></c>');//numeric string |
| 238 | } elseif ($value[0] == '='){ |
| 239 | $file->write('<c r="' . $cell . '" s="' . $s . '" t="s"><f>' . self::xmlspecialchars($value) . '</f></c>'); |
| 240 | } elseif ($value !== ''){ |
| 241 | $file->write('<c r="' . $cell . '" s="' . $s . '" t="s"><v>' . self::xmlspecialchars($this->setSharedString($value)) . '</v></c>'); |
| 242 | } |
| 243 | } |
| 244 | |
| 245 | protected function writeStylesXML() { |
| 246 | $temporary_filename = $this->tempFilename(); |
| 247 | $file = new XLSXWriter_BuffererWriter($temporary_filename); |
| 248 | $file->write('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"); |
| 249 | $file->write('<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'); |
| 250 | $file->write('<numFmts count="4">'); |
| 251 | $file->write( '<numFmt formatCode="GENERAL" numFmtId="164"/>'); |
| 252 | $file->write( '<numFmt formatCode="[$$-1009]#,##0.00;[RED]\-[$$-1009]#,##0.00" numFmtId="165"/>'); |
| 253 | $file->write( '<numFmt formatCode="YYYY-MM-DD\ HH:MM:SS" numFmtId="166"/>'); |
| 254 | $file->write( '<numFmt formatCode="YYYY-MM-DD" numFmtId="167"/>'); |
| 255 | $file->write('</numFmts>'); |
| 256 | $file->write('<fonts count="4">'); |
| 257 | $file->write( '<font><name val="Arial"/><charset val="1"/><family val="2"/><sz val="10"/></font>'); |
| 258 | $file->write( '<font><name val="Arial"/><family val="0"/><sz val="10"/></font>'); |
| 259 | $file->write( '<font><name val="Arial"/><family val="0"/><sz val="10"/></font>'); |
| 260 | $file->write( '<font><name val="Arial"/><family val="0"/><sz val="10"/></font>'); |
| 261 | $file->write('</fonts>'); |
| 262 | $file->write('<fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></fills>'); |
| 263 | $file->write('<borders count="1"><border diagonalDown="false" diagonalUp="false"><left/><right/><top/><bottom/><diagonal/></border></borders>'); |
| 264 | $file->write( '<cellStyleXfs count="20">'); |
| 265 | $file->write( '<xf applyAlignment="true" applyBorder="true" applyFont="true" applyProtection="true" borderId="0" fillId="0" fontId="0" numFmtId="164">'); |
| 266 | $file->write( '<alignment horizontal="general" indent="0" shrinkToFit="false" textRotation="0" vertical="bottom" wrapText="false"/>'); |
| 267 | $file->write( '<protection hidden="false" locked="true"/>'); |
| 268 | $file->write( '</xf>'); |
| 269 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="0"/>'); |
| 270 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="0"/>'); |
| 271 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="2" numFmtId="0"/>'); |
| 272 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="2" numFmtId="0"/>'); |
| 273 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
| 274 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
| 275 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
| 276 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
| 277 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
| 278 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
| 279 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
| 280 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
| 281 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
| 282 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
| 283 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="43"/>'); |
| 284 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="41"/>'); |
| 285 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="44"/>'); |
| 286 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="42"/>'); |
| 287 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="9"/>'); |
| 288 | $file->write( '</cellStyleXfs>'); |
| 289 | $file->write( '<cellXfs count="4">'); |
| 290 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="false" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="164" xfId="0"/>'); |
| 291 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="false" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="165" xfId="0"/>'); |
| 292 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="false" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="166" xfId="0"/>'); |
| 293 | $file->write( '<xf applyAlignment="false" applyBorder="false" applyFont="false" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="167" xfId="0"/>'); |
| 294 | $file->write( '</cellXfs>'); |
| 295 | $file->write( '<cellStyles count="6">'); |
| 296 | $file->write( '<cellStyle builtinId="0" customBuiltin="false" name="Normal" xfId="0"/>'); |
| 297 | $file->write( '<cellStyle builtinId="3" customBuiltin="false" name="Comma" xfId="15"/>'); |
| 298 | $file->write( '<cellStyle builtinId="6" customBuiltin="false" name="Comma [0]" xfId="16"/>'); |
| 299 | $file->write( '<cellStyle builtinId="4" customBuiltin="false" name="Currency" xfId="17"/>'); |
| 300 | $file->write( '<cellStyle builtinId="7" customBuiltin="false" name="Currency [0]" xfId="18"/>'); |
| 301 | $file->write( '<cellStyle builtinId="5" customBuiltin="false" name="Percent" xfId="19"/>'); |
| 302 | $file->write( '</cellStyles>'); |
| 303 | $file->write('</styleSheet>'); |
| 304 | $file->close(); |
| 305 | return $temporary_filename; |
| 306 | } |
| 307 | |
| 308 | protected function setSharedString($v) { |
| 309 | if (isset($this->shared_strings[$v])) |
| 310 | { |
| 311 | $string_value = $this->shared_strings[$v]; |
| 312 | } |
| 313 | else |
| 314 | { |
| 315 | $string_value = count($this->shared_strings); |
| 316 | $this->shared_strings[$v] = $string_value; |
| 317 | } |
| 318 | $this->shared_string_count++;//non-unique count |
| 319 | return $string_value; |
| 320 | } |
| 321 | |
| 322 | protected function writeSharedStringsXML() { |
| 323 | $temporary_filename = $this->tempFilename(); |
| 324 | $file = new XLSXWriter_BuffererWriter($temporary_filename, $fd_flags = 'w', $check_utf8 = true); |
| 325 | $file->write('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"); |
| 326 | $file->write('<sst count="' . ($this->shared_string_count) . '" uniqueCount="' . count($this->shared_strings) . '" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'); |
| 327 | foreach ($this->shared_strings as $s => $c) |
| 328 | { |
| 329 | $file->write('<si><t>' . self::xmlspecialchars($s) . '</t></si>'); |
| 330 | } |
| 331 | $file->write('</sst>'); |
| 332 | $file->close(); |
| 333 | |
| 334 | return $temporary_filename; |
| 335 | } |
| 336 | |
| 337 | protected function buildAppXML() { |
| 338 | $app_xml = ""; |
| 339 | $app_xml .= '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"; |
| 340 | $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>'; |
| 341 | return $app_xml; |
| 342 | } |
| 343 | |
| 344 | protected function buildCoreXML() { |
| 345 | $core_xml = ""; |
| 346 | $core_xml .= '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"; |
| 347 | $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">'; |
| 348 | $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'; |
| 349 | $core_xml .= '<dc:creator>' . self::xmlspecialchars($this->author) . '</dc:creator>'; |
| 350 | $core_xml .= '<cp:revision>0</cp:revision>'; |
| 351 | $core_xml .= '</cp:coreProperties>'; |
| 352 | return $core_xml; |
| 353 | } |
| 354 | |
| 355 | protected function buildRelationshipsXML() { |
| 356 | $rels_xml = ""; |
| 357 | $rels_xml .= '<?xml version="1.0" encoding="UTF-8"?>' . "\n"; |
| 358 | $rels_xml .= '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'; |
| 359 | $rels_xml .= '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'; |
| 360 | $rels_xml .= '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>'; |
| 361 | $rels_xml .= '<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>'; |
| 362 | $rels_xml .= "\n"; |
| 363 | $rels_xml .= '</Relationships>'; |
| 364 | return $rels_xml; |
| 365 | } |
| 366 | |
| 367 | protected function buildWorkbookXML() { |
| 368 | $i = 0; |
| 369 | $workbook_xml = ""; |
| 370 | $workbook_xml .= '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"; |
| 371 | $workbook_xml .= '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'; |
| 372 | $workbook_xml .= '<fileVersion appName="Calc"/><workbookPr backupFile="false" showObjects="all" date1904="false"/><workbookProtection/>'; |
| 373 | $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>'; |
| 374 | $workbook_xml .= '<sheets>'; |
| 375 | foreach ($this->sheets as $sheet_name => $sheet) { |
| 376 | $workbook_xml .= '<sheet name="' . self::xmlspecialchars($sheet->sheetname) . '" sheetId="' . ($i + 1) . '" state="visible" r:id="rId' . ($i + 2) . '"/>'; |
| 377 | $i++; |
| 378 | } |
| 379 | $workbook_xml .= '</sheets>'; |
| 380 | $workbook_xml .= '<calcPr iterateCount="100" refMode="A1" iterate="false" iterateDelta="0.001"/></workbook>'; |
| 381 | return $workbook_xml; |
| 382 | } |
| 383 | |
| 384 | protected function buildWorkbookRelsXML() { |
| 385 | $i = 0; |
| 386 | $wkbkrels_xml = ""; |
| 387 | $wkbkrels_xml .= '<?xml version="1.0" encoding="UTF-8"?>' . "\n"; |
| 388 | $wkbkrels_xml .= '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'; |
| 389 | $wkbkrels_xml .= '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>'; |
| 390 | foreach ($this->sheets as $sheet_name => $sheet) { |
| 391 | $wkbkrels_xml .= '<Relationship Id="rId' . ($i + 2) . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/' . ($sheet->xmlname) . '"/>'; |
| 392 | $i++; |
| 393 | } |
| 394 | if (!empty($this->shared_strings)) { |
| 395 | $wkbkrels_xml .= '<Relationship Id="rId' . (count($this->sheets) + 2) . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>'; |
| 396 | } |
| 397 | $wkbkrels_xml .= "\n"; |
| 398 | $wkbkrels_xml .= '</Relationships>'; |
| 399 | return $wkbkrels_xml; |
| 400 | } |
| 401 | |
| 402 | protected function buildContentTypesXML() { |
| 403 | $content_types_xml = ""; |
| 404 | $content_types_xml .= '<?xml version="1.0" encoding="UTF-8"?>' . "\n"; |
| 405 | $content_types_xml .= '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'; |
| 406 | $content_types_xml .= '<Override PartName="/_rels/.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'; |
| 407 | $content_types_xml .= '<Override PartName="/xl/_rels/workbook.xml.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'; |
| 408 | foreach ($this->sheets as $sheet_name => $sheet) { |
| 409 | $content_types_xml .= '<Override PartName="/xl/worksheets/' . ($sheet->xmlname) . '" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'; |
| 410 | } |
| 411 | if (!empty($this->shared_strings)) { |
| 412 | $content_types_xml .= '<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>'; |
| 413 | } |
| 414 | $content_types_xml .= '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'; |
| 415 | $content_types_xml .= '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>'; |
| 416 | $content_types_xml .= '<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>'; |
| 417 | $content_types_xml .= '<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>'; |
| 418 | $content_types_xml .= "\n"; |
| 419 | $content_types_xml .= '</Types>'; |
| 420 | return $content_types_xml; |
| 421 | } |
| 422 | |
| 423 | //------------------------------------------------------------------ |
| 424 | /* |
| 425 | * @param $row_number int, zero based |
| 426 | * @param $column_number int, zero based |
| 427 | * @return Cell label/coordinates, ex: A1, C3, AA42 |
| 428 | * */ |
| 429 | public static function xlsCell($row_number, $column_number) { |
| 430 | $n = $column_number; |
| 431 | for ($r = ""; $n >= 0; $n = intval($n / 26) - 1) { |
| 432 | $r = chr($n % 26 + 0x41) . $r; |
| 433 | } |
| 434 | return $r . ($row_number + 1); |
| 435 | } |
| 436 | //------------------------------------------------------------------ |
| 437 | public static function log($string) { |
| 438 | file_put_contents("php://stderr", date("Y-m-d H:i:s:") . rtrim(is_array($string) ? json_encode($string) : $string) . "\n"); |
| 439 | } |
| 440 | //------------------------------------------------------------------ |
| 441 | public static function sanitize_filename($filename) { |
| 442 | //http://msdn.microsoft.com/en-us/library/aa365247%28VS.85%29.aspx |
| 443 | $nonprinting = array_map('chr', range(0, 31)); |
| 444 | $invalid_chars = ['<', '>', '?', '"', ':', '|', '\\', '/', '*', '&']; |
| 445 | $all_invalids = array_merge($nonprinting, $invalid_chars); |
| 446 | return str_replace($all_invalids, "", $filename); |
| 447 | } |
| 448 | //------------------------------------------------------------------ |
| 449 | public static function xmlspecialchars($val) { |
| 450 | return str_replace("'", "'", htmlspecialchars($val)); |
| 451 | } |
| 452 | //------------------------------------------------------------------ |
| 453 | public static function array_first_key(array $arr) { |
| 454 | reset($arr); |
| 455 | $first_key = key($arr); |
| 456 | return $first_key; |
| 457 | } |
| 458 | //------------------------------------------------------------------ |
| 459 | public static function convert_date_time($date_input) { |
| 460 | //thanks to Excel::Writer::XLSX::Worksheet.pm (perl) |
| 461 | $days = 0; # Number of days since epoch |
| 462 | $seconds = 0; # Time expressed as fraction of 24h hours in seconds |
| 463 | $year = $month = $day = 0; |
| 464 | $hour = $min = $sec = 0; |
| 465 | |
| 466 | $date_time = $date_input; |
| 467 | if (preg_match("/(\d{4})\-(\d{2})\-(\d{2})/", $date_time, $matches)) |
| 468 | { |
| 469 | list($junk,$year,$month,$day) = $matches; |
| 470 | } |
| 471 | if (preg_match("/(\d{2}):(\d{2}):(\d{2})/", $date_time, $matches)) |
| 472 | { |
| 473 | list($junk,$hour,$min,$sec) = $matches; |
| 474 | $seconds = ( $hour * 60 * 60 + $min * 60 + $sec ) / ( 24 * 60 * 60 ); |
| 475 | } |
| 476 | |
| 477 | //using 1900 as epoch, not 1904, ignoring 1904 special case |
| 478 | |
| 479 | # Special cases for Excel. |
| 480 | if ("$year-$month-$day" == '1899-12-31') return $seconds ; # Excel 1900 epoch |
| 481 | if ("$year-$month-$day" == '1900-01-00') return $seconds ; # Excel 1900 epoch |
| 482 | if ("$year-$month-$day" == '1900-02-29') return 60 + $seconds ; # Excel false leapday |
| 483 | |
| 484 | # We calculate the date by calculating the number of days since the epoch |
| 485 | # and adjust for the number of leap days. We calculate the number of leap |
| 486 | # days by normalising the year in relation to the epoch. Thus the year 2000 |
| 487 | # becomes 100 for 4 and 100 year leapdays and 400 for 400 year leapdays. |
| 488 | $epoch = 1900; |
| 489 | $offset = 0; |
| 490 | $norm = 300; |
| 491 | $range = $year - $epoch; |
| 492 | |
| 493 | # Set month days and check for leap year. |
| 494 | $leap = (($year % 400 == 0) || (($year % 4 == 0) && ($year % 100)) ) ? 1 : 0; |
| 495 | $mdays = [ 31, ($leap ? 29 : 28), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 ]; |
| 496 | |
| 497 | # Some boundary checks |
| 498 | if ($year < $epoch || $year > 9999) return 0; |
| 499 | if ($month < 1 || $month > 12) return 0; |
| 500 | if ($day < 1 || $day > $mdays[$month - 1]) return 0; |
| 501 | |
| 502 | # Accumulate the number of days since the epoch. |
| 503 | $days = $day; # Add days for current month |
| 504 | $days += array_sum( array_slice($mdays, 0, $month - 1 ) ); # Add days for past months |
| 505 | $days += $range * 365; # Add days for past years |
| 506 | $days += intval( ( $range ) / 4 ); # Add leapdays |
| 507 | $days -= intval( ( $range + $offset ) / 100 ); # Subtract 100 year leapdays |
| 508 | $days += intval( ( $range + $offset + $norm ) / 400 ); # Add 400 year leapdays |
| 509 | $days -= $leap; # Already counted above |
| 510 | |
| 511 | # Adjust for Excel erroneously treating 1900 as a leap year. |
| 512 | if ($days > 59) { $days++; |
| 513 | } |
| 514 | |
| 515 | return $days + $seconds; |
| 516 | } |
| 517 | //------------------------------------------------------------------ |
| 518 | } |
| 519 | |
| 520 | class XLSXWriter_BuffererWriter |
| 521 | { |
| 522 | protected $fd = null; |
| 523 | protected $buffer = ''; |
| 524 | protected $check_utf8 = false; |
| 525 | |
| 526 | public function __construct($filename, $fd_fopen_flags='w', $check_utf8=false) { |
| 527 | $this->check_utf8 = $check_utf8; |
| 528 | $this->fd = fopen($filename, $fd_fopen_flags); |
| 529 | if ($this->fd === false) { |
| 530 | XLSXWriter::log("Unable to open $filename for writing."); |
| 531 | } |
| 532 | } |
| 533 | |
| 534 | public function write($string) { |
| 535 | $this->buffer .= $string; |
| 536 | if (isset($this->buffer[8191])) { |
| 537 | $this->purge(); |
| 538 | } |
| 539 | } |
| 540 | |
| 541 | protected function purge() { |
| 542 | if ($this->fd) { |
| 543 | if ($this->check_utf8 && !self::isValidUTF8($this->buffer)) { |
| 544 | XLSXWriter::log("Error, invalid UTF8 encoding detected."); |
| 545 | $this->check_utf8 = false; |
| 546 | } |
| 547 | fwrite($this->fd, $this->buffer); |
| 548 | $this->buffer = ''; |
| 549 | } |
| 550 | } |
| 551 | |
| 552 | public function close() { |
| 553 | $this->purge(); |
| 554 | if ($this->fd) { |
| 555 | fclose($this->fd); |
| 556 | $this->fd = null; |
| 557 | } |
| 558 | } |
| 559 | |
| 560 | public function __destruct() { |
| 561 | $this->close(); |
| 562 | } |
| 563 | |
| 564 | public function ftell() { |
| 565 | if ($this->fd) { |
| 566 | $this->purge(); |
| 567 | return ftell($this->fd); |
| 568 | } |
| 569 | return -1; |
| 570 | } |
| 571 | |
| 572 | public function fseek($pos) { |
| 573 | if ($this->fd) { |
| 574 | $this->purge(); |
| 575 | return fseek($this->fd, $pos); |
| 576 | } |
| 577 | return -1; |
| 578 | } |
| 579 | |
| 580 | protected static function isValidUTF8($string) { |
| 581 | if (function_exists('mb_check_encoding')) |
| 582 | { |
| 583 | return mb_check_encoding($string, 'UTF-8') ? true : false; |
| 584 | } |
| 585 | return preg_match("//u", $string) ? true : false; |
| 586 | } |
| 587 | } |
| 588 |