Live cross-workbookformules in Delphi met HotXLS

HotXLS kan een formule zoals =[Rates.xlsx]FX!B4*C10 evalueren tegen een andere werkmap die in het geheugen is geopend, niet tegen een verouderde gecachete waarde. U registreert de tweede werkmap op naam met RegisterExternalWorkbook, en elke herberekening leest de aangehaalde cellen rechtstreeks uit dat object. Deregistreer het en de evaluatie valt terug op de cache van externe koppelingen die het bestand al bevat

Dit dicht een specifiek en frustrerend gat. Excel-werkmappen verwijzen routinematig naar elkaar: een rentetabel, een rekeningschema, een prijslijst bijgehouden door een ander team. Wanneer zo'n bestand door een library wordt geopend in plaats van door Excel, lossen die verwijzingen normaal gesproken op naar wat er ook maar was gecachet toen Excel voor het laatst herberekende, wat maanden oud kan zijn, en geen enkele hoeveelheid herberekenen lost dat op omdat de data simpelweg niet aanwezig is

Hoe ziet een cross-workbookverwijzing er binnen het bestand uit?

Een externe verwijzing wordt niet opgeslagen als de tekst die u in de formulebalk ziet. De werkmap houdt een tabel van ondersteunende boeken bij, elk formuletoken wijst naar een index in die tabel, en een apart onderdeel met gecachete waarden bewaart het laatst bekende resultaat voor elke aangehaalde cel. Dat is waarom een bestand dat zonder zijn metgezellen wordt geopend nog steeds getallen toont

Het parsen van de tekstvorm heeft zijn eigen finesses. De lexer behandelt een boeknaam tussen vierkante haken als onderdeel van één identifier, dus [Book.xlsx]Sheet!A1 wordt één token in plaats van een ongebalanceerd haakje gevolgd door een bladverwijzing. Namen worden vervolgens genormaliseerd voor opzoeking: aanhalingstekens, padvoorvoegsels, de bestandsextensie en de numerieke-indexvorm vallen allemaal samen tot een naam met hoofdletters, dus 'C:\Rates\[Rates.xlsx]FX'!B4 en [rates.xlsx]FX!B4 lossen op naar hetzelfde geregistreerde boek

Een werkmap registreren en herberekenen

Registratie gebeurt op naam, en de naam is degene die voorkomt in de formules van de werkmap die de verwijzing maakt:

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;

    // Formules in valuation.xlsx die [Rates.xlsx]... zeggen, lezen nu
    // uit het live object in plaats van de gecachete externe waarden
    Main.RegisterExternalWorkbook('Rates.xlsx', Rates);
    Main.Recalculate;

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

    // Verander de bron en herbereken opnieuw: de nieuwe waarde stroomt door
    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;

De registratie houdt een zwakke referentie aan. HotXLS neemt geen eigendom over de geregistreerde werkmap en zal deze niet vrijgeven, wat betekent dat de levensduur uw verantwoordelijkheid is en de volgorde ertoe doet: deregistreer voordat u de bronwerkmap vrijgeeft, of herbereken op zijn minst niet nadat u deze hebt vrijgegeven

Wat gebeurt er wanneer een boek niet geregistreerd is?

De evaluatie valt terug op de cache van externe koppelingen die in het bestand is opgeslagen, wat precies het gedrag is van een werkmap die zonder zijn metgezellen wordt geopend. Die terugval is de reden waarom deregistreren veilig is in plaats van destructief

Het gedrag na deregistratie is het precies benoemen waard, omdat het mensen verrast. Wanneer een verwijzing niet kan worden opgelost, noch vanuit een geregistreerd boek, noch vanuit de cache, produceert de evaluatie een fout en die fout wordt niet teruggeschreven naar de cel. De cel behoudt zijn laatst berekende waarde. Registreer de bron opnieuw en herbereken, en de waarde wordt weer bijgewerkt

