Artykuł techniczny

Strukturalne odwołania tabel Excela w Delphi z HotXLS

Tabela Excela, której kolumna Amount trzyma =[@Qty]*[@Price], nie zapisuje [@Qty] nigdzie w zapisanym pliku. Rozpakuj skoroszyt, a element <f> w XML arkusza pokaże, co Excel faktycznie pisze: długą, kwalifikowaną tabelą pisownię Lines[[#This Row],[Qty]]*Lines[[#This Row],[Price]], bo gramatyka formuł w ECMA-376 Part 1, §18.17 ma słowo kluczowe #This Row i nie ma @. Komórka podsumowania niżej trzyma SUM(Lines[Amount]). Żaden z tych łańcuchów nie zawiera ani jednego adresu komórki, więc silnik, który parsuje wyłącznie referencje A1, nie ma czego liczyć, dopóki nie wie, gdzie siedzi tabela, który wiersz niesie nagłówki i na którym wierszu mieszka sama formuła

Ostatnia z tych informacji jest najbardziej kłopotliwa. Referencję kolumny można zamienić w prostokąt raz na tabelę. Referencji bieżącego wiersza nie da się — znaczy inną komórkę dla każdego wiersza, w który zostanie skopiowana

Dlaczego odwołanie strukturalne to nie po prostu zakres z przyjazną nazwą?

Nazwa zdefiniowana zamraża adres, referencja tabeli nie. Skieruj nazwę DataBlock na Sheet1!$A$2:$D$100, a pozostanie tym prostokątem, dopóki coś jej nie przepisze. Sales[Amount] znaczy „kolumnę Amount tabeli Sales" — cokolwiek akurat jest zasięgiem tabeli w chwili ewaluacji formuły. Dodaj dwadzieścia wierszy do tabeli, a suma je obejmie; nie ma referencji do poprawienia, bo formuła nigdy nie trzymała adresu

HotXLS rozwiązuje te referencje za każdym razem, gdy kompiluje formułę do ewaluacji. TXLSXWorkbook znajduje tabelę po nazwie w modelu tabel skoroszytu (TXLSXTable.Name albo DisplayName, porównywane bez rozróżniania wielkości liter), dopasowuje kolumnę do tekstu nagłówka zapisanego w TXLSXTable.Columns, nakłada specyfikator elementu, by wybrać pas wierszy, i podaje kompilatorowi absolutny zakres, jak 'Sales'!$D$2:$D$25. Przepisany tekst istnieje tylko na czas tej kompilacji. Komórka zachowuje formułę dokładnie taką, jaka została zapisana, a ponieważ wyszukiwanie biegnie względem TXLSXTable.Range w stanie z chwili przeliczania, formuła napisana zanim tabela urosła nadal ewaluuje względem większej tabeli

Diagram HotXLS rozwiązującego odniesienie strukturalne SalesTable[Amount] w Delphi przez znalezienie tabeli po nazwie, dopasowanie nagłówka kolumny Amount i zastosowanie specyfikatora #Data, by uzyskać konkretny zakres
HotXLS kompiluje referencje Table[Column] względem modelu tabeli w czasie parsowania i zamienia je na bieżący konkretny zakres, więc formuła dalej podąża za tabelą, gdy ta rośnie

Gramatyka prostokątów: kolumny, zakresy i specyfikatory elementów

Każda forma obejmująca całą tabelę, którą HotXLS ewaluuje, produkuje jeden ciągły blok. Przyjęte pisownie to [Col] i jej bliźniak w nawiasach [[Col]], gołe specyfikatory elementów [#Data], [#All], [#Headers] i [#Totals], forma złożona [[#Data],[Col]], zakres wewnątrz specyfikatora elementu, jak [[#Data],[Col1]:[Col2]], i zwykły zakres [[Col1]:[Col2]]. Zakres można zapisać od prawej do lewej; [[Q2]:[Q1]] rozwiązuje się do tych samych dwóch kolumn co [[Q1]:[Q2]]

Pas wierszy, który wybiera każdy specyfikator, podąża za dwiema flagami tabeli. TXLSXTable.HasHeaderRow rozstrzyga, czy pierwszy wiersz Range to nagłówek, a TXLSXTable.TotalsRowShown — czy ostatni wiersz to wiersz sum. Zwykła referencja kolumny i [#Data] obejmują wiersze między nimi. [#All] biera pełny Range, z nagłówkiem i sumami. Zażądaj [#Headers] na tabeli bez wiersza nagłówków, [#Totals] na tabeli bez wiersza sum albo kolumny tabeli, której ciało nie ma wierszy, a referencja staje się #REF!. To zgadza się z tym, co Excel pokazuje przy tej samej prośbie, i trzyma niemożliwą referencję z dala od cichego zamieniania się w pusty zakres sumujący do zera

Nazwy kolumn zawierające nawias kwadratowy, hasz, małpkę albo apostrof idą za regułą ucieczek Excela: każdy taki znak jest poprzedzony apostrofem wewnątrz referencji, więc nagłówek zwany Unit [USD] zapisuje się jako Sales[Unit '[USD']]. Formy pojedynczej kolumny i bieżącego wiersza dekodują te ucieczki przed dopasowaniem. Zakresy i złożone formy [#Data],[Col] rozwiązują się względem tabel na arkuszu samej formuły, więc arkusz podsumowujący potrzebujący zakresu po tabeli z innego arkusza lepiej obsłuży zwykła referencja kolumny na każdą kolumnę

Mapa gramatyki odniesień strukturalnych akceptowanej przez HotXLS w Delphi, pokazująca, które regiony tabeli pokrywają formy #Data, #Headers, #Totals i #All oraz które formy bieżącego wiersza wypadają poza
Każda zaakceptowana forma pokrywa jeden ciągły pas tabeli, a warianty w nawiasach kwadratowych rozwiązują się dokładnie jak swoje gołe pisownie

Przykład poniżej buduje tabelę sprzedaży z 24 wierszami i kładzie pięć formuł podsumowujących obok niej w F2 do F6, na tym samym arkuszu co tabela. Wiersz 1 to wiersz nagłówkowy, wiersze 2 do 25 to ciało, a wiersza sum nie ma, więc SalesTable[#All] rozciąga się na 25 wierszy, podczas gdy SalesTable[Amount] na 24

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cols: TStringList;
begin
  Book := TXLSXWorkbook.Create;
  Cols := TStringList.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Cols.Add('Region');
    Cols.Add('Q1');
    Cols.Add('Q2');
    Cols.Add('Amount');
    Sheet.Tables.Add('SalesTable', 'A1:D25', Cols);
    // ... zapisz wiersz nagłówka i 24 wiersze danych ...

    Sheet.Cells[2, 6].Formula := 'SUM(SalesTable[Amount])';
    Sheet.Cells[3, 6].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
    Sheet.Cells[4, 6].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
    Sheet.Cells[5, 6].Formula := 'ROWS(SalesTable[#All])';
    Sheet.Cells[6, 6].Formula := 'COUNTA(SalesTable[#Headers])';

    Book.Recalculate;
    Book.SaveAs('sales.xlsx');
  finally
    Cols.Free;
    Book.Free;
  end;
end;

Oczekiwane wartości dla tego układu: F5 to 25, F6 to 4 — po jednym policzeniu na komórkę nagłówka. F2 do F4 zależą od zapisanych danych

Skąd [@Column] wie, który wiersz czytać?

Referencja bieżącego wiersza bierze wiersz z komórki, która jest właśnie ewaluowana. Gdy Recalculate dochodzi do formuły z Lines[@Qty] w wierszu 7, HotXLS rozwiązuje ją do kolumny Qty wiersza 7; ten sam tekst formuły w wierszu 8 czyta wiersz 8. Długa pisownia, którą Excel zapisuje na dysk, Lines[[#This Row],[Qty]], jest traktowana jako ta sama referencja, i Lines[@[Unit Price]] dla nagłówka ze spacją również

Forma niekwalifikowana [@Qty], bez nazwy tabeli z przodu, potrzebuje jeszcze jednego faktu: do której tabeli należy. HotXLS odpowiada na to pozycją. Jeśli ewaluowana komórka siedzi wewnątrz wierszy ciała i rozpiętości kolumn tabeli na tym samym arkuszu, ta tabela podaje kolumnę, a wewnątrz takiej tabeli [@[Q1]:[Q2]] staje się zakresem jednowierszowym przez te kolumny. Gdziekolwiek indziej referencja zostaje nierozwiązana. Nazwa zdefiniowana, której formuła używa referencji bieżącego wiersza, bierze wiersz z komórki odwołującej się do nazwy — to samo zachowanie, które formuła dostałaby inline

Trzymaj formuły bieżącego wiersza na własnych wierszach ciała tabeli i kwalifikuj je nazwą tabeli, gdy generuje je kod. Kwalifikowane referencje przeżywają przemieszczanie, czytają się tak, jak Excel je zapisuje, a formuła na własnych wierszach tabeli to jedyny przypadek, w którym Excel i jakikolwiek inny konsument pliku zgadzają się, co znaczy @

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cols: TStringList;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  Cols := TStringList.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Cols.Add('Item');
    Cols.Add('Qty');
    Cols.Add('Price');
    Cols.Add('Amount');
    Sheet.Tables.Add('Lines', 'A1:D13', Cols);
    // ... zapisz wiersz nagłówka i pozycje, ilości oraz ceny w wierszach 2..13 ...

    // Jeden tekst formuły, dwanaście różnych wyników: każda komórka czyta własny wiersz
    for Row := 2 to 13 do
      Sheet.Cells[Row, 4].Formula := 'Lines[@Qty]*Lines[@Price]';

    // Kontrola krzyżowa pod tabelą: suma kolumny kontra przeliczony iloczyn
    Sheet.Cells[15, 4].Formula :=
      'SUM(Lines[Amount])-SUMPRODUCT(Lines[Qty],Lines[Price])';

    Book.Recalculate;
    // Oczekiwane: D15 ewaluuje do 0, gdy każda komórka Amount zgadza się z Qty * Price
    Book.SaveAs('orders.xlsx');
  finally
    Cols.Free;
    Book.Free;
  end;
end;

Kontrola w D15 jest tania i warto zostawić ją w generowanym raporcie na czas budowania. Formuła per wiersz, która podchwyciła zły wiersz, rozminęłaby obie sumy, a różnica wychodzi jako niezerowa liczba w komórce, którą każdy może przeczytać

Schemat przepływu HotXLS odrzucającego formę bieżącego wiersza [@Amount] z zachowaniem pomijania bez wartości w Delphi, przy czym tekst formuły jest zachowywany, więc Excel przelicza każdy wiersz po rundzie
Niewspierane referencje bieżącego wiersza zawodzą rozwiązanie bez zapisania wartości, a nietknięty tekst pozwala Excelowi ocenić je poprawnie, gdy skoroszyt zostanie zapisany i otwarty ponownie

Co się dzieje, gdy tabela zmienia kształt

Wzrost to łatwy przypadek, o ile definicja tabeli faktycznie rośnie. Zapisanie wartości w wierszu 26 pod tabelą, której Range to A1:D25, nie czyni wiersza 26 częścią tabeli. Auto-rozszerzanie to funkcja edytorska Excela odpalana przez kogoś piszącego obok tabeli, i zapis komórek przez HotXLS jej nie odpala. Ustaw TXLSXTable.Range na nowy zasięg po imporcie albo wstaw wiersze wewnątrz tabeli przez TXLSXWorksheet.InsertRows, które przesuwa Tables[i].Range razem z każdą inną referencją na arkuszu. Tak czy owak [#Data] się poszerza i żadna formuła się nie zmienia

Kurczenie się to miejsce, gdzie referencje mogą pęknąć, i HotXLS idzie za regułą Excela. DeleteCols po kolumnie tabeli przepisuje każdą strukturalną referencję nazywającą tę kolumnę na #REF! — w formułach komórek, formatach warunkowych, regułach walidacji danych, seriach wykresów i nazwach zdefiniowanych. DeleteRows usuwające wiersz nagłówków usuwa tabelę i unieważnia jej referencje tak samo, a usuwające wiersz sum czyści TotalsRowShown, po czym [#Totals] nie ma już wiersza do wskazania. Zwykłe referencje A1 przechodzą tę samą korektę, opisaną w artykule korekta referencji formuł przy wstawianiu i usuwaniu

TXLSXTables.DeleteByName i przemianowanie przez TXLSXTable.Name działają wyłącznie na modelu tabel. Formuły wspominające starą nazwę zachowują swój tekst, więc przemianuj te formuły w tym samym przebiegu, gdy twój kod przemianowuje tabelę

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Table: TXLSXTable;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('sales.xlsx') <> 1 then Exit;
    Sheet := Book.Sheets[1];  // arkusze skoroszytu liczone od 1

    Table := Sheet.Tables.FindByName('SalesTable');
    if Table = nil then Exit;

    // ... zapisz 20 wierszy importu do A26:D45 ...
    Table.Range := 'A1:D45';  // ciało biegnie teraz od wiersza 2 do wiersza 45

    Book.Recalculate;  // SUM(SalesTable[Amount]) w F2 obejmuje teraz 44 wiersze

    // Wyszukiwanie w stylu recordsetu po ciele tabeli, wynik wiersza od 1, 0 = brak
    Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
    while Row > 0 do
    begin
      Log(VarToStr(Sheet.Cells[Row, 4].Value));  // Log to twoja własna procedura
      Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
    end;

    Book.SaveAs('sales.xlsx');
  finally
    Book.Free;
  end;
end;

FindFirst i FindNext porównują łańcuchy bez rozróżniania wielkości liter, a liczby po wartości, i skanują wyłącznie wiersze ciała, więc tekst nagłówka Region nigdy nie wraca jako dopasowanie

Dyscyplina wiernego odtwarzania

Skoroszyt wczytany z SUM(SalesTable[Amount]) jest zapisywany z SUM(SalesTable[Amount]), a komórka trzymająca Lines[[#This Row],[Qty]] wychodzi z tym tekstem nietkniętym. Rozwiązany adres nigdy nie zastępuje tego, co napisał użytkownik. Gdyby HotXLS zapisywał SUM($D$2:$D$25), plik otwierałby się w Excelu wyglądając dobrze, po czym przestał obejmować nowe wiersze przy pierwszym rozszerzeniu tabeli — najwolniej diagnozowalny rodzaj awarii

Definicje tabel same w sobie, w tym tabele bez nagłówka i komentarze per tabela, przechodzą podróż tam i z powrotem przez model tabel opisany w artykule walidacja danych, AutoFilter i tabele Excela. Gdy wiele komórek dzieli jeden wzorzec formuły, XLSX może zapisać go raz jako formułę współdzieloną, a rozwijanie tych komórek pokrywa artykuł rozwijanie formuły współdzielonej si. Kalkulowana kolumna zapisana przez Excela często przychodzi właśnie tak, więc strukturalna referencja w ciele tabeli przechodzi obie ścieżki, zanim zostanie ewaluowana: formuła współdzielona rozwija się najpierw do tekstu per komórka, potem każda komórka rozwiązuje własny wiersz

Model tabel, silnik Recalculate i wywołania wstawiania i usuwania w arkuszu użyte tutaj są częścią HotXLS, który czyta i pisze XLS, XLSX i ODS z Delphi i C++Builder bez instalacji Excela. Pełna referencja API mieszka na stronie komponentu arkuszowego HotXLS Delphi