Изграждане на работен плот за одит и конвертиране на работни книги в Delphi с HotXLS

Задачата по масово нормализиране на електронни таблици е три проблема под едно наметало. Имате архив от смесени формати: .xls от ерата на BIFF, модерен .xlsx, разпръснати .ods файлове от някой експеримент с LibreOffice и шепа файлове, които никой не може да отвори, защото паролата е си отишла с бивш служител. Целта е всичко да се конвертира в XLSX и CSV. Версията на тази задача, която повечето хора пишат, е цикъл, който отваря всеки файл и го записва под ново разширение, и това работи точно докато някой не попита кои файлове са загубили своите диаграми, изпуснали са макросите си или изобщо не са се отворили. Цикълът няма отговор, защото самото конвертиране не пази запис. Един работен плот обаче пази: той първо описва, второ конвертира и трето проверява, и трите етапа трябва да споделят информация, за да бъде всичко това надеждно.

Сглобяването на този работен плот в Delphi или C++Builder означава свързване на четири възможности на HotXLS, нито една от които не изисква инсталиран Excel никъде в процеса. Има два вградени двигателя, BIFF8 интерфейс за .xls и OOXML интерфейс за .xlsx и .ods. Има евтини сондиращи извиквания, които четат метаданни, без да анализират целия файл. Има броячи за одит на всеки лист, които ви казват какво всъщност съдържа работната книга. И има матрица за конвертиране с документиран профил на вярност за всеки маршрут. Работата се състои в това да знаете къде всеки от тях има остър ръб, защото всеки от тях има, и тези ръбове са точно нещата, които превръщат безпроблемната нощна пакетна обработка в инцидент в понеделник сутрин.

Сондиране преди зареждане: имена на листове и откриване на шифриране

Отварянето на работна книга от 200 MB само за да откриете, че е шифрирана, губи минути за всеки файл, а умножено по голям архив, това губи дни. И двата интерфейса предоставят 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 с едно важно предупреждение. Хранилището за клетки е разредено (sparse), така че числото отчита създадените клетки, а не правоъгълната област на използвания диапазон. Лист с една стойност в A1 и друга в ZZ9999 отчита две клетки, а не милиона и нещо, които лежат между тях. Еквивалентното сканиране от страната на BIFF използва границите на UsedRange заедно с ForEachCell и носи грешката с единица (off-by-one), която обърква почти всеки първия път: UsedRange.FirstRow и неговите сродни свойства са базирани на 0, докато Cells.Item[Row, Col] е базиран на 1. Обхождане, което забравя да добави единица към всяка граница, одитира грешния правоъгълник и никога не съобщава за това.

Два лоста намаляват цената на преминаването само за одит през големи стари файлове. Настройването на _DisableGraphics на true преди отварянето на .xls файл пропуска напълно анализа на чертожния слой OfficeArt пропуска напълно, което спестява реално време при работни книги, наситени с фигури. Това обаче е строго оптимизация само за четене: записването от екземпляр, отворен по този начин, би изхвърлило рисунките, които никога не са били анализирани, така че флагът принадлежи само на пътища, които никога няма да запишат файла обратно. Когато одитът се нуждае от съдържание за всяка клетка, а не от бройки, обратната функция (callback) 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 заместват обединените клетки, а основните стилове са вградени (inlined). Експортът на RTF има по-строго ограничение: той не може да разпростира обединени клетки през колони, така че клетките-продължения на обединяването излизат празни. Вносът на ODS е лек по дизайн, според собствената документация на библиотеката. Скаларните стойности и кешираните резултати от формули се пренасят; стиловете, живите изрази на формули на ODF и чертежите не се пренасят. Това е от значение в момента, в който архивът съдържа реални OpenDocument файлове под управлението на OASIS ODF 1.3, където всичко, което е близо до визуално вярно конвертиране, се нуждае от повече, отколкото този път за импортиране е създаден да поддържа, и одитният преглед е това, което ви казва, че тези файлове съществуват, преди пакетът тихо да ги изравни.

SaveXLSWorkbookAsXLSX е мост за данни, а не за оформление

Интерфейсът на BIFF не може да пише OOXML директно, така че преминаването от .xls към .xlsx става през функцията SaveXLSWorkbookAsXLSX в модула lxXlsxExport. Точността на този мост трябва да се каже ясно, тъй като името предполага повече, отколкото той прави. Той копира стойности, формули, числови формати, цветове на запълване, основни атрибути на шрифта, ширини на колони и настройки на изгледа, като например мрежови линии (gridlines). Той не копира граници, обединени диапазони, коментари, диаграми или условни формати. За нормализиране на ниво данни, където системите надолу по веригата ще анализират резултата и никой няма да гледа форматирането, това е напълно достатъчно и не се губи нищо необходимо. За форматиран отчет на управителния съвет, предназначен за четене от човек, това не е достатъчно и именно тук одитните броячи заслужават мястото си: файл, който одитът е маркирал като съдържащ диаграми и условни формати, трябва да се насочи към опашка за ръчна обработка, а не през мост, който ще изхвърли и двете без дума.

var
  Legacy: IXLSWorkbook;        // interface reference: do not 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;     // stream sheet XML into the 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 Component, който ги изпълнява вградено в процеса без автоматизация на Excel.