PHP PhpSpreadsheet读取分割单元格数据

需求描述：

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Shared\Date as PHPExcel_Shared_Date;
public function readExcel()
{
 
    $path = 'D:\非遗\市级项目及传承人详情.xlsx';
    $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
    // 打开文件、载入excel表格
    $spreadsheet = $reader->load($path);
    $sheet = $spreadsheet->getActiveSheet();
    $columnCnt = $sheet->getHighestColumn();
    /* 兼容原逻辑，循环时使用的是小于等于 */
    $cols_num = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($columnCnt);
    // 获取总行数
    $rows_num = $sheet->getHighestRow();
    $final_arr = [];
    for($row = 1; $row < $rows_num+1; $row++){
        $arr = [];
        for ($column = 1; $column <= $cols_num; $column++) {
            $cellName = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($column);
            $cell = $sheet->getCell($cellName . $row)->getValue();
            $ctype = $sheet->getCell($cellName . $row)->getDataType(); 
            // 如果单元格为函数
            if ($ctype == 'f') {
                $cell = $sheet->getCell($cellName . $row)->getFormattedValue();
            }
            if (!$cell || $cell == "") {
                $cell = $this->fillEmptyMergeCells($sheet,$cellName,$row);
                // echo "该单元格[".$cellName.$row."]属于合并单元格，值为:".$cell;
            }
            $cell = str_replace("\n","",$cell);
            $cell = str_replace("\t","",$cell);
            $cell = str_replace(" ","",$cell);
            $arr[] = $cell;
        }
        $final_arr[] = $arr;
    }
    
    echo "<pre>";
    print_r($final_arr);
    
}
// 表格数据处理
public function dataHandle($final_arr)
{
    unset($final_arr[0]); // 表头
    $arr = [];
    // 数据按id分组
    foreach ($final_arr as $key => $value) {
        $values = [];
        $values['id'] = $value[0];
        $values['leibie'] = $value[1];
        $values['bianhao'] = $value[2];
        $values['xiangmu'] = $value[3];
        $values['area'] = $value[4];
        $values['ccr'] = $value[5];
        $arr[$value[0]][] = $values;
    }
    // 数据编号和批次处理，删除重复的数据
    foreach ($arr as $key => $value) {
        foreach ($value as $keys => $values) {
            $arr[$key][$keys]['pici'] = $value[1]['bianhao'];
            $arr[$key][$keys]['bianhao'] = $value[0]['bianhao'];
            if (empty($values['ccr']) || $value[0]['ccr'] == $value[1]['ccr']) {
                unset($arr[$key][1]);
                break;
            }
        }
    }
    return $arr;
}
// 数据表格合并单元格数据获取
public function fillEmptyMergeCells($sheet,$cellName,$row)
{
    $mergeCells = array_values($sheet->getMergeCells());
    for ($i = 0; $i < count($mergeCells); $i++) {
        $cellArr = [];
        // explode merge cells range,example N25:N27
        $CellIndex = explode(":", $mergeCells[$i]);
        $cellname = $cellName.$row;
        $count1 = mb_strlen($CellIndex[0]) - 1;
        // start index
        $StartIndex = (int) substr($CellIndex[0], 1, $count1);
        // end index
        $count2 = mb_strlen($CellIndex[1]) - 1;
        $EndIndex = (int) substr($CellIndex[1], 1, $count2);
        // column name example "A" ,"B", max "Z"
        $ColumnName = substr($CellIndex[0], 0, 1);
        for ($j = $StartIndex; $j <= $EndIndex; $j++) {
            $name = $ColumnName.$j;
            // 如果单元格为函数
            
            if ($cellname == $name) {
                $type = $sheet->getCell($CellIndex[0])->getDataType(); 
                if ($type == 'f') {
                    $CellValue = (string)$sheet->getCell($CellIndex[0])->getFormattedValue();
                } else {
                    $CellValue = (string)$sheet->getCell($CellIndex[0])->getValue();
                }
                return $CellValue;
                break;
            }
        }
    }
    return false;
}