Dates as 44898, vanished zeros, and 8.98E+18: how not to ruin data on export to Excel

Three classic complaints after an XLSX export ships:

• “The date column shows numbers — 44898 instead of a date”;
• “The SKUs lost their zeros: it was 000123, now it is 123”;
• “Card numbers turned into 8.98E+18, and the last digits are zeros”.

None of the three is a bug in your library or in Excel. They follow from how Excel stores values. Let's work out how to type data correctly when generating XLSX with avadim/fast-excel-writer — a fast streaming library in plain PHP.

Everything below was verified against version 6.16.1.

How XLSX really stores values#

Inside an XLSX a cell has only primitive types: number, string, boolean, error. There is no date type at all. A date is an ordinary number: the count of days since the Excel epoch (1 January 1900, roughly). The number 44898 is 3 December 2022; the time is the fractional part — 44898.75 is 3 December, 6 p.m. That number becomes a “date” only thanks to the number format mask attached to the cell: YYYY-MM-DD shows 2022-12-03, DD MMM YY shows 03 Dec 22. Remove the mask and you are back to 44898.

Hence the main rule of working with XLSX:

The value and the format are set separately. The value is what sits in the cell (a number, a string). The format is how Excel displays it. “Corrupted” data almost always means the wrong type of value was written, or no format was set.

With numbers there is a second trap: Excel stores them as 64-bit floats, that is, 15 significant digits at most. A 16-digit card number or a long ID in a numeric cell will be silently rounded — the last digits become zeros and the display switches to scientific notation like 8.98E+18. The data is lost for good; no format brings it back.

How the library decides on types#

FastExcelWriter goes by the PHP type of the value:

use avadim\FastExcelWriter\Excel;

$excel = Excel::create(['Report']);
$sheet = $excel->sheet();

$sheet->writeCell(123456);    // PHP int   -> number in Excel
$sheet->writeCell(12.34);     // float     -> number
$sheet->writeCell('123456');  // string    -> text (even if it holds digits)

The string '123456' lands in the file as text by default — the library does not guess on your behalf. If numeric strings from the database should become numbers, turn on the option: Excel::create(['Report'], ['auto_convert_number' => true]).

The second way to control the type is to set the cell format. The format goes into the style, the second argument of writeCell():

$sheet->writeCell(12.34, ['format' => '@money']);        // currency format
$sheet->writeCell(date('Y-m-d'), ['format' => '@date']); // date

Formats prefixed with @ are built-in shorthands backed by ordinary Excel masks:

The masks in the middle column are what you get with the en locale. Dates and money come from the locale, and if you do not set it explicitly, the library takes the system one: on a machine with a German locale @date expands to DD.MM.YYYY, and @money to a mask with €. A file built on a developer's laptop and one built on the server would differ. So it is better to pin the locale:

$excel = Excel::create(['Report'], ['locale' => 'en']);
// or after the workbook is created
$excel->setLocale('en');

Instead of a shorthand you can always write the mask directly: 'format' => '#,##0.00' — that one does not depend on the locale at all.

Dates: a number plus a mask, and nothing else#

Write '2022-12-03' without a format and text goes into the file — text you cannot sort as dates. Write time() without a format and a large number of seconds goes in. The right way is a value plus a format:

// a date string + a format: the library converts the string into an
// "excel number" itself and attaches the mask
$sheet->writeCell('2022-12-03', ['format' => '@date']);

// a unix timestamp is understood too
$sheet->writeCell(time(), ['format' => '@datetime']);

When a cell has a date format, the library recognizes the value on its own: strings like '1985-01-28 23:05:59' and '23:05' go through strtotime(), and integers are treated as unix timestamps. DateTimeInterface objects can be written directly — they automatically get the @datetime format unless another one is set.

The mask can be anything, not only ISO. A fragment from the demo-09-datetime-formats.php demo script:

$formats = [
    '@',           // as text, no conversion
    '@datetime',   // 1985-01-28 23:05:59
    '@date',       // 1985-01-28
    'DD MMM YY',   // 28 Jan 85
    'H:MM',        // 23:05
];

