2024!A1 faalt: bladnamen tussen quotes in Excel-formules

Een budgetwerkboek heeft een blad genaamd FY2024, een samenvattingscel zegt =FY2024!B7, en Excel kan de formule niet lezen. FY2024 is ook een celadres, dus de naam heeft apostrofs nodig, zoals in 'FY2024'!B7; HotXLS schrijft ze volgens dezelfde regel die Excel toepast bij het opslaan, en zijn XLSX-werkboek herschrijft elke formule wanneer een blad wordt hernoemd of verwijderd

Welke bladnamen moeten gequote in een formule?

Een bladnaam heeft apostrofs nodig zodra de formule-parser hem als iets anders dan een bladnaam zou kunnen lezen: een getal, een boolean of een celverwijzing. De formulegrammatica in ECMA-376 Part 1 §18.17 dekt gequote en ongequote bladnamen, maar de praktische referentie is de tekst die Excel in de elementen <f> van sheet1.xml schrijft, want dat is de tekst waarop elke andere reader is afgestemd. De COM-echo Range.Formula van Excel en zijn opgeslagen XML spreken elkaar voor sommige namen tegen: R1. en A1.x komen kaal terug uit .Formula maar dragen apostrofs in het bestand. Een regel afgeleid uit de COM-echo wordt tegengesproken door de eigen bestanden van Excel. HotXLS volgt de opgeslagen vorm, die quotes vereist voor:

• Elk teken buiten A-Z, a-z, 0-9 en de underscore: spaties, koppeltekens, punten, haakjes
• Een voorloopcijfer: 2024, 2nd_Half en 3D worden allemaal gequote
• TRUE of FALSE in elke hoofdlettercombinatie. TRUE1 en TRUEX blijven kaal, want geen van beide leest als boolean
• Een naam die als een A1-cel binnen het raster leest: één tot drie letters die een kolom vormen tot hooguit XFD, gevolgd door een rij van 1 tot 1048576, voorloopnullen toegestaan
• Een naam die begint als een R1C1-verwijzing: R, C of RC op zichzelf, of gevolgd door een cijfer
• Binnen een gequote naam wordt een apostrof verdubbeld — Bob's Data wordt weggeschreven als 'Bob''s Data'!A1

De A1-regel is degene waar echte werkboeken op uitkomen, want zoveel zakelijke bladnamen zijn toevallig geldige celadressen. FY2024 is kolom FY, rij 2024. Jan2024 is kolom JAN, rij 2024, en JAN zit ruim binnen de 16.384 kolommen die een modern blad heeft. Q1, CR1 en Ra1 zijn ook allemaal adressen, en alle drie moeten gequote. De grensgevallen gaan de andere kant op: A0 heeft geen rij nul, XFE1 staat één kolom voorbij de rand, XFD1048577 één rij erlangs, en ABCD1 heeft vier letters, dus Excel laat ze allemaal kaal. Voorloopnullen redden een naam niet, en A01 krijgt nog steeds apostrofs omdat de parser hem als A1 leest. Een writer die alleen de tekenclass van de naam controleert levert voor een jaarrapport FY2024!B7 af, en Excel kan die formule niet teruglezen

R1C1-lookalikes en het pleidooi voor over-quoting

Voor R1C1-vormige namen bestaat geen schone regel om uit de uitvoer van Excel te herstellen. Excel quote R, C, RC, R1, C1, R1x, R1C1x, en ook R1C16385 en C99999, die kolommen benoemen die niet bestaan. Toch laat hij R0, C0, RC0, R0C1, R1048577, Rx, Cx en R_1 kaal. Geen enkele combinatie van rasterlimieten en tokenvormen reproduceert beide lijsten, dus HotXLS neemt een superset: een naam die R, C of RC is, of begint met een van die drie gevolgd door een cijfer, krijgt apostrofs. De superset quote een paar namen die Excel kaal laat, R0 en R1048577 erbij. Dat is acceptabel om een kale reden: de gequote spelling van elke bladnaam is geldige formulesyntaxis, dus elke reader die formules parseert accepteert haar, terwijl een lijst die Excel naam voor naam zou naapen rusten zou op een regel die niemand kan formuleren. Gewone namen als Chart1, Region1 en Cost2024 vallen buiten beide toetsen en blijven kaal. Werkt u ook met R1C1-tekst, de notities bij R1C1-formulenotatie in HotXLS beschrijven hoe die verwijzingen in de eerste plaats worden geparseerd

