Gedefinieerde namen en cross-sheetformules (HotXLS Delphi)

Een gedefinieerde naam is een label dat in de plaats komt van een constante, een celbereik of een formule-expressie, één keer in de werkmap opgeslagen en overal waar nodig symbolisch aangeroepen. Schrijf TaxRate in een formule en de motor lost hem op naar wat de definitie van de naam ook bevat, of dat nu de letterlijke 0.08 is of het bereik Data!$A$2:$D$100. Een cross-sheetverwijzing is het orthogonale idee: Data!D2 bereikt een cel op een ander blad door het adres met een bladnaam te kwalificeren. Zet die twee samen en een samenvattingsblad kan een detailblad optellen via een naam die nooit een letterlijk adres noemt, wat precies is wat u wilt in een werkmap die een generator samenstelt en een accountant later controleert

HotXLS, de native Delphi-bibliotheek van losLab voor XLS- en XLSX-bestanden, stelt de naamtabel van beide formaten beschikbaar met toegang om te maken, te vinden en te verwijderen, plus een formulemotor die namen en cross-sheetverwijzingen in het proces zelf oplost. De twee formaten houden aparte klassenhiërarchieën aan, en de verschillen tussen hun naam-API's zijn het deel waarover code struikelt die van de een naar de ander is overgezet

Twee naamopslagplaatsen die geen interface delen

Aan de XLS-kant retourneert TXLSWorkbook.GetNames een IXLSNames-collectie waarvan de overload Add(Name, RefersTo, Visible) een naam in de BIFF-naamtabel schrijft. Afzonderlijke vermeldingen komen terug als IXLSName-objecten met Name, RefersTo, een opgelost RefersToRange en een Delete-methode. Aan de XLSX-kant is TXLSXWorkbook.DefinedNames een TXLSXDefinedNames-collectie met Add, FindByName en DeleteByName

De opzoekconventies lopen uiteen op een manier die tijdens het porten opduikt en niet bij het compileren. De standaardeigenschap Item van de XLS-collectie accepteert een Variant, dus zowel Names[0] als Names['TaxRate'] lost daartegen op. De XLSX-collectie heeft zo'n standaardeigenschap niet; u roept FindByName('TaxRate') aan, die nil retourneert wanneer de naam ontbreekt. Code die voor de ene facade is geschreven compileert alleen bij toeval tegen de andere, en het falen laat zich meestal zien als een nil-toegang tijdens runtime in plaats van een rood kringeltje in de IDE

Bereik is de eerste beslissing, geen vlag die u later toevoegt

Een gedefinieerde naam heeft ofwel werkmapbereik, zichtbaar voor formules op elk blad, ofwel bladbereik, alleen zichtbaar voor formules op het blad dat hem bezit. In de XLSX-API is het onderscheid één optionele parameter. DefinedNames.Add(AName, AFormula) maakt een naam op werkmapniveau, terwijl Add(AName, AFormula, ASheetIndex) hem aan één blad bindt. Bij het teruglezen retourneert TXLSXDefinedName.SheetIndex de waarde -1 voor werkmapbereik en anders de vanaf 0 tellende bladindex

Bereik fungeert tegelijk als uw botsingsbeleid, en dat is de reden om het vast te leggen voordat u de eerste naam schrijft. Excel staat een bladlokale Total op elk blad toe plus een Total op werkmapniveau, en een formule op een bepaald blad lost eerst de lokale op. Gegenereerde werkmappen zouden daar bewust op moeten leunen. Zakelijke aannames die meerdere bladen verbruiken, zoals belastingtarieven, wisselkoersen en de rapportageperiode, horen op werkmapbereik. Hulpbereiken waarnaar alleen de formules van één blad verwijzen zijn veiliger op bladbereik, waar niets ze kan overschaduwen en zij niets kunnen overschaduwen

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... vul Data!A2:D100 met detailrijen ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // werkmapbereik, een constante
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // werkmapbereik, een bereik
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // alleen bladindex 1

    // XLSX-formules nemen geen voorafgaand '='
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

Een gedefinieerde naam hoeft niet naar een bereik te wijzen. TaxRate hierboven verwijst naar de kale constante 0.08, en dat is de schoonste manier om een zakelijke aanname te publiceren. Hij verschijnt één keer in Namenbeheer van Excel, elke formule verwijst er symbolisch naar, en de tariefwijziging van volgend kwartaal is een bewerking van één regel in de generator in plaats van een zoektocht door veertien samengestelde formuletekenreeksen

Het isgelijkteken dat maar aan één kant hoort

Het invoerkanaal voor formules is waar overgezette code het vaakst breekt, want de twee facades zijn het oneens over het isgelijkteken. XLS-cellen krijgen formules via Value met een voorafgaande =. XLSX-cellen hebben een aparte Formula-eigenschap die de expressie zonder het voorvoegsel neemt. Schrijf '=SUM(A1:A10)' in TXLSXCell.Formula en het isgelijkteken wordt onderdeel van de opgeslagen expressietekst in plaats van een markering, en het bestand zal zich niet gedragen zoals dezelfde tekenreeks aan de XLS-kant deed

var
  Book: IXLSWorkbook;   // interfacegeteld: niet vrijgeven
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // ga ervan uit dat een blad met de naam 'Data' de detailrijen al bevat
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = verborgen in Namenbeheer

  // XLS-formules gaan via Value, met het voorvoegsel '='
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

