Створення середовища аудиту та конвертації книг у Delphi з HotXLS

Масова нормалізація електронних таблиць — це три проблеми в одному пальті. У вас є архів мішаних форматів: .xls епохи BIFF, сучасний .xlsx, розсип файлів .ods із якогось експерименту LibreOffice та жменька файлів, які ніхто не може відкрити, бо пароль пішов разом із колишнім працівником. Мета — конвертувати все в XLSX і CSV. Версія цього завдання, яку пишуть найчастіше, — це цикл, що відкриває кожен файл і зберігає його під новим розширенням, і вона працює доти, доки хтось не спитає, які файли втратили діаграми, скинули макроси чи взагалі не відкрилися. Цикл не має відповіді, бо саме конвертація не веде жодного запису. Робоче місце веде: воно спершу інвентаризує, потім конвертує, а потім верифікує, і всі три етапи мають ділитися інформацією, щоб хоч щось із цього заслуговувало на довіру

Зібрати таке робоче місце в Delphi чи C++Builder означає з’єднати чотири можливості HotXLS, жодна з яких не потребує встановленого Excel у жодній точці конвеєра. Є два нативних рушії: фасад BIFF8 для .xls і фасад OOXML для .xlsx та .ods. Є дешеві перевірочні виклики, що читають метадані без розбору всього файлу. Є лічильники аудиту на рівні аркуша, що кажуть, що книга насправді містить. І є матриця конвертації із задокументованим профілем точності для кожного маршруту. Робота полягає в тому, щоб знати, де в кожному з цих є гострий край, бо він є в кожному, і саме ці краї перетворюють чистий нічний пакет на понеділковий інцидент

Перевіряйте перед завантаженням: назви аркушів і виявлення шифрування

Відкрити 200-мегабайтну книгу лише для того, щоб виявити, що вона зашифрована, витрачає хвилини на файл, а помножене на великий архів це витрачає дні. Обидва фасади відкривають GetSheetNames, що читає метадані аркушів, не наповнюючи книгу. Реалізація BIFF сканує лише записи BoundSheet на початку потоку; реалізація OOXML читає лише workbook.xml усередині zip. Поруч, CanReadEncrypted виявляє контейнер шифрування, не намагаючись розшифрувати:

var
  Probe: TXLSXWorkbook;
  Names: TStringList;
begin
  Names := TStringList.Create;
  Probe := TXLSXWorkbook.Create;
  try
    if Probe.CanReadEncrypted(FileName) then
    begin
      Writeln(FileName + ': encrypted container - route to manual handling');
      Exit;
    end;
    if Probe.GetSheetNames(FileName, Names) <= 0 then
      Writeln(FileName + ': unreadable - quarantine')
    else
      Writeln(Format('%s: %d sheet(s), first "%s"',
        [FileName, Names.Count, Names[0]]));
  finally
    Probe.Free;
    Names.Free;
  end;
end;

Дві операційні деталі роблять цей цикл дешевим. GetSheetNames не скидає й не наповнює екземпляр книги, тож один об’єкт перевірки може класифікувати тисячі файлів, жодного разу не будучи пересозданим. А версія того самого виклику у фасаді XLS також розуміє пакети .xlsx, що робить його зручною єдиною перевіркою, коли розширенням файлів довіряти не можна, а в такому старому архіві їм рідко можна довіряти. Тріаж перед завантаженням заслуговує на власний розгляд; механіка легкої перевірки — у нашій статті про перелічування аркушів та легку перевірку книг

Підрахунок того, що книга справді містить

Щойно файл проходить тріаж, прохід аудиту вирішує його маршрут конвертації. Фасад XLSX відкриває лічильник для кожного сімейства можливостей, що впливає на рішення про точність: об’єднані клітинки, діаграми, зображення, умовне форматування, перевірку даних, таблиці, гіперпосилання й коментарі, плюс прапорці рівня книги для макросів, захисту й вихідного формату. Маршрут конвертації для файлу залежить майже повністю від того, які з них повертаються ненульовими

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then Exit;
    for I := 0 to Book.Sheets.Count - 1 do
    begin
      Sheet := Book.Sheets[I];
      Writeln(Format('%s: cells=%d merges=%d charts=%d cf=%d dv=%d protected=%s',
        [Sheet.Name, Sheet.Cells.Count, Sheet.MergedCells.Count,
         Sheet.Charts.Count, Sheet.ConditionalFormats.Count,
         Sheet.DataValidations.Count, BoolToStr(Sheet.IsProtected, True)]));
    end;
    if Book.HasVbaProject then
      Writeln('  contains VBA project - macro policy applies');
    if Book.ExternalLinks.Count > 0 then
      Writeln(Format('  %d external link(s)', [Book.ExternalLinks.Count]));
  finally
    Book.Free;
  end;
end;

