Техническая статья

Почему Excel показывает 1.23457E+11 для 12-значного числа

Экспорт на Delphi хранит номер счёта 123456789012 как число; широкая колонка Excel показывает 1.23457E+11, тогда как =A1&"" возвращает все двенадцать цифр. Excel превращает числа в текст по двум правилам — одиннадцать символов для отображения в General и пятнадцать значащих цифр внутри формул, — поэтому экспорт HotXLS берёт первое из FormattedText, а второе строит формульным движком

Сколько символов разрешает формат General?

Одиннадцать, не считая знака минус. ECMA-376 Part 1 §18.8.30 требует, чтобы приложение «пыталось показать всё число вплоть до 11 цифр (включая десятичную точку)», а правило для плавающей точки формулирует точнее: «максимальная общая длина отображения ячейки — 11, без учёта знака минус». Дальше стандарт набрасывает четыре условия переключения в экспоненциальную форму и останавливается. Округление оставлено открытым. Как и порог переключения для малых чисел. Поведение ниже — то, что Excel показывает в широкой колонке, и то, что HotXLS воспроизводит в отображаемом тексте ячейки и в TEXT(A1, "General"):

  • Число, целая часть которого влезает в одиннадцать символов, остаётся в фиксированной записи и жертвует десятичными: 12345.6789012345 показывается как 12345.6789, а 12345678901.5 округляется до 12345678902
  • Двенадцатизначная целая часть не влезает, поэтому 123456789012 превращается в 1.23457E+11 — мантисса округлена, чтобы оставить место экспоненте
  • Число ниже 1 держит фиксированную запись, пока та показывает не меньше значащих цифр, чем шестизначная научная форма: 0.00012345678 отображается как 0.000123457, но 0.0000123456789 становится 1.23457E-05
  • Экспонента всегда несёт знак и минимум две цифры, поэтому 1020 отображается как 1E+20

Знак сидит вне бюджета. -12345678901 отображается со всеми одиннадцатью цифрами за своим минусом — двенадцать символов в сумме, — тогда как -123456789012 становится -1.23457E+11. Присвоенный Variant-тип результат не меняет. Excel держит каждое числовое значение ячейки как double, и HotXLS прогоняет значения Integer, Int64, Currency и Double через то же правило одиннадцати символов, поэтому Cells[1, 1].Value := Int64(123456789012) тоже отображается как 1.23457E+11. Правило доносится и в пользовательские форматы: литеральный текст в секции добавляется вокруг результата General, поэтому General" kg" показывает то же число как 1.23457E+11 kg. Форматы с пустыми или чисто-литеральными секциями следуют собственным правилам и могут спрятать число целиком

Схема решений правила одиннадцати символов отображения формата General в Excel для HotXLS в Delphi: позиционная запись, переключение в научную и знак минус вне бюджета
широкая колонка даёт формату General одиннадцать символов на цифры и десятичную точку, а знак минус ждёт вне бюджета

Ещё один предел сидит вне любой библиотеки. Стандарт добавляет, что «оптимальный способ показа зависит от доступной ширины ячейки», и в колонке шириной по умолчанию Excel сжимает дальше — до 1.2E+11 или ряда знаков #, в зависимости от метрик шрифта машины, показывающей лист. Сервер, генерирующий файлы, не имеет колонки на экране, поэтому HotXLS оставляет тот последний шаг тому, кто рисует ячейку. Расширьте колонку, прежде чем сравнивать. Иначе вы меряете широтный fallback Excel против правила General, и они разойдутся на любом значении длиннее колонки

Текст формулы хранит пятнадцать значащих цифр

Как только число становится текстом внутри формулы — оператор &, CONCAT, TEXTJOIN, LEN, LEFT — за дело берётся другое правило. ECMA-376 §18.17 определяет грамматику формул, но не формат число-в-текст для этого шага, поэтому единственная справка — вывод самого Excel. Excel округляет до пятнадцати значащих цифр и пишет их простой позиционной записью, пока та занимает не больше двадцати символов без знака. Отсюда =1/3&"" возвращает 0.333333333333333 — пятнадцать троек. =1E+19&"" возвращает 10000000000000000000, а =1E-18&"" возвращает 0.000000000000000001. За двадцатью символами текст переключается в научную запись с тем же знаковым двухзначным показателем, что использует сетка: 1E+20, 1.23456789012345E-05. Разделитель — всегда точка. HotXLS вычисляет формулы, не заглядывая в региональные настройки машины, где крутится ваша программа, поэтому ключ поиска, собранный через &, выходит одинаковым на английском и немецком сервере

