2024!A1 non funziona: citare i nomi dei fogli nelle formule

Una cartella di budget ha un foglio di nome FY2024, una cella di riepilogo dice =FY2024!B7, ed Excel non riesce a leggere la formula. FY2024 è anche un indirizzo di cella, quindi il nome ha bisogno di apostrofi, come in 'FY2024'!B7; HotXLS li scrive con la stessa regola che Excel applica quando salva, e la sua cartella XLSX riscrive ogni formula quando un foglio viene rinominato o eliminato

Quali nomi di foglio vanno citati in una formula?

Un nome di foglio richiede apostrofi ogni volta che il parser delle formule potrebbe leggerlo come qualcosa di diverso da un nome di foglio: un numero, un booleano o un riferimento di cella. La grammatica delle formule in ECMA-376 Parte 1 §18.17 copre i nomi di foglio citati e non citati, ma il riferimento pratico è il testo che Excel scrive negli elementi <f> di sheet1.xml, perché è quel testo contro cui è stato tarato ogni altro reader. L'eco COM di Range.Formula di Excel e il suo XML salvato non sono d'accordo per alcuni nomi: R1. e A1.x tornano nudi da .Formula ma portano apostrofi nel file. Una regola derivata dall'eco COM è contraddetta dai file di Excel stesso. HotXLS segue la forma conservata, che richiede virgolette per:

• Qualsiasi carattere fuori da A-Z, a-z, 0-9 e underscore: spazi, trattini, punti, parentesi
• Una cifra iniziale: 2024, 2nd_Half e 3D vanno tutti citati
• TRUE o FALSE con qualsiasi combinazione di maiuscole e minuscole. TRUE1 e TRUEX restano nudi, perché nessuno dei due si legge come booleano
• Un nome che si legge come cella A1 dentro la griglia: da una a tre lettere che formano una colonna non oltre XFD, seguite da una riga da 1 a 1048576, zeri iniziali ammessi
• Un nome che inizia come riferimento R1C1: R, C o RC da soli, o seguiti da una cifra
• Dentro un nome citato, un apostrofo si raddoppia — Bob's Data viene scritto 'Bob''s Data'!A1

La regola A1 è quella in cui inciampano i workbook veri, perché tanti nomi di foglio aziendali sono per caso indirizzi di cella validi. FY2024 è la colonna FY, riga 2024. Jan2024 è la colonna JAN, riga 2024, e JAN sta ben dentro le 16.384 colonne che un foglio moderno ha. Q1, CR1 e Ra1 sono tutti indirizzi, e tutti e tre vanno citati. I casi limite vanno nell'altra direzione: A0 non ha riga zero, XFE1 è una colonna oltre il bordo, XFD1048577 è una riga oltre, e ABCD1 ha quattro lettere, quindi Excel lascia tutti nudi. Gli zeri iniziali non salvano un nome, e A01 prende comunque gli apostrofi perché il parser lo legge come A1. Un writer che controlla solo la classe di caratteri del nome emetterà FY2024!B7 per un report di esercizio, ed Excel non riesce a rileggersi quella formula

Sosia R1C1 e il caso per citare più del necessario

Per i nomi dalla forma R1C1 non c'è una regola pulita da recuperare dall'output di Excel. Excel cita R, C, RC, R1, C1, R1x, R1C1x, e anche R1C16385 e C99999, che nominano colonne che non esistono. Eppure lascia nudi R0, C0, RC0, R0C1, R1048577, Rx, Cx e R_1. Nessuna combinazione di limiti della griglia e forme di token riproduce entrambe le liste, quindi HotXLS prende un sovrainsieme: un nome che è R, C o RC, o che inizia con uno di questi seguito da una cifra, prende gli apostrofi. Il sovrainsieme cita qualche nome che Excel lascia nudo, R0 e R1048577 fra questi. È accettabile per una ragione semplice: la forma citata di qualsiasi nome di foglio è sintassi di formula valida, quindi la accetta ogni reader che fa il parse delle formule, mentre una lista che provasse a eguagliare Excel nome per nome riposerebbe su una regola che nessuno sa enunciare. I nomi comuni tipo Chart1, Region1 e Cost2024 cadono fuori da entrambi i test e restano nudi. Se lavorate anche con testo R1C1, le note su la notazione di formula R1C1 in HotXLS descrivono come vengono fatti il parse quei riferimenti

