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
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
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