Round-trip Double через XLSX без потерь в Delphi: 17 цифр

HotXLS записывает обычные числовые значения ячеек и кеши формул в XLSX с 17 значащими цифрами, а при чтении правит разбор Delphi RTL ограниченной точной проверкой decimal-to-binary64, так что Double, положенный в ячейку, возвращается бит в бит после сохранения и переоткрытия. Пятнадцати цифр — числа, которые Excel показывает на экране, — недостаточно, чтобы идентифицировать значение IEEE 754 binary64, и зазор проявляется ровно там, где живут финансы и колонки идентификаторов: целые дальше 10¹⁵

Баг, вынудивший изменение, выглядел безобидно. Формульное выражение INT(-1E15-0.1) вычисляется в -1000000000000001 — прекрасно представимый double. HotXLS вычислял его верно, кешировал, сохранял файл, а кеш на диске читался как -1E15. Excel показал бы исходное значение только после пересчёта, и любой потребитель, доверяющий кешированным значениям, — а это большинство, — молча получал число со сдвигом на единицу. Ничего не бросалось, ничего не предупреждалось, и файл проходил валидацию

Почему 15 значащих цифр не хватает для double?

Пятнадцать десятичных цифр гарантированно переживают поездку в binary64 и обратно, но обратное направление требует 17. У IEEE 754 binary64 мантисса в 53 бита, что даёт от 15 до 17 десятичных цифр в зависимости от того, где сидит значение. Любая десятичная строка не более чем с 15 значащими цифрами конвертируется в double и печатается обратно без изменений. Но double требуется до 17 цифр, прежде чем его десятичное написание однозначно его идентифицирует, и в диапазоне выше 2⁵³ соседние double отстоят на 2, а между 10¹⁵ и 2⁵³ — на 0,125–1

Delphi-FloatToStr форматирует с 15 значащими цифрами — той же точностью, что Excel использует для показа. Именно то совпадение, почему дефект так хорошо прячется: откройте файл в Excel, и ячейка выглядит в точности как в вашей Delphi-программе, потому что Excel тоже показывает лишь 15 цифр. Разница становится видимой, только когда вы сравниваете сохранённый double, вычитаете два соседних значения или скармливаете кеш чему-то, делающему арифметику. Значение -1000000000000001 имеет 16 значащих цифр, поэтому округление до 15 даёт -1000000000000000, и хвостовая единица теряется насовсем

ECMA-376 Part 1 §18.3.1.96 определяет элемент <v>, несущий значение ячейки как текст, и ничто в схеме не предписывает, сколько цифр писатель обязан выпустить для числовой ячейки. Точность — целиком ответственность писателя, почему два продюсера могут писать валидный XLSX и всё же расходиться в том, какое число в ячейке

Запись 17 значащих цифр из Delphi

HotXLS теперь использует один инвариант для числовых ячеек: FloatToStrF с ffGeneral, точностью 17 и инвариантными настройками формата. Ветвь дат писателя ячеек уже использовала ровно ту проекцию, поэтому обычные числа просто присоединились. Снаружи в API ничего не меняется: вы присваиваете Double или формулу, сохраняете, и токен, попадающий в <v>, достаточно точен, чтобы воспроизвести double

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Ledger');
    Sheet.Cells[1, 1].Value := 9007199254740991.0;   // 2^53 - 1
    Sheet.Cells[2, 1].Value := -1000000000000001.0;
    Sheet.Cells[3, 1].Formula := 'INT(-1E15-0.1)';
    if Book.Recalculate <> lxOk then
      raise Exception.Create('Recalculation failed');
    // Значения и кеш формул пишутся с 17 значащими цифрами
    if Book.SaveAs('ledger.xlsx') <> lxOk then
      raise Exception.Create('Save failed');
  finally
    Book.Free;
  end;
end;

