majiameng /
spreadsheet-php
| Total Complexity | 83 |
| Total Lines | 516 |
| Duplicated Lines | 0 % |
| Changes | 2 | ||
| Bugs | 0 | 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 | /** |
||
| 94 | * @param $data |
||
| 95 | * @return $this |
||
| 96 | */ |
||
| 97 | public function setData($data){ |
||
| 98 | $this->data = $data; |
||
| 99 | return $this; |
||
| 100 | } |
||
| 101 | |||
| 102 | /** |
||
| 103 | * @param $data |
||
| 104 | * @return $this |
||
| 105 | */ |
||
| 106 | public function getData(){ |
||
| 107 | return $this->data; |
||
| 108 | } |
||
| 109 | |||
| 110 | |||
| 111 | /** |
||
| 112 | * @return void |
||
| 113 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
| 114 | */ |
||
| 115 | public function initWorkSheet() |
||
| 116 | { |
||
| 117 | $this->_col = 0; |
||
| 118 | $this->_row = 1; |
||
| 119 | $this->fileTitle = []; |
||
| 120 | $this->data = []; |
||
| 121 | $this->field = []; |
||
| 122 | if($this->freezePane) $this->workSheet->freezePane($this->freezePane); //冻结窗格 |
||
| 123 | } |
||
| 124 | |||
| 125 | /** |
||
| 126 | * @param $fileTitle |
||
| 127 | * @param $data |
||
| 128 | * @return $this |
||
| 129 | * @throws TinymengException |
||
| 130 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
| 131 | */ |
||
| 132 | public function setWorkSheetData($fileTitle,$data) |
||
| 133 | { |
||
| 134 | if(isset($fileTitle['title_row']) || isset($fileTitle['group_left'])){ |
||
| 135 | /** |
||
| 136 | * $fileTitle = [ |
||
| 137 | * 'title_row'=>1, |
||
| 138 | * 'group_left'=>[], |
||
| 139 | * 'title'=>[ |
||
| 140 | * '姓名'=>'name' |
||
| 141 | * ], |
||
| 142 | * ]; |
||
| 143 | */ |
||
| 144 | $this->title_row = $fileTitle['title_row']??1; |
||
|
Bug
Best Practice
introduced
by
|
|||
| 145 | $this->group_left = $fileTitle['group_left']??[]; |
||
| 146 | $titleData = $fileTitle['title']??[]; |
||
| 147 | // 新增：读取mergeColumns配置 |
||
| 148 | if (isset($fileTitle['mergeColumns'])) { |
||
| 149 | $this->mergeColumns = $fileTitle['mergeColumns']; |
||
| 150 | } |
||
| 151 | }else{ |
||
| 152 | /** |
||
| 153 | * $fileTitle = [ |
||
| 154 | * '姓名'=>'name', |
||
| 155 | * ]; |
||
| 156 | */ |
||
| 157 | $titleData = $fileTitle; |
||
| 158 | } |
||
| 159 | // 根据字段映射方式处理 title |
||
| 160 | if ($this->fieldMappingMethod === ConstCode::FIELD_MAPPING_METHOD_FIELD_CORRESPONDING_NAME) { |
||
| 161 | $this->fileTitle = array_flip($titleData);// 字段对应名称方式 - 需要将键值对调 |
||
| 162 | }else{ |
||
| 163 | $this->fileTitle = $titleData;// 名称对应字段方式 - 保持原样 |
||
| 164 | } |
||
| 165 | $this->data = $data; |
||
| 166 | |||
| 167 | /** 设置第一行格式 */ |
||
| 168 | if(!empty($this->mainTitle)){ |
||
| 169 | $this->excelHeader(); |
||
| 170 | } |
||
| 171 | |||
| 172 | /** 设置表头 **/ |
||
| 173 | $this->excelTitle(); |
||
| 174 | |||
| 175 | /** 获取列表里所有字段 **/ |
||
| 176 | foreach ($this->fileTitle as $key => $val){ |
||
| 177 | if(is_array($val)){ |
||
| 178 | foreach ($val as $k => $v){ |
||
| 179 | $this->field[] = $v; |
||
| 180 | } |
||
| 181 | }else{ |
||
| 182 | $this->field[] = $val; |
||
| 183 | } |
||
| 184 | } |
||
| 185 | /** 查询结果赋值 **/ |
||
| 186 | if(!empty($this->data)){ |
||
| 187 | $this->excelSetValue(); |
||
| 188 | } |
||
| 189 | // 新增：应用全表样式 |
||
| 190 | $this->applySheetStyle(); |
||
| 191 | // 新增：调用自定义表格操作回调 |
||
| 192 | if (is_callable($this->complexFormatCallback)) { |
||
| 193 | call_user_func($this->complexFormatCallback, $this->workSheet); |
||
| 194 | } |
||
| 195 | return $this; |
||
| 196 | } |
||
| 197 | |||
| 198 | /** |
||
| 199 | * excelSetValue |
||
| 200 | * @author: Tinymeng <[email protected]> |
||
| 201 | * @time: 2022/2/22 11:43 |
||
| 202 | */ |
||
| 203 | public function excelSetValue(){ |
||
| 204 | if(empty($this->group_left)){ //判断左侧是否分组 |
||
| 205 | $rowStart = $this->_row; |
||
| 206 | foreach ($this->data as $key => $val){ |
||
| 207 | $this->excelSetCellValue($val); |
||
| 208 | } |
||
| 209 | // 新增：处理mergeColumns自动合并 |
||
| 210 | if (!empty($this->mergeColumns)) { |
||
| 211 | $this->autoMergeColumns($rowStart, $this->_row - 1); |
||
| 212 | } |
||
| 213 | }else{ //根据设置分组字段进行分组 |
||
| 214 | /** 数据分组 **/ |
||
| 215 | $data = []; |
||
| 216 | $group_left_count = count($this->group_left); |
||
| 217 | if($group_left_count == 1){ |
||
| 218 | foreach ($this->data as $k => $v){ |
||
| 219 | $data[$v[$this->group_left[0]]][] = $v; |
||
| 220 | } |
||
| 221 | foreach ($data as $k =>$v){ |
||
| 222 | $data[$k] = [ |
||
| 223 | 'data' => $v, |
||
| 224 | 'count' => count($v) |
||
| 225 | ]; |
||
| 226 | } |
||
| 227 | $this->excelGroupLeft($data, $group_left_count); |
||
| 228 | }elseif($group_left_count == 2){ |
||
| 229 | foreach ($this->data as $v) { |
||
| 230 | $data[$v[$this->group_left[0]]][$v[$this->group_left[1]]][] = $v; |
||
| 231 | } |
||
| 232 | $this->data = $this->arrayCount($data); |
||
| 233 | $this->excelGroupLeft($this->data, $group_left_count); |
||
| 234 | }else{ |
||
| 235 | throw new TinymengException(StatusCode::COMMON_PARAM_INVALID, |
||
| 236 | '左侧分组过多，导出失败！' |
||
| 237 | ); |
||
| 238 | } |
||
| 239 | } |
||
| 240 | } |
||
| 241 | |||
| 242 | /** |
||
| 243 | * @return void |
||
| 244 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
| 245 | */ |
||
| 246 | public function excelHeader(){ |
||
| 247 | $row = 1; |
||
| 248 | if(!empty($this->mainTitle)){ |
||
| 249 | $this->workSheet->setCellValue('A'.$row, $this->mainTitle); |
||
| 250 | } |
||
| 251 | |||
| 252 | // 计算实际的标题列数 |
||
| 253 | $titleCount = 0; |
||
| 254 | foreach ($this->fileTitle as $val) { |
||
| 255 | if (is_array($val)) { |
||
| 256 | $titleCount += count($val); // 如果是数组，加上子项的数量 |
||
| 257 | } else { |
||
| 258 | $titleCount++; // 如果是单个标题，加1 |
||
| 259 | } |
||
| 260 | } |
||
| 261 | |||
| 262 | // 使用实际的标题列数来合并单元格 |
||
| 263 | $this->workSheet->mergeCells('A'.$row.':'.$this->cellName($titleCount-1).$row); |
||
| 264 | } |
||
| 265 | |||
| 266 | /** |
||
| 267 | * @return void |
||
| 268 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
| 269 | */ |
||
| 270 | private function excelTitle(){ |
||
| 271 | if(!empty($this->mainTitle)){ |
||
| 272 | $this->_row ++;//当前行数 |
||
| 273 | } |
||
| 274 | |||
| 275 | $_merge = $this->cellName($this->_col); |
||
| 276 | foreach ($this->fileTitle as $key => $val) { |
||
| 277 | if(!empty($this->titleHeight)) { |
||
| 278 | $this->workSheet->getRowDimension($this->_col)->setRowHeight($this->titleHeight);//行高度 |
||
| 279 | } |
||
| 280 | $rowName = $this->cellName($this->_col); |
||
| 281 | $this->workSheet->getStyle($rowName . $this->_row)->getAlignment()->setWrapText(true);//自动换行 |
||
| 282 | if (is_array($val)) { |
||
| 283 | $num = 1; |
||
| 284 | $_cols = $this->_col; |
||
| 285 | foreach ($val as $k => $v) { |
||
| 286 | $this->workSheet->setCellValue($this->cellName($_cols) . ($this->_row+1), $k); |
||
| 287 | if(!empty($this->titleWidth)) { |
||
| 288 | $this->workSheet->getColumnDimension($this->cellName($_cols))->setWidth($this->titleWidth); //列宽度 |
||
| 289 | }else{ |
||
| 290 | $this->workSheet->getColumnDimension($this->cellName($_cols))->setAutoSize(true); //自动计算宽度 |
||
| 291 | } |
||
| 292 | if ($num < count($val)) { |
||
| 293 | $this->_col++; |
||
| 294 | $num++; |
||
| 295 | } |
||
| 296 | $_cols++; |
||
| 297 | } |
||
| 298 | $this->workSheet->mergeCells($_merge . $this->_row.':' . $this->cellName($this->_col) .$this->_row); |
||
| 299 | $this->workSheet->setCellValue($_merge . $this->_row, $key);//设置值 |
||
| 300 | } else { |
||
| 301 | if ($this->title_row != 1) { |
||
| 302 | $this->workSheet->mergeCells($rowName . $this->_row.':' . $rowName . ($this->_row + $this->title_row - 1)); |
||
| 303 | } |
||
| 304 | $this->workSheet->setCellValue($rowName . $this->_row, $key);//设置值 |
||
| 305 | if(!empty($this->titleWidth)){ |
||
| 306 | $this->workSheet->getColumnDimension($rowName)->setWidth($this->titleWidth); //列宽度 |
||
| 307 | }else{ |
||
| 308 | $this->workSheet->getColumnDimension($rowName)->setAutoSize(true); //自动计算宽度 |
||
| 309 | } |
||
| 310 | } |
||
| 311 | $this->_col++; |
||
| 312 | $_merge = $this->cellName($this->_col); |
||
| 313 | } |
||
| 314 | $this->_row += $this->title_row;//当前行数 |
||
| 315 | } |
||
| 316 | |||
| 317 | /** |
||
| 318 | * excel单元格赋值 |
||
| 319 | * @author tinymeng |
||
| 320 | * @param array $val 数据 |
||
| 321 | */ |
||
| 322 | private function excelSetCellValue($val) |
||
| 323 | { |
||
| 324 | //设置单元格行高 |
||
| 325 | if(!empty($this->height)){ |
||
| 326 | $this->workSheet->getRowDimension($this->_row)->setRowHeight($this->height); |
||
| 327 | } |
||
| 328 | $_lie = 0; |
||
| 329 | foreach ($this->field as $v){ |
||
| 330 | $rowName = $this->cellName($_lie); |
||
| 331 | |||
| 332 | if(strpos($v,'.') !== false){ |
||
| 333 | $v = explode('.',$v); |
||
| 334 | $content = $val; |
||
| 335 | for ($i=0;$i<count($v);$i++){ |
||
| 336 | $content = $content[$v[$i]]??''; |
||
| 337 | } |
||
| 338 | }elseif($v == '_id'){ |
||
| 339 | $content = $this->_row-$this->title_row;//自增序号列 |
||
| 340 | }else{ |
||
| 341 | $content = ($val[$v]??''); |
||
| 342 | } |
||
| 343 | if(is_array($content) && isset($content['type']) && isset($content['content'])){ |
||
| 344 | if($content['type'] == 'image'){ |
||
| 345 | $path = $this->verifyFile($content['content']); |
||
| 346 | $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); |
||
| 347 | $drawing->setPath($path); |
||
| 348 | if(!empty($content['height'])) { |
||
| 349 | $drawing->setHeight($content['height']); |
||
| 350 | } |
||
| 351 | if(!empty($content['width'])) { |
||
| 352 | $drawing->setWidth($content['width']);//只设置高，宽会自适应，如果设置宽后，高则失效 |
||
| 353 | } |
||
| 354 | if(!empty($content['offsetX'])) { |
||
| 355 | $drawing->setOffsetX($content['offsetX']);//设置X方向偏移量 |
||
| 356 | } |
||
| 357 | if(!empty($content['offsetY'])) { |
||
| 358 | $drawing->setOffsetY($content['offsetY']);//设置Y方向偏移量 |
||
| 359 | } |
||
| 360 | |||
| 361 | $drawing->setCoordinates($rowName.$this->_row); |
||
| 362 | $drawing->setWorksheet($this->workSheet); |
||
| 363 | } |
||
| 364 | }else { |
||
| 365 | $content = $this->formatValue($content);//格式化数据 |
||
| 366 | if (is_numeric($content)){ |
||
| 367 | if($this->autoDataType && strlen($content)<11){ |
||
| 368 | $this->workSheet->setCellValueExplicit($rowName.$this->_row, $content,DataType::TYPE_NUMERIC); |
||
| 369 | }else{ |
||
| 370 | $this->workSheet->setCellValueExplicit($rowName.$this->_row, $content,DataType::TYPE_STRING2); |
||
| 371 | } |
||
| 372 | }else{ |
||
| 373 | $this->workSheet->setCellValueExplicit($rowName.$this->_row, $content,DataType::TYPE_STRING2); |
||
| 374 | } |
||
| 375 | } |
||
| 376 | $_lie ++; |
||
| 377 | } |
||
| 378 | $this->_row ++; |
||
| 379 | } |
||
| 380 | |||
| 381 | /** |
||
| 382 | * 单元格合并并赋值 |
||
| 383 | * @param array $data 数据 |
||
| 384 | * @param $group_left_count |
||
| 385 | * @author tinymeng |
||
| 386 | */ |
||
| 387 | private function excelGroupLeft(array $data, $group_left_count) |
||
| 388 | { |
||
| 389 | // 获取分组字段在field中的实际位置 |
||
| 390 | $group_field_positions = []; |
||
| 391 | foreach($this->group_left as $group_field){ |
||
| 392 | $position = array_search($group_field, $this->field); |
||
| 393 | if($position !== false){ |
||
| 394 | $group_field_positions[] = $position; |
||
| 395 | } |
||
| 396 | } |
||
| 397 | |||
| 398 | if(empty($group_field_positions)){ |
||
| 399 | throw new TinymengException(StatusCode::COMMON_PARAM_INVALID, '分组字段未在标题中定义'); |
||
| 400 | } |
||
| 401 | |||
| 402 | $group_start = $this->_row; |
||
| 403 | foreach ($data as $key => $val){ |
||
| 404 | // 第一级分组的合并单元格 |
||
| 405 | $rowName = $this->cellName($group_field_positions[0]); // 使用第一个分组字段的实际位置 |
||
| 406 | $coordinate = $rowName.$this->_row.':'.$rowName.($this->_row+$val['count']-1); |
||
| 407 | $this->workSheet->mergeCells($coordinate); |
||
| 408 | $this->workSheet->setCellValue($rowName.$this->_row, $key); |
||
| 409 | |||
| 410 | // 新增：合并mergeColumns指定的其它列 |
||
| 411 | if (!empty($this->mergeColumns)) { |
||
| 412 | foreach ($this->mergeColumns as $field) { |
||
| 413 | // 跳过分组字段本身 |
||
| 414 | if (in_array($field, $this->group_left)) continue; |
||
| 415 | $colIdx = array_search($field, $this->field); |
||
| 416 | if ($colIdx !== false) { |
||
| 417 | $colLetter = $this->cellName($colIdx); |
||
| 418 | $this->workSheet->mergeCells($colLetter.$this->_row.':'.$colLetter.($this->_row+$val['count']-1)); |
||
| 419 | // 取本组第一个数据的值 |
||
| 420 | $this->workSheet->setCellValue($colLetter.$this->_row, $val['data'][0][$field] ?? ''); |
||
| 421 | } |
||
| 422 | } |
||
| 423 | } |
||
| 424 | |||
| 425 | if($group_left_count == 1){ |
||
| 426 | foreach ($val['data'] as $dataRow){ |
||
| 427 | $this->excelSetCellValue($dataRow); |
||
| 428 | } |
||
| 429 | }else{ |
||
| 430 | $sub_group_start = $this->_row; |
||
| 431 | $rowName = $this->cellName($group_field_positions[1]); // 使用第二个分组字段的实际位置 |
||
| 432 | |||
| 433 | foreach ($val['data'] as $k => $v){ |
||
| 434 | $coordinate = $rowName.$sub_group_start.':'.$rowName.($sub_group_start+$v['count']-1); |
||
| 435 | $this->workSheet->mergeCells($coordinate); |
||
| 436 | $this->workSheet->setCellValue($rowName.$sub_group_start, $k); |
||
| 437 | |||
| 438 | foreach ($v['data'] as $data){ |
||
| 439 | $this->excelSetCellValue($data); |
||
| 440 | } |
||
| 441 | |||
| 442 | $sub_group_start = $sub_group_start + $v['count']; |
||
| 443 | } |
||
| 444 | } |
||
| 445 | |||
| 446 | $this->_row = $group_start + $val['count']; |
||
| 447 | $group_start = $this->_row; |
||
| 448 | } |
||
| 449 | } |
||
| 450 | |||
| 451 | /** |
||
| 452 | * 二位数组获取每一级别数量 |
||
| 453 | * @author tinymeng |
||
| 454 | * @param array $data 二维数组原始数据 |
||
| 455 | * @return array |
||
| 456 | */ |
||
| 457 | private function arrayCount($data=[]) |
||
| 458 | { |
||
| 459 | foreach ($data as $key => $val){ |
||
| 460 | $num = 0; |
||
| 461 | foreach ($val as $k => $v){ |
||
| 462 | $sub_num = count($v); |
||
| 463 | $num = $num+$sub_num; |
||
| 464 | $val[$k] = [ |
||
| 465 | 'count' => $sub_num, |
||
| 466 | 'data' => $v |
||
| 467 | ]; |
||
| 468 | } |
||
| 469 | $data[$key] = [ |
||
| 470 | 'count' => $num, |
||
| 471 | 'data' => $val |
||
| 472 | ]; |
||
| 473 | } |
||
| 474 | return $data; |
||
| 475 | } |
||
| 476 | |||
| 477 | /** |
||
| 478 | * 自动合并指定字段相同值的单元格 |
||
| 479 | * @param int $rowStart 数据起始行 |
||
| 480 | * @param int $rowEnd 数据结束行 |
||
| 481 | */ |
||
| 482 | private function autoMergeColumns($rowStart, $rowEnd) |
||
| 483 | { |
||
| 484 | if ($rowEnd <= $rowStart) return; |
||
| 485 | foreach ($this->mergeColumns as $fieldName) { |
||
| 486 | $colIdx = array_search($fieldName, $this->field); |
||
| 487 | if ($colIdx === false) continue; |
||
| 488 | $colLetter = $this->cellName($colIdx); |
||
| 489 | $lastValue = null; |
||
| 490 | $mergeStart = $rowStart; |
||
| 491 | for ($row = $rowStart; $row <= $rowEnd; $row++) { |
||
| 492 | $cellValue = $this->workSheet->getCell($colLetter . $row)->getValue(); |
||
| 493 | if ($lastValue !== null && $cellValue !== $lastValue) { |
||
| 494 | if ($row - $mergeStart > 1) { |
||
| 495 | $this->workSheet->mergeCells($colLetter . $mergeStart . ':' . $colLetter . ($row - 1)); |
||
| 496 | } |
||
| 497 | $mergeStart = $row; |
||
| 498 | } |
||
| 499 | $lastValue = $cellValue; |
||
| 500 | } |
||
| 501 | // 处理最后一组 |
||
| 502 | if ($rowEnd - $mergeStart + 1 > 1) { |
||
| 503 | $this->workSheet->mergeCells($colLetter . $mergeStart . ':' . $colLetter . $rowEnd); |
||
| 504 | } |
||
| 505 | } |
||
| 506 | } |
||
| 507 | |||
| 508 | /** |
||
| 509 | * 应用全表样式 |
||
| 510 | */ |
||
| 511 | private function applySheetStyle() |
||
| 521 | } |
||
| 522 | |||
| 523 | /** |
||
| 524 | * 设置自定义表格操作回调 |
||
| 525 | * @param callable $fn |
||
| 526 | * @return $this |
||
| 527 | */ |
||
| 528 | public function complexFormat(callable $fn) { |
||
| 531 | } |
||
| 532 | |||
| 533 | |||
| 534 | } |
||
| 535 |
Loading history...
