Artykuł techniczny

Łatanie jednego arkusza w dużym pliku XLSX z Delphi

Finansowy skoroszyt utrzymywany ręcznie zbiera pamięci podręczne tabel przestawnych, fragmentatory, formaty warunkowe i lata stylów komórek, a nocne zadanie musi tylko podmienić liczby na jednej karcie danych. Pełne wczytanie i zapis parsuje, przebudowuje i ponownie kompresuje każdą część. Przepisanie tylko tej części arkusza i przeniesienie każdego innego wpisu zipu nietkniętym pomija niemal całą tę robotę

Możliwe to jest dzięki samemu formatowi pakietu. Plik .xlsx to archiwum zip części XML ułożone według Open Packaging Conventions (ECMA-376 Part 2), i każdy wpis zipu zapisuje własną metodę kompresji, CRC-32 i rozmiar skompresowany w nagłówku lokalnym oraz jeszcze raz w katalogu centralnym. Wpis może więc przejść z jednego archiwum do drugiego nigdy nie będąc napompowanym, pod warunkiem, że kopiujący zapisze te trzy wartości razem z surowymi bajtami. Pamięć podręczna tabeli przestawnej nie musi być rozumiana, żeby ją zachować. Wystarczy ją skopiować

Dlaczego kopiować skompresowane bajty, zamiast ponownie zapisywać skoroszyt?

Kopiowanie na poziomie skompresowanym zamienia większość łatki w zwykłą kopię strumienia. W HotXLS TXLSDirectWriter.BeginPatch otwiera pakiet źródłowy tylko do odczytu i oddaje każdy wpis, którego łatka nie rusza, do TZipArchive.CopyCompressedFrom, które zapisuje skompresowane bajty, CRC i rozmiary wpisu źródłowego wprost do archiwum docelowego. Konwencjonalna ścieżka zapisu płaci za inflate w drodze do środka i deflate w drodze na zewnątrz, a deflate to droższa połowa. W skoroszycie, którego masa to duży bufor tabeli przestawnej plus kilka tuzinów osadzonych obrazów, czas idzie w arkusz, który faktycznie piszesz

Surowa kopia pokrywa wpisy przechowywane nieskompresowane (metoda zip 0) i wpisy skompresowane deflate (metoda 8). Wpis używający innej metody kompresji też jest przenoszony: zapis czyta go przez strumień dekompresujący i wypisuje z powrotem, więc część ląduje w celu tak czy inaczej. Wpisy znaczników katalogów nie niosą treści i są pomijane, bo zapis wytwarza własne. Garść wpisów nigdy nie jest kopiowana w ogóle, bo są odtwarzane albo edytowane: docelowy arkusz i jego część _rels, xl/calcChain.xml, xl/_rels/workbook.xml.rels i [Content_Types].xml, plus xl/workbook.xml, gdy wstawiany jest arkusz

Diagram łaty w miejscu XLSX HotXLS z Delphi: arkusz docelowy jest pisany na nowo przez AddSheet, AddRow i wywołania Write, podczas gdy każda inna część pakietu przechodzi przez CopyCompressedFrom ze swoimi skompresowanymi bajtami dosłownie, a nieaktualna część calcChain jest porzucana, bo Excel przebudowuje ją przy ponownym liczeniu
HotXLS kopiuje nietknięte części na poziomie skompresowanym i na nowo pisze tylko arkusz docelowy, podczas gdy nieaktualny łańcuch obliczeń jest usuwany, a nie kopiowany

Zastąpienie jednego arkusza w miejscu przez BeginPatch

Przeciążenie dwuargumentowe BeginPatch(FileName, SheetName) zastępuje istniejący arkusz i zapisuje wynik z powrotem pod tą samą ścieżką. Rozwiązuje nazwę przez rejestr arkuszy w xl/workbook.xml i mapę relacji wiążącą każdy arkusz z jego częścią, porównuje nazwy bez rozróżniania wielkości liter i podnosi wyjątek zawierający „worksheet not found”, gdy nic nie pasuje. Stamtąd arkusz jest autoryzowany tymi samymi wywołaniami AddSheet, AddRow i Write*, których zapis strumieniowy używa dla nowych plików

var
  W: TXLSDirectWriter;
