2024!A1 scheitert: Excel-Blattnamen in Formeln quoten

Eine Budget-Arbeitsmappe hat ein Blatt namens FY2024, eine Summary-Zelle sagt =FY2024!B7, und Excel kann die Formel nicht lesen. FY2024 ist auch eine Zelladresse, also braucht der Name Apostrophe, wie in 'FY2024'!B7; HotXLS schreibt sie nach derselben Regel, die Excel beim Speichern anwendet, und seine XLSX-Arbeitsmappe schreibt jede Formel um, wenn ein Blatt umbenannt oder gelöscht wird

Welche Blattnamen müssen in einer Formel quotiert sein?

Ein Blattname braucht Apostrophe, wann immer der Formel-Parser ihn als etwas anderes als einen Blattnamen lesen könnte: eine Zahl, einen Boolean oder eine Zellreferenz. Die Formel-Grammatik in ECMA-376 Teil 1 §18.17 deckt quotierte und unquertierte Blattnamen ab, aber die praktische Referenz ist der Text, den Excel in die <f>-Elemente von sheet1.xml schreibt, denn auf diesen Text ist jeder andere Reader geeicht. Excels COM-Echo über Range.Formula und sein gespeichertes XML widersprechen sich bei manchen Namen: R1. und A1.x kommen nackt aus .Formula zurück, tragen aber Apostrophe in der Datei. Eine Regel, die aus dem COM-Echo abgeleitet ist, wird von Excels eigenen Dateien widerlegt. HotXLS folgt der gespeicherten Form, die Quotes verlangt für:

• Jedes Zeichen außerhalb von A-Z, a-z, 0-9 und dem Unterstrich: Leerzeichen, Bindestriche, Punkte, Klammern
• Eine führende Ziffer: 2024, 2nd_Half und 3D werden alle quotiert
• TRUE oder FALSE in jeder Schreibweise. TRUE1 und TRUEX bleiben nackt, da keines als Boolean liest
• Ein Name, der als A1-Zelle im Grid liest: ein bis drei Buchstaben, die eine Spalte bilden, nicht später als XFD, gefolgt von einer Zeile von 1 bis 1048576, führende Nullen erlaubt
• Ein Name, der wie eine R1C1-Referenz beginnt: R, C oder RC allein, oder gefolgt von einer Ziffer
• Innerhalb eines quotierten Namens wird ein Apostroph verdoppelt — Bob's Data wird geschrieben als 'Bob''s Data'!A1

Die A1-Regel ist die, auf die echte Arbeitsmappen stoßen, denn so viele Business-Blattnamen sind zufällig gültige Zelladressen. FY2024 ist Spalte FY, Zeile 2024. Jan2024 ist Spalte JAN, Zeile 2024, und JAN sitzt tief drin in den 16,384 Spalten, die ein modernes Blatt hat. Q1, CR1 und Ra1 sind ebenfalls alle Adressen, und alle drei müssen quotiert werden. Die Grenzfälle laufen andersherum: A0 hat keine Zeile null, XFE1 ist eine Spalte hinter dem Rand, XFD1048577 eine Zeile dahinter, und ABCD1 hat vier Buchstaben, also lässt Excel jeden davon nackt. Führende Nullen retten einen Namen nicht, und A01 bekommt trotzdem Apostrophe, weil der Parser es als A1 liest. Ein Writer, der nur die Zeichenklasse des Namens prüft, wird für einen Geschäftsjahres-Bericht FY2024!B7 ausgeben, und Excel kann diese Formel nicht zurücklesen

R1C1-Doubletten und der Fall fürs Überquotieren

Für R1C1-förmige Namen gibt es keine saubere Regel, um sie aus Excels Output zurückzugewinnen. Excel quotiert R, C, RC, R1, C1, R1x, R1C1x, und außerdem R1C16385 und C99999, die Spalten benennen, die nicht existieren. Doch es lässt R0, C0, RC0, R0C1, R1048577, Rx, Cx und R_1 nackt. Keine Kombination aus Grid-Limits und Token-Formen reproduziert beide Listen, also nimmt HotXLS eine Obermenge: Ein Name, der R, C oder RC ist, oder mit einem davon beginnt, gefolgt von einer Ziffer, bekommt Apostrophe. Die Obermenge quotiert einige Namen, die Excel nackt lässt, R0 und R1048577 unter ihnen. Das ist aus einem schlichten Grund akzeptabel: Die quotierte Schreibweise jedes Blattnamens ist gültige Formel-Syntax, also akzeptiert jeder Reader, der Formeln parst, sie, während eine Liste, die Excel Name für Name nachzubilden versuchte, auf einer Regel ruhte, die niemand angeben kann. Gewöhnliche Namen wie Chart1, Region1 und Cost2024 fallen aus beiden Tests heraus und bleiben nackt. Falls Sie auch mit R1C1-Text arbeiten, beschreiben die Notizen zu R1C1-Formel-Notation in HotXLS, wie diese Referenzen überhaupt geparst werden

