How to set currency dataType of a column not a specific cell ?

Topics: Developer Forum
Sep 3, 2012 at 9:15 AM
Edited Sep 3, 2012 at 9:29 AM

Hello,

          I am using PHPExcel to generate a report(.xlsx)  where i have to mention curreny in

$1234.56 format. I didnt find any method by which i can set a column's dataType to currency.

Currently what i am doing

I choose that column index and use below in foreach loop

 

<?php

 foreach ($arr_report as $row){

  ++$j; $c=0;   

  foreach($row as $col){

  $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($c)->setWidth(strlen($col) + 6);                           $colString = PHPExcel_Cell::stringFromColumnIndex($c);

  if($colString === 'U'){

  $col = empty($col) ? '' : '$'.$col; // add prefix $

  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($c, $j, $col,PHPExcel_Cell_DataType::TYPE_NUMERIC);

  } else {

  $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($c, $j, $col);  } $c++;

  }

 }

}?>

but when report generates, an error indicator on each cell of that column  saying

 

the number is this cell is formatted as text or preceded by an apostrophe
Is there any fine solution of this kind of error?

 


 

Here is your two discussion which i like to mention 

in this discussion you have mentioned below code

$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(17,$cont, $row[3], PHPExcel_Cell_DataType::TYPE_NUMERIC);

but this will set a specific cell not the complete column.

 

in other discussion  you have mentioned

$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode("#,##0 _€");

 I have tried this but this not working too.

Coordinator
Sep 3, 2012 at 10:44 AM
Edited Sep 3, 2012 at 10:44 AM

This block of code

$col = empty($col) ? '' : '$'.$col; // add prefix $

is modifying your numeric (currency) value to a string.

 

A string is not a number, so your next line of code:

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($c, $j, $col,PHPExcel_Cell_DataType::TYPE_NUMERIC);

is meaningless.

 

In MS Excel, if you want a currency value in a cell, you set the cell value as the number, and apply a number format mask to display it as a currency value. This doesn't change the value in the cell (it's still a number) but simply changes the way it is displayed. PHPExcel does exactly the same.

 

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($c, $j, $col);
if($colString === 'U'){ 
    if (!empty($col)) {
        $objPHPExcel->getActiveSheet()->getStyle($colString.$j)
            ->getNumberFormat()
            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD);
    }
}
$c++;