Семнадцать цифр — потолок, а не правило набивки. ffGeneral срезает хвостовые нули, поэтому 0.5 по-прежнему пишется как 0.5, малые целые остаются короткими; лишние цифры получают только значения, которым они по-настоящему нужны. Цена — несколько байтов на тех ячейках, что раньше обрезались, — самый дешёвый фикс корректности, который вам доведётся выпустить

Почему запись 17 цифр всё ещё теряет значения при чтении?

Запись достаточного числа цифр помогает, только если читатель конвертирует их в ближайший double, а RTL-шный Val делает так не всегда. Во время верификации RTL на тестовой машине прочитал и 9007199254740990.7, и голое целое 9007199254740991 в соседнее неверное значение binary64. Обе строки должны попасть на 2⁵³-1; одна — 17-значное написание, которое умеет выпускать HotXLS, вторая — то, как Excel пишет то же значение. Писатель, починенный в изоляции, выпустил бы файл, который сам HotXLS не смог бы правильно прочитать

Поэтому HotXLS держит Val привратником и правит его ответ. После того как существующая числовая грамматика приняла не-датный токен, точный парсер строит десятичное как big-integer дробь и сравнивает её с двоичными средними точками по обе стороны кандидата RTL. Если десятичное ниже нижней средней точки, кандидат шагает на один ulp вниз; выше верхней — вверх; при точном равенстве побеждает чётная мантисса — правило IEEE 754 round-to-nearest-even. Кандидату дозволено максимум восемь соседних поправок, чего с запасом хватает, потому что Val ошибается на единицу, а не на милю

Коррекция нарочно ограничена. Токены длиннее 48 символов, с более чем 17 значащими цифрами или с величиной экспоненты выше 324 её пропускают и сохраняют результат легаси-парсера, и любой внутренний сбой откатывается к тому же. Большие целые используют 48 лимбов по 32 бита — достаточно для самого широкого числителя и знаменателя в тех границах. Одна деталь переносимости здесь важна: лимбы объявлены явно как UInt32. LongWord выглядит очевидным выбором, но это 8 байт на Delphi-цели Linux64, и тип лимба, молча удваивающийся в ширине, ломает каждый перенос и сдвиг в арифметике

Проверка round-trip бит в бит

Сравнивать double через = для этого теста нормально, ведь цель — точное равенство, но сравнение битовых паттернов делает отчёт о сбое полезнее: дрейф на один ulp проявляется разницей 1 в сыром 64-битном значении, а не двумя числами, печатающимися одинаково

function SameBits(A, B: Double): Boolean;
var
  BitsA, BitsB: UInt64;
begin
  Move(A, BitsA, SizeOf(BitsA));
  Move(B, BitsB, SizeOf(BitsB));
  Result := BitsA = BitsB;
end;

procedure CheckReopen(const FileName: string);
const
  Expected: array[1..3] of Double =
    (9007199254740991.0, -1000000000000001.0, -1000000000000001.0);
var
  Book: TXLSXWorkbook;
  Row: Integer;
  Stored: Double;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> lxOk then
      raise Exception.Create('Open failed');
    for Row := 1 to 3 do
    begin
      Stored := Book.Sheets[1].Cells[Row, 1].Value;
      if not SameBits(Stored, Expected[Row]) then
        Writeln('row ', Row, ' drifted: ', FloatToStrF(Stored, ffGeneral, 17, 0));
    end;
  finally
    Book.Free;
  end;
end;

Строка 3 — самая интересная. Она читает кешированный результат INT(-1E15-0.1) без пересчёта, так что доказывает без потерь именно кеша, а не способности формульного движка пересчитать значение. Если вы зависите от кешированных результатов в конвейере, отдельное руководство о чтении кешированных значений формул без пересчёта разбирает состояния кеша и то, почему отсутствующий кеш никогда не выдумывается

Какие ещё части XLSX несут числовые кеши?

Фикс писателя ячеек закрыл один путь из нескольких, и каждую оставшуюся точку числовой записи пришлось проверять отдельно. Первый релиз покрыл значения листов и кеши формул, включая кеши значений вывода Pivot. Кеши источников Pivot и кеши Chart всё ещё шли через 15-значную воронку и были закрыты двумя последующими релизами, а не по предположению

