Levande formler mellan arbetsböcker i Delphi med HotXLS

HotXLS kan beräkna en formel som =[Rates.xlsx]FX!B4*C10 mot en annan arbetsbok som är öppen i minnet, inte mot ett inaktuellt cachat värde. Du registrerar den andra arbetsboken med namn via RegisterExternalWorkbook, och varje omberäkning läser de refererade cellerna direkt från det objektet. Avregistrera den och beräkningen faller tillbaka till cachen för externa länkar som filen redan bär med sig

Detta tätar en specifik och frustrerande lucka. Excel-arbetsböcker refererar rutinmässigt till varandra: en räntetabell, en kontoplan, en prislista som underhålls av ett annat team. När en sådan fil öppnas av ett bibliotek snarare än av Excel löses dessa referenser normalt upp till vilka värden som helst som cachades senaste gången Excel omberäknade, vilket kan vara månader gammalt, och ingen mängd omberäkning fixar det eftersom datan helt enkelt inte finns där

Hur ser en referens mellan arbetsböcker ut inuti filen?

En extern referens lagras inte som texten du ser i formelfältet. Arbetsboken håller en tabell över stödjande böcker, varje formeltoken pekar på ett index i den tabellen, och en separat del för cachade värden håller det senast kända resultatet för varje refererad cell. Det är därför en fil öppnad utan sina följeslagare fortfarande visar siffror

Att parsa textformen har sina egna finesser. Lexern behandlar ett hakparentesomslutet boknamn som en del av en enda identifierare, så [Book.xlsx]Sheet!A1 blir en enda token snarare än en obalanserad hakparentes följd av en bladreferens. Namn normaliseras sedan för uppslagning: citering, sökvägsprefix, filändelsen och den numeriska indexformen kollapsar alla till ett versalt basnamn, så 'C:\Rates\[Rates.xlsx]FX'!B4 och [rates.xlsx]FX!B4 löses upp till samma registrerade bok

Att registrera en arbetsbok och omberäkna

Registrering sker via namn, och namnet är det som förekommer i formlerna i arbetsboken som gör referensen:

uses
  lxHandleX;

var
  Main, Rates: TXLSXWorkbook;
begin
  Main := TXLSXWorkbook.Create;
  Rates := TXLSXWorkbook.Create;
  try
    if (Main.Open('valuation.xlsx') <> 1) or
       (Rates.Open('rates.xlsx') <> 1) then
      Exit;

    // Formler i valuation.xlsx som säger [Rates.xlsx]... läser nu
    // från det levande objektet istället för de cachade externa värdena
    Main.RegisterExternalWorkbook('Rates.xlsx', Rates);
    Main.Recalculate;

    Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));

    // Ändra källan och omberäkna igen: det nya värdet flödar igenom
    Rates.Sheets[0].Cells[4, 2].Value := 1.0842;
    Main.Recalculate;
    Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));
  finally
    Main.UnregisterExternalWorkbook('Rates.xlsx');
    Rates.Free;
    Main.Free;
  end;
end;

Registreringen håller en svag referens. HotXLS tar inte över ägandet av den registrerade arbetsboken och kommer inte att frigöra den, vilket betyder att livstiden är din att hantera och ordningen spelar roll: avregistrera innan du frigör källarbetsboken, eller åtminstone omberäkna inte efter att ha frigjort den

Vad händer när en bok inte är registrerad?

Beräkningen faller tillbaka till cachen för externa länkar som lagras i filen, vilket är exakt beteendet hos en arbetsbok öppnad utan sina följeslagare. Det reservläget är anledningen till att avregistrering är säkert snarare än destruktivt

Beteendet efter avregistrering är värt att ange precist, eftersom det överraskar folk. När en referens varken kan lösas från en registrerad bok eller från cachen, ger beräkningen ett fel och det felet skrivs inte tillbaka till cellen. Cellen behåller sitt senast beräknade värde. Registrera om källan och omberäkna, så uppdateras värdet igen

Denna regel om att bevara vid fel är avsiktlig. Alternativet, att skriva ett felvärde till varje beroende cell i samma ögonblick en källa blir otillgänglig, skulle förvandla en tillfälligt saknad räntetabell till en arbetsbok full av felmarkörer som en senare lyckad omberäkning måste reparera. Att behålla det senaste goda värdet betyder att en avbruten pipeline lämnar ett dokument som fortfarande läses förnuftigt

