Membangun dan Menyegarkan Tabel Pivot Excel di Delphi dengan HotXLS

HotXLS membangun dan menyegarkan tabel pivot XLSX asli dari Delphi dan C++Builder tanpa perlu instalasi Excel dan tanpa otomasi COM pada mesin. Anda memanggil AddPivotTable dengan rentang sumber, menaruh field pada poros baris (row), kolom (column), halaman (page), dan data, menambahkan item terhitung atau tampilan persen-dari-total, dan komponen akan menulis bagian pivotCacheDefinition dan pivotTableDefinition yang dibuka Excel sebagai pivot aktif yang dapat disegarkan

Skenario yang membuat fitur ini sangat berguna adalah server pelaporan. Anda menghasilkan ratusan workbook dalam semalam, masing-masing membawa pivot yang merangkum satu akun, dan bulan berikutnya angka-angkanya berubah sehingga setiap file harus mencerminkan baris sumber yang baru. Menjalankan Excel dari Windows service sangat rapuh dan tidak berlisensi untuk penggunaan server, dan menulis XML pivot secara manual adalah proyek arkeologi spesifikasi yang tidak pernah berakhir. HotXLS berada di antara kedua jalan buntu tersebut: a model objek bertipe di atas bagian-bagian pivot OOXML, sehingga kode Pascal yang sama yang mengisi sel juga mendeklarasikan pivot dan membangun kembali cache-nya dalam proses yang sama

Bagaimana cara membuat tabel pivot di Delphi tanpa Excel?

