Книга, сохранённая 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 обычно содержит переименованные текстовые выгрузки, обрезанные копии и время от времени файл вовсе от другой программы. Согласие трёх независимых полей — куда более сильный сигнал, чем одно
Три поколения, три раскладки записей
Записи ячеек — то место, где поколения расходятся заметнее всего. 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 и разрешает экранирование до назначения ячейке стиля, поэтому ячейка, чей индекс формата требует больше шести бит, всё равно получает собственное форматирование
Текст во всех трёх поколениях — голая байтовая строка без какого-либо 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 с двухбайтовой
Поток токенов — то место, где наивная реализация производит бессмыслицу. 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