LlosLab Software Development BlogNotatki inżynieryjne dla programistów PDF, Delphi i oprogramowania do dokumentów.

Artykuł techniczny

Rozwijanie si formuł współdzielonych XLSX w Delphi: pułapki

HotXLS Component

Kontynuacja formuły współdzielonej w XLSX nie niesie żadnego tekstu formuły. Jej element <f t="shared" si="N"/> wskazuje na komórkę wzorcową gdzie indziej w arkuszu, a czytnik musi odtworzyć tekst, przesuwając formułę wzorcową o różnicę wiersza i kolumny. HotXLS Component dla Delphi i C++Buildera wykonuje to rozwinięcie w momencie otwarcia pliku, tak że każda kontynuacja zgłasza kompletną formułę

Jeśli kiedykolwiek wczytałeś rzeczywisty plik XLSX w bibliotece firmy trzeciej i odkryłeś, że kolumna tysiąca formuł ma tekst dokładnie w jednej komórce, a puste ciągi w pozostałych 999, spotkałeś tę funkcję od złej strony. Nic nie jest uszkodzone. Plik robi dokładnie to, na co pozwala ECMA-376, a czytnik po prostu zatrzymał się w miejscu, w którym zatrzymał się XML

Dlaczego komórka formuły współdzielonej jest pusta?

Ponieważ format celowo zapisuje formułę raz. W ECMA-376 Part 1 i ISO/IEC 29500-1 element <f> (§18.3.1.40) niesie atrybut t typu ST_CellFormulaType, a wartość shared oznacza, że ta komórka uczestniczy w grupie identyfikowanej atrybutem si. Dokładnie jedna komórka w grupie, wzorcowa, niesie też atrybut ref podający zakres, do którego stosuje się grupa, i tylko ta komórka niesie tekst formuły jako zawartość elementu. Każda inna komórka w grupie to kontynuacja. Powtarza t="shared" i to samo si, a jej zawartość elementu jest pusta. Excel zapisuje te grupy agresywnie, ponieważ wypełnienie w dół kolumny na 200 000 wierszy zwija się z 200 000 ciągów formuł do jednego ciągu plus 199 999 malutkich elementów zastępczych. Oszczędność jest realna, a koszt spada w całości na czytnika: bez rozwinięcia kontynuacja nie ma sama w sobie żadnego znaczenia

Przesunięcie to tłumaczenie, nie kopia tekstu

HotXLS rozwiązuje kontynuację, lokalizując formułę wzorcową zarejestrowaną pod tym samym si, obliczając deltę wiersza i kolumny od kotwicy wzorca do bieżącej komórki oraz tłumacząc każde odwołanie w formule wzorcowej o tę deltę. Wymiary względne się przesuwają, bezwzględne nie, a odwołania mieszane przesuwają tylko swoją część niebezwzględną. Literały łańcuchowe są całkowicie pomijane, więc formuła, która akurat zawiera tekst "A1", zachowuje ten tekst niezmieniony w każdej kontynuacji

const
  // xl/worksheets/sheet1.xml, trimmed to the interesting cells
  SheetXml: WideString=
    '<row r="1"><c r="A1"><v>1</v></c>'+
    '<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
    'A1+$A$1+A$1+$A1+&quot;A1&quot;+SUM(A1:A2)</f><v>7</v></c></row>'+
    '<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
    '<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';

var
  Wb: TXLSXWorkbook;
  Sh: TXLSXWorksheet;
begin
  Wb:= TXLSXWorkbook.Create;
  try
    Wb.Open(FileName);
    Sh:= Wb.Sheets[1];
    // Master, verbatim
    // B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
    // Follower one row down: relative row moves, absolute row frozen,
    // the mixed A$1 keeps its row, and the literal stays a literal
    // B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
    ShowMessage(Sh.Cells[2, 2].Formula);
  finally
    Wb.Free;
  end;
end;

Atrybut ref jest bramką, nie ozdobą. Kontynuacja, której współrzędne leżą poza stosowalnym zakresem wzorca, nie jest rozwijana, ponieważ plik wypowiadałby wtedy twierdzenie, którego grupa nie popiera. Podobnie, gdy przesunięcie wypchnęłoby odwołanie powyżej wiersza pierwszego lub na lewo od kolumny A, HotXLS emituje dla tego tokenu #REF! zamiast po cichu je przycinać, co dokładnie odpowiada temu, co samo produkowałby Excel przy tej samej edycji. To tłumaczenie jest bliskim kuzynem, ale nie tym samym, co przepisywanie odwołań zachodzące przy wstawianiu lub usuwaniu wierszy. Ta ścieżka ma własne reguły dotyczące tego, co dzieje się z zakresem, przez który przechodzi edycja, i jest opisana osobno w artykule o dostosowywaniu odwołań formuł przy wstawianiu i usuwaniu. Rozwinięcie współdzielone jest prostsze: to czyste przesunięcie od znanej kotwicy, zastosowane raz, w momencie parsowania

Jakie kształty odwołań musi obsłużyć mechanizm przesunięcia?