Читайте Cells.Count з одним застереженням на увазі. Сховище клітинок розріджене, тож число рахує інстанційовані клітинки, а не прямокутну область використовуваного діапазону. Аркуш зі значенням в A1 та ще одним у ZZ9999 звітує про дві клітинки, а не про той мільйон із гаком, що лежить між ними. Еквівалентне сканування на боці BIFF використовує межі UsedRange разом із ForEachCell, і воно несе помилку на одиницю, що заплутує майже кожного при першій спробі: UsedRange.FirstRow та подібні до нього з нуля, тоді як Cells.Item[Row, Col] з одиниці. Обхід, що забуває додати одиницю до кожної межі, аудитує неправильний прямокутник і ніколи про це не каже

Два важелі скорочують вартість проходу лише-аудиту по великих застарілих файлах. Встановлення _DisableGraphics в true перед відкриттям .xls повністю пропускає розбір шару малюнків OfficeArt, що економить реальний час на книгах, щільно насичених фігурами. Утім, це строго оптимізація лише для читання: збереження з екземпляра, відкритого таким чином, скинуло б малюнки, які він так і не розібрав, тож прапорець належить лише шляхам, що ніколи не запишуть файл назад. Коли аудиту потрібен вміст окремих клітинок, а не лічильники, зворотний виклик ForEachCell обходить заповнені клітинки напряму й обходить накладні витрати Variant на кожен доступ, які платять індексовані властивості клітинок при кожному читанні, а це швидко накопичується на мільйонах клітинок

Нормалізуйте неузгоджені коди повернення заздалегідь

Виклики вводу-виводу HotXLS повідомляють про помилки через цілочислові результати, а не винятки, і домовленості не уніфіковані по всьому API. Більшість викликів відкриття й збереження повертають 1 при успіху та -1 при збої. GetSheetNames повертає кількість аркушів або -1 зі списком, очищеним. XLSX SaveAsHTML знову ламає шаблон і повертає 0 при успіху, -1 при індексі аркуша поза межами. Робоче місце, що перевіряє = 1 усюди, мовчки неправильно класифікує виклики, що сигналізують успіх якимось іншим способом, а те, що перевіряє <> -1, проковтне ті, що провалюються з іншим кодом

Правило, що витримує зіткнення з усім API, вужче, ніж здається: трактуйте <= 0 як збій для викликів, що повертають кількість, перевіряйте задокументоване значення успіху для кожної процедури збереження, яку ви справді використовуєте, і сховайте обидва за однією маленькою функцією перевірки результату, щоб домовленість жила рівно в одному місці. Пакетні конвеєри провалюються значно частіше через повільне накопичення неперевірених кодів повернення, ніж через будь-яку екзотичну помилку парсера, і вартість помилки тут проявляється через сорок тисяч файлів по тому, коли ніхто вже не пам’ятає, які конвертації насправді пройшли

Матриця конвертації і де кожен шлях втрачає дані

Два фасади ділять роботу конвертації між собою. TXLSXWorkbook відкриває XLSX, ODS і CSV, а зберігає XLSX, ODS, CSV, HTML, RTF та AES-зашифрований XLSX. TXLSWorkbook відкриває й зберігає BIFF, а також експортує HTML, RTF і CSV. Корисне тут те, що кожен шлях постачається із задокументованим профілем точності, а не розпливчастою обіцянкою правильності, тож ви можете вирішити заздалегідь, які маршрути безпечні для яких файлів

Експорт CSV записує UTF-8 з BOM, закінчення рядків CRLF і лапки за RFC 4180. Чого він не робить — це не обчислює формули: клітинка зі значенням =SUM(...) експортується як буквальний текст формули, тож аркуш формул перетворюється на аркуш рядків, якщо ви спершу не обчислите значення. Експорт HTML видає одну таблицю, де colspan і rowspan заміняють об’єднані клітинки, а базові стилі вбудовуються інлайново. Експорт RTF має жорсткішу межу: він не може розтягнути об’єднані клітинки через стовпці, тож клітинки продовження об’єднання виходять порожніми. Імпорт ODS навмисно легкий, за власною документацією бібліотеки. Скалярні значення й кешовані результати формул проходять; стилі, живі вирази формул ODF і малюнки — ні. Це має значення тієї миті, коли архів містить справжні файли OpenDocument, керовані OASIS ODF 1.3, де будь-що близьке до візуально точної конвертації потребує більшого, ніж цей шлях імпорту побудований нести, і саме прохід аудиту повідомляє вам про існування таких файлів до того, як пакет мовчки їх сплющить

SaveXLSWorkbookAsXLSX — це міст для даних, а не міст для макета

