format of number on downloading in csv format

Question

Following code returns hours for given seconds, which is working.

<?php

$hrs = seconds_to_dezi(278280);
echo $hrs;
$filename = "test";
header("Content-type: application/vnd-ms-excel");
header("Content-Disposition: attachment; filename=$filename.xls");

function seconds_to_dezi($s) {
    $n = false;
    if ($s < 0) {
        $n = true;
        $s = -$s;
    }
    $h = round($s / 3600,2);
    $h = number_format($h,2, ',', ' ');
    $h = str_replace(".",",",$h);


    if ($n == true) {
        return ("-" . $h);
    } else {
        return ($h);
    }
}

?>

Actual output in excel:- 77,3

Expected output is:- 77,30

i.e on downloading values in csv format, the trailing zero after decimal is truncated. I want to have that zero in excel.

Found a duplicate that has some good answers: [Excel CSV - Number cell format](http://stackoverflow.com/questions/137359/excel-csv-number-cell-format) — Pekka, Apr 12 '11 at 13:24
Hi pekka, i have checked the possible answers in other question. But none of them helped me. If i force the number as string 7.75 is interpreted as jul 75 in excel 2007.So post me if there any alternate solutions — satya, Apr 12 '11 at 14:09
I don't think there are any more solutions than what is shown there. I think you'll have to set the column format manually in Excel after importing, or actually write an Excel file in PHP (which however is much, much more complex.) — Pekka, Apr 12 '11 at 14:10

score 1 · Answer 1 · answered Apr 12 '11 at 13:24

1

The function is returning 77,30. The thing is that Excel doesn't show the final 0, but it's there. Open the file with a text editor.

answered Apr 12 '11 at 13:24

Alin Purcaru

43,655
12
77
90

...as long as you don't re-save while in Excel! – Apr 12 '11 at 13:26
Thanku, yes i have checked it and it is the problem only with excel. – satya Apr 12 '11 at 13:27
This is not his question. He wants to *see* the zero – Pekka Apr 12 '11 at 13:27
@Pekka He wants to see it, but I think he was also worried it isn't generated properly. So my answer along with the duplicate you posted should fully answer his question. – Alin Purcaru Apr 12 '11 at 13:34

score 0 · Answer 2 · answered Apr 12 '11 at 13:26

0

You cannot explicitly set the number of decimals in a CSV file. If you need to show 77,30 you can use ="70,30". It will show up in the file correct, but it will be text then. So you can no longer use it as a number in excel.

answered Apr 12 '11 at 13:26

Hugo Delsing

13,803
5
45
72

Thanku, if i force to print it as "77,30". I am not able to do math operations in excel. I need to convert it manually back to number:( on converting back to number zeros are again removed. – satya Apr 12 '11 at 13:33

format of number on downloading in csv format

2 Answers2