A HotXLS mostantól kiértékeli a strukturált táblahivatkozásokat, így a =SUM(Table1[Amount]) egy számot állít elő ahelyett, hogy kimaradna. A feloldó kezeli a Table[Column], a Table[[Column]], az olyan oszloptartományokat, mint a Table[[Q1]:[Q4]], és az [#Data], [#All], [#Headers] és [#Totals] item specifikátorokat, mindegyiket a munkafüzet táblamodellje ellenében feloldva elemzéskor, miközben az eredeti formulaszöveg szó szerint íródik vissza
Egy forma szándékosan hiányzik, és ez az, amelyikbe az emberek először beleütköznek. Az aktuális-sor rövidítés, a [@Column] nem támogatott, egy strukturális ok miatt, amelyet érdemes megérteni, nem pedig vakon megkerülni
Miért nem csak egy barátságos nevű tartomány egy strukturált hivatkozás?
Mert egy definiált név lefagyaszt egy címet, egy táblahivatkozás pedig nem. Írd, hogy DataBlock, mint egy névre, amely a Sheet1!$A$2:$D$100-ra mutat, és az a téglalap marad, amíg valami át nem írja. Írd, hogy Sales[Amount], és az azt jelenti: „a Sales tábla Amount oszlopa”, bármi is legyen az adott tábla kiterjedése akkor, amikor a formula kiértékelődik. Adj húsz sort a táblához, és az összeg lefedi őket; nincs mit igazítani, mert a formulában eleve soha nem volt cím
Ez a szimbolikus tulajdonság pontosan az oka annak, hogy a hivatkozás nem oldható fel stringhelyettesítéssel. A feloldónak meg kell találnia a táblát a munkafüzetben név szerint, ki kell keresnie az oszlopot a fejlécszövege alapján, el kell döntenie, mely sorokat fedi le a kért item specifikátor, és elő kell állítania egy konkrét téglalapot. A HotXLS ezt a formulafordítás során teszi a táblamodellen keresztül, ezért egy, a tábla növekedése előtt írt formula továbbra is a tábla aktuális kiterjedése ellenében kerül kiértékelésre
A grammatika, amelyet a HotXLS feloldani képes
A támogatott specifikációs grammatika egyetlen téglalap alakú eredményt fed le, és érdemes pontosan kimondani, mert az Excel dokumentációja sokkal nagyobb felületet mutat be, mint amit a legtöbb motor implementál. A HotXLS elfogadja a [Col]-ot és a zárójelezett [[Col]] változatot, a mezítelen [#Data], [#All], [#Headers] és [#Totals] item specifikátorokat, a kombinált [[#Data],[Col]] formát, egy tartományt egy item specifikátoron belül, mint a [[#Data],[Col1]:[Col2]], és egy egyszerű tartományt, mint a [Col1]:[Col2]
Amit ez a halmaz ad, az minden olyan hivatkozásalak, amely egyetlen összefüggő blokkot állít elő: egy oszlop, egymás melletti oszlopok sorozata, bármelyik csak-törzs vagy fejlécet-is-tartalmazó szelete. A nem szomszédos uniók és a többterületű eredmények kívül esnek rajta. Amikor egy hivatkozás nem oldható fel, a formula megtartja a korábbi érték-nélküli-kihagyás viselkedést ahelyett, hogy egy találgatást helyettesítene be, így egy fel nem oldható hivatkozás soha nem válik hihető rossz számmá
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);
// ... írd meg a fejlécsort és a 24 adatsort ...
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;Miért van szándékosan kizárva az aktuális-sor forma?
A [@Column] és a [#This Row] azt jelenti: „annak az oszlopnak a cellája azon a soron, ahol ez a formula él”. Az érték tehát a kiértékelő cella pozíciójától függ, nem csak a táblától. Ez egy másfajta hivatkozás: nem egy téglalap, amelyet a fordító egyszer feloldhat, hanem egy cellánkénti feloldás, amelyet minden sorra újra kell végezni, amelyet a formula elfoglal
A HotXLS False értéket ad vissza a táblatartomány-feloldóból ezekre a formákra, ami az érték-nélküli-kihagyás útvonalra irányítja őket. A formulaszöveg megőrződik, és változatlanul íródik vissza, így egy [@Amount]-ot használó munkafüzet helyesen nyílik meg Excelben, miután végigment az alkalmazásodon; csak a HotXLS által számított érték hiányzik. A hiányzó érték és a rossz sor ellenében kiszámított érték közötti választásban a hiány az, amit észlelni tudsz
A gyakorlati megkerülő megoldás mechanikus: egy általad generált munkafüzetben írd meg az ekvivalens A1-stílusú relatív hivatkozást, amit az Excel amúgy is belsőleg tárol a tábla hatókörű logika nagy részéhez. Egy általad csak feldolgozott munkafüzetben hagyd békén a formulát, és olvasd ki azt a gyorsítótárazott értéket, amelyet az Excel már eltárolt, ami az, amit egy betöltés-és-jelentés pipeline általában akar
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
// Recordset-stílusú keresés a tábla törzse felett, 1-alapú sor-eredmény
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;Mi történik, amikor a tábla alakja megváltozik
A strukturált hivatkozások érvénytelenítésre kerülnek ahelyett, hogy csendben átirányítanák őket, amikor az a dolog eltűnik, amelyet megneveznek. Törölj egy oszlopot, és az arra az oszlopra hivatkozó formulák ugyanúgy érvénytelenítésre kerülnek, ahogy az Excel érvényteleníti őket; töröld vagy nevezd át a táblát, és a rá mutató hivatkozások ugyanúgy kerülnek kezelésre. Ez a helyes viselkedés, és tükrözi a szokásos hivatkozás-igazítást, amit a formula-hivatkozás igazítása beszúráskor és törléskor című cikk ír le, ahol a motor feladata az, hogy a formulákat becsületesen tartsa, ne pedig érvényesnek tűnőnek
A sornövekedés az ellentétes eset, és egyáltalán nem igényel igazítást. Mivel a hivatkozás a táblát nevezi meg, nem egy téglalapot, sorok hozzáfűzése a tábla tartományán belül kiszélesíti azt, amit a [#Data] lefed, anélkül hogy egyetlen formulát is érintene. Ez az a tulajdonság, amely érdemessé teszi a táblák használatát egy jelentéssablonban: az összegzősor tovább összesíti mindazt, amit az import előállított, bármennyi sor is legyen az végül
Vissza-oda íródási fegyelem
A HotXLS megtartja az eredeti formulaszöveget. Egy SUM(SalesTable[Amount])-tal betöltött munkafüzet SUM(SalesTable[Amount])-tal kerül mentésre, nem a feloldott SUM(D2:D25)-tal. Ez jobban számít, mint amennyire tűnhet: egy felhasználó, aki megnyitja a kimenetedet Excelben, azt a formulát várja látni, amit írt, és egy feloldott cím csendben egy önfenntartó modellt egy törékennyé alakítana át, amely leáll az új sorok lefedésénél
Két kapcsolódó képesség teszi teljessé a képet. Maguk a tábladefiníciók, beleértve a fejléc nélküli táblákat és a táblánkénti megjegyzéseket, a adatérvényesítés, AutoFilter és Excel-táblák című cikkben leírt táblamodellen keresztül íródnak vissza. És amikor sok cella egy mintát oszt meg, az XLSX egyszer tárolja őket megosztott formulaként, amelyet kibontanak és újra kiadnak, ahogy azt a megosztott formula si kibontása című cikk tárgyalja. A megosztott formulákon belüli strukturált hivatkozások mindkét útvonalon áthaladnak, így mindkettőnek helyesen kell viselkednie, és úgy is viselkednek
A HotXLS Excel telepítése és Office automatizálás nélkül olvas és ír XLS, XLSX és ODS fájlokat Delphiből és C++Builderből, saját motorjában értékelve ki a formulákat. A táblamodell, a formulamotor és az újraszámítási API a HotXLS Delphi táblázatkezelő komponens oldalán van dokumentálva