2024!A1 falla: entrecomillar nombres de hojas 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 también es una dirección de celda, así que el nombre necesita apóstrofes, como en 'FY2024'!B7; HotXLS los escribe según la misma regla que Excel aplica al guardar, y su libro XLSX reescribe cada fórmula cuando una hoja se renombra o se borra

¿Qué nombres de hoja deben ir entrecomillados en una fórmula?

Un nombre de hoja necesita apóstrofes siempre que el parser de fórmulas pudiera leerlo como otra cosa: un número, un booleano o una referencia de celda. La gramática de fórmulas de ECMA-376 Parte 1 §18.17 cubre nombres de hoja entrecomillados y sin entrecomillar, 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 afinó todo otro lector. El eco del Range.Formula del COM de Excel y su XML guardado discrepan para algunos nombres: R1. y A1.x vuelven desnudos desde .Formula pero llevan apóstrofes en el archivo. Una regla derivada del eco del COM queda contradicha por los propios archivos 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 inicial: 2024, 2nd_Half y 3D van todos entrecomillados
• TRUE o FALSE en cualquier combinación de mayúsculas. TRUE1 y TRUEX quedan desnudos, porque ninguno se lee como booleano
• Un nombre que se lee como celda A1 dentro de la grilla: de una a tres letras formando una columna no posterior a XFD, seguidas de una fila de 1 a 1048576, con ceros a la izquierda permitidos
• Un nombre que arranca como referencia R1C1: R, C o RC solos, o seguidos 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 muchos nombres de hoja de negocios resultan ser direcciones de celda válidas. FY2024 es la columna FY, fila 2024. Jan2024 es la columna JAN, fila 2024, y JAN queda bien adentro de las 16,384 columnas que tiene una hoja moderna. Q1, CR1 y Ra1 son todas direcciones también, y las tres deben ir entrecomilladas. Los casos de frontera van al revés: A0 no tiene fila cero, XFE1 está una columna más allá del borde, XFD1048577 una fila más allá, y ABCD1 tiene cuatro letras, así que Excel deja a todos desnudos. Los ceros a la izquierda no rescatan a un nombre, y A01 igual recibe apóstrofes porque el parser lo lee como A1. Un escritor que solo revisa la clase de caracteres del nombre emitirá FY2024!B7 para un reporte de año fiscal, y Excel no puede leer esa fórmula de vuelta

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

Para nombres con forma R1C1 no hay regla limpia que permita recuperar el criterio desde 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 grilla y formas de token reproduce ambas listas, así que HotXLS toma un superconjunto: un nombre que sea R, C o RC, o que empiece con uno de ellos seguido de un dígito, recibe apóstrofes. El superconjunto entrecomilla algunos nombres que Excel deja desnudos, R0 y R1048577 entre ellos. Eso es aceptable por una razón simple: 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 descansaría en una regla que nadie puede enunciar. Nombres comunes como Chart1, Region1 y Cost2024 caen fuera de ambas pruebas y quedan desnudos. Si además trabaja 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 quedan sobreentrecomillados. Excel es más indulgente 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 cuando abre un archivo, ya que 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 escritor se queda siendo 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 tanto el escritor XLSX como el motor clásico usan cuando emiten una referencia de hoja. Cuando su propio código arma texto de fórmulas a partir de nombres de hoja — por ejemplo, una hoja de resumen que apunta a una hoja por región o por año — pase cada nombre por ella en lugar de concatenar Name + '!'. La función solo responde sí o no. Doblar los apóstrofes internos es asunto 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 hecho a mano: apóstrofes cuando el nombre
// los necesita, con todo 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;
      // Use el nombre que la hoja realmente recibió, no el solicitado
      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 en un archivo XLSX no puede contener : \ / ? * [ ], no puede empezar ni terminar con apóstrofe, se topa en 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 ambos lo hacen cumplir: 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 usted asigna y el que lee de vuelta pueden diferir, y un calificador construido desde el nombre solicitado apunta a una hoja que no existe. Lea el Name de vuelta después de cada asignación y entrecomille 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 una hoja se borra, Excel reemplaza la referencia completa hacia ella con #REF!, calificador y celda o rango juntos. SUM(Data!A1:B2)+1 se almacena como SUM(#REF!)+1, COUNT(Data!C:C) como COUNT(#REF!), un defined name que apuntaba a Data!$A$1:$B$2 como #REF! pelado, y un rango de serie de gráfico como <c:f>#REF!</c:f>. La vista del COM también engaña aquí: Range.Formula muestra =#REF!A1+1, como si la parte de la celda sobreviviera, mientras que el XML que Excel guarda no tiene parte de celda alguna. Copiar la pantalla del COM produce texto de fórmula que el propio Excel nunca escribe — una mala apuesta para un archivo que otras herramientas leerán. TXLSXSheets.DeleteByName escribe la forma almacenada. La misma lógica de colapsar 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 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 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 adentro, 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 texto de fórmula alguno, porque el tramo sigue nombrando dos hojas vivas y simplemente cubre una menos en el 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 obsoleto el resultado en caché de cada celda de fórmula que reescribió durante un borrado; llame 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 cada lugar 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 cada hoja. 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 manejo especial
• Fórmulas de formato condicional, incluidos los umbrales de tipo fórmula en data bars, color scales e icon sets
• 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
• Defined names, la columna vertebral de los modelos construidos sobre defined names y fórmulas entre hojas; en un renombrado, un pivot cache que lee su rango fuente de la hoja renombrada también sigue
• Rangos de gráficos: nombres de series, categorías, valores, tamaños de burbuja y rangos de etiquetas de datos, rangos más y menos de 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 se ocupa se replica tal como estaba escrito. Si una fórmula en otro lugar dice 'Sheet1'!B4, con apóstrofes técnicamente innecesarios, después sigue diciendo eso. Normalizar esos calificadores de 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 reporte 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! para una búsqueda de patrones y no lo son. Dentro de un string literal, "Data!A1" es texto, así que INDIRECT("Data!A1") sigue apuntando al nombre viejo después de un renombrado o un borrado, igual que en Excel; si un reporte arma nombres de hoja para INDIRECT, guarde los nombres en una celda que su 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 el propio #REF! tiene la forma de un calificador llamado REF, razón por la que HotXLS verifica el # precedente antes de tratar REF! como nombre de hoja. Sin esa verificació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 comunes, como FY2024, Q1 y 2025, no los nombres con espacios o puntuación. Entrecomíllelos vía XlsSheetNameNeedsQuotes dondequiera que su código Delphi arma texto de fórmulas. De ahí en adelante, 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