PHPExcel excel读取不适用于某些带有计算的单元格

问题描述

我使用PHPExcel lib读取Codeigniter项目中的excel文件.它不读取某些带有计算的单元格.它显示为#VALUE!，但某些计算值正在同一excel表中读取.这些细胞怎么了?

I use PHPExcel lib for read the excel file in Codeigniter project. It is not read some cells with calculation. It show as #VALUE! But some values with calculation is reading in same excel sheet. Whats wrong with those cells?

具有以下计算的单元格无法读取

Cells with have following calculation is not reading

=+D109*1000
=+B16/B13
=+D23/$D$109

具有以下计算的单元格正在读取

Cells with have following calculation is reading

=+B10-B11
=+C10-C11

但是所有单元格都在读取一些excel表.此问题带有xlsx格式

这是我的代码

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$this->excel = $objReader->load($path);
$this->excel->setActiveSheetIndex($sheet);
$data = $this->excel->getActiveSheet()->toArray(null, true, true, true);

print_r($data);

我向Google查询. getCalculatedValue()应该用于读取计算值.但是我不能一一使用.是否有一种将所有工作表读取为数组的方法?

I check with google. getCalculatedValue() should use for read calculated values. But i can't use it one by one. Is it has a method to read all sheet as array?

我怎么用以下方法检查一些单元格

How ever i checked some cells with following way

$this->excel->getActiveSheet()->getCell('B18')->getCalculatedValue() // return #VALUE!
$this->excel->getActiveSheet()->getCell('B18')->getOldCalculatedValue() //return 0.4211

我如何使用toArray使用旧的计算值?

How i use old calculated value using toArray?

推荐答案

最后，我使用循环获得了旧的getOldCalculatedValue.

Finally I get old getOldCalculatedValue using loop.

$data = $this->excel->getActiveSheet()->toArray(null, true, true, true);

//This code add for some calculated cells were not reading in xlsx format
foreach ($data as $no => $row) {
    foreach ($row as $key => $value) {
        if (isset($value) && $value == '#VALUE!') {
            $data[$no][$key] = $this->excel->getActiveSheet()->getCell($key.$no)->getOldCalculatedValue();
        }
    }
}

这篇关于PHPExcel excel读取不适用于某些带有计算的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！