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

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

Книга, сохранённая Excel 4.0 в 1992 году, начинается с байтов 09 04 06 00, тогда как каждый .xls начиная с Excel 5.0 начинается с подписи составного файла D0 CF 11 E0. Читатель, проверяющий эту подпись, отвергнет старый файл до чтения хоть одной ячейки, а читатель, пропустивший проверку и предположивший BIFF8, разберёт первую запись как нечто, чем она не является. Оба отказа растут из одного допущения: что у каждого .xls есть контейнер. У файлов Excel 2.0, 3.0 и 4.0 его нет, поэтому о том, какое поколение их записало, должен сказать сам поток записей, и записи ячеек должны декодироваться раскладкой того поколения

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

Как до-OLE книга объявляет о себе

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

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

Эти проверки важнее, чем кажутся. Два байта $09 $00 на смещении ноль — не редкость в произвольных бинарных данных, а карантинная папка неопознанных .xls обычно содержит переименованные текстовые выгрузки, обрезанные копии и время от времени файл вовсе от другой программы. Согласие трёх независимых полей — куда более сильный сигнал, чем одно

Диаграмма, как HotXLS распознаёт до-OLE файлы Excel из Delphi: современные книги в контейнере OLE2 с одной стороны, сырые потоки записей BIFF2–BIFF4 с другой, а поля записи BOF проверяются до выбора прежнего пути
Устаревшие файлы не несут обёртки хранилища, поэтому ведущая запись BOF сама доказывает поколение, длину тела и тип подпотока прежде, чем будет прочитана хоть одна ячейка

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

Записи ячеек — то место, где поколения расходятся заметнее всего. BIFF2 занимает непрерывный блок низких номеров записей, $0001–$0005, под пустые, целочисленные, числовые, текстовые и логико-ошибочные ячейки. Каждое из тех тел несёт трёхбайтовое поле атрибутов там, где поздние версии кладут двухбайтовый индекс расширенного формата, поэтому каждое значение сидит на байт глубже в записи. Запись INTEGER BIFF2 — отдельная странность: беззнаковое 16-битное значение без дробной части, тип записи, который BIFF3 выбросил в пользу RK

BIFF3 и BIFF4 отказываются от низких номеров и переиспользуют id и раскладки записей BIFF5: $0201, $0203, $0204, $0205 и $027E, каждая с двухбайтовым индексом XF. То переиспользование вызывает конкретный и легко неверно диагностируемый сбой. Запись LABEL BIFF3 или BIFF4 структурно идентична своему аналогу BIFF5: строка, столбец, индекс формата, затем двухбайтовое число символов на смещении 6. Примените ту раскладку к текстовой записи BIFF2 — и число соберётся из последнего байта атрибутов и настоящего однобайтового размера, так что текст из пяти символов заявит как минимум 1 280 символов, и читатель убежит за конец записи. В другую сторону, раскладка BIFF2 на записи BIFF3, длина прочитается из старшего байта числа, который для любой короткой строки нулевой. Ни в какую сторону ничего не выбрасывает. Книга возвращается с пустыми текстами или правдоподобным мусором, и HotXLS избегает обоих, диспетчеризуя по id записи вместе с поколением, взятым из BOF

Поле атрибутов BIFF2 прячет ещё одну ловушку. Его первый байт держит индекс формата в младших шести битах, а значение $3F означает "настоящий индекс не влезает в шесть бит, смотрите предыдущую запись IXFE". HotXLS помнит последнюю IXFE и разрешает экранирование до назначения ячейке стиля, поэтому ячейка, чей индекс формата требует больше шести бит, всё равно получает собственное форматирование

Сравнение компоновок записей ячеек BIFF2 и BIFF3/BIFF4, читаемых HotXLS в Delphi: номера записей по поколениям, различие компоновки поля LABEL, режим сбоя при неверном чтении и параллельная нумерация записей формул и строк
Тела BIFF2 несут трёхбайтовый блок атрибутов, тогда как BIFF3 и BIFF4 переиспользуют номера BIFF5 с двухбайтовым индексом XF, и смешение раскладок молча сбивает всё после записи

Текст во всех трёх поколениях — голая байтовая строка без какого-либо Unicode. Кодировка приходит из записи CODEPAGE файла, которая в этих файлах сидит внутри единственного потока листа, потому что потока уровня книги, где её можно было бы держать, нет. HotXLS декодирует тексты и строковые результаты через ту кодовую страницу и откатывается к Windows-1252, когда записи нет, что покрывает западное умолчание, которым написано большинство таких файлов. Японская или центральноевропейская книга без записи CODEPAGE декодируется как латинский текст, и единственное лекарство — знать, откуда файл

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

