HotXLS justerar formelreferenser automatiskt när du infogar eller tar bort rader eller kolumner i ett XLSX-kalkylblad. Motorns metoder InsertRows, DeleteRows, InsertCols och DeleteCols skriver om varje kvarvarande formel så att dess A1-referenser — relativa, absoluta och intervall — fortsätter att peka på samma data efter den strukturella redigeringen, och referenser in i ett raderat block blir #REF!, vilket matchar Excels beteende
Buggen som detta förhindrar är en av de mest tystlåtna vid rapportgenerering. En generator skriver dagliga siffror i C2:C9 med =SUM(C2:C9) därunder, och ett senare steg infogar en rubrikrad längst upp. Om motorn endast flyttar cellvärden och lämnar formeltexten oförändrad, läser den där SUM-funktionen fortfarande C2:C9 medan datan nu ligger i C3:C10 — så totalsumman tyst utelämnar den sista dagen och dubbelräknar en rubrik. Ingenting genererar ett undantag, filen öppnas utan problem och siffran är helt enkelt felaktig. Före version 2.160 lämnade HotXLS XLSX-motorn formler orörda vid strukturella redigeringar; sedan 2.160 sker omskrivningen automatiskt och det finns ingen flagga som behöver ställas in
Vad händer med formler när du infogar en rad i Excel?
Excels regel är att referenser följer data, inte adresser. När en rad infogas flyttas varje referens vars radindex ligger på eller under infogningspunkten nedåt med antalet infogade rader; referenser helt ovanför infogningspunkten förblir orörda. Att ta bort rader kör samma regel i omvänd ordning: referenser under det raderade blocket glider uppåt, och referenser in i det raderade blocket självt blir #REF! eftersom cellerna de namngav inte längre existerar. Kolumner beter sig på samma sätt längs den andra axeln. Ett kalkylbladsbibliotek som vill att dess utdata ska överleva kontakt med Excel-användare måste återskapa denna mekanik exakt, eftersom användare resonerar kring sina formler i dessa termer utan att ens tänka på det
Det som överraskar utvecklare är att absoluta referenser också flyttas. Ankarna $ i $B$2 styr vad som händer när en formel kopieras eller fylls i till en annan cell — de gör ingenting vid strukturella redigeringar. Infoga en rad ovanför rad 2 och Excel skriver om $B$2 till $B$3, med dollartecknen intakta, eftersom värdet som formeln beror på fysiskt flyttades till rad 3. En motor som endast flyttade relativa referenser skulle korrumpera just de formler som användare förankrar mest medvetet. HotXLS flyttar båda formerna och bevarar $-markörerna i den omskrivna texten
Hur flyttar HotXLS formelreferenser automatiskt?
Alla fyra metoder för strukturell redigering i TXLSXWorksheet delegerar till en och samma geometri-motor: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) anropar den med ett positivt rad-delta, DeleteRows(StartRow, Count) med ett negativt, och kolumnmetoderna gör detsamma på kolumnaxeln. Rutinen flyttar först själva cellerna — och tar bort alla celler som hamnar inuti ett raderat block — och stegar sedan igenom varje kvarvarande formelcell och skickar dess text genom XlsxAdjustFormulaRowColRefs, en skanner som hittar referenser i A1-stil och skriver om deras rad- och kolumnkomponenter mot förskjutningen. Samma passering flyttar sammanfogade intervall, hyperlänkar, kommentarer, bilder, diagram, villkorsstyrda format, datavalideringar och tabellintervall, så att hela bladet flyttas som en enhet
// Layout before the edit:
// C2..C9 daily figures
// C10 =SUM(C2:C9)
Sheet.InsertRows(2, 1); // one blank row before row 2
// Layout after the call:
// C3..C10 daily figures
// C11 =SUM(C3:C10) -- the range moved with the dataNågra detaljer om skannern är värda att känna till. XlsxAdjustFormulaRowColRefs identifierar encellsreferenser i alla fyra ankarformer (A1, $A1, A$1, $A$1) och tvåhörnsintervall som A1:B3, och justerar varje ändpunkt oberoende av varandra. Formler vars text redan börjar med # — en felmarkering från en tidigare redigering — hoppas över istället för att skannas om. Dessutom lägger InsertCols till en egen Excel-liknande finess: nyligen infogade kolumner ärver bredden från sin vänstra granne, vilket är vad Excels kommando Infoga kalkylbladskolumner gör
När blir en raderad referens till #REF!?
Förskjutningsregeln för ett enskilt rad- eller kolumnindex har tre utfall. Ett index före redigeringspunkten förblir oförändrat. Ett index på eller efter redigeringspunkten flyttas med deltat. Och vid radering har ett index som hamnar inuti det raderade blocket inget meningsfullt nytt värde — cellen är borta — så skannern skriver om hela referensen som #REF!. För en intervallsreferens körs båda ändpunkterna genom samma regel, och om någon av ändpunkterna landar inuti det raderade blocket skrivs referensen om till #REF! snarare än att lämnas halvvägs giltig
// A12 holds =A4+A6+A10
Sheet.DeleteRows(5, 3); // delete rows 5..7
// The formula, now in A9, reads =A4+#REF!+A7
// A4 : above the deleted block, unchanged
// A6 : inside rows 5..7, gone -> #REF!
// A10 : below the block, slides up -> A7Att producera ett tydligt #REF! istället för att tyst ändra målet är rätt val, och det är det val Excel gör. En formel som pekar på en granncell efter att dess verkliga indata har raderats skulle returnera ett rimligt men felaktigt nummer; #REF! sprider sig genom beroende formler och kommer upp till ytan vid det första röktestet. Samma omvandling gäller på kolumnaxeln
// E1 holds =B1*$C$1
Sheet.DeleteCols(3, 1); // remove column C
// The formula, now in D1, reads =B1*#REF!
// The absolute anchor did not protect $C$1 -- the cell itself is goneVilka referensformer skrivs inte om?
Skannern riktar in sig på A1-referenser på samma blad med en explicit kolumnbokstav plus radnummer, och det är värt att vara exakt med vad som faller utanför detta. Helkolumnsreferenser som A:A och helradsreferenser som 1:1 saknar en av de två komponenterna, så skannern lämnar dem som de skrevs. Strukturerade tabellreferenser (Table1[Amount]) släpps på samma sätt igenom orörda. Omskrivningen fungerar också strikt på A1-notation — om din kod bygger formler i R1C1-stil, konvertera dem till A1 före en strukturell redigering, som beskrivs i artikeln om R1C1-formelnotation i Delphi
Korsblads- och arbetsbokskonstruktioner
Korsblads- och arbetsbokskonstruktioner hanteras av separata körningar snarare än av cellskannern. Efter justering av det redigerade bladet sprider ShiftSheetGeometry samma geometriändring till formler på andra blad som refererar till det redigerade bladet, till diagramseriers intervall, till interna hyperlänksmål och till definierade namn. Definierade namn får ytterligare skydd på bladets livscykelnivå: sedan version 2.150 skriver borttagning av ett kalkylblad om varje SheetN!-kvalificerare inuti ett definierat namns formel till #REF!, och namnbyte på ett blad skriver om kvalificeraren till det nya namnet, så att namn aldrig pekar på ett blad som inte längre existerar. Hur namn och korsbladsformler hänger ihop behandlas i artikeln om definierade namn och korsbladsformler
Räkna om efter förskjutningen
Referensjustering skriver om formel-text; den räknar inte om resultat. Efter en strukturell redigering beskriver de cachade värdena som lagras tillsammans med formlerna den gamla geometrin, så den tillförlitliga sekvensen är: utför alla infogningar och borttagningar först, trigga sedan omräkning en gång och spara sedan. Att köra förskjutningen före omräkningen håller också beroendeinformationen ärlig — varje omskriven referens namnger sin sanna föregångare, vilket är precis vad den inkrementella omräkningsmotorn och dess beroendegraf behöver för att räkna om den minsta uppsättningen påverkade celler. Om en omskriven formel nu innehåller #REF!, omräkningen lyfter omedelbart fram felvärdet istället för att lämna ett föråldrat nummer i filen
Justering av formelreferenser levereras som standardbeteende för XLSX-motorn i HotXLS Delphi Excel Component för Delphi och C++Builder; produktsidan innehåller den fullständiga API-referensen för kalkylbladsredigering, inklusive de metoder för infogning och borttagning som visas här