De methode AddCopy van HotXLS kopieert een werkblad van de ene Excel-werkmap naar een andere door elke formule op dat blad te decompileren naar A1-stijltekst en de tekst opnieuw te compileren binnen de doelwerkmap, in plaats van de gecompileerde formuleboom rechtstreeks te kopiëren, omdat grafiekreeksverwijzingen, lettertype-indexen voor opgemaakte tekst, en de nummering van externe koppelingen allemaal onafhankelijk van elkaar binnen elk werkmapbestand worden toegewezen
De storing komt naar voren in precies de werkmap die u zou verwachten: een maandafsluitingstaak die één blad uit elk filiaalrapport haalt en aan een samenvattingsbestand toevoegt. Open het resultaat en een subtotaalgrafiek toont de cijfers van een compleet ander filiaal, een notitie die in de bron vet en rood was, is weer gewoon zwarte tekst, en een formule die ooit een belastingtarief ophaalde uit een bijbehorende opzoekwerkmap toont nu een bevroren getal dat niemand kan verklaren. Er wordt hier geen uitzondering opgeworpen — het bestand opent, de cijfers zien er plausibel uit, en de schade blijft liggen totdat iemand een grafiek met de verkeerde titel ernaast ziet staan
Waarom kan AddCopy niet gewoon de gecompileerde formuleboom kopiëren?
AddCopy kan de gecompileerde formuleboom niet ongewijzigd verplaatsen, omdat een gecompileerde BIFF-formule geen vrijstaande tekst is — het is een reeks tokens, en verschillende van die tokens zijn kleine gehele getallen die alleen correct oplossen binnen de werkmap die ze heeft geproduceerd. Een 3D-verwijzing zoals Sheet2!A1:A10 draagt de letterlijke naam Sheet2 niet meer zodra deze is gecompileerd — het draagt een veld dat de BIFF-specificatie ixti noemt (HotXLS houdt dezelfde waarde in zijn eigen gecompileerde boom onder de veldnaam FExternID), een index in de eigen private EXTERNSHEET-tabel van die werkmap, genummerd op welke manier die specifieke werkmap zijn bladen en externe boeken toevallig heeft geregistreerd. Verplaats het token ongewijzigd naar een werkmap waarvan de EXTERNSHEET-tabel in een andere volgorde is opgebouwd, en index 3 betekent niet langer Sheet2 — het betekent welk blad dan ook dat daar toevallig plek 3 inneemt, en Excel heeft geen manier om de fout te signaleren, omdat de formule wat het bestandsformaat betreft perfect welgevormd is. Dit is precies de storing waarvoor TXLSWorksheets.AddCopy bestaat om te vermijden: aangeroepen vanuit de eigen bladverzameling van beide werkmappen in Delphi- of C++Builder-code, kopieert het een werkblad — celwaarden, opmaak, formules, grafieken, commentaren, samenvoegingen, paginainstellingen en meer — van een bronwerkmap die al dan niet degene is waarop u het aanroept, en voegt het resultaat toe aan de bestemming onder een naam die u kiest of een gedisambigueerde kopie van het origineel
var
Summary, Branch: IXLSWorkbook; // interface-counted: do not Free
begin
Summary := TXLSWorkbook.Create;
Branch := TXLSWorkbook.Create;
Branch.Open('branch-east.xls');
// Appends a copy of Branch's first sheet onto Summary, renamed to
// stay unique inside the destination workbook
Summary.Sheets.AddCopy(Branch.Sheets[1], 'East Detail');
Summary.SaveAs('consolidated.xls');
end;De oplossing: decompileren naar tekst, opnieuw compileren in de bestemming
HotXLS lost het indexeringsprobleem op door de gecompileerde boom zelf nooit de werkmapgrens te laten oversteken. Voor elke formulecel bij een kopie tussen werkmappen decompileert AddCopy de bronformule naar dezelfde A1-stijltekst die een gebruiker in Excel's formulebalk zou zien, en geeft die tekst vervolgens aan de doelwerkmap, die deze vanaf nul terugparst naar een boom met behulp van zijn eigen tabellen — een blad-gekwalificeerde verwijzing zoals Data!D2:D100 is op dat moment gewoon een string, en een string betekent hetzelfde in elke werkmap, dus als de bestemming al een blad heeft genaamd Data, wordt de verwijzing correct opgelost zonder enige indexvertaling, omdat er nooit een ruwe index onderweg was om te vertalen. HotXLS betaalt deze heen-en-weer-reis alleen wanneer het moet: een blad kopiëren binnen dezelfde werkmap volgt een goedkoper pad waarbij de gecompileerde boom eenvoudig in het geheugen wordt gedupliceerd, aangezien elke index erin al geldig is op de plek waar deze blijft, en de tekstomweg draait alleen zodra AddCopy vaststelt dat bron en bestemming werkelijk verschillende werkmapinstanties zijn. Het is ook de moeite waard om precies te zijn over wat deze herschrijving niet is. Het heeft niets te maken met de verschuiving van rijen en kolommen die plaatsvindt wanneer u rijen invoegt of verwijdert binnen één blad, wat een begeleidend artikel in detail behandelt — die engine herschrijft A1-tekst ter plekke om cellen te volgen die een paar rijen omhoog of omlaag zijn verplaatst binnen één werkmap, terwijl deze draait wanneer een formule de werkmap die haar compileerde helemaal verlaat, waar verplaatste rijen niet het probleem zijn en werkmap-private nummering wel
// Conceptually, this is what AddCopy does for each formula cell: turn
// the compiled tree back into text using the source workbook's own
// tables, then let the destination workbook parse that text back into
// a tree using its own tables, from scratch
FormulaText := SourceBook.GetUnCompiledFormula(SourceFormula, Row, Col, SourceSheetID);
DestFormula := DestBook.GetCompiledFormula(FormulaText, DestSheetID);Wat als de bestemming dat blad, of die naam, nog niet heeft?
De hercompilatie van AddCopy slaagt alleen wanneer de doelwerkmap al alles heeft waar de formuletekst naar verwijst, en de twee gaten die in de praktijk naar voren komen zijn een gelijknamig blad dat in deze batch nog niet is overgekopieerd, en een op werkmapniveau gedefinieerde naam die in de bestemming nog nooit heeft bestaan. HotXLS werpt geen uitzondering op wanneer de hercompilatie halverwege een bladkopie mislukt — de toewijzing aan de Value-eigenschap van de cel slaat de formuletekst stilletjes op als een gewone string in plaats daarvan, een bewuste, inspecteerbare faalmodus in plaats van een stille, aangezien een formulecel die onverwacht letterlijke tekst toont zoals =SUM(Q1!B2:B12) in plaats van een berekend getal het teken is dat iets stroomopwaarts in de kopie niet is opgelost. Voordat het opgeeft, probeert AddCopy één reparatie: het loopt door de syntaxisboom van de mislukte formule en verzamelt elk op-naam-gedefinieerd ID dat de formule raakt, en voor elke op werkmapniveau gedefinieerde naam die wel in de bron bestaat maar nog niet in de bestemming, kopieert het de naam over en hercompileert het dezelfde tekst een tweede keer. Op bladniveau gedefinieerde namen vallen buiten wat deze reparatie kan oplossen, aangezien een naam die alleen zichtbaar is voor formules op één blad van de bronwerkmap geen equivalent plekje heeft om naartoe te migreren, en een bestemming die al een naam met dezelfde spelling bezit, wordt ongemoeid gelaten in plaats van overschreven, op de aanname dat een naam die de aanroeper doelbewust vooraf heeft aangemaakt degene is die geëerbiedigd moet worden. Binnen één werkmap loopt de naamopzoeking van een formule tussen bladen automatisch van bladniveau omhoog naar werkmapniveau, wat het mechanisme is dat het artikel van HotXLS over gedefinieerde namen en formules tussen bladen behandelt; het oversteken van een werkelijke werkmapgrens verwijdert dat vangnet volledig, en een naam moet doelbewust worden meegenomen of de formule die ervan afhankelijk is degradeert naar tekst
Grafiekreeksverwijzingen hebben dezelfde oplossing nodig, maar een ander codepad
Een HotXLS-grafiekreeks die een celbereik plot, stuit precies op hetzelfde nummeringsprobleem als een gewone celformule, omdat de databereikverwijzing van een grafiek ook een gecompileerde formule-tokenstroom is — de BIFF-specificatie noemt het record dat deze draagt BRAI ([MS-XLS] paragraaf 2.4.51) — maar AddCopy kan het niet oplossen door het normale grafiek-laadpad te hergebruiken, omdat dat pad precies is wat de bug veroorzaakt. Wanneer een grafiekrecord tijdens het normale openen van een bestand van schijf wordt geparst, wordt de formuleboom ervan opgebouwd door de ruwe bytes te vertalen via welke rekeninstantie dan ook die het parsen uitvoert; voer in plaats daarvan de ruwe BRAI-bytes van een brongrafiek door de gewone recordlader van de doelwerkmap, en de ixti die in die bytes is ingebed, wordt opgelost tegen de EXTERNSHEET-tabel van de bestemming, zodat de reeks stilletjes wijst naar welk blad dan ook dat daar die plek inneemt — dezelfde klasse fout als het ongewijzigd kopiëren van de gecompileerde boom van een cel, alleen moeilijker op te merken omdat niemand grafiekreeksformules leest zoals ze celformules lezen. HotXLS vermijdt de valstrik met een toegewijd kloonpad in plaats daarvan: TXLSCustomChart.AssignFrom kopieert de eigen niet-formule headerbytes van elk grafiekrecord letterlijk, en herbouwt vervolgens het bijgevoegde bereik via hetzelfde decompileer-en-hercompileer-primitief dat voor gewone cellen wordt gebruikt, zodat de nieuwe boom vanaf nul wordt opgebouwd tegen de EXTERNSHEET-tabel van de bestemming in plaats van er achteraf tegen te worden geherinterpreteerd
Hetzelfde nummeringsprobleem, één lettertype-index per keer
Niet elk werkmap-lokaal getal binnen een grafiek of een cel met opgemaakte tekst is een formule, en een lettertype-index is dezelfde klasse probleem in het klein. Reeksen opgemaakte tekst, samen met nog twee grafiekrecordtypen die een bijschrift of aslettertype dragen, slaan een lettertypeverwijzing op als een ruwe geheel-getal-index in de eigen lettertypetabel van de bezittende werkmap, en die index betekent niets in de tabel van een andere werkmap — het zou daar net zo goed naar een compleet ander lettertype, een andere grootte, of een andere kleur kunnen wijzen. HotXLS lost dit op naar waarde in plaats van naar getal: het zoekt de werkelijke lettertype-attributen op die index in de brontabel op, vindt of maakt een overeenkomend item in de lettertypetabel van de bestemming, en herschrijft de opgeslagen index om naar die nieuwe plek te wijzen. Één eigenaardigheid van het formaat maakt de opzoeking zelf lastig — de index-in-bestand slaat plek 4 over, een nummeringsgat dat [MS-XLS] paragraaf 2.5.339 documenteert, dus de code moet de index één omlaag verschuiven voordat lettertypen worden vergeleken en weer één omhoog voordat het resultaat wordt geschreven
// The file-numbered font index skips slot 4 (MS-XLS section 2.5.339);
// shift into the in-memory slot, migrate the font by value if the
// destination differs, then shift back before writing the result
if Ifnt >= 5 then
Dec(Ifnt);
if DestFonts.Key[Ifnt] <> SourceFonts.Key[Ifnt] then
Ifnt := DestFonts.SetKey(0, SourceFonts.Key[Ifnt]);
if Ifnt >= 4 then
Inc(Ifnt);Wat gebeurt er met een formule die al buiten de werkmap wijst?
Een formule die al naar een derde werkmap reikt voordat u AddCopy ooit aanroept, is het ene geval dat de heen-en-weer-reis via tekst niet kan dragen, omdat HotXLS's eigen formule-naar-tekst-decompiler bewust geen [Boek]Blad!-haakjestekst synthetiseert voor een externe verwijzing, en de compiler aan de andere kant die syntax ook niet als invoer accepteert — dus dit ene geval loopt via een tweede mechanisme dat tekst helemaal niet aanraakt. Wanneer de hierboven beschreven reparatie voor naammigratie een cel nog steeds als string achterlaat, en de bronwerkmap een echte bestandsnaam heeft, schakelt AddCopy van strategie: het maakt een diepe kopie van de gecompileerde formuleboom zelf in plaats van de tekst ervan, en geeft de kopie vervolgens aan een toegewijde herbindingsronde, RebindExternRefsInTree, die er knoop voor knoop doorheen loopt. Voor elke bereikverwijzing die het vindt, lost die ronde het EXTERNSHEET-item van de bron op naar een paar bladnamen, en registreert, of hergebruikt, een equivalent item in de eigen externe-verwijzingstabellen van de bestemming, en creëert een gloednieuwe externe-werkmapkoppeling als de bestemming nog nooit naar dat bronbestand heeft verwezen
Hier is het werkmap-lokale nummeringsprobleem op zijn meest letterlijk, omdat een externe-verwijzingstoken drie afzonderlijke coördinaten in één veld bundelt en elk daarvan is privé voor de werkmap die het schreef: welke externe werkmap, een plek in de eigen lijst van externe boeken van de bestemming, toegewezen in welke volgorde die werkmap ze ook toevallig heeft geregistreerd; welk blad binnen de eigen bladlijst van die externe werkmap, opgeslagen als een 1-gebaseerde index specifiek gescoped aan het externe boek, een compleet ander nummeringsdomein dan de eigen interne blad-ID's van de bestemming; en het celbereik zelf, gewone rij- en kolomcoördinaten die geen vertaling nodig hebben omdat ze nooit werkmap-relatief waren om te beginnen. Krijg een van de eerste twee verkeerd en Excel opent het bestand nog steeds, toont nog steeds een formule, en evalueert deze zonder klagen tegen de verkeerde externe cellen. Eén soort knoop verslaat zelfs deze herbinding op boomniveau: een verwijzing naar een gedefinieerde naam, een index in de eigen private naamtabel van zijn werkmap precies zoals een bladindex privé is voor zijn eigen EXTERNSHEET, zonder equivalente reparatie op boomniveau beschikbaar — op het moment dat de herbindingsronde ergens in de boom een naamverwijzing tegenkomt, geeft het de hele formule op in plaats van er een gedeeltelijk correcte uit te schrijven. Zelfs wanneer de herbinding wel slaagt, toont de doelcel geen vers herberekend getal; het toont de waarde die de broncel al had op het moment van kopiëren, bewaard in een gecachete plek op dezelfde manier waarop Excel zelf de laatst bekende waarde van elke externe verwijzing cachet totdat u koppelingen expliciet vernieuwt, wat de juiste standaard is, aangezien herberekenen over een levende koppeling naar een ander bestand precies het soort bewerking is dat u één keer, doelbewust, wilt triggeren, in plaats van bij elke keer openen
Wat dit ontwerp kost
De decompileer-en-hercompileer-machinerie van AddCopy is niet gratis, en de kosten zijn de moeite waard om vooraf in te plannen bij een grote consolidatietaak, niet achteraf. Een blad kopiëren binnen dezelfde werkmap volgt het goedkope pad, een rechttoe-rechtaan duplicatie van de gecompileerde boom in het geheugen, omdat elke index erin al geldig is in de werkmap waar het blijft; een kopie tussen werkmappen betaalt in plaats daarvan voor een echte parse op elke formulecel, decompileren naar tekst en die tekst dan weer vanaf nul compileren, en hoewel het verschil het niet waard is om te meten op een blad met een paar dozijn formules, moet een bronwerkmap met tienduizenden formulecellen, gekopieerd als één blad onder tientallen in een batchtaak, verwachten dat de hercompilatie de looptijd domineert in plaats van de bestands-I/O eromheen. Kopieervolgorde is om een tweede reden belangrijk, los van snelheid: een formule die verwijst naar een blad dat AddCopy in deze batch nog niet heeft bereikt, mislukt de hercompilatie om dezelfde reden als een formule die naar een werkelijk niet-bestaand blad verwijst, dus een taak die blad B kopieert vóór het blad-A-formule dat ervan afhankelijk is, zal die formule precies zien degraderen zoals hierboven beschreven, letterlijke tekst of een terugval op een externe koppeling die rechtstreeks terugwijst naar het bronbestand waar het net vandaan kwam. En omdat elke bronwerkmap in een consolidatiebatch meestal onafhankelijk is opgesteld, is het de moeite waard om expliciet te testen op de ene faalmodus waarvoor geen enkel afzonderlijk bronbestand u ooit had kunnen waarschuwen — vijf filiaalwerkmappen die elk de cijfers van een collega-filiaal optellen, kunnen combineren tot een werkelijke circulaire verwijzing binnen de samenvattingswerkmap zonder dat enig individueel bronbestand er ooit een bevatte, een cyclus die pas bestaat zodra elk blad op dezelfde plek is beland en de herberekening over de gecombineerde verzameling draait
Kopiëren van werkbladen tussen werkmappen wordt standaard geleverd als gedrag van AddCopy in de HotXLS Delphi Excel-component voor Delphi en C++Builder; de productpagina bevat de volledige werkblad- en werkmap-API-referentie, inclusief het hier beschreven gedrag voor grafieken, opgemaakte tekst en externe verwijzingen