majiameng /
spreadsheet-php
| Total Complexity | 61 |
| Total Lines | 403 |
| 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 | * @var |
||
| 30 | */ |
||
| 31 | private $mainTitle; |
||
| 32 | /** |
||
| 33 | * 是否需要报表名称(主标题) |
||
| 34 | * @var bool |
||
| 35 | */ |
||
| 36 | private $mainTitleLine = false; |
||
| 37 | |||
| 38 | /** |
||
| 39 | * 定义默认列数 |
||
| 40 | * @var int |
||
| 41 | */ |
||
| 42 | private $_col = 0; |
||
| 43 | /** |
||
| 44 | * 定义当前行数 |
||
| 45 | * @var int |
||
| 46 | */ |
||
| 47 | private $_row = 1; |
||
| 48 | /** |
||
| 49 | * 定义所有字段 |
||
| 50 | * @var array |
||
| 51 | */ |
||
| 52 | private $field = []; |
||
| 53 | |||
| 54 | /** |
||
| 55 | * 文件信息 |
||
| 56 | * @var array |
||
| 57 | */ |
||
| 58 | private $fileTitle=[]; |
||
| 59 | |||
| 60 | |||
| 61 | /** |
||
| 62 | * 左侧分组字段 |
||
| 63 | * @var array |
||
| 64 | */ |
||
| 65 | private $group_left = []; |
||
| 66 | |||
| 67 | |||
| 68 | /** |
||
| 69 | * 获取sheet表格数目 |
||
| 70 | * @var |
||
| 71 | */ |
||
| 72 | private $sheetCount = 1; |
||
| 73 | |||
| 74 | /** |
||
| 75 | * 字段映射方式 |
||
| 76 | * @var int |
||
| 77 | */ |
||
| 78 | private $fieldMappingMethod = ConstCode::FIELD_MAPPING_METHOD_FIELD_CORRESPONDING_NAME; |
||
| 79 | |||
| 80 | /** |
||
| 81 | * @param $data |
||
| 82 | * @return $this |
||
| 83 | */ |
||
| 84 | public function setData($data){ |
||
| 85 | $this->data = $data; |
||
| 86 | return $this; |
||
| 87 | } |
||
| 88 | |||
| 89 | /** |
||
| 90 | * @param $data |
||
| 91 | * @return $this |
||
| 92 | */ |
||
| 93 | public function getData(){ |
||
| 94 | return $this->data; |
||
| 95 | } |
||
| 96 | |||
| 97 | |||
| 98 | /** |
||
| 99 | * @return void |
||
| 100 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
| 101 | */ |
||
| 102 | public function initWorkSheet() |
||
| 103 | { |
||
| 104 | $this->_col = 0; |
||
| 105 | $this->_row = 1; |
||
| 106 | $this->fileTitle = []; |
||
| 107 | $this->data = []; |
||
| 108 | $this->field = []; |
||
| 109 | if($this->freezePane) $this->workSheet->freezePane($this->freezePane); //冻结窗格 |
||
| 110 | } |
||
| 111 | |||
| 112 | /** |
||
| 113 | * @param $fileTitle |
||
| 114 | * @param $data |
||
| 115 | * @return $this |
||
| 116 | * @throws TinymengException |
||
| 117 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
| 118 | */ |
||
| 119 | public function setWorkSheetData($fileTitle,$data) |
||
| 120 | { |
||
| 121 | if(isset($fileTitle['title_row']) || isset($fileTitle['group_left'])){ |
||
| 122 | /** |
||
| 123 | * $fileTitle = [ |
||
| 124 | * 'title_row'=>1, |
||
| 125 | * 'group_left'=>[], |
||
| 126 | * 'title'=>[ |
||
| 127 | * '姓名'=>'name' |
||
| 128 | * ], |
||
| 129 | * ]; |
||
| 130 | */ |
||
| 131 | $this->title_row = $fileTitle['title_row']??1; |
||
|
Bug
Best Practice
introduced
by
|
|||
| 132 | $this->group_left = $fileTitle['group_left']??[]; |
||
| 133 | $titleData = $fileTitle['title']??[]; |
||
| 134 | }else{ |
||
| 135 | /** |
||
| 136 | * $fileTitle = [ |
||
| 137 | * '姓名'=>'name', |
||
| 138 | * ]; |
||
| 139 | */ |
||
| 140 | $titleData = $fileTitle; |
||
| 141 | } |
||
| 142 | // 根据字段映射方式处理 title |
||
| 143 | if ($this->fieldMappingMethod === ConstCode::FIELD_MAPPING_METHOD_FIELD_CORRESPONDING_NAME) { |
||
| 144 | $this->fileTitle = array_flip($titleData);// 字段对应名称方式 - 需要将键值对调 |
||
| 145 | }else{ |
||
| 146 | $this->fileTitle = $titleData;// 名称对应字段方式 - 保持原样 |
||
| 147 | } |
||
| 148 | $this->data = $data; |
||
| 149 | |||
| 150 | /** 设置第一行格式 */ |
||
| 151 | if($this->mainTitleLine == true){ |
||
| 152 | $this->excelHeader(); |
||
| 153 | } |
||
| 154 | |||
| 155 | /** 设置表头 **/ |
||
| 156 | $this->excelTitle(); |
||
| 157 | |||
| 158 | /** 获取列表里所有字段 **/ |
||
| 159 | foreach ($this->fileTitle as $key => $val){ |
||
| 160 | if(is_array($val)){ |
||
| 161 | foreach ($val as $k => $v){ |
||
| 162 | $this->field[] = $v; |
||
| 163 | } |
||
| 164 | }else{ |
||
| 165 | $this->field[] = $val; |
||
| 166 | } |
||
| 167 | } |
||
| 168 | /** 查询结果赋值 **/ |
||
| 169 | if(!empty($this->data)){ |
||
| 170 | $this->excelSetValue(); |
||
| 171 | } |
||
| 172 | return $this; |
||
| 173 | } |
||
| 174 | |||
| 175 | /** |
||
| 176 | * excelSetValue |
||
| 177 | * @author: Tinymeng <[email protected]> |
||
| 178 | * @time: 2022/2/22 11:43 |
||
| 179 | */ |
||
| 180 | public function excelSetValue(){ |
||
| 209 | ); |
||
| 210 | } |
||
| 211 | } |
||
| 212 | } |
||
| 213 | |||
| 214 | /** |
||
| 215 | * @return void |
||
| 216 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
| 217 | */ |
||
| 218 | public function excelHeader(){ |
||
| 219 | $row = 1; |
||
| 220 | $this->workSheet->setCellValue('A'.$row, $this->mainTitle); |
||
| 221 | $this->workSheet->mergeCells('A'.$row.':'.$this->cellName($this->_col-1).$row); |
||
| 222 | } |
||
| 223 | |||
| 224 | /** |
||
| 225 | * @return void |
||
| 226 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
||
| 227 | */ |
||
| 228 | private function excelTitle(){ |
||
| 229 | if($this->mainTitleLine == true){ |
||
| 230 | $this->_row ++;//当前行数 |
||
| 231 | } |
||
| 232 | |||
| 233 | $_merge = $this->cellName($this->_col); |
||
| 234 | foreach ($this->fileTitle as $key => $val) { |
||
| 235 | if(!empty($this->titleHeight)) { |
||
| 236 | $this->workSheet->getRowDimension($this->_col)->setRowHeight($this->titleHeight);//行高度 |
||
| 237 | } |
||
| 238 | $rowName = $this->cellName($this->_col); |
||
| 239 | $this->workSheet->getStyle($rowName . $this->_row)->getAlignment()->setWrapText(true);//自动换行 |
||
| 240 | if (is_array($val)) { |
||
| 241 | $num = 1; |
||
| 242 | $_cols = $this->_col; |
||
| 243 | foreach ($val as $k => $v) { |
||
| 244 | $this->workSheet->setCellValue($this->cellName($_cols) . ($this->_row+1), $k); |
||
| 245 | if(!empty($this->titleWidth)) { |
||
| 246 | $this->workSheet->getColumnDimension($this->cellName($_cols))->setWidth($this->titleWidth); //列宽度 |
||
| 247 | }else{ |
||
| 248 | $this->workSheet->getColumnDimension($this->cellName($_cols))->setAutoSize(true); //自动计算宽度 |
||
| 249 | } |
||
| 250 | if ($num < count($val)) { |
||
| 251 | $this->_col++; |
||
| 252 | $num++; |
||
| 253 | } |
||
| 254 | $_cols++; |
||
| 255 | } |
||
| 256 | $this->workSheet->mergeCells($_merge . $this->_row.':' . $this->cellName($this->_col) .$this->_row); |
||
| 257 | $this->workSheet->setCellValue($_merge . $this->_row, $key);//设置值 |
||
| 258 | } else { |
||
| 259 | if ($this->title_row != 1) { |
||
| 260 | $this->workSheet->mergeCells($rowName . $this->_row.':' . $rowName . ($this->_row + $this->title_row - 1)); |
||
| 261 | } |
||
| 262 | $this->workSheet->setCellValue($rowName . $this->_row, $key);//设置值 |
||
| 263 | if(!empty($this->titleWidth)){ |
||
| 264 | $this->workSheet->getColumnDimension($rowName)->setWidth($this->titleWidth); //列宽度 |
||
| 265 | }else{ |
||
| 266 | $this->workSheet->getColumnDimension($rowName)->setAutoSize(true); //自动计算宽度 |
||
| 267 | } |
||
| 268 | } |
||
| 269 | $this->_col++; |
||
| 270 | $_merge = $this->cellName($this->_col); |
||
| 271 | } |
||
| 272 | $this->_row += $this->title_row;//当前行数 |
||
| 273 | } |
||
| 274 | |||
| 275 | /** |
||
| 276 | * excel单元格赋值 |
||
| 277 | * @author tinymeng |
||
| 278 | * @param array $val 数据 |
||
| 279 | */ |
||
| 280 | private function excelSetCellValue($val) |
||
| 337 | } |
||
| 338 | |||
| 339 | /** |
||
| 340 | * 单元格合并并赋值 |
||
| 341 | * @param array $data 数据 |
||
| 342 | * @param $group_left_count |
||
| 343 | * @author tinymeng |
||
| 344 | */ |
||
| 345 | private function excelGroupLeft(array $data, $group_left_count) |
||
| 346 | { |
||
| 347 | // 获取分组字段在field中的实际位置 |
||
| 348 | $group_field_positions = []; |
||
| 349 | foreach($this->group_left as $group_field){ |
||
| 350 | $position = array_search($group_field, $this->field); |
||
| 351 | if($position !== false){ |
||
| 352 | $group_field_positions[] = $position; |
||
| 353 | } |
||
| 354 | } |
||
| 355 | |||
| 356 | if(empty($group_field_positions)){ |
||
| 357 | throw new TinymengException(StatusCode::COMMON_PARAM_INVALID, '分组字段未在标题中定义'); |
||
| 358 | } |
||
| 359 | |||
| 360 | $group_start = $this->_row; |
||
| 361 | foreach ($data as $key => $val){ |
||
| 362 | // 第一级分组的合并单元格 |
||
| 363 | $rowName = $this->cellName($group_field_positions[0]); // 使用第一个分组字段的实际位置 |
||
| 364 | $coordinate = $rowName.$this->_row.':'.$rowName.($this->_row+$val['count']-1); |
||
| 365 | $this->workSheet->mergeCells($coordinate); |
||
| 366 | $this->workSheet->setCellValue($rowName.$this->_row, $key); |
||
| 367 | |||
| 368 | if($group_left_count == 1){ |
||
| 369 | foreach ($val['data'] as $data){ |
||
| 370 | $this->excelSetCellValue($data); |
||
| 371 | } |
||
| 372 | }else{ |
||
| 373 | $sub_group_start = $this->_row; |
||
| 374 | $rowName = $this->cellName($group_field_positions[1]); // 使用第二个分组字段的实际位置 |
||
| 375 | |||
| 376 | foreach ($val['data'] as $k => $v){ |
||
| 377 | $coordinate = $rowName.$sub_group_start.':'.$rowName.($sub_group_start+$v['count']-1); |
||
| 378 | $this->workSheet->mergeCells($coordinate); |
||
| 379 | $this->workSheet->setCellValue($rowName.$sub_group_start, $k); |
||
| 380 | |||
| 381 | foreach ($v['data'] as $data){ |
||
| 382 | $this->excelSetCellValue($data); |
||
| 383 | } |
||
| 384 | |||
| 385 | $sub_group_start = $sub_group_start + $v['count']; |
||
| 386 | } |
||
| 387 | } |
||
| 388 | |||
| 389 | $this->_row = $group_start + $val['count']; |
||
| 390 | $group_start = $this->_row; |
||
| 391 | } |
||
| 392 | } |
||
| 393 | |||
| 394 | /** |
||
| 395 | * 二位数组获取每一级别数量 |
||
| 396 | * @author tinymeng |
||
| 397 | * @param array $data 二维数组原始数据 |
||
| 398 | * @return array |
||
| 399 | */ |
||
| 400 | private function arrayCount($data=[]) |
||
| 418 | } |
||
| 419 | |||
| 420 | |||
| 421 | } |
||
| 422 |
Loading history...