Wszystkie, inaczej rozwinięcie jest zamaskowanym błędem utraty danych. Naiwny mechanizm przesunięcia, który rozumie tylko A1 i A1:B2, uszkodzi lub porzuci bardziej egzotyczne formy, a prawdziwe skoroszyty są ich pełne. Translator formuł współdzielonych HotXLS rozpoznaje całą rodzinę A1, zanim zdecyduje, co przesunąć. Odwołania do zewnętrznych skoroszytów, takie jak [Book.xlsx]Sheet1!A1, oraz odwołania 3D, takie jak Sheet1:Sheet3!A1, zachowują swój prefiks nienaruszony, podczas gdy przesuwa się końcowe odwołanie do komórki. Cytowane nazwy arkuszy przetrwają, w tym paskudny przypadek, gdy arkusz nazywa się dosłownie A1, więc 'A1'!A1 przesuwa tylko część po wykrzykniku. Całokolumnowe A:A przesuwa swój wymiar kolumny i nic więcej; całowierszowe 1:1 przesuwa swój wymiar wiersza i nic więcej; $A:$A w ogóle się nie przesuwa. Strukturalne odwołania do tabeli, takie jak Table[A1], pozostają nietknięte, ponieważ część w nawiasach kwadratowych to nazwa kolumny, nie współrzędna

// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1       : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3       : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the structured reference Table[A1]

Nazwy funkcji to tutaj cicha pułapka. Skaner tokenów, który chwyta litery po których następują cyfry, chętnie przepisze LOG10 na LOG11 jeden wiersz niżej. HotXLS wymaga granicy odwołania przed kandydującym tokenem i po nim, więc identyfikator, który kontynuuje w literę, cyfrę, podkreślenie, kropkę lub otwierający nawias, nie jest odwołaniem do komórki. Jeśli pracujesz w drugiej notacji, ten sam problem granicy objawia się inaczej, a artykuł o notacji R1C1 omawia, gdzie oba modele się rozchodzą

Dlaczego samozamykający się element f pochłania kolejną wartość?

Ponieważ element samozamykający się nie generuje zdarzenia końca elementu. To najbardziej kosztowny błąd w całej tej funkcjonalności i nie jest specyficzny dla żadnego konkretnego parsera XML. W TXMLReader <f t="shared" si="4"/> generuje dokładnie jedno zdarzenie Element z IsEmptyElement ustawionym na True, i nigdy nie generuje pasującego EndElement. Parser, który zamyka swój stan przechwytujący formułę tylko przy EndElement, pozostaje więc wewnątrz formuły, a kolejny tekst, który widzi, czyli zbuforowany wynik wewnątrz <v>, zostaje dopisany do bufora formuły. Co gorsza, ten stan przetrwa granicę komórki, więc kolejna komórka posiadająca prawdziwe <f> ma swój tekst formuły wchłonięty przez poprzednią komórkę. Rozwiązaniem jest zakończenie stanu formuły już przy samym zdarzeniu Element, ilekroć IsEmptyElement jest True, i przeprowadzenie tam całego rozwiązywania kontynuacji, zamiast czekać. Oznacza to odczytanie t, si, ref, aca i ca z atrybutów, zastosowanie rozwinięcia współdzielonego, zapisanie atrybutów przeliczania na komórce i wyczyszczenie stanu współdzielonego, wszystko wewnątrz gałęzi obsługującej pusty element. Zwróć uwagę, że format dopuszcza obie pisownie, <f t="shared" si="4"/> i <f t="shared" si="4"></f>, a ta druga faktycznie generuje EndElement. Poprawny czytnik musi obsługiwać tę parę identycznie, dlatego HotXLS obejmuje obie pisownie w tym samym pliku regresyjnym

Rzadkie, nieuporządkowane wartości si i kolejka oczekujących

Atrybut si to liczba całkowita bez znaku dostarczana przez plik, nie pozycja tablicy, którą kontrolujesz. Nic w schemacie nie wymaga, by indeksy współdzielone były gęste, zaczynały się od zera ani pojawiały w kolejności rosnącej, i nic nie powstrzymuje wrogiego lub po prostu dziwnego pliku przed użyciem si="4294967290" na pierwszej komórce. Sizing tablicy wyszukiwania na podstawie największego zaobserwowanego si jest więc prymitywem wyczerpania pamięci, nie optymalizacją. HotXLS utrzymuje ścieżkę otwierania skoroszytu na posortowanej rzadkiej tablicy zamiast tego: grupy współdzielone są rejestrowane pod swoim kluczem całkowitym w posortowanej TStringList, co czyni wyszukiwanie przeszukiwaniem binarnym po tylu grupach, ile faktycznie istnieje, bez żadnego związku z liczbowym rozmiarem indeksów. Kolejność to druga połowa problemu. Wzorzec zwykle poprzedza swoje kontynuacje w kolejności dokumentu, ale to konwencja, nie reguła, więc każda kontynuacja, która nie może rozwiązać swojego si w momencie parsowania, trafia do kolejki oczekujących. Gdy arkusz się kończy, kolejka jest odtwarzana wobec już kompletnej tablicy, a spóźnione wzorce rozwiązują swoje sieroty. Komórki, które nigdy nie znajdą wzorca, zachowują pustą formułę, co jest uczciwym wynikiem dla pliku odwołującego się do grupy, której nigdy nie zdefiniował

