Problem formatting a float like string as text

Topics: Developer Forum, User Forum
Apr 11, 2012 at 10:20 AM
Edited Apr 11, 2012 at 10:23 AM

Hi all!

I've a problem trying to format this string (seems a float but it is a string code) in excel5:

00.45984584

as a text.

I'm using Ubuntu 11.10 with PHPExcel 1.7.6 and LibreOffice Calc 3.4.4 OOO340m1 (Build:402) to test the script

 

<?php
require_once '../Classes/PHPExcel.php';
require_once '../Classes/PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel -> setActiveSheetIndex(0);
$sheetPHPExcel = $objPHPExcel -> getActiveSheet();
$sheetPHPExcel -> getStyle('A') -> getNumberFormat() -> setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
// PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());

$cellCoord = 'A1'; // $sheetPHPExcel -> getStyle($cellCoord) -> getNumberFormat() -> setFormatCode('@'); $sheetPHPExcel -> setCellValue($cellCoord, "000.10290"); date_default_timezone_set('Europe/Rome'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $filename = date('Y-m-d') . "_test.xls"; header("Pragma: public"); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header("Content-Disposition: attachment;filename=" . $filename); header("Content-Transfer-Encoding: binary "); $objWriter -> save('php://output'); ?>

 

 

As you can see (commented in //) I'm trying to use all possible configurations but nothing worked for me.

My final excel displays a float like 0,45984584 (here in Italy) cell (checking cell format it seems to be TEXT!!! Auch!). But this is a string code 00.45984584!

So? Can someone help me?

Coordinator
Apr 11, 2012 at 7:26 PM
Edited Apr 11, 2012 at 7:27 PM
$sheetPHPExcel -> setCellValueExplicit(
    $cellCoord, 
    "000.10290", 
    PHPExcel_Cell_DataType::TYPE_STRING
);
Apr 12, 2012 at 7:15 AM

Thank you for your answer!

I tried this solution before, but the problem is that LibreOffice Calc 3.4.4 OOO340m1 (Build:402) displays the number correctly, but when you look at the cell content the value is:

'000.10290

with a leading ' (apostrophe).

No other solutions?

 

 

Coordinator
Apr 12, 2012 at 10:44 AM

That is the solution.... try entering a string containing "000.10290" manually in MS Excel or Open/Libre Office Calc or Gnumeric. They all require you to prefix it with ' to force it to be treated as a string... and in all cases it appears correctly in the sheet, and has a ' prefixed when you look at the cell contents. This is how all those spreadsheets work.

Apr 12, 2012 at 10:54 AM
Edited Apr 12, 2012 at 10:55 AM

Using format/cell in Libre Office -> Text (code @) you can force a string containing "000.10290" to be 000.10290 without leading '

Have a look at this file

http://wildnove.com/tests/2012-04-12_test-1.xls

created with (your help)

$sheetPHPExcel -> setCellValueExplicit(
    'A1', 
    "000.10290", 
    PHPExcel_Cell_DataType::TYPE_STRING
);

and then edited (the B1 cell) with LibreOffice Calc 3.4.4 OOO340m1 (Build:402).

The two cells are different...

Thank you for your patience

Coordinator
Apr 12, 2012 at 8:42 PM

The MS Excel GUI allows you to set a cell content that could otherwise be interpreted as a number, boolean, or date in two ways if you want it to remain text: either format the cell as Text and then enter your value, or prefix the value with a single quote when you enter it.

// Add some data, we will force the datatype to string here
$objPHPExcel->getActiveSheet()
    ->setCellValueExplicit(
        'A1',
        '0123.456789',
        PHPExcel_Cell_DataType::TYPE_STRING
    )
    ->setCellValueExplicit(
        'B1',
        '0123.456789',
        PHPExcel_Cell_DataType::TYPE_STRING
    ->setCellValueExplicit(
        'C1',
        '0123.456789',
        PHPExcel_Cell_DataType::TYPE_STRING
); // Set cell B1 to use General format explicitly
$objPHPExcel->getActiveSheet()->getStyle('B1')
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL); // Set cell C1 to use Text format explicitly $objPHPExcel->getActiveSheet()->getStyle('C1') ->getNumberFormat() ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

When I open this file in MS Excel (2010), A1 is formatted "General" and has content "0123.456789", cell B1 is formatted "General" and has content "0123.456789", while cell C1 is formatted "Text" and has content "0123.456789". In all three cases, Excel has an information alert warning me that "The number in this cell is formatted as text or preceded by an apostrophe" and offering me the choice of converting to a number, ignore error, edit in formula bar, etc. All appear in the cell edit bar as "0123.456789".

If I open the file in Gnumeric (v1.10.14), A1 is formatted "General" and has content "0123.456789", cell B1 is formatted "General" and has content "0123.456789", while cell C1 is formatted "Text" and has content "0123.456789". Cells A1 and B1 appear in the cell edit bar as "'0123.456789" (with a leading ') while cell C1 appears as "0123.456789" (without a leading ').

If I open the file in Libre Office (v3.4.4), A1 is formatted "Number/General" and has content "0123.456789", cell B1 is formatted "Number/General" and has content "0123.456789", while cell C1 is formatted "Text/@" and has content "0123.456789". Cells A1 and B1 appear in the cell edit bar as "'0123.456789" (with a leading ') while cell C1 appears as "0123.456789" (without a leading ').

Earlier version of Excel display the same behaviour as Gnumeric and Libre Office, with the cell edit bar showing the leading ' if the cell is formatted General (which is the default format unless Excel itself has specifically applied a formatting based on the content you entered into the cell such as converting to a date or percentage, etc).

Apr 26, 2012 at 10:12 AM

Thank you for the exhaustive response!

Regards

Marco