Дробные форматы Excel и цепные дроби в HotXLS

Отформатируйте 3.14159 дробной маской # ??/??, и Excel покажет 3 1/7, хотя 3 14/99 ближе более чем в семь раз, поэтому экспорт из Delphi, ищущий ближайшую дробь, напечатает значение, которое пользователь никогда не увидит. Причина в том, что Excel принимает только подходящие дроби разложения в цепную дробь; в HotXLS TXLSXCell.FormattedText возвращает те же 3 1/7, включая все заполняющие пробелы

Excel останавливается на подходящих дробях

Каждая приближающая дробная маска даёт Excel одну и ту же работу: найти числитель и знаменатель для части после целого, с знаменателем, ограниченным числом позиций под цифры после слэша. Excel делает это, разлагая значение в цепную дробь и оставляя только подходящие дроби — те, что получаются обрывом разложения после каждого члена. Для 0.14159 разложение начинается [0; 7, 15, 1, ...], что даёт 1/7, затем 15/106, затем 16/113. Целое 3 ничего не меняет: 3.14159 проходит через те же 22/7, 333/106 и 355/113 из любой таблицы приближений числа пи. Поскольку 106 уже переполняет двухзначный знаменатель, Excel оставляет 1/7. Более близкая 14/99 — полуподходящая дробь, одна из промежуточных, что сидят между двумя соседними подходящими. Excel их никогда не навещает. Чтобы на это наткнуться, не нужен собственный код: типы Up to one digit и Up to two digits в диалоге Format Cells — это встроенные идентификаторы форматов 12 и 13, # ?/? и # ??/??, из списка предопределённых числовых форматов ECMA-376 Part 1 §18.8.30

Числовая прямая подходящих дробей 3.14159: 22/7, 333/106 и 355/113 на приближающихся осях, с потолками знаменателя дробных масок, решающими, какую подходящую дробь Excel оставит — каждый обрыв разложения садится ближе к 3.14159, а число цифр после слэша решает, какая подходящая дробь переживёт потолок

Разрыв не ограничен похожими на пи входами. Наилучшее приближение — обычно спуск по Stern-Brocot или перебор всех допустимых знаменателей — спрашивает, какая дробь ближе всех, а цикл подходящих дробей спрашивает, какая дробь достигается разложением первой, пока знаменатель ещё влезает. На обычных входах с двумя десятичными знаками два правила расходятся достаточно часто, чтобы это имело значение, и 0.27 в коде ниже — одно из таких значений. Если ваш код пишет дроби в PDF-накладную, HTML-отчёт или CSV, который кто-то будет сверять с книгой, скопируйте туда правило Excel, даже если оно менее точное. Читатель, увидевший 3 14/99 на бумаге и 3 1/7 в Excel, не заключит, что ваш экспорт точнее. Он заключит, что сломано одно из двух. И это будет не Excel

Приближенная числовая прямая для 0.27: подходящая дробь 10/37, которую показывает Excel, против более близкой 17/63 из перебора ближайших дробей, с расходящимися и совпадающими образцами — на обычных значениях с двумя десятичными знаками цикл подходящих дробей и перебор ближайших расходятся достаточно часто, чтобы это имело значение, поэтому экспортам стоит копировать правило Excel

Сам цикл короток. Сосчитайте заполнители цифр после слэша — ?, # или 0 — и потолок составит 9, 99 или 999 для одного, двух или трёх из них. Смешанная маска вроде # ??/?? отделяет целое и разлагает только дробную часть; неправильная маска вроде ?/? разлагает всё значение целиком, поэтому 3.14159 под ?/? выходит как 22/7. Каждый шаг берёт целую часть A текущего значения B и вплетает её в следующую подходящую дробь. Цикл останавливается, как только остаток B − A падает ниже 5e-8, так что простая дробь плюс шум плавающей точки всё равно приземлится на ту же дробь. Когда шаг толкает знаменатель за потолок, Excel откатывается к предыдущей подходящей дроби, и этот откат может приземлиться на 1/1, которую смешанная маска внесёт в целое, поэтому 12.9999 под # ?/? показывает 13. Ниже цикл выписан на чистом Delphi рядом с тупым перебором ближайших дробей, чтобы вы могли увидеть, где оба разойдутся, на собственных числах

uses
  SysUtils;

