icy2003 /
php
| Total Complexity | 45 |
| Total Lines | 189 |
| Duplicated Lines | 0 % |
| Changes | 1 | ||
| Bugs | 0 | Features | 0 |
Complex classes like iWorksheet 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 iWorksheet, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 20 | class iWorksheet |
||
| 21 | { |
||
| 22 | /** |
||
| 23 | * 返回一个指定范围内的单元格的数组 |
||
| 24 | * |
||
| 25 | * 修复不被支持的函数： NUMBERSTRING |
||
| 26 | * |
||
| 27 | * ps：作者表示不修车 |
||
| 28 | * |
||
| 29 | * @param \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $workSheet |
||
| 30 | * @param string $pRange 单元格范围 (i.e. "A1:B10"), 或者一个单元格 (i.e. "A1") |
||
| 31 | * @param mixed $nullValue 单元格内容不存在时返回的值 |
||
| 32 | * @param bool $returnCellRef false - 按照索引返回数组，true - 按照真实的行列返回数组 |
||
| 33 | * @param bool $calculateFormulas 是否计算公式的值？ |
||
| 34 | * @param bool $formatData 是否应用格式化到该数据 |
||
| 35 | * @param array $params 额外条件参数 |
||
| 36 | * onlyVisible 是否只返回可见单元格，默认 true |
||
| 37 | * fillColor 是否按填充颜色返回，如："#ff0000"，表示只返回红色背景的数据，默认 false，表示不限制 |
||
| 38 | * |
||
| 39 | * |
||
| 40 | * @return array |
||
| 41 | */ |
||
| 42 | public static function rangeToArray($workSheet, $pRange, $nullValue = null, $returnCellRef = true, $calculateFormulas = true, $formatData = true, $params = []) |
||
| 43 | { |
||
| 44 | $returnValue = []; |
||
| 45 | $styleArray = []; |
||
| 46 | list($rangeStart, $rangeEnd) = Coordinate::rangeBoundaries($pRange); |
||
| 47 | $minCol = Coordinate::stringFromColumnIndex($rangeStart[0]); |
||
| 48 | $minRow = $rangeStart[1]; |
||
| 49 | $maxCol = Coordinate::stringFromColumnIndex($rangeEnd[0]); |
||
| 50 | $maxRow = $rangeEnd[1]; |
||
| 51 | |||
| 52 | ++$maxCol; |
||
| 53 | $r = -1; |
||
| 54 | $onlyVisible = I::get($params, 'onlyVisible', true); |
||
| 55 | $fillColor = I::get($params, 'fillColor', false); |
||
| 56 | for ($row = $minRow; $row <= $maxRow; ++$row) { |
||
| 57 | if (true === $onlyVisible) { |
||
| 58 | $rowVisible = $workSheet->getRowDimension($row)->getVisible(); |
||
| 59 | if (!$rowVisible) { |
||
| 60 | continue; |
||
| 61 | } |
||
| 62 | } |
||
| 63 | $rRef = ($returnCellRef) ? $row : ++$r; |
||
| 64 | $c = -1; |
||
| 65 | for ($col = $minCol; $col != $maxCol; ++$col) { |
||
| 66 | if (true === $onlyVisible) { |
||
| 67 | $columnVisible = $workSheet->getColumnDimension($col)->getVisible(); |
||
| 68 | if (!$columnVisible) { |
||
| 69 | continue; |
||
| 70 | } |
||
| 71 | } |
||
| 72 | $cRef = ($returnCellRef) ? $col : ++$c; |
||
| 73 | if ($workSheet->getCellCollection()->has($col . $row)) { |
||
| 74 | $cell = $workSheet->getCellCollection()->get($col . $row); |
||
| 75 | if ($fillColor) { |
||
| 76 | if ($cell->getStyle()->getFill()->getStartColor()->getRGB() != substr(strtoupper($fillColor), 1)) { |
||
| 77 | continue; |
||
| 78 | } |
||
| 79 | } |
||
| 80 | if ($cell->getValue() !== null) { |
||
| 81 | if ($cell->getValue() instanceof RichText) { |
||
| 82 | $returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText(); |
||
| 83 | } else { |
||
| 84 | if ($calculateFormulas) { |
||
| 85 | // fix |
||
| 86 | if (false !== strpos(strtoupper($cell->getValue()), 'NUMBERSTRING')) { |
||
| 87 | $returnValue[$rRef][$cRef] = $cell->getOldCalculatedValue(); |
||
| 88 | } else { |
||
| 89 | $returnValue[$rRef][$cRef] = $cell->getCalculatedValue(); |
||
| 90 | } |
||
| 91 | } else { |
||
| 92 | $returnValue[$rRef][$cRef] = $cell->getValue(); |
||
| 93 | } |
||
| 94 | } |
||
| 95 | |||
| 96 | if ($formatData) { |
||
| 97 | $style = $workSheet->getParent()->getCellXfByIndex($cell->getXfIndex()); |
||
| 98 | $returnValue[$rRef][$cRef] = NumberFormat::toFormattedString( |
||
| 99 | $returnValue[$rRef][$cRef], |
||
| 100 | ($style && $style->getNumberFormat()) ? $style->getNumberFormat()->getFormatCode() : NumberFormat::FORMAT_GENERAL |
||
| 101 | ); |
||
| 102 | } |
||
| 103 | } else { |
||
| 104 | $returnValue[$rRef][$cRef] = $nullValue; |
||
| 105 | } |
||
| 106 | // 背景图目前有bug：当背景是白色时，拿到的是黑色，这里可以按文字颜色判断：如果文字不是白色，那黑色背景强制改成白色 |
||
| 107 | $bgColor = $cell->getStyle()->getFill()->getStartColor()->getRGB(); |
||
| 108 | $color = $cell->getStyle()->getFont()->getColor()->getRGB(); |
||
| 109 | if ('FFFFFF' != $color) { |
||
| 110 | if ('000000' == $bgColor) { |
||
| 111 | $bgColor = 'FFFFFF'; |
||
| 112 | } |
||
| 113 | } |
||
| 114 | $styleArray[$rRef][$cRef] = [ |
||
| 115 | 'color' => $color, |
||
| 116 | 'name' => $cell->getStyle()->getFont()->getName(), |
||
| 117 | 'size' => $cell->getStyle()->getFont()->getSize(), |
||
| 118 | 'bold' => $cell->getStyle()->getFont()->getBold(), |
||
| 119 | 'italic' => $cell->getStyle()->getFont()->getItalic(), |
||
| 120 | 'underline' => $cell->getStyle()->getFont()->getUnderline(), |
||
| 121 | 'alignment' => $cell->getStyle()->getAlignment()->getHorizontal(), |
||
| 122 | 'valign' => $cell->getStyle()->getAlignment()->getVertical(), |
||
| 123 | 'bgColor' => $bgColor, |
||
| 124 | ]; |
||
| 125 | } else { |
||
| 126 | $returnValue[$rRef][$cRef] = $nullValue; |
||
| 127 | $styleArray[$rRef][$cRef] = []; |
||
| 128 | } |
||
| 129 | } |
||
| 130 | } |
||
| 131 | |||
| 132 | // 过滤空白行列，如果末尾有合并的单元格，因为被合并的单元格本身是空，因此这里会以合并单元格为准，再筛选掉空白行列 |
||
| 133 | $mergeArray = array_keys($workSheet->getMergeCells()); |
||
| 134 | if (!empty($mergeArray)) { |
||
| 135 | $rowMax = $colMax = 1; |
||
| 136 | foreach ($mergeArray as $range) { |
||
| 137 | // rangeStart 没用，因为肯定不会比 rangeEnd 大 |
||
| 138 | list($rangeStart, $rangeEnd) = Coordinate::rangeBoundaries($range); |
||
| 139 | $startCol = Coordinate::stringFromColumnIndex($rangeStart[0]); |
||
| 140 | $endCol = Coordinate::stringFromColumnIndex($rangeEnd[0]); |
||
| 141 | // 隐藏单元格不拿时，可能会因为隐藏了合并单元格的一部分导致数据拿不到，因此需要做检测 |
||
| 142 | if (true === $onlyVisible) { |
||
| 143 | $s = 0; |
||
| 144 | for ($i = $startCol; $i <= $endCol; $i++) { |
||
| 145 | for ($j = $rangeStart[1]; $j <= $rangeEnd[1]; $j++) { |
||
| 146 | $colVisible = $workSheet->getColumnDimension($i)->getVisible(); |
||
| 147 | $rowVisible = $workSheet->getRowDimension($j)->getVisible(); |
||
| 148 | if (0 === $s++) { |
||
| 149 | $initVisible = $colVisible && $rowVisible; |
||
| 150 | } |
||
| 151 | $pVisible = $colVisible && $rowVisible; |
||
| 152 | if (true === $initVisible ^ $pVisible) { |
||
| 153 | // 如果隐藏单元格不拿，那返回的数据里肯定也没有，所以拿第一个单元格判断，因为第一个单元格存着合并单元格的内容数据，不能不拿 |
||
| 154 | if (empty($returnValue[$rangeStart[1]][$startCol])) { |
||
| 155 | $title = $workSheet->getTitle(); |
||
| 156 | throw new \Exception('不允许隐藏合并单元格的主单元格（合并前左上角那个），' . |
||
| 157 | '该表为：' . $title . '，' . |
||
| 158 | '合并单元格是 ' . $range . '，' . |
||
| 159 | '其中 ' . $startCol . $rangeStart[1] . ' 被隐藏了'); |
||
| 160 | } |
||
| 161 | } |
||
| 162 | } |
||
| 163 | } |
||
| 164 | } |
||
| 165 | if ($rangeEnd[0] > $colMax) { |
||
| 166 | $colMax = $rangeEnd[0]; |
||
| 167 | } |
||
| 168 | if ($rangeEnd[1] > $rowMax) { |
||
| 169 | $rowMax = $rangeEnd[1]; |
||
| 170 | } |
||
| 171 | } |
||
| 172 | } |
||
| 173 | // 去掉空白行和列 |
||
| 174 | $data = []; |
||
| 175 | $style = []; |
||
| 176 | foreach ($returnValue as $r => $row) { |
||
| 177 | // 如果使用索引，那么索引和真实行差1 |
||
| 178 | if (false === $returnCellRef) { |
||
| 179 | $rowIndex = $r + 1; |
||
| 180 | } else { |
||
| 181 | $rowIndex = $r; |
||
| 182 | } |
||
| 183 | if (!empty(array_filter($row)) || !empty($mergeArray) && $rowIndex <= $rowMax) { |
||
| 184 | foreach ($row as $c => $value) { |
||
| 185 | // 如果使用索引，那么索引和真实列差1 |
||
| 186 | if (false === $returnCellRef) { |
||
| 187 | $colIndex = $c + 1; |
||
| 188 | } else { |
||
| 189 | $colIndex = Coordinate::columnIndexFromString($c); |
||
| 190 | } |
||
| 191 | $isEmpty = true; |
||
| 192 | foreach ($returnValue as $d) { |
||
| 193 | // 如果内容不为空，或者在合并单元格中出现，这个单元格不能算成是空 |
||
| 194 | if (!empty($d[$c]) || !empty($mergeArray) && $colIndex <= $colMax) { |
||
| 195 | $isEmpty = false; |
||
| 196 | break; |
||
| 197 | } |
||
| 198 | } |
||
| 199 | if (false === $isEmpty) { |
||
| 200 | // 使用之前的下标 |
||
| 201 | $data[$r][$c] = $value; |
||
| 202 | $style[$r][$c] = $styleArray[$r][$c]; |
||
| 203 | } |
||
| 204 | } |
||
| 205 | } |
||
| 206 | } |
||
| 207 | |||
| 208 | return [$data, $style]; |
||
| 209 | } |
||
| 211 |