Dat fragment toont nog twee eigenaardigheden aan de XLS-kant. De bladencollectie telt vanaf 1, dus Sheets[1] is het eerste blad, tegenover de vanaf 0 tellende XLSX-variant Sheets[0]. En de derde Add-parameter maakt een verborgen naam: aanwezig in het bestand en bruikbaar door formules, maar onzichtbaar in Namenbeheer van Excel. Verborgen namen zijn het juiste vervoermiddel voor generatorintern leidingwerk dat eindgebruikers nooit per ongeluk zouden moeten bewerken of verwijderen

Cross-sheetverwijzingen, en wat er gebeurt als rijen verschuiven

Beide formulemotoren accepteren de standaard cross-sheetsyntaxis. Gewone bladnamen kwalificeren direct als Data!A1; een naam met spaties of leestekens heeft enkele aanhalingstekens nodig, zoals in 'Sheet With Space'!A1. Grijp binnen de RefersTo-tekst van een naam vrijwel altijd naar absolute verwijzingen zoals Data!$A$2:$D$100. Een relatieve verwijzing binnen een gedefinieerde naam lost op relatief ten opzichte van de cel die hem gebruikt, wat een bewuste Excel-functie is en een betrouwbare bron van verwarring wanneer zij per ongeluk afgaat

Structurele bewerkingen zijn waar cross-sheetboekhouding haar plek verdient, en de XLSX-kant houdt namen daar consistent doorheen. InsertRows en DeleteRows verschuiven de bereiken van gedefinieerde namen mee met cellen, samenvoegingen, hyperlinks en grafiekankers, dus een naam die naar Data!$A$2:$D$100 wijst dekt het datablok nog steeds nadat de generator er een gat boven heeft geopend. Formules komen met één gedocumenteerd voorbehoud: rij-invoeging past alleen de verwijzingen aan die het bewerkte blad als doel hebben. Een Summary-formule die naar Data!D2:D100 verwijst wordt herschreven wanneer er rijen in Data gaan, wat het geval is dat u meestal wilt. Verifieer het in plaats van het aan te nemen, want de motor vertelt het u goedkoop:

// de rekenmotor lost namen en cross-sheetverwijzingen in het proces zelf op
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate evalueert een willekeurige expressie tegen de huidige toestand van de werkmap zonder iets op te slaan, wat het de natuurlijke assertieprimitief maakt voor generatortests. Bereken het verwachte totaal uit de brongegevens in Pascal, evalueer de eigen formule van de werkmap, en vergelijk de twee. Het artikel over de formulemotor behandelt wat de motor evalueert, wanneer, en hoe u haar met aangepaste functies uitbreidt

De _xlnm-namen die de eigenschappenlaag bezit

Open de naamtabel van een gegenereerd bestand in een laagniveau-inspecteur en u vindt vermeldingen die u nooit hebt geschreven: _xlnm.Print_Area, _xlnm.Print_Titles en hun verwanten. Zo slaat OOXML (ECMA-376 / ISO 29500) afdrukbereiken en herhaalde titelrijen op, als gedefinieerde namen met gereserveerde identificatoren. HotXLS beheert ze via toegewijde werkbladeigenschappen, dus het instellen van PrintArea of PrintTitleRows schrijft de bijbehorende _xlnm.*-vermelding voor u

De valstrik is met de hand in die gereserveerde namespace grijpen. Voeg een _xlnm.Print_Area-vermelding toe via DefinedNames.Add terwijl u ook de eigenschap PrintArea instelt en de werkmap draagt twee tegenstrijdige definities voor één gereserveerde naam, een toestand die Excel oplost op manieren waarvan geen enkel product afhankelijk zou moeten zijn. Behandel elke identificator die met _xlnm. begint als eigendom van de eigenschappenlaag. Lees om de afdrukinstellingen te inspecteren de eigenschappen, niet de naamtabel. Het artikel over beveiliging en pagina-instelling behandelt de afdrukbereikeigenschappen in context

Twee grenzen die het kennen waard zijn voordat u een ontwerp vastlegt

Gedefinieerde namen reizen niet mee door de gemakkelijke brug van XLS naar XLSX. SaveXLSWorkbookAsXLSX kopieert celinhoud en basisopmaak, en de naamtabel staat niet op zijn gedocumenteerde kopieerlijst, dus een werkmap die van haar namen afhing verliest ze bij de oversteek. Maak de namen na de conversie opnieuw via DefinedNames.Add. Die stap is minder karwei dan het klinkt, want zij geeft u een moment om hun bereiken te normaliseren in plaats van over te nemen wat het XLS-bestand toevallig had

De andere grens is drift tussen formuletekenreeksen en bladnamen. Excel herschrijft bladverwijzingen binnen formules en namen tijdens een interactieve hernoeming, dus bestanden die een gebruiker in Excel bewerkt blijven vanzelf consistent. De blootstelling zit aan de generatorkant: wanneer Pascal-code formuletekenreeksen uit een letterlijke bladnaam samenstelt, levert het blad op de ene plek hernoemen en de andere vergeten een verwijzing op naar een blad dat niet meer bestaat. Houd de bladnaam in één Delphi-constante en voer die aan zowel Sheets.Add als uw formulesamenstelling, en de twee kunnen het nooit oneens zijn. Dit is hetzelfde instinct dat pleit voor het benoemen van de uitvoercellen van een rapport in plaats van adressen hard te coderen: een sjabloon waarvan de totaalcel een naam heeft blijft werken nadat een ontwerper er drie rijen boven invoegt, terwijl een generator die naar een letterlijke B17 schrijft zijn getal stilzwijgend op de verkeerde plek laat landen. Het artikel over sjabloongebaseerde rapportgeneratie bouwt precies op dat patroon voort

De volledige API voor gedefinieerde namen voor beide formaten, samen met de referentie van de formulemotor, wordt geleverd bij het HotXLS Delphi Component