HotXLS metod AddCopy kopierar ett kalkylblad från en Excel-arbetsbok till en annan genom att avkompilera varje formel på det bladet till A1-stiltext och rekompilera texten inuti målarbetsboken, snarare än att kopiera det kompilerade formelträdet direkt, eftersom diagramseriereferenser, rich text-typsnittsindex, och extern länknumrering alla tilldelas oberoende inuti varje arbetsboksfil
Felet visar sig i precis den arbetsbok man skulle förvänta sig: ett månadsslutjobb som drar ut ett blad från varje filialkontors rapport och lägger till det i en sammanfattningsfil. Öppna resultatet och ett delsummadiagram plottar en helt annan filials siffror, en anteckning som var fetstilt och röd i källan är tillbaka till vanlig svart text, och en formel som en gång hämtade en skattesats från en följeslagande uppslagsarbetsbok visar nu ett fruset tal ingen kan förklara. Inget kastar ett undantag här — filen öppnas, siffrorna ser rimliga ut, och skadan ligger där tills någon märker ett diagram med fel titel bredvid
Varför kan inte AddCopy bara kopiera det kompilerade formelträdet?
AddCopy kan inte flytta det kompilerade formelträdet oförändrat, eftersom en kompilerad BIFF-formel inte är fristående text — den är en sekvens av tokens, och flera av de tokensen är små heltal som bara löses upp korrekt inuti arbetsboken som producerade dem. En 3D-referens som Sheet2!A1:A10 bär inte det bokstavliga namnet Sheet2 när den väl kompilerats; den bär ett fält BIFF-specen kallar ixti (HotXLS behåller samma värde i sitt eget kompilerade träd under fältnamnet FExternID), ett index in i den arbetsbokens privata EXTERNSHEET-tabell, numrerad hur den specifika arbetsboken än råkade registrera sina blad och externa böcker. Flytta token oförändrad in i en arbetsbok vars EXTERNSHEET-tabell byggdes i en annan ordning och index 3 betyder inte längre Sheet2 — det betyder vad för blad som helst som råkar uppta plats 3 där, och Excel har inget sätt att flagga misstaget, eftersom formeln, vad filformatet beträffar, är perfekt välformad. Det är exakt det felet TXLSWorksheets.AddCopy finns för att undvika: anropad från antingen arbetsbokens egen kalkylbladssamling i Delphi- eller C++Builder-kod, kopierar den ett kalkylblad — cellvärden, format, formler, diagram, kommentarer, sammanslagningar, sidinställningar, med mera — från en källarbetsbok som kan eller inte kan vara den man anropar den på, och lägger till resultatet i målarbetsboken under ett namn man väljer eller en särskiljd kopia av originalet
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;Lösningen: avkompilera till text, rekompilera i målet
HotXLS löser indexeringsproblemet genom att aldrig låta det kompilerade trädet självt korsa arbetsboksgränsen. För varje formelcell vid en arbetsboksöverskridande kopiering avkompilerar AddCopy källformeln till samma A1-stiltext en användare skulle se i Excels formelfält, och lämnar sedan den texten till målarbetsboken, som tolkar den tillbaka till ett träd med sina egna tabeller från grunden — en bladkvalificerad referens som Data!D2:D100 är bara en sträng vid den punkten, och en sträng betyder samma sak i vilken arbetsbok som helst, så om målet redan har ett blad vid namn Data löses referensen upp korrekt utan någon indexöversättning alls, eftersom det aldrig fanns ett rått index i rörelse att översätta. HotXLS betalar bara för den här rundturen när den måste: att kopiera ett blad inom samma arbetsbok tar en billigare väg där det kompilerade trädet helt enkelt dupliceras i minnet, eftersom varje index inuti det redan är giltigt där det stannar, och textomvägen körs bara när AddCopy upptäcker att källan och målet genuint är olika arbetsboksinstanser. Det är värt att vara exakt om vad den här omskrivningen inte är, också. Den har inget att göra med rad- och kolumnförskjutningen som körs när man infogar eller tar bort rader inuti ett enda blad, vilket en följeartikel täcker i detalj — den motorn skriver om A1-text på plats för att spåra celler som flyttat några rader upp eller ner inom en arbetsbok, medan den här körs när en formel lämnar arbetsboken som kompilerade den helt och hållet, där flyttade rader inte är problemet och arbetsboksprivat numrering är
// 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);Vad händer om målet inte har det bladet, eller det namnet, ännu?
AddCopy:s rekompilering lyckas bara när målarbetsboken redan har allt formeltexten refererar till, och de två luckorna som visar sig i praktiken är ett likanamnat blad som ännu inte kopierats över i den här batchen, och ett arbetsboksomfattat definierat namn som aldrig existerat i målet alls. HotXLS kastar inget undantag när rekompileringen misslyckas mitt i en bladkopiering — cellens Value-tilldelning lagrar tyst formeltexten som en vanlig sträng istället, ett medvetet, inspekterbart felläge snarare än ett tyst, eftersom en formelcell som oväntat visar bokstavlig text som =SUM(Q1!B2:B12) istället för ett beräknat tal är tecknet på att något uppströms i kopieringen inte löstes upp. Innan den ger upp provar AddCopy en reparation: den går igenom den misslyckade formelns syntaxträd och samlar varje namn-ID med definierat namn som formeln rör, och för varje arbetsboksomfattat namn som finns i källan men inte ännu i målet kopierar den namnet över och rekompilerar samma text en andra gång. Bladomfattade namn ligger utanför vad den här reparationen kan fixa, eftersom ett namn synligt bara för formler på ett blad i källarbetsboken inte har någon motsvarande plats att migrera till, och ett mål som redan äger ett namn med samma stavning lämnas orört snarare än överskrivet, under antagandet att ett namn anroparen medvetet förskapat är det de vill ska respekteras. Inuti en enda arbetsbok går en tvärbladsformels namnsökning från bladomfattning upp till arbetsboksomfattning automatiskt, vilket är mekanismen HotXLS artikel om definierade namn och tvärbladsformler täcker; att korsa en faktisk arbetsboksgräns tar bort det säkerhetsnätet helt, och ett namn måste medvetet föras över eller så degraderas formeln som beror på det till text
Diagramseriereferenser behöver samma fix, men en annan kodväg
En HotXLS-diagramserie som plottar ett cellintervall stöter på precis samma numreringsproblem som en vanlig cellformel, eftersom en diagrams datareferens också är en kompilerad formeltoken-ström — BIFF-specen kallar posten som bär den BRAI ([MS-XLS] avsnitt 2.4.51) — men AddCopy kan inte fixa det genom att återanvända den vanliga diagraminläsningsvägen, eftersom den vägen är exakt vad som skapar buggen. När en diagrampost tolkas från disk under den vanliga öppningsprocessen för en fil byggs dess formelträd genom att översätta de råa byten genom vilken kalkylatorinstans som helst som utför tolkningen; mata en källdiagrams råa BRAI-byte genom målarbetsbokens egen vanliga postladdare istället, och ixti inbäddad i de byten löses upp mot målets EXTERNSHEET-tabell, så serien pekar tyst på vilket blad som helst som upptar den platsen där — samma felkategori som att kopiera en cells kompilerade träd oförändrat, bara svårare att märka eftersom ingen läser diagramseriens formler på samma sätt som de läser cellformler. HotXLS undviker fällan med en dedikerad klonväg istället: TXLSCustomChart.AssignFrom kopierar varje diagramposts egna icke-formel-huvudbyte ordagrant, och bygger sedan om det bifogade intervallet genom samma avkompilera-och-rekompilera-primitiv som används för vanliga celler, så det nya trädet konstrueras mot målets EXTERNSHEET-tabell från grunden snarare än omtolkas mot den i efterhand
Samma numreringsproblem, ett typsnittsindex i taget
Inte varje arbetsbokslokalt tal inuti ett diagram eller en rich text-cell är en formel, och ett typsnittsindex är samma problemkategori i miniatyr. Rich text-segment, tillsammans med två till diagrampost-typer som bär en bildtext- eller axeltypsnitt, lagrar en typsnittsreferens som ett rått heltalsindex in i den ägande arbetsbokens egen typsnittstabell, och det indexet betyder ingenting i en annan arbetsboks tabell — det kunde precis lika gärna peka på ett helt annat typsnitt, storlek, eller färg där. HotXLS löser det här efter värde snarare än efter nummer: den slår upp de faktiska typsnittsattributen vid det indexet i källtabellen, hittar eller skapar en matchande post i målets typsnittstabell, och skriver om det lagrade indexet så att det pekar på den nya platsen. En formategenhet gör själva uppslagningen knepig — filindexet hoppar över plats 4, en numreringslucka [MS-XLS] avsnitt 2.5.339 dokumenterar, så koden måste skifta indexet ner med ett innan typsnitten jämförs och tillbaka upp med ett innan resultatet skrivs
// 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);Vad händer med en formel som redan pekar utanför arbetsboken?
En formel som når in i en tredje arbetsbok innan man någonsin anropar AddCopy är det ena fallet textrundturen inte kan bära, eftersom HotXLS egen formel-till-text-avkompilerare medvetet inte syntetiserar [Book]Sheet!-hakparentestext för en extern referens, och kompilatorn i andra änden accepterar inte heller den syntaxen som indata — så det här ena fallet körs genom en andra mekanism som aldrig rör text alls. När namn-migrationsreparationen som beskrivs ovan fortfarande lämnar en cell som en sträng, och källarbetsboken har ett riktigt filnamn, byter AddCopy strategi: den djupkopierar det kompilerade formelträdet självt snarare än dess text, och lämnar sedan kopian till en dedikerad ombindningspassering, RebindExternRefsInTree, som går igenom det nod för nod. För varje intervallreferens den hittar löser den passeringen upp källans EXTERNSHEET-post tillbaka till ett par bladnamn, och registrerar, eller återanvänder, en motsvarande post i målets egna externreferenstabeller, och skapar en helt ny extern arbetsbokslänk om målet aldrig tidigare refererat den källfilen
Det är här arbetsbokslokala numreringsproblemet är som mest bokstavligt, eftersom en extern referenstoken buntar ihop tre separata koordinater i ett fält och var och en av dem är privat för arbetsboken som skrev den: vilken extern arbetsbok, en plats i målets egen lista av externa böcker tilldelad i vilken ordning den arbetsboken än råkade registrera dem; vilket blad inuti den externa arbetsbokens egen bladlista, lagrat som ett 1-baserat index omfattat specifikt till den externa boken, en helt annan numreringsdomän från målets egna interna blad-ID:n; och själva cellintervallet, vanliga rad- och kolumnkoordinater som inte behöver någon översättning eftersom de aldrig var arbetsboksrelativa från början. Får man endera av de första två fel öppnar Excel fortfarande filen, visar fortfarande en formel, och utvärderar den mot fel externa celler utan att klaga. En typ av nod besegrar även den här trädnivå-ombindningen: en referens till ett definierat namn, ett index in i dess egen arbetsboks privata namntabell precis på samma sätt som ett bladindex är privat till sin egen EXTERNSHEET, utan någon motsvarande trädnivåreparation tillgänglig — i det ögonblick ombindningsgenomgången möter en namnreferens någonstans i trädet överger den hela formeln snarare än att skriva ut en delvis korrekt en. Även när ombindningen lyckas visar målcellen inte ett nyberäknat tal; den visar värdet källcellen redan hade vid kopieringstillfället, hållet i en cachad plats på samma sätt Excel självt cachar det senast kända värdet av vilken extern referens som helst tills man uttryckligen uppdaterar länkar, vilket är rätt standard, eftersom att räkna om över en levande länk in i en annan fil är exakt den typen av operation man vill utlösa en gång, medvetet, snarare än vid varje öppning
Vad den här designen kostar dig
AddCopy:s avkompilera-och-rekompilera-maskineri är inte gratis, och kostnaden är värd att planera kring innan man skriptar ett stort konsolideringsjobb snarare än efteråt. Att kopiera ett blad inom samma arbetsbok tar den billiga vägen, en rak minnesduplicering av det kompilerade trädet, eftersom varje index inuti det redan är giltigt i arbetsboken det stannar i; en arbetsboksöverskridande kopiering betalar för en genuin tolkning på varje formelcell istället, avkompilera till text och sedan kompilera den texten igen från noll, och medan skillnaden inte är värd att mäta på ett blad med några dussin formler bör en källarbetsbok med tiotusentals formelceller, kopierad som ett blad bland dussintals i ett batchjobb, förvänta sig att rekompileringen dominerar körtiden snarare än fil-I/O:n runt den. Kopieringsordning spelar roll av ett andra skäl bortom hastighet: en formel som refererar ett blad AddCopy inte nått ännu i den här batchen misslyckas sin rekompilering av samma skäl som en formel som refererar ett genuint icke-existerande blad gör, så ett jobb som kopierar blad B innan blad A-formeln som beror på det degraderar exakt den formeln som beskrivits ovan, strängtext eller en extern-länk-reservlösning som pekar rakt tillbaka på källfilen den just kom från. Och eftersom varje källarbetsbok i en konsolideringsbatch vanligtvis är författad oberoende är det värt att uttryckligen testa för det enda felläget ingen enskild källfil någonsin kunde ha varnat om — fem filialarbetsböcker som var och en summerar en jämlik filials siffror kan kombineras till en genuin cirkulär referens inuti sammanfattningsarbetsboken utan att någon enskild källfil någonsin innehöll en, en cykel som bara existerar när varje blad väl har landat på samma plats och omräkning körs över den kombinerade uppsättningen
Arbetsboksöverskridande kalkylbladskopiering levereras som standardbeteende hos AddCopy i HotXLS Delphi Excel-komponenten för Delphi och C++Builder; produktsidan bär den fullständiga kalkylblads- och arbetsboks-API-referensen, inklusive diagram-, rich text-, och externreferensbeteendet som beskrivs här