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

HotXLS potrafi przepisać jeden arkusz wewnątrz istniejącego pakietu XLSX bez parsowania ani ponownej kompresji reszty pliku. TXLSDirectWriter.BeginPatch otwiera pakiet źródłowy, kopiuje każdy wpis oprócz docelowego arkusza dosłownie razem z jego skompresowanymi bajtami i pozwala od nowa utworzyć ten jeden arkusz za pomocą zwykłych wywołań AddSheet, AddRow i Write*. Wykresy, bufory tabel przestawnych, motywy, style i współdzielone ciągi znaków nigdy nie są dekompresowane

Przepływ pracy, który to rozwiązuje, pojawia się przy raportowaniu i odświeżaniu danych. Skoroszyt przychodzi od zespołu biznesowego, niosąc tabele przestawne, fragmentatory, formaty warunkowe i dekadę nagromadzonego formatowania. Co noc jeden arkusz danych trzeba zastąpić świeżymi liczbami. Wczytanie i ponowny zapis całego skoroszytu kosztuje minuty na plik i, co ważniejsze, naraża na szwank wierność funkcji, które silnik wczytujący musi odtworzyć. Łatanie omija oba problemy, nie dotykając tego, czego dotykać nie musi

Dlaczego kopiowanie skompresowanych bajtów jest ciekawą częścią?

Wpis zip skopiowany na poziomie skompresowanym kosztuje tyle, co kopiowanie strumienia. Ten sam wpis przepuszczony przez zwykłą ścieżkę zapisu kosztuje dekompresję (inflate) po drodze do wewnątrz i kompresję (deflate) po drodze na zewnątrz, a deflate to droższa połowa. W skoroszycie z dużym buforem tabeli przestawnej i kilkudziesięcioma osadzonymi obrazami ta różnica to różnica między łatką, która kończy się w czasie potrzebnym na zapisanie nowego arkusza, a taką, która większość czasu spędza na ponownej kompresji bajtów, których nigdy nie zbadała

HotXLS wykorzystuje do tego CopyCompressedFrom, które zapisuje skompresowane bajty wpisu źródłowego prosto do archiwum docelowego. Gdy wpisu nie da się skopiować w ten sposób, ponieważ używa innej metody kompresji albo słabego szyfrowania, zapis przechodzi na kopiowanie strumienia zdekompresowanego zamiast zawodzić. Wpisy znaczników katalogów są pomijane, ponieważ zapis tworzy własne

Zastąpienie w miejscu albo zapis do nowego pliku

Dwa przeciążenia obejmują dwa kształty tego zadania. Postać w miejscu przygotowuje wynik w pliku tymczasowym obok oryginału, zamyka uchwyt źródłowy, a następnie usuwa i zmienia nazwę, więc awaria w trakcie zapisu pozostawia oryginał nienaruszony. Postać z jawnym celem pozostawia źródło nietknięte i może albo zastąpić arkusz, albo dołączyć nowy:

var
  W: TXLSDirectWriter;
begin
  W := TXLSDirectWriter.Create;
  try
    W.BeginPatch('monthly-dashboard.xlsx', 'Data');   // w miejscu
    W.AddSheet('Data');
    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.WriteFormula(1, '=SUM(B2:B2)');
    W.Close;
  finally
    W.Free;
  end;
end;

Wariant wstawiania przyjmuje ścieżkę źródłową i docelową plus InsertSheet:

  // Źródło pozostaje nietknięte; cel otrzymuje dodatkowy arkusz o nazwie Extra
  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 część wymagająca prawdziwej chirurgii księgowej. Zapis parsuje rejestr arkuszy w xl/workbook.xml oraz mapę relacji wiążącą każdy arkusz z jego częścią, a następnie wybiera kolejny wolny numer części, identyfikator arkusza i identyfikator relacji. Typy relacji podążają za konwencjami pakietu źródłowego, więc łatanie ścisłego skoroszytu ISO 29500 generuje ścisłe typy relacji, a łatanie przejściowego generuje typy przejściowe

Co łatka celowo odrzuca i ogranicza

Łańcuch obliczeń jest odrzucany w obu trybach. W trybie zastępowania jego wpisy opisują komórki w arkuszu, który w tej postaci już nie istnieje; w trybie wstawiania przesunięcie indeksów arkuszy unieważnia go wprost. Excel odbudowuje łańcuch przy kolejnym przeliczeniu, więc jego odrzucenie jest poprawne, a nie stratne. Ta część zostaje pominięta w kopii, a jej wpis relacji i nadpisanie typu zawartości są usuwane chirurgicznie

