5

I've a project in Yii2 that using PHPExcel library for read an Excel file. I've a Transaction Id column that contain data in General format cell. Screenshoot of my Excel file: enter image description here

You can see above, if I input 15 characters of number and put two dots inside it. It will displayed as number.

First Ex: Input => 800.003.151476962 in cell showed as image and in Formula Bar showed as image

But if input 15 characters of number without dots inside, it will displayed as Scientific Notation/Exponential.

Second Ex: Input => 800003151476925 in cell showed as image => Scientific Notation/Exponential data format and in Formula Bar showed as image

When I'm trying to read the data in my PHP, my PHP will read the Second Ex data as Scientific Notation/Exponential format not as string/number/decimal.

How do I can get the Second Ex data as 800003151476925(no matter string or number or decimal format) not 8.00003E+14 in my PHP?

Thanks :)

NOTE: Actually I've read and try suggestion in Convert exponential to a whole number in PHP, Convert exponential number to decimal in php, Convert exponential number to decimal in php but I got nothing.

Community
  • 1
  • 1
Blackjack
  • 1,016
  • 1
  • 20
  • 51

1 Answers1

5

If you put 2 dots (decimal points) in a value, then it can only be a string; so MS Excel will format it General (the default cell formatting for MS Excel) as a string. With a single dot (decimal point), PHPExcel will treat it as a number, and set the datatype accordingly; and MS Excel will see it as a floating point number, and MS Excel General formatting rules will format it using scientific formatting if it is greater than a standard 32-bit integer size, treating it as a float.

If you want to force string formatting, then you need to do so explicitly, either by setting it as a string datatype using setCellValueExplicit() to force it to be set as a string; or set a format mask other than General such as 0

$objPHPExcel->getActiveSheet()->getStyle('A9')
    ->getNumberFormat()
    ->setFormatCode(
        '0'
    );
Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • Thanks for the knowledge :) Doesn't the your code above only can use for write an Excel file? – Blackjack Jan 23 '17 at 09:37
  • 2
    If you're reading the value, then it's too large for an integer in 32-bit PHP, and will be displayed in scientific format anyway even with 64-bit PHP if you simply echo it (nothing to do with PHPExcel, just basic PHP). use PHP's `sprintf()` or `number_format()` functions to display it as an integer – Mark Baker Jan 23 '17 at 09:40
  • Even though your suggestion did'n work for my case, at least I get new knowledge may be useful nexttime. Thanks :D I've tried the to display as string using `sprintf` but it display the **8.00003E+14** as string, not **800003151476922** as string – Blackjack Jan 23 '17 at 10:21
  • FormatCode 0 just set it as Number and format it with "0" format. It shows as a Number not a Text in cell format panel in Excel. –  Mar 29 '17 at 04:55