6

I have an excel export from mysql, which should display data from a column holding a value of 17 digits (a unique, numerical, reference). When using PHPExcel, everything but this reference displays fine.

I used : $objPHPExcel->getActiveSheet()->getStyle('F'.$xlsRow)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER); which comes close, but...

When comparing values in the Excel sheet and the original data in the database, I see the last 2 digits of the reference are set to 00.

eg:

`20130829071002200` instead of `20130829071002210`

I tried setting other (numerical and non-numerical) formats in the setFormatCode and (PHPExcel_Style_NumberFormat:: ) but I either get ###### when using STRING format, the scientific notation (2,01308E+16) when using nothing or TEXT format or the output mentioned here-above (the last 2 digits being zeroes)

No idea what I'm doing wrong...

Any help would be appreciated.

Bram Kortleven
  • 61
  • 1
  • 2

1 Answers1

9

I'd suggest that's because your number is too large to be stored as a 32-bit integer value, so it's implicitly being set as float (with all corresponding precision issues). If you're working with numeric values this large, then you really need to treat them as strings. When you store the value in the PHPExcel cell, use setCellValueExplicit() with a type of string.

$objPHPExcel->getActiveSheet()
    ->setCellValueExplicit(
        'A1', 
        '20130829071002210', 
        PHPExcel_Cell_DataType::TYPE_STRING
    );
Mark Baker
  • 209,507
  • 32
  • 346
  • 385