Таблица с миллионом строк и дюжиной столбцов - совершенно обычный экспорт из задания по формированию отчётов в базе данных. Откройте её обычным способом, загрузив всю книгу в TXLSWorkbook, и процессу придётся материализовать каждую из этих двенадцати миллионов ячеек как живой объект, прежде чем выполнится первая строка бизнес-логики. Файл на диске может занимать шестьдесят мегабайт сжатого XML. Дерево объектов, в которое он разворачивается, в несколько раз больше, и всё это должно присутствовать в памяти одновременно, потому что модель предназначена для произвольного доступа. Для отчёта, который вы планируете читать сверху вниз и отбрасывать, - это огромная трата памяти на структуру, которая вам никогда не понадобилась
Существует второй путь через тот же файл. Вместо построения модели вы сканируете XML листа только в прямом направлении, по одной ячейке за раз, позволяя каждой ячейке пройти мимо после того, как вы на неё взглянули. Ничего не накапливается. Память остаётся почти постоянной независимо от того, содержит ли лист тысячу строк или десять миллионов, потому что ридер никогда не держит больше той части, которую в данный момент разбирает, плюс пару небольших таблиц поиска. Именно это делает прямой ридер HotXLS, и остальная часть этой статьи посвящена тому, почему он остаётся компактным и что он даёт взамен
Почему модель в памяти не масштабируется
Файл XLSX представляет собой ZIP-пакет из XML-частей, описанных в ECMA-376. Каждый лист - это отдельная часть xl/worksheets/sheetN.xml, и внутри неё каждая строка является элементом <row>, содержащим элементы ячеек <c>. Стандартный путь загрузки читает эту часть и строит адресуемый объект для каждой ячейки, чтобы впоследствии вы могли запросить Cells[12345, 7] и получить ответ за постоянное время. Произвольный доступ - это весь смысл модели книги, и именно он делает редактирование, вычисление формул и стилизацию удобными
Цена в том, что произвольный доступ требует одновременного присутствия всего. Нельзя индексироваться в структуру, построенную лишь частично. Поэтому пиковая память при полной загрузке является функцией от количества ячеек, и на листе с миллионами заполненных ячеек эта функция достигает значений, нежелательных для вашего сервиса, особенно если несколько таких заданий выполняются одновременно на общей машине. Когда шаблон доступа, который вам действительно нужен, является последовательным, платить за произвольный доступ - значит платить за возможность, которой вы не воспользуетесь
Однонаправленное SAX-сканирование без построения дерева
Прямой ридер открывает ZIP-пакет и обходит каждую часть листа с помощью SAX-подобного pull-парсера. SAX здесь означает, что парсер сообщает о событиях разбора по мере их обнаружения - начало элемента, текстовый фрагмент, конец элемента, - а затем движется дальше. Он не хранит за собой дерево узлов. Ридер отслеживает текущую строку и столбец из атрибутов r, собирает тип ячейки, индекс стиля, значение и текст формулы по мере поступления событий, и когда встречается закрывающий тег </c>, испускает одну ячейку и забывает её. Следующая ячейка повторно использует ту же горстку локальных переменных
Поскольку между ячейками ничего не сохраняется, объём памяти не растёт с количеством ячеек. Это и есть свойство, которое стоит сохранить. Лист из двухсот строк и лист из двадцати миллионов строк потребляют от ридера одинаковый объём резидентной памяти, а разница между ними лишь в том, сколько времени длится сканирование. Вы отказываетесь от произвольного доступа - главной возможности модели - и взамен получаете потолок памяти, который количество ячеек не может пробить
Что остаётся резидентным и почему именно эти две части
Сканирование не является полностью безсостоятельным, и исключения поучительны. Две небольшие таблицы должны находиться в памяти на протяжении всего процесса, потому что ячейки сами по себе не несут достаточно информации для интерпретации без них
Первая - таблица общих строк. В SpreadsheetML текстовая ячейка не хранит собственный текст. Она несёт t="s" и числовую нагрузку - индекс в xl/sharedStrings.xml, единый дедуплицированный список всех различных строк в книге. Это хороший компромисс по объёму для файлов, где одни и те же метки повторяются в тысячах строк, но это означает, что ридер должен загрузить эту таблицу строк заранее и держать её в памяти, потому что любая ячейка в любом листе может ссылаться на любую запись в ней. Размер таблицы определяется количеством различных строк, а не количеством ячеек, поэтому она остаётся скромной даже на огромных листах
Вторая - отображение числовых форматов из части стилей. Числовая ячейка и ячейка с датой на уровне байтов идентичны: обе представляют собой простое число, потому что дата в SpreadsheetML - это просто серийный порядковый номер дня. Единственное, что отличает их, - стиль ячейки, который через cellXfs в xl/styles.xml указывает на идентификатор числового формата. Чтобы сообщать о дате как о дате, а не как о сырой порядковой цифре, ридер загружает эту таблицу отображения стиль-формат и держит её в памяти. Всё остальное в файле - фактические данные ячеек, составляющие основной объём байтов, - проходит потоком, не сохраняясь
Каждая ячейка сообщает тип и значение
Каждая испущенная ячейка поступает как запись TXLSDirectCell. Она несёт индекс и имя листа, строку и столбец (1-based), семантический Kind, Value как Variant, текст Formula без ведущего знака равенства и сырой StyleIndex. Тип - один из xdkNumber, xdkString, xdkBoolean, xdkDate или xdkError, поэтому вы можете ветвиться по смыслу ячейки, а не переопределять его из атрибутов. Формульная ячейка сообщает тип кэшированного результата с текстом формулы рядом, поэтому вычисленный итог поступает как число, которое также сообщает, как он был получен
type
TReportScan = class
procedure OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
end;
procedure TReportScan.OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
begin
// A formula cell delivers the cached result in Value, and the text in Formula
if Cell.Formula <> '' then
Log(Format('Cell %s: Formula %s equals %s',
[Cell.Address, Cell.Formula, VarToStr(Cell.Value)]))
else
case Cell.Kind of
xdkString: Log('String: ' + Cell.Value);
xdkNumber: Log(Format('Number: %.4f', [Double(Cell.Value)]));
xdkDate: Log('Date: ' + DateTimeToStr(Cell.Value));
xdkBoolean: Log('Boolean: ' + BoolToStr(Cell.Value, True));
end;
end;Как отличить дату от числа
Вопрос с датами заслуживает более пристального взгляда, потому что именно здесь большинство наивных сканеров допускают ошибки. Для числовой ячейки не существует типа даты. Ячейка со значением 46000 может быть количеством, ценой или 17 февраля 2025 года, и файл сообщает, которое именно, только через идентификатор числового формата, доступный через стиль ячейки. ECMA-376 резервирует блок встроенных идентификаторов форматов с фиксированным значением, и идентификаторы, связанные с датами, находятся в двух диапазонах: с 14 по 22 для стандартных форматов даты и времени и с 45 по 47 для форматов истёкшего времени, таких как [h]:mm:ss. Когда DetectDates включён (что является настройкой по умолчанию), ридер разрешает стиль каждой числовой ячейки до её идентификатора формата, и ячейка, чей идентификатор попадает в эти зарезервированные диапазоны, сообщается как xdkDate, а её Value уже преобразован в Delphi TDateTime. Пользовательские форматы тоже проверяются - путём инспекции кода формата на наличие токенов даты и времени, - но зарезервированные диапазоны являются надёжной основой. Отключите DetectDates, и таблица стилей вообще не будет загружена, каждая числовая ячейка будет поступать как xdkNumber, и сканирование станет немного экономнее
Пропуск листов и досрочное прерывание
Последовательное сканирование имеет тихое преимущество перед произвольным доступом: его можно остановить. Событие OnSheet срабатывает перед открытием каждого листа и предоставляет два переключателя. Установите SkipSheet, и вся эта часть никогда не будет разбираться - так вы сканируете только нужные листы в многолистовой книге, не тратя ресурсы на чтение остальных. Установите Abort, и всё сканирование немедленно завершается. Событие OnCell несёт собственный Abort, поэтому вы можете остановиться в тот момент, когда нашли искомое - конкретную строку, сигнальное значение, конец блока заголовков, - не читая оставшиеся миллионы ячеек. При однонаправленном сканировании прерывание действительно бесплатно, потому что пропущенная работа - это работа, которая ещё не началась
procedure TReportScan.OnSheet(Sender: TObject; SheetIndex: Integer;
const SheetName: WideString; var SkipSheet: Boolean; var Abort: Boolean);
begin
// Only process the 'Transactions' sheet, ignore all others instantly
if SameText(SheetName, 'Transactions') = False then
SkipSheet := True;
end;Подсчёт ячеек без обработчика
Одно недавнее улучшение заслуживает упоминания, потому что превращает распространённый вопрос в единственный дешёвый вызов. Ридер подсчитывает каждую заполненную ячейку, которую проходит, независимо от того, подключён ли обработчик OnCell. Раньше без установленного обработчика счётчик заполненных ячеек возвращал ноль, поскольку подсчёт был побочным эффектом испускания. Теперь подсчёт независим от испускания. Это значит, что вы можете задать один вопрос - сколько заполненных ячеек на самом деле содержит эта книга - и получить ответ за цену сканирования без каких-либо обратных вызовов. ReadFile и ReadStream оба возвращают этот итог как Int64, и то же число доступно впоследствии как свойство CellCount. Возврат -1 сигнализирует о том, что файл не удалось открыть или он не является пакетом OOXML
var
Reader: TXLSDirectReader;
TotalCells: Int64;
begin
Reader := TXLSDirectReader.Create;
try
// Scan without handlers just to count the contents
TotalCells := Reader.ReadFile('massive_dump.xlsx');
Writeln('The workbook contains ', TotalCells, ' populated cells');
finally
Reader.Free;
end;
end;Для полного сканирования вы подключаете обработчик и вызываете ReadFile точно так же. Контраст с полной загрузкой - вот в чём смысл: тогда как загрузка quarterly_export.xlsx в книгу разворачивала бы каждую ячейку в резидентный объект и держала всё это, прямой ридер хранит лишь общие строки и таблицу стилей, пока двенадцать миллионов ячеек проходят через ваш OnCell по одной за раз. Арифметика, выполненная на ячейке, ничего не оставляет после себя, поэтому пиковая память определяется количеством различных строк в книге, а не количеством строк данных
Прямой ридер - правильный инструмент, когда задача состоит в однократном чтении большой книги и извлечении или суммировании данных. Если вместо этого вам нужен произвольный доступ полной модели, но вы хотите, чтобы она нормально работала с большими файлами, настройки из статьи о производительности больших книг в Delphi описывают этот путь. А когда направление обратное - генерация большого вывода вместо его потребления - руководство по потоковой записи для серверных пакетных заданий применяет ту же дисциплину постоянной памяти к записи. Все три поставляются в составе компонента HotXLS для Delphi и C++Builder, наряду с API чтения, записи, формул и форматирования, описанными в других статьях этого блога