2024!A1 falla: entrecomillar nombres de hoja en fórmulas

Un libro de presupuestos tiene una hoja llamada FY2024, una celda de resumen dice =FY2024!B7 y Excel no puede leer la fórmula. FY2024 es también una dirección de celda, así que el nombre necesita apóstrofes, como en 'FY2024'!B7; HotXLS los escribe por la misma regla que aplica Excel al guardar, y su libro XLSX reescribe todas las fórmulas cuando se renombra o se borra una hoja

¿Qué nombres de hoja hay que entrecomillar en una fórmula?

Un nombre de hoja necesita apóstrofes siempre que el parser de fórmulas pueda leerlo como algo que no es un nombre de hoja: un número, un booleano o una referencia de celda. La gramática de fórmulas de ECMA-376 Part 1 §18.17 cubre los nombres de hoja con y sin comillas, pero la referencia práctica es el texto que Excel escribe en los elementos <f> de sheet1.xml, porque ese es el texto contra el que se ha afinado cualquier otro lector. El eco COM Range.Formula de Excel y su XML guardado discrepan para algunos nombres: R1. y A1.x vuelven desnudos de .Formula pero llevan apóstrofes en el fichero. Una regla derivada del eco COM queda contradicha por los propios ficheros de Excel. HotXLS sigue la forma almacenada, que exige comillas para:

• Cualquier carácter fuera de A-Z, a-z, 0-9 y el guion bajo: espacios, guiones, puntos, paréntesis
• Un dígito delante: 2024, 2nd_Half y 3D van todos entrecomillados
• TRUE o FALSE en cualquier combinación de mayúsculas. TRUE1 y TRUEX se quedan desnudos, porque ninguno se lee como booleano
• Un nombre que se lee como celda A1 dentro de la rejilla: de una a tres letras formando una columna no posterior a XFD, seguidas de una fila de 1 a 1048576, con ceros delante permitidos
• Un nombre que empieza como una referencia R1C1: R, C o RC solas, o seguidas de un dígito
• Dentro de un nombre entrecomillado, un apóstrofe se dobla — Bob's Data se escribe 'Bob''s Data'!A1

La regla A1 es la que de verdad encuentran los libros reales, porque un montón de nombres de hoja de negocio resultan ser direcciones de celda válidas. FY2024 es la columna FY, fila 2024. Jan2024 es la columna JAN, fila 2024, y JAN está bien dentro de las 16.384 columnas que tiene una hoja moderna. Q1, CR1 y Ra1 son direcciones también, y los tres hay que entrecomillarlos. Los casos límite van al revés: A0 no tiene fila cero, XFE1 está una columna pasada del borde, XFD1048577 una fila pasada, y ABCD1 tiene cuatro letras, así que Excel deja a todos desnudos. Los ceros delante no rescatan un nombre, y A01 sigue llevando apóstrofes porque el parser lo lee como A1. Un writer que solo mira la clase de caracteres del nombre emitirá FY2024!B7 para un informe del año fiscal, y Excel no puede leer esa fórmula

Los dobles de R1C1 y el caso a favor de entrecomillar de más

Para los nombres con forma R1C1 no hay regla limpia que recuperar de la salida de Excel. Excel entrecomilla R, C, RC, R1, C1, R1x, R1C1x, y también R1C16385 y C99999, que nombran columnas que no existen. Sin embargo deja desnudos R0, C0, RC0, R0C1, R1048577, Rx, Cx y R_1. Ninguna combinación de límites de rejilla y formas de token reproduce ambas listas, así que HotXLS toma un superconjunto: un nombre que sea R, C o RC, o que empiece por una de ellas seguida de un dígito, lleva apóstrofes. El superconjunto entrecomilla algunos nombres que Excel deja desnudos, R0 y R1048577 entre ellos. Es aceptable por una razón llana: la grafía entrecomillada de cualquier nombre de hoja es sintaxis de fórmula válida, así que todo lector que parsea fórmulas la acepta, mientras que una lista que intentara igualar a Excel nombre por nombre se apoyaría en una regla que nadie puede enunciar. Nombres corrientes como Chart1, Region1 y Cost2024 caen fuera de ambas pruebas y se quedan desnudos. Si además trabajas con texto R1C1, las notas sobre la notación de fórmulas R1C1 en HotXLS describen cómo se parsean esas referencias en primer lugar

