HotXLS wylicza teraz strukturalne odwołania tabel, więc =SUM(Table1[Amount]) tworzy liczbę zamiast być pomijane. Resolwer obsługuje Table[Column], Table[[Column]], zakresy kolumn takie jak Table[[Q1]:[Q4]], oraz specyfikatory elementów [#Data], [#All], [#Headers] i [#Totals], rozwiązując każdy z nich względem modelu tabel skoroszytu już w czasie parsowania, podczas gdy oryginalny tekst formuły odtwarza się dosłownie po zapisie
Jedna forma jest celowo nieobecna, i to ta, na którą ludzie trafiają najpierw. Skrót bieżącego wiersza [@Column] nie jest obsługiwany, z przyczyny strukturalnej wartej zrozumienia zamiast ślepego obchodzenia
Dlaczego odwołanie strukturalne to nie po prostu zakres z przyjazną nazwą?
Ponieważ nazwa zdefiniowana zamraża adres, a odwołanie do tabeli nie. Napisz DataBlock jako nazwę wskazującą na Sheet1!$A$2:$D$100, a pozostanie tym prostokątem, dopóki coś go nie nadpisze. Napisz Sales[Amount], a oznacza to „kolumnę Amount tabeli Sales”, niezależnie od tego, jaki będzie zasięg tej tabeli w chwili wyliczania formuły. Dodaj dwadzieścia wierszy do tabeli, a suma je obejmie; nie ma żadnego odwołania do skorygowania, ponieważ w formule nigdy nie było adresu
Ta symboliczna właściwość jest dokładnie tym, co sprawia, że odwołania nie da się rozwiązać przez podstawienie tekstu. Resolwer musi znaleźć tabelę po nazwie w skoroszycie, wyszukać kolumnę po tekście nagłówka, zdecydować, które wiersze obejmuje żądany specyfikator elementu, i wyprodukować konkretny prostokąt. HotXLS robi to podczas kompilacji formuły przez model tabel, dlatego formuła napisana, zanim tabela urosła, nadal wylicza się względem bieżącego zasięgu tabeli
Gramatyka, którą rozwiązuje HotXLS
Obsługiwana gramatyka specyfikatora obejmuje pojedynczy prostokątny wynik i warto ją precyzyjnie opisać, ponieważ dokumentacja Excela przedstawia znacznie szerszy zestaw, niż implementuje większość silników. HotXLS akceptuje [Col] i wariant w nawiasach [[Col]], gołe specyfikatory elementów [#Data], [#All], [#Headers] i [#Totals], formę łączoną [[#Data],[Col]], zakres wewnątrz specyfikatora elementu jako [[#Data],[Col1]:[Col2]], oraz zwykły zakres [Col1]:[Col2]
To, co daje ten zestaw, to każdy kształt odwołania, który tworzy jeden ciągły blok: kolumnę, ciąg sąsiadujących kolumn, wycinek samego korpusu albo wycinek z nagłówkiem. Unie nieprzylegające i wyniki wieloobszarowe leżą poza nim. Gdy odwołania nie da się rozwiązać, formuła zachowuje dotychczasowe zachowanie pomijania bez wartości, zamiast podstawiać domysł, więc nierozwiązywalne odwołanie nigdy nie staje się wiarygodnie wyglądającą błędną liczbą
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[27, 4].Formula := 'SUM(SalesTable[Amount])';
Sheet.Cells[28, 4].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
Sheet.Cells[29, 4].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
Sheet.Cells[30, 4].Formula := 'ROWS(SalesTable[#All])';
Book.Recalculate;
Book.SaveAs('sales.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;Dlaczego forma bieżącego wiersza jest celowo wykluczona?
[@Column] i [#This Row] oznaczają „komórkę tej kolumny w wierszu, w którym żyje ta formuła”. Wartość zależy więc od pozycji wyliczanej komórki, nie tylko od tabeli. To inny rodzaj odwołania: nie prostokąt, który kompilator może rozwiązać raz, lecz rozwiązanie per komórka, które trzeba powtórzyć dla każdego wiersza, jaki zajmuje formuła
HotXLS zwraca False z resolwera zakresu tabeli dla tych form, co kieruje je na ścieżkę pomijania bez wartości. Tekst formuły jest zachowany i zapisywany bez zmian, więc skoroszyt używający [@Amount] otwiera się poprawnie w Excelu po przejściu przez twoją aplikację; brakuje jedynie wartości wyliczonej przez HotXLS. Mając wybór między brakiem wartości a wartością wyliczoną względem złego wiersza, brak jest tym, który można wykryć
Praktyczne obejście jest mechaniczne: w skoroszycie, który sam generujesz, zapisz równoważne odwołanie względne w stylu A1, co i tak Excel wewnętrznie przechowuje dla znacznej części logiki zakresowej tabel. W skoroszycie, który jedynie przetwarzasz, zostaw formułę w spokoju i odczytaj buforowaną wartość, którą Excel już zapisał, co zwykle jest tym, czego chce potok wczytaj-i-zaraportuj
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];
Table := Sheet.Tables.FindByName('SalesTable');
if Table <> nil then
begin
// Wyszukiwanie w stylu recordsetu po korpusie tabeli, wynik wiersza liczony od 1
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value));
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
end;
finally
Book.Free;
end;
end;Co się dzieje, gdy tabela zmienia kształt
Odwołania strukturalne są unieważniane, a nie po cichu przekierowywane, gdy rzecz, którą nazywają, znika. Usuń kolumnę, a formuły odwołujące się do tej kolumny są unieważniane tak samo, jak unieważnia je Excel; usuń albo zmień nazwę tabeli, a odwołania do niej są obsługiwane tak samo. To poprawne zachowanie i odzwierciedla zwykłe dostosowywanie odwołań, opisane w dostosowywaniu odwołań formuł przy wstawianiu i usuwaniu, gdzie zadaniem silnika jest utrzymanie formuł uczciwymi, a nie utrzymanie ich pozornej ważności
Wzrost liczby wierszy to przeciwny przypadek i w ogóle nie wymaga dostosowania. Ponieważ odwołanie nazywa tabelę, a nie prostokąt, dopisywanie wierszy wewnątrz zasięgu tabeli poszerza to, co obejmuje [#Data], bez dotykania choćby jednej formuły. To właśnie ta właściwość sprawia, że tabele warto stosować w szablonie raportu: wiersz sum wciąż sumuje wszystko, co wyprodukował import, niezależnie od tego, ile ostatecznie było wierszy
Dyscyplina wiernego odtwarzania
HotXLS zachowuje oryginalny tekst formuły. Skoroszyt wczytany z SUM(SalesTable[Amount]) jest zapisywany z SUM(SalesTable[Amount]), a nie z rozwiązanym SUM(D2:D25). Ma to większe znaczenie, niż mogłoby się wydawać: użytkownik, który otwiera twoje wyjście w Excelu, spodziewa się zobaczyć formułę, którą napisał, a rozwiązany adres po cichu zamieniłby samoutrzymujący się model w kruchy, który przestaje obejmować nowe wiersze
Dwie powiązane funkcje dopełniają obraz. Same definicje tabel, łącznie z tabelami bez nagłówków i komentarzami per tabela, odtwarzają się po zapisie przez model tabel opisany w walidacji danych, AutoFilter i tabelach Excela. A gdy wiele komórek dzieli jeden wzorzec, XLSX przechowuje je raz jako formułę współdzieloną, która jest rozwijana i emitowana ponownie zgodnie z opisem w rozwijaniu si formuły współdzielonej XLSX. Odwołania strukturalne wewnątrz formuł współdzielonych przechodzą przez obie ścieżki, więc obie muszą działać poprawnie, i działają
HotXLS odczytuje i zapisuje XLS, XLSX i ODS z poziomu Delphi i C++Builder bez instalacji Excela i bez automatyzacji Office, wyliczając formuły we własnym silniku. Model tabel, silnik formuł i API przeliczania są udokumentowane na stronie komponentu arkusza kalkulacyjnego HotXLS dla Delphi