Definerte navn og formler på tvers av ark i Delphi med HotXLS

Et definert navn er en etikett som står i stedet for en konstant, et celleområde eller et formeluttrykk, lagret én gang i arbeidsboken og referert symbolsk overalt der det trengs. Skriv TaxRate i en formel, og motoren løser det opp til det navnets definisjon inneholder, enten det er literalen 0.08 eller området Data!$A$2:$D$100. En arkkryssende referanse er den ortogonale ideen: Data!D2 når en celle på et annet ark ved å kvalifisere adressen med et arknavn. Sett de to sammen, og et sammendragsark kan summere et detaljark gjennom et navn som aldri nevner en literal adresse, som er akkurat det du vil ha i en arbeidsbok en generator setter sammen og en regnskapsfører senere reviderer

HotXLS, losLabs native Delphi-bibliotek for XLS- og XLSX-filer, eksponerer navnetabellen i begge formatene med opprett-, finn- og slett-tilgang, pluss en formelmotor som løser navn og arkkryssende referanser i prosessen. De to formatene holder atskilte klassehierarkier, og forskjellene mellom navne-API-ene deres er det som velter kode portert fra det ene til det andre

To navnelagre som ikke deler et grensesnitt

På XLS-siden returnerer TXLSWorkbook.GetNames en IXLSNames-samling hvis Add(Name, RefersTo, Visible)-overbelastning skriver et navn inn i BIFF-navnetabellen. Individuelle oppføringer kommer tilbake som IXLSName-objekter som bærer Name, RefersTo, et løst RefersToRange, og en Delete-metode. På XLSX-siden er TXLSXWorkbook.DefinedNames en TXLSXDefinedNames-samling med Add, FindByName og DeleteByName

Oppslagskonvensjonene divergerer på en måte som dukker opp under porting snarere enn ved kompileringstidspunktet. XLS-samlingens standard Item-egenskap godtar en Variant, så både Names[0] og Names['TaxRate'] løses opp mot den. XLSX-samlingen har ingen slik standardegenskap; du kaller FindByName('TaxRate'), som returnerer nil når navnet mangler. Kode skrevet for én fasade kompilerer mot den andre bare ved et uhell, og feilen har en tendens til å dukke opp som en nil-tilgang ved kjøretid i stedet for en rød snirkel i IDE-en

Omfang er den første beslutningen, ikke et flagg du legger til senere

Et definert navn er enten arbeidsbok-omfattet, synlig for formler på hvert ark, eller ark-omfattet, synlig bare for formler på arket som eier det. I XLSX-API-et er skillet én enkelt valgfri parameter. DefinedNames.Add(AName, AFormula) lager et navn på arbeidsboknivå, mens Add(AName, AFormula, ASheetIndex) binder det til ett ark. Når du leser det tilbake, returnerer TXLSXDefinedName.SheetIndex -1 for arbeidsbokomfang og den 0-baserte arkindeksen ellers

Omfang fungerer også som kollisjonspolicyen din, og det er grunnen til å avklare det før du skriver det første navnet. Excel tillater et arklokalt Total på hvert ark pluss et Total på arbeidsboknivå, og en formel på et gitt ark løser opp det lokale først. Genererte arbeidsbøker bør lene seg bevisst på det. Forretningsforutsetninger som flere ark bruker, som skattesatser, valutakurser og rapporteringsperioden, hører hjemme på arbeidsboknivå. Hjelpeområder som bare ett arks formler refererer til, er tryggere ark-omfattet, der ingenting kan overskygge dem og de kan overskygge ingenting

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... fyll Data!A2:D100 med detaljrader ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // arbeidsbokomfang, en konstant
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // arbeidsbokomfang, et område
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // omfattet til bare arkindeks 1

    // XLSX-formler tar ingen innledende '='
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

Et definert navn må ikke peke på et område. TaxRate ovenfor refererer til den bare konstanten 0.08, og det er den reneste måten å publisere en forretningsforutsetning på. Det vises én gang i Excels Name Manager, hver formel refererer til det symbolsk, og neste kvartals satsendring er en énlinjes redigering av generatoren i stedet for et søk gjennom fjorten sammensatte formelstrenger

Likhetstegnet som bare hører hjemme på én side

Formelinnføringskanalen er der portert kode bryter sammen oftest, fordi de to fasadene er uenige om likhetstegnet. XLS-celler mottar formler gjennom Value med en innledende =. XLSX-celler har en dedikert Formula-egenskap som tar uttrykket uten prefikset. Skriv '=SUM(A1:A10)' inn i TXLSXCell.Formula, og likhetstegnet blir en del av den lagrede uttrykksteksten i stedet for en markør, og filen vil ikke oppføre seg slik den samme strengen gjorde på XLS-siden

var
  Book: IXLSWorkbook;   // grensesnitt-tellet: ikke kall Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // anta at et ark ved navn 'Data' allerede holder detaljradene
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = skjult fra Name Manager

  // XLS-formler går gjennom Value, med '='-prefikset
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

