PHPExcel 插入数组公式 [英] PHPExcel insert array formula
问题描述
我想插入这个数组公式:
I want to insert this array formula:
{=SUM(IF(FREQUENCY(IF(T9:T977=1,MATCH(U9:U977,U9:U977,0)),ROW(U9:U977)-ROW(U9)+1),1))}
但是当我使用时:
$sheet->getCell("C1")->setValue("{=SUM(IF(FREQUENCY(IF(T9:T977=1,MATCH(U9:U977,U9:U977,0)),ROW(U9:U977)-ROW(U9)+1),1))}");
它不起作用,我已经检查了文档,但仍然没有找到任何东西.
It doesn't work, I've checked the documentation, but still haven't found anything.
推荐答案
我找不到任何答案,所以我通过 PHPExcel 并为自己制定了一个解决方案:
I couldn't find any answer so I went through PHPExcel and made myself a solution:
在/PHPExcel/Cell.php
中的 switch($pDataType)
的第 251 行添加:
in /PHPExcel/Cell.php
at row 251 in the switch($pDataType)
add this:
case PHPExcel_Cell_DataType::TYPE_FORMULA_ARRAY:
$this->_value = (string)$pValue;
break;
在/PHPExcel/Cell/DataType.php
中添加这个常量:
const TYPE_FORMULA_ARRAY = 't';
最后在 /PHPExcel/Writer/Excel2007/Worksheet.php
我已经在第 1095 行开始的开关中添加了这个:
At last in /PHPExcel/Writer/Excel2007/Worksheet.php
I've added this in the switch beginning at row 1095:
case 't': // Array Formulae
$objWriter->startElement('f');
$objWriter->writeAttribute('t', 'array');
$objWriter->writeAttribute('ref', $pCellAddress);
$objWriter->writeAttribute('aca', '1');
$objWriter->writeAttribute('ca', '1');
$objWriter->text($cellValue);
$objWriter->endElement();
if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
if ($this->getParentWriter()->getPreCalculateFormulas()) {
$calculatedValue = $pCell->getCalculatedValue();
if (!is_array($calculatedValue) && substr($calculatedValue, 0, 1) != '#') {
$objWriter->writeElement('v', PHPExcel_Shared_String::FormatNumber($calculatedValue));
} else {
$objWriter->writeElement('v', '0');
}
} else {
$objWriter->writeElement('v', '0');
}
}
break;
然后我使用了这样的函数:
Then I used the function like this:
$sheet->getCell("C1")->setValueExplicit("=SUM(IF(FREQUENCY(IF(T9:T977=1,MATCH(U9:U977,U9:U977,0)),ROW(U9:U977)-ROW(U9)+1),1))", PHPExcel_Cell_DataType::TYPE_FORMULA_ARRAY);
当我创建一个 excel 文件时它一切正常!
And it works all good when I'm creating a excel file!
这篇关于PHPExcel 插入数组公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!