LБлог losLab по разработке программного обеспеченияИнженерные заметки для разработчиков PDF, Delphi и программного обеспечения для работы с документами.

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

Чтение и запись бинарных книг XLSB в Delphi

HotXLS Delphi Component

HotXLS читает и пишет файлы Excel Binary Workbook (XLSB) из Delphi через тот же фасад TXLSXWorkbook, что и для XLSX: Open распознаёт бинарную книгу по содержимому пакета, а SaveAs или SaveAsChecked с xlsxExcelBinaryWorkbook пишет её. Бэкенд покрывает валидированное подмножество и отвергает всё, что не может представить, прежде чем тронуть приёмник

Последняя оговорка — то место, которое стоит читать медленно. XLSB выглядит простым вариантом XLSX, и в терминах упаковки так и есть. Беда живёт в деталях, которые два формата кодируют по-разному, и в том, что библиотека молча предполагает, перекидывая мост из одного в другое. Самый поучительный баг этого бэкенда вообще не был битой записью. Это был пропавший шрифт по умолчанию и колонка дат, превратившаяся в Excel в ####, хотя значение, код формата и ширина колонки были все правильные

Что такое файл XLSB и чем он отличается от XLSX?

Файл XLSB — это OPC-пакет с точно той же структурой частей, что у книги XLSX, но части книги, листа, стилей и общих строк хранят бинарные записи вместо XML. Формат специфицирован в [MS-XLSB], а кодирование записей часто называют BIFF12. Вы по-прежнему получаете [Content_Types].xml, части-отношения и ZIP-контейнер; меняется то, что xl/workbook.bin заменяет xl/workbook.xml, xl/worksheets/sheet1.bin заменяет XML листа и так далее

Каждая запись BIFF12 начинается с компактного заголовка переменной длины. Тип записи занимает один или два байта — семь бит на байт со старшим битом как флагом продолжения, так что тип записи влезает в 14 бит. Размер полезной нагрузки следует в одном-четырёх байтах по той же семибитной схеме. Строки — это 32-битный счётчик символов, за которым идут кодовые единицы UTF-16. По сравнению с BIFF8 это куда более чистая раскладка: никакого разбиения 8224-байтовыми CONTINUE, никаких флагов «сжатая или широкая строка» посреди записи. По сравнению с XLSX он целиком пропускает токенизацию XML — потому большие XLSB-файлы обычно меньше на диске и быстрее открываются Excel

Одна упаковочная деталь сбивает код распознавания. Excel регистрирует часть книги XLSB с content type application/vnd.ms-excel.sheet.binary.macroEnabled.main, даже для книг без макросов. HotXLS сначала проверяет эту регистрацию в [Content_Types].xml и лишь при отсутствии content type откатывается к расширению части .bin, причём любой из путей всё равно должен пройти строгий валидатор записей. Общие правила об именах частей, отношениях и content types — те же, что разобраны в том, почему Excel чинит валидный XLSX, и правилах OPC-пакета за этим

Когда выбирать XLSB вместо XLSX?

Выбирайте XLSB, когда его требует нижестоящая система или когда вы обмениваетесь крупными, насыщенными значениями книгами с пользователями Excel, которым важно время открытия; иначе XLSX остаётся более безопасным дефолтом. XLSX — стандарт ISO (ISO/IEC 29500) с широкой поддержкой инструментов: он инспектируется ZIP-утилитой и текстовым редактором, и его читает каждый табличный продукт. XLSB — формат Microsoft с опубликованной спецификацией, но более узкой экосистемой

На практике запросы приходят с двух сторон. Некоторые репортинговые конвейеры и архивы годы назад стандартизировались на .xlsb и отвергают всё прочее. Другие команды генерируют книги с сотнями тысяч числовых ячеек и обнаружили, что бинарную версию пользователи открывают заметно быстрее. Обе причины законны. «Звучит эффективнее» — нет: бинарный бэкенд HotXLS держит весь пакет в памяти, поэтому это не инструмент для серверных экспортов с ограниченной памятью. Для них правильный путь — стриминговый XLSX-писатель

Открытие и сохранение XLSB через TXLSXWorkbook

TXLSXWorkbook.Open не нуждается в подсказке формата: тот же вызов грузит XLSX, XLSM или XLSB, а после успешной бинарной загрузки TXLSXWorkbook.SourceFormat сообщает xlsxExcelBinaryWorkbook. Запись явная. SaveAs(FileName) без формата всегда производит книгу Open XML, даже когда имя файла кончается на .xlsb, поэтому бинарный вывод всегда называет формат

uses
  SysUtils, lxHandleX;

procedure ConvertToXlsb(const Source, Target: string);
var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    // Open распознаёт XLSX, XLSM или XLSB по самому пакету
    if Book.Open(Source) <> 1 then
      raise Exception.Create('Input is unsupported or invalid');

    // Одно расширение никогда не выбирает бинарный вывод
    if Book.SaveAsChecked(Target, xlsxExcelBinaryWorkbook) <> 1 then
      raise Exception.Create('XLSB output is unsupported or failed');
  finally
    Book.Free;
  end;
