php output to excel, long numbers converted

Posted on 2006-04-05
Last Modified: 2008-02-26
I have a php page that outputs to excel.
It pretty much is a dump of a form.
When I print to the screen, without the following lines

 header('Content-type: application/csv');
 header('Content-Disposition: attachment; filename="' . date("F_j_Y_g_i_a") . '.csv"');

Everything is as it should be.

When I add those lines, if I have the number 012345678901234567890, excel converts
it to 1.23457E+19 as soon as I open it.  If I try to convert it to a number, once in excel,
it drops some numbers, it goes to 12345678901234500000.00

How do I prevent excel from formatting this string.
I don't specify any sort of number at all, I just print out with tabs and "\n"


Question by:jackjohnson44
    LVL 1

    Expert Comment

    In a CSV file you have no control over formatting. Excel is going to format it in the default fashion for your standard spreadsheet template. You can change your defaults (within Excel) or you can code some VBA to start Excel, change the defaults, and then load the CVS, but that's about it.

    Author Comment

    So it is impossible to output the number 012345678901234567890 from a webpage into a csv file?

    I put single quotes around the number and it did not format it.
    I would like to remove them however.
    LVL 1

    Accepted Solution

    It's possible to output it, but Excel will not preserve the full number -- as you have seen, it only keeps 15 digits, and then chops off the rest.

    If you quote it, excel will treat it as a string and not truncate it. But as soon as you try to do math on it, the value gets truncated.

    PHP is better at math than Excel. :(


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
    Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit ( and similar technologies have enjoyed wide adoption, making it possib…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now