type
  TFraction = record
    Num, Den: Int64;
  end;

// Правило подходящих дробей за масками Excel # ?/?, # ??/?? и # ???/???.
// X неотрицательно: дробная часть для смешанной маски, всё значение
// для неправильной маски. MaxDen равен 9, 99 или 999.
function ExcelFraction(X: Double; MaxDen: Integer): TFraction;
var
  B, R: Double;
  A, P, Q, P1, Q1, P2, Q2: Int64;
begin
  B := X;
  P2 := 0; Q2 := 1;      // подходящая дробь k-2
  P1 := 1; Q1 := 0;      // подходящая дробь k-1
  P := 0;  Q := 1;
  while Q1 < MaxDen do
  begin
    A := Trunc(B);
    P := A * P1 + P2;
    Q := A * Q1 + Q2;
    R := B - A;
    if R < 5E-8 then
      Break;               // остаток считается нулём
    B := 1 / R;
    P2 := P1; Q2 := Q1;
    P1 := P;  Q1 := Q;
  end;
  if Q > MaxDen then       // перескочили потолок: шаг назад на одну подходящую дробь
    if Q1 > MaxDen then
    begin
      P := P2; Q := Q2;
    end
    else
    begin
      P := P1; Q := Q1;
    end;
  Result.Num := P;
  Result.Den := Q;
end;

// Что возвращает подпрограмма «наилучшего приближения»: ближайшая N/D с D <= MaxDen
function NearestFraction(X: Double; MaxDen: Integer): TFraction;
var
  D: Integer;
  N: Int64;
  Err, BestErr: Double;
begin
  Result.Num := Round(X);
  Result.Den := 1;
  BestErr := Abs(X - Result.Num);
  for D := 2 to MaxDen do
  begin
    N := Round(X * D);
    Err := Abs(X - N / D);
    if Err < BestErr then  // строгое сравнение: при ничьей побеждает сокращённая форма
    begin
      BestErr := Err;
      Result.Num := N;
      Result.Den := D;
    end;
  end;
end;

procedure CompareFractionRules;
const
  Samples: array[0..3] of Double = (0.14159, 0.99, 0.27, 0.5);
var
  I: Integer;
  E, N: TFraction;
begin
  for I := Low(Samples) to High(Samples) do
  begin
    E := ExcelFraction(Samples[I], 99);
    N := NearestFraction(Samples[I], 99);
    Writeln(Format('%-8g Excel %d/%d   nearest %d/%d',
      [Samples[I], E.Num, E.Den, N.Num, N.Den]));
  end;
end;

// Ожидаемо:
// 0.14159  Excel 1/7   nearest 14/99
// 0.99     Excel 98/99   nearest 98/99
// 0.27     Excel 10/37   nearest 17/63
// 0.5      Excel 1/2   nearest 1/2

Строка 0.27 в списке потому, что и заурядный вход с двумя десятичными знаками расходится — 10/37 против 17/63, безо всякой экзотики в значении. Как часто правила расходятся, зависит от ваших данных: настоящие простые дроби вроде 0.5 или 0.375 выходят одинаково в обоих случаях, измеренные величины и вычисленные отношения — куда менее надёжно. Trunc стоит вместо Floor, потому что вход никогда не отрицателен. Excel разбирается со знаком вне дроби. Так должен и ваш код. И аргументом обязан быть тот Double, который ячейка реально хранит, — по причине, которую следующий раздел сделает конкретной

Что происходит с 0.99 в двухзначной дроби?

Excel показывает 0.99 под # ??/?? как ·98/99 (каждый · в этой статье — один символ пробела). Виновник — двоичная плавающая точка. У литерала 0.99 нет точного Double, и хранимое значение сидит на волос ниже. Поэтому 1/(1/0.99 − 1) вычисляется как примерно 98.9999999999994 вместо 99, второй член разложения становится 98, и подходящая дробь 98/99 влезает под потолок. Проделайте то же разложение в точных десятичных — получите [0; 1, 99]: та 99 толкает знаменатель к 100, цикл откатывается к 1/1, и ячейка читалась бы 1······. Здесь быть аккуратнее Excel — само по себе баг. Генератор отчётов, держащий суммы в Currency или TBcd, либо перепарсящий десятичную строку из базы и разлагающий её точной арифметикой, напечатает целую 1 там, где Excel печатает 98/99. Кормите форматтер хранимым Double. Держите каждое промежуточное в плавающей точке. Дробь остаётся и чисто отображательским делом: формулы по-прежнему видят 0.99, а HotXLS держит секции дробей на полной точности даже в книге со включённой «точностью как на экране»

