Даты числом 44898, пропавшие нули и 8.98E+18: как не испортить данные при экспорте в Excel

Три классические жалобы после выкатки экспорта в XLSX:

• «В колонке с датами какие-то числа — 44898 вместо даты»;
• «У артикулов пропали нули: было 000123, стало 123»;
• «Номера карт превратились в 8.98E+18, последние цифры — нули».

Все три бага — не баги вашей библиотеки и не баги Excel. Это следствие того, как Excel хранит значения. Разберём, как правильно типизировать данные при генерации XLSX с помощью avadim/fast-excel-writer — быстрой потоковой библиотеки на чистом PHP.

Всё ниже проверено на версии 6.16.1.

Как XLSX хранит значения на самом деле#

Внутри XLSX у ячейки есть только примитивные типы: число, строка, булево значение, ошибка. Типа «дата» нет вообще. Дата — это обычное число: количество дней с эпохи Excel (условно 1 января 1900 года). Число 44898 — это 3 декабря 2022 года; время — дробная часть: 44898.75 — 3 декабря, 18:00. «Датой» это число становится только благодаря маске числового формата, привязанной к ячейке: YYYY-MM-DD покажет 2022-12-03, DD MMM YY — 03 Dec 22. Уберите маску — снова увидите 44898.

Отсюда главное правило работы с XLSX:

Значение и формат задаются отдельно. Значение — то, что лежит в ячейке (число, строка). Формат — то, как Excel его показывает. «Испорченные» данные почти всегда означают, что записан не тот тип значения или не задан формат.

С числами вторая засада: Excel хранит их как 64-битный float, то есть максимум 15 значащих цифр. Номер карты из 16 цифр или длинный ID в числовой ячейке будет молча округлён — последние цифры превратятся в нули, а отображение переключится на научную нотацию вида 8.98E+18. Данные теряются безвозвратно, никакой формат их не вернёт.

Как библиотека определяет типы#

FastExcelWriter отталкивается от PHP-типа значения:

use avadim\FastExcelWriter\Excel;

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

$sheet->writeCell(123456);    // int PHP  -> число в Excel
$sheet->writeCell(12.34);     // float    -> число
$sheet->writeCell('123456');  // string   -> текст (даже если внутри цифры)

Строка '123456' по умолчанию попадёт в файл как текст — библиотека не гадает за вас. Если числовые строки из БД должны становиться числами, включите опцию: Excel::create(['Report'], ['auto_convert_number' => true]).

Второй способ управлять типом — задать формат ячейки. Формат передаётся в стиле, вторым аргументом writeCell():

$sheet->writeCell(12.34, ['format' => '@money']);        // денежный формат
$sheet->writeCell(date('Y-m-d'), ['format' => '@date']); // дата

Форматы с префиксом @ — встроенные сокращения, за которыми стоят обычные маски Excel:

Маски в правой колонке — это то, что получится при локали en. Даты и деньги берутся из локали, и если её не задать явно, библиотека возьмёт системную: на машине с русской локалью @date развернётся в DD.MM.YYYY, а @money — в #,##0.00 [$₽]. Файл, собранный на ноутбуке разработчика и на сервере, окажется разным. Поэтому локаль лучше фиксировать явно:

$excel = Excel::create(['Report'], ['locale' => 'en']);
// или уже после создания книги
$excel->setLocale('en');

Вместо сокращения всегда можно написать маску напрямую: 'format' => '#,##0.00' — она от локали не зависит вообще.

Даты: число + маска, и никак иначе#

Запишете '2022-12-03' без формата — в файл уйдёт текст, который не отсортируешь как даты. Запишете time() без формата — уйдёт большое число секунд. Правильный вариант — значение плюс формат:

// строка с датой + формат: библиотека сама преобразует строку
// в «excel-число» и привяжет маску
$sheet->writeCell('2022-12-03', ['format' => '@date']);

// unix timestamp тоже понимается
$sheet->writeCell(time(), ['format' => '@datetime']);

Когда у ячейки датный формат, библиотека распознаёт значение сама: строки вида '1985-01-28 23:05:59', '23:05' разбираются через strtotime(), целые числа трактуются как unix timestamp. Объекты DateTimeInterface можно писать напрямую — они автоматически получают формат @datetime, если другой не задан.

