Delphi Veri Kümelerini (Datasets) HotXLS ile Excel

Bir sorgu sonucunu Excel raporuna dönüştürmek, tek bir ceket giymiş üç ayrı sorundur. Her Delphi alan (field) türünün bir hücreye doğru Excel türü olarak yerleşmesi gerekir, başlık satırının bir şema dökümü yerine bir rapor gibi okunması gerekir ve sayıların, tarihlerin ve paranın yolculuktan sağ çıkacak biçimleri taşıması gerekir. Bunlardan herhangi birini atlayın; dosya yine de açılır, makul görünür ve finans kullanıcısının bir sütunu seçip asla görünmeyen bir toplamı beklediği anda başarısız olur. Değerler metin olarak yazılmıştır, Excel bunları etiket olarak değerlendirir ve sizi uyarmak için hiçbir zaman bir istisna fırlatılmamıştır

HotXLS, Excel otomasyonu içermeden XLS ve XLSX dosyalarını doğrudan Delphi ve C++Builder'dan yazan yerel bir Object Pascal elektronik tablo kütüphanesidir. Bir TDataset'ten çalışma kitabına iki yol sunar: Hazır TDataToXLS bileşeni ve çalışma kitabı API'sine karşı elle yazılmış bir döngü. Bunlar birbirinin yerine geçemez. Bileşen, XLS arayüzü üzerine kurulmuş bir VCL vatandaşıdır, bu nedenle doğru seçim kodun nerede çalıştığına ve tüketicinin hangi dosya biçimini beklediğine bağlıdır. Aşağıda her iki yol, bileşenin doğru araç olmaktan çıktığı sınır ve hangisini seçerseniz seçin alan türlerini nasıl bozmadan koruyacağınız yer almaktadır

Alan (field) türleri gerçek dışa aktarma sözleşmesidir

Herhangi bir API çağrısından önce, her Delphi alan türünün bir hücreye nasıl yerleşeceğine karar verin. Bir Delphi dizgesi (string) alan hücre dizge olarak kalır. HotXLS, '1.234,50' değerinin bir sayı olmasının amaçlandığını tahmin etmez ve etmemelidir de, çünkü yerel ayara bağlı yeniden ayrıştırma, tam olarak bir Alman ondalık virgülünün İngilizce bir sunucuda binlik ayırıcıya dönüşme şeklidir. Güvenilir kalıp, tiplendirilmiş erişimciler (accessors) aracılığıyla atama yapmaktır: Hücrenin biçimlendirilmiş bir dize yerine gerçek bir Excel tarih serisi tutması için tarih alanlarında AsFloat veya sayısal alanlar için AsCurrency, tarihler için AsDateTime ve yalnızca gerçekten metin olan alanlar için AsString kullanın

Null (boş değer) yönetimi varsayılan yerine açık bir kararı hak eder. Bir alan değerini VarToStr ile dönüştürmek, SQL NULL değerini bir metin hücresi olan boş bir dizeye dönüştürür; atamanın atlanması ise hücreyi gerçekten boş bırakır ki bu da AVERAGE, COUNT ve pivot tablo tüketicilerinin beklediği şeydir. Para sütunları için, döngü yazılmadan önce NULL değerinin sıfır mı yoksa bilinmeyen mi anlamına geldiğine karar verin. Birisi sütunu biçimlendirdiğinde ikisi de aynı şekilde işlenir ve aralarındaki fark aşağı akışta hesaplanan her toplamı değiştirir

