HotXLS вече изчислява структурирани референции към таблици, така че =SUM(Table1[Amount]) произвежда число, вместо да бъде пропуснато. Резолверът обработва Table[Column], Table[[Column]], обхвати от колони като Table[[Q1]:[Q4]], и спецификаторите за елементи [#Data], [#All], [#Headers] и [#Totals], разрешавайки всеки спрямо модела на таблиците на книгата по време на парсване, докато оригиналният текст на формулата се съхранява дословно
Една форма е умишлено отсъстваща и точно тя е онази, на която хората попадат първо. Съкратената форма за текущ ред [@Column] не се поддържа, по структурна причина, която си струва да се разбере, а не да се заобиколи слепешката
Защо структурирана референция не е просто диапазон с приятелско име?
Защото именувано определение замразява адрес, а референция към таблица — не. Напишете DataBlock като име, сочещо към Sheet1!$A$2:$D$100, и то остава този правоъгълник, докато нещо не го пренапише. Напишете Sales[Amount] и то означава „колоната Amount на таблицата Sales“, каквото и да се окаже разширението на тази таблица в момента на изчисление. Добавете двайсет реда към таблицата и сумата ги покрива; няма референция за коригиране, защото никога не е имало адрес във формулата да се започне
Точно това символно качество е причината референцията да не може да се разреши чрез замяна на низове. Резолверът трябва да намери таблицата по име в книгата, да потърси колоната по текста на заглавието ѝ, да реши кои редове покрива заявеният спецификатор на елемент и да произведе конкретен правоъгълник. HotXLS прави това по време на компилация на формулата чрез модела на таблиците, което е причината формула, написана преди таблицата да порасне, все още да се изчислява спрямо текущото ѝ разширение
Граматиката, която HotXLS разрешава
Поддържаната граматика на спецификатори покрива единичен правоъгълен резултат и си струва да бъде посочена точно, защото документацията на Excel представя далеч по-голяма повърхност, отколкото повечето движоци имплементират. HotXLS приема [Col] и скобирания вариант [[Col]], голите спецификатори за елементи [#Data], [#All], [#Headers] и [#Totals], комбинираната форма [[#Data],[Col]], обхват вътре в спецификатор за елемент като [[#Data],[Col1]:[Col2]], и обикновен обхват [Col1]:[Col2]
Онова, което този набор дава, е всяка форма на референция, произвеждаща един непрекъснат блок: колона, поредица от съседни колони, срез само на тялото или включващ заглавието на всяко от тях. Несъседни обединения и резултати от няколко области са извън него. Когато референция не може да бъде разрешена, формулата запазва предишното поведение skip-without-value, вместо да замести с догадка, така че неразрешима референция никога не се превръща в правдоподобно грешно число
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);
// ... запис на реда със заглавия и 24 реда данни ...
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;Защо формата за текущ ред е изключена умишлено?
[@Column] и [#This Row] означават „клетката от тази колона на реда, на който живее тази формула“. Стойността следователно зависи от позицията на изчисляващата клетка, не само от таблицата. Това е различен вид референция: не правоъгълник, който компилаторът може да разреши веднъж, а разрешаване по клетка, което трябва да се повтори за всеки ред, който формулата заема
HotXLS връща False от резолвера за диапазони на таблици за тези форми, което ги насочва по пътя skip-without-value. Текстът на формулата се запазва и записва обратно непроменен, така че книга, използваща [@Amount], се отваря коректно в Excel след round-trip през вашето приложение; само стойността, изчислена от HotXLS, отсъства. Между отсъстваща стойност и стойност, изчислена спрямо грешния ред, отсъствието е онова, което можете да засечете
Практическото заобикаляне е механично: в книга, която генерирате, напишете еквивалентната относителна референция в стил A1, което е онова, което Excel съхранява вътрешно за голяма част от логиката, обвързана с таблица. В книга, която само обработвате, оставете формулата непокътната и прочетете кешираната стойност, вече съхранена от Excel, което е онова, което конвейер за зареждане и отчитане обикновено иска
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 през тялото на таблицата, резултат ред с база 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;Какво се случва, когато таблицата смени формата си
Структурираните референции се обезсилват, вместо тихо да бъдат пренасочени, когато онова, което именуват, изчезне. Изтрийте колона и формулите, сочещи към тази колона, се обезсилват по същия начин, по който Excel ги обезсилва; изтрийте или преименувайте таблицата и референциите към нея се третират по същия начин. Това е правилното поведение и отразява обикновената корекция на референции, описана в корекцията на формулни референции при вмъкване и изтриване, където задачата на движока е да пази формулите честни, а не да ги пази изглеждащи валидни
Растежът на редовете е обратният случай и не изисква никаква корекция. Тъй като референцията именува таблицата, а не правоъгълник, добавянето на редове вътре в диапазона на таблицата разширява онова, което [#Data] покрива, без да пипа нито една формула. Точно това е свойството, което прави таблиците стойностни за шаблон на отчет: редът за общи суми продължава да сумира всичко, което импортът е произвел, колкото и редове да се окажат
Дисциплина при round-trip
HotXLS пази оригиналния текст на формулата. Книга, заредена с SUM(SalesTable[Amount]), се записва със SUM(SalesTable[Amount]), не с разрешения SUM(D2:D25). Това има по-голямо значение, отколкото изглежда: потребител, отварящ вашия изход в Excel, очаква да види формулата, която е написал, а разрешен адрес тихо би превърнал самоподдържащ се модел в крехък, който спира да покрива нови редове
Две свързани възможности допълват картината. Самите дефиниции на таблици, включително таблици без заглавия и коментари по таблица, преминават round-trip през модела на таблиците, описан в валидацията на данни, AutoFilter и Excel таблиците. А когато много клетки споделят един образец, XLSX ги съхранява веднъж като споделена формула, която се разгъва и преизлъчва, както е разгледано в разгъването si на споделена формула. Структурираните референции вътре в споделени формули минават през двата пътя, така че и двата трябва да работят, и работят
HotXLS чете и записва XLS, XLSX и ODS от Delphi и C++Builder без инсталация на Excel и без Office automation, изчислявайки формули в собствен движок. Моделът на таблиците, формулният движок и API-то за преизчисление са документирани на страницата на HotXLS Delphi компонента за електронни таблици