2024!A1 falha: apóstrofos em nomes de planilha em fórmulas

Uma pasta de trabalho tem uma planilha chamada FY2024, uma célula de resumo diz =FY2024!B7, e o Excel não consegue ler a fórmula. FY2024 também é um endereço de célula, então o nome precisa de apóstrofos, como em 'FY2024'!B7; o HotXLS os grava pela mesma regra que o Excel aplica ao salvar, e o workbook XLSX dele regrava toda fórmula quando uma planilha é renomeada ou excluída

Quais nomes de planilha precisam de apóstrofos numa fórmula?

Um nome de planilha precisa de apóstrofos sempre que o parser de fórmulas poderia lê-lo como algo que não seja um nome de planilha: um número, um boolean ou uma referência de célula. A gramática de fórmulas no ECMA-376 Part 1 §18.17 cobre nomes de planilha com e sem apóstrofos, mas a referência prática é o texto que o Excel grava nos elementos <f> do sheet1.xml, porque é contra esse texto que todo outro reader foi afinado. O echo do Range.Formula do COM do Excel e o XML salvo dele divergem para alguns nomes: R1. e A1.x voltam sem apóstrofos do .Formula mas carregam apóstrofos no arquivo. Uma regra derivada do echo do COM é contrariada pelos próprios arquivos do Excel. O HotXLS segue a forma armazenada, que exige apóstrofos para:

• Qualquer caractere fora de A-Z, a-z, 0-9 e do underscore: espaços, hífens, pontos, parênteses
• Dígito na frente: 2024, 2nd_Half e 3D vão todos com apóstrofos
• TRUE ou FALSE em qualquer caixa. TRUE1 e TRUEX ficam sem apóstrofos, já que nenhum lê como boolean
• Um nome que lê como célula A1 dentro da grade: uma a três letras formando coluna não posterior a XFD, seguidas de linha de 1 a 1048576, zeros à esquerda permitidos
• Um nome que começa como referência R1C1: R, C ou RC sozinhos, ou seguidos de dígito
• Dentro de um nome com apóstrofos, um apóstrofo é dobrado — Bob's Data é gravado 'Bob''s Data'!A1

A regra A1 é a que workbooks reais encontram, porque tantos nomes de planilha corporativos por acaso são endereços de célula válidos. FY2024 é a coluna FY, linha 2024. Jan2024 é a coluna JAN, linha 2024, e JAN fica bem dentro das 16.384 colunas que uma planilha moderna tem. Q1, CR1 e Ra1 também são endereços, e os três precisam de apóstrofos. Os casos de fronteira vão para o outro lado: A0 não tem linha zero, XFE1 está uma coluna além da borda, XFD1048577 está uma linha além dela, e ABCD1 tem quatro letras, então o Excel deixa todos sem apóstrofos. Zeros à esquerda não resgatam um nome, e A01 ainda ganha apóstrofos porque o parser lê como A1. Um writer que só checa a classe de caracteres do nome vai emitir FY2024!B7 para um relatório de ano fiscal, e o Excel não consegue ler essa fórmula de volta

Sósias de R1C1 e o caso a favor do excesso de apóstrofos

Para nomes com forma de R1C1 não há regra limpa para recuperar da saída do Excel. O Excel põe apóstrofos em R, C, RC, R1, C1, R1x, R1C1x, e também em R1C16385 e C99999, que nomeiam colunas que não existem. No entanto deixa R0, C0, RC0, R0C1, R1048577, Rx, Cx e R_1 sem apóstrofos. Nenhuma combinação de limites de grade e formas de token reproduz as duas listas, então o HotXLS toma um superconjunto: um nome que é R, C ou RC, ou que começa com um desses seguido de dígito, ganha apóstrofos. O superconjunto põe apóstrofos em alguns nomes que o Excel deixa sem, R0 e R1048577 entre eles. Isso é aceitável por um motivo simples: a grafia com apóstrofos de qualquer nome de planilha é sintaxe de fórmula válida, então todo reader que faz parse de fórmulas a aceita, enquanto uma lista que tentasse bater com o Excel nome por nome descansaria numa regra que ninguém consegue enunciar. Nomes comuns como Chart1, Region1 e Cost2024 ficam fora dos dois testes e permanecem sem apóstrofos. Se você também trabalha com texto R1C1, as notas sobre notação de fórmula R1C1 no HotXLS descrevem como essas referências são parseadas em primeiro lugar

