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]
Цей набір дає вам усі форми посилань, що дають один суцільний блок: стовпець, послідовність суміжних стовпців, зріз лише тіла або з включенням заголовка для будь-якого з них. Непослідовні об'єднання та результати з кількома областями лишаються поза цим набором. Коли посилання не вдається розв'язати, формула зберігає попередню поведінку «пропустити без значення» замість підстановки здогадки, тож нерозв'язне посилання ніколи не перетворюється на правдоподібне неправильне число
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 з резолвера діапазону таблиці для цих форм, що спрямовує їх у шлях «пропустити без значення». Текст формули зберігається й записується назад без змін, тож книга, що використовує [@Amount], коректно відкривається в Excel після проходження через ваш застосунок; відсутнє лише значення, обчислене 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], не торкаючись жодної формули. Саме ця властивість робить таблиці вартими використання в шаблоні звіту: рядок підсумків продовжує підсумовувати все, що створив імпорт, скільки б рядків це не виявилося
Дисципліна зворотного запису
HotXLS зберігає оригінальний текст формули. Книга, завантажена з SUM(SalesTable[Amount]), зберігається з SUM(SalesTable[Amount]), а не з розв'язаним SUM(D2:D25). Це важливіше, ніж може здатися: користувач, що відкриває ваш вивід у Excel, очікує побачити формулу, яку він написав, а розв'язана адреса тихо перетворила б модель, що самопідтримується, на крихку, яка перестає покривати нові рядки
Дві пов'язані можливості доповнюють картину. Самі визначення таблиць, включно з таблицями без заголовків і коментарями для окремих таблиць, зберігаються при зворотному записі через модель таблиць, описану в статті про перевірку даних, AutoFilter і таблиці Excel. А коли багато клітинок мають один спільний патерн, XLSX зберігає їх один раз як спільну формулу, яка розгортається й видається заново, як розглянуто в статті про розгортання si спільної формули. Структуровані посилання всередині спільних формул проходять обидва шляхи, тож обидва мають поводитися коректно, і вони поводяться
HotXLS читає та записує XLS, XLSX і ODS з Delphi та C++Builder без встановленого Excel і без автоматизації Office, обчислюючи формули у власному рушії. Модель таблиць, рушій формул та API перерахунку задокументовані на сторінці HotXLS Delphi spreadsheet component