„Excel“ suvestinių lentelių kūrimas ir atnaujinimas Delphi aplinkoje naudojant HotXLS

HotXLS kuria bei atnaujina vietines XLSX suvestines lenteles iš Delphi bei C++Builder aplinkų nenaudodama nei „Excel“ programos, nei COM automatizacijos pačiame kompiuteryje. Jūs iškviečiate AddPivotTable su šaltinio diapazonu, pridedate laukus į eilučių, stulpelių, puslapių bei duomenų ašis, pritaikote skaičiuojamus elementus arba bendrojo dydžio procentinę išraišką, o komponentas įrašo pivotCacheDefinition bei pivotTableDefinition dalis, kurias „Excel“ atidaro kaip gyvą, atnaujinamą suvestinę lentelę

Scenarijus, dėl kurio verta tuo pasinaudoti, yra ataskaitų teikimo serveris. Kiekvieną naktį sugeneruojate šimtus darbo knygų, kurių kiekviena turi suvestinę lentelę, apibendrinančią vieną sąskaitą; kitą mėnesį skaičiai pasikeičia ir kiekvienas failas turi atspindėti naujas šaltinio eilutes. „Excel“ valdymas iš „Windows“ paslaugos yra nepatikimas bei nelicencijuotas naudoti serveryje, o rankinis suvestinės lentelės XML rašymas yra specifikacijų archeologijos projektas, kuris niekada nesibaigia. HotXLS randasi tarp šių dviejų aklaviečių: ji turi tipizuotą objektų modelį virš OOXML suvestinių lentelių dalių, todėl tas pats Pascal kodas, kuris užpildo langelius, taip pat deklaruoja suvestinę lentelę bei iš naujo sukuria jos talpyklą tame pačiame procese

Kaip sukurti suvestinę lentelę Delphi aplinkoje nenaudojant „Excel“?

Sukuriate ją vienu iškvietimu, o tada išdėstote laukus ašyse. AddPivotTable priima šaltinio diapazoną A1 formatu, tikslinį langelį, prie kurio lentelė prikabinama, bei pavadinimą; ji išanalizuoja diapazoną, nuskaito kiekvieną stulpelį, kad nustatytų jo duomenų tipą, sukuria suvestinės lentelės talpyklą (arba pakartotinai naudoja jau su tuo pačiu diapazonu susietą talpyklą) ir grąžina TXLSPivotTable, kurios visi laukai pradžioje yra už ašių ribų. Iš ten patogumo metodai AddRowField, AddColumnField, AddPageField bei AddDataFieldByName sujungia maketą, o kiekvienas duomenų laukas priima vieną iš vienuolikos agregavimo tipų iš TXLSPivotAggregation sąrašo (xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP)

uses
  lxHandleX, lxPivot;

var
  Book : TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[1];                 // report sheet (1-based in the XLSX engine)

    // Source A1:E500 on the 'Data' sheet; anchor the pivot at row 3, col 1.
    Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
    if Pivot <> nil then
    begin
      Pivot.AddRowField('Region');
      Pivot.AddColumnField('Quarter');
      Pivot.AddPageField('Year');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
      Pivot.AddDataFieldByName('Units', xlpaAverage);
      Book.SaveAs('sales-pivot.xlsx');
    end;
  finally
    Book.Free;
  end;
end;

Kodėl talpykla ir lentelė yra dvi atskiros dalys

Suvestinė lentelė iš tikrųjų susideda iš dviejų artefaktų, kurie nurodo vienas kitą, ir šio padalijimo supratimas yra labai svarbus. pivotCacheDefinition yra duomenų momentinė nuotrauka: worksheetSource, nukreiptas į šaltinio diapazoną, ir vienas cacheField kiekvienam stulpeliui, kuriame saugomos to stulpelio unikalios reikšmės (jo sharedItems) bei išvestos ribos. pivotTableDefinition yra vaizdas: kuris talpyklos laukas randasi kurioje ašyje, duomenų laukai ir jų agregavimas, maketo jungikliai. Vaizdas susiejamas su talpykla pagal cacheId per darbo knygos pivotCaches ryšį, būtent taip, kaip numatyta ECMA-376 1 dalies §18.10 bei [MS-XLSX] specifikacijose

Ši netiesioginė nuoroda nėra biurokratija, ji suteikia du pranašumus. Viena talpykla gali palaikyti kelias lenteles, todėl vieną kartą atnaujinus talpyklą atnaujinamas kiekvienas iš jos nupieštas vaizdas. Be to, talpykla saugo kiekvieno stulpelio reikšmes pašalindama dublikatus su kiekvieno įrašo indeksų lentele, o ne neapdorotu tinkleliu; būtent todėl suvestinės lentelės kūrimas reiškia šaltinio nuskaitymą, o ne langelių kopijavimą. HotXLS tai modeliuoja vienodai abiejuose varikliuose, todėl aukščiau pateiktas kodas yra beišmetantys beveik identiškas klasikiniam keliui, aprašytam straipsnyje apie dvejetainį SX įrašų maketą, esantį už klasikinių .xls suvestinių lentelių. Jei jūsų šaltinis yra kitame lape arba kreipiatės į jį per pavadinimą, diapazono priešdėlis bei apibrėžti pavadinimai bei kryžminės nuorodos tarp lapų seka įprastas A1 taisykles, leidžiant naudoti kabutėse esančius lapų pavadinimus, jei juose yra tarpų