Los nombres con letras fuera del ASCII también se entrecomillan de más. Excel es más permisivo aquí: escribe Données!A1 y 数据!A1 sin apóstrofes, porque las letras acentuadas y CJK cuentan como caracteres de nombre para su parser. HotXLS lee esos calificadores desnudos al abrir un fichero, porque Excel los produce, pero escribe tales nombres entrecomillados, como en 'Données'!A1. La forma entrecomillada es válida en todas partes, y la prueba del writer se queda en una lista corta y predecible en lugar de una tabla de letras Unicode con la que cada consumidor tendría que estar de acuerdo

Construir calificadores de hoja en Delphi

lxStandard.XlsSheetNameNeedsQuotes es la forma pública de esa regla, y es la función que usan tanto el writer XLSX como el motor clásico al emitir una referencia de hoja. Cuando tu propio código ensambla texto de fórmula a partir de nombres de hoja, por ejemplo una hoja resumen que apunta a una hoja por región o por año, pasa cada nombre por ella en lugar de concatenar Name + '!'. La función solo responde sí o no. Doblar los apóstrofes internos es cosa del llamador, como hace el helper de abajo, y las fórmulas de celda XLSX se asignan sin el = inicial

program SheetQualifiers;

{$APPTYPE CONSOLE}

uses
  System.SysUtils, lxStandard, lxHandleX;

// Calificador para texto de fórmula construido a mano: apóstrofes cuando el nombre
// los necesita, con cualquier apóstrofe dentro del nombre doblado
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;
      // Usa el nombre que la hoja recibió de verdad, no el que se pidió
      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

Un nombre de hoja de trabajo en un fichero XLSX no puede contener : \ / ? * [ ], no puede empezar o acabar en apóstrofe, está limitado a 31 caracteres, debe ser único sin distinguir mayúsculas y no puede ser History, que Excel reserva. TXLSXSheets.Add y el setter de TXLSXWorksheet.Name lo hacen cumplir ambos: los caracteres prohibidos se vuelven guiones bajos, un choque recibe un sufijo (2), y un nombre reservado o vacío recurre a Sheet. El valor que asignas y el valor que lees de vuelta pueden por tanto diferir, y un calificador construido desde el nombre pedido apunta a una hoja que no existe. Lee Name de vuelta tras cada asignación y entrecomilla ese

Sheet := Book.Sheets.Add('Totals');
Sheet.Name := 'Q1/Q2 Sales';                 // '/' no se permite en un nombre de hoja
Writeln(Sheet.Name);                         // Q1_Q2 Sales
Writeln(SheetQualifier(Sheet.Name) + 'C9');  // 'Q1_Q2 Sales'!C9

¿Qué guarda Excel después de borrar una hoja?

Cuando se borra una hoja, Excel sustituye la referencia entera a ella por #REF!, calificador y celda o rango juntos. SUM(Data!A1:B2)+1 se guarda como SUM(#REF!)+1, COUNT(Data!C:C) como COUNT(#REF!), un nombre definido que apuntaba a Data!$A$1:$B$2 como #REF! a secas, y el rango de una serie de gráfico como <c:f>#REF!</c:f>. La vista COM engaña aquí también: Range.Formula muestra =#REF!A1+1, como si la parte de celda sobreviviera, mientras que el XML que Excel guarda no lleva parte de celda alguna. Copiar la visualización COM produce texto de fórmula que el propio Excel nunca escribe — mala apuesta para un fichero que otras herramientas van a leer. TXLSXSheets.DeleteByName escribe la forma almacenada. La misma lógica de colapso a #REF! gobierna las referencias cuyas filas o columnas desaparecen, lo que cubre la pieza sobre cómo insertar y borrar filas ajusta las referencias de las fórmulas para el caso dentro de la hoja