end;

Вывод в поток идёт через SaveAs(Stream, xlsxExcelBinaryWorkbook) или потоковую перегрузку SaveAsChecked. Внутри оба маршрута уходят в приватные методы SaveAsXlsbStream и SaveAsXlsbFile; напрямую вы их не вызываете. Файловый вывод готовится в соседнем временном файле и фиксируется одной финальной заменой. Потоковый приёмник обязан быть читаемым и позиционируемым: писатель снимает слепок его байтов, размера и позиции и восстанавливает их, если финальное копирование провалится. TXLSXWorkbook.LastSaveUsedStreaming после бинарного сохранения сообщает False — точно: весь пакет существует в памяти до того, как первый байт достигнет вашего приёмника

Почему валидированное подмножество вместо полной точности?

HotXLS не держит вторую объектную модель для XLSB. При открытии XlsbToOpenXml конвертирует бинарный пакет в Open XML-пакет в памяти, и его грузит обычный XML-парсер; при сохранении XML-писатель строит пакет, а OpenXmlToXlsb кодирует его бинарными записями. Поэтому каждая возможность обязана пережить два перевода, а возможность, которую мост не может перевести точно, отвергается, а не аппроксимируется

Поддерживаемый набор в HotXLS 2.384.113 достаточно широк для большинства книг с данными: типизированные значения, общие и встроенные строки, распространённые шрифты, заливки, границы и числовые форматы, высоты строк и ширины колонок, обычные формулы ячеек с кэшированными результатами, именованные диапазоны, объединённые ячейки, гиперссылки, закреплённые и разделённые области, диапазоны AutoFilter с простыми пользовательскими критериями и обычные таблицы листов. За пределами — общие и массивные формулы, future- и пользовательские функции, ссылки на внешние книги, диаграммы и рисунки, PivotTable и slicers, VBA, условное форматирование, проверка данных и нестандартные настройки расчёта вроде ручного пересчёта или итераций

Отвержение обязательно, а не рекомендательно. TXLSXWorkbook.GetConversionReport(xlsConversionExcelBinaryWorkbook) перечисляет неподдерживаемые возможности до сохранения, и эти записи валят сохранение даже с AAllowLossy = True или неконтролируемым SaveAs. Это намеренно. Тихо выбросить PivotTable из бинарной копии — такой вид потери, который никто не заметит, пока книга не окажется перед клиентом

uses
  Classes, lxHandleX, lxDiagnostics;

function CanSaveAsXlsb(Book: TXLSXWorkbook; Log: TStrings): Boolean;
var
  Report: TXLSConversionReport;
  I: Integer;
begin
  Report := Book.GetConversionReport(xlsConversionExcelBinaryWorkbook);
  try
    Result := True;
    for I := 0 to Report.Count - 1 do
      if Report[I].Fidelity = xlsConversionUnsupported then
      begin
        Result := False;
        Log.Add(Report[I].SheetName + ' ' + Report[I].Location + ': ' +
          Report[I].Feature + ' - ' + Report[I].Message);
      end;
  finally
    Report.Free;   // отчётом владеет вызывающий
  end;
end;

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

Почему пропали шрифт по умолчанию и формат 0.00?

Шрифт по умолчанию и встроенные числовые форматы пропали, потому что XML-ридер стилей сделал два допущения, верных для файлов, которые HotXLS пишет сам, но не для книг, написанных Excel. Мост сохранил оба ресурса идеально; потеря случилась слоем выше, в публичном пути Open. Этот зазор — главный урок бэкенда: корректный мост форматов не гарантирует, что публичный цикл «открыть — сохранить» пройдёт без потерь

Первое допущение касалось fonts[0]. Сохраняя XLSX, HotXLS пишет неявный шрифт по умолчанию в слот ноль, а его XML-ридер этот слот пропускает, чтобы видимая пользователю коллекция Fonts начиналась с индекса ноль. Созданный Excel XLSB несёт в этом слоте собственный явный дефолт, и пропуск выбрасывал этот выбор. Второе допущение касалось числовых форматов. Ридер отображал в NumberFormats только пользовательские значения numFmtId (164 и выше); встроенные id оставались нулями. Стилизованная пустая ячейка со встроенным форматом 2 — это 0.00 — возвращалась как General

Фикс целиком живёт в пути бинарного импорта. После того как весь пакет проходит preflight, открытие XLSB выставляет приватный стиль-режим только для синхронного разбора XML. В этом режиме ридер грузит каждый шрифт, включая слот ноль, сдвигает каждый fontId на единицу, конвертирует каждый поддерживаемый встроенный id в явный код формата в модели и по-прежнему использует исходный numFmtId для детекции дат, так что встроенный формат даты сохраняет свою семантику даты. Блок finally всегда снимает режим, а последующее обычное открытие XLSX или ODS через Open чистит и удержанное состояние дефолтного шрифта

Как fonts[0] влияет на ширины колонок Excel?

