Technisch artikel

Verliesloze XLSX-rondgang in Delphi: theme en calcChain

Een facturatiejob opent een klantsjabloon, schrijft 42750 in B3 en slaat op. Diff de uitgepakte pakketten daarna en de changelijst is veel langer dan één cel: xl/theme/theme1.xml bevat nu het standaard Office-palet in plaats van de teal en slate van de klant, de <extLst> onderaan de worksheet-XML is de sparklinegroep en een nieuwere voorwaardelijke opmaakregel kwijt, en xl/calcChain.xml ontbreekt helemaal. Excel opent het resultaat, biedt een reparatie aan, en de huisstijlkleuren zijn weg. De code raakte één cel aan. Al het andere veranderde omdat de opslag het pakket opbouwde uit wat het in-memory model kende, en drie onderdelen van een typisch sjabloon zitten buiten wat een spreadsheetmodel normaal vasthoudt

Drie mechanismen achter een verliesloze HotXLS XLSX-rondgang in Delphi: xl/theme/theme1.xml gecached als ruwe bytes en ongewijzigd teruggeschreven behalve ThemeColor-slotoverschrijvingen, vreemde extLst-blokken vastgelegd uit XML-events en gereplayed, en een verse aan de specificatie voldoende calcChain.xml bij elke formuleopslag
Elk behouden onderdeel volgt zijn eigen pad door de opslag — letterlijke themabytes, event-niveau extLst-replay en een geregenereerde berekeningsketen — terwijl worksheet-XML en styles uit het model worden herbouwd

Waarom een edit van één cel het hele XLSX-pakket herschrijft

Een .xlsx-bestand is een ZIP van XML-onderdelen: xl/workbook.xml, één xl/worksheets/sheetN.xml per werkblad, xl/styles.xml, xl/sharedStrings.xml, het thema, de berekeningsketen en wat er aan tekeningen, tabellen en pivot-caches verder in de werkmap zit. Een bibliotheek die waarden en formules in objecten leest en bij het opslaan elk onderdeel uit die objecten hergenereert, heeft één zwakke plek. Alles waar het objectmodel geen plek voor heeft, komt nooit in het geheugen, dus het hergenereerde onderdeel komt er zonder naar buiten. Niemand hoeft de sparklines te verwijderen; ze hebben simpelweg nergens onderdak tussen Open en SaveAs

ECMA-376 zag een helft hiervan aankomen. SpreadsheetML reserveert extLst (ECMA-376 Part 1, §18.2.10 voor het element op werkmapniveau, met passende elementen op worksheets, styles en voorwaardelijke opmaak) als opslagruimte voor toekomstige functies. Een nieuwere producent wikkelt elke functie in een <ext>-element waarvan het uri-attribuut de functie benoemt, en van een oudere consument wordt verwacht dat hij meedraagt wat hij niet begrijpt. Sparklines, slicerlijsten, nieuwere opties voor databalken en pictogramsets en uitgebreide gegevensvalidatie reizen op deze manier door de bestanden die Excel vandaag schrijft. Onbekende <ext>-blokken weglaten breekt dus het forwards-compatibiliteitscontract waar het formaat om draait, en de schade wordt pas zichtbaar als de klant het bestand in Excel opent

Het thema en de berekeningsketen falen om verschillende redenen, dus elk vraagt zijn eigen behandeling. De rest van dit artikel doorloopt hoe HotXLS alle drie aanpakt, wat een Delphi-beller kan waarnemen en controleren, en waar de garantie ophoudt

Een eigen theme1.xml in Delphi intact houden

HotXLS bewaart het thema van een werkmap door de originele bytes van xl/theme/theme1.xml uit het geopende pakket vast te houden en dat onderdeel bij SaveAs ongewijzigd terug te schrijven. Het themaonderdeel (ECMA-376 Part 1, §14.2.7) is DrawingML: een kleurenschema met twaalf slots, een fontschema, een formatschema van vullingen, lijnen en effecten. Celstijlen wijzen erin met <color theme="4"/> en soortgelijke verwijzingen, dus de spreadsheetengine hoeft alleen slots te herleiden en nooit gradients of effectstijlen te doorgronden. Bij elke opslag een vast Office-thema hergenereren is precies wat de teal van de klant in Office-blauw doet omslaan. Een werkmap die vanaf nul is gemaakt, zonder thema om mee te dragen, krijgt in plaats daarvan het ingebouwde Office-thema

