Електронна таблиця з мільйоном рядків і десятком стовпців — це цілком звичайний експорт із завдання звітування бази даних. Відкрийте її звичайним способом, завантаживши всю робочу книгу в TXLSWorkbook, і цей процес має матеріалізувати кожну з цих дванадцяти мільйонів клітинок як живий об'єкт ще до того, як виконається ваш перший рядок бізнес-логіки. Файл на диску може займати шістдесят мегабайтів стисненого XML. Дерево об'єктів, у яке він розгортається, перевищує його в кілька разів, і все це має бути резидентним одночасно, оскільки модель за своєю природою має довільний доступ. Для звіту, який ви збираєтеся прочитати зверху вниз і викинути, це величезний обсяг пам'яті, витрачений на структуру, яка вам ніколи не була потрібна
Існує і другий шлях через той самий файл. Замість побудови моделі ви скануєте XML аркуша лише вперед, по одній клітинці за раз, і дозволяєте кожній клітинці протікати повз після того, як ви на неї подивилися. Нічого не накопичується. Пам'ять залишається майже незмінною незалежно від того, чи має аркуш тисячу рядків або десять мільйонів, оскільки зчитувач ніколи не утримує більше, ніж ту частину, яку він зараз розбирає, плюс пару невеликих таблиць пошуку. Саме це робить прямий зчитувач HotXLS, і решта цієї статті про те, чому він залишається малим і що він дає вам натомість
Чому модель у пам'яті не масштабується
Файл XLSX — це ZIP-пакет частин XML, описаних стандартом ECMA-376. Кожен аркуш — це окрема частина, xl/worksheets/sheetN.xml, і всередині неї кожен рядок є елементом <row>, який містить елементи клітинок <c>. Звичайний шлях завантаження читає цю частину і створює адресуємий об'єкт для кожної клітинки, щоб ви могли пізніше запитати Cells[12345, 7] і отримати відповідь за константний час. Довільний доступ — це головний сенс моделі робочої книги, і саме він робить зручним редагування, обчислення формул та стилізацію
Ціною є те, що довільний доступ вимагає одночасної присутності всього. Ви не можете індексувати структуру, яку побудували лише частково. Тому пікова пам'ять при повному завантаженні є функцією кількості клітинок, і на аркуші з мільйонами заповнених клітинок ця функція потрапляє туди, де вашому сервісу краще не бути, особливо якщо кілька таких завдань працюють одночасно на спільній машині. Коли шаблон доступу, який вам насправді потрібен, є послідовним, сплата за довільний доступ є сплатою за можливість, якою ви не скористаєтеся
SAX-сканування лише вперед, яке не будує дерево
Прямий зчитувач відкриває ZIP-пакет і обходить кожну частину аркуша за допомогою pull-парсера у стилі SAX. SAX тут означає, що парсер повідомляє про події розбору по мірі їх зустрічі: початковий елемент, текстовий прогін, кінцевий елемент, а потім рухається далі. Він не зберігає після себе дерево вузлів. Зчитувач відстежує поточний рядок і стовпець за допомогою атрибутів r, збирає тип клітинки, індекс стилю, значення та текст формули по мірі надходження подій, і коли зустрічається закриваючий тег </c>, він видає одну клітинку і забуває про неї. Наступна клітинка повторно використовує ту саму жменьку локальних змінних
Оскільки між клітинками нічого не зберігається, обсяг використовуваної пам'яті не зростає з кількістю клітинок. Це та властивість, за яку варто триматися. Аркуш на двісті рядків і аркуш на двадцять мільйонів рядків коштують зчитувачу однаково резидентної пам'яті, і різниця між ними полягає лише в тому, як довго триватиме сканування. Ви відмовляєтеся від довільного доступу, головної особливості моделі, і натомість отримуєте стелю використання пам'яті, яку кількість клітинок не може пробити
Що залишається в пам'яті та чому саме ці дві частини
Сканування не є повністю без збереження стану, і ці винятки є повчальними. Дві невеликі таблиці повинні зберігатися в пам'яті протягом усього часу, оскільки сама по собі клітинка не містить достатньо інформації для інтерпретації без них
Перша — це таблиця спільних рядків. У SpreadsheetML текстова клітинка не зберігає власний текст. Вона несе t="s" та числове корисне навантаження, яке є індексом у xl/sharedStrings.xml, єдиному дедуплікованому списку кожного окремого рядка в робочій книзі. Це хороший компроміс простору для файлів, де однакові мітки повторюються в тисячах рядків, але це означає, що зчитувач має завантажити цю таблицю рядків наперед і тримати її в пам'яті, оскільки будь-яка клітинка в будь-якому місці будь-якого аркуша може посилатися на будь-який її запис. Розмір таблиці визначається кількістю унікальних рядків, а не кількістю клітинок, тому він залишається скромним навіть на величезних аркушах
Друга — це відображення форматів чисел із частини стилів. Числова клітинка та клітинка з датою байт у байт однакові в мережі: обидві є звичайним числом, оскільки дата в SpreadsheetML — це просто серійний номер дня. Єдине, що їх відрізняє, — це стиль клітинки, який вказує через cellXfs у xl/styles.xml на ідентифікатор формату числа. Щоб звітувати про дату саме як про дату, а не як про сирий серійний номер, зчитувач завантажує цю таблицю відображення стилів на формати та зберігає її. Все інше у файлі, фактичні дані клітинок, які складають основну масу байтів, протікає повз без збереження
Кожна клітинка повідомляє тип та значення
Кожна видана клітинка надходить як запис TXLSDirectCell. Він несе індекс та назву аркуша, рядок і стовпець (починаючи з 1), семантичний 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
case Cell.Kind of
xdkString: AccumulateLabel(Cell.Row, Cell.Col, VarToStr(Cell.Value));
xdkNumber: AddToTotals(Cell.Col, Double(Cell.Value));
xdkDate: NoteWhen(Cell.Row, VarToDateTime(Cell.Value));
xdkBoolean: FlagRow(Cell.Row, Boolean(Cell.Value));
xdkError: LogBadCell(Cell.Row, Cell.Col, VarToStr(Cell.Value));
end;
end;Як відрізнити дату від числа
Питання дати заслуговує на пильніший погляд, оскільки саме тут більшість наївних сканерів помиляються. На числовій клітинці немає типу дати. Клітинка, що містить серійне значення 46000, може бути кількістю, ціною або 17 лютого 2025 року, і файл повідомляє вам про це лише через ідентифікатор формату числа, доступний через стиль клітинки. Стандарт ECMA-376 резервує блок вбудованих ідентифікаторів форматів, значення яких фіксоване для кожного сумісного виробника, і ідентифікатори, що містять дати, знаходяться у двох діапазонах: від 14 до 22 для стандартних форматів дати та часу, і від 45 до 47 для форматів минулого часу, таких як [h]:mm:ss. Коли увімкнено параметр DetectDates, який працює за замовчуванням, зчитувач розв'язує стиль кожної числової клітинки до ідентифікатора її формату, і клітинка, ідентифікатор якої потрапляє в ці зарезервовані діапазони, звітується як xdkDate зі своїм Value, уже перетвореним на TDateTime у Delphi. Спеціальні формати також перевіряються шляхом перевірки коду формату на наявність токенів дати та часу, але зарезервовані діапазони є надійною основою. Вимкніть DetectDates, і таблиця стилів навіть не завантажуватиметься, кожна числова клітинка проходитиме як xdkNumber, а сканування стане трохи легшим
Пропуск аркушів і дострокове переривання
Послідовне сканування має тиху перевагу, з якою довільний доступ не може зрівнятися: ви можете зупинитися. Подія OnSheet спрацьовує перед відкриттям кожного аркуша і надає вам два перемикачі. Встановіть SkipSheet, і вся ця частина ніколи не розбиратиметься — саме так ви скануєте лише ті аркуші, які вас цікавлять у робочій книзі з багатьма аркушами, не сплачуючи за читання решти. Встановіть Abort, і все сканування завершиться негайно. Подія OnCell має власний Abort, тому ви можете зупинитися в той момент, коли знайшли те, що шукали: конкретний рядок, сигнальне значення, кінець блоку заголовка, не читаючи решту мільйонів клітинок. При скануванні лише вперед переривання є дійсно безкоштовним, оскільки робота, яку ви пропускаєте — це робота, яка ще не відбулася
procedure TReportScan.OnSheet(Sender: TObject; SheetIndex: Integer;
const SheetName: WideString; var SkipSheet: Boolean; var Abort: Boolean);
begin
// Scan only the "Data" sheet; leave the rest unread
SkipSheet := SheetName <> 'Data';
end;Підрахунок клітинок без обробника
Одне нещодавнє вдосконалення варто виділити, оскільки воно перетворює поширене запитання на єдиний дешевий виклик. Зчитувач рахує кожну заповнену клітинку, яку він проходить, і він робить це незалежно від того, чи підключений обробник OnCell. Раніше, якщо обробник не був встановлений, кількість заповнених клітинок поверталася як нуль, оскільки підрахунок був побічним ефектом видачі. Тепер підрахунок незалежний від видачі. Це означає, що ви можете поставити одне запитання: скільки заповнених клітинок насправді містить ця робоча книга, і отримати відповідь ціною сканування взагалі без зворотних викликів. ReadFile і ReadStream обидва повертають цей підсумок як Int64, і це ж число доступне згодом як властивість CellCount. Повернення -1 сигналізує, що файл не вдалося відкрити або він не є пакетом OOXML
var
Reader: TXLSDirectReader;
Populated: Int64;
begin
Reader := TXLSDirectReader.Create;
try
// No OnCell handler: a pure populated-cell census, still near-constant memory
Populated := Reader.ReadFile('quarterly_export.xlsx');
if Populated < 0 then
raise Exception.Create('Not a readable XLSX package')
else
Writeln(Format('%d populated cells (CellCount = %d)',
[Populated, Reader.CellCount]));
finally
Reader.Free;
end;
end;Для повного сканування ви приєднуєте обробник і викликаєте ReadFile точно так само. Контраст із повним завантаженням полягає в самому сенсі: якщо завантаження quarterly_export.xlsx у робочу книгу розгорнуло б кожну клітинку в резидентний об'єкт і утримувало б усе це, прямий зчитувач зберігає лише спільні рядки та таблицю стилів, тоді як дванадцять мільйонів клітинок протікають через ваш OnCell по черзі. Арифметика, що працювала для кожної клітинки, не залишає нічого позаду, тому пікова пам'ять встановлюється кількістю унікальних рядків робочої книги, а не кількістю її рядків
Прямий зчитувач — це правильний інструмент, коли завдання полягає в тому, щоб один раз прочитати велику робочу книгу та витягти дані або підбити підсумки. Якщо натомість вам потрібен довільний доступ повної моделі, але ви хочете, щоб він добре поводився на великих файлах, налаштування в наших нотатках про продуктивність великих робочих книг у Delphi охоплює цей шлях. А коли напрямок змінюється на зворотний, створюючи великий вивід замість його споживання, посібник з потокового запису для серверних пакетних завдань застосовує ту саму дисципліну постійної пам'яті до запису. Усі три постачаються як частина компонента HotXLS для Delphi та C++Builder, поряд з API читання, запису, формул та форматування, які висвітлені в інших статтях цього блогу