Definerede navne og formler på tværs af ark i Delphi med HotXLS

Et defineret navn er en etiket, der står i stedet for en konstant, et celleområde eller et formeludtryk, gemt én gang i projektmappen og refereret symbolsk overalt, hvor det er nødvendigt. Skriv TaxRate i en formel, og motoren opløser det til, hvad navnets definition indeholder, hvad enten det er den bogstavelige værdi 0.08 eller området Data!$A$2:$D$100. En krydsark-reference er den ortogonale idé: Data!D2 når en celle på et andet ark ved at kvalificere adressen med et arknavn. Sæt de to sammen, og et sammendragsark kan summere et detaljeark gennem et navn, der aldrig nævner en bogstavelig adresse, hvilket er præcis, hvad man ønsker i en projektmappe, en generator samler, og en revisor senere gennemgår

HotXLS, losLabs native Delphi-bibliotek til XLS- og XLSX-filer, eksponerer navnetabellen for begge formater med opret-, find- og sletteadgang, plus en formelmotor, der opløser navne og krydsark-referencer in-process. De to formater holder separate klassehierarkier, og forskellene mellem deres navne-API'er er det, der får kode portet fra det ene til det andet til at snuble

To navnelagre, der ikke deler en grænseflade

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

Opslagskonventionerne divergerer på en måde, der dukker op under portering frem for ved kompilering. XLS-samlingens standard-Item-egenskab accepterer en Variant, så både Names[0] og Names['TaxRate'] opløses mod den. XLSX-samlingen har ingen sådan standardegenskab; du kalder FindByName('TaxRate'), som returnerer nil, når navnet ikke findes. Kode skrevet til den ene facade kompilerer kun mod den anden ved et tilfælde, og fejlen har en tendens til at vise sig som en runtime-nil-adgang frem for en rød bølgelinje i IDE'en

Scope er den første beslutning, ikke et flag du tilføjer senere

Et defineret navn er enten projektmappe-scoped, synligt for formler på hvert ark, eller ark-scoped, synligt kun for formler på dets ejende ark. I XLSX-API'et er forskellen en enkelt valgfri parameter. DefinedNames.Add(AName, AFormula) opretter et navn på projektmappeniveau, mens Add(AName, AFormula, ASheetIndex) binder det til ét ark. Ved tilbagelæsning returnerer TXLSXDefinedName.SheetIndex -1 for projektmappe-scope og ellers det 0-baserede arkindeks

Scope fungerer samtidig som din kollisionspolitik, og det er grunden til at afklare den, før du skriver det første navn. Excel tillader et ark-lokalt Total på hvert ark plus et Total på projektmappeniveau, og en formel på et givet ark opløser den lokale først. Genererede projektmapper bør læne sig bevidst op ad det. Forretningsantagelser, som flere ark forbruger, såsom skattesatser, valutakurser og rapporteringsperioden, hører hjemme i projektmappe-scope. Hjælpeområder, som kun ét arks formler refererer, er sikrere ark-scopede, hvor intet kan overskygge dem, og de kan ikke overskygge noget

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

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

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

Et defineret navn behøver ikke pege på et område. TaxRate ovenfor refererer til den bare konstant 0.08, og det er den reneste måde at udgive en forretningsantagelse på. Det optræder én gang i Excels Name Manager, hver formel refererer det symbolsk, og næste kvartals satsændring er en enkelt-linje-redigering af generatoren i stedet for en søgning på tværs af fjorten samlede formelstrenge

Lighedstegnet, der kun hører hjemme på den ene side

Formelindtastningskanalen er der, hvor porteret kode oftest går i stykker, fordi de to facader er uenige om lighedstegnet. XLS-celler modtager formler via Value med et indledende =. XLSX-celler har en dedikeret Formula-egenskab, der tager udtrykket uden præfikset. Skriv '=SUM(A1:A10)' ind i TXLSXCell.Formula, og lighedstegnet bliver en del af den gemte udtrykstekst frem for en markør, og filen vil ikke opføre sig, som den samme streng gjorde på XLS-siden

var
  Book: IXLSWorkbook;   // grænseflade-talt: kald ikke Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // antag at et ark ved navn 'Data' allerede indeholder detaljerækkerne
  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 gennem Value, med '='-præfikset
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

Det udsnit viser to yderligere XLS-side-særheder. Arksamlingen er 1-baseret, så Sheets[1] er det første ark, mod den 0-baserede XLSX Sheets[0]. Og den tredje Add-parameter opretter et skjult navn: til stede i filen og brugbart af formler, men usynligt i Excels Name Manager. Skjulte navne er det rigtige middel til generator-internt rørarbejde, som slutbrugere aldrig bør redigere eller slette ved et uheld

Krydsark-referencer, og hvad der sker, når rækker flyttes