Skaičiuojami elementai nėra skaičiuojami laukai

Šie trys terminai įvardija tris skirtingus dalykus, ir jų sumaišymas yra klasikinė suvestinės lentelės klaida. Skaičiuojamas elementas randasi viename lauke bei sujungia to lauko elementus pagal pavadinimą, todėl Regiono lauke galite apibrėžti sintetinę CoreMarkets eilutę, lygią North plius South; HotXLS tai pateikia kaip TXLSPivotField.AddCalculatedItem bei įrašo <calculatedItem> po to lauko <calculatedItems> grupe. Skaičiuojamas laukas skiriasi: tai yra nauja reikšmė, gauta iš kitų stulpelių, pavyzdžiui, Margin iš Revenue bei Cost, ir ji pateikiama kaip formulė talpyklos lauke per TXLSPivotCacheField.Formula. Skaičiuojamas narys, pridedamas per TXLSPivotTable.AddCalculatedMember, yra lentelės lygio pasirinktinis narys, kuris gali veikti kaip matas (nario tipas data) arba dimensijos narys; tai daugiausia naudinga OLAP struktūros suvestinėms lentelėms

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // A calculated ITEM combines items of a single field by name.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // A calculated MEMBER is declared at the table level. Member type
  // 'data' marks it as a measure; an empty type is a dimension member.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

Viena reali riba galioja visiems trims. HotXLS išveda formulės tekstą į apibrėžimo XML; ji jo nevertina. „Excel“ apskaičiuoja skaičiuojamą elementą, lauką ar narį, kai atidaro failą, lygiai taip pat, kaip ji apskaičiuoja kiekvieną agregatą tinklelyje. HotXLS rašo instrukcijas, o ne rezultatus, todėl jūsų pateikiamos formulės turi būti teisingos suvestinių lentelių formulės pačios „Excel“ tarmėje, nurodančios laukų bei elementų pavadinimus taip, kaip tai darytų „Excel“

Kaip rodyti reikšmes kaip procentą nuo bendros sumos?

Nustatote vertės rodymo režimą duomenų lauke, užuot patys transformavę skaičius. TXLSPivotDataField.ShowDataAs priima TXLSPivotShowDataAs sąrašą, kuris atspindi OOXML ST_ShowDataAs reikšmes: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal bei xlpsdaIndex. Dažnas triukas yra patalpinti tą patį šaltinio stulpelį duomenų ašyje du kartus: vieną kartą kaip įprastą sumą, o kitą kartą kaip dalį nuo bendros sumos, kad ataskaita rodytų tiek skaičių, tiek jo svorį

var
  Rev, Share: TXLSPivotDataField;
begin
  Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);

  Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Share.DisplayName := 'Share of total';
  Share.ShowDataAs  := xlpsdaPercentOfTotal;

  // Item-relative modes need a base to compare against. A running total
  // down 'Quarter' (cache-field index 3) would read:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // cache-field index the transform runs over
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

xlpsdaPercentOfTotal nereikia jokios bazės, nes ji yra susijusi su bendra suma, tačiau santykiniams režimams jos reikia. xlpsdaDifference, xlpsdaPercentDiff bei xlpsdaRunTotal reikalauja BaseField (talpyklos lauko indekso, per kurį vykdomas palyginimas) bei, su elementais susietoms formoms, BaseItem indekso, kur $7FFD reiškia (All) žymeklį. HotXLS rašo juos kaip <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> atributus bei, kaip ir su skaičiuojamomis formulėmis, palieka aritmetiką „Excel“ programai

Datų bei skaičių grupavimas ir puslapio laukų filtrai

Grupavimas konfigūruojamas talpyklos lauke, o ne suvestinės lentelės lauke, nes jis keičia, kaip suskirstoma šaltinio sritis. Nustatykite HasGroup := True talpyklos lauke, kurį grąžina Cache.FindFieldByName, tada pasirinkite arba skaitinio diapazono parinktis (GroupStartNum, GroupEndNum, GroupInterval), arba datos hierarchijos vėliavėles (GroupByDate su GroupMonths, GroupQuarters, GroupYears bei GroupStartDate / GroupEndDate intervalu). HotXLS generuoja atitinkamą <fieldGroup><rangePr groupBy="months"/> elementą, todėl laukas, sugrupuotas pagal mėnesį arba pagal 1000 dydžio skaitinę juostą, atsidaro taip, kaip jį būtų sugrupavusi „Excel“

