Jeśli SUBTOTAL(109, ...) i SUBTOTAL(9, ...) zwracają tę samą liczbę w skoroszycie zawierającym ukryte wiersze, jedna z tych dwóch wartości jest błędna. HotXLS, natywny komponent arkusza Excel dla Delphi i C++Buildera, zachowywał się dokładnie w ten sposób aż do wersji 2.197.0, ponieważ jego silnik obliczeniowy nie miał żadnego sposobu, by zapytać arkusz, czy dany wiersz jest ukryty
Ten objaw rzadko trafia jako zgłoszenie błędu dotyczące kodów formuł. Pojawia się jako niezgodność: zadanie wsadowe na serwerze oblicza sumę, użytkownik otwiera ten sam plik w Excelu z zastosowanym filtrem, a obie liczby różnią się o to, co akurat sumowały odfiltrowane wiersze. Nikt nie podejrzewa funkcji agregującej, bo ciąg formuły w komórce jest identyczny w obu miejscach. Różnica tkwi wyłącznie w tym, co ewaluator mógł zobaczyć
Dlaczego SUBTOTAL 109 obejmuje ukryte wiersze?
Ponieważ w większości projektów silników warstwa ewaluująca formułę nigdy nie dowiaduje się o widoczności wiersza. HotXLS był tu podręcznikowym przykładem: silnik obliczeniowy w lxCalc.pas docierał do wartości komórek przez pojedynczy callback TXLSGetValue, który zwraca wartość dla trójki (arkusz, wiersz, kolumna) i nic więcej. Widoczność jest atrybutem prezentacyjnym zapisanym w rekordzie wiersza, a żadna część tego rekordu nie przemieszczała się dalej wzdłuż łańcucha wywołań. Silnik miał więc jedną ścieżkę agregacji, a obie połowy tabeli numerów funkcji SUBTOTAL rozwiązywały się do niej. To nie jest defekt klasy błędu zaokrąglenia — to cały powód, dla którego druga połowa tej tabeli w ogóle istnieje. ECMA-376 Part 1, opublikowany jako ISO/IEC 29500-1, definiuje SUBTOTAL w definicjach funkcji formuł (§18.17.7) z pierwszym argumentem, który wybiera zarówno wewnętrzną agregację, jak i politykę ukrytych wierszy. Kody od 1 do 11 mapują się na AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR i VARP, uwzględniając przy tym wartości w ręcznie ukrytych wierszach. Kody od 101 do 111 wybierają te same jedenaście agregacji, ale je wykluczają. Użytkownik, który wpisuje 109 zamiast 9, świadomie wypowiada się na temat ukrytych danych, a silnik, który zaciera to rozróżnienie, po cichu unieważnia tę deklarację
Na co mapują się numery funkcji wewnątrz silnika
HotXLS rozwiązuje pierwszy argument SUBTOTAL w CalcSubtotalFunc, która normalizuje kody od 101 do 111 do tych samych wewnętrznych identyfikatorów funkcji co kody od 1 do 11, a następnie przekazuje sterowanie do samej agregacji. Większość tej rodziny przepływa przez przyrostowy akumulator ExcelSum, obsługujący SUM, COUNT, COUNTA, MIN, MAX i AVERAGE. Pięć z nich tego nie może: STDEV, VAR, STDEVP, VARP i PRODUCT wymagają przebiegu w formie zamkniętej po danych, więc CalcSubtotalFunc kieruje kody wewnętrzne 12, 46, 193, 194 i 183 do osobnego reduktora, SubtotalReduceVariance. Ten podział jest pierwszą rzeczą wartą zmapowania, zanim czegokolwiek dotkniemy, bo dwie niezależne ścieżki agregacji oznaczają dwie niezależne pętle przechodzenia po komórkach, a poprawka zastosowana tylko do jednej z nich daje najgorszy możliwy efekt: SUBTOTAL(109, ...) respektuje filtr, podczas gdy SUBTOTAL(107, ...) na tym samym zakresie już nie. Policzenie pętli w HotXLS dało sześć, gdy uwzględniono AGGREGATE, rozłożonych między ewaluację zakresu, zwykłe zbieranie zakresu i trzy osobne reduktory
Dlaczego pole robocze zamiast sześciu nowych sygnatur?
Ponieważ przeciągnięcie nowego parametru przez sześć funkcji przechodzących po komórkach, plus wszystko, co je wywołuje, to szeroka zmiana w gorącej ścieżce kodu dla jednego booleana. HotXLS miał już precedens dla alternatywy: pole tymczasowe na kalkulatorze, w tym samym duchu co pole robocze, którego GetRangeInfo używa do zapisania momentu, gdy odwołanie 3D rozwiązało się do zewnętrznego skoroszytu. Wersja 2.197.0 dodała drugie takie pole. Silnik zyskał typ callbacku, TXLSIsRowHidden, zadeklarowany jako funkcja (SheetIndex, row) zwracająca Boolean, przechowywaną w FIsRowHidden, plus tymczasową flagę FIgnoreHiddenRows. Flaga jest uzbrajana na wejściu do CalcSubtotalFunc, gdy kod funkcji mieści się w zakresie od 101 do 111, oraz na wejściu do CalcAggregateFunc dla kodów opcji AGGREGATE wybierających wykluczenie ukrytych wierszy. Każda pętla przechodzenia po komórkach sprawdza ją następnie i pomija jeden wiersz, gdy jest ustawiona, dodając po jednej linii na każdą
// The shape repeated in all six cell-walk loops
for sh := s1 to s2 do
for rr := r1 to r2 do
begin
if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
Continue;
for cc := c1 to c2 do
begin
// ... fold Cells[rr, cc] into the accumulator ...
end;
end;Dwa szczegóły w kodzie uzbrajania decydują o poprawności całego mechanizmu. Flaga jest zapisywana i przywracana, a nie po prostu ustawiana i czyszczona, ponieważ argument SUBTOTAL może zawierać wyrażenie, które uruchamia własną ewaluację, podczas gdy zewnętrzna agregacja wciąż jest na stosie, a ta zagnieżdżona praca nie może odziedziczyć ani zniszczyć zewnętrznej bramki. A przywrócenie znajduje się w bloku finally, ponieważ CalcSubtotalFunc ma kilka wczesnych wyjść dla kodów błędów; flaga pozostawiona uzbrojona po zwrocie błędu po cichu zepsułaby kolejną, niepowiązaną formułę w kolejności przeliczania
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;Test Assigned jest tym, co utrzymuje zgodność zmiany wstecz. HotXLS rozszerzył konstruktor kalkulatora o trzeci parametr domyślnie nil, więc każdy kod budujący TXLSCalculator starym wywołaniem dwuargumentowym nadal się kompiluje i nadal otrzymuje odziedziczone zachowanie uwzględniające ukryte wiersze. Kształt żadnej istniejącej części API się nie zmienił
Skąd właściwie bierze się bit ukrycia wiersza?
Z arkusza, poprzez dwa różne źródła, ponieważ HotXLS obsługuje dwa silniki skoroszytów. Strona legacy BIFF odpowiada z TXLSRowInfoList.GetHidden, osiąganego przez TXLSWorkbook.GetRowHidden. Strona OOXML odpowiada z TXLSXWorksheet.GetRowHidden, osiąganego przez TXLSXWorkbook.GetCalcRowHidden. Oba są podłączone do kalkulatora w momencie konstrukcji, obok odzwierciedlanego przez nie callbacku wartości komórki. Konwencje numeracji wierszy to miejsce, w którym tego rodzaju mostek zwykle się psuje, więc warto je wprost wypunktować. Kalkulator przekazuje do callbacku wiersz liczony od 0, zgodnie ze współrzędnymi, których już używa TXLSGetValue. Arkusz XLSX indeksuje swoją mapę ukrycia wierszy numerem wiersza liczonym od 1, dokładnie tak jak numeruje wiersze Excel, co odpowiada też publicznej właściwości RowHidden[ARow]. Mostek XLSX dodaje więc jeden przed wyszukaniem, a mostek BIFF tego nie robi, ponieważ TXLSRowInfoList już jest liczony od 0. Oba mostki traktują indeks arkusza lub wiersz spoza prawidłowego zakresu jako widoczny, więc zapytanie poza zakresem degraduje się do starej odpowiedzi uwzględniającej ukryte wiersze, zamiast gubić dane
Co się zmienia dla filtrowanych skoroszytów
To właśnie ten przypadek generuje zgłoszenia do wsparcia technicznego. Zastosowanie AutoFiltra w HotXLS przez ApplyAutoFilter ewaluuje kryteria kolumny i ukrywa każdy wiersz danych, który nie pasuje, dokładnie tak, jak robi to Excel po kliknięciu rozwijanej listy filtra przez użytkownika. Przed v2.197.0 te ukryte wiersze były niewidoczne dla użytkownika i w pełni widoczne dla silnika obliczeniowego, więc serwerowe SUBTOTAL(109, ...) zgłaszało sumę bez uwzględnienia filtra. Teraz to samo wywołanie zgłasza sumę po filtrze
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
VisibleRows: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[0];
Sheet.SetAutoFilter('A1:E500');
Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
VisibleRows := Sheet.ApplyAutoFilter; // hides the non-matching rows
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// The cell value now agrees with what Excel shows for the same filter,
// and VisibleRows tells you how many rows fed into it
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;Ręczne ukrywanie działa tak samo, ponieważ RowHidden[ARow] := True to ten sam stan, który zapisuje filtr. Ta równoważność jest w Excelu celowa i teraz obowiązuje też w HotXLS. Jedna konsekwencja zasługuje na wzmiankę w dokumentacji dołączanej do generowanych przez ciebie skoroszytów: suma obliczona kodem 109 jest wartością zależną od widoku, więc odbiorca, który wyczyści filtr, ją zmienia. Gdy raport musi podawać stałą liczbę niezależnie od tego, co czytelnik zrobi z widokiem, właściwym wyborem jest i zawsze był kod 9. Filtry, walidacja i tabele są omówione razem w artykule o walidacji danych, AutoFiltrze i tabelach. Ponieważ ukrycie wierszy nie dotyka żadnej formuły, samo w sobie nie brudzi też grafu zależności, co warto wiedzieć, jeśli polegasz na przyrostowym przeliczaniu po brudnym podgrafie, by duże skoroszyty pozostawały responsywne
Kody opcji AGGREGATE i jedno wciąż otwarte ograniczenie
AGGREGATE to SUBTOTAL z drugim argumentem polityki, a HotXLS obsługuje ją w CalcAggregateFunc. Argument opcji koduje niezależne przełączniki: czy zagnieżdżone wywołania SUBTOTAL i AGGREGATE wewnątrz zakresu są pomijane, czy wartości w ukrytych wierszach są pomijane oraz czy wartości błędów są tłumione zamiast propagowane. HotXLS uzbraja wspólną bramkę ukrytych wierszy dla kodów opcji 2, 3, 6 i 7 oraz tłumi wartości błędów dla kodów opcji od 4 do 7. Argument numeru funkcji wybiera następnie agregację dokładnie tak samo jak SUBTOTAL, wliczając w to kierowanie wariancji, odchylenia standardowego i iloczynu do ich własnych reduktorów. Jedna udokumentowana luka pozostaje, i lepiej ją tu opisać, niż odkryć na produkcji: semantyka ignorowania zagnieżdżonego SUBTOTAL powiązana z niskimi kodami opcji nie jest zaimplementowana w HotXLS. Wykrycie zagnieżdżonego SUBTOTAL wewnątrz zakresu, do którego się odwołujemy, wymaga oznaczenia stanu rekurencji ewaluatora, tak by wewnętrzna agregacja mogła zgłosić się zewnętrznej, co jest zmianą większą niż bramka ukrytych wierszy. W praktyce ekspozycja jest niewielka, ponieważ prawdziwe skoroszyty niemal zawsze umieszczają formuły SUBTOTAL poza zakresami, po których agregują inne formuły SUBTOTAL. Jeśli twój generator faktycznie tworzy nakładające się zakresy agregacji, nie polegaj na niskich kodach opcji przy ich deduplikacji
Zabezpieczenie liczby argumentów dostarczone przy okazji
Wersja 2.197.0 zamknęła też lukę walidacyjną w tym samym dyspatcherze, a powód projektowy jest ten sam, który motywował pole robocze: umieścić sprawdzenie tam, gdzie można je napisać raz. Około 280 wbudowanych ciał funkcji weryfikowało samodzielnie liczbę swoich argumentów względem Item.ChildCount, co nie pozostawiało spójnej granicy dla przypadku zbyt wielu argumentów. Wywołanie takie jak =SIN(1,2) trafiało do ciała funkcji, które sprawdzało pierwszy argument, ignorowało nadmiar i zwracało wiarygodną liczbę tam, gdzie Excel zwraca #VALUE!. HotXLS przechowywał już zadeklarowaną liczbę argumentów każdej funkcji wbudowanej w swoim rejestrze funkcji, wystawianą jako THashFunc.ArgsCnt, z wartością -1 oznaczającą funkcję wariadyczną, taką jak SUM, IF czy CONCAT. Wersja 2.197.0 przekazała to dalej przez nową właściwość TXLSFormula.FuncArgsCntByPtg i dodała jedną bramkę na szczycie GetValueItemFunc, głównego dyspatchera
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] is the function node
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) now yields #VALUE!
Exit;
end;
end;Zabezpieczenie odrzuca zbyt wiele argumentów i celowo nic nie mówi o zbyt małej ich liczbie. Pominięcie końcowego argumentu opcjonalnego jest w Excelu legalne dla VLOOKUP, SUBSTITUTE i długiej listy innych funkcji, więc symetryczne sprawdzenie zepsułoby poprawne formuły, próbując wychwycić niepoprawne. Nieznane identyfikatory są zgłaszane jako wariadyczne i całkowicie omijają bramkę, co utrzymuje funkcje zdefiniowane przez użytkownika poza jej zasięgiem; jeśli rejestrujesz własne funkcje, zachowanie opisane w przewodniku po silniku formuł i funkcjach niestandardowych pozostaje niezmienione. Scentralizowanie przypadku zbyt małej liczby argumentów to osobne zadanie, ponieważ każde z tych 280 ciał ma własną semantykę kodów błędów i muszą być przeglądane pojedynczo, a nie z założenia
Opisany tu silnik obliczeniowy, obie fasady skoroszytów oraz zasilające je API AutoFiltra i widoczności wierszy są częścią komponentu arkusza Excel HotXLS dla Delphi, który dostarczany jest z pełnym kodem źródłowym dla Delphi i C++Buildera i nie wymaga zainstalowanego Excela na maszynie, na której działa