Begge formelmotorer accepterer den almindelige krydsark-syntaks. Almindelige arknavne kvalificerer direkte som Data!A1; et navn med mellemrum eller tegnsætning kræver enkelte anførselstegn, som i 'Sheet With Space'!A1. Inde i et navns RefersTo-tekst skal man næsten altid gribe til absolutte referencer såsom Data!$A$2:$D$100. En relativ reference inde i et defineret navn opløses relativt til den celle, der bruger det, hvilket er en bevidst Excel-funktion og en pålidelig kilde til forvirring, når den udløses ved et uheld

Strukturelle redigeringer er der, hvor krydsark-bogføring gør fyldest, og XLSX-siden holder navnene konsistente gennem dem. InsertRows og DeleteRows flytter definerede navnes områder sammen med celler, sammenlægninger, hyperlinks og diagramankre, så et navn, der peger på Data!$A$2:$D$100, stadig dækker datablokken, efter generatoren åbner et hul over den. Formler kommer med ét dokumenteret forbehold: rækkeindsættelse justerer kun de referencer, der målretter det ark, der redigeres. En Summary-formel, der refererer Data!D2:D100, bliver omskrevet, når rækker går ind i Data, hvilket er det tilfælde, man normalt ønsker. Verificér det frem for at antage det, fordi motoren fortæller dig det billigt:

// beregningsmotoren opløser navne og krydsark-referencer in-process
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate evaluerer et vilkårligt udtryk mod projektmappens nuværende tilstand uden at gemme noget, hvilket gør den til det naturlige assertion-primitiv til generator-tests. Beregn det forventede aggregat fra kildedataene i Pascal, evaluér projektmappens egen formel, og sammenlign de to. Artiklen om formelmotoren dækker, hvad motoren evaluerer, hvornår, og hvordan man udvider den med brugerdefinerede funktioner

_xlnm-navnene, som egenskabslaget ejer

Åbn en genereret fils navnetabel i en lavniveau-inspektør, og du vil finde poster, du aldrig skrev: _xlnm.Print_Area, _xlnm.Print_Titles og deres slægtninge. Det er sådan, OOXML (ECMA-376 / ISO 29500) gemmer udskriftsområder og gentagne titelrækker, som definerede navne med reserverede identifikatorer. HotXLS håndterer dem gennem dedikerede regnearksegenskaber, så det at sætte PrintArea eller PrintTitleRows skriver den tilsvarende _xlnm.*-post for dig

Fælden er at gribe ind i det reserverede navnerum i hånden. Tilføj en _xlnm.Print_Area-post via DefinedNames.Add, mens du også sætter PrintArea-egenskaben, og projektmappen bærer to modstridende definitioner for ét reserveret navn, en tilstand Excel løser på måder, intet produkt bør stole på. Behandl hver identifikator, der starter med _xlnm., som hørende til egenskabslaget. For at inspicere udskriftsopsætningen skal du læse egenskaberne, ikke navnetabellen. Artiklen om beskyttelse og sideopsætning dækker udskriftsområde-egenskaberne i kontekst

To grænser, det er værd at kende, før du forpligter dig til et design

Definerede navne følger ikke med gennem bekvemmelighedsbroen fra XLS til XLSX. SaveXLSWorkbookAsXLSX kopierer celleindhold og grundlæggende formatering, og navnetabellen er ikke på dens dokumenterede kopieringsliste, så en projektmappe, der var afhængig af sine navne, mister dem i overgangen. Genskab navnene via DefinedNames.Add efter konvertering. Det trin er mindre besværligt, end det lyder, fordi det giver dig et øjeblik til at normalisere deres scopes i stedet for at bære videre, hvad XLS-filen nu havde

Den anden grænse er drift mellem formelstrenge og arknavne. Excel omskriver arkreferencer inde i formler og navne under en interaktiv omdøbning, så filer, en bruger redigerer i Excel, forbliver konsistente af sig selv. Eksponeringen er på generatorsiden: når Pascal-kode samler formelstrenge fra en arknavn-literal, producerer det at omdøbe arket ét sted og glemme det andet en reference til et ark, der ikke længere findes. Hold arknavnet i én enkelt Delphi-konstant, og fød den til både Sheets.Add og din formelsamling, så de to aldrig kan være uenige. Det er det samme instinkt, der taler for at navngive en rapports outputceller frem for at hardkode adresser: en skabelon, hvis totalcelle er navngivet, bliver ved med at virke, efter en designer indsætter tre rækker over den, mens en generator, der skriver til en bogstavelig B17, i det stille lander sit tal det forkerte sted. Artiklen om skabelonbaseret rapportgenerering bygger videre på præcis det mønster

Det fulde defined-names-API for begge formater, sammen med formelmotor-referencen, følger med HotXLS-Delphi-komponenten