setCellValueByColumnAndRow's strange behaviour

Topics: Developer Forum, Project Management Forum, User Forum
Dec 23, 2009 at 7:15 AM
Edited Dec 23, 2009 at 7:33 AM

 

error_reporting(E_ALL);  

The following is my code, I have two problem:

1. it seems that the row is start with 1 , but the column is start with 0.

2. I cannot save a formula into the cell with SetCellValueByColumnAndRow. When I open the file I generate, the cell 11 is empty, the formula is gone. However, If I quote the formula to be a string ( add a '  to the beginning), then I can get the formula string without problem.

 My PHP: 5.2.8, PHPExcel: 1.7.0, Excel: Chinese Excel 2007, OS: Chinese Windows XP SP3

Regards,

Francois

 

error_reporting(E_ALL);  

require_once 'classes/PHPExcel.php'; 

require_once 'classes/PHPExcel/IOFactory.php'; 

      for($rr=1;$rr<10;$rr++) {          

          for($cc=0;$cc<10;$cc++) {      

            $objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($cc, $rr , $rr+$cc);  

          }

          $formula = '=sum(r' . $rr . 'c1:r' . $rr . 'c10)';          

          $objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(11, $rr, $formula); 

      }

$objPHPExcel->setActiveSheetIndex(0);  

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment;filename="myfile.xls"');

header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save('php://output');

exit; 

 

Developer
Jan 17, 2010 at 10:59 AM
FrancoisYu wrote:
error_reporting(E_ALL);  

1. it seems that the row is start with 1 , but the column is start with 0.

Yes, that is intentional.

 

FrancoisYu wrote:

2. I cannot save a formula into the cell with SetCellValueByColumnAndRow. When I open the file I generate, the cell 11 is empty, the formula is gone. However, If I quote the formula to be a string ( add a '  to the beginning), then I can get the formula string without problem.

What does your formula look like?

Oct 6, 2010 at 8:59 AM
Edited Oct 6, 2010 at 9:00 AM
koyama wrote:
FrancoisYu wrote:
error_reporting(E_ALL);  

1. it seems that the row is start with 1 , but the column is start with 0.

Yes, that is intentional.

 

So then why is it not stated so in the documentation? It says:

"

// Set cell B8
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 8, 'Some value');

"

 

I lost almost half a day until I figured this out. It's very not nice.