Definierade namn och formler mellan blad i Delphi med HotXLS

Ett definierat namn är en etikett som företräder en konstant, ett cellområde eller ett formeluttryck, lagrad en gång i arbetsboken och refererad symboliskt överallt där den behövs. Skriv TaxRate i en formel och motorn löser upp namnet till det som namnets definition innehåller, vare sig det är litteralen 0.08 eller området Data!$A$2:$D$100. En referens mellan blad är den ortogonala idén: Data!D2 når en cell på ett annat blad genom att kvalificera adressen med ett bladnamn. Kombinera de två och ett sammanfattningsblad kan summera ett detaljblad via ett namn som aldrig nämner en bokstavlig adress, vilket är precis vad man vill ha i en arbetsbok som en generator sätter samman och en revisor senare granskar

HotXLS, losLabs nativa Delphi-bibliotek för XLS- och XLSX-filer, exponerar namntabellen i båda formaten med åtkomst för att skapa, söka och ta bort, plus en formelmotor som löser namn och referenser mellan blad i processen. De två formaten håller separata klasshierarkier, och skillnaderna mellan deras namn-API:er är det som snubblar kod som portas från det ena till det andra

Två namnlager som inte delar ett gränssnitt

På XLS-sidan returnerar TXLSWorkbook.GetNames en IXLSNames-samling vars överlagring Add(Name, RefersTo, Visible) skriver ett namn till BIFF-namntabellen. Enskilda poster kommer tillbaka som IXLSName-objekt som bär på Name, RefersTo, ett upplöst RefersToRange och en Delete-metod. På XLSX-sidan är TXLSXWorkbook.DefinedNames en TXLSXDefinedNames-samling med Add, FindByName och DeleteByName

Uppslagkonventionerna skiljer sig åt på ett sätt som visar sig under portning i stället för vid kompilering. XLS-samlingens standardegenskap Item tar emot en Variant, så både Names[0] och Names['TaxRate'] löses mot den. XLSX-samlingen har ingen sådan standardegenskap; du anropar FindByName('TaxRate'), som returnerar nil när namnet saknas. Kod skriven för den ena fasaden kompilerar mot den andra bara av en slump, och felet tenderar att visa sig som nollpekaråtkomst vid körning i stället för som en röd markering i IDE:n

Omfattningen är det första beslutet, inte en flagga du lägger till senare

Ett definierat namn är antingen arbetsboksomfattande, synligt för formler på alla blad, eller bladomfattande, synligt bara för formler på sitt eget blad. I XLSX-API:et är skillnaden en enda valfri parameter. DefinedNames.Add(AName, AFormula) skapar ett namn på arbetsboksnivå, medan Add(AName, AFormula, ASheetIndex) binder det till ett enda blad. Vid utläsning returnerar TXLSXDefinedName.SheetIndex -1 för arbetsboksomfattning och annars det nollbaserade bladindexet

Omfattningen fungerar samtidigt som din kollisionspolicy, och det är skälet att avgöra den innan du skriver det första namnet. Excel tillåter ett bladlokalt Total på varje blad plus ett Total på arbetsboksnivå, och en formel på ett givet blad löser det lokala först. Genererade arbetsböcker bör utnyttja det medvetet. Affärsantaganden som flera blad konsumerar, som momssatser, växelkurser och rapporteringsperioden, hör hemma på arbetsboksnivå. Hjälpområden som bara ett enda blads formler refererar är säkrare bladomfattande, där inget kan skymma dem och de kan inte skymma något

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');                // arbetsboksomfattning, en konstant
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // arbetsboksomfattning, ett område
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // endast bundet till bladindex 1

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

Ett definierat namn måste inte peka på ett område. TaxRate ovan refererar till den nakna konstanten 0.08, och det är det renaste sättet att publicera ett affärsantagande. Det dyker upp en enda gång i Excels Namnhanterare, varje formel refererar det symboliskt, och nästa kvartals ändring av satserna är en enradsändring i generatorn i stället för en sökning genom fjorton hopsatta formelsträngar

Lika-tecknet som bara hör hemma på ena sidan

Formelinmatningskanalen är där portad kod går sönder oftast, eftersom de två fasaderna är oense om lika-tecknet. XLS-celler tar emot formler via Value med ett inledande =. XLSX-celler har en dedikerad egenskap Formula som tar uttrycket utan prefixet. Skriv '=SUM(A1:A10)' i TXLSXCell.Formula och lika-tecknet blir en del av den lagrade uttryckstexten i stället för en markör, och filen kommer inte att bete sig som samma sträng gjorde på XLS-sidan

var
  Book: IXLSWorkbook;   // referensräknat gränssnitt: frigör det inte med Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // anta att ett blad vid namn 'Data' redan innehåller detaljraderna
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = dold i Namnhanteraren

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