Puslapio laukai yra filtrų išskleidžiamieji sąrašai virš lentelės. AddPageField patalpina lauką puslapio ašyje, o PageItemIndex iš anksto pasirenka vieną talpyklos elementą, pagal nutylėjimą xlPageItemAll ($7FFD, reiškiantį (All)). Norėdami leisti skaitytojui pažymėti kelis elementus iš karto, nustatykite MultipleItemSelectionAllowed := True, ką HotXLS įrašo kaip <pivotField multipleItemSelectionAllowed="1"/>. Kriterijams, viršijantiems rankinį pasirinkimą, kiekvienas laukas turi tipizuotą Filters kolekciją, apimančią OOXML ST_FilterType šeimas — skaičiaus, procento, sumos, antraštės, reikšmės bei datos filtrus; kiekvienas įrašas susieja filtro tipą su jo palyginimo reikšmėmis

Kaip atnaujinti suvestinės lentelės talpyklą pasikeitus šaltiniui?

RefreshPivotCache metodas TXLSXWorkbook klasei iš naujo nuskaito šaltinio diapazoną bei sukuria talpyklą vietoje; tai yra būtent tai, ko reikia automatizuotam procesui pakeitus pagrindines eilutes. Perduokite talpyklos ID, ir metodas iš naujo perskaitys kiekvieną šaltinio diapazono langelį (antraštę pirmoje eilutėje, duomenis nuo antrosios), iš naujo sugeneruos kiekvieno lauko bendrų elementų sritį, pašalindamas dublikatus bei iš naujo nustatydamas tipų ribas, ir perrašys įrašų indeksus. Jis grąžina 1 sėkmės atveju bei -1, kai talpyklos ID yra nežinomas arba trūksta šaltinio lapo

var
  Rc: Integer;
begin
  // ...the source data has changed since the pivot was built...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // a corrected Revenue figure

  // Rebuild the cache's shared items and record indices from the source.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = refreshed, -1 = cache/source missing
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

Čia esančią semantinę ribą verta įvardyti aiškiai. Prieš atsirandant šiam metodui, HotXLS pasikliovė refreshOnLoad="1" vėliavėle bei palikdavo visą atnaujinimo darbą „Excel“ programai kito atidarymo metu; tai puikiai tinka, kai failą atidarys žmogus, tačiau nenaudinga beekraniame procese, kuris turi perduoti teisingus duomenis. RefreshPivotCache padaro išsaugotą talpyklą aktualią pačiame procese, o kadangi lentelės yra susietos su talpykla pagal ID, kiekviena iš tos talpyklos nupiešta suvestinė lentelė mato atnaujinimą. Ko ji nedaro, tai neišdėsto bei neagreguoja matomo tinklelio — „Excel“ vis tiek iš naujo apskaičiuoja atvaizduojamą lentelę iš atnaujintos talpyklos, kai failas atidaromas

Ką HotXLS rašo ir ką Excel skaičiuoja

Turėkite omenyje šį darbo pasidalijimą ir niekas jūsų nenustebins. HotXLS yra apibrėžimų rašytojas: ji sukuria pivotCacheDefinition, talpyklos įrašus bei pivotTableDefinition su ašimis, agregacijomis, skaičiuojamais elementais bei nariais, reikšmių rodiniais, grupavimu bei filtrais. „Excel“ yra skaičiuotuvas: atidarant dokumentą ji materializuoja grupių krepšelius, įvertina skaičiuojamas formules, pritaiko showDataAs transformacijas bei agreguoja turinį. Reikšmės, kurias mato vartotojas, yra sukurtos „Excel“ pagal HotXLS įrašytas instrukcijas, todėl kiekviena formulė bei bazinis indeksas turi būti teisingi įrašymo metu, o ne tikrinami atvaizdavimo metu

Prieš projektuojant aplink šią funkciją, verta žinoti dvi ribas. AddPivotTable analizuoja stačiakampį A1 diapazoną su neprivalomu lapo priešdėliu; apibrėžto diapazono bei išorinių darbo knygų šaltiniai nepatenka į tai, ką sprendžia kūrėjas, nors talpyklos, nuskaitytos iš „Excel“ sukurto failo, išlaiko savo apibrėžto diapazono šaltinį perkėlimo metu. Taip pat „Excel“ sukurtos suvestinės lentelės, kurias HotXLS nuskaito iš disko, saugant atkuriamos baitas po baito, todėl čia aprašyti tipizuoti redagavimai švariai taikomi toms suvestinėms lentelėms, kurias kuriate kode, o esamos išlieka neprarandant duomenų. Norėdami sužinoti apie ataskaitos įvesties dalį — patvirtintus langelius bei filtruotas lenteles, kurias suvestinė lentelė apibendrina —, skaitykite straipsnį duomenų patvirtinimas, AutoFilter bei struktūrizuotos lentelės

Čia parodytas suvestinės lentelės modelis platinamas kaip standartinio HotXLS Delphi Excel komponento, skirto Delphi ir C++Builder, dalis, kuris iš to paties objektų modelio skaito bei rašo tiek XLSX tipizuotas suvestinių lentelių dalis, tiek klasikinės BIFF8 įrašus