Numbers are not displaying properly in excel report

Question

n excel report generation, numbers are displaying 7.70225E+11 but it should display like 770225145579 . Any one please help to solve this issue. My code is,

<?php

include("connection.php");

if(isset($_POST['submit']) && $_POST['submit']=="Download")
    {
    $payor_code = $_POST['payor_code'];
        $corp_code= $_POST['corp_code'];
        $pro_type = isset($_POST['product_type']);
        $submit_date = $_POST['sub_date'];

 $sql= oci_parse($conn, "select * from members ");

oci_execute($sql);
$dat = date("d/m/Y");

    $filename = "Report_$dat";

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=export.csv");
header("Pragma: no-cache");
header("Expires: 0");

$header = array(
    "ReceivedDate",
    "SubmissionDate",
    "PatientName",
    "PatientIC",
    "PrincipalName",
    "PrincipalIC",
    "ClaimNo"

       );

$fp = fopen('php://output', 'w');
fputcsv($fp, $header);

//$incr = 0;
while($row = oci_fetch_array($sql)) {

    $name = $row['RECEIVED_DATE'];
    $email = $row['SUBMISSION_DATE'];
    $mobile = $row['PATIENT_NAME'];
    $address = $row['NRIC_ID'];
    $city = $row['PRINCIPAL_NAME'];
    $state = $row['PRINCIPAL_NRIC'];
    $pincode = $row['CLAIMS_ID'];

        $fields = array ($name, $email, $mobile, $address,$city,$state,$pincode);

       fputcsv($fp, $fields);
  }
}
?>

Please solve this issue. I have to get the proper output after i download the excel report. I dont want to do any manual changes.

Solution

You have a couple of options here (though the numbers you flagged look more like unique ID's than numbers). You can potentially:

1. Manually extend the cell width to see if that is the source of the issue

2. Type ctrl 1, go to excel options, and change the type of the number in the PatientIC and PrincipalIC columns to some type that is consistent

3. (If both 1 and 2 fail) Confirm that the source data or process that created the excel spreadsheet is providing / generating the data accurately or in a consistent format

My immediate concern is that there are different formats being applied to different cells, rows and columns. For example, cell A5 and A6 both refer to the month of March, yet the date formatting is different. This may be an issue within the PatientIC and PrincipalIC columns.

Comments

[eggyal]

Beware that Excel works with floating point numbers, which may not require the requisite accuracy for your needs. You might consider casting to strings instead.

[telcoM]

Note that the output format is CSV that is generated using data from a database, not an XLS file, so PHPExcel does not apply. Also, PHPExcel is deprecated