majiameng /
spreadsheet-php
| Total Complexity | 95 |
| Total Lines | 539 |
| Duplicated Lines | 0 % |
| Changes | 3 | ||
| Bugs | 1 | Features | 0 |
Complex classes like TWorkSheet 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 TWorkSheet, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 15 | trait TWorkSheet{ |
||
| 16 | |||
| 17 | /** |
||
| 18 | * sheet名称 |
||
| 19 | * @var |
||
| 20 | */ |
||
| 21 | private $sheetName; |
||
| 22 | /** |
||
| 23 | * 查询数据 |
||
| 24 | * @var |
||
| 25 | */ |
||
| 26 | private $data; |
||
| 27 | |||
| 28 | /** |
||
| 29 | * 定义默认列数 |
||
| 30 | * @var int |
||
| 31 | */ |
||
| 32 | private $_col = 0; |
||
| 33 | /** |
||
| 34 | * 定义当前行数 |
||
| 35 | * @var int |
||
| 36 | */ |
||
| 37 | private $_row = 1; |
||
| 38 | /** |
||
| 39 | * 定义所有字段 |
||
| 40 | * @var array |
||
| 41 | */ |
||
| 42 | private $field = []; |
||
| 43 | |||
| 44 | /** |
||
| 45 | * 文件信息 |
||
| 46 | * @var array |
||
| 47 | */ |
||
| 48 | private $fileTitle=[]; |
||
| 49 | |||
| 50 | |||
| 51 | /** |
||
| 52 | * 左侧分组字段 |
||
| 53 | * @var array |
||
| 54 | */ |
||
| 55 | private $group_left = []; |
||
| 56 | |||
| 57 | |||
| 58 | /** |
||
| 59 | * 获取sheet表格数目 |
||
| 60 | * @var |
||
| 61 | */ |
||
| 62 | private $sheetCount = 1; |
||
| 63 | |||
| 64 | /** |
||
| 65 | * 字段映射方式 |
||
| 66 | * @var int |
||
| 67 | */ |
||
| 68 | private $fieldMappingMethod = ConstCode::FIELD_MAPPING_METHOD_FIELD_CORRESPONDING_NAME; |
||
| 69 | |||
| 70 | /** |
||
| 71 | * 需要自动合并的字段 |
||
| 72 | * @var array |
||
| 73 | */ |
||
| 74 | private $mergeColumns = []; |
||
| 75 | |||
| 76 | /** |
||
| 77 | * 小计行样式 |
||
| 78 | * @var array |
||
| 79 | */ |
||
| 80 | private $subtotalStyle = []; |
||
| 81 | /** |
||
| 82 | * 全表样式 |
||
| 83 | * @var array |
||
| 84 | */ |
||
| 85 | private $sheetStyle = []; |
||
| 86 | |||
| 87 | /** |
||
| 88 | * 用户自定义表格操作回调 |
||
| 89 | * @var callable|null |
||
| 90 | */ |
||
| 91 | private $complexFormatCallback = null; |
||
| 92 | /** |
||
| 93 | * @var array |
||
| 94 | */ |
||
| 95 | private $titleConfig = []; |
||
| 96 | |||
| 97 | /** |
||
| 98 | * @param $data |
||
| 99 | * @return $this |
||
| 100 | */ |
||
| 101 | public function setData($data){ |
||
| 102 | $this->data = $data; |
||
| 103 | return $this; |
||
| 104 | } |
||
| 105 | |||
| 106 | /** |
||
| 107 | * @param $data |
||
| 108 | * @return $this |
||
| 109 | */ |
||
| 110 | public function getData(){ |
||
| 112 | } |
||
| 113 | |||
| 114 | |||
| 115 | /** |
||
| 116 | * @return void |
||
| 117 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
| 118 | */ |
||
| 119 | public function initWorkSheet() |
||
| 127 | } |
||
| 128 | |||
| 129 | /** |
||
| 130 | * @param $fileTitle |
||
| 131 | * @param $data |
||
| 132 | * @return $this |
||
| 133 | * @throws TinymengException |
||
| 134 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
| 135 | */ |
||
| 136 | public function setWorkSheetData($titleConfig,$data) |
||
| 202 | } |
||
| 203 | |||
| 204 | /** |
||
| 205 | * excelSetValue |
||
| 206 | * @author: Tinymeng <[email protected]> |
||
| 207 | * @time: 2022/2/22 11:43 |
||
| 208 | */ |
||
| 209 | public function excelSetValue(){ |
||
| 210 | if(!empty($this->titleConfig['data_start_row'])){ |
||
| 211 | $this->_row = $this->titleConfig['data_start_row']; |
||
| 212 | } |
||
| 213 | |||
| 214 | if(empty($this->group_left)){ //判断左侧是否分组 |
||
| 215 | $rowStart = $this->_row; |
||
| 216 | foreach ($this->data as $key => $val){ |
||
| 217 | $this->excelSetCellValue($val); |
||
| 218 | } |
||
| 219 | // 新增：处理mergeColumns自动合并 |
||
| 220 | if (!empty($this->mergeColumns)) { |
||
| 221 | $this->autoMergeColumns($rowStart, $this->_row - 1); |
||
| 222 | } |
||
| 223 | }else{ //根据设置分组字段进行分组 |
||
| 224 | /** 数据分组 **/ |
||
| 225 | $data = []; |
||
| 226 | $group_left_count = count($this->group_left); |
||
| 227 | if($group_left_count == 1){ |
||
| 228 | foreach ($this->data as $k => $v){ |
||
| 229 | if(isset($v[$this->group_left[0]])){ |
||
| 230 | $data[$v[$this->group_left[0]]][] = $v; |
||
| 231 | } |
||
| 232 | } |
||
| 233 | foreach ($data as $k =>$v){ |
||
| 234 | $data[$k] = [ |
||
| 235 | 'data' => $v, |
||
| 236 | 'count' => count($v) |
||
| 237 | ]; |
||
| 238 | } |
||
| 239 | $this->excelGroupLeft($data, $group_left_count); |
||
| 240 | }elseif($group_left_count == 2){ |
||
| 241 | foreach ($this->data as $v) { |
||
| 242 | if(isset($v[$this->group_left[0]]) && isset($v[$this->group_left[1]])){ |
||
| 243 | $data[$v[$this->group_left[0]]][$v[$this->group_left[1]]][] = $v; |
||
| 244 | } |
||
| 245 | } |
||
| 246 | $this->data = $this->arrayCount($data); |
||
| 247 | $this->excelGroupLeft($this->data, $group_left_count); |
||
| 248 | }else{ |
||
| 249 | throw new TinymengException(StatusCode::COMMON_PARAM_INVALID, |
||
| 250 | '左侧分组过多，导出失败！' |
||
| 251 | ); |
||
| 252 | } |
||
| 253 | } |
||
| 254 | } |
||
| 255 | |||
| 256 | /** |
||
| 257 | * @return void |
||
| 258 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
| 259 | */ |
||
| 260 | public function excelHeader(){ |
||
| 261 | $row = 1; |
||
| 262 | if(!empty($this->mainTitle)){ |
||
| 263 | $this->workSheet->setCellValue('A'.$row, $this->mainTitle); |
||
| 264 | } |
||
| 265 | |||
| 266 | // 计算实际的标题列数 |
||
| 267 | $titleCount = 0; |
||
| 268 | foreach ($this->fileTitle as $val) { |
||
| 269 | if (is_array($val)) { |
||
| 270 | $titleCount += count($val); // 如果是数组，加上子项的数量 |
||
| 271 | } else { |
||
| 272 | $titleCount++; // 如果是单个标题，加1 |
||
| 273 | } |
||
| 274 | } |
||
| 275 | |||
| 276 | // 使用实际的标题列数来合并单元格 |
||
| 277 | $this->workSheet->mergeCells('A'.$row.':'.$this->cellName($titleCount-1).$row); |
||
| 278 | } |
||
| 279 | |||
| 280 | /** |
||
| 281 | * @return void |
||
| 282 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
| 283 | */ |
||
| 284 | private function excelTitle(){ |
||
| 285 | if(!empty($this->titleConfig['title_start_row'])){ |
||
| 286 | $this->_row = $this->titleConfig['title_start_row']; |
||
| 287 | } |
||
| 288 | |||
| 289 | $_merge = $this->cellName($this->_col); |
||
| 290 | foreach ($this->fileTitle as $key => $val) { |
||
| 291 | if(!empty($this->titleHeight)) { |
||
| 292 | $this->workSheet->getRowDimension($this->_col)->setRowHeight($this->titleHeight);//行高度 |
||
| 293 | } |
||
| 294 | $rowName = $this->cellName($this->_col); |
||
| 295 | $this->workSheet->getStyle($rowName . $this->_row)->getAlignment()->setWrapText(true);//自动换行 |
||
| 296 | if (is_array($val)) { |
||
| 297 | $num = 1; |
||
| 298 | $_cols = $this->_col; |
||
| 299 | foreach ($val as $k => $v) { |
||
| 300 | if(!isset($this->titleConfig['title_show']) || $this->titleConfig['title_show']!==false) { |
||
| 301 | $this->workSheet->setCellValue($this->cellName($_cols) . ($this->_row+1), $k); |
||
| 302 | } |
||
| 303 | if(!empty($this->titleWidth)) { |
||
| 304 | $this->workSheet->getColumnDimension($this->cellName($_cols))->setWidth($this->titleWidth); //列宽度 |
||
| 305 | }else{ |
||
| 306 | $this->workSheet->getColumnDimension($this->cellName($_cols))->setAutoSize(true); //自动计算宽度 |
||
| 307 | } |
||
| 308 | if ($num < count($val)) { |
||
| 309 | $this->_col++; |
||
| 310 | $num++; |
||
| 311 | } |
||
| 312 | $_cols++; |
||
| 313 | } |
||
| 314 | $this->workSheet->mergeCells($_merge . $this->_row.':' . $this->cellName($this->_col) .$this->_row); |
||
| 315 | if(!isset($this->titleConfig['title_show']) || $this->titleConfig['title_show']!==false) { |
||
| 316 | $this->workSheet->setCellValue($_merge . $this->_row, $key);//设置值 |
||
| 317 | } |
||
| 318 | } else { |
||
| 319 | if ($this->title_row != 1) { |
||
| 320 | $this->workSheet->mergeCells($rowName . $this->_row.':' . $rowName . ($this->_row + $this->title_row - 1)); |
||
| 321 | } |
||
| 322 | if(!isset($this->titleConfig['title_show']) || $this->titleConfig['title_show']!==false) { |
||
| 323 | $this->workSheet->setCellValue($rowName . $this->_row, $key);//设置值 |
||
| 324 | } |
||
| 325 | if(!empty($this->titleWidth)){ |
||
| 326 | $this->workSheet->getColumnDimension($rowName)->setWidth($this->titleWidth); //列宽度 |
||
| 327 | }else{ |
||
| 328 | $this->workSheet->getColumnDimension($rowName)->setAutoSize(true); //自动计算宽度 |
||
| 329 | } |
||
| 330 | } |
||
| 331 | $this->_col++; |
||
| 332 | $_merge = $this->cellName($this->_col); |
||
| 333 | } |
||
| 334 | $this->_row += $this->title_row;//当前行数 |
||
| 335 | } |
||
| 336 | |||
| 337 | /** |
||
| 338 | * excel单元格赋值 |
||
| 339 | * @author tinymeng |
||
| 340 | * @param array $val 数据 |
||
| 341 | */ |
||
| 342 | private function excelSetCellValue($val) |
||
| 343 | { |
||
| 344 | //设置单元格行高 |
||
| 345 | if(!empty($this->height)){ |
||
| 346 | $this->workSheet->getRowDimension($this->_row)->setRowHeight($this->height); |
||
| 347 | } |
||
| 348 | $_lie = 0; |
||
| 349 | foreach ($this->field as $v){ |
||
| 350 | $rowName = $this->cellName($_lie); |
||
| 351 | |||
| 352 | if(strpos($v,'.') !== false){ |
||
| 353 | $v = explode('.',$v); |
||
| 354 | $content = $val; |
||
| 355 | for ($i=0;$i<count($v);$i++){ |
||
| 356 | $content = $content[$v[$i]]??''; |
||
| 357 | } |
||
| 358 | }elseif($v == '_id'){ |
||
| 359 | $content = $this->_row-$this->title_row;//自增序号列 |
||
| 360 | }else{ |
||
| 361 | $content = ($val[$v]??''); |
||
| 362 | } |
||
| 363 | if(is_array($content) && isset($content['type']) && isset($content['content'])){ |
||
| 364 | if($content['type'] == 'image'){ |
||
| 365 | $path = $this->verifyFile($content['content']); |
||
| 366 | $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); |
||
| 367 | $drawing->setPath($path); |
||
| 368 | if(!empty($content['height'])) { |
||
| 369 | $drawing->setHeight($content['height']); |
||
| 370 | } |
||
| 371 | if(!empty($content['width'])) { |
||
| 372 | $drawing->setWidth($content['width']);//只设置高，宽会自适应，如果设置宽后，高则失效 |
||
| 373 | } |
||
| 374 | if(!empty($content['offsetX'])) { |
||
| 375 | $drawing->setOffsetX($content['offsetX']);//设置X方向偏移量 |
||
| 376 | } |
||
| 377 | if(!empty($content['offsetY'])) { |
||
| 378 | $drawing->setOffsetY($content['offsetY']);//设置Y方向偏移量 |
||
| 379 | } |
||
| 380 | |||
| 381 | $drawing->setCoordinates($rowName.$this->_row); |
||
| 382 | $drawing->setWorksheet($this->workSheet); |
||
| 383 | } |
||
| 384 | }elseif(is_array($content) && isset($content['formula'])){ |
||
| 385 | // 新增：支持 ['formula' => '公式'] 写法 |
||
| 386 | $this->workSheet->setCellValueExplicit($rowName.$this->_row, $content['formula'], \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_FORMULA); |
||
| 387 | }else { |
||
| 388 | $content = $this->formatValue($content);//格式化数据 |
||
| 389 | if (is_numeric($content)){ |
||
| 390 | if($this->autoDataType && strlen($content)<11){ |
||
| 391 | $this->workSheet->setCellValueExplicit($rowName.$this->_row, $content,DataType::TYPE_NUMERIC); |
||
| 392 | }else{ |
||
| 393 | $this->workSheet->setCellValueExplicit($rowName.$this->_row, $content,DataType::TYPE_STRING2); |
||
| 394 | } |
||
| 395 | }else{ |
||
| 396 | $this->workSheet->setCellValueExplicit($rowName.$this->_row, $content,DataType::TYPE_STRING2); |
||
| 397 | } |
||
| 398 | } |
||
| 399 | $_lie ++; |
||
| 400 | } |
||
| 401 | $this->_row ++; |
||
| 402 | } |
||
| 403 | |||
| 404 | /** |
||
| 405 | * 单元格合并并赋值 |
||
| 406 | * @param array $data 数据 |
||
| 407 | * @param $group_left_count |
||
| 408 | * @author tinymeng |
||
| 409 | */ |
||
| 410 | private function excelGroupLeft(array $data, $group_left_count) |
||
| 411 | { |
||
| 412 | // 获取分组字段在field中的实际位置 |
||
| 413 | $group_field_positions = []; |
||
| 414 | foreach($this->group_left as $group_field){ |
||
| 415 | $position = array_search($group_field, $this->field); |
||
| 416 | if($position !== false){ |
||
| 417 | $group_field_positions[] = $position; |
||
| 418 | } |
||
| 419 | } |
||
| 420 | |||
| 421 | if(empty($group_field_positions)){ |
||
| 422 | throw new TinymengException(StatusCode::COMMON_PARAM_INVALID, '分组字段未在标题中定义'); |
||
| 423 | } |
||
| 424 | |||
| 425 | $group_start = $this->_row; |
||
| 426 | foreach ($data as $key => $val){ |
||
| 427 | // 第一级分组的合并单元格 |
||
| 428 | $rowName = $this->cellName($group_field_positions[0]); // 使用第一个分组字段的实际位置 |
||
| 429 | $coordinate = $rowName.$this->_row.':'.$rowName.($this->_row+$val['count']-1); |
||
| 430 | $this->workSheet->mergeCells($coordinate); |
||
| 431 | $this->workSheet->setCellValue($rowName.$this->_row, $key); |
||
| 432 | |||
| 433 | // 新增：合并mergeColumns指定的其它列 |
||
| 434 | if (!empty($this->mergeColumns)) { |
||
| 435 | foreach ($this->mergeColumns as $field) { |
||
| 436 | // 跳过分组字段本身 |
||
| 437 | if (in_array($field, $this->group_left)) continue; |
||
| 438 | $colIdx = array_search($field, $this->field); |
||
| 439 | if ($colIdx !== false) { |
||
| 440 | $colLetter = $this->cellName($colIdx); |
||
| 441 | $this->workSheet->mergeCells($colLetter.$this->_row.':'.$colLetter.($this->_row+$val['count']-1)); |
||
| 442 | // 取本组第一个数据的值 |
||
| 443 | $this->workSheet->setCellValue($colLetter.$this->_row, $val['data'][0][$field] ?? ''); |
||
| 444 | } |
||
| 445 | } |
||
| 446 | } |
||
| 447 | |||
| 448 | if($group_left_count == 1){ |
||
| 449 | foreach ($val['data'] as $dataRow){ |
||
| 450 | $this->excelSetCellValue($dataRow); |
||
| 451 | } |
||
| 452 | }else{ |
||
| 453 | $sub_group_start = $this->_row; |
||
| 454 | $rowName = $this->cellName($group_field_positions[1]); // 使用第二个分组字段的实际位置 |
||
| 455 | |||
| 456 | foreach ($val['data'] as $k => $v){ |
||
| 457 | $coordinate = $rowName.$sub_group_start.':'.$rowName.($sub_group_start+$v['count']-1); |
||
| 458 | $this->workSheet->mergeCells($coordinate); |
||
| 459 | $this->workSheet->setCellValue($rowName.$sub_group_start, $k); |
||
| 460 | |||
| 461 | foreach ($v['data'] as $data){ |
||
| 462 | $this->excelSetCellValue($data); |
||
| 463 | } |
||
| 464 | |||
| 465 | $sub_group_start = $sub_group_start + $v['count']; |
||
| 466 | } |
||
| 467 | } |
||
| 468 | |||
| 469 | $this->_row = $group_start + $val['count']; |
||
| 470 | $group_start = $this->_row; |
||
| 471 | } |
||
| 472 | } |
||
| 473 | |||
| 474 | /** |
||
| 475 | * 二位数组获取每一级别数量 |
||
| 476 | * @author tinymeng |
||
| 477 | * @param array $data 二维数组原始数据 |
||
| 478 | * @return array |
||
| 479 | */ |
||
| 480 | private function arrayCount($data=[]) |
||
| 481 | { |
||
| 482 | foreach ($data as $key => $val){ |
||
| 483 | $num = 0; |
||
| 484 | foreach ($val as $k => $v){ |
||
| 485 | $sub_num = count($v); |
||
| 486 | $num = $num+$sub_num; |
||
| 487 | $val[$k] = [ |
||
| 488 | 'count' => $sub_num, |
||
| 489 | 'data' => $v |
||
| 490 | ]; |
||
| 491 | } |
||
| 492 | $data[$key] = [ |
||
| 493 | 'count' => $num, |
||
| 494 | 'data' => $val |
||
| 495 | ]; |
||
| 496 | } |
||
| 497 | return $data; |
||
| 498 | } |
||
| 499 | |||
| 500 | /** |
||
| 501 | * 自动合并指定字段相同值的单元格 |
||
| 502 | * @param int $rowStart 数据起始行 |
||
| 503 | * @param int $rowEnd 数据结束行 |
||
| 504 | */ |
||
| 505 | private function autoMergeColumns($rowStart, $rowEnd) |
||
| 506 | { |
||
| 507 | if ($rowEnd <= $rowStart) return; |
||
| 508 | foreach ($this->mergeColumns as $fieldName) { |
||
| 509 | $colIdx = array_search($fieldName, $this->field); |
||
| 510 | if ($colIdx === false) continue; |
||
| 511 | $colLetter = $this->cellName($colIdx); |
||
| 512 | $lastValue = null; |
||
| 513 | $mergeStart = $rowStart; |
||
| 514 | for ($row = $rowStart; $row <= $rowEnd; $row++) { |
||
| 515 | $cellValue = $this->workSheet->getCell($colLetter . $row)->getValue(); |
||
| 516 | if ($lastValue !== null && $cellValue !== $lastValue) { |
||
| 517 | if ($row - $mergeStart > 1) { |
||
| 518 | $this->workSheet->mergeCells($colLetter . $mergeStart . ':' . $colLetter . ($row - 1)); |
||
| 519 | } |
||
| 520 | $mergeStart = $row; |
||
| 521 | } |
||
| 522 | $lastValue = $cellValue; |
||
| 523 | } |
||
| 524 | // 处理最后一组 |
||
| 525 | if ($rowEnd - $mergeStart + 1 > 1) { |
||
| 526 | $this->workSheet->mergeCells($colLetter . $mergeStart . ':' . $colLetter . $rowEnd); |
||
| 527 | } |
||
| 528 | } |
||
| 529 | } |
||
| 530 | |||
| 531 | /** |
||
| 532 | * 应用全表样式 |
||
| 533 | */ |
||
| 534 | private function applySheetStyle() |
||
| 544 | } |
||
| 545 | |||
| 546 | /** |
||
| 547 | * 设置自定义表格操作回调 |
||
| 548 | * @param callable $fn |
||
| 549 | * @return $this |
||
| 550 | */ |
||
| 551 | public function complexFormat(callable $fn) { |
||
| 554 | } |
||
| 555 | |||
| 556 | |||
| 557 | } |
||
| 558 |
Loading history...