Детекция происходит внутри Open, поэтому вызывающий код тот же, что для любой книги. Цикл ниже обходит used range и печатает каждую ячейку дважды, когда в ней формула: переведённое выражение и значение так, как Excel показал бы его через числовой формат ячейки

uses

SysUtils, Variants, lxHandle; var Book: TXLSWorkbook; Sheet: TXLSWorksheet; Cell: IXLSRange; R, C: Integer; begin Book := TXLSWorkbook.Create; try try if Book.Open('archive\1992-inventory.xls') <> 1 then begin Writeln('not a readable workbook - quarantine for manual review'); Exit; end; except // Open выбрасывает исключение, когда файл на самом деле ZIP-пакет, // обычно .xlsx, переименованный кем-то в .xls on E: Exception do begin Writeln('rejected: ', E.Message); Exit; end; 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 Cell := Sheet.Cells[R, C]; if VarIsEmpty(Cell.Value) or VarIsNull(Cell.Value) then Continue; if Cell.HasFormula then Writeln(Format('R%dC%d %s -> %s', [R, C, VarToStr(Cell.Formula), Cell.FormattedText])) else Writeln(Format('R%dC%d %s', [R, C, Cell.FormattedText])); end; finally Book.Free; end; end;

Следите за арифметикой индексов. Границы UsedRange с нулевой базой, тогда как и коллекция листов, и доступ к ячейкам однобазовые, — несогласованность, которую API сохраняет ради совместимости с существующим кодом. Забыть поправку — и цикл аудитует не тот прямоугольник, не сообщая ничего необычного. Файлы, которые принимает этот путь, несут единственный подпоток листа, диаграммы или листа макросов, так что Sheets[1] — единственный лист, который вы найдёте; связанная книга Excel 4.0 (BOF типа $0100) лежит вне этого набора, и Open возвращает код ошибки. Дешёвые предпроверки, избавляющие от загрузки файла вообще, разобраны в статье лёгкая инспекция книги

Файлы с защитой паролем следуют тому же правилу единственного потока. Запись FILEPASS появляется сразу за BOF в потоке листа, где её подхватывает HotXLS и включает унаследованную XOR-деобфускацию этих поколений; пароль передавайте через перегрузку Open(FileName, APassWord)

Что происходит с формулами в файле BIFF2–BIFF4?

HotXLS читает обе половины устаревшей формулы: кешированный результат, который Excel сохранил при последнем расчёте, и поток токенов, переводимый в текущую модель вычислений при загрузке. Записи формул делят параллельную нумерацию через три поколения — $0006, $0206 и $0406, — но тела различаются ровно в тех местах, о которые споткнулся бы парсер BIFF8

Кешированный результат начинается с байта 7 в BIFF2 — из-за поля атрибутов — и с байта 6 в BIFF3 и BIFF4. Нечисловые результаты используют маркерное слово $FFFF в последних двух байтах того восьмибайтового слота, с дискриминатором в первом байте: 0 для строки, 1 для булева, 2 для кода ошибки, 3 для пустой. Строковый результат в слот не влезает вовсе, поэтому текст приходит следующей записью: $0007 в BIFF2 с однобайтовой длиной или $0207 в BIFF3 и BIFF4 с двухбайтовой

Анатомия кешированного результата формулы в записях FORMULA BIFF2–BIFF4, читаемых HotXLS из Delphi: значение начинается с байта 7 в BIFF2 и с байта 6 в BIFF3 и BIFF4, со словом-маркером FFFF для строк, булевых значений, ошибок и пустых
HotXLS держит значение, которое Excel вычислил последним, рядом с переведённым выражением, а нечисловые результаты приходят как слово-маркер плюс дискриминатор

Поток токенов — то место, где наивная реализация производит бессмыслицу. BIFF2 ставит перед выражением однобайтовую длину на смещении 16 и начинает токены с 17; BIFF3 и BIFF4 используют двухбайтовую длину и начинают с 18. Внутри потока токены функций несут однобайтовый индекс функции до BIFF5 и двухбайтовый начиная с BIFF5. Скормите токены BIFF4 неизменёнными транслятору BIFF8 — и каждый вызов функции съест на байт больше, так что результатом будет не слегка неверная формула, а посторонняя. Транслятор HotXLS принимает версию BIFF параметром и читает каждый токен ширинами того поколения

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