Фасад BIFF не може записувати OOXML напряму, тож перехід від .xls до .xlsx проходить через функцію SaveXLSWorkbookAsXLSX у модулі lxXlsxExport. Точність цього моста варто озвучити прямо, бо назва натякає на більше, ніж він насправді робить. Він копіює значення, формули, числові формати, кольори заливки, основні атрибути шрифтів, ширини стовпців і налаштування подання, такі як лінії сітки. Він не копіює рамки, об’єднані діапазони, коментарі, діаграми чи умовне форматування. Для нормалізації рівня даних, де подальші системи розбиратимуть результат, і ніхто не дивиться на форматування, цього рівно достатньо, і нічого, що комусь потрібне, не втрачається. Для оформленого звіту для правління, призначеного для читання людиною, цього недостатньо, і саме тут лічильники аудиту виправдовують своє місце: файл, який аудит позначив як такий, що несе діаграми й умовне форматування, має маршрутизуватися в чергу для ручної обробки, а не через міст, що скине обидва без жодного слова

var
  Legacy: IXLSWorkbook;        // посилання на інтерфейс: не викликати Free
  Modern: TXLSXWorkbook;
begin
  if SameText(ExtractFileExt(FileName), '.xls') then
  begin
    Legacy := TXLSWorkbook.Create;
    if Legacy.Open(FileName) <= 0 then Exit;
    if SaveXLSWorkbookAsXLSX(Legacy,
         ChangeFileExt(FileName, '.xlsx')) <= 0 then
      Writeln('bridge failed: ' + FileName);
  end
  else
  begin
    Modern := TXLSXWorkbook.Create;
    try
      Modern.StreamingWrite := True;     // передавати XML аркуша потоком у zip
      if Modern.Open(FileName) = 1 then
        Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
    finally
      Modern.Free;
    end;
  end;
end;

Цикл вище також показує важіль пропускної здатності на боці OOXML. Встановлення StreamingWrite у true передає XML аркуша потоком прямо у вихідний пакет замість того, щоб виставляти його одним гігантським рядком у пам’яті, і це різниця між комфортним запуском і крахом через нестачу пам’яті, коли файли сягають сотень тисяч рядків. Розмір і поведінка пам’яті для цього режиму отримують окремий розгляд у нашій статті про потокові записи для серверних пакетних завдань. Ще одна властивість має значення для пакету, що хоче використати кожне ядро: жоден із фасадів не є потокобезпечним, але жоден і не ділиться глобальним станом, тож підтримуваний шаблон паралельної конвертації — один екземпляр книги на робочий потік, без блокувань між ними

Файли з паролями і що з ними робити

Заблоковані файли архіву чисто розділяються за форматом, і цей поділ вирішує, куди вони йдуть. Застаріле шифрування .xls, чи то RC4, RC4 через CryptoAPI, чи стара обфускація XOR, читається: передайте пароль в Open, і файл конвертується, як і будь-який інший. Зашифровані пакети .xlsx — інша історія. HotXLS виявляє їх через CanReadEncrypted, але не може розшифрувати, тож єдиний чесний хід — маршрутизувати їх у чергу, де людина відкриває й повторно зберігає кожен в Excel, перш ніж він повернеться до конвеєра. Цю асиметрію варто закладати в дизайн заздалегідь, бо зашифровані файли XLSX — найімовірніше саме ті записи, до яких комусь справді є діло

Замикання циклу верифікацією

Третій етап — той, який пропускають, і саме пропуск перетворює масову конвертацію на відповідальність. Жоден шлях збереження в HotXLS не обчислює формули. Excel перераховує при відкритті файлу, тож конвертація з XLSX у XLSX лишається правильною, але ціль CSV отримує текст формули дослівно, якщо конвеєр спершу не виконає Calculate для клітинок і не запише результати назад. Знання цього заздалегідь — різниця між CSV, повним чисел, і CSV, повним рядків =SUM(...), яких ніхто не помічає, поки подальший імпорт на них не похлинеться

Сама верифікація достатньо дешева, щоб не було виправдання її пропускати. Повторно відкрийте кожен конвертований файл тією самою бібліотекою, повторно запустіть лічильники аудиту й порівняйте їх із числами до конвертації, які прохід інвентаризації вже записав. Кількість аркушів, що впала, кількість діаграм, що впала до нуля там, де джерело мало три, кількість клітинок, що обвалилася: кожне з цього — безшумна втрата, спіймана ціною другого відкриття. Додайте до цього вибіркову перевірку зразка на око в Excel чи LibreOffice, і ця комбінація ловить переважну більшість пошкоджень конвертації до того, як вони підуть у продакшн. Саме заради цього етап інвентаризації живить етап верифікації. Без чисел «до» числа «після» нічого не доводять

Робоче місце, що ставить аудит на перше місце, перетворює ризиковану масову конвертацію на вимірюваний процес із карантинною смугою для файлів, що не можуть пройти чисто. Усі виклики перевірки, підрахунку й конвертації, показані тут, — частина HotXLS Delphi Component, який виконує їх нативно, в одному процесі, без автоматизації Excel