Export to Excel is dropping leading zero

Question

I found this code on web for export tables from Mysql to Excel. The problem is that data exported to excel is missing the leading zero. So instead of number 090888 i get 90888. Except that code work fine, so i would like to change this particular code.

If anyone can help with this I would really appreciate it.

The code:

header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=$filename.xls");
header("Pragma: no-cache");
header("Expires: 0");

/*******Start of Formatting for Excel*******/

//define separator (defines columns in excel & tabs in word)

$sep = "\t"; //tabbed character

//start of printing column names as names of MySQL fields

for ($i = 0; $i < mysql_num_fields($result); $i++) {

echo mysql_field_name($result,$i) . "\t";

}

print("\n");

//end of printing column names

//start while loop to get data

    while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";

        for($j=0; $j<mysql_num_fields($result);$j++)

        {

            if(!isset($row[$j]))

                $schema_insert .= "NULL".$sep;

            elseif ($row[$j] != "")

                $schema_insert .= "$row[$j]".$sep;

            else

                $schema_insert .= "".$sep;

        }

        $schema_insert = str_replace($sep."$", "", $schema_insert);

 $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);

        $schema_insert .= "\t";

        print(trim($schema_insert));

        print "\n";
    }
?>

Does the spreadsheet already exist? If so, ensure that the columns' formats are set to Text for those columns where the zero is dropping. Currently they are numeric columns (and so the 0 at the front is superfluous) — mcalex, Oct 22 '12 at 09:10

Anirudh Ramanathan · Answer 1 · 2013-08-01T19:43:57.670

0

When you print it into the excel sheet, precede the numbers by a single quote. That will force them to be treated as text, and not as numeric data.

For example, print '090888 instead of just 090888, which will force it to treat it as a string (but will not display the ' in excel.

Another way would be to adjust formatting, but I don't think that is possible in the above case.

edited Aug 01 '13 at 19:43

answered Oct 22 '12 at 09:13

Anirudh Ramanathan

46,179
22
132
191

Adjusting formatting simply isn't possible with a tab-separated-value file – Mark Baker Oct 22 '12 at 09:17
If `$row[$j]` contains your number, preceded by 0, `$schema_insert .= "'$row[$j]".$sep;`. Note the single quote before the number itself. – Anirudh Ramanathan Oct 22 '12 at 09:31
Yes, but then i get like '090888 – user1758545 Oct 22 '12 at 09:44
@user1758545 - But isn't that what you said you wanted? "So instead of number 090888 i get 90888"? If not, please explain exactly what you do want – Mark Baker Oct 22 '12 at 19:42

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

You can't add the '0' for integer when creating XLS. because the format of the column is integer only in the Excel you have created. so we need to change the format of the column. for this you can use this package PHPEXCEL for creating Excel.

and also you can refer this post:

https://stackoverflow.com/a/3269351/1638375

edited May 23 '17 at 11:45

Community

1
1

answered Oct 22 '12 at 09:50

Prasath Albert

1,447
10
20

score 0 · Answer 3 · edited Feb 05 '13 at 04:17

0

After using PHPEXCEL, use like this:

$objPHPExcel->getActiveSheet()
    ->setCellValueExplicit("$row[$j]", "value" ,PHPExcel_Cell_DataType::TYPE_STRING);

edited Feb 05 '13 at 04:17

andr

15,970
10
45
59

answered Feb 05 '13 at 03:52

Yosep Tito

737
6
7

score 0 · Answer 4 · answered May 09 '23 at 12:51

Despite the age of this thread, I would like to add something to it. None of the solutions mentioned above worked for me, but this code will:

"="" . $order['phone'] . """;

You can use it like this:

$orders = array(
    array(
        'id' => 1,
        'name' => 'John Doe',
        'phone' => '0123456789'
    ),
    array(
        'id' => 2,
        'name' => 'Jane Smith',
        'phone' => '0987654321'
    )
);

// Loop through each order in the array
foreach ($orders as $order) {
    // Output the phone number with leading zeros intact
    echo "=\"" . $order['phone'] . "\"\n";
}

This code adds an equals sign (=) followed by two double quotes (") before and after the phone number. This tells Excel to treat the value as text and keep the leading zeros intact.

When you export the data to Excel, the phone number field will be displayed with the leading zeros preserved.

Export to Excel is dropping leading zero

4 Answers4