Имена листов вроде 2024 требуют кавычек в формулах Excel

В бюджетной книге есть лист с именем FY2024, ячейка сводки говорит =FY2024!B7, и Excel не может прочитать формулу. FY2024 — ещё и адрес ячейки, поэтому имени нужны апострофы, как в 'FY2024'!B7; HotXLS пишет их по тому же правилу, каким Excel руководствуется при сохранении, и его XLSX-книга переписывает каждую формулу при переименовании или удалении листа

Какие имена листов нужно брать в кавычки в формуле?

Имени листа нужны апострофы всякий раз, когда парсер формул может прочитать его как что-то иное, чем имя листа: число, булево значение или ссылку на ячейку. Грамматика формул в ECMA-376 Part 1 §18.17 покрывает имена листов в кавычках и без, но практический ориентир — текст, который Excel пишет в элементы <f> файла sheet1.xml, потому что именно на этот текст настроен любой другой читатель. COM-эхо Range.Formula и сохранённый XML у Excel расходятся на некоторых именах: R1. и A1.x возвращаются голыми из .Formula, но несут апострофы в файле. Правило, выведенное из COM-эха, опровергается собственными файлами Excel. HotXLS следует хранимой форме, которая требует кавычек для:

• Любой символ вне A-Z, a-z, 0-9 и подчёркивания: пробелы, дефисы, точки, скобки
• Цифра в начале: 2024, 2nd_Half и 3D — все в кавычках
• TRUE или FALSE в любом регистре букв. TRUE1 и TRUEX остаются голыми, поскольку ни одно не читается как булево
• Имя, читающееся как ячейка A1 внутри сетки: от одной до трёх букв, образующих колонку не позднее XFD, за которыми следует строка от 1 до 1048576, ведущие нули допустимы
• Имя, начинающееся как ссылка R1C1: R, C или RC сами по себе или с цифрой следом
• Внутри имени в кавычках апостроф удваивается — Bob's Data записывается как 'Bob''s Data'!A1

Правило A1 — то самое, на которое натыкаются реальные книги, ведь столько деловых имён листов оказываются валидными адресами ячеек. FY2024 — колонка FY, строка 2024. Jan2024 — колонка JAN, строка 2024, и JAN сидит глубоко внутри 16 384 колонок современного листа. Q1, CR1 и Ra1 — тоже адреса, и все три должны быть в кавычках. Пограничные случаи идут в обратную сторону: у A0 нет нулевой строки, XFE1 — на колонку за краем, XFD1048577 — на строку за ним, а ABCD1 несёт четыре буквы, так что Excel оставляет все эти имена голыми. Ведущие нули имя не спасают, и A01 всё равно получает апострофы, потому что парсер читает его как A1. Писатель, проверяющий только класс символов имени, выдаст FY2024!B7 для отчёта за фингод, и Excel не прочтёт такую формулу обратно

Двойники R1C1 и доводы в пользу перестраховки кавычками

Для имён формы R1C1 чистого правила, восстанавливаемого из вывода Excel, нет. Excel берёт в кавычки R, C, RC, R1, C1, R1x, R1C1x, а также R1C16385 и C99999, называющие несуществующие колонки. При этом оставляет голыми R0, C0, RC0, R0C1, R1048577, Rx, Cx и R_1. Никакая комбинация пределов сетки и форм токенов не воспроизводит оба списка, поэтому HotXLS берёт супермножество: имя, равное R, C или RC, либо начинающееся с одного из них и цифры следом, получает апострофы. Супермножество закавычивает несколько имён, которые Excel оставляет голыми, — среди них R0 и R1048577. Это приемлемо по простой причине: закавыченное написание любого имени листа — валидный синтаксис формулы, и любой читатель, разбирающий формулы, его примет, тогда как список, пытающийся совпасть с Excel поимённо, держался бы на правиле, которое никто не в силах сформулировать. Обычные имена вроде Chart1, Region1 и Cost2024 вне обеих проверок и остаются голыми. Если работаете ещё и с текстом R1C1, заметки о нотации формул R1C1 в HotXLS описывают, как такие ссылки разбираются в первую очередь

Имена с буквами вне ASCII тоже закавычиваются с запасом. Excel здесь мягче. Excel пишет Données!A1 и 数据!A1 без апострофов, потому что буквы с акцентами и CJK-буквы его парсер считает символами имени. HotXLS такие голые квалификаторы читает при открытии файла — их производит Excel, — но пишет такие имена в кавычках, как 'Données'!A1. Закавыченная форма валидна везде, а проверка писателя остаётся коротким предсказуемым списком, а не таблицей юникодных букв, с которой пришлось бы сверяться каждому потребителю

Собираем квалификаторы листов в Delphi

lxStandard.XlsSheetNameNeedsQuotes — публичная форма этого правила, и именно её используют и XLSX-писатель, и классический движок, когда выпускают ссылку на лист. Когда ваш код сам собирает текст формул из имён листов — скажем, лист сводки, указывающий на лист по каждому региону или году, — прогоняйте каждое имя через неё, а не склеивайте Name + '!'. Функция отвечает только да или нет. Удвоение внутренних апострофов — забота вызывающего, как в хелпере ниже, а формулы ячеек XLSX присваиваются без ведущего =

