Tjyylli

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.

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.

You can set the value as string, while adding it to excel. This will prevent the integer number to get converted into scientific notation.
Check this thread PHP Excel - Correct format. To understand why this is happening, check this url Why does Excel treat long numeric strings as scientific notation

Try this code it's works for me :

$objPHPExcel

   ->getActiveSheet()

   ->getCellByColumnAndRow($col, $row)

   ->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);

And Good luck !

In laravel I have changed format of colum to this

[

    'D' => '+#'

]