Format Error by opening File in Excel 2010 generated with ExcelWriter2007

Topics: User Forum
Jan 2, 2014 at 12:02 PM
Hi,

I developed a little export function by using PHPExcel latest version 1.7.9
The problem is that I have in a column data values which looks like
12345.10
12345.B
Generating the excel table by using SetCellValue() setting the format by getStyle()->getNumberFormat()->setFormatCode() seems to convert the dot to comma, so when opening the generated file the values are looking that way:
12345,10
12345,B
I used the IOFactory.php and also the PHPExcel_Writer_Excel2007 to instanciate a writer for excel 2007 but open it in Excel 2010.
Is this a possible failure or do I something wrong?

Many thanks
Jan 2, 2014 at 3:34 PM
I do not think either PHPExcel making change, but rather Excel.
Two solutions: set these values to a string (by setCellValueExplicit) or achieve with Excel a whole little workbook with a value formatted as you want, watch how Excel notes this format internally (*) and use it with PHPExcel.
(*) You can determine this by directly reading the xml files or by loading this file with PHPExcel and then asking to show you the format of the affected cell.
Jan 2, 2014 at 4:07 PM
Edited Jan 2, 2014 at 4:10 PM
Hi, thank you for answers.
By reading the file generated with excel the Format type of the cell is "General". When I'm Using setCellValueExplicit(), I can't choose TYPE_GENERAL, so it has to be TYPE_STRING I think?!
But by using this variation, I get an error by opening the PHPExcel file in Excel 2010, that sounds like that:
Excel detected unreadable content in 'file.xlsx' ...
and I can't open my file.

By using
getStyle(...)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
instead, I have the same problem as described above.

What can I do wrong?!
For better understanding, here the method, which fills the cells by using setCellValueExplicit:
    public function process() {

        $iRowNumber = 2;
        $oSheet = $this->Excel->getActiveSheet();

        while ( $dsRow = $this->Statement->fetch(PDO::FETCH_OBJ) ) {

            $oSheet->setCellValueExplicit( 'A' . $iRowNumber, $dsRow->Reference, PHPExcel_Cell_DataType::TYPE_STRING);
            $oSheet->setCellValueExplicit( 'B' . $iRowNumber, $dsRow->EAN, PHPExcel_Cell_DataType::TYPE_NUMERIC);
            $oSheet->setCellValueExplicit( 'C' . $iRowNumber, $dsRow->Condition, PHPExcel_Cell_DataType::TYPE_STRING);
            $oSheet->setCellValueExplicit( 'D' . $iRowNumber, $dsRow->Stock, PHPExcel_Cell_DataType::TYPE_STRING);
            $oSheet->setCellValueExplicit( 'E' . $iRowNumber, $dsRow->Price, PHPExcel_Cell_DataType::TYPE_STRING);
            $oSheet->setCellValueExplicit( 'F' . $iRowNumber, $dsRow->DeliveryCode, PHPExcel_Cell_DataType::TYPE_STRING);
            $oSheet->setCellValueExplicit( 'G' . $iRowNumber, $dsRow->OfferDescription, PHPExcel_Cell_DataType::TYPE_STRING);

            $iRowNumber++;

        }
        
        return $this;

    }
btw.: same problem by using TYPE_INLINE...
I don't see any mistake.
Jan 3, 2014 at 6:25 AM
Ok, it seems to be an error occuring by opening in excel.
The generated file has dots in it and when opening in excel 2007 or 2010, the dots are converted to comma.

No big help for my problem, but thats not an error by the PHPExcel class.

Sorry and thanks.
Coordinator
Jan 3, 2014 at 8:09 AM
Check your MS Excel locale settings, it's likely that they're set to a "continental" format, which shows a decimal point as a comma
Jan 3, 2014 at 11:59 AM
'General' is a number format, the String type is a data type.
However, for setCellValueExplicit, it is the String type to which I thought. And in this case, there is no number to indicate format: Excel displays the data literally. And it will not seek to change the display depending on the locale.
I don't see any obvious errors in your code, but something bothers Excel. It 'just' to identify the problem. For example by generating the problem field with a number reduced records to analyze the result.

If Excel transforms the points by commas (by applying a locale, probably), is that the relevant values are considered as numbers by Excel, which certainly generates errors if this is not the case.