HotXLS justerer formelreferanser automatisk når du setter inn eller sletter rader eller kolonner i et XLSX-regneark. Motorens metoder InsertRows, DeleteRows, InsertCols og DeleteCols skriver om hver overlevende formel slik at dens A1-referanser — relative, absolutte og område — fortsetter å peke på de samme dataene etter den strukturelle endringen, og referanser inn i en slettet blokk blir til #REF!, noe som samsvarer med oppførselen til Excel
Feilen dette forhindrer, er en av de mest lydløse i rapportgenerering. En generator skriver daglige tall inn i C2:C9 med =SUM(C2:C9) under, og deretter setter et senere trinn inn en overskriftsrad øverst. Hvis motoren bare flytter celleverdier og lar formelteksten være i fred, vil den SUM-en fremdeles lese C2:C9 mens dataene nå lever i C3:C10 — slik at totalsummen lydløst utelater den siste dagen og dobbeltteller en overskrift. Ingenting utløser unntak, filen åpnes fint, og tallet er rett og slett feil. Før versjon 2.160 lot HotXLS XLSX-motoren formler være urørte under strukturelle endringer; siden 2.160 skjer omskrivingen automatisk og det er ingen flagg å sette
Hva skjer med formler når du setter inn en rad i Excel?
Excels regel er at referanser følger data, ikke adresser. Når en rad settes inn, vil hver referanse hvis radindeks ligger på eller under innsettingspunktet forskyves ned med antallet innsatte rader; referanser helt over innsettingspunktet blir ikke berørt. Sletting av rader kjører den samme regelen i omvendt rekkefølge: referanser under den slettede blokken glir opp, og referanser inn i selve den slettede blokken blir til #REF! fordi cellene de navngir ikke lenger eksisterer. Kolonner oppfører seg identisk langs den andre aksen. Et regneark-bibliotek som ønsker at utdataene skal overleve møtet med Excel-brukere, må gjenskape denne mekanikken nøyaktig, fordi brukere tenker på formlene sine i disse banene uten noen gang å tenke over det
Det som overrasker utviklere er at absolutte referanser også flytter seg. Ankrene $ i $B$2 kontrollerer hva som skjer når en formel kopieres eller fylles til en annen celle — de gjør ingenting under strukturelle endringer. Sett inn en rad over rad 2, og Excel skriver om $B$2 til $B$3, med dollartegnene intakte, fordi verdien formelen avhenger av fysisk flyttet til rad 3. En motor som bare forskjøv relative referanser, ville ødelegge akkurat de formlene folk forankrer mest bevisst. HotXLS forskyver begge former og bevarer $-markørene i den omskrevne teksten
Hvordan forskyver HotXLS formelreferanser automatisk?
Alle de fire metodene for strukturell endring på TXLSXWorksheet delegerer til en enkelt geometri-motor: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) kaller den med en positiv raddelta, DeleteRows(StartRow, Count) med en negativ, og kolonnemetodene gjør det samme på kolonneaksen. Rutinen flytter først selve cellene — og sletter eventuelle celler som faller innenfor en slettet blokk — og går deretter gjennom hver overlevende formelcelle og sender teksten gjennom XlsxAdjustFormulaRowColRefs, en skanner som finner A1-stil-referanser og skriver om rad- og kolonnekomponentene i henhold til forskyvningen. Den samme gjennomgangen flytter sammenslåtte områder, hyperkoblinger, kommentarer, bilder, diagrammer, betinget formatering, datavalidering og tabellområder, slik at hele arket flyttes som én 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 dataEnkelte detaljer ved skanneren er verdt å kjenne til. XlsxAdjustFormulaRowColRefs gjenkjenner enkeltcellereferanser i alle de fire ankerformene (A1, $A1, A$1, $A$1) og områder med to hjørner som A1:B3, og justerer hvert endepunkt uavhengig. Formler hvis tekst allerede begynner med # — en feilmarkør fra en tidligere endring — hoppes over i stedet for å skannes på nytt. Og InsertCols legger til en fin detalj for samsvar med Excel: nylig innsatte kolonner arver bredden til sin venstre nabo, noe som er det Excels Insert Sheet Columns-kommando gjør
Når blir en slettet referanse til #REF!?
Forskyvningsregelen for en enkelt rad- eller kolonneindeks hat tre utfall. En indeks før endringspunktet er uendret. En indeks på eller etter endringspunktet flyttes med deltaen. Og ved sletting har en indeks som faller innenfor den slettede blokken ingen meningsfull ny verdi — cellen er borte — så skanneren skriver om hele referansen som #REF!. For en områdereferanse kjøres begge endepunkter gjennom den samme regelen, og hvis ett av endepunktene lander innenfor den slettede blokken, skrives referansen om som #REF! i stedet for å etterlates halvveis gyldig
// 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 -> A7Å produsere en tydelig #REF! i stedet for å lydløst peke om er den riktige avveiningen, og det er den Excel gjør. En formel som peker på en nabocelle etter at dens virkelige inndata ble slettet, ville returnere et tall som ser sannsynlig ut; #REF! forplanter seg gjennom avhengige formler og dukker opp i den første røyksjekken. Den samme konverteringen gjelder på kolonneaksen
// 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 goneHvilke referanseformer blir ikke omskrevet?
Skanneren retter seg mot A1-referanser på samme ark med en eksplisitt form av kolonnetall-pluss-radnummer, og det er verdt å være presis om hva som faller utenfor dette. Referanser for hele kolonner som A:A og hele rader som 1:1 mangler en av de to komponentene, så skanneren lar dem være som de er skrevet. Strukturerte tabellreferanser (Table1[Amount]) sendes på samme måte urørt igjennom. Omskrivingen opererer også strengt på A1-notasjon — hvis koden din bygger formler i R1C1-stil, må du konvertere dem til A1 før en strukturell endring, som beskrevet i den tilhørende artikkelen om R1C1-formelnotasjon i Delphi
Beregn på nytt etter forskyvningen
Konstruksjoner på tvers av ark og på arbeidsboknivå håndteres av separate gjennomganger i stedet for av celle-tekstskanneren. Etter justering av det endrede arket, forplanter ShiftSheetGeometry den samme geometri-endringen til formler på andre ark som refererer til det endrede arket, til diagramserier, til interne hyperkoblingsmål og til definerte navn. Definerte navn får ytterligere beskyttelse på arklivssyklus-nivå: siden versjon 2.150, sletting av et regneark skriver om hver SheetN!-kvalifikator inni formelen for et definert navn til #REF!, og endring av navn på et ark skriver om kvalifikatoren til det nye navnet, slik at navn aldri peker på et ark som ikke lenger eksisterer. Hvordan navn og formler på tvers av ark passer sammen, dekkes i artikkelen om definerte navn og formler på tvers av ark
Referansejustering skriver om formel-tekst; den beregner ikke resultater på nytt. Etter en strukturell endring beskriver de hurtigbufrede verdiene lagret sammen med formler den gamle geometrien, så den pålitelige sekvensen er: utfør alle innsettinger og slettinger først, utløs deretter rekalkulering én gang, og lagre deretter. Å kjøre forskyvningen før rekalkulering holder også avhengighetsinformasjonen ærlig — hver omskrevne referanse navngir sin sanne forløper, noe som er akkurat det den inkrementelle rekalkuleringsmotoren og dens avhengighetsgraf trenger for å beregne det minimale settet av berørte celler på nytt. Hvis en omskrevet formel nå inneholder #REF!, vil rekalkuleringen vise feilverdien umiddelbart i stedet for å etterlate et foreldet tall i filen
Justering av formelreferanser leveres som standard oppførsel for XLSX-motoren i HotXLS Delphi Excel Component for Delphi og C++Builder; produktsiden inneholder den fullstendige API-referansen for redigering av regneark, inkludert innsettings- og slettingsmetodene som vises her