Namen mit Buchstaben außerhalb von ASCII werden ebenfalls überquotiert. Excel ist hier großzügiger. Excel schreibt Données!A1 und 数据!A1 ohne Apostrophe, weil akzentuierte und CJK-Buchstaben für seinen Parser als Namenszeichen zählen. HotXLS liest diese nackten Qualifier beim Öffnen einer Datei, da Excel sie produziert, schreibt solche Namen aber quotiert, wie in 'Données'!A1. Die quotierte Form ist überall gültig, und der Test des Writers bleibt eine kurze, vorhersagbare Liste statt einer Unicode-Buchstabentabelle, mit der jeder Consumer übereinstimmen müsste

Sheet-Qualifier in Delphi bauen

lxStandard.XlsSheetNameNeedsQuotes ist die öffentliche Form dieser Regel, und es ist die Funktion, die der XLSX-Writer und die klassische Engine beide benutzen, wenn sie eine Blattreferenz ausgeben. Wenn Ihr eigener Code Formeltext aus Blattnamen zusammensetzt, etwa ein Summary-Blatt, das auf ein Blatt pro Region oder pro Jahr zeigt, schicken Sie jeden Namen durch sie, statt Name + '!' zu konkatenieren. Die Funktion antwortet nur ja oder nein. Innere Apostrophe zu verdoppeln ist Sache des Callers, wie der Helper unten es tut, und XLSX-Zellformeln werden ohne das führende = zugewiesen

program SheetQualifiers;

{$APPTYPE CONSOLE}

uses
  System.SysUtils, lxStandard, lxHandleX;

// Qualifier für handgebauten Formeltext: Apostrophe, wenn der Name
// sie braucht, mit jedem Apostroph im Namen verdoppelt
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;
      // Den Namen benutzen, den das Sheet tatsächlich bekam, nicht den angeforderten
      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.

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

Ein Worksheet-Name in einer XLSX-Datei darf : \ / ? * [ ] nicht enthalten, darf nicht mit einem Apostroph beginnen oder enden, ist auf 31 Zeichen begrenzt, muss unabhängig von Groß-/Kleinschreibung einzigartig sein und darf nicht History sein, das Excel reserviert. TXLSXSheets.Add und der TXLSXWorksheet.Name-Setter setzen beide das durch: Verbotene Zeichen werden zu Unterstrichen, ein Konflikt bekommt einen (2)-Suffix, und ein reservierter oder leerer Name fällt auf Sheet zurück. Der Wert, den Sie zuweisen, und der Wert, den Sie zurücklesen, können sich also unterscheiden, und ein Qualifier aus dem angeforderten Namen zeigt auf ein Blatt, das nicht existiert. Lesen Sie Name nach jeder Zuweisung zurück und quotieren Sie das

Sheet := Book.Sheets.Add('Totals');
Sheet.Name := 'Q1/Q2 Sales';                 // '/' ist in einem Sheet-Namen nicht erlaubt
Writeln(Sheet.Name);                         // Q1_Q2 Sales
Writeln(SheetQualifier(Sheet.Name) + 'C9');  // 'Q1_Q2 Sales'!C9

Was speichert Excel, nachdem ein Blatt gelöscht wurde?

Wird ein Blatt gelöscht, ersetzt Excel die ganze Referenz auf es durch #REF!, Qualifier und Zelle oder Range zusammen. SUM(Data!A1:B2)+1 wird als SUM(#REF!)+1 gespeichert, COUNT(Data!C:C) als COUNT(#REF!), ein definierter Name, der auf Data!$A$1:$B$2 zeigte, als nacktes #REF!, und eine Chart-Serien-Range als <c:f>#REF!</c:f>. Die COM-Sicht täuscht auch hier: Range.Formula zeigt =#REF!A1+1, als wäre der Zellteil überlebt, während das XML, das Excel speichert, überhaupt keinen Zellteil hat. Die COM-Anzeige zu kopieren liefert Formeltext, den Excel selbst nie schreibt — eine schlechte Wette für eine Datei, die andere Tools lesen werden. TXLSXSheets.DeleteByName schreibt die gespeicherte Form. Dieselbe Kollaps-zu-#REF!-Logik regelt Referenzen, deren Zeilen oder Spalten verschwinden, was der Beitrag zu wie Einfügen und Löschen von Zeilen Formelreferenzen anpasst für den In-Sheet-Fall abdeckt

Dreidimensionale Referenzen benehmen sich anders. Excel schrumpft eine 3D-Spanne, statt sie zu zerstören. Bei Blättern in der Ordnung Data, Mid, Keep macht das Löschen von Data aus SUM(Data:Keep!A1) ein SUM(Mid:Keep!A1): Das verschwundene Ende rückt ein Blatt nach innen, gemessen in der Tabreihenfolge, wie sie vor dem Löschen stand. Bei nur zwei Blättern in der Spanne ist das Ergebnis die Einzelblatt-Form SUM(Keep!A1). Mid zu löschen ändert überhaupt keinen Formeltext, denn die Spanne nennt weiterhin zwei lebende Blätter und deckt nur eines weniger dazwischen ab. Erst wenn beide Enden das gelöschte Blatt benennen, wird die Referenz zu #REF!. Eine tote Referenz ändert, was eine Formel zurückgibt, also markiert HotXLS das gecachte Ergebnis jeder Formelzelle, die es während eines Löschens umgeschrieben hat, als stale; rufen Sie Recalculate auf, bevor Sie Werte lesen. Ein Umbenennen lässt gecachte Werte in Ruhe, da sich die Zahlen nicht ändern können

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;   // Zellen, die Data verloren haben, tragen stale Cached Values
    Book.SaveAs('report.xlsx');
  finally
    Book.Free;
  end;
