Live tværprojektmappe-formler i Delphi med HotXLS

HotXLS kan evaluere en formel som =[Rates.xlsx]FX!B4*C10 mod en anden projektmappe, der er åben i hukommelsen, ikke mod en forældet cachet værdi. Du registrerer den anden projektmappe ved navn med RegisterExternalWorkbook, og hver genberegning læser de refererede celler direkte fra det objekt. Ophæv registreringen, og evalueringen falder tilbage til den eksternlink-cache, filen allerede bærer

Dette lukker et specifikt og frustrerende hul. Excel-projektmapper refererer rutinemæssigt til hinanden: en rentetabel, en kontoplan, en prisliste vedligeholdt af et andet team. Når en sådan fil åbnes af et bibliotek frem for af Excel, opløses disse referencer normalt til, hvilke værdier der nu var cachet, sidst Excel genberegnede, hvilket kan være måneder gammelt, og ingen mængde genberegning retter det, fordi dataene simpelthen ikke er der

Hvordan ser en tværprojektmappe-reference ud inde i filen?

En ekstern reference gemmes ikke som den tekst, du ser i formellinjen. Projektmappen holder en tabel over understøttende mapper, hvert formeltoken peger på et indeks ind i den tabel, og en separat cached-værdier-del holder det senest kendte resultat for hver refereret celle. Det er derfor, en fil åbnet uden sine ledsagere stadig viser tal

Parsing af tekstformen har sine egne finesser. Lexeren behandler et mappenavn i firkantede parenteser som en del af én samlet identifikator, så [Book.xlsx]Sheet!A1 bliver ét token frem for en ubalanceret parentes efterfulgt af en arkreference. Navne normaliseres derefter til opslag: citationstegn, sti-præfikser, filendelsen og den numeriske indeksform kollapser alle til et versalt basisnavn, så 'C:\Rates\[Rates.xlsx]FX'!B4 og [rates.xlsx]FX!B4 opløses til den samme registrerede mappe

Registrering af en projektmappe og genberegning

Registrering sker ved navn, og navnet er det, der optræder i formlerne i den projektmappe, der refererer:

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, der siger [Rates.xlsx]..., læser nu
    // fra det levende objekt i stedet for de cachede eksterne værdier
    Main.RegisterExternalWorkbook('Rates.xlsx', Rates);
    Main.Recalculate;

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

    // Ændr kilden, og genberegn igen: den nye værdi løber igennem
    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 holder en svag reference. HotXLS overtager ikke ejerskabet af den registrerede projektmappe og vil ikke frigive den, hvilket betyder, at levetiden er dit ansvar, og rækkefølgen betyder noget: ophæv registreringen, før du frigiver kilde-projektmappen, eller undlad som minimum at genberegne, efter du har frigivet den

Hvad sker der, når en mappe ikke er registreret?

Evaluering falder tilbage til den eksternlink-cache, der er gemt i filen, hvilket er præcis adfærden for en projektmappe åbnet uden sine ledsagere. Den fallback er grunden til, at ophævelse af registrering er sikker og ikke destruktiv

Adfærden efter ophævelse af registrering er værd at angive præcist, fordi den overrasker folk. Når en reference hverken kan opløses fra en registreret mappe eller fra cachen, giver evalueringen en fejl, og den fejl skrives ikke tilbage i cellen. Cellen beholder sin sidst beregnede værdi. Genregistrér kilden og genberegn, og værdien opdateres igen

Denne bevar-ved-fejl-regel er bevidst. Alternativet, at skrive en fejlværdi ind i hver afhængig celle, i det øjeblik en kilde bliver utilgængelig, ville gøre en midlertidigt manglende rentetabel til en projektmappe fuld af fejlmarkører, som en senere vellykket genberegning skal reparere. At beholde den sidste gode værdi betyder, at en afbrudt pipeline efterlader et dokument, der stadig giver mening at læse

Dirty-propagering stopper ved projektmappens grænse

At redigere en celle i den registrerede kilde markerer ikke afhængige celler i hovedprojektmappen som dirty. Inkrementel genberegning sporer afhængigheder inde i én projektmappe; den overvåger ikke på tværs af grænsen. Excel opfører sig på samme måde, hvilket er grunden til, at dets eksterne referencer opdateres ved åbning eller ved en eksplicit opdatering frem for kontinuerligt

Den praktiske konsekvens er, at ændring af en kildeværdi kræver en eksplicit genberegning af den refererende projektmappe, som i eksemplet ovenfor. Hvis din applikation redigerer begge mapper i en løkke, skal du genberegne én gang til sidst frem for efter hver redigering; den inkrementelle motor beskrevet i inkrementel genberegning og afhængighedsgrafen udfører derefter det mindst mulige arbejde inde i hver projektmappe

Tre former denne funktion passer til

Konsolidering er den oplagte. En overordnet projektmappe refererer til tolv månedlige filer; registrér alle tolv, genberegn én gang, og læs de konsoliderede tal uden nogensinde at åbne Excel. Registreringsnavnet for hver skal matche, hvad den overordnede mappes formler faktisk siger, hvilket er værd at verificere mod formelteksten frem for at antage, at det matcher filnavnet

Scenarieanalyse er den anden. Registrér en kilde, skriv et sæt forudsætninger ind i den, genberegn den afhængige model, registrér outputtene, og gentag så. Fordi kilden er et levende objekt, skrives der ingen fil mellem iterationerne, og hundrede scenarier koster hundrede genberegninger frem for hundrede gem-og-genindlæs-cyklusser

Validering før levering er den tredje og mindst oplagte. Åbn en projektmappe, registrér dens ledsagere, genberegn, og sammenlign resultaterne med de værdier, filen allerede bar. En uoverensstemmelse betyder, at de cachede værdier er forældede, hvilket er en defekt, det er værd at fange, før en kunde modtager filen. Den sammenligning er en naturlig følgesvend til den projektmappe-diffing, der er beskrevet i sammenligning af to Excel-projektmapper

Referencehygiejne, før du stoler på det

Kør først en formelanalysepasning. Den rapporterer manglende funktioner, eksterne referencer og cirkulære afhængighedskomponenter i ét gennemløb, hvilket fortæller dig præcis, hvilke mappenavne et dokument forventer, før du begynder at lede efter filerne:

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;

To andre vaner sparer tid. Hold registreringsnavnet kanonisk — registrér under det rene filnavn, og lad normaliseringen håndtere citerede og sti-præfikserede varianter i formlerne. Og behandl en projektmappe, der refererer til filer, du ikke kan levere, som en skrivebeskyttet artefakt: genberegning bevarer de cachede værdier, men enhver redigering, der ændrer en afhængig celle, efterlader dokumentet i en tilstand, hvor nogle værdier er aktuelle og andre er historiske, uden noget i filen, der siger hvilket er hvad. At kopiere formler mellem projektmapper er en anden operation med sine egne ombindingsregler, dækket i tværprojektmappe-kopiering og formel-ombinding, og navngivne områder tilføjer endnu et lag, beskrevet i definerede navne og tvær-ark-formler

Live tværprojektmappe-evaluering, inkrementel genberegning og formelanalyse kører på den samme motor til Delphi og C++Builder; den komplette funktionsliste findes på HotXLS Delphi spreadsheet component-siden