setCellValueByColumnAndRow

Jan 17, 2011 at 10:31 AM
Edited Jan 17, 2011 at 11:25 AM

I'm sure its something stupid but.....

 

this was working fine, until my table got tol large ie my  chr($starting_pos+$index_pos) was more than Z          

 $objPHPExcel->getActiveSheet()->setCellValue(chr($starting_pos+$index_pos) . $rowcounter, $showmonth);

so i thought no problems i'll use setCellValueByColumnAndRow, but this writes NOTHING into the cells.
Whats wrong please ?

 $posnow = $starting_pos+$index_pos;
 $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($posnow,$rowcounter, $showmonth);

 

EDIT - i'm wrong it is writing stuff just in a totally wrong place.'
whereas previously the first call of this  $objPHPExcel->getActiveSheet()->setCellValue(chr($starting_pos+$index_pos) . $rowcounter, $showmonth);
would write in D4

this  $posnow = $starting_pos+$index_pos;
 $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($posnow,$rowcounter, $showmonth);

writes into CJ4 .......so the next question is why ?

Ok got it, this may help someone

Its to do with the asci charset, where A is 65, so you need to do this


$posnow = $starting_pos+$index_pos-65;
 $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($posnow,$rowcounter, $showmonth);

and then everythings good

 

EDIT - new question - is there an easy way to convert between these ie how can i get AB as column when i know the column number ?
I am trying to write in the formula =SUM(A1 : AB1) as example and so need the excel lettering, or can you write the formula without the lettering ?

 

 

Jan 17, 2011 at 12:21 PM

this works

 

    function getColumnLetter($columnNumber) {
        if ($columnNumber > 26) {
            $columnLetter = Chr(intval(($columnNumber - 1) / 26) + 64) . Chr((($columnNumber - 1) % 26) + 65);
        } else {
            $columnLetter = Chr($columnNumber + 64);
        }
    return $columnLetter;
    }

Coordinator
Jan 17, 2011 at 12:46 PM

So does using the built-in functions designed for just this purpose:

PHPExcel_Cell::stringFromColumnIndex();

Or you could be clever, and take advantage of PHP's character incrementor:

$col = 'A'
for($x = 1; $x < 256; $x++) {
   echo $col++,'<br />';
}