Схема двух путей для 0.99 под дробной маской Excel # ??/??: хранимый Double разлагается в 98/99, тогда как точные десятичные перескакивают потолок и показывают целую 1 — хранимый Double приземляется на 98/99, тогда как точная десятичная арифметика откатывается к 1/1 — здесь быть аккуратнее Excel и есть баг

Как Excel дополняет и выравнивает дробную область

Excel раскладывает дроби так же, как колонку десятичных: числитель выровнен по правому краю в своих позициях, знаменатель — по левому, поэтому слэш попадает в одну и ту же позицию в каждой строке. Каждая неиспользованная позиция заполняется по своему заполнителю. ? оставляет пробел, 0 пишет ноль, # не пишет ничего. Вот почему 3.14159 под # ??/?? читается 3··1/7·: один пробел — разделитель из маски, один заполняет пустую позицию числителя перед 1, и один — пустую позицию знаменателя после 7. Таблица перечисляет текст, который Excel показывает для набора входов, покрывающих правила компоновки

Код формата	Значение	Excel показывает
`# ?/?`	0.5	`·1/2`
`# ?/?`	4/3	`1·1/3`
`# ??/??`	3.14159	`3··1/7·`
`# ??/??`	0.99	`·98/99`
`# ?/?`	12	`12····`
`# ?/?`	0	`0····`
`# ?/?`	12.9999	`13····`
`# #/#`	12	`12`
`0 ?/?`	0.5	`0·1/2`
`#,##0 ?/?`	1234.75	`1,234·3/4`
`?/?`	3.14159	`22/7`
`?/?`	0	`0/1`
`??/??`	12	`12/1·`

Целые числа — там, где рукописные форматтеры уплывают от Excel дальше всего. Под смешанной маской с ? в числителе Excel сохраняет цифры целого, включая ноль, и меняет разделитель, числитель, слэш и знаменатель на пробел каждый, поэтому 12 под # ?/? становится 12····, а под # ??/100 за ним следуют семь пробелов. Переключите заполнитель числителя на # — и заполнение исчезает. Нулевое целое работает наоборот: позиция # не печатает ничего для 0.5, и разделитель выживает только как пробел, тогда как 0 ?/? заставляет цифру появиться, а неправильные маски дробь вообще никогда не сбрасывают

Что делать с этим заполнением, зависит от того, куда идёт текст. Сохраняйте его для моноширинных текстовых отчётов и для ячеек PDF-таблиц, выровненных по правому краю: выстроить слэши в линию — там весь смысл. Обрезайте для CSV и JSON, где хвостовой пробел превращает 12 в строку, которая больше не разбирается как число. HTML тоже требует решения, потому что браузер схлопывает серии пробелов, если ячейка не использует white-space: pre; установите его или обрежьте, но избегайте наполовину схлопнутого заполнения, которое съезжает у одних строк и не съезжает у других

Фиксированные знаменатели вроде # ?/8

Знаменатель, записанный цифрами от 1 до 9, — фиксированное значение вместо потолка. Арифметика меняется полностью: Excel умножает значение на знаменатель, округляет до ближайшего целого числителя и оставляет дробь несокращённой. Под 0/8 0.375 показывает 3/8, а 0.64 показывает 5/8, потому что 0.64 × 8 равно 5.12. Штатный тип As sixteenths показывает 0.5 как 8/16 — именно этого ждёт мастер, читающий чертёж, и во что форматтер, сокращающий каждый результат, превратил бы в 1/2. Округлённый числитель может дотянуться до знаменателя. Тогда смешанная маска внесёт его в целое: 2.97 под # ?/8 положено читать как 3····, поскольку 0.97 × 8 = 7.76 округляется вверх до полных восьми. Выбирайте между двумя семействами по тому, что знаменатель значит для читателя. Фиксированные знаменатели подходят всему, что котируется фиксированными шагами: имперским длинам, диаметрам свёрл, старым ценам акций в восьмых. Никому, читающему размеры пиломатериалов, не нужны 3/7 дюйма. Приближающие маски уместны для отношений и измеренных величин, где никакой конкретный знаменатель не несёт смысла

