HotXLS: аудит книг и конвертация форматов в Delphi

Массовая нормализация электронных таблиц — это три задачи в одном пальто. У вас архив смешанных форматов: .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 с очищенным списком. Метод SaveAsHTML у XLSX снова ломает шаблон и возвращает 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