begin
  W := TXLSDirectWriter.Create;
  try
    W.BeginPatch('monthly-dashboard.xlsx', 'Data');  // zamiana w miejscu
    W.AddSheet('Data');               // nazwa jest informacyjna w trybie łatki
    W.AddRow(1);
    W.WriteString(1, 'Region');
    W.WriteString(2, 'Revenue');
    W.AddRow(2);
    W.WriteString(1, 'North');
    W.WriteNumber(2, 184320.55);
    W.AddRow(3);
    W.WriteString(1, 'South');
    W.WriteNumber(2, 97410.20);
    W.AddRow(4);
    W.WriteString(1, 'Total');
    W.WriteFormula(2, 'SUM(B2:B3)');  // bez wiodącego '='
    W.Close;
  finally
    W.Free;
  end;
end;

Dwa szczegóły w tym zestawieniu łatwo błędnie odczytać. Nazwa przekazana do AddSheet niczego nie przemianowuje: karta zachowuje nazwę, sheetId i pozycję, które już ma w xl/workbook.xml, więc AddSheet('Totals') nadal pisałby do Data. A drugie AddSheet podnosi wyjątek, bo jedna łatka pisze dokładnie jeden arkusz. Odświeżenie trzech kart znaczy trzy łatki, z których każda kopiuje pakiet jeszcze raz

Formuła wchodzi bez znaku równości. WriteFormula wkłada swój tekst bezpośrednio do elementu <f> komórki, a ECMA-376 Part 1 §18.3.1.40 przechowuje tam formuły bez znaku „=”, który Excel pokazuje w pasku formuły; napisanie '=SUM(B2:B3)' wkłada dosłowny znak równości do XML. WriteFormula nie emituje też żadnego buforowanego wyniku. Jeśli coś w dalszej części potoku czyta wartości buforowane zamiast ponownie liczyć, WriteFormulaNumber zapisuje formułę razem z liczbą, którą już znasz jako jej wynik

Przeciążenie w miejscu nigdy nie pisze do oryginału w trakcie pracy. Łatany pakiet jest budowany w pliku sąsiednim nazwanym od źródła z doklejonym .hxlpatch.tmp. Przy Close uchwyt źródła jest zwalniany, a odstawiony plik zastępuje oryginał jedną zmianą nazwy. Gdyby ta zmiana nazwy zawiodła, Close podnosi wyjątek, którego komunikat nazywa obie ścieżki, zachowuje odstawioną kopię i zostawia oryginał tam, gdzie był. Proces zabity przed Close zostawia oryginał nienaruszony i zbłąkany .hxlpatch.tmp obok niego, który następne BeginPatch na tym pliku usuwa, zanim zacznie

Dokładanie nowego arkusza do kopii szablonu

Przeciążenie czteroargumentowe BeginPatch(SourceFileName, TargetFileName, SheetName, InsertSheet) zostawia plik źródłowy w spokoju i zapisuje nowy pakiet. Przy InsertSheet = False zastępuje nazwany arkusz w kopii; przy True dokłada nowy arkusz pod tą nazwą i podnosi „worksheet already exists”, jeśli szablon ma już kartę o takiej nazwie

  // template.xlsx zostaje nietknięty; output.xlsx dostaje jedną dodatkową kartę
  W.BeginPatch('template.xlsx', 'output.xlsx', 'Extra', True);
  W.AddSheet('Extra');
  W.AddRow(1);
  W.WriteString(1, 'Appended by the nightly job');
  W.Close;

Wstawianie to miejsce, gdzie księgowanie pakietu jest edytowane. Zapis skanuje istniejące wpisy xl/worksheets/sheetN.xml po najwyższy numer części, elementy <sheet> po najwyższe sheetId i relacje skoroszytu po najwyższe rId, po czym bierze kolejną wartość każdego z nich. Nowy element <sheet> trafia tuż przed </sheets>, więc nowa karta pokazuje się ostatnia. Atrybut relacji ponownie używa prefiksu, jakim skoroszyt źródłowy wiąże arkusze, co zwykle jest r:id, ale nie musi

Typy relacji też podążają za pakietem źródłowym. Gdy relacje skoroszytu używają już ścisłych przestrzeni nazw ISO/IEC 29500 spod purl.oclc.org, nowa relacja arkusza jest zapisywana ze ścisłym URI typu; skoroszyt przejściowy dostaje przejściowy. Załatany skoroszyt ścisły nie kończy więc z jedną relacją przejściową wśród ścisłych

