data from database to excel in number format

Topics: Developer Forum, User Forum
Jan 31, 2011 at 1:56 PM
Dear all,

i have a problem using the great PHPExcel library.

I read data from Oracle database and I would like to write them into Excel.

if I write:

     ->setCellValue('E1', $row['CURRENT_VALUE']);

r$row[CURRENT_VALUE'] is my variable from database that contains 291,083751927132 (number)

 The result on Excel is a text cell instead of a number cell.

If i write

           ->getCell('E'. $count)->setValueExplicit($row['CURRENT_VALUE'], PHPExcel_Cell_DataType::TYPE_NUMERIC);

The result on Excel is a number Cell (as I want) but without decimal part like this:  291

I'm desperate

Can anyone help me?


Jan 31, 2011 at 2:13 PM
Edited Jan 31, 2011 at 2:13 PM

The value you're getting from your database (291,083751927132) is not a PHP number, but a string containing a locale-formatted numeric value... recognisable by the comma (",") decimal separator rather than the point ("."). When you do

setCellValue('E1', $row['CURRENT_VALUE']);

with this value, then PHPExcel treats it as the string that it really is.

When you do

setValueExplicit($row['CURRENT_VALUE'], PHPExcel_Cell_DataType::TYPE_NUMERIC);

then PHPExcel casts the string to a numeric using standard PHP casting rules, which basically strips everything following the first non-numeric character... the comma (",").


You'll need to convert the string to a normal PHP floating-point value using something like:

$numberAsFloat = (float) str_replace(',','.',$row['CURRENT_VALUE']);

and then you can assign it to the cell normally

setCellValue('E1', $numberAsFloat);
Jan 31, 2011 at 2:29 PM

Thank you very much MarkBaker! It works fine!!!

Any idea to format an entire column with:



istead to specify each Cell can I specify entire column 'E' ?




Feb 1, 2011 at 10:07 AM

You should be able to specify a row, column or range of cells, or a range of rows or columns.

However, there are some issues with row and column ranges, but cell ranges such as
work without any known problems
Jun 23, 2011 at 10:28 AM
Edited Jun 23, 2011 at 10:54 AM

Hi Mark,

I suffer from a similar problem just vice versa: In my database I have values stored like 4.1 (basically an expression for software version) as a varchar. When I now output it to excel i get 4,1 - with a comma. I tried everything from setting the format code to text (@) or from various conversion attempts like settype or quoting the variabe.

I also tried:

$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(23, $row, $incomingloader, PHPExcel_Cell_DataType::TYPE_STRING);

In this case the cell is empty!

When I enter the value 4.1 manually in Excel it works fine.  I am really despaired. Have you got another hint for me?

Thanks in advance