Anche i nomi con lettere fuori dall'ASCII vengono citati in eccesso. Excel qui è più permissivo: scrive Données!A1 e 数据!A1 senza apostrofi, perché le lettere accentate e CJK contano come caratteri da nome per il suo parser. HotXLS legge quei qualificatori nudi quando apre un file, visto che Excel li produce, ma scrive tali nomi citati, come in 'Données'!A1. La forma citata è valida ovunque, e il test del writer resta una lista corta e prevedibile invece di una tabella di lettere Unicode con cui ogni consumatore dovrebbe essere d'accordo

Costruire qualificatori di foglio in Delphi

lxStandard.XlsSheetNameNeedsQuotes è la forma pubblica di quella regola, ed è la funzione che sia il writer XLSX che il motore classico usano quando emettono un riferimento di foglio. Quando il vostro codice assembla testo di formula dai nomi dei fogli, diciamo un foglio di riepilogo che punta a un foglio per regione o per anno, fate passare ogni nome da lì invece di concatenare Name + '!'. La funzione risponde solo sì o no. Raddoppiare gli apostrofi interni spetta al chiamante, come fa l'helper qui sotto, e le formule delle celle XLSX si assegnano senza il = iniziale

program SheetQualifiers;

{$APPTYPE CONSOLE}

uses
  System.SysUtils, lxStandard, lxHandleX;

// Qualificatore per testo di formula costruito a mano: apostrofi quando
// il nome li richiede, con qualsiasi apostrofo dentro il nome raddoppiato
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;
      // Usate il nome che il foglio ha davvero preso, non quello richiesto
      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.

// Atteso:
// 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 nome di foglio in un file XLSX non può contenere : \ / ? * [ ], non può iniziare o finire con un apostrofo, è limitato a 31 caratteri, deve essere unico a prescindere dal maiuscolo/minuscolo e non può essere History, che Excel si riserva. TXLSXSheets.Add e il setter di TXLSXWorksheet.Name lo impongono entrambi: i caratteri vietati diventano underscore, un conflitto prende un suffisso (2), e un nome riservato o vuoto ripiega su Sheet. Il valore che assegnate e il valore che rileggete possono quindi differire, e un qualificatore costruito col nome richiesto punta a un foglio che non esiste. Rileggete Name dopo ogni assegnazione e citate quello

Sheet := Book.Sheets.Add('Totals');
Sheet.Name := 'Q1/Q2 Sales';                 // '/' non è ammesso in un nome di foglio
Writeln(Sheet.Name);                         // Q1_Q2 Sales
Writeln(SheetQualifier(Sheet.Name) + 'C9');  // 'Q1_Q2 Sales'!C9

Cosa conserva Excel dopo l'eliminazione di un foglio?

Quando un foglio viene eliminato, Excel sostituisce l'intero riferimento con #REF!, qualificatore e cella o range insieme. SUM(Data!A1:B2)+1 viene conservato come SUM(#REF!)+1, COUNT(Data!C:C) come COUNT(#REF!), un defined name che puntava a Data!$A$1:$B$2 come semplice #REF!, e il range di una serie di un grafico come <c:f>#REF!</c:f>. Anche la vista COM inganna qui: Range.Formula mostra =#REF!A1+1, come se la parte di cella fosse sopravvissuta, mentre l'XML che Excel salva non ha alcuna parte di cella. Copiare la visualizzazione COM produce testo di formula che Excel stesso non scrive mai — una brutta scommessa per un file che altri strumenti dovranno leggere. TXLSXSheets.DeleteByName scrive la forma conservata. La stessa logica di collasso in #REF! governa i riferimenti le cui righe o colonne scompaiono, cosa che il pezzo su come inserire ed eliminare righe aggiusta i riferimenti delle formule copre per il caso dentro il foglio

