Экспорт на 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. Форматы с пустыми или чисто-литеральными секциями следуют собственным правилам и могут спрятать число целиком
Ещё один предел сидит вне любой библиотеки. Стандарт добавляет, что «оптимальный способ показа зависит от доступной ширины ячейки», и в колонке шириной по умолчанию 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 |
|---|---|---|---|
| 123456789012 | 1.23457E+11 | 123456789012 | 123456789012 |
| 1234567890123456 | 1.23457E+15 | 1234567890123460 | 1.23456789012346E15 |
| 1/3 | 0.333333333 | 0.333333333333333 | 0.333333333333333 |
| 1E20 | 1E+20 | 1E+20 | 1E20 |
| 0.000001 | 0.000001 | 0.000001 | 1E-6 |
| 0.00000123456789 | 1.23457E-06 | 0.00000123456789 | 1.23456789E-6 |
Последняя колонка объясняет дрейф. FloatToStr использует общий формат Delphi с пятнадцатью цифрами, пишет экспоненты без плюса и нулевого заполнения и переключается в экспоненциальную запись на собственных порогах: ниже 0.00001 или как только целой части нужно больше пятнадцати цифр. Разделитель дробной части она берёт из FormatSettings хоста. Для лог-файлов это прекрасная функция. Ни одного правила Excel она не воспроизводит, и один помощник, обслуживающий и отображение, и текст формул, ошибётся в каждой строке, где те правила расходятся, — в четырёх из шести строк выше
Какой текст брать экспорту или ключу поиска?
Выбирайте правило вопросом, где строку будут сравнивать. Текст для людей — 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 заказов — метки, написанные цифрами; их никто не суммирует, а ведущий ноль принадлежит метке. Присвоенные строками, они добираются до сетки и каждой формулы одними и теми же шестнадцатью символами, и ключ поиска по ним не может дрейфовать. 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