Чтение дробного текста Excel в Delphi через FormattedText

В HotXLS сам цикл вам редко нужен. TXLSXCell.FormattedText отрисовывает хранимое значение через NumberFormat ячейки с помощью TXLSFormater.GetDisplayValue из lxStandard.pas, так что выбор подходящей дроби, заполнение и любой литеральный текст приходят вместе одной строкой. Процедура ниже назначает ячейкам пары значения и маски и печатает каждый результат в скобках, чтобы хвостовые пробелы оставались видимыми. Новая TXLSXWorkbook стартует без листов, поэтому код вызывает Sheets.Add, прежде чем трогать ячейку; Sheets.Items и Cells[Row, Col] отсчитываются с единицы

uses
  SysUtils, lxHandleX;

type
  TFractionCase = record
    Mask: string;
    Value: Double;
  end;

const
  Cases: array[0..8] of TFractionCase = (
    (Mask: '# ??/??';        Value: 3.14159),
    (Mask: '# ??/??';        Value: 0.99),
    (Mask: '# ?/?';          Value: 12),
    (Mask: '# ?/?';          Value: 12.9999),
    (Mask: '# #/#';          Value: 12),
    (Mask: '?/?';            Value: 3.14159),
    (Mask: '0/8';            Value: 0.64),
    (Mask: '"x"# ?/?" y"';   Value: -12),
    (Mask: '# ?/?;(# ?/?)';  Value: -1.5));

procedure ShowFractionText;
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cell: TXLSXCell;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Fractions');   // у новой книги пока нет листов
    for I := Low(Cases) to High(Cases) do
    begin
      Cell := Sheet.Cells[I + 1, 1];          // строки и столбцы начинаются с 1
      Cell.Value := Cases[I].Value;
      Cell.NumberFormat := Cases[I].Mask;
      Writeln(Format('%-16s %9g  [%s]',
        [Cases[I].Mask, Cases[I].Value, Cell.FormattedText]));
    end;
  finally
    Book.Free;
  end;
end;

// Ожидаемо:
// # ??/??            3.14159  [3  1/7 ]
// # ??/??               0.99  [ 98/99]
// # ?/?                   12  [12    ]
// # ?/?              12.9999  [13    ]
// # #/#                   12  [12]
// ?/?                3.14159  [22/7]
// 0/8                   0.64  [5/8]
// "x"# ?/?" y"           -12  [-x12     y]
// # ?/?;(# ?/?)         -1.5  [(1 1/2)]

Последние два случая показывают, что окружает дробь. Текст в кавычках и backslash-экранирования печатаются как обычные символы, и заполнение целого значения садится перед суффиксом, поэтому "x"# ?/?" y" превращает -12 в -x12·····y. Минус встаёт перед всем, включая префикс, если только явная отрицательная секция вроде (# ?/?) не выписывает знак сама; дроби следуют тем же правилам секций и знака минус, что и всякий другой числовой формат Excel. Когда значение приходит из набора данных, а не из ячейки, книгу можно пропустить: у TXLSFormater есть конструктор без параметров, и GetDisplayValue(Value, NumberFormat) — тот вызов, который FormattedText делает для числовых значений

uses
  SysUtils, lxStandard;

function FractionText(const AValue: Double; const AMask: WideString): WideString;
var
  Fmt: TXLSFormater;
begin
  Fmt := TXLSFormater.Create;
  try
    Result := Fmt.GetDisplayValue(AValue, AMask);
  finally
    Fmt.Free;
  end;
end;

// FractionText(0.5, '# ?/?')                Ожидаемо: ' 1/2'
// FractionText(1234.75, '#,##0 ?/?')        Ожидаемо: '1,234 3/4'
// Trim(FractionText(12, '# ?/?'))           Ожидаемо: '12'  (пригодно для CSV)

Всякая дробь, которую ваш код на Delphi печатает вне Excel, должна происходить из цикла подходящих дробей или напрямую из FormattedText, накормленного собственным Double ячейки. Тогда 3 1/7 на бумаге — это те же 3 1/7 в сетке, а с табличным компонентом HotXLS для Delphi вы получаете этот текст, вообще не выписывая цикл