I riferimenti tridimensionali si comportano diversamente. Excel restringe uno span 3D invece di distruggerlo. Con i fogli ordinati Data, Mid, Keep, eliminare Data trasforma SUM(Data:Keep!A1) in SUM(Mid:Keep!A1): l'estremità sparita si sposta di un foglio verso l'interno, misurata nell'ordine delle linguette così com'era prima dell'eliminazione. Con soli due fogli nello span, il risultato è la forma a foglio singolo SUM(Keep!A1). Eliminare Mid non cambia affatto il testo delle formule, dato che lo span nomina ancora due fogli vivi e copre semplicemente uno di meno in mezzo. Solo quando entrambe le estremità nominano il foglio eliminato il riferimento diventa #REF!. Un riferimento morto cambia ciò che una formula restituisce, quindi HotXLS marca come stanti i risultati in cache di ogni cella formula riscritta durante un'eliminazione; chiamate Recalculate prima di leggere i valori. Un rinomina lascia in pace i valori in cache, dato che i numeri non possono cambiare

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;   // le celle che hanno perso Data portano valori in cache stanti
    Book.SaveAs('report.xlsx');
  finally
    Book.Free;
  end;
end.

// Atteso:
// Dopo aver eliminato Data:
//   B1: SUM(Mid:Keep!A1)
//   B2: SUM(#REF!)+1
//   B3: Keep!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!
// Dopo aver rinominato Keep in 2025:
//   B1: SUM('Mid:2025'!A1)
//   B2: SUM(#REF!)+1
//   B3: '2025'!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!

Rinominare un foglio riscrive i riferimenti in tutta la cartella

Assegnare TXLSXWorksheet.Name in una cartella XLSX porta il nuovo nome in ogni posto in cui una formula può vivere, e DeleteByName percorre gli stessi posti per applicare il collasso in #REF!. La lista va ben oltre le formule di cella:

• Formule di cella su ogni foglio. Una formula condivisa sta espansa su ogni cella che la segue e una formula array vive sulla propria cella radice, quindi entrambe sono coperte senza gestione speciale
• Formule di formattazione condizionale, incluse le soglie di tipo formula nelle barre dati, nelle scale di colore e nei set di icone
• Formule di convalida dati, come una sorgente di elenco su un altro foglio
• Formule di colonna calcolata e riga dei totali delle tabelle
• Defined name, la spina dorsale dei modelli costruiti su defined name e formule tra fogli; a un rinomina segue anche una pivot cache che legge il proprio range sorgente dal foglio rinominato
• Range dei grafici: nomi delle serie, categorie, valori, dimensioni bubble e range delle etichette dati, range più e meno delle barre di errore, e i riferimenti di cella dietro i titoli di grafico e di asse

Uno span 3D viene citato come blocco unico quando una delle estremità ha bisogno di virgolette: rinominare Keep in 2025 produce 'Mid:2025'!A1, mai Mid:'2025'!A1, perché lo span è un qualificatore solo. Ogni qualificatore del quale il rinomina non si occupa viene risperto così com'è scritto. Se una formula altrove dice 'Sheet1'!B4, con apostrofi tecnicamente superflui, continua a dirlo anche dopo. Normalizzare quei qualificatori di passaggio sarebbe innocuo per Excel, ma un rinomina di un solo foglio toccherebbe allora ogni formula della cartella, e un diff fra due versioni di un report generato nasconderebbe la modifica vera sotto centinaia di modifiche estetiche

Testo che sembra solo un riferimento di foglio

Tre tipi di testo sembrano un qualificatore Name! a una ricerca per pattern e non lo sono. Dentro una stringa letterale, "Data!A1" è testo, quindi INDIRECT("Data!A1") continua a puntare al vecchio nome dopo un rinomina o un'eliminazione, come in Excel; se un report costruisce nomi di foglio per INDIRECT, teneteli in una cella che il vostro codice aggiorna insieme al rinomina. Un qualificatore preceduto da un indice di libro fra parentesi quadre, come in [1]Data!A1, appartiene a una cartella esterna che per caso ha un foglio chiamato Data, e rinominare il foglio Data locale non deve toccarlo; un qualificatore citato che contiene una [ viene trattato allo stesso modo. E #REF! in persona ha la forma di un qualificatore di nome REF, ed è per questo che HotXLS controlla la # precedente prima di trattare REF! come un nome di foglio. Senza quel controllo, rinominare un foglio chiamato REF riscriverebbe ogni #REF! della cartella

I nomi di foglio più probabili per rompere una formula generata sono quelli comuni tipo FY2024, Q1 e 2025, non i nomi con spazi o punteggiatura. Citateli tramite XlsSheetNameNeedsQuotes ovunque il vostro codice Delphi costruisca testo di formula. Da lì in poi, un rinomina o un'eliminazione nel componente fogli di calcolo HotXLS per Delphi lascia ogni formula nella forma che Excel stesso avrebbe salvato