``` 重寫 rangeToArray <?php namespace Module\Cloud\Aliyun; use PhpOffice\PhpSpreadsheet\Cell\Coordinate; use PhpOffice\PhpSpreadsheet\Collection\Cells; use PhpOffice\PhpSpreadsheet\RichText\RichText; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Style\NumberFormat; use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet as BaseWorksheet; /** * 重寫PhpExcel */ class Worksheet extends BaseWorksheet { /** * Collection of cells. * * @var Cells */ private $cellCollection; /** * Parent spreadsheet. * * @var Spreadsheet */ private $parent; public function __construct(Cells $cellCollection, Spreadsheet $spreadsheet) { parent::__construct(); $this->cellCollection = $cellCollection; $this->parent = $spreadsheet; } /** * {@inheritdoc} * @param array $options 操作選項，例如： * array formatUnit 格式化指定元素，例如['A1', 'C1'] * array formatCol 格式化列，例如['A', 'C'] * array formatRange 格式化范圍，例如 'A1:C3' (字符串) * * @return array */ public function rangeToArray($pRange, $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false, $options = []) { // Returnvalue $returnValue = []; // Identify the range that we need to extract from the worksheet [$rangeStart, $rangeEnd] = Coordinate::rangeBoundaries($pRange); $minCol = Coordinate::stringFromColumnIndex($rangeStart[0]); $minRow = $rangeStart[1]; $maxCol = Coordinate::stringFromColumnIndex($rangeEnd[0]); $maxRow = $rangeEnd[1]; ++$maxCol; // Loop through rows $r = -1; for ($row = $minRow; $row <= $maxRow; ++$row) { $rRef = $returnCellRef ? $row : ++$r; $c = -1; // Loop through columns in the current row for ($col = $minCol; $col != $maxCol; ++$col) { $cRef = $returnCellRef ? $col : ++$c; // Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen // so we test and retrieve directly against cellCollection if ($this->cellCollection->has($col . $row)) { // Cell exists $cell = $this->cellCollection->get($col . $row); if (null !== $cell->getValue()) { if ($cell->getValue() instanceof RichText) { $returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText(); } else { if ($calculateFormulas) { $returnValue[$rRef][$cRef] = $cell->getCalculatedValue(); } else { $returnValue[$rRef][$cRef] = $cell->getValue(); } } // 全局格式化 if ($formatData) { $returnValue[$rRef][$cRef] = $returnValue[$rRef][$cRef] = $this->formatData($cell, $returnValue[$rRef][$cRef]); } // 部分格式化 if ($options && !$formatData) { // 單個字段格式化['A1','B2'] if (isset($options['formatUnit']) && is_array($options['formatUnit'])) { foreach ($options['formatUnit'] as $cr) { if (strtoupper($cr) == $col . $row) { $returnValue[$rRef][$cRef] = $this->formatData($cell, $returnValue[$rRef][$cRef]); } } } // 格式化列['A','B'] if (isset($options['formatCol']) && is_array($options['formatCol'])) { foreach ($options['formatCol'] as $forCol) { if (strtoupper($forCol) == $col) { $returnValue[$rRef][$cRef] = $this->formatData($cell, $returnValue[$rRef][$cRef]); } } } // 格式化范圍string = 'A1:C3' if (isset($options['formatRange']) && is_string($options['formatRange'])) { $formatRange = explode(':', $options['formatRange']); $rangeA = $formatRange[0]; $rangeB = $formatRange[1]; $rangeStart = Coordinate::coordinateFromString($rangeA); $rangeEnd = Coordinate::coordinateFromString($rangeB); $colStart = (int) Coordinate::columnIndexFromString($rangeStart[0]); $colEnd = (int) Coordinate::columnIndexFromString($rangeEnd[0]); $rowStart = (int) $rangeStart[1]; $rowEnd = (int) $rangeEnd[1]; if ($colStart <= $col && $col <= $colEnd && $rowStart <= $row && $row <= $rowEnd) { $returnValue[$rRef][$cRef] = $this->formatData($cell, $returnValue[$rRef][$cRef]); } } } } else { // Cell holds a NULL $returnValue[$rRef][$cRef] = $nullValue; } } else { // Cell doesn't exist $returnValue[$rRef][$cRef] = $nullValue; } } } // Return return $returnValue; } /** * @param $cell * @param $data * @return float|int|string */ protected function formatData($cell, $data) { $style = $this->parent->getCellXfByIndex($cell->getXfIndex()); $defaultFormatCode = ($style && $style->getNumberFormat()) ? $style->getNumberFormat()->getFormatCode() : NumberFormat::FORMAT_GENERAL; //日期格式固定輸出 $formatCode = $style->getNumberFormat()->getFormatCode(); if (preg_match('/^(\[\$[A-Z]*-[0-9A-F]*\])*[hmsdy]/i', $formatCode)) { $defaultFormatCode = NumberFormat::FORMAT_DATE_YYYYMMDDSLASH; } return NumberFormat::toFormattedString($data, $defaultFormatCode); } } ~~~ ```