Nomes com letras fora do ASCII recebem apóstrofos em excesso também. O Excel é mais tolerante aqui. O Excel grava Données!A1 e 数据!A1 sem apóstrofos, porque letras acentuadas e CJK contam como caracteres de nome para o parser dele. O HotXLS lê esses qualificadores sem apóstrofos ao abrir um arquivo, já que o Excel os produz, mas grava tais nomes com apóstrofos, como em 'Données'!A1. A forma com apóstrofos vale em toda parte, e o teste do writer fica uma lista curta e previsível em vez de uma tabela de letras Unicode com a qual todo consumidor precisaria concordar

Construindo qualificadores de planilha no Delphi

O lxStandard.XlsSheetNameNeedsQuotes é a forma pública dessa regra, e é a função que o writer XLSX e o motor clássico ambos usam quando emitem uma referência de planilha. Quando o seu próprio código monta texto de fórmula a partir de nomes de planilha, como uma planilha de resumo que aponta para uma planilha por região ou por ano, passe cada nome por ela em vez de concatenar Name + '!'. A função só responde sim ou não. Dobrar apóstrofos internos é tarefa do chamador, como o helper abaixo faz, e fórmulas de célula XLSX são atribuídas sem o = inicial

program SheetQualifiers;

{$APPTYPE CONSOLE}

uses
  System.SysUtils, lxStandard, lxHandleX;

// Qualificador para texto de fórmula montado à mão: apóstrofos quando o nome
// precisa deles, com qualquer apóstrofo dentro do nome dobrado
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;
      // Use o nome que a planilha de fato recebeu, não o pedido
      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.

// Esperado:
// 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

Um nome de worksheet num arquivo XLSX não pode conter : \ / ? * [ ], não pode começar nem terminar com apóstrofo, tem teto de 31 caracteres, precisa ser único sem considerar a caixa, e não pode ser History, que o Excel reserva. O TXLSXSheets.Add e o setter do TXLSXWorksheet.Name ambos fazem cumprir isso: caracteres proibidos viram underscores, uma colisão ganha um sufixo (2), e um nome reservado ou vazio cai para Sheet. O valor que você atribui e o valor que você lê de volta podem portanto divergir, e um qualificador montado a partir do nome pedido aponta para uma planilha que não existe. Leia o Name de volta depois de cada atribuição e ponha apóstrofos nesse

Sheet := Book.Sheets.Add('Totals');
Sheet.Name := 'Q1/Q2 Sales';                 // '/' não é permitido em nome de planilha
Writeln(Sheet.Name);                         // Q1_Q2 Sales
Writeln(SheetQualifier(Sheet.Name) + 'C9');  // 'Q1_Q2 Sales'!C9

O que o Excel armazena depois que uma planilha é excluída?

Quando uma planilha é excluída, o Excel substitui a referência inteira a ela por #REF!, qualificador e célula ou faixa juntos. SUM(Data!A1:B2)+1 é armazenado como SUM(#REF!)+1, COUNT(Data!C:C) como COUNT(#REF!), um defined name que apontava para Data!$A$1:$B$2 como #REF! puro, e uma faixa de série de gráfico como <c:f>#REF!</c:f>. A visão do COM engana aqui também: o Range.Formula exibe =#REF!A1+1, como se a parte de célula tivesse sobrevivido, enquanto o XML que o Excel salva não tem parte de célula nenhuma. Copiar a exibição do COM rende texto de fórmula que o próprio Excel nunca grava — aposta ruim para um arquivo que outras ferramentas vão ler. O TXLSXSheets.DeleteByName grava a forma armazenada. A mesma lógica de colapso-para-#REF! governa referências cujas linhas ou colunas desaparecem, o que o texto sobre como inserir e excluir linhas ajusta referências de fórmula cobre para o caso dentro da planilha

