Excel – Numbers are not displaying properly in excel report

microsoft excelPHPsql

enter image description here

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.

Best Answer

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

enter image description here

  1. (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.

Related Question