Excel вычисляет ширины колонок из шрифта книги по умолчанию в fonts[0], а не из шрифтов, применённых к ячейкам колонки. width колонки хранится в символьных единицах максимальной ширины цифры дефолтного шрифта, поэтому смена fonts[0] меняет пиксельную ширину каждой колонки, включая колонки, чьи ячейки все используют другой шрифт

Это дало самый сбивающий с толку симптом всего расследования. Перезаписанная книга имела идентичные значения ячеек, идентичные коды форматов и идентичные атрибуты width, шрифт ячейки и стиль Normal указывали на сохранённый шрифт, а Excel всё равно рисовал колонку дат как ####. Записанный fonts[0] откатился к Calibri 11, максимальная ширина цифры сжалась, и даты перестали помещаться. Теперь HotXLS сохраняет дефолтный шрифт источника и пишет его обратно как fonts[0], откатываясь к Calibri 11 только для книг, у которых бинарного источника никогда не было. Полная арифметика ширин — в ширинах колонок Excel и max digit width (MDW) в Delphi

Зависящие от локали встроенные форматы отвергаются, а не угадываются

Встроенный числовой формат id без явного кода формата принимается, только когда его значение фиксировано; зависящие от локали id отвергаются на preflight пакета. HotXLS отображает id 0–22, 37–40 и 45–49 в известные коды вроде 0.00, 0%, mm:ss и @. Остальные id ниже 164 принадлежат форматам, чей рендеринг зависит от региональных настроек установки Excel, и импорт отказывает им с сообщением «A locale-dependent number format requires an explicit format code»

Выдумывать код для этих id хуже, чем падать. Угаданный восточноазиатский паттерн даты выглядит правдоподобно, проходит круг без ошибок и показывает не то половине ваших пользователей. Важен и тайминг: проверка идёт в preflight всего пакета, до очистки существующей книги, так что отвергнутый файл оставляет прежние модель, имя файла и исходный формат нетронутыми. О том, как встроенные id дат взаимодействуют с системами дат 1900 и 1904, рассказывает статья о серийных датах Excel и numFmt

procedure LoadBinaryWorkbook(Book: TXLSXWorkbook; const FileName: string);
var
  I: Integer;
begin
  // Бинарному входу нужна полная жадная загрузка; выборочные режимы отвергают сразу
  Book.MetadataOnly := False;
  Book.SelectedSheets.Clear;

  if Book.Open(FileName) <> 1 then
  begin
    for I := 0 to Book.Diagnostics.Count - 1 do
      Writeln(Book.Diagnostics[I].Message);
    Exit;   // прежде загруженная книга остаётся целой
  end;

  // Выводу формул нужен кэш, если только сохранение не пересчитывает
  Book.RecalcBeforeSave := xrmAlways;
end;

Практические правила для XLSB в производственном коде

На бинарный бэкенд можно полагаться, если обращаться с его отвержениями как с данными, а не как с ошибками, которые надо давить. Из описанного выше поведения следует несколько правил

  • Называйте формат при каждом бинарном сохранении; одно расширение .xlsb по-прежнему даёт XLSX
  • Прогоняйте GetConversionReport(xlsConversionExcelBinaryWorkbook) перед сохранением редактированных пользователями книг и показывайте пользователю неподдерживаемые возможности
  • Для бинарного входа держите MetadataOnly выключенным, а SelectedSheets пустым: ленивой загрузке пришлось бы удерживать конвертированный архив
  • Дайте формулам кэшированный результат или пересчитайте перед сохранением: отсутствующий кэш отвергается под xrmManual и под чистым xrmIfDirty
  • Закладывайте память на полный конвертированный пакет плюс бинарный вывод; когда память должна оставаться ограниченной, используйте стриминговый XLSX

Тестируя, сравнивайте с Excel через публичный API, а не на уровне моста. Регрессии дефолтного шрифта и 0.00 обе прошли мостовые тесты, и обе проявились немедленно в книге от Excel, открытой и пересохранённой через Open и SaveAsChecked

FAQ

Распознаёт ли HotXLS файлы XLSB автоматически?

Да. TXLSXWorkbook.Open инспектирует content type, зарегистрированный для части книги, и валидирует бинарные записи, поэтому тот же вызов грузит XLSX и XLSB, а SourceFormat сообщает, какой был прочитан

Почему SaveAs с именем .xlsb всё равно пишет XLSX?

HotXLS никогда не выводит формат сохранения из расширения. Передайте xlsxExcelBinaryWorkbook в SaveAs или SaveAsChecked, чтобы записать бинарную книгу

Что происходит, когда книга использует возможности вне подмножества XLSB?

Сохранение падает прежде, чем изменится приёмник, даже при разрешённом сохранении с потерями. GetConversionReport заранее перечисляет блокирующие возможности, а Diagnostics объясняет провал задним числом

Поддержка XLSB, отчёт о конверсии и остальной API книг поставляются в HotXLS — нативном табличном компоненте для Delphi и C++Builder