Anda membuatnya dengan satu panggilan tunggal lalu menempatkan field pada poros. AddPivotTable menerima rentang sumber dalam notasi A1, sel tujuan tempat tabel ditambatkan, dan sebuah nama; ia mengurai rentang tersebut, memindai setiap kolom untuk menyimpulkan tipe datanya, membangun cache pivot (atau menggunakan kembali cache yang sudah terikat ke rentang yang sama), dan mengembalikan TXLSPivotTable yang semua field-nya dimulai di luar poros. Dari sana, metode kenyamanan seperti AddRowField, AddColumnField, AddPageField, dan AddDataFieldByName menghubungkan tata letak, dan setiap field data mengambil salah satu dari sebelas agregasi dari enum TXLSPivotAggregation (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;

Mengapa cache dan tabel adalah dua bagian yang terpisah

Tabel pivot sebenarnya adalah dua artefak yang saling mereferensikan, dan memahami pembagian ini adalah apa yang membuat sisa penjelasan ini menjadi jelas. pivotCacheDefinition adalah snapshot data: sebuah worksheetSource yang menunjuk ke rentang sumber, dan satu cacheField per kolom yang menampung nilai berbeda dari kolom tersebut (sharedItems-nya) ditambah batas turunan. pivotTableDefinition adalah tampilan: field cache mana yang berada pada poros mana, field data dan agregasinya, serta sakelar tata letak. Tampilan mengikat ke cache berdasarkan cacheId melalui hubungan pivotCaches workbook, persis seperti yang dijelaskan di ECMA-376 Bagian 1 §18.10 dan [MS-XLSX]

Indireksi tersebut bukanlah birokrasi, melainkan memberikan dua keuntungan. Satu cache dapat mendukung beberapa tabel, sehingga menyegarkan cache sekali akan memperbarui setiap tampilan yang ditarik darinya. Dan cache menyimpan nilai setiap kolom yang telah dideduplikasi dengan tabel indeks per rekaman alih-alih kisi mentah, itulah sebabnya membangun pivot berarti memindai sumber, bukan menyalin sel. HotXLS memodelkan ini dengan cara yang sama untuk kedua engine, sehingga kode di atas hampir identik dengan jalur klasik yang didokumentasikan bersama tata letak rekaman SX biner di balik pivot .xls klasik. Jika sumber Anda berada di lembar kerja lain atau Anda merujuknya melalui nama, awalan rentang serta nama yang ditentukan dan referensi lintas-lembar mengikuti aturan A1 biasa, dengan nama lembar yang dikutip diperbolehkan untuk nama yang mengandung spasi

Item terhitung bukan field terhitung

Ketiga istilah ini menamai tiga hal yang berbeda, dan mencampurnya adalah kesalahan pivot klasik. Item terhitung (calculated item) berada di dalam satu field dan menggabungkan item field itu sendiri berdasarkan nama, sehingga dalam field Region Anda dapat menentukan baris sintetis CoreMarkets yang sama dengan North ditambah South; HotXLS mengeksposnya sebagai TXLSPivotField.AddCalculatedItem dan menulis <calculatedItem> di bawah <calculatedItems> field tersebut. Field terhitung (calculated field) berbeda: ini adalah nilai baru yang diturunkan dari kolom lain, seperti Margin dari Revenue dan Cost, dan dibawa sebagai rumus pada field cache melalui TXLSPivotCacheField.Formula. Anggota terhitung (calculated member), yang ditambahkan dengan TXLSPivotTable.AddCalculatedMember, adalah anggota kustom tingkat tabel yang dapat bertindak sebagai ukuran (tipe anggota data) atau anggota dimensi, terutama berguna untuk pivot berbentuk OLAP

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;

Item terhitung bukan field terhitung

Bagaimana cara menampilkan nilai sebagai persen dari total?

Anda mengatur mode tampilan nilai pada field data alih-alih mengubah angka-angkanya sendiri. TXLSPivotDataField.ShowDataAs menerima enum TXLSPivotShowDataAs, yang mencerminkan nilai OOXML ST_ShowDataAs: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal, dan xlpsdaIndex. Trik umum adalah menempatkan kolom sumber yang sama pada poros data dua kali, sekali sebagai jumlah mentah dan sekali sebagai bagian dari total keseluruhan, sehingga laporan menunjukkan angka sekaligus bobotnya

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 tidak memerlukan basis karena ia relatif terhadap total keseluruhan, tetapi mode yang relatif terhadap item memerlukannya. xlpsdaDifference, xlpsdaPercentDiff, dan xlpsdaRunTotal memerlukan BaseField (indeks field cache tempat perbandingan berjalan) dan, untuk bentuk yang ditambatkan pada item, indeks BaseItem, di mana $7FFD melambangkan sentinel (All). HotXLS menulis ini sebagai atribut <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> dan, sama seperti rumus terhitung, menyerahkan aritmatikanya ke Excel

Pengelompokan tanggal dan angka, serta filter field halaman

Pengelompokan dikonfigurasi pada field cache, bukan field pivot, karena ia mengubah cara domain sumber dikelompokkan (bucketed). Atur HasGroup := True pada field cache yang dikembalikan oleh Cache.FindFieldByName, lalu pilih tombol rentang numerik (GroupStartNum, GroupEndNum, GroupInterval) atau bendera hierarki tanggal (GroupByDate dengan GroupMonths, GroupQuarters, GroupYears, dan rentang GroupStartDate / GroupEndDate). HotXLS memancarkan elemen <fieldGroup><rangePr groupBy="months"/> yang cocok sehingga field yang dikelompokkan berdasarkan bulan atau pita numerik selebar 1000 terbuka dengan cara Excel mengelompokkannya

Field halaman (page fields) adalah dropdown filter di atas tabel. AddPageField menempatkan field pada poros halaman, dan PageItemIndex memilih sebelumnya satu item cache, dengan bawaan xlPageItemAll ($7FFD, yang berarti (All)). Untuk memungkinkan pembaca mencentang beberapa item sekaligus, atur MultipleItemSelectionAllowed := True, yang ditulis HotXLS sebagai <pivotField multipleItemSelectionAllowed="1"/>. Untuk kriteria di luar pemilihan manual, setiap field membawa koleksi Filters bertipe yang mencakup keluarga ST_FilterType OOXML — filter jumlah, persen, total, keterangan (caption), nilai, dan tanggal — dengan masing-masing entri memasangkan jenis filter dengan nilai perbandingannya

Bagaimana cara menyegarkan cache pivot ketika sumber berubah?

RefreshPivotCache pada TXLSXWorkbook memindai ulang rentang sumber dan membangun kembali cache di tempatnya, yang merupakan apa yang dibutuhkan oleh pipa batch setelah ia mengedit baris yang mendasarinya. Teruskan id cache, dan metode akan membaca ulang setiap sel dalam rentang sumber (header pada baris pertama, data dari baris kedua), membangun kembali domain shared-item dari setiap field dengan mendeduplikasi ulang nilai dan menurunkan kembali batas per jenis, serta menulis ulang indeks item per rekaman. Ini mengembalikan 1 pada keberhasilan dan -1 ketika id cache tidak dikenal atau lembar kerja sumber hilang

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;

Batasan semantik di sini layak untuk dinyatakan secara jelas. Sebelum metode ini ada, HotXLS bergantung pada bendera refreshOnLoad="1" dan menyerahkan semua pekerjaan penyegaran ke Excel pada saat pembukaan berikutnya, yang tidak masalah ketika manusia yang akan membuka file tetapi tidak berguna dalam pipa headless yang harus menyerahkan data yang benar. RefreshPivotCache membuat cache yang disimpan menjadi mutakhir di dalam proses (in-process), dan karena tabel mengikat ke cache berdasarkan id, setiap pivot yang ditarik dari cache tersebut melihat pembaruan. Apa yang tidak dilukannya adalah menata atau menggabungkan kisi yang terlihat — Excel tetap menghitung ulang tabel yang dirender dari cache yang disegarkan ketika file dibuka

Apa yang ditulis HotXLS and apa yang dihitung Excel

Apa yang ditulis HotXLS dan apa yang dihitung Excel

Jaga agar pembagian kerja tetap terlihat dan tidak ada yang akan mengejutkan Anda. HotXLS adalah penulis definisi: ia menghasilkan pivotCacheDefinition, rekaman cache, dan pivotTableDefinition, lengkap dengan poros, agregasi, item dan anggota terhitung, tampilan nilai, pengelompokan, dan filter. Excel adalah kalkulatornya: saat dibuka, ia mewujudkan wadah kelompok (group buckets), mengevaluasi rumus terhitung, menerapkan transformasi showDataAs, dan menggabungkan isinya. Nilai yang dilihat pengguna adalah milik Excel, yang dihasilkan dari instruksi yang ditulis HotXLS, itulah sebabnya setiap rumus dan indeks dasar harus benar pada saat penulisan daripada diperiksa pada saat rendering

Dua batasan patut diketahui sebelum Anda merancang sistem di sekitar fitur ini. AddPivotTable mengurai rentang A1 persegi panjang dengan awalan lembar kerja opsional; sumber named-range dan eksternal-workbook berada di luar apa yang dapat diselesaikan oleh pembuat (builder), meskipun cache yang dibaca dari file yang disimpan Excel mempertahankan sumber named-range-nya saat round-trip. Dan pivot buatan Excel yang dibaca HotXLS dari disk diputar ulang secara byte-demi-byte saat disimpan, sehingga pengeditan bertipe yang dijelaskan di sini berlaku bersih untuk pivot yang Anda buat dalam kode sementara pivot yang sudah ada tetap tanpa kehilangan data. Untuk sisi input laporan — sel yang divalidasi dan tabel yang difilter yang dirangkum oleh pivot — lihat validasi data, AutoFilter, dan tabel terstruktur

Model pivot yang ditunjukkan di sini adalah bagian dari standar HotXLS Delphi Excel Component untuk Delphi dan C++Builder, yang membaca dan menulis baik bagian-bagian pivot bertipe XLSX maupun rekaman BIFF8 klasik dari model objek yang sama