У текста формулы есть один угол, где сам Excel зависит от машины. CHAR и CODE прогоняют числа выше 127 через ANSI-кодовую страницу компьютера, где стоит Excel, поэтому =CHAR(128) — знак евро на западноевропейской установке Windows и другой символ под прочими кодовыми страницами. Сервис на Delphi не может знать, какую кодовую страницу использует итоговый читатель. HotXLS вычисляет эти коды как Windows-1252, западноевропейскую кодовую страницу, на любой машине, поэтому =CHAR(150) даёт среднее тире на любом сервере. Если ваши книги предназначены для другой кодовой страницы, считайте такие символы в Delphi и сохраняйте их текстом

FloatToStr не следует ни одному из правил

ЗначениеОтображение General / TEXTТекст формулы (&"")Delphi FloatToStr
1234567890121.23457E+11123456789012123456789012
12345678901234561.23457E+1512345678901234601.23456789012346E15
1/30.3333333330.3333333333333330.333333333333333
1E201E+201E+201E20
0.0000010.0000010.0000011E-6
0.000001234567891.23457E-060.000001234567891.23456789E-6

Последняя колонка объясняет дрейф. FloatToStr использует общий формат Delphi с пятнадцатью цифрами, пишет экспоненты без плюса и нулевого заполнения и переключается в экспоненциальную запись на собственных порогах: ниже 0.00001 или как только целой части нужно больше пятнадцати цифр. Разделитель дробной части она берёт из FormatSettings хоста. Для лог-файлов это прекрасная функция. Ни одного правила Excel она не воспроизводит, и один помощник, обслуживающий и отображение, и текст формул, ошибётся в каждой строке, где те правила расходятся, — в четырёх из шести строк выше

Одно хранимое double, отображённое в три строки в HotXLS для Delphi: показ сетки Excel, текст формулы с пятнадцатью значащими цифрами и результат Delphi FloatToStr
показ сетки, текст формулы и FloatToStr применяют каждое своё правило, поэтому выбирайте по месту, где строку будут сравнивать

Какой текст брать экспорту или ключу поиска?

Выбирайте правило вопросом, где строку будут сравнивать. Текст для людей — PDF-отчёт, HTML-таблица, CSV, который кто-то откроет рядом с книгой, — должен совпадать с сеткой, поэтому читайте отображаемый текст ячейки. Текст, используемый как данные, следует правилу формулы: склеенный ключ, значение для сравнения с тем, что произвела формула Excel, строка поиска. CSV, который загрузится обратно в Excel или базу, тоже считается данными. Запись 1.23457E+11 в такой файл выбрасывает пять цифр номера счёта насовсем: импортёр парсит то, что прочёл, и не восстановит исходное. Первый пример печатает все три строки рядом. Сэмплы — Double, но значение Int64 или Currency отображается так же:

uses
  SysUtils, Variants, lxHandleX;

procedure CompareNumberTexts;
const
  Samples: array[0..5] of Double =
    (123456789012.0, 1234567890123456.0, 1 / 3, 1E20, 0.000001,
     0.00000123456789);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Numbers');
    for I := 0 to High(Samples) do
    begin
      Sheet.Cells[1, 1].Value := Samples[I];       // формат General по умолчанию
      Writeln(Format('%-22s %-22s %s', [
        string(Sheet.Cells[1, 1].FormattedText),  // что показывает сетка
        VarToStr(Sheet.Calculate('A1&""')),         // что видит формула
        FloatToStr(Samples[I])]));                // собственное мнение RTL
    end;
  finally
    Book.Free;
  end;
end;

