[大数据]phpexcel 获取公示对应值以及合并取值 已解决

在操作 phpexcel 中发现 mysql中 取的值是

????????

如果某个cell使用到了公式
通过getValue()获取的是公式本身
????????而通过

getCalculatedValue()会有对象

getFormattedValue()获取到的是公式计算后的值

同时 合并取值 参考代码

$referenceRow=array();
for ( $row = 2; $row <= $noOfBooks; $row++ ){
     for ( $col = 0; $col < 7; $col++ ){
         if (!$objPHPExcel->getActiveSheet()->getCellByColumnAndRow( $col, $row )->isInMergeRange() || $objPHPExcel->getActiveSheet()->getCellByColumnAndRow( $col, $row )->isMergeRangeValueCell()) {
             // Cell is not merged cell
             $data[$row][$col] = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow( $col, $row )->getCalculatedValue();
             // 取合并之前的数据
             $referenceRow[$col]=$data[$row][$col];  
             //This will store the value of cell in $referenceRow so that if the next row is merged then it will use this value for the attribute
          } else {
             // Cell is part of a merge-range
             // 获取合并之前的数据
             $data[$row][$col]=$referenceRow[$col];  
             //The value stored for this column in $referenceRow in one of the previous iterations is the value of the merged cell
          }

      }
 }

最后 成品代码

        $output->writeln([
            'Start export data',
            '---------------',
        ]);
        $file = './web/doc/WaterSupplyProject.xls';
        $objReader = \PHPExcel_IOFactory::createReader('Excel5'); /*Excel5 for 2003 excel2007 for 2007*/
        $objPHPExcel = $objReader->load($file); //Excel 路径
        $objWorksheet = $objPHPExcel->getActiveSheet();
        $highestRow = $objWorksheet->getHighestRow(); // 取得总行数
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn); //总列数

        $referenceRow = array();

        $this->truncateWaterFunctionalTable();
        for ($row = 38; $row <= $highestRow; $row++) {
            $data = array();
            //注意highestColumnIndex的列数索引从0开始
            for ($col = 0; $col < $highestColumnIndex; $col++) {

                if (!$objWorksheet->getCellByColumnAndRow($col, $row)->isInMergeRange() || $objWorksheet->getCellByColumnAndRow($col, $row)->isMergeRangeValueCell()) {
                    // 获取单元格合并值
                    // https://stackoverflow.com/questions/40960159/read-excel-sheet-containing-merged-cells-using-phpexcel
                    // Cell is not merged cell
                    $str = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();

                    $referenceRow[$col] = $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();
                    //This will store the value of cell in $referenceRow so that if the next row is merged then it will use this value for the attribute
                } else {
                     // Cell is part of a merge-range
                    $str = $referenceRow[$col];
                    //The value stored for this column in $referenceRow in one of the previous iterations is the value of the merged cell
                    //
                }

                if (is_object($str)) {
                    $str = $str->__toString();
                }

                $data[$col] = $str;
            }
            $res = $this->add($data, $output);

            if ($res) {
                $output->writeln([
                    '' . $row . 'ROW SUCCESS',
                ]);
            }
        }

感谢?https://github.com/PHPOffice/PHPExcel/issues/643

感谢?php - Read excel sheet containing merged cells using PHPExcel - Stack Overflow