ellumilel /
php-excel-writer
Complex classes like ExcelWriter 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. You can also have a look at the cohesion graph to spot any un-connected, or weakly-connected components.
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 ExcelWriter, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 14 | class ExcelWriter |
||
| 15 | { |
||
| 16 | /** |
||
| 17 | * @link http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx |
||
| 18 | */ |
||
| 19 | const EXCEL_MAX_ROW = 1048576; |
||
| 20 | const EXCEL_MAX_RANGE = 2147483647; |
||
| 21 | const EXCEL_MAX_COL = 16384; |
||
| 22 | |||
| 23 | /** @var string */ |
||
| 24 | private $urlSchemaFormat = 'http://schemas.openxmlformats.org/officeDocument/2006'; |
||
| 25 | |||
| 26 | /** @var string */ |
||
| 27 | protected $author ='Unknown Author'; |
||
| 28 | /** @var array */ |
||
| 29 | protected $sheets = []; |
||
| 30 | /** @var array */ |
||
| 31 | protected $sharedStrings = [];//unique set |
||
| 32 | /** @var int */ |
||
| 33 | protected $sharedStringCount = 0;//count of non-unique references to the unique set |
||
| 34 | /** @var array */ |
||
| 35 | protected $tempFiles = []; |
||
| 36 | /** @var array */ |
||
| 37 | protected $cellFormats = [];//contains excel format like YYYY-MM-DD HH:MM:SS |
||
| 38 | /** @var array */ |
||
| 39 | protected $cellTypes = [];//contains friendly format like datetime |
||
| 40 | /** @var string */ |
||
| 41 | protected $currentSheet = ''; |
||
| 42 | /** @var null */ |
||
| 43 | protected $tmpDir = null; |
||
| 44 | |||
| 45 | /** |
||
| 46 | * ExcelWriter constructor. |
||
| 47 | * @throws \Exception |
||
| 48 | */ |
||
| 49 | public function __construct() |
||
| 61 | |||
| 62 | /** |
||
| 63 | * @param string $author |
||
| 64 | */ |
||
| 65 | public function setAuthor($author = '') |
||
| 69 | |||
| 70 | public function __destruct() |
||
| 80 | |||
| 81 | /** |
||
| 82 | * @param $dir |
||
| 83 | */ |
||
| 84 | public function setTmpDir($dir) |
||
| 88 | |||
| 89 | /** |
||
| 90 | * Return tmpFileName |
||
| 91 | * @return string |
||
| 92 | */ |
||
| 93 | protected function tempFilename() |
||
| 101 | |||
| 102 | public function writeToStdOut() |
||
| 108 | |||
| 109 | /** |
||
| 110 | * @return string |
||
| 111 | */ |
||
| 112 | public function writeToString() |
||
| 120 | |||
| 121 | /** |
||
| 122 | * @param string $filename |
||
| 123 | */ |
||
| 124 | public function writeToFile($filename) |
||
| 125 | { |
||
| 126 | foreach ($this->sheets as $sheetName => $sheet) { |
||
| 127 | $this->finalizeSheet($sheetName);//making sure all footers have been written |
||
| 128 | } |
||
| 129 | if (file_exists($filename) && is_writable($filename)) { |
||
| 130 | unlink($filename); |
||
| 131 | } |
||
| 132 | |||
| 133 | $zip = new \ZipArchive(); |
||
| 134 | if (empty($this->sheets) || !$zip->open($filename, \ZipArchive::CREATE)) { |
||
| 135 | self::log("Error in ".__CLASS__."::".__FUNCTION__.", no worksheets defined or unable to create zip."); |
||
| 136 | return; |
||
| 137 | } |
||
| 138 | |||
| 139 | $app = new App(); |
||
| 140 | $core = new Core(); |
||
| 141 | |||
| 142 | $contentTypes = new ContentTypes(!empty($this->sharedStrings)); |
||
| 143 | $contentTypes->setSheet($this->sheets); |
||
| 144 | |||
| 145 | $rels = new Relationships(!empty($this->sharedStrings)); |
||
| 146 | $rels->setSheet($this->sheets); |
||
| 147 | |||
| 148 | $zip->addEmptyDir("docProps/"); |
||
| 149 | $zip->addFromString("docProps/app.xml", $app->buildAppXML()); |
||
| 150 | $zip->addFromString("docProps/core.xml", $core->buildCoreXML()); |
||
| 151 | $zip->addEmptyDir("_rels/"); |
||
| 152 | $zip->addFromString("_rels/.rels", $rels->buildRelationshipsXML()); |
||
| 153 | $zip->addEmptyDir("xl/worksheets/"); |
||
| 154 | foreach ($this->sheets as $sheet) { |
||
| 155 | /** @var Sheet $sheet */ |
||
| 156 | $zip->addFile($sheet->getFilename(), "xl/worksheets/".$sheet->getXmlName()); |
||
| 157 | } |
||
| 158 | if (!empty($this->sharedStrings)) { |
||
| 159 | $zip->addFile( |
||
| 160 | $this->writeSharedStringsXML(), |
||
| 161 | "xl/sharedStrings.xml" |
||
| 162 | ); |
||
| 163 | } |
||
| 164 | $zip->addFromString("xl/workbook.xml", self::buildWorkbookXML()); |
||
| 165 | $zip->addFile( |
||
| 166 | $this->writeStylesXML(), |
||
| 167 | "xl/styles.xml" |
||
| 168 | ); |
||
| 169 | $zip->addFromString("[Content_Types].xml", $contentTypes->buildContentTypesXML()); |
||
| 170 | $zip->addEmptyDir("xl/_rels/"); |
||
| 171 | $zip->addFromString("xl/_rels/workbook.xml.rels", $rels->buildWorkbookRelationshipsXML()); |
||
| 172 | $zip->close(); |
||
| 173 | } |
||
| 174 | |||
| 175 | /** |
||
| 176 | * @param string $sheetName |
||
| 177 | */ |
||
| 178 | protected function initializeSheet($sheetName) |
||
| 179 | { |
||
| 180 | if ($this->currentSheet == $sheetName || isset($this->sheets[$sheetName])) { |
||
| 181 | return; |
||
| 182 | } |
||
| 183 | $sheetFilename = $this->tempFilename(); |
||
| 184 | $sheetXmlName = 'sheet' . (count($this->sheets) + 1).".xml"; |
||
| 185 | $sheetObj = new Sheet(); |
||
| 186 | $sheetObj |
||
| 187 | ->setFilename($sheetFilename) |
||
| 188 | ->setSheetName($sheetName) |
||
| 189 | ->setXmlName($sheetXmlName) |
||
| 190 | ->setWriter(new Writer($sheetFilename)) |
||
| 191 | ; |
||
| 192 | $this->sheets[$sheetName] = $sheetObj; |
||
| 193 | /** @var Sheet $sheet */ |
||
| 194 | $sheet = &$this->sheets[$sheetName]; |
||
| 195 | $selectedTab = count($this->sheets) == 1 ? 'true' : 'false';//only first sheet is selected |
||
| 196 | $maxCell = ExcelWriter::xlsCell(self::EXCEL_MAX_ROW, self::EXCEL_MAX_COL);//XFE1048577 |
||
| 197 | $sheet->getWriter()->write('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'."\n"); |
||
| 198 | $sheet->getWriter()->write( |
||
| 199 | '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" |
||
| 200 | xmlns:r="'.$this->urlSchemaFormat.'/relationships">' |
||
| 201 | ); |
||
| 202 | $sheet->getWriter()->write('<sheetPr filterMode="false">'); |
||
| 203 | $sheet->getWriter()->write('<pageSetUpPr fitToPage="false"/>'); |
||
| 204 | $sheet->getWriter()->write('</sheetPr>'); |
||
| 205 | $sheet->setMaxCellTagStart($sheet->getWriter()->fTell()); |
||
| 206 | $sheet->getWriter()->write('<dimension ref="A1:'.$maxCell.'"/>'); |
||
| 207 | $sheet->setMaxCellTagEnd($sheet->getWriter()->fTell()); |
||
| 208 | $sheet->getWriter()->write('<sheetViews>'); |
||
| 209 | $sheet->getWriter()->write( |
||
| 210 | '<sheetView colorId="64" defaultGridColor="true" rightToLeft="false" showFormulas="false" |
||
| 211 | showGridLines="true" showOutlineSymbols="true" showRowColHeaders="true" showZeros="true" |
||
| 212 | tabSelected="'.$selectedTab.'" topLeftCell="A1" view="normal" windowProtection="false" |
||
| 213 | workbookViewId="0" zoomScale="100" zoomScaleNormal="100" zoomScalePageLayoutView="100">' |
||
| 214 | ); |
||
| 215 | $sheet->getWriter()->write('<selection activeCell="A1" activeCellId="0" pane="topLeft" sqref="A1"/>'); |
||
| 216 | $sheet->getWriter()->write('</sheetView>'); |
||
| 217 | $sheet->getWriter()->write('</sheetViews>'); |
||
| 218 | $sheet->getWriter()->write('<cols>'); |
||
| 219 | $sheet->getWriter()->write('<col collapsed="false" hidden="false" max="1025" min="1" style="0" width="11.5"/>'); |
||
| 220 | $sheet->getWriter()->write('</cols>'); |
||
| 221 | $sheet->getWriter()->write('<sheetData>'); |
||
| 222 | } |
||
| 223 | |||
| 224 | /** |
||
| 225 | * @param $cellFormat |
||
| 226 | * |
||
| 227 | * @return string |
||
| 228 | */ |
||
| 229 | private function determineCellType($cellFormat) |
||
| 230 | { |
||
| 231 | $cellFormat = str_replace("[RED]", "", $cellFormat); |
||
| 232 | if ($cellFormat == 'GENERAL') { |
||
| 233 | return 'string'; |
||
| 234 | } |
||
| 235 | if ($cellFormat == '0') { |
||
| 236 | return 'numeric'; |
||
| 237 | } |
||
| 238 | if (preg_match("/[H]{1,2}:[M]{1,2}/", $cellFormat)) { |
||
| 239 | return 'datetime'; |
||
| 240 | } |
||
| 241 | if (preg_match("/[M]{1,2}:[S]{1,2}/", $cellFormat)) { |
||
| 242 | return 'datetime'; |
||
| 243 | } |
||
| 244 | if (preg_match("/[YY]{2,4}/", $cellFormat)) { |
||
| 245 | return 'date'; |
||
| 246 | } |
||
| 247 | if (preg_match("/[D]{1,2}/", $cellFormat)) { |
||
| 248 | return 'date'; |
||
| 249 | } |
||
| 250 | if (preg_match("/[M]{1,2}/", $cellFormat)) { |
||
| 251 | return 'date'; |
||
| 252 | } |
||
| 253 | if (preg_match("/$/", $cellFormat)) { |
||
| 254 | return 'currency'; |
||
| 255 | } |
||
| 256 | if (preg_match("/%/", $cellFormat)) { |
||
| 257 | return 'percent'; |
||
| 258 | } |
||
| 259 | if (preg_match("/0/", $cellFormat)) { |
||
| 260 | return 'numeric'; |
||
| 261 | } |
||
| 262 | |||
| 263 | return 'string'; |
||
| 264 | } |
||
| 265 | |||
| 266 | /** |
||
| 267 | * @todo check escaping |
||
| 268 | * |
||
| 269 | * @param $cellFormat |
||
| 270 | * |
||
| 271 | * @return string |
||
| 272 | */ |
||
| 273 | private function escapeCellFormat($cellFormat) |
||
| 274 | { |
||
| 275 | $ignoreUntil = ''; |
||
| 276 | $escaped = ''; |
||
| 277 | for ($i = 0, $ix = strlen($cellFormat); $i < $ix; $i++) { |
||
| 278 | $c = $cellFormat[$i]; |
||
| 279 | if ($ignoreUntil == '' && $c == '[') { |
||
| 280 | $ignoreUntil = ']'; |
||
| 281 | } else { |
||
| 282 | if ($ignoreUntil == '' && $c == '"') { |
||
| 283 | $ignoreUntil = '"'; |
||
| 284 | } else { |
||
| 285 | if ($ignoreUntil == $c) { |
||
| 286 | $ignoreUntil = ''; |
||
| 287 | } |
||
| 288 | } |
||
| 289 | } |
||
| 290 | if ($ignoreUntil == '' && |
||
| 291 | ($c == ' ' || $c == '-' || $c == '(' || $c == ')') && |
||
| 292 | ($i == 0 || $cellFormat[$i - 1] != '_') |
||
| 293 | ) { |
||
| 294 | $escaped .= "\\".$c; |
||
| 295 | } else { |
||
| 296 | $escaped .= $c; |
||
| 297 | } |
||
| 298 | } |
||
| 299 | |||
| 300 | return $escaped; |
||
| 301 | } |
||
| 302 | |||
| 303 | /** |
||
| 304 | * backwards compatibility |
||
| 305 | * |
||
| 306 | * @param $cellFormat |
||
| 307 | * |
||
| 308 | * @return int|mixed |
||
| 309 | */ |
||
| 310 | private function addCellFormat($cellFormat) |
||
| 311 | { |
||
| 312 | switch ($cellFormat) { |
||
| 313 | case 'string': |
||
| 314 | $cellFormat = 'GENERAL'; |
||
| 315 | break; |
||
| 316 | case 'integer': |
||
| 317 | $cellFormat = '0'; |
||
| 318 | break; |
||
| 319 | case 'date': |
||
| 320 | $cellFormat = 'YYYY-MM-DD'; |
||
| 321 | break; |
||
| 322 | case 'datetime': |
||
| 323 | $cellFormat = 'YYYY-MM-DD HH:MM:SS'; |
||
| 324 | break; |
||
| 325 | case 'dollar': |
||
| 326 | $cellFormat = '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00'; |
||
| 327 | break; |
||
| 328 | case 'money': |
||
| 329 | $cellFormat = '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00'; |
||
| 330 | break; |
||
| 331 | case 'euro': |
||
| 332 | $cellFormat = '#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]'; |
||
| 333 | break; |
||
| 334 | case 'NN': |
||
| 335 | $cellFormat = 'DDD'; |
||
| 336 | break; |
||
| 337 | case 'NNN': |
||
| 338 | $cellFormat = 'DDDD'; |
||
| 339 | break; |
||
| 340 | case 'NNNN': |
||
| 341 | $cellFormat = 'DDDD", "'; |
||
| 342 | break; |
||
| 343 | } |
||
| 344 | |||
| 345 | $cellFormat = strtoupper($cellFormat); |
||
| 346 | $position = array_search($cellFormat, $this->cellFormats, $strict = true); |
||
| 347 | if ($position === false) { |
||
| 348 | $position = count($this->cellFormats); |
||
| 349 | $this->cellFormats[] = $this->escapeCellFormat($cellFormat); |
||
| 350 | $this->cellTypes[] = $this->determineCellType($cellFormat); |
||
| 351 | } |
||
| 352 | |||
| 353 | return $position; |
||
| 354 | } |
||
| 355 | |||
| 356 | /** |
||
| 357 | * @param string $sheetName |
||
| 358 | * @param array $headerTypes |
||
| 359 | * @param bool $suppressRow |
||
| 360 | */ |
||
| 361 | public function writeSheetHeader($sheetName, array $headerTypes, $suppressRow = false) |
||
| 362 | { |
||
| 363 | if (empty($sheetName) || empty($headerTypes) || !empty($this->sheets[$sheetName])) { |
||
| 364 | return; |
||
| 365 | } |
||
| 366 | $this->initializeSheet($sheetName); |
||
| 367 | /** @var Sheet $sheet */ |
||
| 368 | $sheet = &$this->sheets[$sheetName]; |
||
| 369 | $sheet->setColumns([]); |
||
| 370 | foreach ($headerTypes as $val) { |
||
| 371 | $sheet->setColumn($this->addCellFormat($val)); |
||
| 372 | } |
||
| 373 | if (!$suppressRow) { |
||
| 374 | $this->writeRowHeader($sheet, array_keys($headerTypes)); |
||
| 375 | $sheet->increaseRowCount(); |
||
| 376 | } |
||
| 377 | $this->currentSheet = $sheetName; |
||
| 378 | } |
||
| 379 | |||
| 380 | /** |
||
| 381 | * @param Sheet $sheet |
||
| 382 | * @param array $headerRow |
||
| 383 | */ |
||
| 384 | private function writeRowHeader(Sheet $sheet, $headerRow) |
||
| 385 | { |
||
| 386 | $sheet->getWriter()->write( |
||
| 387 | '<row collapsed="false" customFormat="false" |
||
| 388 | customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="'.(1).'">' |
||
| 389 | ); |
||
| 390 | foreach ($headerRow as $k => $v) { |
||
| 391 | $this->writeCell($sheet->getWriter(), 0, $k, $v, $cell_format_index = '0'); |
||
| 392 | } |
||
| 393 | $sheet->getWriter()->write('</row>'); |
||
| 394 | } |
||
| 395 | |||
| 396 | /** |
||
| 397 | * @param string $sheetName |
||
| 398 | * @param array $row |
||
| 399 | */ |
||
| 400 | public function writeSheetRow($sheetName, array $row) |
||
| 401 | { |
||
| 402 | if (empty($sheetName) || empty($row)) { |
||
| 403 | return; |
||
| 404 | } |
||
| 405 | $this->initializeSheet($sheetName); |
||
| 406 | /** @var Sheet $sheet */ |
||
| 407 | $sheet = &$this->sheets[$sheetName]; |
||
| 408 | $columns = $sheet->getColumns(); |
||
| 409 | if (empty($columns)) { |
||
| 410 | $sheet->setColumns(array_fill($from = 0, $until = count($row), '0'));//'0'=>'string' |
||
| 411 | } |
||
| 412 | $sheet->getWriter()->write( |
||
| 413 | '<row collapsed="false" customFormat="false" customHeight="false" |
||
| 414 | hidden="false" ht="12.1" outlineLevel="0" r="'.($sheet->getRowCount() + 1).'">' |
||
| 415 | ); |
||
| 416 | $column_count = 0; |
||
| 417 | $sheetColumns = $sheet->getColumns(); |
||
| 418 | foreach ($row as $k => $v) { |
||
| 419 | $this->writeCell( |
||
| 420 | $sheet->getWriter(), |
||
| 421 | $sheet->getRowCount(), |
||
| 422 | $column_count, |
||
| 423 | $v, |
||
| 424 | $sheetColumns[$column_count] |
||
| 425 | ); |
||
| 426 | $column_count++; |
||
| 427 | } |
||
| 428 | $sheet->getWriter()->write('</row>'); |
||
| 429 | $sheet->increaseRowCount(); |
||
| 430 | $this->currentSheet = $sheetName; |
||
| 431 | } |
||
| 432 | |||
| 433 | /** |
||
| 434 | * @param string $sheetName |
||
| 435 | */ |
||
| 436 | protected function finalizeSheet($sheetName) |
||
| 437 | { |
||
| 438 | if (empty($sheetName) || $this->sheets[$sheetName]->getFinalized()) { |
||
| 439 | return; |
||
| 440 | } |
||
| 441 | /** @var Sheet $sheet */ |
||
| 442 | $sheet = &$this->sheets[$sheetName]; |
||
| 443 | $sheet->getWriter()->write('</sheetData>'); |
||
| 444 | $mergeCells = $sheet->getMergeCells(); |
||
| 445 | if (!empty($mergeCells)) { |
||
| 446 | $sheet->getWriter()->write('<mergeCells>'); |
||
| 447 | foreach ($mergeCells as $range) { |
||
| 448 | $sheet->getWriter()->write('<mergeCell ref="'.$range.'"/>'); |
||
| 449 | } |
||
| 450 | $sheet->getWriter()->write('</mergeCells>'); |
||
| 451 | } |
||
| 452 | $sheet->getWriter()->write( |
||
| 453 | '<printOptions headings="false" gridLines="false" gridLinesSet="true" horizontalCentered="false" |
||
| 454 | verticalCentered="false"/>' |
||
| 455 | ); |
||
| 456 | $sheet->getWriter()->write( |
||
| 457 | '<pageMargins left="0.5" right="0.5" top="1.0" bottom="1.0" header="0.5" footer="0.5"/>' |
||
| 458 | ); |
||
| 459 | $sheet->getWriter()->write( |
||
| 460 | '<pageSetup blackAndWhite="false" cellComments="none" copies="1" draft="false" firstPageNumber="1" |
||
| 461 | fitToHeight="1" fitToWidth="1" horizontalDpi="300" orientation="portrait" pageOrder="downThenOver" |
||
| 462 | paperSize="1" scale="100" useFirstPageNumber="true" usePrinterDefaults="false" verticalDpi="300"/>' |
||
| 463 | ); |
||
| 464 | $sheet->getWriter()->write('<headerFooter differentFirst="false" differentOddEven="false">'); |
||
| 465 | $sheet->getWriter()->write( |
||
| 466 | '<oddHeader>&C&"Times New Roman,Regular"&12&A</oddHeader>' |
||
| 467 | ); |
||
| 468 | $sheet->getWriter()->write( |
||
| 469 | '<oddFooter>&C&"Times New Roman,Regular"&12Page &P</oddFooter>' |
||
| 470 | ); |
||
| 471 | $sheet->getWriter()->write('</headerFooter>'); |
||
| 472 | $sheet->getWriter()->write('</worksheet>'); |
||
| 473 | $maxCell = self::xlsCell($sheet->getRowCount() - 1, count($sheet->getColumns()) - 1); |
||
| 474 | $maxCellTag = '<dimension ref="A1:'.$maxCell.'"/>'; |
||
| 475 | $paddingLength = $sheet->getMaxCellTagEnd() - $sheet->getMaxCellTagStart() - strlen($maxCellTag); |
||
| 476 | $sheet->getWriter()->fSeek($sheet->getMaxCellTagStart()); |
||
| 477 | $sheet->getWriter()->write($maxCellTag.str_repeat(" ", $paddingLength)); |
||
| 478 | $sheet->getWriter()->close(); |
||
| 479 | $sheet->setFinalized(true); |
||
| 480 | } |
||
| 481 | |||
| 482 | /** |
||
| 483 | * @param string $sheetName |
||
| 484 | * @param int $startCellRow |
||
| 485 | * @param int $startCellColumn |
||
| 486 | * @param int $endCellRow |
||
| 487 | * @param int $endCellColumn |
||
| 488 | */ |
||
| 489 | public function markMergedCell($sheetName, $startCellRow, $startCellColumn, $endCellRow, $endCellColumn) |
||
| 490 | { |
||
| 491 | if (empty($sheetName) || $this->sheets[$sheetName]->getFinalized()) { |
||
| 492 | return; |
||
| 493 | } |
||
| 494 | $this->initializeSheet($sheetName); |
||
| 495 | /** @var Sheet $sheet */ |
||
| 496 | $sheet = &$this->sheets[$sheetName]; |
||
| 497 | $startCell = self::xlsCell($startCellRow, $startCellColumn); |
||
| 498 | $endCell = self::xlsCell($endCellRow, $endCellColumn); |
||
| 499 | $sheet->setMergeCells($startCell.":".$endCell); |
||
| 500 | } |
||
| 501 | |||
| 502 | /** |
||
| 503 | * @param array $data |
||
| 504 | * @param string $sheetName |
||
| 505 | * @param array $headerTypes |
||
| 506 | */ |
||
| 507 | public function writeSheet(array $data, $sheetName = '', array $headerTypes = []) |
||
| 508 | { |
||
| 509 | $sheetName = empty($sheetName) ? 'Sheet1' : $sheetName; |
||
| 510 | $data = empty($data) ? [['']] : $data; |
||
| 511 | if (!empty($headerTypes)) { |
||
| 512 | $this->writeSheetHeader($sheetName, $headerTypes); |
||
| 513 | } |
||
| 514 | foreach ($data as $i => $row) { |
||
| 515 | $this->writeSheetRow($sheetName, $row); |
||
| 516 | } |
||
| 517 | $this->finalizeSheet($sheetName); |
||
| 518 | } |
||
| 519 | |||
| 520 | /** |
||
| 521 | * @param Writer $file |
||
| 522 | * @param $rowNumber |
||
| 523 | * @param $columnNumber |
||
| 524 | * @param $value |
||
| 525 | * @param $cellIndex |
||
| 526 | */ |
||
| 527 | protected function writeCell( |
||
| 528 | Writer $file, |
||
| 529 | $rowNumber, |
||
| 530 | $columnNumber, |
||
| 531 | $value, |
||
| 532 | $cellIndex |
||
| 533 | ) { |
||
| 534 | $cellType = $this->cellTypes[$cellIndex]; |
||
| 535 | $cellName = self::xlsCell($rowNumber, $columnNumber); |
||
| 536 | if (!is_scalar($value) || $value === '') { |
||
| 537 | $file->write('<c r="'.$cellName.'" s="'.$cellIndex.'"/>'); |
||
| 538 | } elseif (is_string($value) && $value{0} == '=') { |
||
| 539 | $file->write( |
||
| 540 | sprintf('<c r="%s" s="%s" t="s"><f>%s</f></c>', $cellName, $cellIndex, self::xmlspecialchars($value)) |
||
| 541 | ); |
||
| 542 | } elseif ($cellType == 'date') { |
||
| 543 | $file->write( |
||
| 544 | sprintf('<c r="%s" s="%s" t="n"><v>%s</v></c>', $cellName, $cellIndex, self::convertDateTime($value)) |
||
| 545 | ); |
||
| 546 | } elseif ($cellType == 'datetime') { |
||
| 547 | $file->write( |
||
| 548 | '<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.self::convertDateTime($value).'</v></c>' |
||
| 549 | ); |
||
| 550 | } elseif ($cellType == 'currency' || $cellType == 'percent' || $cellType == 'numeric') { |
||
| 551 | $file->write( |
||
| 552 | '<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.self::xmlspecialchars($value).'</v></c>' |
||
| 553 | ); |
||
| 554 | } else { |
||
| 555 | if (!is_string($value)) { |
||
| 556 | $file->write('<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.($value * 1).'</v></c>'); |
||
| 557 | } else { |
||
| 558 | if ($value{0} != '0' && $value{0} != '+' && filter_var( |
||
| 559 | $value, |
||
| 560 | FILTER_VALIDATE_INT, |
||
| 561 | ['options' => ['max_range' => self::EXCEL_MAX_RANGE]] |
||
| 562 | )) { |
||
| 563 | $file->write('<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.($value * 1).'</v></c>'); |
||
| 564 | } else { |
||
| 565 | $file->write( |
||
| 566 | '<c r="'.$cellName.'" s="'.$cellIndex.'" t="s"><v>'.self::xmlspecialchars( |
||
| 567 | $this->setSharedString($value) |
||
| 568 | ).'</v></c>' |
||
| 569 | ); |
||
| 570 | } |
||
| 571 | } |
||
| 572 | } |
||
| 573 | } |
||
| 574 | |||
| 575 | /** |
||
| 576 | * @return string |
||
| 577 | */ |
||
| 578 | protected function writeStylesXML() |
||
| 579 | { |
||
| 580 | $temporaryFilename = $this->tempFilename(); |
||
| 581 | $file = new Writer($temporaryFilename); |
||
| 582 | $file->write('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'."\n"); |
||
| 583 | $file->write('<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'); |
||
| 584 | $file->write('<numFmts count="'.count($this->cellFormats).'">'); |
||
| 585 | foreach ($this->cellFormats as $i => $v) { |
||
| 586 | $file->write('<numFmt numFmtId="'.(164 + $i).'" formatCode="'.self::xmlspecialchars($v).'" />'); |
||
| 587 | } |
||
| 588 | $file->write('</numFmts>'); |
||
| 589 | $file->write('<fonts count="4">'); |
||
| 590 | $file->write('<font><name val="Arial"/><charset val="1"/><family val="2"/><sz val="10"/></font>'); |
||
| 591 | $file->write('<font><name val="Arial"/><family val="0"/><sz val="10"/></font>'); |
||
| 592 | $file->write('<font><name val="Arial"/><family val="0"/><sz val="10"/></font>'); |
||
| 593 | $file->write('<font><name val="Arial"/><family val="0"/><sz val="10"/></font>'); |
||
| 594 | $file->write('</fonts>'); |
||
| 595 | $file->write('<fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></fills>'); |
||
| 596 | $file->write('<borders count="1"><border diagonalDown="false" diagonalUp="false"><left/><right/><top/><bottom/><diagonal/></border></borders>'); |
||
| 597 | $file->write('<cellStyleXfs count="20">'); |
||
| 598 | $file->write('<xf applyAlignment="true" applyBorder="true" applyFont="true" applyProtection="true" borderId="0" fillId="0" fontId="0" numFmtId="164">'); |
||
| 599 | $file->write('<alignment horizontal="general" indent="0" shrinkToFit="false" textRotation="0" vertical="bottom" wrapText="false"/>'); |
||
| 600 | $file->write('<protection hidden="false" locked="true"/>'); |
||
| 601 | $file->write('</xf>'); |
||
| 602 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="0"/>'); |
||
| 603 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="0"/>'); |
||
| 604 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="2" numFmtId="0"/>'); |
||
| 605 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="2" numFmtId="0"/>'); |
||
| 606 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
||
| 607 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
||
| 608 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
||
| 609 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
||
| 610 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
||
| 611 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
||
| 612 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
||
| 613 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
||
| 614 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
||
| 615 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="0"/>'); |
||
| 616 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="43"/>'); |
||
| 617 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="41"/>'); |
||
| 618 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="44"/>'); |
||
| 619 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="42"/>'); |
||
| 620 | $file->write('<xf applyAlignment="false" applyBorder="false" applyFont="true" applyProtection="false" borderId="0" fillId="0" fontId="1" numFmtId="9"/>'); |
||
| 621 | $file->write('</cellStyleXfs>'); |
||
| 622 | $file->write('<cellXfs count="'.count($this->cellFormats).'">'); |
||
| 623 | foreach ($this->cellFormats as $i => $v) { |
||
| 624 | $file->write( |
||
| 625 | '<xf applyAlignment="false" applyBorder="false" applyFont="false" |
||
| 626 | applyProtection="false" borderId="0" fillId="0" fontId="0" numFmtId="'.(164 + $i).'" xfId="0"/>' |
||
| 627 | ); |
||
| 628 | } |
||
| 629 | $file->write('</cellXfs>'); |
||
| 630 | $file->write('<cellStyles count="6">'); |
||
| 631 | $file->write('<cellStyle builtinId="0" customBuiltin="false" name="Normal" xfId="0"/>'); |
||
| 632 | $file->write('<cellStyle builtinId="3" customBuiltin="false" name="Comma" xfId="15"/>'); |
||
| 633 | $file->write('<cellStyle builtinId="6" customBuiltin="false" name="Comma [0]" xfId="16"/>'); |
||
| 634 | $file->write('<cellStyle builtinId="4" customBuiltin="false" name="Currency" xfId="17"/>'); |
||
| 635 | $file->write('<cellStyle builtinId="7" customBuiltin="false" name="Currency [0]" xfId="18"/>'); |
||
| 636 | $file->write('<cellStyle builtinId="5" customBuiltin="false" name="Percent" xfId="19"/>'); |
||
| 637 | $file->write('</cellStyles>'); |
||
| 638 | $file->write('</styleSheet>'); |
||
| 639 | $file->close(); |
||
| 640 | return $temporaryFilename; |
||
| 641 | } |
||
| 642 | |||
| 643 | /** |
||
| 644 | * @param $v |
||
| 645 | * |
||
| 646 | * @return int|mixed |
||
| 647 | */ |
||
| 648 | protected function setSharedString($v) |
||
| 660 | |||
| 661 | /** |
||
| 662 | * @return string |
||
| 663 | */ |
||
| 664 | protected function writeSharedStringsXML() |
||
| 674 | |||
| 675 | /** |
||
| 676 | * @return string |
||
| 677 | */ |
||
| 678 | protected function buildWorkbookXML() |
||
| 702 | |||
| 703 | /** |
||
| 704 | * @param int $rowNumber |
||
| 705 | * @param int $columnNumber |
||
| 706 | * |
||
| 707 | * @return string Cell label/coordinates (A1, C3, AA42) |
||
| 708 | */ |
||
| 709 | public static function xlsCell($rowNumber, $columnNumber) |
||
| 710 | { |
||
| 711 | $n = $columnNumber; |
||
| 712 | for ($r = ""; $n >= 0; $n = intval($n / 26) - 1) { |
||
| 713 | $r = chr($n % 26 + 0x41).$r; |
||
| 714 | } |
||
| 715 | |||
| 718 | |||
| 719 | /** |
||
| 720 | * @param $string |
||
| 721 | */ |
||
| 722 | public static function log($string) |
||
| 729 | |||
| 730 | /** |
||
| 731 | * @link https://msdn.microsoft.com/ru-RU/library/aa365247%28VS.85%29.aspx |
||
| 732 | * |
||
| 733 | * @param string $filename |
||
| 734 | * |
||
| 735 | * @return mixed |
||
| 736 | */ |
||
| 737 | public static function checkFilename($filename) |
||
| 746 | |||
| 747 | /** |
||
| 748 | * @param $val |
||
| 749 | * |
||
| 750 | * @return mixed |
||
| 751 | */ |
||
| 752 | public static function xmlspecialchars($val) |
||
| 756 | |||
| 757 | /** |
||
| 758 | * @param string $dateInput |
||
| 759 | * |
||
| 760 | * @return int |
||
| 761 | */ |
||
| 762 | public static function convertDateTime($dateInput) |
||
| 832 | } |
||
| 833 |