end.

// Erwartet:
// Nach dem Löschen von Data:
//   B1: SUM(Mid:Keep!A1)
//   B2: SUM(#REF!)+1
//   B3: Keep!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!
// Nach dem Umbenennen von Keep in 2025:
//   B1: SUM('Mid:2025'!A1)
//   B2: SUM(#REF!)+1
//   B3: '2025'!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!

Ein Blatt umzubenennen schreibt Referenzen in der ganzen Arbeitsmappe um

TXLSXWorksheet.Name in einer XLSX-Arbeitsmappe zuzuweisen trägt den neuen Namen in jeden Ort, an dem eine Formel wohnen kann, und DeleteByName geht dieselben Orte ab, um den #REF!-Kollaps anzuwenden. Die Liste geht weit über Zellformeln hinaus:

• Zellformeln auf jedem Blatt. Eine Shared Formula liegt auf jeder Folgezelle expandiert vor, und eine Array-Formel wohnt auf ihrer Root-Zelle, also sind beide ohne Sonderbehandlung abgedeckt
• Conditional-Format-Formeln, eingeschlossen Formel-Schwellen in Datenbalken, Farbskalen und Symbolsätzen
• Data-Validation-Formeln, etwa eine Listenquelle auf einem anderen Blatt
• Tabellen-Formeln für berechnete Spalten und Summenzeilen
• Definierte Namen, das Rückgrat von Modellen, die auf definierten Namen und blattübergreifenden Formeln bauen; bei einer Umbenennung zieht ein Pivot Cache, der seine Quell-Range vom umbenannten Blatt liest, mit
• Chart-Ranges: Seriennamen, Kategorien, Werte, Bubble-Größen und Datenbeschriftungs-Ranges, Fehlerbalken-Plus-und-Minus-Ranges, und die Zellreferenzen hinter Chart- und Achsentiteln

Eine 3D-Spanne wird als Ganzes quotiert, wenn irgendein Ende Quotes braucht: Keep zu 2025 umzubenennen erzeugt 'Mid:2025'!A1, nie Mid:'2025'!A1, denn die Spanne ist ein Qualifier. Jeder Qualifier, um den die Umbenennung nicht geht, wird wiedergegeben, wie geschrieben. Sagt eine Formel anderswo 'Sheet1'!B4, mit technisch unnötigen Apostrophen, sagt sie das danach weiterhin. Diese Qualifier im Vorbeigehen zu normalisieren wäre für Excel harmlos, aber eine Ein-Blatt-Umbenennung würde dann jede Formel in der Arbeitsmappe anfassen, und ein Diff zwischen zwei Versionen eines generierten Berichts würde die echte Änderung unter hunderten kosmetischen begraben

Text, der nur wie eine Blattreferenz aussieht

Drei Arten von Text sehen für eine Mustersuche wie ein Name!-Qualifier aus und sind keine. Innerhalb eines String-Literals ist "Data!A1" Text, also zeigt INDIRECT("Data!A1") nach einer Umbenennung oder einem Löschen weiter auf den alten Namen, ebenso wie in Excel; baut ein Bericht Blattnamen für INDIRECT, halten Sie sie in einer Zelle, die Ihr Code neben der Umbenennung aktualisiert. Ein Qualifier, dem ein eingeklammerter Buch-Index vorangeht, wie in [1]Data!A1, gehört zu einer externen Arbeitsmappe, die zufällig ein Blatt namens Data hat, und das lokale Data-Blatt umzubenennen darf es nicht anfassen; ein quotierter Qualifier, der ein [ enthält, wird genauso behandelt. Und #REF! selbst hat die Gestalt eines Qualifiers namens REF, weshalb HotXLS nach dem vorangehenden # sucht, bevor es REF! als Blattnamen behandelt. Ohne diese Prüfung würde das Umbenennen eines Blatts namens REF jedes #REF! in der Arbeitsmappe umschreiben

Die Blattnamen, die mit der größten Wahrscheinlichkeit eine generierte Formel brechen, sind gewöhnliche wie FY2024, Q1 und 2025, nicht Namen mit Leerzeichen oder Interpunktion. Quotieren Sie sie über XlsSheetNameNeedsQuotes, wo auch immer Ihr Delphi-Code Formeltext baut. Von da an lässt eine Umbenennung oder ein Löschen in der HotXLS Delphi Spreadsheet-Komponente jede Formel in der Form, in der Excel selbst sie gespeichert hätte