Referências tridimensionais se comportam de forma diferente. O Excel encolhe uma faixa 3D em vez de destruí-la. Com as planilhas ordenadas Data, Mid, Keep, excluir Data transforma SUM(Data:Keep!A1) em SUM(Mid:Keep!A1): a ponta que desapareceu move uma planilha para dentro, medida na ordem de abas como ela estava antes da exclusão. Com só duas planilhas na faixa, o resultado é a forma de planilha única SUM(Keep!A1). Excluir Mid não muda texto de fórmula nenhum, já que a faixa ainda nomeia duas planilhas vivas e simplesmente cobre uma a menos no meio. Só quando ambas as pontas nomeiam a planilha excluída é que a referência vira #REF!. Uma referência morta muda o que uma fórmula retorna, então o HotXLS marca como stale o resultado em cache de toda célula de fórmula que ele regravou durante uma exclusão; chame Recalculate antes de ler valores. Um rename deixa os valores em cache em paz, já que os números não podem mudar

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;   // células que perderam Data carregam valores em cache stale
    Book.SaveAs('report.xlsx');
  finally
    Book.Free;
  end;
end.

// Esperado:
// Após excluir Data:
//   B1: SUM(Mid:Keep!A1)
//   B2: SUM(#REF!)+1
//   B3: Keep!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!
// Após renomear Keep para 2025:
//   B1: SUM('Mid:2025'!A1)
//   B2: SUM(#REF!)+1
//   B3: '2025'!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!

Renomear uma planilha regrava referências pelo workbook inteiro

Atribuir o TXLSXWorksheet.Name num workbook XLSX leva o nome novo a todo lugar onde uma fórmula pode morar, e o DeleteByName percorre os mesmos lugares para aplicar o colapso para #REF!. A lista vai bem além de fórmulas de célula:

• Fórmulas de célula em toda planilha. Uma shared formula fica guardada expandida em cada célula seguidora e uma array formula mora na célula raiz dela, então ambas ficam cobertas sem tratamento especial
• Fórmulas de formatação condicional, incluindo thresholds do tipo fórmula em data bars, color scales e icon sets
• Fórmulas de validação de dados, como uma fonte de lista em outra planilha
• Fórmulas de coluna calculada e de linha de totais de tabela
• Defined names, a espinha dorsal de modelos construídos sobre defined names e fórmulas entre planilhas; num rename, um pivot cache que lê a faixa de origem dele da planilha renomeada acompanha também
• Faixas de gráfico: nomes de série, categorias, valores, tamanhos de bolha e faixas de rótulo de dados, faixas mais e menos de error bar, e as referências de célula por trás de títulos de gráfico e de eixo

Uma faixa 3D recebe apóstrofos como um todo quando qualquer ponta precisa deles: renomear Keep para 2025 produz 'Mid:2025'!A1, nunca Mid:'2025'!A1, porque a faixa é um qualificador único. Todo qualificador sobre o qual o rename não trata é reaplicado como gravado. Se uma fórmula em outro lugar diz 'Sheet1'!B4, com apóstrofos tecnicamente desnecessários, ela continua dizendo isso depois. Normalizar esses qualificadores no caminho seria inofensivo para o Excel, mas um rename de uma planilha tocaria então toda fórmula do workbook, e um diff entre duas versões de um relatório gerado enterraria a mudança real sob centenas de mudanças cosméticas

Texto que só parece uma referência de planilha

Três tipos de texto parecem um qualificador Name! para uma busca por padrão e não são. Dentro de um string literal, "Data!A1" é texto, então INDIRECT("Data!A1") continua apontando para o nome antigo depois de um rename ou uma exclusão, igual no Excel; se um relatório monta nomes de planilha para o INDIRECT, guarde-os numa célula que o seu código atualiza junto com o rename. Um qualificador precedido por um índice de book entre colchetes, como em [1]Data!A1, pertence a um workbook externo que por acaso tem uma planilha chamada Data, e renomear a planilha Data local não deve tocá-lo; um qualificador com apóstrofos que contém um [ é tratado da mesma forma. E o próprio #REF! tem a forma de um qualificador chamado REF, que é por que o HotXLS confere o # precedente antes de tratar REF! como nome de planilha. Sem essa checagem, renomear uma planilha chamada REF regravaria todo #REF! do workbook

Os nomes de planilha mais propensos a quebrar uma fórmula gerada são os comuns como FY2024, Q1 e 2025, não nomes com espaços ou pontuação. Ponha apóstrofos neles via XlsSheetNameNeedsQuotes onde quer que o seu código Delphi monte texto de fórmula. A partir daí, um rename ou uma exclusão no componente de planilhas Delphi HotXLS deixa toda fórmula na forma que o próprio Excel teria salvo