Маска может быть любой, не только ISO. Фрагмент из демо-скрипта demo-09-datetime-formats.php:

$formats = [
    '@',           // как текст, без преобразования
    '@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]);
}

Названия месяцев в масках (MMM, MMMM) Excel локализует сам, под язык пользователя.

Запомните симптом: видите в колонке дат числа около 45000 — вы записали дату, но забыли формат. Значение верное, не хватает только маски.

Ведущие нули и длинные ID: только текст#

Артикул 000123, телефон, штрихкод EAN, номер счёта или карты — всё это не числа, даже если состоит из цифр: любое числовое представление их калечит. Решение — писать такие значения строками PHP и явно помечать формат как текстовый:

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

С форматом @text (он же @string, он же маска @) значение уходит в файл как текст независимо от содержимого. Нюанс с опцией auto_convert_number: она превращает числовые строки в числа, а для колонок с ID это ровно то, чего вы не хотите, — если опция включена глобально, обязательно задайте таким колонкам текстовый формат.

Именно здесь XLSX выигрывает у CSV. В CSV типов нет вообще: Excel при открытии сам решит, что 000123 — это число 123, а длинный номер — float, и повлиять на это со стороны генератора невозможно. В XLSX тип ячейки — часть файла, и он под вашим контролем.

Деньги и проценты#

Денежные суммы — обычные float/int плюс маска. Минимум — разделители разрядов и два знака после запятой; маска может включать символ валюты и отдельное оформление отрицательных значений (до ; — формат положительных чисел, после — отрицательных):

$sheet->writeCell(1234567.891, ['format' => '#,##0.00']); // 1 234 567,89
$sheet->writeCell(12.34, ['format' => '@money']);         // символ валюты — из локали

$style = ['format' => '[$$]#,##0.00;[RED]-[$$]#,##0.00'];
$sheet->writeCell(-500, $style);                          // -$500.00 красным

Разделители в маске (, и .) — это роли (разряды и десятичная часть), а не литералы: пользователю с русской локалью Excel сам покажет пробелы и запятую. И не форматируйте деньги в строку на стороне PHP — number_format() превратит сумму в текст, по которому не построишь сумму по колонке.

Проценты — отдельный сюрприз. Маска % умножает значение на 100 при отображении, поэтому в ячейку пишется доля, а не готовое число процентов:

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

Если записать 15 с маской 0%, получите 1500%. Экспортируете из БД поле, где уже лежит «15» — поделите на 100 перед записью.

Формат на колонку, а не на каждую ячейку#

У типичного отчёта тип определяется колонкой, поэтому задавать стиль в каждом writeCell() не нужно — форматы колонок объявляются один раз, дальше вы просто пишете строки данных.

Самый компактный способ — через writeHeader(): ключи массива становятся заголовками, значения — форматами колонок:

$sheet->writeHeader([
    'Дата'    => '@date',
    'Артикул' => '@text',
    'Сумма'   => '#,##0.00',
    'Скидка'  => '0%',
]);

foreach ($orders as $order) {
    $sheet->writeRow([
        $order['created_at'], // '2022-12-03' -> дата
        $order['sku'],        // '000123'     -> текст, нули на месте
        $order['amount'],
        $order['discount'],   // 0.15 -> 15%
    ]);
}

Если заголовки не нужны, есть прямые методы:

// одна колонка
$sheet->setColFormat('K', '@date');

// все колонки по порядку, начиная с 'A'; null — формат по умолчанию
$sheet->setColFormats([null, '@', '@', '@date', '0', '0.00', '@money']);

// формат + ширина, как в demo-09
$sheet->setColDataStyleArray([
    1 => ['format' => '@date',    'width' => 14],
    2 => ['format' => '@text',    'width' => 12],
    3 => ['format' => '#,##0.00', 'width' => 16],
]);

Формат ячейки, заданный в writeCell(), при необходимости переопределит формат колонки.

Шпаргалка#

Итого: XLSX не хранит «даты» и не знает про ведущие нули — он хранит числа, строки и маски форматов. Один раз явно решите для каждой колонки, что это — число, дата или текст, — и все три классических бага исчезнут.

composer require avadim/fast-excel-writer

Документация и примеры — в репозитории на GitHub.