ggoffy /
wgevents
| Total Complexity | 124 |
| Total Lines | 655 |
| Duplicated Lines | 0 % |
| Changes | 1 | ||
| Bugs | 0 | Features | 0 |
Complex classes like SimpleXLSXGen often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use SimpleXLSXGen, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 13 | class SimpleXLSXGen { |
||
| 14 | |||
| 15 | public $curSheet; |
||
| 16 | protected $defaultFont; |
||
| 17 | protected $defaultFontSize; |
||
| 18 | protected $sheets; |
||
| 19 | protected $template; |
||
| 20 | protected $F, $F_KEYS; // fonts |
||
| 21 | protected $XF, $XF_KEYS; // cellXfs |
||
| 22 | protected $SI, $SI_KEYS; // shared strings |
||
| 23 | const N_NORMAL = 0; // General |
||
| 24 | const N_INT = 1; // 0 |
||
| 25 | const N_DEC = 2; // 0.00 |
||
| 26 | const N_PERCENT_INT = 9; // 0% |
||
| 27 | const N_PRECENT_DEC = 10; // 0.00% |
||
| 28 | const N_DATE = 14; // mm-dd-yy |
||
| 29 | const N_TIME = 20; // h:mm |
||
| 30 | const N_DATETIME = 22; // m/d/yy h:mm |
||
| 31 | const F_NORMAL = 0; |
||
| 32 | const F_HYPERLINK = 1; |
||
| 33 | const F_BOLD = 2; |
||
| 34 | const F_ITALIC = 4; |
||
| 35 | const F_UNDERLINE = 8; |
||
| 36 | const F_STRIKE = 16; |
||
| 37 | const A_DEFAULT = 0; |
||
| 38 | const A_LEFT = 1; |
||
| 39 | const A_RIGHT = 2; |
||
| 40 | const A_CENTER = 3; |
||
| 41 | |||
| 42 | |||
| 43 | public function __construct() { |
||
| 44 | $this->curSheet = -1; |
||
| 45 | $this->defaultFont = 'Calibri'; |
||
| 46 | $this->sheets = [ ['name' => 'Sheet1', 'rows' => [], 'hyperlinks' => [] ] ]; |
||
| 47 | $this->SI = []; // sharedStrings index |
||
| 48 | $this->SI_KEYS = []; // & keys |
||
| 49 | $this->F = [ self::F_NORMAL ]; // fonts |
||
| 50 | $this->F_KEYS = [0]; // & keys |
||
| 51 | $this->XF = [ [self::N_NORMAL, self::F_NORMAL, self::A_DEFAULT] ]; // styles |
||
| 52 | $this->XF_KEYS = ['N0F0A0' => 0 ]; // & keys |
||
| 53 | |||
| 54 | $this->template = [ |
||
| 55 | '_rels/.rels' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
||
| 56 | <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> |
||
| 57 | <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/> |
||
| 58 | <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/> |
||
| 59 | <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/> |
||
| 60 | </Relationships>', |
||
| 61 | 'docProps/app.xml' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
||
| 62 | <Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties"> |
||
| 63 | <TotalTime>0</TotalTime> |
||
| 64 | <Application>'.__CLASS__.'</Application></Properties>', |
||
| 65 | 'docProps/core.xml' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
||
| 66 | <cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> |
||
| 67 | <dcterms:created xsi:type="dcterms:W3CDTF">{DATE}</dcterms:created> |
||
| 68 | <dc:language>en-US</dc:language> |
||
| 69 | <dcterms:modified xsi:type="dcterms:W3CDTF">{DATE}</dcterms:modified> |
||
| 70 | <cp:revision>1</cp:revision> |
||
| 71 | </cp:coreProperties>', |
||
| 72 | 'xl/_rels/workbook.xml.rels' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
||
| 73 | <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> |
||
| 74 | <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/> |
||
| 75 | {SHEETS}', |
||
| 76 | 'xl/worksheets/sheet1.xml' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
||
| 77 | <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" |
||
| 78 | xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" |
||
| 79 | ><dimension ref="{REF}"/>{COLS}<sheetData>{ROWS}</sheetData>{HYPERLINKS}</worksheet>', |
||
| 80 | 'xl/worksheets/_rels/sheet1.xml.rels' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
||
| 81 | <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">{HYPERLINKS}</Relationships>', |
||
| 82 | 'xl/sharedStrings.xml' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
||
| 83 | <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="{CNT}" uniqueCount="{CNT}">{STRINGS}</sst>', |
||
| 84 | 'xl/styles.xml' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
||
| 85 | <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> |
||
| 86 | {FONTS} |
||
| 87 | <fills count="1"><fill><patternFill patternType="none"/></fill></fills> |
||
| 88 | <borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders> |
||
| 89 | <cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" /></cellStyleXfs> |
||
| 90 | {XF} |
||
| 91 | <cellStyles count="1"> |
||
| 92 | <cellStyle name="Normal" xfId="0" builtinId="0"/> |
||
| 93 | </cellStyles> |
||
| 94 | </styleSheet>', |
||
| 95 | 'xl/workbook.xml' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
||
| 96 | <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"> |
||
| 97 | <fileVersion appName="'.__CLASS__.'"/><sheets> |
||
| 98 | {SHEETS} |
||
| 99 | </sheets></workbook>', |
||
| 100 | '[Content_Types].xml' => '<?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
||
| 101 | <Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"> |
||
| 102 | <Override PartName="/rels/.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/> |
||
| 103 | <Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/> |
||
| 104 | <Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/> |
||
| 105 | <Override PartName="/xl/_rels/workbook.xml.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/> |
||
| 106 | <Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/> |
||
| 107 | <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/> |
||
| 108 | <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/> |
||
| 109 | {TYPES} |
||
| 110 | </Types>', |
||
| 111 | ]; |
||
| 112 | |||
| 113 | // <col min="1" max="1" width="22.1796875" bestFit="1" customWidth="1"/> |
||
| 114 | // <row r="1" spans="1:2" x14ac:dyDescent="0.35"><c r="A1" t="s"><v>0</v></c><c r="B1"><v>100</v></c></row><row r="2" spans="1:2" x14ac:dyDescent="0.35"><c r="A2" t="s"><v>1</v></c><c r="B2"><v>200</v></c></row> |
||
| 115 | // <si><t>Простой шаблон</t></si><si><t>Будем делать генератор</t></si> |
||
| 116 | } |
||
| 117 | public static function fromArray( array $rows, $sheetName = null ) { |
||
| 118 | $xlsx = new static(); |
||
| 119 | return $xlsx->addSheet( $rows, $sheetName ); |
||
| 120 | } |
||
| 121 | |||
| 122 | public function addSheet( array $rows, $name = null ) { |
||
| 123 | |||
| 124 | $this->curSheet++; |
||
| 125 | if ( $name === null ) { // autogenerated sheet names |
||
| 126 | $name = 'Sheet'.($this->curSheet+1); |
||
| 127 | } else { |
||
| 128 | $names = []; |
||
| 129 | foreach( $this->sheets as $sh ) { |
||
| 130 | $names[ \mb_strtoupper( $sh['name']) ] = 1; |
||
| 131 | } |
||
| 132 | for( $i = 0; $i < 100; $i++ ) { |
||
| 133 | $new_name = ($i === 0) ? $name : $name .' ('.$i.')'; |
||
| 134 | $NEW_NAME = \mb_strtoupper( $new_name ); |
||
| 135 | if ( !isset( $names[ $NEW_NAME ]) ) { |
||
| 136 | $name = $new_name; |
||
| 137 | break; |
||
| 138 | } |
||
| 139 | } |
||
| 140 | } |
||
| 141 | |||
| 142 | $this->sheets[$this->curSheet] = ['name' => $name, 'hyperlinks' => []]; |
||
| 143 | |||
| 144 | if ( \is_array( $rows ) && isset( $rows[0] ) && \is_array($rows[0]) ) { |
||
| 145 | $this->sheets[$this->curSheet]['rows'] = $rows; |
||
| 146 | } else { |
||
| 147 | $this->sheets[$this->curSheet]['rows'] = []; |
||
| 148 | } |
||
| 149 | return $this; |
||
| 150 | } |
||
| 151 | |||
| 152 | public function __toString() { |
||
| 153 | $fh = fopen( 'php://memory', 'wb' ); |
||
| 154 | if ( ! $fh ) { |
||
|
introduced
by
|
|||
| 155 | return ''; |
||
| 156 | } |
||
| 157 | |||
| 158 | if ( ! $this->_write( $fh ) ) { |
||
| 159 | fclose( $fh ); |
||
| 160 | return ''; |
||
| 161 | } |
||
| 162 | $size = ftell( $fh ); |
||
| 163 | fseek( $fh, 0); |
||
| 164 | |||
| 165 | return (string) fread( $fh, $size ); |
||
| 166 | } |
||
| 167 | |||
| 168 | public function saveAs( $filename ) { |
||
| 169 | $fh = fopen( $filename, 'wb' ); |
||
| 170 | if (!$fh) { |
||
| 171 | return false; |
||
| 172 | } |
||
| 173 | if ( !$this->_write($fh) ) { |
||
| 174 | fclose($fh); |
||
| 175 | return false; |
||
| 176 | } |
||
| 177 | fclose($fh); |
||
| 178 | |||
| 179 | return true; |
||
| 180 | } |
||
| 181 | |||
| 182 | public function download() { |
||
| 183 | return $this->downloadAs( gmdate('YmdHi') . '.xlsx' ); |
||
| 184 | } |
||
| 185 | |||
| 186 | public function downloadAs( $filename ) { |
||
| 187 | $fh = fopen('php://memory','wb'); |
||
| 188 | if (!$fh) { |
||
| 189 | return false; |
||
| 190 | } |
||
| 191 | |||
| 192 | if ( !$this->_write( $fh )) { |
||
| 193 | fclose( $fh ); |
||
| 194 | return false; |
||
| 195 | } |
||
| 196 | |||
| 197 | $size = ftell($fh); |
||
| 198 | |||
| 199 | header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); |
||
| 200 | header('Content-Disposition: attachment; filename="'.$filename.'"'); |
||
| 201 | header('Last-Modified: ' . gmdate('D, d M Y H:i:s \G\M\T' , \time() )); |
||
| 202 | header('Content-Length: '.$size); |
||
| 203 | |||
| 204 | while( ob_get_level() ) { |
||
| 205 | ob_end_clean(); |
||
| 206 | } |
||
| 207 | fseek($fh,0); |
||
| 208 | fpassthru( $fh ); |
||
| 209 | |||
| 210 | fclose($fh); |
||
| 211 | return true; |
||
| 212 | } |
||
| 213 | |||
| 214 | protected function _write( $fh ) { |
||
| 215 | |||
| 216 | |||
| 217 | $dirSignatureE= "\x50\x4b\x05\x06"; // end of central dir signature |
||
| 218 | $zipComments = 'Generated by '.__CLASS__.' PHP class, thanks [email protected]'; |
||
| 219 | |||
| 220 | if (!$fh) { |
||
| 221 | return false; |
||
| 222 | } |
||
| 223 | |||
| 224 | $cdrec = ''; // central directory content |
||
| 225 | $entries= 0; // number of zipped files |
||
| 226 | $cnt_sheets = \count( $this->sheets ); |
||
| 227 | |||
| 228 | foreach ($this->template as $cfilename => $template ) { |
||
| 229 | if ( $cfilename === 'xl/_rels/workbook.xml.rels' ) { |
||
| 230 | $s = ''; |
||
| 231 | for ( $i = 0; $i < $cnt_sheets; $i++) { |
||
| 232 | $s .= '<Relationship Id="rId'.($i+2).'" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"'. |
||
| 233 | ' Target="worksheets/sheet'.($i+1).".xml\"/>\n"; |
||
| 234 | } |
||
| 235 | $s .= '<Relationship Id="rId'.($i+2).'" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/></Relationships>'; |
||
| 236 | $template = \str_replace('{SHEETS}', $s, $template); |
||
| 237 | $this->_writeEntry($fh, $cdrec, $cfilename, $template); |
||
| 238 | $entries++; |
||
| 239 | } elseif ( $cfilename === 'xl/workbook.xml' ) { |
||
| 240 | $s = ''; |
||
| 241 | foreach ( $this->sheets as $k => $v ) { |
||
| 242 | $s .= '<sheet name="' . $this->esc( $v['name'] ) . '" sheetId="' . ( $k + 1) . '" state="visible" r:id="rId' . ( $k + 2) . '"/>'; |
||
| 243 | } |
||
| 244 | $template = \str_replace('{SHEETS}', $s, $template); |
||
| 245 | $this->_writeEntry($fh, $cdrec, $cfilename, $template); |
||
| 246 | $entries++; |
||
| 247 | } |
||
| 248 | elseif ( $cfilename === 'docProps/core.xml' ) { |
||
| 249 | $template = \str_replace('{DATE}', gmdate('Y-m-d\TH:i:s\Z'), $template); |
||
| 250 | $this->_writeEntry($fh, $cdrec, $cfilename, $template); |
||
| 251 | $entries++; |
||
| 252 | } elseif ( $cfilename === 'xl/sharedStrings.xml' ) { |
||
| 253 | if (!\count($this->SI)) { |
||
| 254 | $this->SI[] = 'No Data'; |
||
| 255 | } |
||
| 256 | $si_cnt = \count($this->SI); |
||
| 257 | $si = '<si><t>'.\implode("</t></si>\r\n<si><t>", $this->SI).'</t></si>'; |
||
| 258 | $template = \str_replace(['{CNT}', '{STRINGS}'], [ $si_cnt, $si ], $template ); |
||
| 259 | $this->_writeEntry($fh, $cdrec, $cfilename, $template); |
||
| 260 | $entries++; |
||
| 261 | } elseif ( $cfilename === 'xl/worksheets/sheet1.xml' ) { |
||
| 262 | foreach ( $this->sheets as $k => $v ) { |
||
| 263 | $filename = 'xl/worksheets/sheet'.($k+1).'.xml'; |
||
| 264 | $xml = $this->_sheetToXML($k, $template); |
||
| 265 | $this->_writeEntry($fh, $cdrec, $filename, $xml ); |
||
| 266 | $entries++; |
||
| 267 | } |
||
| 268 | $xml = null; |
||
| 269 | } elseif ( $cfilename === 'xl/worksheets/_rels/sheet1.xml.rels' ) { |
||
| 270 | foreach ( $this->sheets as $k => $v ) { |
||
| 271 | if ( \count($v['hyperlinks'])) { |
||
| 272 | $RH = []; |
||
| 273 | $filename = 'xl/worksheets/_rels/sheet' . ( $k + 1 ) . '.xml.rels'; |
||
| 274 | foreach ( $v['hyperlinks'] as $h ) { |
||
| 275 | $RH[] = '<Relationship Id="' . $h['ID'] . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="' . $this->esc($h['H']) . '" TargetMode="External"/>'; |
||
| 276 | } |
||
| 277 | $xml = \str_replace( '{HYPERLINKS}', \implode( "\r\n", $RH ), $template ); |
||
| 278 | $this->_writeEntry( $fh, $cdrec, $filename, $xml ); |
||
| 279 | $entries++; |
||
| 280 | } |
||
| 281 | } |
||
| 282 | $xml = null; |
||
| 283 | |||
| 284 | } elseif ( $cfilename === '[Content_Types].xml' ) { |
||
| 285 | $TYPES = ['<Override PartName="/_rels/.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>']; |
||
| 286 | foreach ( $this->sheets as $k => $v) { |
||
| 287 | $TYPES[] = '<Override PartName="/xl/worksheets/sheet'.($k+1). |
||
| 288 | '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'; |
||
| 289 | if (\count( $v['hyperlinks'])) { |
||
| 290 | $TYPES[] = '<Override PartName="/xl/worksheets/_rels/sheet'.($k+1).'.xml.rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'; |
||
| 291 | } |
||
| 292 | } |
||
| 293 | $template = \str_replace('{TYPES}', \implode("\r\n", $TYPES), $template); |
||
| 294 | $this->_writeEntry($fh, $cdrec, $cfilename, $template); |
||
| 295 | $entries++; |
||
| 296 | } elseif ( $cfilename === 'xl/styles.xml' ) { |
||
| 297 | $FONTS = ['<fonts count="'.\count($this->F).'">']; |
||
| 298 | foreach ( $this->F as $f ) { |
||
| 299 | $FONTS[] = '<font><name val="'.$this->defaultFont.'"/><family val="2"/>' |
||
| 300 | . ( $this->defaultFontSize ? '<sz val="'.$this->defaultFontSize.'"/>' : '' ) |
||
| 301 | .( $f & self::F_BOLD ? '<b/>' : '') |
||
| 302 | .( $f & self::F_ITALIC ? '<i/>' : '') |
||
| 303 | .( $f & self::F_UNDERLINE ? '<u/>' : '') |
||
| 304 | .( $f & self::F_STRIKE ? '<strike/>' : '') |
||
| 305 | .( $f & self::F_HYPERLINK ? '<color rgb="FF0563C1"/><u/>' : '') |
||
| 306 | .'</font>'; |
||
| 307 | } |
||
| 308 | $FONTS[] = '</fonts>'; |
||
| 309 | $XF = ['<cellXfs count="'.\count($this->XF).'">']; |
||
| 310 | foreach( $this->XF as $xf ) { |
||
| 311 | $align = ($xf[2] === self::A_LEFT ? ' applyAlignment="1"><alignment horizontal="left"/>' : '') |
||
| 312 | .($xf[2] === self::A_RIGHT ? ' applyAlignment="1"><alignment horizontal="right"/>' : '') |
||
| 313 | .($xf[2] === self::A_CENTER ? ' applyAlignment="1"><alignment horizontal="center"/>' : ''); |
||
| 314 | $XF[] = '<xf numFmtId="'.$xf[0].'" fontId="'.$xf[1].'" fillId="0" borderId="0" xfId="0"' |
||
| 315 | .($xf[0] > 0 ? ' applyNumberFormat="1"' : '') |
||
| 316 | .($align ? $align . '</xf>' : '/>'); |
||
| 317 | |||
| 318 | } |
||
| 319 | $XF[] = '</cellXfs>'; |
||
| 320 | $template = \str_replace(['{FONTS}','{XF}'], [\implode("\r\n", $FONTS), \implode("\r\n", $XF)], $template); |
||
| 321 | $this->_writeEntry($fh, $cdrec, $cfilename, $template); |
||
| 322 | $entries++; |
||
| 323 | } else { |
||
| 324 | $this->_writeEntry($fh, $cdrec, $cfilename, $template); |
||
| 325 | $entries++; |
||
| 326 | } |
||
| 327 | } |
||
| 328 | $before_cd = ftell($fh); |
||
| 329 | fwrite($fh, $cdrec); |
||
| 330 | |||
| 331 | // end of central dir |
||
| 332 | fwrite($fh, $dirSignatureE); |
||
| 333 | fwrite($fh, pack('v', 0)); // number of this disk |
||
| 334 | fwrite($fh, pack('v', 0)); // number of the disk with the start of the central directory |
||
| 335 | fwrite($fh, pack('v', $entries)); // total # of entries "on this disk" |
||
| 336 | fwrite($fh, pack('v', $entries)); // total # of entries overall |
||
| 337 | fwrite($fh, pack('V', \mb_strlen($cdrec,'8bit'))); // size of central dir |
||
| 338 | fwrite($fh, pack('V', $before_cd)); // offset to start of central dir |
||
| 339 | fwrite($fh, pack('v', \mb_strlen($zipComments,'8bit'))); // .zip file comment length |
||
| 340 | fwrite($fh, $zipComments); |
||
| 341 | |||
| 342 | return true; |
||
| 343 | } |
||
| 344 | |||
| 345 | protected function _writeEntry($fh, &$cdrec, $cfilename, $data) { |
||
| 346 | $zipSignature = "\x50\x4b\x03\x04"; // local file header signature |
||
| 347 | $dirSignature = "\x50\x4b\x01\x02"; // central dir header signature |
||
| 348 | |||
| 349 | $e = []; |
||
| 350 | $e['uncsize'] = \mb_strlen($data, '8bit'); |
||
| 351 | |||
| 352 | // if data to compress is too small, just store it |
||
| 353 | if($e['uncsize'] < 256){ |
||
| 354 | $e['comsize'] = $e['uncsize']; |
||
| 355 | $e['vneeded'] = 10; |
||
| 356 | $e['cmethod'] = 0; |
||
| 357 | $zdata = $data; |
||
| 358 | } else{ // otherwise, compress it |
||
| 359 | $zdata = gzcompress($data); |
||
| 360 | $zdata = \substr(\substr($zdata, 0, - 4 ), 2); // fix crc bug (thanks to Eric Mueller) |
||
| 361 | $e['comsize'] = \mb_strlen($zdata, '8bit'); |
||
| 362 | $e['vneeded'] = 10; |
||
| 363 | $e['cmethod'] = 8; |
||
| 364 | } |
||
| 365 | |||
| 366 | $e['bitflag'] = 0; |
||
| 367 | $e['crc_32'] = crc32($data); |
||
| 368 | |||
| 369 | // Convert date and time to DOS Format, and set then |
||
| 370 | $lastmod_timeS = str_pad(decbin(date('s')>=32?date('s')-32:date('s')), 5, '0', STR_PAD_LEFT); |
||
| 371 | $lastmod_timeM = str_pad(decbin(date('i')), 6, '0', STR_PAD_LEFT); |
||
| 372 | $lastmod_timeH = str_pad(decbin(date('H')), 5, '0', STR_PAD_LEFT); |
||
| 373 | $lastmod_dateD = str_pad(decbin(date('d')), 5, '0', STR_PAD_LEFT); |
||
| 374 | $lastmod_dateM = str_pad(decbin(date('m')), 4, '0', STR_PAD_LEFT); |
||
| 375 | $lastmod_dateY = str_pad(decbin(date('Y')-1980), 7, '0', STR_PAD_LEFT); |
||
| 376 | |||
| 377 | # echo "ModTime: $lastmod_timeS-$lastmod_timeM-$lastmod_timeH (".date("s H H").")\n"; |
||
| 378 | # echo "ModDate: $lastmod_dateD-$lastmod_dateM-$lastmod_dateY (".date("d m Y").")\n"; |
||
| 379 | $e['modtime'] = bindec("$lastmod_timeH$lastmod_timeM$lastmod_timeS"); |
||
| 380 | $e['moddate'] = bindec("$lastmod_dateY$lastmod_dateM$lastmod_dateD"); |
||
| 381 | |||
| 382 | $e['offset'] = ftell($fh); |
||
| 383 | |||
| 384 | fwrite($fh, $zipSignature); |
||
| 385 | fwrite($fh, pack('s', $e['vneeded'])); // version_needed |
||
| 386 | fwrite($fh, pack('s', $e['bitflag'])); // general_bit_flag |
||
| 387 | fwrite($fh, pack('s', $e['cmethod'])); // compression_method |
||
| 388 | fwrite($fh, pack('s', $e['modtime'])); // lastmod_time |
||
| 389 | fwrite($fh, pack('s', $e['moddate'])); // lastmod_date |
||
| 390 | fwrite($fh, pack('V', $e['crc_32'])); // crc-32 |
||
| 391 | fwrite($fh, pack('I', $e['comsize'])); // compressed_size |
||
| 392 | fwrite($fh, pack('I', $e['uncsize'])); // uncompressed_size |
||
| 393 | fwrite($fh, pack('s', \mb_strlen($cfilename, '8bit'))); // file_name_length |
||
| 394 | fwrite($fh, pack('s', 0)); // extra_field_length |
||
| 395 | fwrite($fh, $cfilename); // file_name |
||
| 396 | // ignoring extra_field |
||
| 397 | fwrite($fh, $zdata); |
||
| 398 | |||
| 399 | // Append it to central dir |
||
| 400 | $e['external_attributes'] = (\substr($cfilename, -1) === '/'&&!$zdata)?16:32; // Directory or file name |
||
| 401 | $e['comments'] = ''; |
||
| 402 | |||
| 403 | $cdrec .= $dirSignature; |
||
| 404 | $cdrec .= "\x0\x0"; // version made by |
||
| 405 | $cdrec .= pack('v', $e['vneeded']); // version needed to extract |
||
| 406 | $cdrec .= "\x0\x0"; // general bit flag |
||
| 407 | $cdrec .= pack('v', $e['cmethod']); // compression method |
||
| 408 | $cdrec .= pack('v', $e['modtime']); // lastmod time |
||
| 409 | $cdrec .= pack('v', $e['moddate']); // lastmod date |
||
| 410 | $cdrec .= pack('V', $e['crc_32']); // crc32 |
||
| 411 | $cdrec .= pack('V', $e['comsize']); // compressed filesize |
||
| 412 | $cdrec .= pack('V', $e['uncsize']); // uncompressed filesize |
||
| 413 | $cdrec .= pack('v', \mb_strlen($cfilename,'8bit')); // file name length |
||
| 414 | $cdrec .= pack('v', 0); // extra field length |
||
| 415 | $cdrec .= pack('v', \mb_strlen($e['comments'],'8bit')); // file comment length |
||
| 416 | $cdrec .= pack('v', 0); // disk number start |
||
| 417 | $cdrec .= pack('v', 0); // internal file attributes |
||
| 418 | $cdrec .= pack('V', $e['external_attributes']); // internal file attributes |
||
| 419 | $cdrec .= pack('V', $e['offset']); // relative offset of local header |
||
| 420 | $cdrec .= $cfilename; |
||
| 421 | $cdrec .= $e['comments']; |
||
| 422 | } |
||
| 423 | |||
| 424 | protected function _sheetToXML($idx, $template) { |
||
| 425 | // locale floats fr_FR 1.234,56 -> 1234.56 |
||
| 426 | $_loc = setlocale(LC_NUMERIC, 0); |
||
| 427 | setlocale(LC_NUMERIC,'C'); |
||
| 428 | $COLS = []; |
||
| 429 | $ROWS = []; |
||
| 430 | if ( \count($this->sheets[$idx]['rows']) ) { |
||
| 431 | $COLS[] = '<cols>'; |
||
| 432 | $CUR_ROW = 0; |
||
| 433 | $COL = []; |
||
| 434 | foreach( $this->sheets[$idx]['rows'] as $r ) { |
||
| 435 | $CUR_ROW++; |
||
| 436 | $row = '<row r="'.$CUR_ROW.'">'; |
||
| 437 | $CUR_COL = 0; |
||
| 438 | foreach( $r as $v ) { |
||
| 439 | $CUR_COL++; |
||
| 440 | if ( !isset($COL[ $CUR_COL ])) { |
||
| 441 | $COL[ $CUR_COL ] = 0; |
||
| 442 | } |
||
| 443 | if ( $v === null || $v === '' ) { |
||
| 444 | continue; |
||
| 445 | } |
||
| 446 | |||
| 447 | $cname = $this->num2name($CUR_COL) . $CUR_ROW; |
||
| 448 | |||
| 449 | $ct = $cv = null; |
||
| 450 | $N = $F = $A = 0; |
||
| 451 | |||
| 452 | if ( \is_string($v) ) { |
||
| 453 | |||
| 454 | if ( $v[0] === "\0" ) { // RAW value as string |
||
| 455 | $v = \substr($v,1); |
||
| 456 | $vl = \mb_strlen( $v ); |
||
| 457 | } else { |
||
| 458 | if ( \strpos( $v, '<' ) !== false ) { // tags? |
||
| 459 | if ( \strpos( $v, '<b>' ) !== false ) { |
||
| 460 | $F += self::F_BOLD; |
||
| 461 | } |
||
| 462 | if ( \strpos( $v, '<i>' ) !== false ) { |
||
| 463 | $F += self::F_ITALIC; |
||
| 464 | } |
||
| 465 | if ( \strpos( $v, '<u>' ) !== false ) { |
||
| 466 | $F += self::F_UNDERLINE; |
||
| 467 | } |
||
| 468 | if ( \strpos( $v, '<s>' ) !== false ) { |
||
| 469 | $F += self::F_STRIKE; |
||
| 470 | } |
||
| 471 | if ( \strpos( $v, '<left>' ) !== false ) { |
||
| 472 | $A += self::A_LEFT; |
||
| 473 | } |
||
| 474 | if ( \strpos( $v, '<center>' ) !== false ) { |
||
| 475 | $A += self::A_CENTER; |
||
| 476 | } |
||
| 477 | if ( \strpos( $v, '<right>' ) !== false ) { |
||
| 478 | $A += self::A_RIGHT; |
||
| 479 | } |
||
| 480 | if ( \preg_match( '/<a href="(https?:\/\/[^"]+)">(.*?)<\/a>/i', $v, $m ) ) { |
||
| 481 | $h = \explode( '#', $m[1] ); |
||
| 482 | $this->sheets[ $idx ]['hyperlinks'][] = ['ID' => 'rId' . ( \count( $this->sheets[ $idx ]['hyperlinks'] ) + 1 ), 'R' => $cname, 'H' => $h[0], 'L' => isset( $h[1] ) ? $h[1] : '']; |
||
| 483 | $F = self::F_HYPERLINK; // Hyperlink |
||
| 484 | } |
||
| 485 | if ( \preg_match( '/<a href="(mailto?:[^"]+)">(.*?)<\/a>/i', $v, $m ) ) { |
||
| 486 | $this->sheets[ $idx ]['hyperlinks'][] = ['ID' => 'rId' . ( \count( $this->sheets[ $idx ]['hyperlinks'] ) + 1 ), 'R' => $cname, 'H' => $m[1], 'L' => '']; |
||
| 487 | $F = self::F_HYPERLINK; // mailto hyperlink |
||
| 488 | } |
||
| 489 | $v = \strip_tags( $v ); |
||
| 490 | } // tags |
||
| 491 | $vl = \mb_strlen( $v ); |
||
| 492 | if ( $v === '0' || \preg_match( '/^[-+]?[1-9]\d{0,14}$/', $v ) ) { // Integer as General |
||
| 493 | $cv = \ltrim( $v, '+' ); |
||
| 494 | if ( $vl > 10 ) { |
||
| 495 | $N = self::N_INT; // [1] 0 |
||
| 496 | } |
||
| 497 | } elseif ( \preg_match( '/^[-+]?(0|[1-9]\d*)\.(\d+)$/', $v, $m ) ) { |
||
| 498 | $cv = \ltrim( $v, '+' ); |
||
| 499 | if ( \strlen( $m[2] ) < 3 ) { |
||
| 500 | $N = self::N_DEC; |
||
| 501 | } |
||
| 502 | } elseif ( \preg_match( '/^([-+]?\d+)%$/', $v, $m ) ) { |
||
| 503 | $cv = \round( $m[1] / 100, 2 ); |
||
| 504 | $N = self::N_PERCENT_INT; // [9] 0% |
||
| 505 | } elseif ( \preg_match( '/^([-+]?\d+\.\d+)%$/', $v, $m ) ) { |
||
| 506 | $cv = \round( $m[1] / 100, 4 ); |
||
| 507 | $N = self::N_PRECENT_DEC; // [10] 0.00% |
||
| 508 | } elseif ( \preg_match( '/^(\d\d\d\d)-(\d\d)-(\d\d)$/', $v, $m ) ) { |
||
| 509 | $cv = $this->date2excel( $m[1], $m[2], $m[3] ); |
||
| 510 | $N = self::N_DATE; // [14] mm-dd-yy |
||
| 511 | } elseif ( \preg_match( '/^(\d\d)\/(\d\d)\/(\d\d\d\d)$/', $v, $m ) ) { |
||
| 512 | $cv = $this->date2excel( $m[3], $m[2], $m[1] ); |
||
| 513 | $N = self::N_DATE; // [14] mm-dd-yy |
||
| 514 | } elseif ( \preg_match( '/^(\d\d):(\d\d):(\d\d)$/', $v, $m ) ) { |
||
| 515 | $cv = $this->date2excel( 0, 0, 0, $m[1], $m[2], $m[3] ); |
||
| 516 | $N = self::N_TIME; // time |
||
| 517 | } elseif ( \preg_match( '/^(\d\d\d\d)-(\d\d)-(\d\d) (\d\d):(\d\d):(\d\d)$/', $v, $m ) ) { |
||
| 518 | $cv = $this->date2excel( $m[1], $m[2], $m[3], $m[4], $m[5], $m[6] ); |
||
| 519 | $N = self::N_DATETIME; // [22] m/d/yy h:mm |
||
| 520 | } elseif ( \preg_match( '/^(\d\d)\/(\d\d)\/(\d\d\d\d) (\d\d):(\d\d):(\d\d)$/', $v, $m ) ) { |
||
| 521 | $cv = $this->date2excel( $m[3], $m[2], $m[1], $m[4], $m[5], $m[6] ); |
||
| 522 | $N = self::N_DATETIME; // [22] m/d/yy h:mm |
||
| 523 | } elseif ( \preg_match( '/^[0-9+-.]+$/', $v ) ) { // Long ? |
||
| 524 | $A = self::A_RIGHT; |
||
| 525 | } elseif ( \preg_match( '/^https?:\/\/\S+$/i', $v ) ) { |
||
| 526 | $h = \explode( '#', $v ); |
||
| 527 | $this->sheets[ $idx ]['hyperlinks'][] = ['ID' => 'rId' . ( \count( $this->sheets[ $idx ]['hyperlinks'] ) + 1 ), 'R' => $cname, 'H' => $h[0], 'L' => isset( $h[1] ) ? $h[1] : '']; |
||
| 528 | $F = self::F_HYPERLINK; // Hyperlink |
||
| 529 | } elseif ( \preg_match( "/^[a-zA-Z0-9_\.\-]+@([a-zA-Z0-9][a-zA-Z0-9\-]*\.)+[a-zA-Z]{2,}$/", $v ) ) { |
||
| 530 | $this->sheets[ $idx ]['hyperlinks'][] = ['ID' => 'rId' . ( \count( $this->sheets[ $idx ]['hyperlinks'] ) + 1 ), 'R' => $cname, 'H' => 'mailto:' . $v, 'L' => '']; |
||
| 531 | $F = self::F_HYPERLINK; // Hyperlink |
||
| 532 | } |
||
| 533 | } |
||
| 534 | if ( !$cv) { |
||
| 535 | |||
| 536 | $v = $this->esc( $v ); |
||
| 537 | |||
| 538 | if ( \mb_strlen( $v ) > 160 ) { |
||
| 539 | $ct = 'inlineStr'; |
||
| 540 | $cv = $v; |
||
| 541 | } else { |
||
| 542 | $ct = 's'; // shared string |
||
| 543 | $cv = false; |
||
| 544 | $skey = '~' . $v; |
||
| 545 | if ( isset( $this->SI_KEYS[ $skey ] ) ) { |
||
| 546 | $cv = $this->SI_KEYS[ $skey ]; |
||
| 547 | } |
||
| 548 | if ( $cv === false ) { |
||
| 549 | $this->SI[] = $v; |
||
| 550 | $cv = \count( $this->SI ) - 1; |
||
| 551 | $this->SI_KEYS[ $skey ] = $cv; |
||
| 552 | } |
||
| 553 | } |
||
| 554 | } |
||
| 555 | } elseif ( \is_int( $v ) ) { |
||
| 556 | $vl = \mb_strlen( (string) $v ); |
||
| 557 | $cv = $v; |
||
| 558 | } elseif ( \is_float( $v ) ) { |
||
| 559 | $vl = \mb_strlen( (string) $v ); |
||
| 560 | $cv = $v; |
||
| 561 | } elseif ( $v instanceof DateTime ) { |
||
| 562 | $vl = 16; |
||
| 563 | $cv = $this->date2excel( $v->format('Y'), $v->format('m'), $v->format('d'), $v->format('H'), $v->format('i'), $v->format('s') ); |
||
| 564 | $N = self::N_DATETIME; // [22] m/d/yy h:mm |
||
| 565 | } else { |
||
| 566 | continue; |
||
| 567 | } |
||
| 568 | |||
| 569 | $COL[ $CUR_COL ] = max( $vl, $COL[ $CUR_COL ] ); |
||
| 570 | |||
| 571 | $cs = 0; |
||
| 572 | if ( $N + $F + $A > 0 ) { |
||
| 573 | |||
| 574 | if ( isset($this->F_KEYS[ $F ] ) ) { |
||
| 575 | $cf = $this->F_KEYS[ $F ]; |
||
| 576 | } else { |
||
| 577 | $cf = \count($this->F); |
||
| 578 | $this->F_KEYS[$F] = $cf; |
||
| 579 | $this->F[] = $F; |
||
| 580 | } |
||
| 581 | $NFA = 'N' . $N . 'F' . $cf . 'A' . $A; |
||
| 582 | if ( isset( $this->XF_KEYS[ $NFA ] ) ) { |
||
| 583 | $cs = $this->XF_KEYS[ $NFA ]; |
||
| 584 | } |
||
| 585 | if ( $cs === 0 ) { |
||
| 586 | $cs = \count( $this->XF ); |
||
| 587 | $this->XF_KEYS[ $NFA ] = $cs; |
||
| 588 | $this->XF[] = [$N, $cf, $A]; |
||
| 589 | } |
||
| 590 | } |
||
| 591 | |||
| 592 | $row .= '<c r="' . $cname . '"'.($ct ? ' t="'.$ct.'"' : '').($cs ? ' s="'.$cs.'"' : '').'>' |
||
| 593 | .($ct === 'inlineStr' ? '<is><t>'.$cv.'</t></is>' : '<v>' . $cv . '</v>')."</c>\r\n"; |
||
| 594 | } |
||
| 595 | $ROWS[] = $row . "</row>\r\n"; |
||
| 596 | } |
||
| 597 | foreach ( $COL as $k => $max ) { |
||
| 598 | $COLS[] = '<col min="'.$k.'" max="'.$k.'" width="'.min( $max+1, 60).'" />'; |
||
| 599 | } |
||
| 600 | $COLS[] = '</cols>'; |
||
| 601 | $REF = 'A1:'.$this->num2name(\count($COLS)) . $CUR_ROW; |
||
| 602 | } else { |
||
| 603 | $ROWS[] = '<row r="1"><c r="A1" t="s"><v>0</v></c></row>'; |
||
| 604 | $REF = 'A1:A1'; |
||
| 605 | } |
||
| 606 | $HYPERLINKS = []; |
||
| 607 | if ( \count( $this->sheets[$idx]['hyperlinks']) ) { |
||
| 608 | $HYPERLINKS[] = '<hyperlinks>'; |
||
| 609 | foreach ( $this->sheets[$idx]['hyperlinks'] as $h ) { |
||
| 610 | $HYPERLINKS[] = '<hyperlink ref="' . $h['R'] . '" r:id="' . $h['ID'] . '" location="' . $this->esc( $h['L'] ) . '" display="' . $this->esc( $h['H'] . ( $h['L'] ? ' - ' . $h['L'] : '' ) ) . '" />'; |
||
| 611 | } |
||
| 612 | $HYPERLINKS[] = '</hyperlinks>'; |
||
| 613 | } |
||
| 614 | //restore locale |
||
| 615 | setlocale(LC_NUMERIC, $_loc); |
||
| 616 | |||
| 617 | return \str_replace(['{REF}','{COLS}','{ROWS}','{HYPERLINKS}'], |
||
| 618 | [ $REF, \implode("\r\n", $COLS), \implode("\r\n",$ROWS), \implode("\r\n", $HYPERLINKS) ], |
||
| 619 | $template ); |
||
| 620 | } |
||
| 621 | |||
| 622 | public function num2name($num) { |
||
| 623 | $numeric = ($num - 1) % 26; |
||
| 624 | $letter = \chr( 65 + $numeric ); |
||
| 625 | $num2 = (int) ( ($num-1) / 26 ); |
||
| 626 | if ( $num2 > 0 ) { |
||
| 627 | return $this->num2name( $num2 ) . $letter; |
||
| 628 | } |
||
| 629 | return $letter; |
||
| 630 | } |
||
| 631 | |||
| 632 | public function date2excel($year, $month, $day, $hours=0, $minutes=0, $seconds=0) { |
||
| 633 | $excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400; |
||
| 634 | |||
| 635 | if ( $year === 0 ) { |
||
| 636 | return $excelTime; |
||
| 637 | } |
||
| 638 | |||
| 639 | // self::CALENDAR_WINDOWS_1900 |
||
| 640 | $excel1900isLeapYear = True; |
||
| 641 | if (((int)$year === 1900) && ($month <= 2)) { $excel1900isLeapYear = False; } |
||
| 642 | $myExcelBaseDate = 2415020; |
||
| 643 | |||
| 644 | // Julian base date Adjustment |
||
| 645 | if ($month > 2) { |
||
| 646 | $month -= 3; |
||
| 647 | } else { |
||
| 648 | $month += 9; |
||
| 649 | --$year; |
||
| 650 | } |
||
| 651 | // Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0) |
||
| 652 | $century = \substr($year,0,2); |
||
| 653 | $decade = \substr($year,2,2); |
||
| 654 | $excelDate = \floor((146097 * $century) / 4) + \floor((1461 * $decade) / 4) + \floor((153 * $month + 2) / 5) + $day + 1721119 - $myExcelBaseDate + $excel1900isLeapYear; |
||
| 655 | |||
| 656 | return (float) $excelDate + $excelTime; |
||
| 657 | } |
||
| 658 | public function setDefaultFont( $name ) { |
||
| 659 | $this->defaultFont = $name; |
||
| 660 | return $this; |
||
| 661 | } |
||
| 662 | public function setDefaultFontSize( $size ) { |
||
| 663 | $this->defaultFontSize = $size; |
||
| 664 | return $this; |
||
| 665 | } |
||
| 666 | public function esc( $str ) { |
||
| 668 | } |
||
| 669 | } |
||
| 670 |
Loading history...