foreach ($formats as $format) {
    $sheet->writeCell('1985-01-28 23:05:59', ['format' => $format]);
}

Month names in masks (MMM, MMMM) are localized by Excel itself, to the user's language.

Remember the symptom: numbers around 45000 in a date column mean you wrote the date but forgot the format. The value is right, only the mask is missing.

Leading zeros and long IDs: text only#

An SKU like 000123, a phone number, an EAN barcode, an account or card number — none of these are numbers, even though they consist of digits: any numeric representation mangles them. The solution is to write such values as PHP strings and mark the format as text explicitly:

$sheet->writeCell('000123', ['format' => '@text']);
$sheet->writeCell('8983190010004321757', ['format' => '@text']);

With the @text format (also known as @string, also known as the @ mask) the value goes into the file as text regardless of its content. A caveat about the auto_convert_number option: it turns numeric strings into numbers, which for ID columns is exactly what you do not want — if the option is on globally, be sure to give such columns a text format.

This is precisely where XLSX beats CSV. CSV has no types at all: on opening, Excel decides for itself that 000123 is the number 123 and a long number is a float, and the generator has no way to influence that. In XLSX the cell type is part of the file, and it is under your control.

Money and percentages#

Monetary amounts are ordinary floats or integers plus a mask. The minimum is thousands separators and two decimals; the mask may include a currency symbol and separate styling for negatives (before the ; is the format for positive numbers, after it for negative ones):

$sheet->writeCell(1234567.891, ['format' => '#,##0.00']); // 1,234,567.89
$sheet->writeCell(12.34, ['format' => '@money']);         // currency symbol comes from the locale

$style = ['format' => '[$$]#,##0.00;[RED]-[$$]#,##0.00'];
$sheet->writeCell(-500, $style);                          // -$500.00 in red

The separators in the mask (, and .) are roles — thousands and decimal — rather than literals: a user with a German locale will see Excel render them the other way round. And do not format money into a string on the PHP side — number_format() turns the amount into text you cannot sum up over a column.

Percentages hold a separate surprise. The % mask multiplies the value by 100 when displaying, so the cell must hold a fraction, not a ready-made percentage:

$sheet->writeCell(0.15, ['format' => '0%']);      // 15%
$sheet->writeCell(0.1234, ['format' => '0.00%']); // 12.34%

Write 15 with the 0% mask and you get 1500%. If you export a database field that already holds “15”, divide it by 100 before writing.

Format per column, not per cell#

In a typical report the type is defined by the column, so there is no need to pass a style into every writeCell() — column formats are declared once, and after that you simply write rows of data.

The most compact way is through writeHeader(): the array keys become headings, and the values become column formats:

$sheet->writeHeader([
    'Date'     => '@date',
    'SKU'      => '@text',
    'Amount'   => '#,##0.00',
    'Discount' => '0%',
]);

foreach ($orders as $order) {
    $sheet->writeRow([
        $order['created_at'], // '2022-12-03' -> date
        $order['sku'],        // '000123'     -> text, zeros intact
        $order['amount'],
        $order['discount'],   // 0.15 -> 15%
    ]);
}

If you do not need headings, there are direct methods:

// a single column
$sheet->setColFormat('K', '@date');

// all columns in order, starting from 'A'; null — the default format
$sheet->setColFormats([null, '@', '@', '@date', '0', '0.00', '@money']);

// format plus width, as in demo-09
$sheet->setColDataStyleArray([
    1 => ['format' => '@date',    'width' => 14],
    2 => ['format' => '@text',    'width' => 12],
    3 => ['format' => '#,##0.00', 'width' => 16],
]);

A cell format set in writeCell() overrides the column format where needed.

Cheat sheet#

In short: XLSX stores no “dates” and knows nothing about leading zeros — it stores numbers, strings, and format masks. Decide once, explicitly, what each column is — a number, a date, or text — and all three classic bugs disappear.

composer require avadim/fast-excel-writer

Documentation and examples are in the GitHub repository.