Deze regel om bij een fout de waarde te behouden, is bewust. Het alternatief, een foutwaarde in elke afhankelijke cel schrijven zodra een bron niet beschikbaar is, zou een tijdelijk ontbrekende rentetabel veranderen in een werkmap vol foutmarkeringen die een latere geslaagde herberekening moet herstellen. De laatste goede waarde behouden betekent dat een onderbroken pijplijn een document achterlaat dat nog steeds zinvol leest

Dirty-propagatie stopt bij de werkmapgrens

Het bewerken van een cel in de geregistreerde bron markeert afhankelijke cellen in de hoofdwerkmap niet als dirty. Incrementele herberekening volgt afhankelijkheden binnen één werkmap; ze kijkt niet over de grens heen. Excel gedraagt zich hetzelfde, wat de reden is dat externe verwijzingen bij het openen of bij een expliciete refresh worden bijgewerkt in plaats van continu

Het praktische gevolg is dat het wijzigen van een bronwaarde een expliciete herberekening van de verwijzende werkmap vereist, zoals in het voorbeeld hierboven. Als uw toepassing beide boeken in een lus bewerkt, herbereken dan één keer aan het einde in plaats van na elke bewerking; de incrementele engine beschreven in incrementele herberekening en de afhankelijkheidsgraaf doet dan het minimale werk binnen elke werkmap

Drie vormen waarin deze functie past

Consolidatie is de voor de hand liggende. Een ouderwerkmap verwijst naar twaalf maandbestanden; registreer alle twaalf, herbereken één keer, en lees de geconsolideerde cijfers zonder ooit Excel te openen. De registratienaam voor elk moet overeenkomen met wat de formules van de ouder daadwerkelijk zeggen, wat het waard is te verifiëren tegen de formuletekst in plaats van aan te nemen dat het overeenkomt met de bestandsnaam

Scenario-analyse is de tweede. Registreer een bron, schrijf een set aannames erin, herbereken het afhankelijke model, leg de outputs vast, herhaal dan. Omdat de bron een live object is, wordt er tussen iteraties geen bestand geschreven, en honderd scenario's kosten honderd herberekeningen in plaats van honderd opslaan-en-herladen-cycli

Validatie vóór levering is de derde en de minst voor de hand liggende. Open een werkmap, registreer zijn metgezellen, herbereken, en vergelijk de resultaten met de waarden die het bestand al bevatte. Een mismatch betekent dat de gecachete waarden verouderd zijn, wat een defect is dat het waard is te vangen voordat een klant het bestand ontvangt. Die vergelijking is een natuurlijke metgezel van de werkmapvergelijking beschreven in twee Excel-werkmappen vergelijken

Verwijzingshygiëne voordat u erop vertrouwt

Voer eerst een formulanalysepas uit. Die rapporteert ontbrekende functies, externe verwijzingen en circulaire afhankelijkheidscomponenten in één doorloop, wat u precies vertelt welke boeknamen een document verwacht voordat u naar de bestanden gaat zoeken:

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;

Twee andere gewoonten besparen tijd. Houd de registratienaam canoniek — registreer onder de gewone bestandsnaam en laat de normalisatie de variaties met aanhalingstekens en padvoorvoegsels in de formules afhandelen. En behandel een werkmap die verwijst naar bestanden die u niet kunt aanleveren als een alleen-lezen artefact: herberekenen behoudt de gecachete waarden, maar elke bewerking die een afhankelijke cel verandert, laat het document achter in een toestand waarin sommige waarden actueel zijn en andere historisch, zonder dat het bestand aangeeft welke welke is. Formules tussen werkmappen kopiëren is een andere bewerking met eigen rebindingsregels, behandeld in cross-workbookkopiëren en formulerebindings, en benoemde bereiken voegen nog een laag toe, beschreven in benoemde bereiken en formules tussen bladen

Live cross-workbookevaluatie, incrementele herberekening en formulanalyse draaien op dezelfde engine voor Delphi en C++Builder; de volledige functielijst staat op de HotXLS Delphi spreadsheetcomponent-pagina