来自其他工作表的PHPExcel公式COUNTIFS

Question

I am working on a very large sheet, that sheet is created from database and have 40 columns with more than 3000 rows, I set its title Backup Data in Php Excel

Now I have created a new sheet where I can set value of different cells. I need to use countifs formula to show results from Backup Data sheet to Summary Sheet

$sheetSummary->setCellValue('E39', '=COUNTIFS(\'Backup Data\'!$H:$H,"=KTM", \'Backup Data\'!$J:$J,"=Full Size", \'Backup Data\'!$Y:$Y,"<>SPD", \'Backup Data\'!$U:$U, ">=1-Dec-12", \'Backup Data\'!$U:$U, "<=31-Dec-12")');

When I use a formula in php

A file is prompted but when I open the file in notepad it is having this text http://pastie.org/5584430

Answer 1

The problem is the use of column references such as $H:$H and $J:$J aren't yet supported in formulae by PHPExcel. use ranges instead (such as $H1:$H3000) or disable precalculation on save

EDIT

To disable precalculation on save:

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setPreCalculateFormulas(FALSE);
$objWriter->save('myFile.xlsx');