Bileşen rotası: VCL uygulamalarında TDataToXLS

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // herhangi bir TDataset soyundan
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // ham sütun adları değil başlıklar
    Exporter.GroupFields.Add('CustomerID');   // müşteri başına alt toplam bloğu
    Exporter.RowsPerSheet := 50000;           // BIFF8 satır tavanının altında kalın
    Exporter.OnlyVisible := True;             // Field.Visible değerine saygı duy
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Burada üretim yükünün çoğunu iki özellik taşır. HeaderSource := hsDisplayLabel, ham SQL sütun adı yerine her alanın DisplayLabel değerini yazar, böylece çalışma kitabında CUST_NM yerine "Customer Name" yazar. RowsPerSheet mevcuttur çünkü bileşen, ızgarası 65.536 satır ve 256 sütunda duran BIFF8 yazar; bunu 50.000 olarak ayarlamak, biçim tavanı onu kesmeden önce büyük bir sonuç kümesini sayfalar arasında böler. Görünüm; HeaderFont, DetailFont, GroupColor ve kenarlık stili özellikleri tarafından yönetilir ve DisableFormat kümesi, tüketici düz hücreler istediğinde tüm biçimlendirme kategorilerini kapatır. Özel herhangi bir şey için, AfterCell ve AfterRow olayları, işlem sonrası için size az önce yazılmış aralığı teslim eder

Bileşenin durduğu sınır

TDataToXLS bileşeninde üç kısıtlama tasarlanmıştır ve bunları önceden bilmek iki sprint sonra garip bir yeniden tasarımdan kaçınmanızı sağlar:

Tam anlamıyla bir VCL bileşenidir. Ünitesi Forms, Controls ve Dialogs'u çeker, bu nedenle bir konsol işine veya bir Windows servisine bağlanması VCL'i ikili dosyaya (binary) sürükler. Çekirdek çalışma kitabı ünitelerinin böyle bir bağımlılığı yoktur. Yalnızca Windows, Classes, SysUtils ve Variants'a ihtiyaç duyarlar; bu nedenle sunucu tarafındaki kodlar bunun yerine aşağıda gösterilen döngüyü kullanmalıdır
XLS arayüzü üzerine kurulmuştur. Bileşen bir IXLSWorkbook doldurur ve .xls (BIFF8) yazar. Bunu OOXML çıktısına geçiren hiçbir özellik yoktur
Olayları XLS lehçesini konuşur. AfterCell içindeki Cell: IXLSRange parametresi XLS nesne modeline aittir, bu nedenle burada yazılan hücre başına özelleştirme, dosya sonradan .xlsx biçimine dönüştürülse bile XLS tarzı koddur

Bileşen çıktısından .xlsx üretme

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// bileşen doldurduğu IXLSWorkbook'u açığa çıkarır
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Köprüyü tam aslına sadık bir dönüştürücü değil, bir tablo verisi taşıyıcısı olarak ele alın. Değerleri, formülleri, sayı biçimlerini, dolgu renklerini, yazı tipi özniteliklerini, sütun genişliklerini ve görünüm ayarlarını kopyalar. Kenarlıkları, birleştirilmiş aralıkları, yorumları, grafikleri veya koşullu biçimlendirmeleri kasıtlı olarak kopyalamaz. Başlık artı satırlardan oluşan düz bir ızgara için bu tam olarak yeterlidir. Biçimlendirilmiş bir rapor için ise değildir ve dürüst düzeltme, dönüştürülen dosyayı yamamak yerine doğrudan XLSX üretmektir

Servisler ve toplu işler için elle yazılmış döngü

Sunucu tarafındaki kod doğrudan TXLSXWorkbook nesnesini hedeflemelidir. Herhangi bir örneği kopyalamadan önce iki arayüz arasındaki yaşam döngüsü farkına dikkat edin. XLS tarafındaki TXLSWorkbook referans sayımlı bir arayüz aracılığıyla tutulur ve manuel olarak serbest bırakılmamalıdır (free), oysa TXLSXWorkbook bir try..finally Free gerektiren düz bir sınıftır. İki kuralı karıştırmak, bir bellek sızıntısı veya çift serbest bırakma (double-free) hatası üretmenin garantili bir yoludur

