Om SUBTOTAL(109, ...) och SUBTOTAL(9, ...) returnerar samma tal i en arbetsbok som innehåller dolda rader, är en av de två fel. HotXLS, den inbyggda Excel-kalkylbladskomponenten för Delphi och C++Builder, betedde sig precis så fram till version 2.197.0, eftersom dess beräkningsmotor inte hade något sätt att fråga ett kalkylblad om en given rad var dold
Symptomet dyker sällan upp som en felrapport om formelkoder. Det dyker upp som en avvikelse: ett batchjobb på servern beräknar en summa, en användare öppnar samma fil i Excel med ett filter applicerat, och de två talen skiljer sig med vad de bortfiltrerade raderna råkade summera till. Ingen misstänker aggregeringsfunktionen, för formelsträngen i cellen är identisk på båda ställena. Skillnaden ligger helt i vad utvärderaren fick lov att se
Varför inkluderar SUBTOTAL 109 dolda rader?
Därför att i de flesta motordesigner får lagret som utvärderar en formel aldrig veta något om radsynlighet. HotXLS var ett läroboksexempel: beräkningsmotorn i lxCalc.pas nådde cellvärden genom en enda TXLSGetValue-callback som svarar med ett värde för en (blad, rad, kolumn)-trippel och inget annat. Synlighet är ett presentationsattribut lagrat på radposten, och ingen del av den posten reste längre ner i anropskedjan. Motorn hade därför en aggregeringsväg, och båda halvorna av SUBTOTAL:s funktionsnummertabell löstes till den. Det är inte en avrundningsfelklass av defekt: det är hela anledningen till att den andra halvan av tabellen existerar. ECMA-376 del 1, publicerad som ISO/IEC 29500-1, definierar SUBTOTAL i sina formelfunktionsdefinitioner (§18.17.7) med ett första argument som väljer både den inre aggregeringen och policyn för dolda rader. Koderna 1 till 11 mappar till AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR och VARP medan de inkluderar värden på manuellt dolda rader. Koderna 101 till 111 väljer samma elva aggregeringar och exkluderar dem. En användare som skriver 109 istället för 9 gör ett avsiktligt uttalande om dold data, och en motor som kollapsar distinktionen tyst upphäver det uttalandet
Vad funktionsnumren mappar till inuti motorn
HotXLS löser SUBTOTAL:s första argument i CalcSubtotalFunc, som normaliserar koderna 101 till 111 ner till samma inre funktionsidentifierare som koderna 1 till 11 och sedan dispatchar på själva aggregeringen. Merparten av familjen flödar genom den inkrementella ExcelSum-ackumulatorn, den som hanterar SUM, COUNT, COUNTA, MIN, MAX och AVERAGE. Fem av dem kan inte det: STDEV, VAR, STDEVP, VARP och PRODUCT behöver en sluten form-genomgång av datan, så CalcSubtotalFunc dirigerar inre koderna 12, 46, 193, 194 och 183 till en separat reducerare, SubtotalReduceVariance. Den uppdelningen är det första värt att kartlägga innan man rör något, eftersom två oberoende aggregeringsvägar betyder två oberoende cellgenomgångsloopar, och en fix tillämpad på bara en av dem producerar det värsta möjliga utfallet: SUBTOTAL(109, ...) respekterar filtret medan SUBTOTAL(107, ...) på samma intervall inte gör det. Att räkna looparna i HotXLS gav sex av dem när AGGREGATE inkluderades, utspridda över intervallutvärdering, ren intervallinsamling och tre separata reducerare
Varför ett scratch-fält istället för sex nya signaturer?
Därför att att tråda en ny parameter genom sex cellgenomgångsfunktioner, plus allt som anropar dem, är en bred ändring av en het kodväg för skull av en enda boolean. HotXLS hade redan ett prejudikat för alternativet: ett transient fält på kalkylatorn, i samma anda som det scratch-fält GetRangeInfo använder för att registrera när en 3D-referens löstes in i en extern arbetsbok. Version 2.197.0 lade till ett andra. Motorn fick en callback-typ, TXLSIsRowHidden, deklarerad som en funktion av (SheetIndex, row) som returnerar Boolean, lagrad i FIsRowHidden, plus en transient FIgnoreHiddenRows-flagga. Flaggan väpnas vid ingången till CalcSubtotalFunc när funktionskoden faller inom 101 till 111, och vid ingången till CalcAggregateFunc för de AGGREGATE-alternativkoder som väljer exkludering av dolda rader. Varje cellgenomgångsloop inspekterar den sedan och hoppar över en rad när den är satt, vilket lägger till en enda rad vardera
// 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;Två detaljer i väpningskoden bär hela schemats korrekthet. Flaggan sparas och återställs istället för att bara sättas och rensas, eftersom ett SUBTOTAL-argument kan innehålla ett uttryck som kör sin egen utvärdering medan den yttre aggregeringen fortfarande är på stacken, och det nästlade arbetet får inte ärva eller förstöra den yttre grinden. Och återställningen bor i ett finally-block, eftersom CalcSubtotalFunc har flera tidiga utgångar för felkoder; en flagga lämnad väpnad efter en felretur skulle tyst korrumpera nästa orelaterade formel i omräkningsordningen
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;Assigned-testet är det som håller ändringen kompatibel. HotXLS utökade kalkylatorns konstruktor med en tredje parameter som defaultar till nil, så all kod som bygger en TXLSCalculator med det gamla tvåargumentsanropet fortfarande kompilerar och fortfarande får det gamla beteendet med inkluderade dolda rader. Ingenting om det befintliga API:et bytte form
Var kommer bit-flaggan för dold rad faktiskt ifrån?
Från kalkylbladet, genom två olika källor, eftersom HotXLS bär två arbetsboksmotorer. Den äldre BIFF-sidan svarar från TXLSRowInfoList.GetHidden, nådd genom TXLSWorkbook.GetRowHidden. OOXML-sidan svarar från TXLSXWorksheet.GetRowHidden, nådd genom TXLSXWorkbook.GetCalcRowHidden. Båda är kopplade in i kalkylatorn vid konstruktionstillfället, tillsammans med cellvärde-callbacken de speglar. Radkonventionerna är där den här sortens bro normalt går fel, så de är värda att säga uttryckligen. Kalkylatorn ger callbacken en 0-baserad rad, i linje med de koordinater TXLSGetValue redan använder. XLSX-kalkylbladet nycklar sin dold-rad-karta med 1-baserat radnummer, exakt som Excel numrerar rader, vilket också är vad den publika RowHidden[ARow]-egenskapen exponerar. XLSX-bryggan lägger därför till en före uppslagningen, och BIFF-bryggan gör det inte, eftersom TXLSRowInfoList redan är 0-baserad. Båda broarna behandlar ett blad-index eller en rad utanför det giltiga intervallet som synlig, så en fråga utanför gränserna nedgraderas till det gamla inkludera-dold-svaret istället för att tappa data
Vad ändras för filtrerade arbetsböcker
Det här är fallet som genererar supportärendena. Att applicera ett AutoFilter i HotXLS genom ApplyAutoFilter utvärderar kolumnkriterierna och döljer varje datarad som inte matchar, precis vad Excel gör när en användare klickar på en filterrullgardin. Före v2.197.0 var de dolda raderna osynliga för användaren och fullt synliga för beräkningsmotorn, så en SUBTOTAL(109, ...) på servern rapporterade den ofiltrerade summan. Nu rapporterar samma anrop den filtrerade
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;Manuell döljning fungerar på samma sätt, eftersom RowHidden[ARow] := True är samma tillstånd som filtret skriver. Den ekvivalensen är avsiktlig i Excel och gäller nu även i HotXLS. En konsekvens förtjänar en not i vilken dokumentation som än levereras med dina genererade arbetsböcker: en summa beräknad med kod 109 är ett vybaserat tal, så en mottagare som rensar filtret ändrar det. När en rapport måste ange en fast siffra oavsett vad läsaren gör med vyn, är kod 9 det korrekta valet och har alltid varit det. Filter, validering och tabeller behandlas tillsammans i artikeln om datavalidering, AutoFilter och tabeller. Eftersom att dölja rader inte rör vid någon formel, smutsar det heller inte ner beroendegrafen av sig själv, vilket är värt att veta om du förlitar dig på inkrementell omräkning över den smutsiga undergrafen för att hålla stora arbetsböcker responsiva
AGGREGATE-alternativkoder och en begränsning som fortfarande är öppen
AGGREGATE är SUBTOTAL med ett andra policyargument, och HotXLS hanterar den i CalcAggregateFunc. Alternativargumentet kodar oberoende brytare: om nästlade SUBTOTAL- och AGGREGATE-anrop inuti intervallet hoppas över, om värden på dolda rader hoppas över, och om felvärden undertrycks istället för att propageras. HotXLS väpnar den delade dold-rad-grinden för alternativkoderna 2, 3, 6 och 7, och undertrycker felvärden för alternativkoderna 4 till 7. Funktionsnummerargumentet väljer sedan aggregeringen precis som SUBTOTAL gör, inklusive dirigeringen av varians, standardavvikelse och produkt genom sina egna reducerare. En dokumenterad brist kvarstår, och det är bättre att säga det här än att upptäcka det i produktion: semantiken för att ignorera nästlad SUBTOTAL kopplad till de låga alternativkoderna är inte implementerad i HotXLS. Att upptäcka en nästlad SUBTOTAL inuti ett refererat intervall kräver att markera evalueringsmotorns rekursionstillstånd så en inre aggregering kan meddela sig till den yttre, vilket är en större ändring än dold-rad-grinden. I praktiken är exponeringen liten, eftersom riktiga arbetsböcker nästan alltid placerar SUBTOTAL-formler utanför de intervall andra SUBTOTAL-formler aggregerar över. Om din generator faktiskt bygger överlappande aggregeringsintervall, lita inte på de låga alternativkoderna för att avdubblera dem
Aritetsvakten som levererades tillsammans med den
Version 2.197.0 stängde också en valideringslucka i samma dispatcher, och designskälet är samma som motiverade scratch-fältet: sätt kontrollen där den kan skrivas en gång. Runt 280 inbyggda funktionskroppar verifierade var och en sitt eget argumentantal mot Item.ChildCount, vilket inte lämnade någon konsekvent gräns för fallet med för många argument. Ett anrop som =SIN(1,2) nådde en funktionskropp som undersökte sitt första argument, ignorerade överskottet, och returnerade ett rimligt tal där Excel returnerar #VALUE!. HotXLS lagrade redan den deklarerade ariteten för varje inbyggd funktion i sitt funktionsregister, exponerad som THashFunc.ArgsCnt med -1 som markerar en variadisk funktion som SUM, IF eller CONCAT. Version 2.197.0 vidarebefordrade det genom en ny TXLSFormula.FuncArgsCntByPtg-egenskap och lade till en grind högst upp i GetValueItemFunc, huvuddispatchern
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;Vakten avvisar för många argument och säger avsiktligt ingenting om för få. Att utelämna ett avslutande valfritt argument är lagligt i Excel för VLOOKUP, SUBSTITUTE och en lång lista andra, så en symmetrisk kontroll skulle ha brutit korrekta formler för att fånga felaktiga. Okända identifierare rapporteras som variadiska och hoppar helt över grinden, vilket är det som håller användardefinierade funktioner ur dess väg; om du registrerar dina egna funktioner påverkas inte beteendet beskrivet i guiden till formelmotorn och anpassade funktioner. Att centralisera för-få-fallet är ett separat jobb, eftersom var och en av de 280 kropparna har sin egen felkodssemantik och de måste granskas en i taget snarare än antas
Beräkningsmotorn som beskrivs här, båda arbetsboksfasaderna, och AutoFilter- och radsynlighets-API:erna som matar den är del av HotXLS Delphi-kalkylbladskomponent, som levereras med fullständig källkod för Delphi och C++Builder och inte kräver någon Excel-installation på maskinen som kör den