PHP：始终在EXCEL中显示小数位，甚至是零

Question

I am generating an excel sheet using Content-Type: application/vnd.ms-excel. Code looks like this

    $filename = "Stack_Rank_ReportV2_(".$fromdate."_".$todate.").xls";
        header("Pragma: no-cache");
        header("Content-Type: application/vnd.ms-excel");    
        header("Content-Disposition: attachment; filename=$filename");      
        header("Expires: 0");

        $head_row  = "Emp. ID"."\t";
        $head_row .= "Employee Name"."
";
        echo $head_row;

        $user_row = $users[$userid]['empid']."\t";
        $user_row .= $users[$userid]['name']."
";
        echo $user_row;

The problem is that the decimal points are ignored by excel if it is zero say 1.000 becomes 1 but shows properly if it is 1.001. I'm looking for something like setFormatCode() that is there in PHPEXCEL. Is there a way to acheive this?

Answer 1

You can do it, but it will have to be a string instead of a float.

$nums = [1, 10.00, 10.001, 1.000, 0];

function f($num){
    if(!strpos($num, '.')) $num = number_format($num, 2, '.', '');
    return $num;
}

foreach ($nums as $num) echo f($num)."
";

Output:

1.00
10.00
10.001
1.00
0.00

Sandbox

You can't see it but the types are diffrent

string(4) "1.00"
string(5) "10.00"
float(10.001)
string(4) "1.00"
string(4) "0.00"

Not really a "big deal" for PHP but something to be aware of. PHP won't display a float with the zeros. Once it converts to string the 0's are removed, which is why strpos above works when the number version has the .. I should add when you do echo or otherwise output a float with 0's PHP will convert it to a string and remove them, so you have to convert it yourself using number format etc...