Ändringsspridning stannar vid arbetsbokens gräns

Att redigera en cell i den registrerade källan markerar inte beroende celler i huvudarbetsboken som ändrade. Inkrementell omberäkning spårar beroenden inuti en arbetsbok; den bevakar inte över gränsen. Excel beter sig på samma sätt, vilket är anledningen till att dess externa referenser uppdateras vid öppning eller vid en explicit uppdatering snarare än kontinuerligt

Den praktiska konsekvensen är att en ändring av ett källvärde kräver en explicit omberäkning av den refererande arbetsboken, som i exemplet ovan. Om din applikation redigerar båda böckerna i en loop, omberäkna en gång i slutet snarare än efter varje redigering; motorn för inkrementell beräkning som beskrivs i inkrementell omberäkning och beroendegrafen gör då minsta möjliga arbete inuti varje arbetsbok

Tre former denna funktion passar

Konsolidering är den uppenbara. En överordnad arbetsbok refererar till tolv månadsfiler; registrera alla tolv, omberäkna en gång, och läs de konsoliderade siffrorna utan att någonsin öppna Excel. Registreringsnamnet för var och en måste matcha vad den överordnade arbetsbokens formler faktiskt säger, vilket är värt att verifiera mot formeltexten snarare än att anta att det matchar filnamnet

Scenarioanalys är den andra. Registrera en källa, skriv en uppsättning antaganden till den, omberäkna den beroende modellen, registrera resultaten, och upprepa. Eftersom källan är ett levande objekt skrivs ingen fil mellan iterationerna, och hundra scenarier kostar hundra omberäkningar snarare än hundra spara-och-ladda-om-cykler

Validering före leverans är den tredje och den minst uppenbara. Öppna en arbetsbok, registrera dess följeslagare, omberäkna, och jämför resultaten mot de värden filen redan bar med sig. En avvikelse betyder att de cachade värdena är inaktuella, vilket är en defekt värd att fånga innan en klient tar emot filen. Den jämförelsen är en naturlig följeslagare till jämförelsen av arbetsböcker som beskrivs i att jämföra två Excel-arbetsböcker

Referenshygien innan du förlitar dig på det

Kör en formelanalyspassering först. Den rapporterar saknade funktioner, externa referenser och cirkulära beroendekomponenter i en enda genomgång, vilket talar om exakt vilka boknamn ett dokument förväntar sig innan du börjar leta efter filerna:

var
  Issues: TArray<TXLSXFormulaIssue>;
  I: Integer;
begin
  Issues := Main.AnalyzeFormulaIssues;
  for I := 0 to High(Issues) do
    if Issues[I].Kind = xlsxFormulaIssueExternalReference then
      Writeln(Format('sheet %d R%dC%d needs [%s] (%s)',
        [Issues[I].Sheet, Issues[I].Row, Issues[I].Col,
         Issues[I].ExternalWorkbook, Issues[I].SourceFileName]));
end;

Två andra vanor sparar tid. Håll registreringsnamnet kanoniskt — registrera under det enkla filnamnet och låt normaliseringen hantera citerade och sökvägsprefixade varianter i formlerna. Och behandla en arbetsbok som refererar till filer du inte kan tillhandahålla som ett skrivskyddat artefakt: att omberäkna den bevarar de cachade värdena, men varje redigering som ändrar en beroende cell lämnar dokumentet i ett tillstånd där vissa värden är aktuella och andra historiska, utan något i filen som säger vilket som är vilket. Att kopiera formler mellan arbetsböcker är en annan operation med sina egna ombindningsregler, som täcks i kopiering mellan arbetsböcker och ombindning av formler, och namngivna intervall lägger till ytterligare ett lager som beskrivs i definierade namn och formler mellan blad

Levande beräkning mellan arbetsböcker, inkrementell omberäkning och formelanalys körs på samma motor för Delphi och C++Builder; den fullständiga funktionslistan finns på HotXLS Delphi kalkylbladskomponentsidan