Det kodavsnittet visar två egenheter till på XLS-sidan. Bladsamlingen är enbaserad, så Sheets[1] är det första bladet, mot den nollbaserade XLSX-Sheets[0]. Och den tredje Add-parametern skapar ett dolt namn: finns i filen och är användbart för formler, men osynligt i Excels Namnhanterare. Dolda namn är rätt fordon för generatorens interna rördrag som slutanvändare aldrig ska råka redigera eller ta bort

Referenser mellan blad, och vad som händer när rader flyttas

Båda formelmotorerna accepterar standardsyntaxen för referenser mellan blad. Vanliga bladnamn kvalificerar direkt som Data!A1; ett namn med mellanslag eller skiljetecken behöver enkla citattecken, som i 'Sheet With Space'!A1. Inuti ett namns RefersTo-text bör du nästan alltid använda absoluta referenser som Data!$A$2:$D$100. En relativ referens inuti ett definierat namn löses relativt till cellen som använder den, vilket är en medveten Excel-funktion och en pålitlig källa till förvirring när den träder i kraft av misstag

Strukturella redigeringar är där bokföringen mellan blad visar sitt värde, och XLSX-sidan håller namnen konsekventa genom dem. InsertRows och DeleteRows förskjuter definierade namns områden tillsammans med celler, sammanslagningar, hyperlänkar och diagramförankringar, så ett namn som pekar på Data!$A$2:$D$100 täcker fortfarande datablocket efter att generatorn öppnat ett gap ovanför det. Formlerna följer med ett dokumenterat förbehåll: radinfogning justerar bara de referenser som riktar sig mot bladet som redigeras. En formel på Summary som refererar Data!D2:D100 skrivs om när rader läggs in i Data, vilket är fallet du oftast vill ha. Verifiera det i stället för att anta det, eftersom motorn berättar det billigt:

// beräkningsmotorn löser namn och referenser mellan blad i processen
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate beräknar ett godtyckligt uttryck mot arbetsbokens aktuella tillstånd utan att spara något, vilket gör den till den naturliga primitiven för assertion i generatortester. Beräkna den förväntade aggregeringen från källdata i Pascal, utvärdera arbetsbokens egen formel och jämför de två. Artikeln om formelmotorn täcker vad motorn beräknar, när den gör det och hur man utökar den med egna funktioner

_xlnm-namnen som egenskapslaget äger

Öppna en genererad fils namntabell i en lågnivåinspektör och du hittar poster du aldrig skrev: _xlnm.Print_Area, _xlnm.Print_Titles och deras släktingar. Detta är hur OOXML (ECMA-376 / ISO 29500) lagrar utskriftsområden och upprepade titelrader, som definierade namn med reserverade identifierare. HotXLS hanterar dem via dedikerade bladegenskaper, så att sätta PrintArea eller PrintTitleRows skriver motsvarande _xlnm.*-post åt dig

Fällan är att röra i det reserverade namnrymden för hand. Lägg till en _xlnm.Print_Area-post via DefinedNames.Add samtidigt som du sätter egenskapen PrintArea, och arbetsboken bär på två motstridiga definitioner för ett reserverat namn, ett tillstånd som Excel löser på sätt som ingen produkt bör bero på. Behandla varje identifierare som börjar med _xlnm. som egenskapslagets egendom. För att inspektera utskriftsinställningarna, läs egenskaperna, inte namntabellen. Artikeln om skydd och sidinställningar täcker utskriftsområdesegenskaperna i sitt sammanhang

Två gränser värda att känna till innan du fastställer en design

Definierade namn följer inte med över bekvämlighetsbron från XLS till XLSX. SaveXLSWorkbookAsXLSX kopierar cellinnehåll och grundläggande formatering, och namntabellen finns inte på dess dokumenterade kopieringslista, så en arbetsbok som var beroende av sina namn förlorar dem i övergången. Återskapa namnen via DefinedNames.Add efter konverteringen. Det steget är mindre besvärligt än det låter, eftersom det ger dig ett tillfälle att normalisera deras omfattningar i stället för att bära med dig det som XLS-filen råkade innehålla

Den andra gränsen är drift mellan formelsträngar och bladnamn. Excel skriver om bladreferenser inuti formler och namn under en interaktiv omdöpning, så filer som en användare redigerar i Excel håller sig konsekventa på egen hand. Risken ligger på generatorsidan: när Pascal-kod sätter samman formelsträngar från en bladnamnsliteral, ger det att döpa om bladet på ett ställe och glömma det andra en referens till ett blad som inte längre finns. Håll bladnamnet i en enda Delphi-konstant och mata den till både Sheets.Add och din formelsammansättning, och de två kan aldrig vara oense. Det är samma instinkt som argumenterar för att namnge en rapports utdataceller i stället för att hårdkoda adresser: en mall vars summacell är namngiven fortsätter fungera efter att en designer infogat tre rader ovanför den, medan en generator som skriver till en bokstavlig B17 i tysthet landar sitt tal på fel ställe. Artikeln om mallbaserad rapportgenerering bygger på precis det mönstret

Det kompletta API:et för definierade namn för båda formaten, tillsammans med referensen för formelmotorn, levereras med HotXLS Delphi Component