Таблица Excel, чей столбец Amount хранит =[@Qty]*[@Price], не сохраняет [@Qty] нигде в записанном файле. Распакуйте книгу — и элемент <f> в XML листа покажет, что Excel пишет на самом деле: длинную, таблично-квалифицированную форму Lines[[#This Row],[Qty]]*Lines[[#This Row],[Price]], потому что в грамматике формул из ECMA-376 Part 1, §18.17 есть ключевое слово #This Row, а @ нет. Ячейка итога пониже хранит SUM(Lines[Amount]). Ни в одной из этих строк нет ни одного адреса ячейки, поэтому движку, умеющему парсить только ссылки A1, нечего вычислять, пока он не узнает, где сидит таблица, какая строка несёт заголовки и на какой строке живёт сама формула
Вот эта последняя часть информации — самая неловкая. Ссылку на столбец можно превратить в прямоугольник один раз на таблицу. Ссылку текущей строки нельзя: она означает разную ячейку для каждой строки, в которую скопирована
Почему структурная ссылка — не просто диапазон с дружелюбным именем?
Определённое имя замораживает адрес, ссылка на таблицу — нет. Наведите имя DataBlock на Sheet1!$A$2:$D$100 — и оно останется этим прямоугольником, пока что-то его не перепишет. Sales[Amount] означает «столбец Amount таблицы Sales» — каким бы ни оказался охват таблицы в момент вычисления формулы. Добавьте в таблицу двадцать строк — и сумма накроет их; корректировать нечего, потому что формула изначально не держала адреса
HotXLS разрешает эти ссылки при каждой компиляции формулы для вычисления. TXLSXWorkbook находит таблицу по имени в табличной модели книги (TXLSXTable.Name или DisplayName, сравнение без учёта регистра), сопоставляет столбец с текстом заголовка, хранящимся в TXLSXTable.Columns, применяет спецификатор элемента, чтобы выбрать пояс строк, и передаёт компилятору абсолютный диапазон вроде 'Sales'!$D$2:$D$25. Переписанный текст существует только для той компиляции. Ячейка хранит формулу в точности как написано, и поскольку поиск идёт по TXLSXTable.Range в его состоянии на момент пересчёта, формула, написанная до роста таблицы, всё равно вычисляется по большей таблице
Грамматика прямоугольника: столбцы, охваты и спецификаторы элементов
Всякая широко-табличная форма, которую вычисляет HotXLS, даёт один непрерывный блок. Принимаемые написания: [Col] и его скобочный двойник [[Col]], голые спецификаторы элементов [#Data], [#All], [#Headers] и [#Totals], комбинированная форма [[#Data],[Col]], охват внутри спецификатора элемента вроде [[#Data],[Col1]:[Col2]] и простой охват [[Col1]:[Col2]]. Охват можно писать справа налево: [[Q2]:[Q1]] разрешается в те же два столбца, что [[Q1]:[Q2]]
Пояс строк, который выбирает каждый спецификатор, следует двум флагам таблицы. TXLSXTable.HasHeaderRow решает, является ли первая строка Range заголовком, а TXLSXTable.TotalsRowShown — является ли последняя строка строкой итогов. Простая ссылка на столбец и [#Data] накрывают строки между этими двумя. [#All] берёт весь Range, включая заголовок и итоги. Попросите [#Headers] у таблицы без строки заголовка, [#Totals] у таблицы без строки итогов или столбец таблицы, чьё тело пусто, — и ссылка станет #REF!. Это совпадает с тем, что показывает Excel на тот же запрос, и не даёт невозможной ссылке тихо превратиться в пустой диапазон, суммирующийся в ноль
Имена столбцов, содержащие скобки, решётку, собаку или апостроф, следуют правилу экранирования Excel: каждый такой знак внутри ссылки предваряется апострофом, поэтому заголовок Unit [USD] записывается как Sales[Unit '[USD']]. Одностолбцовая и текуще-строчная формы декодируют эти экранирования перед сопоставлением. Охваты и комбинированные формы [#Data],[Col] разрешаются по таблицам собственного листа формулы, поэтому сводному листу, которому нужен охват по таблице другого листа, лучше служит простая ссылка на столбец для каждого столбца
Пример ниже строит таблицу продаж на 24 строки и кладёт пять итоговых формул рядом с ней в F2–F6, на том же листе, что и таблица. Строка 1 — строка заголовка, строки 2–25 — тело, строки итогов нет, поэтому SalesTable[#All] охватывает 25 строк, а SalesTable[Amount] — 24
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[2, 6].Formula := 'SUM(SalesTable[Amount])';
Sheet.Cells[3, 6].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
Sheet.Cells[4, 6].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
Sheet.Cells[5, 6].Formula := 'ROWS(SalesTable[#All])';
Sheet.Cells[6, 6].Formula := 'COUNTA(SalesTable[#Headers])';
Book.Recalculate;
Book.SaveAs('sales.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;
Ожидаемые значения для этой раскладки: F5 равен 25, F6 равен 4 — по одному счёту на ячейку заголовка. F2–F4 зависят от данных, которые вы запишете
Откуда [@Column] знает, какую строку читать?
Ссылка текущей строки берёт свою строку из вычисляемой ячейки. Когда Recalculate добирается до формулы с Lines[@Qty] в строке 7, HotXLS разрешает её в столбец Qty строки 7; тот же текст формулы в строке 8 читает строку 8. Длинное написание, которое Excel пишет на диск, Lines[[#This Row],[Qty]], трактуется как та же ссылка, и Lines[@[Unit Price]] для заголовка с пробелом — тоже
Неквалифицированная форма [@Qty], без имени таблицы впереди, нуждается ещё в одном факте: какой таблице она принадлежит. HotXLS отвечает на это позицией. Если вычисляемая ячейка сидит внутри строк тела и охвата столбцов таблицы на том же листе, столбец поставляет та таблица, и внутри такой таблицы [@[Q1]:[Q2]] становится диапазоном в одну строку поперёк этих столбцов. Где угодно ещё ссылка остаётся неразрешённой. Определённое имя, чья формула использует ссылку текущей строки, берёт свою строку из ячейки, ссылающейся на имя, — то же поведение, что формула получила бы инлайн
Держите формулы текущей строки на собственных строках тела таблицы и квалифицируйте их именем таблицы, когда их генерирует код. Квалифицированные ссылки переживают перемещение, читаются так же, как их хранит Excel, и формула на собственных строках таблицы — тот единственный случай, когда Excel и любой другой потребитель файла сходятся в том, что значит @
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Cols: TStringList;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
Cols := TStringList.Create;
try
Sheet := Book.Sheets.Add('Orders');
Cols.Add('Item');
Cols.Add('Qty');
Cols.Add('Price');
Cols.Add('Amount');
Sheet.Tables.Add('Lines', 'A1:D13', Cols);
// ... запишите строку заголовка и товары, количества, цены в строках 2..13 ...
// Один текст формулы, двенадцать разных результатов: каждая ячейка читает свою строку
for Row := 2 to 13 do
Sheet.Cells[Row, 4].Formula := 'Lines[@Qty]*Lines[@Price]';
// Перекрёстная проверка под таблицей: сумма столбца против пересчитанного произведения
Sheet.Cells[15, 4].Formula :=
'SUM(Lines[Amount])-SUMPRODUCT(Lines[Qty],Lines[Price])';
Book.Recalculate;
// Ожидание: D15 вычисляется в 0, когда каждая ячейка Amount совпадает с Qty * Price
Book.SaveAs('orders.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;
Проверка в D15 дёшева, и её стоит оставить в генерируемом отчёте, пока тот строится. Построчная формула, подхватившая не ту строку, разошлась бы с одной из двух сумм, и расхождение высветится ненулевым числом в ячейке, которую любой может прочитать
Что происходит, когда таблица меняет форму
Рост — лёгкий случай, если определение таблицы действительно растёт. Запись значения в строку 26 под таблицей с Range равным A1:D25 не делает строку 26 частью таблицы. Автораширение — редакторская функция Excel, срабатывающая, когда кто-то печатает рядом с таблицей, и запись ячеек через HotXLS её не запускает. Выставьте TXLSXTable.Range на новый охват после импорта или вставьте строки внутрь таблицы через TXLSXWorksheet.InsertRows, который сдвигает Tables[i].Range вместе со всеми прочими ссылками листа. Любым из способов [#Data] расширяется, и ни одна формула не меняется
Сжатие — то место, где ссылки могут ломаться, и HotXLS следует правилу Excel. DeleteCols по столбцу таблицы переписывает каждую структурную ссылку, называющую этот столбец, в #REF! — в формулах ячеек, условных форматах, правилах проверки данных, рядах диаграмм и определённых именах. DeleteRows, убирающий строку заголовка, удаляет таблицу и инвалидирует её ссылки так же, а убирающий строку итогов сбрасывает TotalsRowShown, после чего [#Totals] не имеет строки, на которую указывать. Обычные ссылки A1 проходят ту же коррекцию, описанную в статье о коррекции ссылок формул при вставке и удалении
TXLSXTables.DeleteByName и переименование через TXLSXTable.Name работают только с табличной моделью. Формулы, упоминающие старое имя, сохраняют свой текст, поэтому переименовывайте эти формулы тем же проходом, когда ваш код переименовывает таблицу
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 Exit;
// ... запишите 20 импортированных строк в A26:D45 ...
Table.Range := 'A1:D45'; // тело теперь идёт со строки 2 по строку 45
Book.Recalculate; // SUM(SalesTable[Amount]) в F2 теперь накрывает 44 строки
// Поиск в стиле recordset по телу таблицы, результат — строка с единицы, 0 = нет
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value)); // Log — ваша собственная подпрограмма
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
Book.SaveAs('sales.xlsx');
finally
Book.Free;
end;
end;
FindFirst и FindNext сравнивают строки без учёта регистра, а числа по значению, и сканируют только строки тела, поэтому текст заголовка Region никогда не возвращается как совпадение
Дисциплина round-trip
Книга, загруженная с SUM(SalesTable[Amount]), сохраняется с SUM(SalesTable[Amount]), а ячейка, хранящая Lines[[#This Row],[Qty]], выходит наружу с этим текстом нетронутым. Разрешённый адрес никогда не заменяет написанное пользователем. Сохрани HotXLS вместо этого SUM($D$2:$D$25) — файл открылся бы в Excel с виду исправным, а потом перестал бы накрывать новые строки при первом же расширении таблицы кем-то, а это самый медленный вид поломки для отслеживания до причины
Сами определения таблиц, включая таблицы без заголовков и комментарии на таблицу, проходят round-trip через табличную модель, описанную в статье о проверке данных, автофильтре и таблицах Excel. Когда множество ячеек делят один шаблон формулы, XLSX может сохранить его один раз как общую формулу, и разворачивание этих ячеек описано в статье о развёртывании общей формулы si. Вычисляемый столбец, сохранённый Excel, часто приходит именно так, поэтому структурная ссылка в теле таблицы проходит оба пути до вычисления: общая формула сперва разворачивается в почекеечный текст, затем каждая ячейка разрешает собственную строку
Табличная модель, движок Recalculate и использованные здесь вызовы вставки и удаления листа — часть HotXLS, читающего и пишущего XLS, XLSX и ODS из Delphi и C++Builder без установки Excel. Полный справочник API живёт на странице компонента электронных таблиц HotXLS для Delphi