procedure ExportOrders(Q: TDataSet; const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 'Order No';
    Sheet.Cells[1, 2].Value := 'Customer';
    Sheet.Cells[1, 3].Value := 'Ordered';
    Sheet.Cells[1, 4].Value := 'Amount';

    Row := 2;
    Q.First;
    while not Q.Eof do
    begin
      Sheet.Cells[Row, 1].Value := Q.FieldByName('OrderNo').AsInteger;
      Sheet.Cells[Row, 2].Value := Q.FieldByName('Customer').AsString;
      if not Q.FieldByName('Ordered').IsNull then
        Sheet.Cells[Row, 3].Value := Q.FieldByName('Ordered').AsDateTime;
      Sheet.Cells[Row, 4].Value := Q.FieldByName('Amount').AsFloat;
      Inc(Row);
      Q.Next;
    end;

    Book.StreamingWrite := True;  // sayfa XML'ini doğrudan zip içine aktarır
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

Önemli olan satırlar, tiplendirilmiş atamalar ve IsNull korumasıdır. Tarihler tarih serisi olarak gelir, miktarlar double olarak gelir ve NULL sipariş tarihleri boş dizelere dönüşmek yerine gerçekten boş kalır. StreamingWrite := True yalnızca kaydetme yolunu değiştirir: Çalışma sayfası XML'i önce tek bir büyük dize olarak birleştirilmek yerine doğrudan zip kapsayıcısına akar, bu da altı haneli satır sayıları için SaveAs anındaki bellek sıçramasını düzleştirir. Her kaydetme yönteminin bir TStream aşırı yüklemesi de vardır, böylece çalışma kitabı diske dokunmadan doğrudan bir HTTP yanıtına gidebilir. Akışlı yazma ve toplu iş makalesi bu dağıtım kalıbını inceler ve büyük çalışma kitabı performansı makalesi satır sayıları daha da tırmandığında ne yapılacağını kapsar

Bu döngü aynı zamanda iş parçacıkları (threads) arasında ölçeklenen yoldur. Her iki motor da yerel Object Pascal yazıcılarıdır; bir tarafta BIFF8 kayıt akışları ve diğer tarafta OOXML zip artı XML vardır, bu nedenle dışa aktarmanın hiçbir kısmı sunucuda COM otomasyonuna dokunmaz veya bir Excel lisansına ihtiyaç duymaz. Bu durum, her iş parçacığının kendi çalışma kitabını oluşturması koşuluyla, tek bir örnek darboğazı olmadan size paralellik kazandırır. Çalışma kitabı nesneleri paylaşımlı kullanım için thread-safe (iş parçacığı güvenli) değildir, bu nedenle kural dışa aktarma başına bir örnek oluşturulmasıdır, asla kilit (lock) ile korunan paylaşılan bir örnek değil

Etrafında tasarım yapmadan önce bilinmesi gereken bir sınır vardır. XLSX ızgarası 1.048.576 satır ve 16.384 sütunda durur, bu nedenle XLS tarafında RowsPerSheet'in gerçekleştirdiği sayfa bölme işlemine burada nadiren ihtiyaç duyulur. Milyon satırlık bir çalışma kitabı zaten bir insan tüketicinin isteyeceği bir şey değildir. Sonuç kümesi gerçekten bu kadar büyük olduğunda, genellikle sınırlı (delimited) bir dosya daha iyi bir sözleşmedir ve CSV ve TSV dışa aktarma makalesi sınırlayıcıları, BOM davranışını ve orada geçerli olan formül değerlendirme uyarısını kapsar

Bir başlangıç noktası seçme

Dışa aktarma bir VCL masaüstü aracında yer alıyorsa ve .xls çıktısı kabul edilebilirse, TDataToXLS ve onun gruplama desteğiyle başlayın. Bu en az koddur ve yukarıda açıklanan aslına uygunluk sınırlarını kabul ettiğiniz sürece, birisi daha sonra .xlsx istediğinde SaveXLSWorkbookAsXLSX köprüsü oradadır. Kod gözetimsiz çalışıyorsa veya tüketici en başından itibaren .xlsx talep ediyorsa döngüyü yazın. Her iki yol da çalışan demo projeleriyle birlikte gelir ve HotXLS Bileşeni paketinin bir parçasıdır