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