Las referencias tridimensionales se comportan distinto. Excel encoge un tramo 3D en lugar de destruirlo. Con las hojas ordenadas Data, Mid, Keep, borrar Data convierte SUM(Data:Keep!A1) en SUM(Mid:Keep!A1): el extremo que desapareció se mueve una hoja hacia dentro, medido en el orden de pestañas tal como estaba antes del borrado. Con solo dos hojas en el tramo, el resultado es la forma de hoja única SUM(Keep!A1). Borrar Mid no cambia nada de texto de fórmula, ya que el tramo sigue nombrando dos hojas vivas y simplemente cubre una menos en medio. Solo cuando ambos extremos nombran la hoja borrada la referencia se vuelve #REF!. Una referencia muerta cambia lo que devuelve una fórmula, así que HotXLS marca como obsoletos los resultados en caché de cada celda de fórmula que reescribió durante un borrado; llama a Recalculate antes de leer valores. Un renombrado deja los valores en caché tranquilos, porque los números no pueden cambiar

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;   // las celdas que perdieron Data llevan valores en caché obsoletos
    Book.SaveAs('report.xlsx');
  finally
    Book.Free;
  end;
end.

// Esperado:
// Tras eliminar Data:
//   B1: SUM(Mid:Keep!A1)
//   B2: SUM(#REF!)+1
//   B3: Keep!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!
// Tras renombrar Keep a 2025:
//   B1: SUM('Mid:2025'!A1)
//   B2: SUM(#REF!)+1
//   B3: '2025'!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!

Renombrar una hoja reescribe referencias por todo el libro

Asignar TXLSXWorksheet.Name en un libro XLSX lleva el nombre nuevo a todos los lugares donde puede vivir una fórmula, y DeleteByName recorre los mismos lugares para aplicar el colapso a #REF!. La lista va mucho más allá de las fórmulas de celda:

• Fórmulas de celda en todas las hojas. Una fórmula compartida se guarda expandida en cada celda seguidora y una fórmula array vive en su celda raíz, así que ambas quedan cubiertas sin tratamiento especial
• Fórmulas de formato condicional, incluidos los umbrales de tipo fórmula en las barras de datos, las escalas de color y los juegos de iconos
• Fórmulas de validación de datos, como una fuente de lista en otra hoja
• Fórmulas de columnas calculadas y de fila de totales de tablas
• Nombres definidos, la columna vertebral de los modelos construidos sobre nombres definidos y fórmulas entre hojas; en un renombrado, una caché de tabla dinámica que lee su rango fuente de la hoja renombrada también sigue
• Rangos de gráfico: nombres de series, categorías, valores, tamaños de burbuja y rangos de etiquetas de datos, rangos más y menos de las barras de error, y las referencias de celda detrás de los títulos de gráfico y de eje

Un tramo 3D se entrecomilla como un todo cuando cualquiera de los extremos necesita comillas: renombrar Keep a 2025 produce 'Mid:2025'!A1, nunca Mid:'2025'!A1, porque el tramo es un solo calificador. Todo calificador del que el renombrado no va se repite tal como estaba escrito. Si una fórmula en otro sitio dice 'Sheet1'!B4, con apóstrofes técnicamente innecesarios, sigue diciendo eso después. Normalizar esos calificadores al paso sería inofensivo para Excel, pero un renombrado de una sola hoja tocaría entonces cada fórmula del libro, y un diff entre dos versiones de un informe generado enterraría el cambio real bajo cientos de cambios cosméticos

Texto que solo parece una referencia de hoja

Tres clases de texto parecen un calificador Name! a una búsqueda de patrones y no lo son. Dentro de un literal de cadena, "Data!A1" es texto, así que INDIRECT("Data!A1") sigue apuntando al nombre viejo tras un renombrado o un borrado, igual que en Excel; si un informe construye nombres de hoja para INDIRECT, guárdalos en una celda que tu código actualice junto con el renombrado. Un calificador precedido por un índice de libro entre corchetes, como en [1]Data!A1, pertenece a un libro externo que casualmente tiene una hoja llamada Data, y renombrar la hoja Data local no debe tocarlo; un calificador entrecomillado que contiene un [ se trata igual. Y #REF! en sí tiene la forma de un calificador llamado REF, que es por lo que HotXLS comprueba el # previo antes de tratar REF! como nombre de hoja. Sin esa comprobación, renombrar una hoja llamada REF reescribiría cada #REF! del libro

Los nombres de hoja con más probabilidad de romper una fórmula generada son los corrientes, como FY2024, Q1 y 2025, no los nombres con espacios o puntuación. Entrecomíllalos mediante XlsSheetNameNeedsQuotes dondequiera que tu código Delphi construya texto de fórmula. A partir de ahí, un renombrado o borrado en el componente de hojas de cálculo HotXLS para Delphi deja cada fórmula en la forma que el propio Excel habría guardado