MYSQL to phpEXCEL is converting numbers to scientific notation

Question

The issue is that the phonenumber is shown in scientific notation:

include_once 'PHPExcel.php';
$sheet = new PHPExcel();

$servername = 
$username = 
$password = 
$dbname = 
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT firstname, lastname, phonenumber FROM people where answer='true'";   
$result = $conn->query($sql);

$x=0;
if ($result->num_rows > 0) {
    // output data of each row
    $activeSheet=$sheet->getActiveSheet();
    while($row = $result->fetch_assoc()) {
        $activeSheet->setCellValue('A'. $x,$row["firstname"]);
        $activeSheet->setCellValue('B'. $x,$row["lastname"]);
        $activeSheet->setCellValue('C'. $x,$row["phonenumber"]);        
        $activeSheet->setCellValue('D'. $x,"True");
        $x++;
    }
}
$conn->close();         
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="report.xlsx"');
header('Cache-Control: max-age=0');

$objWriter=PHPExcel_IOFactory::createWriter($sheet,'Excel2007');
$objWriter->save('php://output'); 
exit;

The question has already been [answered here](http://stackoverflow.com/questions/3054312/correct-format-for-strings-numbers-beginning-with-zero) and is detailed in the [PHPExcel documentation here](https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/07-Accessing-Cells.md#setting-a-number-with-leading-zeroes) — Mark Baker, Dec 15 '15 at 10:11

score 0 · Answer 1 · answered Dec 15 '15 at 09:51

0

You should store phone-numbers in a text-field; not a numeric field; that will lead to all sort of problems. You're seeing one of them; the number is too big to show as number, and is probably already corrupt in your mysql database. This means you'll never be able to retrieve the phone-number.

Again; update your database and make phonenumber a VARCHAR instead of an INT

answered Dec 15 '15 at 09:51

Sjon

4,989
6
28
46

Despite your answer is right, I can't agree with this: `and is probably already corrupt in your mysql database`. Supossing that the OP is storing the telephone number in a `INT` field, the maximun value for this is 2147483647 while the telephone number he/she is using has 9 digits. Of course, the answer is right and he/she should use VARCHAR. Indeed, the "scientific notation" is because the representation of the number by Excel, try a CSV file and the result is the same. – ojovirtual Dec 15 '15 at 09:59
That's why I said *probably*; indicating the problem is not on the retrieving, but the storing side – Sjon Dec 15 '15 at 10:00
I tried this and change my field to varchar(255) an problem is remained. Excel convert it to scientific format and last digits will be converted to 0. – Ahmad Dec 08 '19 at 12:09

MYSQL to phpEXCEL is converting numbers to scientific notation

1 Answers1