Help with some formatting

Topics: Developer Forum, User Forum
Jun 17, 2011 at 2:51 PM

Hi,

I am trying to apply several kinds of formatting in my generated Excel 2007 document, using the latest version of PHPExcel, however I am having trouble to accomplish the following. If any of you could help me shed some light on these problems, I would be very thankful!

a) Apply rounding

I am trying to make the following to work:

$sheet->setCellValue("F".$i, "=ROUNDUP(D".$i."*(-E$".$i."+1);1)");

But this code throws a Formula Error Exception.

My question is, how do you ROUNDUP a cell?

b) I am trying to apply the percentage formatting to a cell with the value "25%", but I can't find that data type:

$sheet->getCell("E".$i)->setValueExplicit("25%", PHPExcel_Cell_DataType::TYPE_PERCENT);

How can you make sure that the cell appears as "25%" and not "0,25"?

Thanks for your all you answers.

Best

Per

Jun 17, 2011 at 3:09 PM
Edited Jun 17, 2011 at 3:11 PM

I find those data types not that reliable presently -- try this instead:

$sheet->getStyle($cell)->getNumberFormat()->setFormatCode('0%');

LibreOffice or OpenOffice.org will show you all format codes including custom ones.

This should also round it up if it had decimal places.

Jun 18, 2011 at 11:39 PM

Scott,

I wonder if you might help me out on this:

In my Db I have a text value stored like 4.1. Unfortunately phpExcel creates a file showing 4,1 (which would be the german equivalent. As 4.1 is supposed to be an expression for software version in my case, I definitely want it to be show correctly as 4.1

I use a pre-formatted excel sheet. When I type the value manually everything is fine. When I let phpExcel do the work it goes wrong and yields 4,1.  Grmmpf...

I'd appreciate any hint!

 

Chrs

Michael

Jun 20, 2011 at 1:49 PM

Try format code @ (text).

Jun 22, 2011 at 10:51 PM
Edited Jun 22, 2011 at 10:53 PM

I tried! I use

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(24-$ColumnOffset,$row+$rowoffset,$outgoingsoftware, true);

...

$range="V2:Y$row";
$objPHPExcel->getActiveSheet()->getStyle($range)->getNumberFormat()->setFormatCode('@');

The range in Excel is correct but the values always appear as describe above (4,1 instead of 4.1).  I also tried to use strval or replace characters when reading from database (varchar field) - no success.When I enter the value manually in Excel the value is displayed correctly.

 

I am really dispaired.Can you help again with some hint?

 

Thanks

Michael