Det utdraget viser to til av XLS-sidens særegenheter. Arksamlingen er 1-basert, så Sheets[1] er det første arket, mot den 0-baserte XLSX-en sin Sheets[0]. Og den tredje Add-parameteren lager et skjult navn: til stede i filen og brukbart av formler, men usynlig i Excels Name Manager. Skjulte navn er rett kjøretøy for generator-intern rørlegging som sluttbrukere aldri bør redigere eller slette ved et uhell

Arkkryssende referanser, og hva som skjer når rader flytter seg

Begge formelmotorene godtar standard arkkryssende syntaks. Vanlige arknavn kvalifiserer direkte som Data!A1; et navn med mellomrom eller tegnsetting trenger enkle anførselstegn, som i 'Sheet With Space'!A1. Inne i et navns RefersTo-tekst, grip nesten alltid til absolutte referanser som Data!$A$2:$D$100. En relativ referanse inne i et definert navn løses opp relativt til cellen som bruker det, som er en bevisst Excel-funksjon og en pålitelig kilde til forvirring når den utløses ved et uhell

Strukturelle redigeringer er der arkkryssende bokføring virkelig gjør nytte for seg, og XLSX-siden holder navn konsistente gjennom dem. InsertRows og DeleteRows flytter definerte navns områder sammen med celler, sammenslåinger, hyperlenker og diagramankere, så et navn som peker på Data!$A$2:$D$100, dekker fortsatt datablokken etter at generatoren åpner et gap over den. Formler kommer med ett dokumentert forbehold: radinnsetting justerer bare referansene som retter seg mot arket som redigeres. En Summary-formel som refererer til Data!D2:D100, skrives om når rader settes inn i Data, som er tilfellet du vanligvis vil ha. Verifiser det i stedet for å anta det, fordi motoren forteller deg det billig:

// beregningsmotoren løser navn og arkkryssende referanser i prosessen
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate evaluerer et vilkårlig uttrykk mot arbeidsbokens nåværende tilstand uten å lagre noe, noe som gjør den til den naturlige påstandsprimitiven for generatortester. Beregn det forventede aggregatet fra kildedataene i Pascal, evaluer arbeidsbokens egen formel, og sammenlign de to. Artikkelen om formelmotoren dekker hva motoren evaluerer, når, og hvordan du utvider den med egendefinerte funksjoner

_xlnm-navnene som egenskapslaget eier

Åpne en generert fils navnetabell i en lavnivåinspektør, og du vil finne oppføringer du aldri skrev: _xlnm.Print_Area, _xlnm.Print_Titles, og slektningene deres. Dette er hvordan OOXML (ECMA-376 / ISO 29500) lagrer utskriftsområder og gjentatte overskriftsrader, som definerte navn med reserverte identifikatorer. HotXLS forvalter dem gjennom dedikerte regnearkegenskaper, så det å sette PrintArea eller PrintTitleRows skriver den tilsvarende _xlnm.*-oppføringen for deg

Fellen er å gripe inn i det reserverte navnerommet for hånd. Legg til en _xlnm.Print_Area-oppføring gjennom DefinedNames.Add mens du også setter PrintArea-egenskapen, og arbeidsboken bærer to motstridende definisjoner for ett reservert navn, en tilstand Excel løser på måter intet produkt bør stole på. Behandle hver identifikator som starter med _xlnm. som tilhørende egenskapslaget. For å inspisere utskriftsoppsettet, les egenskapene, ikke navnetabellen. Artikkelen om beskyttelse og sideoppsett dekker utskriftsområde-egenskapene i kontekst

To grenser verdt å kjenne før du forplikter deg til et design

Definerte navn blir ikke med gjennom bekvemmelighetsbroen fra XLS til XLSX. SaveXLSWorkbookAsXLSX kopierer celleinnhold og grunnleggende formatering, og navnetabellen er ikke på den dokumenterte kopilisten dens, så en arbeidsbok som var avhengig av navnene sine, mister dem i overgangen. Gjenskap navnene gjennom DefinedNames.Add etter konvertering. Det trinnet er mindre av et pliktløp enn det høres ut som, fordi det gir deg et øyeblikk til å normalisere omfangene deres i stedet for å bære over hva enn XLS-filen tilfeldigvis hadde

Den andre grensen er avdrift mellom formelstrenger og arknavn. Excel skriver om arkreferanser inne i formler og navn under en interaktiv omdøping, så filer en bruker redigerer i Excel, forblir konsistente av seg selv. Eksponeringen er på generatorsiden: når Pascal-kode setter sammen formelstrenger fra en arknavn-literal, produserer det å gi arket nytt navn ett sted og glemme det andre, en referanse til et ark som ikke lenger finnes. Hold arknavnet i én enkelt Delphi-konstant og mat den til både Sheets.Add og formelsammensetningen din, og de to kan aldri komme i konflikt. Dette er det samme instinktet som taler for å navngi en rapports utdataceller i stedet for å hardkode adresser: en mal hvis totalcelle er navngitt, fortsetter å fungere etter at en designer setter inn tre rader over den, mens en generator som skriver til en literal B17, stille lander tallet sitt feil sted. Artikkelen om malbasert rapportgenerering bygger på nøyaktig det mønsteret

Den fullstendige API-en for definerte navn for begge formatene, sammen med formelmotorreferansen, følger med HotXLS Delphi Component