laravel PhpOffice 读取表格数据

    /**
     * 更新安通船期
     * Description 
     * Author Allen
     * Date 2024-11-11
     * @param  Request $request [description]
     * @return [type]           [description]
     */
    public function updateAntongShipDate(Request $request){
        set_time_limit(0);
        $file = request()->file('file');
        if (empty($file) || !$file->isValid()) {
            return '请选择文件';
        }

        $extension = $file->getClientOriginalExtension();//文件后缀名
        $fileType  = ['xls', 'xlsx'];
        if (!in_array($extension, $fileType)) {
            return '请上传xls\xlsx文件';
        }

        if ($file->isValid()) {

            $data['clientName'] = $file->getClientOriginalName();//文件原名
            $data['tmpName']    = $file->getFileName();
            $data['realPath']   = $file->getRealPath();//该文件所在路径
            $data['extension']  = substr($data['clientName'], strripos($data['clientName'], '.', '-1') + 1);
            // $data['extension']  = $file->getClientOriginalExtension();//文件后缀名
            if ($data['extension'] != "xls" && $data['extension'] != "xlsx") {
                echo('不是Excel文件，重新上传后缀名为xls的Excel表');
                exit;
            }

            $data['mimeTye'] = $file->getMimeType();//文件类型
            $data['newName'] = $data['clientName'];//存储文件名
            $file->move(storage_path('tempdir/'), $data['newName']);
            $filePath = storage_path('tempdir/') . $data['clientName'];

        } else {
            return '文件不可用';
        }
        try{

            // 加载Excel文件
            $spreadsheet = IOFactory::load($filePath);
            // 获取工作簿中的最后一个工作表
            $lastSheetIndex = $spreadsheet->getSheetCount() - 1; // 工作表索引是从 0 开始的
            $worksheet = $spreadsheet->getSheet($lastSheetIndex);

            // 获取所有合并的单元格
            $mergedCells = $worksheet->getMergeCells();

            // 查找包含 "东莞港二期-温州-天津" 的行
            $rowsToRead = [];

            foreach ($mergedCells as $cell) {
                [$startCell, $endCell] = preg_split('/:/', $cell);
                $cellValue = $worksheet->getCell($startCell)->getFormattedValue();
                if ($cellValue === '东莞港二期-温州-天津') {
                    $startRow=$worksheet->getCell($startCell)->getRow();
                    $endRow=$worksheet->getCell($endCell)->getRow();
                    $targetRow=$startRow-2;
                    $start_port=$worksheet->getCell('I'.$targetRow)->getFormattedValue();
                    // $end_port=$worksheet->getCell('M'.$targetRow)->getFormattedValue();

                    // 读取 A 列到 S 列，所有值
                    $data = [];
                    for ($row = $startRow; $row <= $endRow; $row++) {
                        $rowData = [];
                        for ($col = 'A'; $col <= 'S'; $col++) {
                            $cellValue = $worksheet->getCell($col . $row)->getFormattedValue();
                            $rowData[$col] = $cellValue;
                        }
                        $data[] = $rowData;
                    }
                }
            }

      
            unlink($filePath);
            return '成功更新数据'.count($data).'条';
            // return $res;
        } catch (\Exception $e) {
            $res = ['code' => 600, 'data' => '文件数据读取失败', 'getMessage' => $e->getMessage(), 'getFile' => $e->getFile(), 'getLine' => $e->getLine(), 'getTrace' => $e->getTrace()];
            return $res;

        }

    }