Задание формирования отчётов работает нормально год. Оно строит книгу, заполняет лист тем, что возвращает запрос, и сохраняет. Затем клиент с пятилетней историей запрашивает полный экспорт, количество строк пересекает миллион, и процесс завершается ошибкой нехватки памяти задолго до того, как файл достигает диска. В коде не было ничего неправильного. Он держал всю книгу в оперативной памяти, чтобы сериализовать её в конце, и потребляемая им память росла в ногу с количеством строк, которые требовалось записать
Исправление - не более мощная машина. Это иная модель записи. Потоковый прямой писатель в HotXLS выводит пакет OOXML инкрементально по мере поступления строк, поэтому используемая им память не зависит от того, сколько строк вы записываете. Это аналог ридера на стороне записи: как ридер обходит огромный лист без построения дерева ячеек, писатель создаёт его тоже без построения дерева ячеек
Почему обычный путь сохранения растёт вместе с данными
Обычный путь TXLSXWorkbook сначала строит полную объектную модель. Каждая ячейка со своим значением, типом и ссылкой на стиль живёт как объект в памяти до тех пор, пока вы не вызовете сохранение, после чего всё дерево сериализуется в пакет. Эта модель правильна, когда вы хотите прочитать лист, отредактировать его, пересчитать и записать обратно, потому что произвольный доступ к любой ячейке - это именно то, что нужно для редактирования. Она неправильна, когда вы льёте строки в одном направлении и никогда не оглядываетесь назад, потому что вы платите за хранение каждой строки без всякой пользы. Миллион строк объектов остаётся миллионом строк объектов, независимо от того, возвращаетесь ли вы к ним или нет
Потоковый писатель убирает дерево. Как только ячейка записана, она становится байтами в части листа, и эти байты передаются на zip-вывод. Поток листа - единственный буфер, который растёт, и он растёт на стороне вывода, а не как живые объекты Delphi в куче. В памяти остаётся фиксированный объём учётных данных: имена листов, несколько флагов, текущий номер строки, счётчик ячеек. Этот набор не меняется между первой и десятимиллионной строкой
Таблица общих строк - ловушка, а строки inline - выход
Большинство потоковых XLSX-писателей справляются хорошо, пока не встречают текст. Формат OOXML обычно хранит строки в таблице общих строк: каждая отдельная строка записывается один раз в отдельную часть, и каждая ячейка, содержащая эту строку, несёт индекс в таблицу вместо текста. Это хорошая оптимизация пространства для файлов с повторяющимися метками, и это настройка по умолчанию, которую использует стандартный путь сохранения. Проблема для потокового писателя жестокая. Для дедупликации таблица должна оставаться резидентной на протяжении всего задания, потому что любая строка, ещё не пришедшая, может повторить строку из уже записанной строки, и только полная карта в памяти увиденных строк может присвоить правильный индекс. Таким образом, единственная структура, которую потоковый писатель не может записать потоком, - это именно та структура, которая должна делать файл маленьким. Текстовые данные сводят на нет то потоковое преимущество, ради которого вы пришли
Прямой писатель полностью обходит таблицу. Строки записываются в виде inline, как ячейки t="inlineStr", чей текст находится непосредственно внутри ячейки в элементе <is><t>. Нет таблицы для накопления и нет карты увиденных строк для хранения, поэтому текстовые столбцы не требуют больше памяти, чем числовые. Компромисс явный и стоит его назвать прямо. Строки inline повторяют один и тот же текст везде, где он встречается, поэтому файл с множеством одинаковых меток больше на диске, чем эквивалент с общими строками. Вы тратите размер файла, чтобы получить постоянную память. Для однопроходного экспорта это правильная сторона компромисса, и zip-сжатие поглощает большую часть повторений на выходе
Таблица стилей поступает в конце, с одним форматом даты
Стили создают то же напряжение, что и строки. Книга ссылается на форматирование через часть стилей, и потоковый писатель не может держать растущую палитру стилей в шаге с ячейками, которые он уже сбросил. Прямой писатель отвечает на это, держа таблицу стилей маленькой и фиксированной и выводя её при закрытии, а не заранее. Один формат ячейки по умолчанию охватывает обычные ячейки. Один формат числа даты охватывает даты, зарегистрированный с кодом формата yyyy-mm-dd на известной позиции в списке форматов ячеек
Этот формат даты и есть причина существования WriteDateTime как отдельного вызова. Excel не имеет собственного типа даты; дата - это число в формате даты. WriteDateTime записывает значение как простое серийное число и помечает ячейку одним стилем даты, чтобы таблица отображала его как дату, а не пятизначное целое. Записываемое серийное число важно для обратной совместимости. Оно хранит значение TDateTime непосредственно в системе дат 1900 года, которая является тем же соглашением, что использует обычный путь сохранения TXLSXWorkbook. Поскольку оба пути соглашаются на серийном числе, файл, созданный потоковым писателем, считывается обратно через ридер HotXLS и открывается в Excel с датами, соответствующими задуманным, без расхождения на единицу или сюрпризов с эпохой между писателем и ридером
Порядок обязателен, потому что байты уже ушли
Потоковая запись обеспечивает свой профиль памяти одним правилом, которое необходимо соблюдать. Вывод испускается по ходу и не может быть пересмотрен, поэтому всё должно записываться в том порядке, в котором оно появляется в файле. Внутри строки ячейки идут в порядке возрастания столбцов. Внутри листа строки идут в порядке возрастания. Нет буфера, позволяющего писателю отсортировать ваши ячейки после факта, потому что строка, закрытая мгновение назад, уже является байтами в zip-потоке и больше недостижима. Передайте ему столбец 5, а затем столбец 2 в той же строке, и вывод будет некорректным, поскольку писатель просто выводит то, что вы ему даёте, в той последовательности, в которой вы это даёте
API строки имеет небольшое удобство для распространённого случая. AddRow принимает 1-based индекс строки, но передача 0 означает взять следующую строку после предыдущей, поэтому последовательное заполнение не должно отслеживать и передавать увеличивающийся счётчик. Каждый AddRow закрывает предыдущую строку, а каждый AddSheet закрывает предыдущий лист, поэтому вам никогда явно не нужно заканчивать строку или лист. Вы начинаете следующую, и писатель финализирует для вас открытую структуру
Экранирование обрабатывается там, где текст входит в XML
Любой текст, который вы записываете, становится частью XML-документа, поэтому пять предопределённых XML-сущностей должны быть экранированы, иначе пакет будет недействительным в тот момент, когда значение содержит амперсанд или угловую скобку. Писатель экранирует &, <, >, " и ' для вас как в тексте строк inline, так и в тексте формул - двух местах, где предоставленные вызывающим символы попадают внутрь разметки. Вы передаёте сырую WideString, а писатель делает её безопасной. Название компании вроде Smith & Co <Ltd> или формула, ссылающаяся на имя листа в кавычках, выходит как правильно сформированный XML без какого-либо экранирования с вашей стороны
Жизненный цикл и почему Destroy всё равно закрывает
Финализация пакета - это то, что записывает часть книги, часть стилей, части типов содержимого и связей и наконец центральный каталог zip. Эта работа происходит в Close. Пакет, который никогда не закрывается, - это неполный zip, который ни одна программа для работы с таблицами не откроет, поэтому закрытие - не необязательная очистка, а шаг, делающий файл действительным. Чтобы защититься от забытого Close в пути ошибки, Destroy выполняет закрытие «лучшим возможным образом», если пакет ещё открыт, поэтому освобождение писателя не утечёт базовый объект zip даже тогда, когда исключение пропустило явный вызов. Надёжный шаблон по-прежнему обычный для Delphi: записывайте внутри try, вызывайте Close и освобождайте в finally
Потоковая запись большого листа от начала до конца
Форма задания такова: начать, добавить лист, лить строки, закрыть. Пример ниже записывает строку заголовка, а затем длинный прогон типизированных строк данных, смешивая строки, числа, формулу без кэшированного результата и дату. Используемая им память для десяти строк и десяти миллионов строк одинакова, потому что каждая ячейка уходит в zip-поток сразу после записи
uses
lxDirectWrite;
procedure StreamReport(const Path: string; RowCount: Integer);
var
W: TXLSDirectWriter;
I: Integer;
begin
Writer := TXLSDirectWriter.Create;
try
Writer.BeginFile('export.xlsx');
Writer.AddSheet('Log Data');
// Header row
Writer.AddRow(1);
Writer.WriteString(1, 'Category');
Writer.WriteString(2, 'Value');
Writer.WriteString(3, 'Threshold');
Writer.WriteString(4, 'Status');
Writer.WriteString(5, 'Timestamp');
// Millions of rows, memory footprint remains unchanged
for R := 2 to 2000000 do
begin
Writer.AddRow(0); // 0 means next sequential row
Writer.WriteString(1, 'Sample');
Writer.WriteNumber(2, R * 0.15);
Writer.WriteNumber(3, 1000);
Writer.WriteFormula(4, Format('IF(B%d>C%d,"High","Normal")', [R, R]));
Writer.WriteDateTime(5, Now);
end;
Writer.Close;
finally
Writer.Free;
end;
end;Второй лист - это просто ещё один AddSheet перед продолжением, и писатель закрывает первый лист при открытии второго. Булевы флаги используют WriteBoolean, который записывает типизированную булеву ячейку, а не текст "True". Если вы хотите убедиться, что файл корректен и совместим при обратном чтении, свойство CellCount сообщает, сколько ячеек было записано, а обратное чтение результата потоковым ридером должно сообщить тот же итог
// A second sheet of typed flags after the data sheet above
W.AddSheet('Flags');
W.AddRow(1);
W.WriteString(1, 'Name');
W.WriteString(2, 'Active');
W.AddRow(0);
W.WriteString(1, 'alpha');
W.WriteBoolean(2, True);
WriteLn(Format('wrote %d cells', [W.CellCount]));Запись в поток вместо файла - это тот же код с BeginStream вместо BeginFile, что позволяет серверу отправлять книгу в HTTP-ответ или поток памяти без временного файла на диске. Писатель не владеет потоком, который вы передаёте, поэтому вы сохраняете контроль над его жизненным циклом
Когда задача - серверная конечная точка, создающая книги по запросу, шаблоны из статьи о потоковой записи для серверных и пакетных заданий показывают, как подключить это к обработчику запросов и плановому экспорту. Когда вопрос о более широкой стоимости очень больших книг - как чтения, так и записи - статья о производительности больших книг в Delphi описывает, куда реально уходят время и память. Потоковый прямой писатель поставляется в составе компонента HotXLS для Delphi и C++Builder, наряду с полными API чтения, редактирования и сохранения, описанными в других статьях этого блога