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, ожидает увидеть написанную им формулу, а разрешённый адрес незаметно превратил бы самоподдерживающуюся модель в хрупкую, переставшую охватывать новые строки
Картину дополняют две смежные возможности. Сами определения таблиц, включая таблицы без заголовков и комментарии на уровне таблицы, переживают обратное преобразование через модель таблиц, описанную в статье о проверке данных, автофильтре и таблицах Excel. А когда много ячеек разделяют один шаблон, XLSX хранит их один раз как общую формулу, которая разворачивается и записывается заново, как рассмотрено в статье о разворачивании общих формул si. Структурные ссылки внутри общих формул проходят через оба пути, поэтому оба должны вести себя корректно — и они себя так и ведут
HotXLS читает и записывает XLS, XLSX и ODS из Delphi и C++Builder без установки Excel и без автоматизации Office, вычисляя формулы в собственном движке. Модель таблиц, движок формул и API пересчёта задокументированы на странице HotXLS Delphi spreadsheet component