HotXLS cachet de ruwe bytes van xl/theme/theme1.xml bij het openen en schrijft ze bij het opslaan ongewijzigd terug, waarbij alleen kleurslots die via ThemeColor worden overschreven vervangen; een bibliotheek die uit het model herbouwt regenereert een standaard Office-thema en schiet de huisstijlkleuren van de klant terug
theme1.xml letterlijk cachen vraagt helemaal geen themamodel, en ThemeMajorFont met ThemeMinorFont stylen alleen werkmappen die geen vastgelegd thema meedragen

Het alledaagse geval heeft helemaal geen themacode nodig. Let op de sheetindex: Sheets is 1-based in de XLSX-engine, en Cells[3, 2] is rij 3, kolom 2, dus B3

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('branded-invoice.xlsx');
    Book.Sheets[1].Cells[3, 2].Value := 42750.00;  // de enige edit
    Book.SaveAs('branded-invoice-out.xlsx');
    // xl/theme/theme1.xml in de uitvoer draagt het eigen thema van het sjabloon
  finally
    Book.Free;
  end;
end;

Ruwe bytes zijn de sterkste gelijkvormigheid die voor zo'n onderdeel te halen valt: geen parse, geen herserialisatie, niets dat kan afdrijven. Schrijft de opslag Strict OOXML in plaats van het transitional-dialect, dan gaat het vastgelegde thema door de namespace-vertaler in plaats van gekopieerd te worden, want een bytekopie zou transitional-namespace-URI's binnen een Strict-pakket achterlaten

De valkuil zit aan de API-kant. TXLSXWorkbook stelt ThemeMajorFont en ThemeMinorFont bloot, en na Open rapporteren ze de kop- en brontypografie die uit het fontschema van het sjabloon zijn geparseerd. Ze zetten lijkt de natuurlijke manier om lettertypen te wisselen, maar ze stylen alleen het ingebouwde Office-thema dat nieuwe werkmappen krijgen. Op een werkmap die zijn eigen thema meenam wint het vastgelegde onderdeel en veranderen die twee setters niets in het opgeslagen bestand. Kleuren worden anders aangepakt: ThemeColor[Slot] adresseert de twaalf clrScheme-slots, en een overschrijving wordt in het behouden thema geschreven door alleen de kleur van dat slot te vervangen, met het fontschema, het formatschema en eventuele thema-extensies onaangetast

Book.Open('branded-invoice.xlsx');
// Geen effect op het opgeslagen bestand: het eigen thema van het sjabloon blijft behouden
Book.ThemeMajorFont := 'Segoe UI Semibold';
// Bereikt het bestand wel: alleen het accent1-slot in theme1.xml verandert
Book.ThemeColor[xlsxThemeAccent1] := $FF0F7B6C;  // $AARRGGBB
Book.SaveAs('rebranded.xlsx');

Die splitsing is bewust. Een kleuroverschrijving is een nauwe, goed gedefinieerde edit op één element, dus de rest van het thema van de ontwerper kan precies blijven zoals het was. Een fontschema-herschrijving zou per-script lettertypelijsten raken die een op data gerichte API zich moeilijk laat uitdrukken. Wie een sjabloon een nieuw lettertype geeft, past het aan in Excel en levert het nieuwe sjabloon aan; zo blijft het thema in handen van wie de huisstijl bezit

Wat gebeurt er bij het opslaan met onbekende extLst-blokken?