Namen met letters buiten ASCII worden ook overgequote. Excel is hier soepeler. Excel schrijft Données!A1 en 数据!A1 zonder apostrofs, want geaccentueerde en CJK-letters tellen als naamtekens voor zijn parser. HotXLS leest die kale kwalificatoren bij het openen van een bestand, want Excel produceert ze, maar schrijft zulke namen gequote, zoals in 'Données'!A1. De gequote vorm is overal geldig, en de toets van de writer blijft een korte, voorspelbare lijst in plaats van een Unicode-lettertabel waarmee elke consumer het zou moeten eens zijn

Blad-kwalificatoren bouwen in Delphi

lxStandard.XlsSheetNameNeedsQuotes is de publieke vorm van die regel, en het is de functie die zowel de XLSX-writer als de klassieke engine gebruiken wanneer ze een bladverwijzing uitschrijven. Assembleert uw eigen code formuletekst uit bladnamen, bijvoorbeeld een samenvattingsblad dat per regio of per jaar naar één blad wijst, stuur dan elke naam erdoorheen in plaats van Name + '!' aan elkaar te plakken. De functie antwoordt alleen ja of nee. Binnenste apostrofs verdubbelen is aan de aanroeper, zoals de helper hieronder doet, en XLSX-celformules worden toegewezen zonder de voorloop-=

program SheetQualifiers;

{$APPTYPE CONSOLE}

uses
  System.SysUtils, lxStandard, lxHandleX;

// Kwalificator voor handgebouwde formuletekst: apostrofs wanneer de naam
// ze nodig heeft, met elke apostrof binnen de naam verdubbeld
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;
      // Gebruik de naam die het blad echt kreeg, niet de gevraagde
      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.

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

Een werkbladnaam in een XLSX-bestand mag geen : \ / ? * [ ] bevatten, mag niet met een apostrof beginnen of eindigen, stopt bij 31 tekens, moet uniek zijn ongeacht hoofdletters, en mag niet History zijn, dat Excel reserveert. TXLSXSheets.Add en de setter van TXLSXWorksheet.Name handhaven dat allebei: verboden tekens worden underscores, een botsing krijgt een suffix (2), en een gereserveerde of lege naam valt terug op Sheet. De waarde die u toewijst en de waarde die u terugleest kunnen dus verschillen, en een kwalificator gebouwd op de gevraagde naam wijst naar een blad dat niet bestaat. Lees Name na elke toewijzing terug en quote die

Sheet := Book.Sheets.Add('Totals');
Sheet.Name := 'Q1/Q2 Sales';                 // '/' mag niet in een bladnaam
Writeln(Sheet.Name);                         // Q1_Q2 Sales
Writeln(SheetQualifier(Sheet.Name) + 'C9');  // 'Q1_Q2 Sales'!C9

Wat slaat Excel op nadat een blad is verwijderd?

Wanneer een blad wordt verwijderd vervangt Excel de hele verwijzing ernaartoe door #REF!, kwalificator en cel of range samen. SUM(Data!A1:B2)+1 wordt opgeslagen als SUM(#REF!)+1, COUNT(Data!C:C) als COUNT(#REF!), een defined name die naar Data!$A$1:$B$2 wees als kale #REF!, en een chart-series-range als <c:f>#REF!</c:f>. De COM-weergave is hier ook misleidend: Range.Formula toont =#REF!A1+1, alsof het celddeel overleefde, terwijl de XML die Excel opslaat helemaal geen celddeel heeft. De COM-weergave kopiëren levert formuletekst op die Excel zelf nooit schrijft — een slechte gok voor een bestand dat andere tools zullen lezen. TXLSXSheets.DeleteByName schrijft de opgeslagen vorm. Dezelfde ineenstorting-tot-#REF!-logica stuurt verwijzingen aan waarvan rijen of kolommen verdwijnen, wat het stuk over hoe het invoegen en verwijderen van rijen formuleverwijzingen aanpast voor het binnen-blad-geval behandelt