Diagram dwóch przeciążeń BeginPatch HotXLS w Delphi: forma w miejscu odstawia łatany skoroszyt w pliku tymczasowym i kończy atomowym zamknięciem, usunięciem i zmianą nazwy, więc awaria zostawia oryginał nietknięty, podczas gdy forma wstawiania pisze nowy plik po operacji na rejestrze arkuszy i mapie relacji
Łatanie w miejscu etapuje wynik w pliku tymczasowym przed atomową zamianą, podczas gdy wstawianie przepisuje rejestr arkuszy i mapę relacji osobnego pliku docelowego

Style, ciągi znaków i części, których łatka nie rusza

Wewnątrz łatki wszystko, na co wskazuje nowy arkusz, musi już istnieć w pakiecie źródłowym. Ciągi znaków są więc zapisywane wprost w arkuszu zamiast do xl/sharedStrings.xml, bo tabela współdzielonych ciągów przechodzi bajt w bajt i nie może urosnąć. Ustawienie UseSharedStrings na True sprawia, że AddSheet podnosi wyjątek w trybie łatki

Ta sama reguła zmienia, co znaczy StyleIndex. W łatce to pozycja na liście cellXfs skoroszytu źródłowego (ECMA-376 Part 1 §18.8.10), a zapis sprawdza tylko, czy mieści się między 0 a 65534. Nie potrafi stwierdzić, czy indeks 14 istnieje w tym konkretnym pliku, więc zła liczba idzie wprost do wyjścia. Najbardziej godny zaufania sposób, by znaleźć właściwy, to rozpakować aktualną wersję arkusza i przeczytać atrybut s na komórce, która już niesie pożądany format; ta liczba to pozycja w cellXfs. Excel może dodać wpisy do tej listy, kiedykolwiek ktoś formatuje komórki w szablonie, więc szukaj indeksu jeszcze raz po każdej edycji szablonu, zamiast ufać stałej zapisanej rok temu

const
  // pozycja cellXfs formatu daty w TYM szablonie, wzięta z
  // atrybutu s="..." istniejącej komórki daty; sprawdź ponownie po edycjach szablonu
  TemplateDateXf = 5;
begin
  ...
  W.AddRow(5);
  W.WriteString(1, 'Refreshed');
  W.WriteDateTime(2, Now, TemplateDateXf);  // jawny indeks: akceptowane

  // W.WriteDateTime(2, Now);  podnosi w trybie łatki: jego niejawny styl daty
  //                           mieszka w części stylów, której łatka nigdy nie pisze
end;

Wszystko, co dodałoby nowe części pakietu, jest odrzucane. AddTable, AddChart, rodzina AddImage, AddComment, AddDefinedName i AddCellStyle podnoszą wyjątki od razu po wywołaniu, a Close sprawdza jeszcze raz przed zatwierdzeniem, co łapie też formaty warunkowe, które ustawiają własne wypełnienie, kolor czcionki, pogrubienie albo kursywę, bo te potrzebują nowych wpisów w styles.xml. Odrzucone wywołanie jest lepsze niż pakiet, w którym arkusz wskazuje część tabeli, której nikt nie zarejestrował

Tryb zastępowania też startuje z arkuszem od zera. Część relacji starego arkusza nie jest kopiowana, więc rysunek, komentarze albo zewnętrzne hiperłącza przyczepione do poprzedniej wersji nie są już przyczepione do nowej, a szerokości kolumn, zamrożone okienka, scalone zakresy i walidacja danych zapisane w starym XML arkusza też znikają. Te, które mieszkają w XML arkusza, da się zapisać ponownie przez SetColumnWidth, FreezePanes, MergeCells, AddDataValidation i AddHyperlink. Wykres albo obraz zakotwiczony na karcie danych nie wróci przez łatkę, więc trzymaj wykresy na osobnym arkuszu i celuj ich serie w kartę danych

Łańcuch obliczeń jest porzucany w obu trybach. xl/calcChain.xml wylicza komórki z formułami w kolejności, w jakiej Excel ostatni raz je przeliczył; po zastąpieniu jego wpisy opisują komórki, które nie istnieją już w tej postaci, a po wstawieniu indeksy arkuszy, do których się odnosi, przesunęły się. Część jest zostawiana poza kopią, a jej relacja i jej Override w [Content_Types].xml są usuwane, więc pakiet nie wskazuje brakującej części. Excel przebuduje łańcuch przy następnym przeliczeniu

Gdy pętla wierszy rzuci w połowie