HotXLS legt elk <ext>-blok op worksheetniveau dat hij niet native modelleert vast en replayt hem in de extLst van het opgeslagen werkblad. De blokken die hij wel modelleert worden geparseerd naar getypeerde objecten en daaruit herbouwd: sparklinegroepen gaan naar SparklineGroups, de x14-uitbreiding voor gegevensvalidatie wordt in de gegevensvalidatiecollectie gelezen, en slicerlijsten worden door het slicermodel opnieuw uitgezonden. Alles andere belandt, één XML-fragment per blok, in de read-only RawWorksheetExts-tekenreekslijst op elke TXLSXWorksheet, zodat een test exact kan inspecteren wat er wordt doorgevoerd

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-newer-excel.xlsx');
    Sheet := Book.Sheets[1];
    WriteLn(Format('%d foreign ext block(s), %d sparkline group(s)',
      [Sheet.RawWorksheetExts.Count, Sheet.SparklineGroups.Count]));
    for i := 0 to Sheet.RawWorksheetExts.Count - 1 do
      WriteLn(Copy(Sheet.RawWorksheetExts[i], 1, 100)); // de openingstag toont de uri
    // Namespace-declaraties die de fragmenten van de <worksheet>-root leenden
    WriteLn(Sheet.RawExtNamespaceDecls);
  finally
    Book.Free;
  end;
end;

De vastlegging werkt vanaf de XML-eventstroom, want een streaming reader geeft elementen, tekst en eindtags maar geen byte-offsets in de bron. Elke onbekende subtree wordt uit die events herbouwd terwijl ze passeren. Een naïeve diepteteller breekt hier: hij verhoogt bij elke starttag en verlaagt bij elke eindtag, en een self-closing kind zoals <x14:cfvo type="min"/> komt aan als één start-event gemarkeerd als leeg, zonder eindtag die volgt. Tel hem als geopend element en de teller komt nooit meer op nul terug, dus de vastlegging slikt de rest van het werkblad in. De vastleggingslus in HotXLS verhoogt de diepte alleen voor een starttag die niet leeg is, en een <ext/> die zelf leeg is wordt meteen als éénregelfragment opgeslagen

Uit events herbouwen heeft een zichtbaar neveneffect. Attribuutaanhalingstekens, witruimte tussen elementen en de keuze tussen <a></a> en <a/> komen genormaliseerd terug, dus een fragment is qua betekenis gelijkwaardig aan het origineel maar niet byte voor byte identiek. Excel parseert de XML; hij vergelijkt geen bytes. Een diff-tool die tekst vergelijkt vlagt de verandering wel aan, en dat is goed om te weten voordat iemand hem als regressie meldt

Namespaces zijn het tweede dat met een gereplayd fragment mis kan gaan. Excel declareert de prefixes die hij nodig heeft meestal op het <ext>-element zelf, wat het fragment zelfvoorzienend maakt, maar een blok kan ook leunen op een prefix zoals x14ac of xr die één keer op de <worksheet>-root van de bron stond gedeclareerd. Replay dat fragment in een werkblad zonder de declaratie en de opgeslagen XML is niet namespace-well-formed. HotXLS scant elk vastgelegd fragment op prefixes die hij gebruikt maar niet declareert, noteert de passende declaraties uit de bron in RawExtNamespaceDecls en schrijft ze op de root van het opgeslagen werkblad. Omdat de fragmenten gewone tekenreeksen zijn, kan een werkblad dupliceren binnen of over werkmappen ze ook naar het nieuwe werkblad meenemen

calcChain.xml schrijven zodat Excel uw formules vertrouwt

HotXLS schrijft xl/calcChain.xml (het Calculation Chain-onderdeel, ECMA-376 Part 1, §12.3.1) zodra de opgeslagen werkmap ten minste één formulecel bevat, samen met zijn content-type override en workbook-relatie. Elke entry is een <c r="D10" i="1"/>-element: de celverwijzing plus de 1-based sheetindex, die de schrijver op elke entry uitzendt ook al laat het schema een reeks entries op hetzelfde werkblad hem weglaten. Expliciet is eenvoudiger en net zo geldig

De volgorde hangt af van wat de werkmap al weet. Bestaat de formuleafhankelijkheidsgraaf en is hij actueel — de toestand vlak na Recalculate — dan volgt de keten een volledige topologische volgorde: elke formule na de cellen waar hij van afhangt, met leden van kringverwijzingen achteraan zodat de keten toch elke formulecel noemt. Is de graaf verouderd of nooit gebouwd, dan worden formulecellen in documentvolgorde opgesomd, werkblad voor werkblad. Beide zijn legaal. De implementatienotities van Microsoft voor het formaat, [MS-XLSX], beschrijven de keten als een hint die Excel tijdens het berekenen controleert en hersort, dus een volledige opsomming in elke volgorde laadt schoon op

