Elävät työkirjojen väliset kaavat Delphissä HotXLS:llä

HotXLS voi laskea kaavan, kuten =[Rates.xlsx]FX!B4*C10, toista muistissa avoinna olevaa työkirjaa vasten, ei vanhentunutta välimuistiarvoa vasten. Rekisteröit toisen työkirjan nimellä komennolla RegisterExternalWorkbook, ja jokainen uudelleenlaskenta lukee viitatut solut suoraan siitä objektista. Poista rekisteröinti, ja laskenta palaa tiedoston jo kantamaan ulkoisen linkin välimuistiin

Tämä sulkee tietyn ja turhauttavan aukon. Excel-työkirjat viittaavat toisiinsa säännöllisesti: korkotaulukko, tilikartta, hinnasto, jota toinen tiimi ylläpitää. Kun tällaisen tiedoston avaa kirjasto eikä Excel, nuo viittaukset ratkeavat normaalisti mihin tahansa arvoihin, jotka olivat välimuistissa viimeksi kun Excel laski uudelleen, mikä voi olla kuukausia vanhaa, eikä mikään määrä uudelleenlaskentaa korjaa sitä, koska dataa ei yksinkertaisesti ole siellä

Miltä työkirjojen välinen viittaus näyttää tiedoston sisällä?

Ulkoinen viittaus ei tallennu tekstinä, jonka näet kaavarivillä. Työkirja pitää taulukkoa tukevista kirjoista, jokainen kaavatoken osoittaa indeksiin tuossa taulukossa, ja erillinen välimuistiarvo-osa pitää viimeisimmän tunnetun tuloksen jokaiselle viitatulle solulle. Siksi tiedosto, joka avataan ilman kumppaneitaan, näyttää silti numeroita

Tekstimuodon jäsentämisellä on omat hienovaraisuutensa. Lekseri käsittelee sulkeisiin laitettua kirjan nimeä osana yhtä tunnistetta, joten [Book.xlsx]Sheet!A1:sta tulee yksi token eikä epätasapainoinen sulku, jota seuraa taulukkoviittaus. Nimet normalisoidaan sitten hakua varten: lainausmerkit, polkuetuliitteet, tiedostopääte ja numeerinen indeksimuoto romahtavat kaikki isoksi kirjoitettuun perusnimeen, joten 'C:\Rates\[Rates.xlsx]FX'!B4 ja [rates.xlsx]FX!B4 ratkeavat samaan rekisteröityyn kirjaan

Työkirjan rekisteröinti ja uudelleenlaskenta

Rekisteröinti tapahtuu nimellä, ja nimi on se, joka esiintyy viittaavan työkirjan kaavoissa:

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;

    // valuation.xlsx:n kaavat, jotka sanovat [Rates.xlsx]..., lukevat nyt
    // elävästä objektista sen sijaan, että lukisivat välimuistin ulkoisia arvoja
    Main.RegisterExternalWorkbook('Rates.xlsx', Rates);
    Main.Recalculate;

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

    // Muuta lähdettä ja laske uudelleen: uusi arvo virtaa läpi
    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;

Rekisteröinti pitää heikkoa viitettä. HotXLS ei ota omistukseen rekisteröityä työkirjaa eikä vapauta sitä, mikä tarkoittaa, että elinkaari on sinun hallittavanasi, ja järjestyksellä on väliä: poista rekisteröinti ennen lähdetyökirjan vapauttamista, tai vähintään älä laske uudelleen sen vapauttamisen jälkeen

Mitä tapahtuu, kun kirjaa ei ole rekisteröity?

Laskenta palaa tiedostossa tallennettuun ulkoisen linkin välimuistiin, mikä on täsmälleen se käytös, joka on työkirjalla, joka avataan ilman kumppaneitaan. Se varareitti on syy siihen, miksi rekisteröinnin poistaminen on turvallista eikä tuhoisaa

Käytös rekisteröinnin poiston jälkeen kannattaa todeta tarkasti, koska se yllättää ihmiset. Kun viittausta ei voida ratkaista rekisteröidystä kirjasta eikä välimuistista, laskenta tuottaa virheen, eikä sitä virhettä kirjoiteta takaisin soluun. Solu säilyttää viimeksi lasketun arvonsa. Rekisteröi lähde uudelleen ja laske uudelleen, ja arvo päivittyy jälleen

Tämä säilytä-epäonnistuessa-sääntö on tarkoituksellinen. Vaihtoehto, virhearvon kirjoittaminen jokaiseen riippuvaan soluun heti kun lähde ei ole saatavilla, muuttaisi väliaikaisesti puuttuvan korkotaulukon työkirjaksi täynnä virhemerkkejä, jotka myöhemmän onnistuneen uudelleenlaskennan täytyy korjata. Viimeisen hyvän arvon säilyttäminen tarkoittaa, että keskeytynyt putki jättää jälkeensä asiakirjan, joka lukee edelleen järkevästi