Rozwijanie formuł współdzielonych bez wczytywania skoroszytu

Czytniki strumieniowe stają przed tym samym wymogiem przy znacznie ciaśniejszym budżecie pamięci i rozwiązują go tablicą lokalną dla arkusza. TXLSDirectReader i TXLSRowCursor oba rozwijają kontynuacje w kompletne formuły per komórka, zachowując przy tym swoje ograniczone zużycie pamięci i zachowanie projekcji, więc przebieg tylko-do-przodu po arkuszu 300 MB wciąż podaje prawdziwy tekst formuły

var
  Reader: TXLSDirectReader;
  Cursor: TXLSRowCursor;
begin
  // Projection: only rows 2..3, only column A. The master lives in row 1,
  // outside the projection, and is still parsed so the followers resolve
  Reader:= TXLSDirectReader.Create;
  try
    Reader.FirstRow:= 2;
    Reader.LastRow:= 3;
    Reader.IncludeColumn(1);
    Reader.OnCell:= HandleCell;   // Cell.Formula is fully expanded here
    Reader.ReadFile(FileName);
  finally
    Reader.Free;
  end;

  // Forward-only row traversal, same expansion
  Cursor:= TXLSRowCursor.Create;
  try
    Cursor.Open(FileName);
    if Cursor.FindFirst then
      repeat
        if Cursor.CellCount > 0 then
          WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
      until not Cursor.FindNext;
  finally
    Cursor.Free;
  end;
end;

Z tego projektu wynikają dwa ograniczenia. Po pierwsze, projekcja nigdy nie może pominąć wzorca. Filtr wiersza ustawiony przez FirstRow i LastRow, lub filtr kolumny zbudowany przez IncludeColumn, może pominąć emisję komórki wzorcowej do twojego callbacku, ale parser musi wciąż zapisać jej si, współrzędne kotwicy, stosowalny zakres i tekst formuły, inaczej każda kontynuacja wewnątrz projekcji rozwiąże się do niczego. Tylko praca po stronie kontynuacji, przesunięcie i dekodowanie wartości, jest bezpieczna do pominięcia. Po drugie, tablica jest lokalna dla arkusza, a jej cykl życia musi być zarządzany jawnie: TXLSRowCursor trzyma jedną instancję na czas przebiegu po arkuszu i czyści ją przy restarcie, zmianie arkusza, końcu pliku, wyjątku i zamknięciu, więc grupa zdefiniowana na arkuszu pierwszym nigdy nie może przeciec do arkusza drugiego. Ponieważ ścieżka strumieniowa to gorąca pętla, używa otwartego adresowania hasha całkowitoliczbowego zamiast posortowanej tablicy ciągów znaków, co unika konwersji całkowita-na-ciąg per komórka

Co dzieje się przy zapisie i gdzie leżą granice

Gdy kontynuacja została już rozwinięta, staje się zwykłą formułą, a HotXLS zapisuje ją z powrotem jako niezależny element <f> bez t="shared" i bez si. Round trip jest stabilny, a zbuforowane wyniki <v> przetrwają, ale wynik jest większy niż wejście dla mocno współdzielonego arkusza, a grupowanie stworzone przez Excela nie jest odtwarzane przy zapisie. Jeśli wierność bajtowa grup współdzielonych ma dla ciebie większe znaczenie niż posiadanie prawdziwego tekstu formuły w każdej komórce, to jest kompromis, który akceptujesz. Strona XLS jest przy okazji inna: rekord SHRFMLA BIFF8 ma własne kodowanie i własny zapisywacz, z przełącznikiem grupy współdzielonej na poziomie skoroszytu

Dwie powiązane rzeczy jawnie nie są formułami współdzielonymi, mimo że współdzielą element <f>. Klasyczne formuły tablicowe CSE używają t="array" z ref pokrywającym zakotwiczony zakres, a tablice dynamiczne używają tej samej pisowni t="array", ale są identyfikowane atrybutem cm, który łańcuchuje przez cellMetadata do rekordu XLDAPR. Traktowanie komórki rozlewu tablicy dynamicznej jako kontynuacji współdzielonej lub CSE to prawdziwy błąd poprawności, a to rozróżnienie jest omówione w artykule o formułach tablic dynamicznych i rozlewu. Czytaj te trzy przypadki jako trzy parsery, które akurat współdzielą nazwę znacznika, a kod pozostaje uczciwy

Rozwinięcie formuł współdzielonych, czytniki strumieniowe i translator odwołań opisane tutaj są dostarczane w ramach komponentu Excel HotXLS dla Delphi i C++Buildera; strona produktu niesie pełne odniesienie API formuł i odczytu bezpośredniego, w tym właściwości projekcji użyte powyżej