HotXLS zendt xl/calcChain.xml in topologische volgorde uit wanneer Recalculate de afhankelijkheidsgraaf heeft gebouwd, en anders in documentvolgorde; Excel behandelt elke volledige opsomming als hint en hersort hem bij het laden
Beide volgordes blijven legaal omdat Excel de keten bij het laden opnieuw verifieert, en HotXLS dwingt binnen SaveAs nooit een volledige afhankelijkheidsgraafbouw af
Book.Open('model.xlsx');
Book.Sheets[1].Cells[10, 4].Formula := '=SUM(D2:D9)';
// Hier opslaan somt formulecellen in documentvolgorde op, want het wijzigen
// van een formule maakte de afhankelijkheidsgraaf ongeldig.
Book.Recalculate;   // bouwt de graaf opnieuw op en ververset de cachewaarden
Book.SaveAs('model-out.xlsx');
// calcChain.xml volgt nu de afhankelijkheidsvolgorde: D10 na D2:D9

SaveAs bouwt de graaf niet uit eigen initiatief. Een volledige graafbouw betekent dat elke verwijzing in elke formule wordt herleid, wat bij een groot model echt werk is dat een beller die alleen een bestand wilde schrijven nooit vroeg. Als de cachewaarden in de uitvoer voor u tellen — en dat doen ze meestal voor alles wat een stroomafwaarts systeem leest zonder Excel te openen — roep dan toch Recalculate aan vóór het opslaan; de graafgeordende keten komt gratis mee

Waarom een onderdeel uitzenden dat Excel als advies behandelt? Omdat zijn afwezigheid opvalt. Reparatieheuristieken, externe viewers en diff-tooling verwachten allemaal dat een formulierwerkmap een berekeningsketen meedraagt, en een pakket zonder oogt anders dan alles wat Excel zelf produceert. Een geldige keten schrijven houdt de uitvoer binnen de vorm waar de rest van het ecosysteem op is getraind, en kost één pass over de formulecellen

Waar de verliesloze rondgang eindigt

HotXLS kopieert niet het hele pakket byte voor byte, en de uitvoer moet ook niet zo worden beoordeeld. Worksheet-XML, styles.xml, gedeelde tekenreeksen en het werkmaponderdeel worden uit het geparseerde model hergenereerd, dus ze zijn inhoudelijk getrouw maar niet binair identiek aan de invoer. Alleen al de ZIP-local headers dragen verse tijdstempels. Vastgelegde <ext>-fragmenten komen genormaliseerd terug, zoals hierboven beschreven. ThemeMajorFont en ThemeMinorFont blijven buiten een behouden thema, terwijl ThemeColor-overschrijvingen er slot voor slot ingaan

Het behoudnet heeft een vaste vorm: functies die de engine native modelleert, zoals sparklines en uitgebreide gegevensvalidatie, die worden geparseerd en herschreven; vreemde <ext>-inhoud op worksheetniveau, die wordt gereplayd; en het themaonderdeel, dat wordt meegedragen zoals vastgelegd. VBA-projecten en externe werkmapverwijzingen overleven de opslag met dezelfde houd-wat-je-niet-modelleert-benadering, behandeld in het zusterartikel over VBA- en extern-linkbehoud, en documenteigenschappen in docProps hebben een eigen lees-schrijf-API. Een onderdeel van een exotische add-in dat niet wordt gemodelleerd en niet binnen een extensiepunt zit, valt buiten de drie mechanismen die hier worden behandeld

De controle die telt gebruikt uw eigen bestanden. Open een productiesjabloon vol functies, verander één waarde, sla op, pak beide pakketten uit en diff onderdeel voor onderdeel. Verwacht verschillen in het werkblad dat u bewerkte, in styles.xml en in attribuutopmaak binnen extLst; het themaonderdeel zou de invoer moeten matchen, de <ext>-elementen moeten dezelfde uri-waarden noemen, en calcChain.xml zou elke formulecel moeten noemen. Alles daarbuiten is het nastreven waard voordat het sjabloon naar productie gaat

Themabehoud, replay van vreemde extLst en berekeningsketenuitvoer horen bij de XLSX-engine in de HotXLS Delphi Excel Component; de productpagina somt de rest op van wat hij leest en schrijft voor Delphi- en C++Builder-projecten