Likaisuuden leviäminen pysähtyy työkirjan rajalle

Solun muokkaaminen rekisteröidyssä lähteessä ei merkitse pääasiallisen työkirjan riippuvaisia soluja likaisiksi. Inkrementaalinen uudelleenlaskenta seuraa riippuvuuksia yhden työkirjan sisällä; se ei tarkkaile rajan yli. Excel käyttäytyy samalla tavalla, minkä vuoksi sen ulkoiset viittaukset päivittyvät avattaessa tai eksplisiittisessä päivityksessä eikä jatkuvasti

Käytännön seuraus on, että lähdearvon muuttaminen vaatii viittaavan työkirjan eksplisiittisen uudelleenlaskennan, kuten yllä olevassa esimerkissä. Jos sovelluksesi muokkaa molempia kirjoja silmukassa, laske uudelleen kerran lopussa jokaisen muokkauksen jälkeen laskemisen sijaan; inkrementaalinen moottori, kuvattu artikkelissa inkrementaalinen uudelleenlaskenta ja riippuvuusgraafi, tekee sitten minimityön jokaisen työkirjan sisällä

Kolme muotoa, joihin tämä ominaisuus sopii

Konsolidointi on ilmeisin. Emo-työkirja viittaa kahteentoista kuukausittaiseen tiedostoon; rekisteröi kaikki kaksitoista, laske uudelleen kerran, ja lue konsolidoidut luvut avaamatta koskaan Exceliä. Kunkin rekisteröintinimen täytyy täsmätä sitä, mitä emo-työkirjan kaavat todella sanovat, mikä kannattaa varmistaa kaavatekstistä eikä olettaa sen täsmäävän tiedostonimeen

Skenaarioanalyysi on toinen. Rekisteröi lähde, kirjoita joukko oletuksia siihen, laske riippuvainen malli uudelleen, tallenna tulosteet, ja toista. Koska lähde on elävä objekti, tiedostoja ei kirjoiteta iteraatioiden välillä, ja sata skenaariota maksaa sata uudelleenlaskentaa eikä sataa tallenna-ja-lataa-uudelleen-kierrosta

Validointi ennen toimitusta on kolmas ja vähiten ilmeinen. Avaa työkirja, rekisteröi sen kumppanit, laske uudelleen, ja vertaa tuloksia arvoihin, joita tiedosto jo kantoi. Poikkeama tarkoittaa, että välimuistiarvot ovat vanhentuneet, mikä on virhe, joka kannattaa napata ennen kuin asiakas saa tiedoston. Tuo vertailu on luonnollinen kumppani työkirjadiffauksen kanssa, kuvattu artikkelissa kahden Excel-työkirjan diffaus

Viittaushygienia ennen kuin luotat siihen

Aja ensin kaava-analyysikierros. Se raportoi puuttuvat funktiot, ulkoiset viittaukset ja kehäriippuvuuskomponentit yhdessä läpikäynnissä, mikä kertoo tarkalleen, mitä kirjannimiä asiakirja odottaa ennen kuin lähdet etsimään tiedostoja:

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;

Kaksi muuta tapaa säästävät aikaa. Pidä rekisteröintinimi kanonisena — rekisteröi pelkän tiedostonimen alla ja anna normalisoinnin käsitellä lainattuja ja polkuetuliitteisiä muunnelmia kaavoissa. Ja kohtele työkirjaa, joka viittaa tiedostoihin, joita et voi toimittaa, vain luku -artefaktina: sen uudelleenlaskenta säilyttää välimuistiarvot, mutta mikä tahansa muokkaus, joka muuttaa riippuvaisen solun, jättää asiakirjan tilaan, jossa jotkin arvot ovat ajan tasalla ja jotkin historiallisia, eikä mikään tiedostossa kerro kumpi on kumpi. Kaavojen kopiointi työkirjojen välillä on eri operaatio omine uudelleensidontasääntöineen, käsitelty artikkelissa työkirjojen välinen kopiointi ja kaavan uudelleensidonta, ja nimetyt alueet lisäävät toisen kerroksen, kuvattu artikkelissa nimetyt alueet ja laskentataulukoiden väliset kaavat

Elävä työkirjojen välinen laskenta, inkrementaalinen uudelleenlaskenta ja kaava-analyysi toimivat samalla moottorilla Delphille ja C++Builderille; täydellinen ominaisuusluettelo löytyy sivulta HotXLS Delphi-laskentataulukkokomponentin sivu