PHPExcel:使用rangetoArray()函数读取单元格值并应用求和公式

问题描述

我正在尝试使用rangetoArray()函数读取单元格公式，然后返回数组值，当此函数读取范围值时，我想用phpexcel库对其求和.

I'm trying to read the cell formulas with rangetoArray() function and then there is array values return i want to sum it with the phpexcel library when this function read the range values.

include 'PHPExcel/Classes/PHPExcel.php';
include 'PHPExcel/Classes/PHPExcel/IOFactory.php'

$objPHPExcel = PHPExcel_IOFactory::load($uploadedFile);

$cellValues = $objPHPExcel->getActiveSheet()->rangeToArray('A1:A2');

///基本上，我正在为每个循环中的和值执行此操作..但这是自定义创建的.我想要用于SUM和其他数学运算的PHPExcel库函数.

//Basically i'm doing this for sum values in for each loop .. but it is custom created. i want PHPExcel Library Function for SUM and other Mathmatic Operations.

foreach ($cellValues as $cell) {
    foreach ($cell as $finalValue) {
        $sumVal += $finalValue;
    }
}

//我想通过rangetoArray('A1:A5');读取值像这样，并使用一些PHPExcel Library函数/方法自动将其求和.

//I want to read value through rangetoArray('A1:A5'); like this and automatic sum it with some PHPExcel Library function / method.

谢谢

推荐答案

选项1

创建一个公式，将其分配给一个虚拟单元格(在本例中为A1，尽管它不会以任何方式更改单元格A1的值)，然后使用计算引擎执行该公式:

Create a formula, assigning to a dummy cell (A1 in this case, though it doesn't change the value of cell A1 in any way), and using the calculation engine to execute that formula:

$sumFormula = '=SUM(A1:B2)';
$rangeSum = PHPExcel_Calculation::getInstance()->calculateFormula(
    $sumFormula,
    'A1',
    $objPHPExcel->getActiveSheet()->getCell('A1')
);
var_dump($rangeSum);

选项#2

直接从您自己的代码中调用SUM()函数，将toArray()方法的结果传递给该函数:

Call the SUM() function directly from your own code, passing the result of the toArray() method to the function:

$rangeSum = PHPExcel_Calculation_MathTrig::SUM(
    $objPHPExcel->getActiveSheet()->rangeToArray('A1:B2')
);
var_dump($rangeSum);

选项3

使用doSum()方法创建自己的类，该类将接受fromArray()调用的结果:

Create your own class which will take the result of a fromArray() call, with a doSum() method:

class mySummer{
    protected $_cellArray;

    public function __construct(array $cells = array()) {
        $this->_cellArray = PHPExcel_Calculation_Functions::flattenArray($cells);
    }

    public function doSum(){
        return array_sum($this->_cellArray);
    }

}


$summer = new mySummer(
    $objPHPExcel->getActiveSheet()->rangeToArray('A1:B2')
);
var_dump($summer->doSum());

这篇关于PHPExcel:使用rangetoArray()函数读取单元格值并应用求和公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！