Wewnątrz łatki zmieniają się dwie semantyki tworzenia treści i obie wynikają z tej samej zasady: łatka nie może naruszyć części, których nie przepisała. Ciągi znaków są zapisywane bezpośrednio w arkuszu zamiast dodawane do tablicy współdzielonych ciągów, ponieważ tablica źródłowa przechodzi nietknięta. A StyleIndex odnosi się do wpisów w cellXfs pakietu źródłowego, nie do tabeli stylów, którą buduje zapis. Oznacza to, że można odwoływać się do formatów już zdefiniowanych w oryginalnym skoroszycie, co zwykle jest dokładnie tym, czego chce odświeżenie danych, ale oznacza też, że trzeba wiedzieć, który indeks niesie który format

// Wewnątrz łatki StyleIndex indeksuje cellXfs pakietu ŹRÓDŁOWEGO.
// Data wymaga jawnego indeksu, który mapuje się tam na format daty:
W.WriteDateTime(3, EncodeDate(2026, 8, 22), DateStyleIndexFromTemplate);

// Przeciążenie WriteDateTime bez stylu jest odrzucane w trybie łatki,
// ponieważ zakłada ono własną tabelę stylów zapisu, której łatka
// nigdy nie tworzy

Sześć punktów wejścia tworzenia treści jest zablokowanych: dodawanie tabel, wykresów, obrazów, komentarzy, nazw zdefiniowanych i stylów komórek zgłasza wyjątek w trybie łatki, z drugą siatką bezpieczeństwa przy zamykaniu, która zawodzi, jeśli którykolwiek z ich liczników jest niezerowy. Każda z tych funkcji wymagałaby edycji części, które łatka kopiuje dosłownie, a częściowo zedytowany pakiet jest gorszy niż odrzucona operacja. W jednej operacji można załatać dokładnie jeden arkusz

Kiedy łatać, a kiedy wczytywać

Łatanie jest właściwym narzędziem, gdy skoroszyt jest duży, zmiana ogranicza się do jednego arkusza, a reszta pliku musi przetrwać bit po bicie. Jest złym narzędziem, gdy zmiana obejmuje kilka arkuszy, gdy potrzebne jest nowe formatowanie lub nowe obiekty, albo gdy plik jest na tyle mały, że zwykłe wczytanie i zapis nic nie kosztują. Do masowego generowania od zera lepiej sprawdza się ścieżka strumieniowa opisana w strumieniowym zapisie bezpośrednim, która współdzieli to samo API AddRow i Write*, więc przechodzenie między jednym a drugim jest mechaniczne

Manipulacja na poziomie arkusza wewnątrz wczytanego skoroszytu, gdy rzeczywiście potrzebny jest pełny model obiektowy, jest opisana w duplikowaniu arkuszy w pakietach XLSX. A jeśli powodem rozważania łatki jest to, że przetwarzanie całego skoroszytu stało się powolne, warto przed wyborem podejścia przeczytać pomiary i zachowanie pamięci opisane w wydajności dużych skoroszytów

Weryfikacja, czy łatka rzeczywiście zrobiła to, co myślisz

Trzy sprawdzenia wyłapują niemal każdy błąd. Potwierdź, że części, których przetrwania oczekiwałeś, nadal są w archiwum, że xl/calcChain.xml zniknął, oraz że ponowne otwarcie pliku przez TXLSXWorkbook zgłasza oczekiwaną liczbę arkuszy — niezmienioną przy zastąpieniu i zwiększoną o jeden przy wstawieniu. Odczytanie z powrotem załatanego arkusza i porównanie kilku wartości i formuł zamyka pętlę

Jeden szczegół implementacyjny z rozwoju tej funkcji warto powtórzyć, ponieważ może dotknąć każdego, kto pisze podobny kod na poziomie zip. Nazwy części arkuszy są dopasowywane po prefiksie, a błąd o jeden w długości prefiksu oznacza, że predykat nigdy się nie dopasowuje, więc nowo zapisana część koliduje z istniejącą nazwą, a czytniki biorące ostatni wpis o danej nazwie po cichu wybierają zły arkusz. Jeśli łatka wygląda, jakby zamieniła zawartość dwóch arkuszy, spójrz najpierw na dopasowywanie nazw, a dopiero potem na XML

Łatanie w miejscu, zapisy strumieniowe i pełny model obiektowy skoroszytu są dostarczane w tej samej bibliotece dla Delphi i C++Builder; lista funkcji znajduje się na stronie komponentu arkusza kalkulacyjnego HotXLS dla Delphi