// Перекрёстная проверка: сравните сохранённые результаты со свежим пересчётом.

// Этот хелпер и его массив CachedText — ваш собственный код procedure CompareWithRecalc(Book: TXLSWorkbook; Sheet: TXLSWorksheet); var CachedText: array of array of string; Rows, Cols, R, C: Integer; begin Rows := Sheet.UsedRange.LastRow + 1; Cols := Sheet.UsedRange.LastCol + 1; SetLength(CachedText, Rows + 1, Cols + 1); for R := 1 to Rows do for C := 1 to Cols do if Sheet.Cells[R, C].HasFormula then CachedText[R, C] := VarToStr(Sheet.Cells[R, C].Value); Book.Recalculate; for R := 1 to Rows do for C := 1 to Cols do if Sheet.Cells[R, C].HasFormula and (VarToStr(Sheet.Cells[R, C].Value) <> CachedText[R, C]) then Writeln(Format('R%dC%d stored=%s now=%s formula=%s', [R, C, CachedText[R, C], VarToStr(Sheet.Cells[R, C].Value), VarToStr(Sheet.Cells[R, C].Formula)])); end;

Запускайте это на копии или до сохранения, поскольку Recalculate заменяет сохранённые результаты в памяти. Пустой отчёт — тот исход, который вам нужен; короткий обычно указывает на волатильные функции вроде NOW() или на ссылки, оставленные исходным автором висящими

Форматирование, даты и ловушка формата General

HotXLS конвертирует записи форматирования этих поколений в ту же модель книги, какую производит современный .xls. Записи FONT (с отдельной записью FONTCOLOR BIFF2, сложенной внутрь), строки FORMAT и записи XF каждого поколения, с их узорами заливки, стилями границ и устаревшими цветовыми индексами, становятся обычными шрифтами, числовыми форматами и стилями ячеек, читаемыми и редактируемыми через Font, NumberFormat и остальные свойства диапазона

Даты выигрывают напрямую. Когда числовая ячейка несёт числовой формат даты, читатель заодно применяет флаг DATEMODE книги, поэтому книга системы 1904 попадает на ту же шкалу TDateTime, что и книга системы 1900, и FormattedText показывает дату так, как её показывал Excel. Правила за сдвигом в 1462 дня разобраны в статье серийные номера дат, система 1904 и числовые форматы

Ловушка — столбец с датами под форматом General. Немало книг 1990-х заполнялись макросами или импортами, писавшими серийные номера, не утруждаясь их форматированием, и файл не подаёт никакого сигнала, что 33604 означает день января 1992-го, а не номер детали. Такие ячейки возвращаются ровно как сохранены, без поправки на систему дат. Решайте по столбцу, исходя из того, что вы знаете об источнике, и конвертируйте такие столбцы сами:

// ColumnHoldsDates — ваш собственный справочник, построенный из знания

// исходной системы; устаревший файл не ответит на этот вопрос за вас. // Serial — локальная Double; Cell и C взяты из первого листинга if ColumnHoldsDates(C) and (Pos('d', LowerCase(Cell.NumberFormat)) = 0) then begin // Серийный номер с форматом General: поправки на систему дат ещё не было Serial := Cell.Value; if Book.Date1904 then Serial := Serial + 1462; Writeln(DateToStr(TDateTime(Serial))) end else Writeln(Cell.FormattedText);

Проверка Pos('d', ...) нарочно груба и служит лишь чтобы пропустить ячейки, уже несущие токен дня в своём формате; более строгая проверка по собственному списку известных форматов дат стоит лишних строк, если архив велик

Запись мигрированной копии

Книга, открытая из файла BIFF2–BIFF4, сохраняется через писатели BIFF5/BIFF8 или XLSX, потому что нет причины производить новые файлы в формате, который текущий Excel блокирует. Book.SaveAs('migrated\1992-inventory.xls', xlExcel97) пишет копию BIFF8, несущую значения, переведённые формулы и сконвертированные стили, и каждый следующий инструмент конвейера открывает её без специальной обработки. Храните оригинал рядом: если через два года ячейка поднимет вопрос, байты 1992 года — единственная власть

Устаревшее чтение, современная запись, пересчёт и форматирование живут в одной библиотеке для Delphi и C++Builder; покрытие форматов перечислено на странице HotXLS Delphi spreadsheet component