program SheetQualifiers;

{$APPTYPE CONSOLE}

uses
  System.SysUtils, lxStandard, lxHandleX;

// Квалификатор для формул, собранных руками: апострофы, когда имя
// их требует, с удвоением каждого апострофа внутри имени
function SheetQualifier(const Name: WideString): WideString;
begin
  if XlsSheetNameNeedsQuotes(Name) then
    Result := '''' + StringReplace(Name, '''', '''''', [rfReplaceAll]) + '''!'
  else
    Result := Name + '!';
end;

const
  Candidates: array[0..10] of WideString = (
    'Summary', '2024', 'FY2024', 'Q1', 'TRUE', 'TRUE1',
    'R1C1', 'Region1', 'XFE1', 'Données', 'Bob''s Data');
var
  Book: TXLSXWorkbook;
  Map, Sheet: TXLSXWorksheet;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Map := Book.Sheets.Add('Map');
    for I := Low(Candidates) to High(Candidates) do
    begin
      Sheet := Book.Sheets.Add(Candidates[I]);
      Sheet.Cells[1, 1].Value := (I + 1) * 10;
      // Берите имя, которое лист реально получил, а не запрошенное
      Map.Cells[I + 1, 1].Value := Sheet.Name;
      Map.Cells[I + 1, 2].Formula := SheetQualifier(Sheet.Name) + 'A1';
      Writeln(Format('%-12s %s', [Sheet.Name, Map.Cells[I + 1, 2].Formula]));
    end;
    Book.SaveAs('sheet-map.xlsx');
  finally
    Book.Free;
  end;
end.

// Ожидание:
// Summary      Summary!A1
// 2024         '2024'!A1
// FY2024       'FY2024'!A1
// Q1           'Q1'!A1
// TRUE         'TRUE'!A1
// TRUE1        TRUE1!A1
// R1C1         'R1C1'!A1
// Region1      Region1!A1
// XFE1         XFE1!A1
// Données      'Données'!A1
// Bob's Data   'Bob''s Data'!A1

Имя листа в XLSX-файле не может содержать : \ / ? * [ ], не может начинаться или заканчиваться апострофом, ограничено 31 символом, должно быть уникальным без учёта регистра и не может быть History, которое зарезервировано Excel. И TXLSXSheets.Add, и сеттер TXLSXWorksheet.Name это обеспечивают: запретные символы становятся подчёркиваниями, при коллизии добавляется суффикс (2), а зарезервированное или пустое имя откатывается к Sheet. Присвоенное значение и прочитанное обратно могут различаться, и квалификатор, собранный из запрошенного имени, указывает на лист, которого нет. Читайте Name обратно после каждого присвоения и закавычивайте именно его

Sheet := Book.Sheets.Add('Totals');
Sheet.Name := 'Q1/Q2 Sales';                 // '/' в имени листа не разрешён
Writeln(Sheet.Name);                         // Q1_Q2 Sales
Writeln(SheetQualifier(Sheet.Name) + 'C9');  // 'Q1_Q2 Sales'!C9

Что Excel хранит после удаления листа?

При удалении листа Excel заменяет всю ссылку на него на #REF! — квалификатор вместе с ячейкой или диапазоном. SUM(Data!A1:B2)+1 хранится как SUM(#REF!)+1, COUNT(Data!C:C) — как COUNT(#REF!), определённое имя, указывавшее на Data!$A$1:$B$2, — как голое #REF!, а диапазон серии диаграммы — как <c:f>#REF!</c:f>. COM-картина вводит в заблуждение и здесь: Range.Formula показывает =#REF!A1+1, как будто часть с ячейкой выжила, тогда как в XML, который Excel сохраняет, части с ячейкой нет вовсе. Скопированное COM-отображение даёт текст формулы, который Excel сам никогда не пишет, — плохая ставка для файла, который будут читать другие инструменты. TXLSXSheets.DeleteByName пишет хранимую форму. Та же логика схлопывания в #REF! управляет ссылками, чьи строки или колонки исчезли, — для внутрилистового случая это разбирает заметка о том, как вставка и удаление строк корректируют ссылки в формулах

Трёхмерные ссылки ведут себя иначе. Excel сжимает 3D-диапазон вместо того, чтобы уничтожать его. При порядке листов Data, Mid, Keep удаление Data превращает SUM(Data:Keep!A1) в SUM(Mid:Keep!A1): исчезнувший конец сдвигается на лист внутрь, если мерить по порядку ярлыков, каким он был до удаления. Когда в диапазоне всего два листа, результат — одностраничная форма SUM(Keep!A1). Удаление Mid вообще не меняет текст формул: диапазон всё ещё называет два живых листа и просто накрывает на один промежуточный меньше. Ссылка становится #REF!, только когда оба конца называют удалённый лист. Мёртвая ссылка меняет то, что формула возвращает, поэтому HotXLS помечает кэшированный результат каждой переписанной при удалении ячейки с формулой как устаревший; вызывайте Recalculate до чтения значений. Переименование кэш не трогает, ведь числа измениться не могут

