阻止MS Excel使用PHP 5.3剥离前导零

I have the following sample line in a CSV file I am generating with PHP. When I cat the file this is what I see:

541787,271561,"04/01/2013 09:26:35",18801872,Many,"PINSTRIPE JACKET",18806821872,75.00,GBP,1,0078,5051916991872

However, when I open this file in MS Excel the "0078" has been changed to "78" - so MS Excel has taken it upon itself to strip the leading zeros. It has also converted the EAN13 number (in Column L) to an exponent.

In terms of the PHP code I am outputting the file to the browser with the following code, which works in principal, but I would like to know if there is a way to control the stripping of the leading zeros with PHP?

// set the headers
header('Content-Type: application/csv');
header('Content-Disposition: attachment; filename=_report_' . date('Y-m-d-His') . '.csv');
header('Pragma: no-cache');

echo $report->getCSVOutput();
exit;    

It turns out that with a small tweak (as suggested by the owner) to SimpleExcel this package can handle the stripping of leading zeros.

I am using the code below:

/**
 * get the order data in Microsoft Excel XML format (to be used when output to the browser directly)
 * @return void
 */
public function getExcelXMLOutput() {

    // instantiate new object (will automatically construct the parser & writer type as XML)
    $excel = new SimpleExcel('xml');                    

    // add some data to the writer
    $excel->writer->setData($this->orders);                                                 

    // save the file with specified name
    // and specified target (default to browser)           
    $excel->writer->saveFile('report_' . date('Y-m-d-His'));

}

The code amendment required me to change the XMLWriter class in the addRow method to:

$datatype = is_string($val) ? 'String' : (is_numeric($val) ? 'Number' : 'String');

A column value that starts with a single quote ' will be treated as Text by Excel and preserves the leading zeros.

With the CSV format, there is nothing you can do about it. Since it doesn't contain any formatting, Excel analyzes each values and tries to guess what it most likely is.

So 0078 becomes 78 (since Excel thinks it's a number) and 5051916991872 becomes 5.05192E+12 since that's the default way of representing long numbers.

If this is a serious problem, you need to use a file format where you can specify the type of the values. The easiest one is probably XML Spreadsheet 2003. You can create a template by formatting an Excel spreadsheet the way you want it, then save it as XML Spreadsheet 2003 and open it in a text editor.

When using dates the following seems to work.

Simply pad the the date with whitespace likeso:

$csvVal = '" ' . $DateTime->format('m/d/y') . ' "';