Каждая напечатанная строка должна совпасть с соответствующей строкой таблицы. Двигаются только разделители. На немецком хосте колонка отображения и колонка FloatToStr переключаются на десятичную запятую, как сетка Excel на той машине, а колонка формул держит точку. Второй пример вкладывает выбор в экспортёр, пишущий отчётный текст так, как его показывает Excel, и ключевые колонки так, как их строят формулы Excel:

uses
  SysUtils, Classes, Variants, lxHandleX, lxCalc;

// Отчётный текст, как его показывает Excel, плюс ключевая колонка, независимая от локали
procedure ExportInvoiceLines(Sheet: TXLSXWorksheet; FirstRow, LastRow: Integer;
  Output: TStrings);
var
  Row: Integer;
  Shown, Key: string;
begin
  for Row := FirstRow to LastRow do
  begin
    Shown := Sheet.Cells[Row, 3].FormattedText;           // сумма, как отображается
    Key := VarToStr(Sheet.EvaluateFormulaAt(Row, 6,
      Format('A%d&"-"&C%d', [Row, Row])).Value);       // ключ из номера счёта и суммы
    Output.Add(Format('%s;%s', [Shown, Key]));
  end;
end;

EvaluateFormulaAt вычисляет формулу так, будто она сидит в заданной ячейке, не сохраняя её, поэтому ключ выходит по тому же правилу, которое Excel применяет к ключевой колонке внутри книги. Если сумма несёт денежный или тысячный формат, FormattedText применяет его, и правило General управляет лишь ячейками без формата. Возврат округлённого показанного значения в вычисление — другой переключатель в Excel, precision as displayed, и он переписывает само хранимое double, так что держите его подальше от экспорта, которому нужен только текст

Что случается с ID длиннее пятнадцати цифр?

Он теряет хвост в тот момент, когда превращается в текст. Double вмещает каждое целое до 253, около 9.007 × 1015, поэтому шестнадцатизначный номер клиента 1234567890123456 выживает нетронутым в ячейке и в файле, где семнадцати значащих цифр хватает для XLSX round trip double без потерь. С текстом другая история. Правило General показывает 1.23457E+15, а правило формул округляет до пятнадцати значащих цифр и добивает нулём, поэтому =A1&"" возвращает 1234567890123460 — уже другого клиента. Excel также хранит лишь пятнадцать значащих цифр числа, вбитого в ячейку руками, поэтому пользователь, перепечатающий значение, приземлится на 1234567890123450. Один клиент в итоге получает три числа: 1234567890123456 в ячейке, 1234567890123460 в формульном ключе и 1234567890123450 после перепечатки

Анатомия шестнадцатизначного ID в HotXLS для Delphi: хранимое значение ячейки, пятнадцатизначный текст формулы и перепечатанный ввод дают три разных числа
идентификаторы — это метки, написанные цифрами, поэтому храните их текстом, и ни одно правило не сможет округлить последнюю цифру

Храните идентификаторы текстом с самого начала. Номера счетов, номера карт и ID заказов — метки, написанные цифрами; их никто не суммирует, а ведущий ноль принадлежит метке. Присвоенные строками, они добираются до сетки и каждой формулы одними и теми же шестнадцатью символами, и ключ поиска по ним не может дрейфовать. Excel помечает такие ячейки маленьким зелёным треугольником «число сохранено как текст» — честная цена за ключ, сохраняющий последнюю цифру:

// Идентификаторы заходят текстом; суммы остаются числовыми
procedure WriteCustomerRow(Sheet: TXLSXWorksheet; Row: Integer;
  const CustomerNo: string; Balance: Currency);
begin
  Sheet.Cells[Row, 1].Value := WideString(CustomerNo); // '1234567890123456' остаётся целиком
  Sheet.Cells[Row, 2].Value := Balance;                // показывается по правилу General
end;

Прежде чем трогать экспортёр, откройте одну выпущенную им книгу в Excel, расширьте колонку с номером счёта и положите рядом =A1&"". Если ваша нижестоящая система сравнивает с левой строкой, просите у Excel-компонента HotXLS для Delphi FormattedText; если с правой — стройте текст через EvaluateFormulaAt