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

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

Чтение файлов Excel 2.0-4.0 в Delphi с помощью HotXLS

HotXLS Component

HotXLS открывает книги, записанные Excel 2.0, 3.0 и 4.0, прямо из Delphi и C++Builder. Эти файлы предшествуют составному документу-контейнеру OLE, который использует каждый последующий .xls, поэтому они представляют собой сырые потоки записей BIFF вообще без обёртки-хранилища, и читатель, построенный под BIFF8, не найдёт внутри них ни одной узнаваемой структуры. Открытие такого файла использует тот же вызов Open, что и любая другая книга; читатель определяет формат и переключает пути обработки

Эти файлы всё ещё встречаются, и это единственная причина, по которой всё это важно. Инженерные архивы, хранение государственных записей, лабораторные данные с приборов, чьё управляющее ПО было написано в 1993 году, и давно работающие бухгалтерские системы — все они оставили после себя книги BIFF2 и BIFF4. Современный Excel напрямую отказывается открывать многие из них, убрав устаревшие конвертеры по соображениям безопасности, что оставляет набор данных, который никто не может прочитать никаким доступным инструментом

Чем отличается книга, созданная до эры OLE?

Каждый .xls, начиная с Excel 5.0, — это составной файл OLE2, небольшая файловая система внутри файла, где книга живёт в потоке с именем Workbook или Book. Разбор такого файла начинается с разбора этого контейнера, как описано в статье двоичный формат составного файла на Pascal

У BIFF2-BIFF4 нет контейнера. Файл начинается сразу с записи BOF, и номер записи этого BOF кодирует поколение: $0009 для BIFF2, $0209 для BIFF3 и $0409 для BIFF4. HotXLS проверяет длину тела BOF, которая составляет от четырёх до шести байт, и тип подпотока — $0010 для листа, $0020 для диаграммы и $0040 для листа макросов, — прежде чем переходить к сырому пути обработки. Именно эта проверка не даёт повреждённому или неверно опознанному файлу интерпретироваться как очень старая книга

Три поколения, три раскладки записей

Записи ячеек — это то место, где поколения расходятся заметнее всего. BIFF2 занимает непрерывный блок низких номеров записей, $0001 - $0005 для пустых, целочисленных, числовых, текстовых и логических/ошибочных ячеек, и каждое тело несёт трёхбайтовое поле атрибутов, куда более поздние версии помещают расширенный индекс формата. BIFF3 и BIFF4 отказываются от этого и повторно используют номера и раскладки записей BIFF5 — $0201, $0203, $0204 и $0205 — с двухбайтовым индексом XF

Эта последняя деталь вызывает конкретный и легко неверно диагностируемый сбой. Запись LABEL в BIFF3 или BIFF4 структурно идентична своему аналогу в BIFF5: строка и столбец, за которыми следуют индекс формата и затем число символов. Напишите читателя, предполагающего раскладку BIFF2, — и он прочитает на два байта меньше, чем нужно, а затем выйдет за конец записи и неверно интерпретирует всё, что за ней следует. Симптом — не исключение; это книга, которая читается с правдоподобным мусором внутри

Записи формул занимают параллельную нумерацию во всех трёх поколениях: $0006, $0206 и $0406. Когда формула даёт строковый результат, эта строка приходит в отдельной следующей записи, $0007 или $0207, а форма BIFF2 использует однобайтовый префикс длины вместо двухбайтового, применяемого позже

Почему формулы возвращаются как значения, а не как текст

HotXLS читает кешированный результат формулы в этих файлах и не пытается восстановить выражение формулы. Это осознанная граница, а не пробел, который предстоит когда-нибудь заполнить

Разобранное выражение в BIFF2-BIFF4 использует кодировку токенов, отличающуюся от BIFF5 и более поздних версий способами, выходящими за рамки косметики: длины токенов имеют иные префиксы, ссылочные токены имеют иные размеры, а таблицы индексов функций были перенумерованы между поколениями. Прогон этих байтов через транслятор выражений BIFF8 не даёт неверную формулу — он даёт случайную. Чтение кешированного значения даёт число или строку, которые Excel вычислил последний раз, а именно это и требуется миграции архива

Кешированное значение находится по зависящему от поколения смещению внутри записи: байт 7 для BIFF2 и байт 6 для BIFF3 и BIFF4. Особые значения — строки, логические значения, ошибки и пустые ячейки — кодируются маркерным словом $FFFF с дискриминатором, тем же соглашением, что сохранили и более поздние поколения BIFF

Открытие файла

Вызывающий код ничем не примечателен, и в этом суть. Определение происходит внутри Open:

uses
  lxHandle;

var
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
  R, C: Integer;
  V: Variant;
begin
  Book := TXLSWorkbook.Create;
  try
    if Book.Open('archive\1993-inventory.xls') <> 1 then
    begin
      Writeln('unreadable - quarantine for manual review');
      Exit;
    end;
    Sheet := Book.Sheets[1];          // Sheets[] с отсчётом от 1
    for R := Sheet.UsedRange.FirstRow + 1 to Sheet.UsedRange.LastRow + 1 do
      for C := Sheet.UsedRange.FirstCol + 1 to Sheet.UsedRange.LastCol + 1 do
      begin
        V := Sheet.Cells[R, C].Value;
        if not VarIsEmpty(V) then
          Writeln(Format('R%dC%d = %s', [R, C, VarToStr(V)]));
      end;
  finally
    Book.Free;
  end;
end;

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

Чего вы не получаете и что с этим делать

Форматирование не интерпретируется. HotXLS не разбирает записи XF и FONT этих поколений, поэтому шрифты, цвета, границы и числовые форматы недоступны, а ячейки, которые Excel когда-то отображал как даты, возвращаются как их сырые серийные номера

Последнее нужно обрабатывать в собственном коде, а не в читателе, и причина честная: числовые форматы в BIFF2-BIFF4 недостаточно надёжны, чтобы управлять автоматическим решением о дате. Столбец пятизначных чисел может быть датами, а может быть номерами деталей. Конвертируйте осознанно, используя систему дат книги, чьи правила описаны в статье серийные номера дат, система 1904 и числовые форматы:

// Решайте по столбцу, никогда по отдельному значению: пятизначное
// число может быть датой или номером детали, а устаревший формат
// вам этого не скажет
if ColumnHoldsDates(C) then
begin
  // Две системы дат разнесены на 1462 дня, поэтому один и тот же
  // серийный номер обозначает две даты с разницей в четыре года.
  // Читайте систему из книги, а не предполагайте её
  if Book.Date1904 then
    Writeln(DateToStr(SerialToDate1904(V)))
  else
    Writeln(DateToStr(SerialToDate1900(V)));
end
else
  Writeln(VarToStr(V));

Две структурные детали дополняют картину. Защита паролем и записи кодовой страницы появляются внутри единственного потока листа, а не в потоке уровня книги, поскольку потока уровня книги просто не существует, поэтому их нужно распознавать в контексте листа. И файл BIFF2-BIFF4 содержит ровно один подпоток листа; многолистовые книги не существовали, пока формат не обзавёлся своим контейнером

Поэтому прагматичный путь миграции — двухшаговый: прочитайте устаревший файл ради его значений, затем запишите современную книгу, несущую эти значения с форматированием, которое вы применяете сами. Устаревшее чтение, современная запись и всё, что между ними, работают в одной библиотеке для Delphi и C++Builder, описанной на странице компонента HotXLS для электронных таблиц Delphi