uses
  System.SysUtils, lxHandleX;

procedure ShowFormulas(Book: TXLSXWorkbook; const Caption: string);
var
  Report: TXLSXWorksheet;
  Row: Integer;
begin
  Writeln(Caption);
  Report := Book.Sheets.SheetByName('Report');
  for Row := 1 to 4 do
    Writeln('  B', Row, ': ', Report.Cells[Row, 2].Formula);
  Writeln('  DataBlock: ', Book.DefinedNames.FindByName('DataBlock').Formula);
end;

var
  Book: TXLSXWorkbook;
  Report: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');
    Book.Sheets.Add('Mid');
    Book.Sheets.Add('Keep');
    Report := Book.Sheets.Add('Report');
    Book.DefinedNames.Add('DataBlock', 'Data!$A$1:$B$2');

    Report.Cells[1, 2].Formula := 'SUM(Data:Keep!A1)';
    Report.Cells[2, 2].Formula := 'SUM(Data!A1:B2)+1';
    Report.Cells[3, 2].Formula := 'Keep!A1*2';
    Report.Cells[4, 2].Formula := 'INDIRECT("Data!A1")';

    Book.Sheets.DeleteByName('Data');
    ShowFormulas(Book, 'After deleting Data:');

    Book.Sheets.SheetByName('Keep').Name := '2025';
    ShowFormulas(Book, 'After renaming Keep to 2025:');

    Book.Recalculate;   // ячейки, потерявшие Data, несут устаревший кэш значений
    Book.SaveAs('report.xlsx');
  finally
    Book.Free;
  end;
end.

// Expected:
// После удаления Data:
//   B1: SUM(Mid:Keep!A1)
//   B2: SUM(#REF!)+1
//   B3: Keep!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!
// После переименования Keep в 2025:
//   B1: SUM('Mid:2025'!A1)
//   B2: SUM(#REF!)+1
//   B3: '2025'!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!

Переименование листа переписывает ссылки по всей книге

Присвоение TXLSXWorksheet.Name в XLSX-книге заносит новое имя в каждое место, где может жить формула, а DeleteByName обходит те же места, чтобы применить схлопывание в #REF!. Список далеко выходит за формулы ячеек:

• Формулы ячеек на каждом листе. Разделяемая формула хранится развёрнутой на каждой ведомой ячейке, а формула массива живёт на своей корневой ячейке, так что обе покрыты без особой обработки
• Формулы условного форматирования, включая пороги типа formula в data bars, color scales и icon sets
• Формулы проверки данных, например источник списка на другом листе
• Формулы вычисляемых колонок и строки итогов таблиц
• Определённые имена — хребет моделей, построенных на определённых именах и межлистовых формулах; при переименовании за ними следует и pivot cache, читающий исходный диапазон с переименованного листа
• Диапазоны диаграмм: имена серий, категории, значения, размеры пузырей и диапазоны подписей данных, плюс- и минус-диапазоны планок погрешностей и ссылки на ячейки за заголовками диаграмм и осей

3D-диапазон закавычивается целиком, когда кавычек требует любой из концов: переименование Keep в 2025 даёт 'Mid:2025'!A1, но никак не Mid:'2025'!A1, потому что диапазон — один квалификатор. Каждый квалификатор, которого переименование не касается, воспроизводится как написано. Если формула где-то в стороне говорит 'Sheet1'!B4 — с технически излишними апострофами, — она и дальше будет говорить то же. Нормализация таких квалификаторов походя была бы для Excel безвредна, но тогда переименование одного листа трогало бы каждую формулу книги, и diff двух версий сгенерированного отчёта хоронил бы настоящее изменение под сотнями косметических

Текст, который лишь притворяется ссылкой на лист

Три вида текста похожи на квалификатор Name! для поиска по шаблону, но не являются им. Внутри строкового литерала "Data!A1" — текст, поэтому INDIRECT("Data!A1") и после переименования, и после удаления продолжает указывать на старое имя, как и в Excel; если отчёт собирает имена листов для INDIRECT, держите их в ячейке, которую ваш код обновляет вместе с переименованием. Квалификатор, перед которым стоит индекс книги в скобках, как в [1]Data!A1, принадлежит внешней книге, где случайно нашёлся лист с именем Data, и переименование локального листа Data его трогать не должно; закавыченный квалификатор, содержащий [, обрабатывается так же. А у #REF! самого по себе форма квалификатора с именем REF, поэтому HotXLS проверяет предшествующий #, прежде чем считать REF! именем листа. Без этой проверки переименование листа с именем REF переписало бы каждый #REF! в книге

Имена листов, чаще всего ломающие сгенерированную формулу, — обычные, вроде FY2024, Q1 и 2025, а не имена с пробелами или пунктуацией. Закавычивайте их через XlsSheetNameNeedsQuotes везде, где ваш Delphi-код собирает текст формул. Дальше переименование или удаление в компоненте электронных таблиц HotXLS для Delphi оставляет каждую формулу в той форме, какую сам Excel и сохранил бы