Destruktor TXLSDirectWriter dokańcza pakiet, gdy Close nigdy nie zostało wywołane, więc zapomniane Close nie zostawia na wpół napisanego zipa leżącego odłogi. Ponieważ dokańcza to, co zapis trzyma, wyjątek, który rozwija się przez Free pod przeciążeniem w miejscu, nadal zatwierdza wiersze zapisane dotąd i podmienia nimi oryginał. Pętla, która pobiera i zapisuje wiersze, rzuca w połowie i zwalnia zapis w bloku finally, kończy się dokładnie tak

Dla wszystkiego, co biegnie bez nadzoru, pisz do jawnego celu, otwórz wynik, sprawdź go i dopiero wtedy sam podmień żywy plik. Wyjątek z pętli wierszy nadal pozwala Free dokończyć odstawiony plik, ale pomija też zamianę, więc żywy skoroszyt zachowuje wczorajsze liczby zamiast połowy dzisiejszych

uses
  Windows, SysUtils, Variants, lxDirectWrite, lxHandleX;

procedure RefreshDashboard;
const
  Live = 'monthly-dashboard.xlsx';
  Staged = 'monthly-dashboard.next.xlsx';
var
  W: TXLSDirectWriter;
  Check: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  W := TXLSDirectWriter.Create;
  try
    W.BeginPatch(Live, Staged, 'Data', False);
    W.AddSheet('Data');
    WriteRefreshedRows(W);  // twoja własna pętla wierszy, nie część HotXLS
    W.Close;
  finally
    W.Free;
  end;

  Check := TXLSXWorkbook.Create;
  try
    if Check.Open(Staged) <> 1 then
      raise Exception.Create('Staged workbook does not open');
    Sheet := Check.Sheets.SheetByName('Data');
    if (Sheet = nil) or (VarToStr(Sheet.Cells[2, 1].Value) <> 'North') then
      raise Exception.Create('Data sheet did not come back as written');
  finally
    Check.Free;
  end;

  // Windows API, nie HotXLS: podmień żywy plik dopiero po sprawdzeniach
  if not MoveFileEx(PChar(Staged), PChar(Live), MOVEFILE_REPLACE_EXISTING) then
    RaiseLastOSError;
end;

TXLSXWorkbook.Open zwraca 1 przy sukcesie, a i Sheets, i Cells są liczone od jedynki: Check.Sheets[1] to pierwsza karta, a Cells[2, 1] to A2. Poza wartością kontrolną dwa sprawdzenia strukturalne łapią większość błędów. Check.Sheets.Count powinno równać się liczbie szablonu po zastąpieniu i przerastać ją o jeden po wstawieniu, a spis archiwum z dowolnego narzędzia zip nie powinien pokazywać ani xl/calcChain.xml, ani nazwy wpisu pojawiającej się dwa razy. Zduplikowane nazwy mają znaczenie, bo niektóre czytniki biorą ostatni wpis o danej nazwie, co sprawia, że dwa arkusze wyglądają, jakby ich zawartości zostały zamienione

Łatać, strumieniować czy wczytać skoroszyt?

Łatanie pasuje, gdy skoroszyt jest duży, zmiana ogranicza się do jednego arkusza, a reszta pliku musi przetrwać bit w bit. To złe narzędzie, gdy zmiana obejmuje kilka arkuszy, gdy nowe dane potrzebują formatów, których szablon jeszcze nie definiuje, gdy wykresy albo tabele muszą powstać, albo gdy plik jest na tyle mały, że zwykłe wczytanie i zapis nie kosztują niczego wartego mierzenia. Do masowego generowania od zera lepiej pasuje strumieniowy zapis bezpośredni, a ponieważ dzieli te same wywołania AddRow i Write*, przenoszenie kodu między nimi to głównie kwestia zamiany BeginFile na BeginPatch

Gdy zadanie naprawdę potrzebuje pełnego modelu obiektowego, praca na poziomie arkusza wewnątrz wczytanego skoroszytu jest omówiona w duplikowaniu arkuszy w pakietach XLSX. A jeśli łatanie jest na stole tylko dlatego, że przetwarzanie całego skoroszytu stało się powolne, wydajność dużych skoroszytów patrzy, gdzie faktycznie idzie czas i pamięć wczytania i zapisu, zanim zobowiążesz się do podejścia

Zapis łatki, zapis strumieniowy i pełny model obiektowy TXLSXWorkbook płyną wszystkie w jednej bibliotece dla Delphi i C++Buildera; edycje i pełna lista funkcji są na stronie komponentu arkusza kalkulacyjnego HotXLS dla Delphi