Driedimensionale verwijzingen gedragen zich anders. Excel krimpt een 3D-span in plaats van hem te vernietigen. Met bladen in de volgorde Data, Mid, Keep maakt het verwijderen van Data van SUM(Data:Keep!A1) een SUM(Mid:Keep!A1): het verdwenen einde schuift één blad naar binnen, gemeten in de tabvolgorde zoals die vóór de verwijdering stond. Met slechts twee bladen in de span is het resultaat de enkele-bladvorm SUM(Keep!A1). Het verwijderen van Mid verandert helemaal geen formuletekst, want de span benoemt nog steeds twee levende bladen en dekt er simpelweg één minder ertussen. Pas wanneer beide einde het verwijderde blad benoemen wordt de verwijzing #REF!. Een dode verwijzing verandert wat een formule teruggeeft, dus HotXLS markeert de gecachte uitkomst van elke formulecel die hij tijdens een verwijdering herschreef als verouderd; roep Recalculate aan voordat u waarden leest. Een hernoeming laat gecachte waarden met rust, want de getallen kunnen niet veranderen

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;   // cellen die Data verloren dragen verouderde gecachte waarden
    Book.SaveAs('report.xlsx');
  finally
    Book.Free;
  end;
end.

// Verwacht:
// Na het verwijderen van Data:
//   B1: SUM(Mid:Keep!A1)
//   B2: SUM(#REF!)+1
//   B3: Keep!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!
// Na het hernoemen van Keep naar 2025:
//   B1: SUM('Mid:2025'!A1)
//   B2: SUM(#REF!)+1
//   B3: '2025'!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!

Een blad hernoemen herschrijft verwijzingen door het hele werkboek

Het toewijzen van TXLSXWorksheet.Name in een XLSX-werkboek draagt de nieuwe naam naar elke plek waar een formule kan wonen, en DeleteByName loopt dezelfde plekken langs om de #REF!-instorting toe te passen. De lijst gaat ruim voorbij celformules:

• Celformules op elk blad. Een gedeelde formule wordt uitgeklapt vastgehouden op elke volgcel en een array-formule woont op zijn root-cel, dus beide zijn gedekt zonder bijzondere behandeling
• Formules van voorwaardelijke opmaak, inclusief drempels van het type formule in databalken, kleurschalen en pictogramsets
• Gegevensvalidatie-formules, zoals een lijstbron op een ander blad
• Berekende-kolom- en totalenrij-formules van tabellen
• Defined names, de ruggengraat van modellen gebouwd op defined names en cross-sheet-formules; bij een hernoeming volgt een pivot cache die zijn bronrange van het hernoemde blad leest mee
• Chart-ranges: series-namen, categorieën, waarden, bubbelgroottes en data-label-ranges, plus- en minranges van error bars, en de celverwijzingen achter chart- en astitels

Een 3D-span wordt als geheel gequote wanneer een van beide einde quotes nodig heeft: Keep naar 2025 hernoemen levert 'Mid:2025'!A1 op, nooit Mid:'2025'!A1, want de span is één kwalificator. Elke kwalificator waar de hernoeming niet over gaat wordt gereproduceerd zoals geschreven. Zegt een formule ergens anders 'Sheet1'!B4, met apostrofs die technisch overbodig zijn, dan zegt hij dat erna nog steeds. Die kwalificatoren onderweg normaliseren zou Excel niet schaden, maar een hernoeming van één blad zou dan elke formule in het werkboek aanraken, en een diff tussen twee versies van een gegenereerd rapport zou de echte verandering begraven onder honderden cosmetische

Tekst die alleen lijkt op een bladverwijzing

Drie soorten tekst lijken voor een patroonzoektocht op een kwalificator Name! en zijn het niet. Binnen een string literal is "Data!A1" tekst, dus INDIRECT("Data!A1") blijft na een hernoeming of verwijdering naar de oude naam wijzen, hetzelfde als in Excel; bouwt een rapport bladnamen voor INDIRECT, bewaar ze dan in een cel die uw code samen met de hernoeming bijwerkt. Een kwalificator voorafgegaan door een gebracketteerde boekindex, zoals in [1]Data!A1, hoort bij een extern werkboek dat toevallig een blad Data heeft, en het hernoemen van het lokale blad Data mag die niet aanraken; een gequote kwalificator met een [ erin wordt hetzelfde behandeld. En #REF! zelf heeft de vorm van een kwalificator genaamd REF, wat is waarom HotXLS controleert op de voorafgaande # voordat hij REF! als bladnaam behandelt. Zonder die controle zou het hernoemen van een blad genaamd REF elke #REF! in het werkboek herschrijven

De bladnamen die een gegenereerde formule het meest waarschijnlijk breken zijn gewone als FY2024, Q1 en 2025, geen namen met spaties of leestekens. Quote ze via XlsSheetNameNeedsQuotes waar uw Delphi-code ook formuletekst bouwt. Vanaf daar laat een hernoeming of verwijdering in de HotXLS Delphi spreadsheet component elke formule in de vorm achter die Excel zelf zou hebben opgeslagen