• Кеш источника Pivot: значения sharedItems и оба писателя cacheRecords, инлайн и потоковый, теперь используют тот же 17-значный инвариант, а путь чтения pivot применяет ту же точную коррекцию средних точек. Описательные атрибуты вроде refreshedDate сохраняют исходное написание
• Кеши Chart: единственная точка записи, строящая кешированные значения точек, переключилась на 17 цифр, а шесть точек чтения — значения и категории линий тренда, валидация error bar и два пути рендеринга — идут через один хелпер точного разбора
• Всё прочее числовое, вроде ширин колонок и границ осей, по-прежнему идёт через защитный форматтер, сбрасывающий не-конечные и субнормальные значения в 0, потому что сериализованная часть никогда не должна содержать непарсируемый float

Самой важной проверкой совместимости была проверка с самим Excel. Excel пишет 2⁵³-1 как целое 9007199254740991; HotXLS может написать эквивалентное 17-значное написание 9007199254740990.7. Оба токена парсятся в один double на обеих сторонах, поэтому файл, созданный в Excel, переоткрытый и пересохранённый HotXLS, а затем снова открытый в Excel, сохраняет те же значения в кеше Pivot. Взаимное признание, а не идентичное написание — вот свойство, которое стоит проверять

Пределы и краевые случаи, о которых стоит знать

Без потерь здесь значит, что значение binary64 сохранено, а не что всякое значение представимо. Не-конечные результаты по-прежнему сохраняются как ошибки #NUM!, субнормали — как ноль, ровно как раньше. Показ — тоже отдельная забота: Excel продолжит показывать 15 значащих цифр на экране, поэтому пользователь, вбивающий 9007199254740993 в Excel, всё равно получит другое сохранённое значение, и никакой писатель этого не починит. Если ваша книга использует Set precision as displayed, значения нарочно округляются до их числового формата — противоположность тому, о чём эта статья; правила того режима разобраны в статье о том, как HotXLS применяет округление Excel precision as displayed

Точный читатель также не заменяет Val; он правит только значения, которые Val уже принял. Токен, отвергнутый RTL, по-прежнему отвергается, а токен вне границ 48 символов, 17 цифр и экспоненты 324 оставляет то, что дал легаси-парсер. Для файлов, написанных HotXLS или Excel, это никогда не важно, поскольку ни один не выпускает таких токенов для значений ячеек, но рукотворная XML-часть с 40 значащими цифрами читается с точностью RTL, а не с точной коррекцией

FAQ

Меняет ли HotXLS число, которое Excel показывает для ячейки?

Нет. HotXLS меняет только то, сколько цифр уходит в сохраняемый токен <v>. Excel по-прежнему показывает 15 значащих цифр, поэтому ячейки выглядят так же; разница в том, что лежащий в основе double теперь совпадает с тем, что присвоил ваш Delphi-код

Почему бы не писать 16 цифр вместо 17?

Шестнадцати цифр хватает не для каждого значения binary64. Некоторым double нужно 17 значащих цифр, прежде чем их десятичное написание станет уникальным, поэтому 16-значный писатель в редких случаях всё ещё отображал бы два разных double в один текст — ровно тот класс молчаливого дрейфа, который изменение убирает

Нужно ли перегенерировать файлы, записанные до фикса?

Только если они содержат значения, требовавшие больше 15 цифр. Те токены уже потеряли последние цифры на диске, и более точное чтение не вернёт того, что никогда не было записано. Пересохранение из исходных данных, либо пересчёт формул с повторным сохранением, даёт точный файл

17-значный числовой вывод, точная коррекция чтения binary64 и согласованная обработка кешей Pivot и Chart — часть HotXLS Delphi Spreadsheet Component для Delphi, C++Builder и Lazarus, читающего и пишущего XLSX без Excel и без установленной OLE-автоматизации