Teknik Makale

HotXLS ile Delphi'de Excel 2.0 - 4.0 Dosyalarını Okuma

1992'de Excel 4.0'ın kaydettiği bir çalışma kitabı 09 04 06 00 baytlarıyla başlar; Excel 5.0 ve sonrasının her .xls dosyası ise bileşik dosya imzası D0 CF 11 E0 ile. O imzayı arayan bir okuyucu, tek bir hücre okumadan eski dosyayı reddeder; denetimi atlayıp BIFF8 varsayan bir okuyucu ilk kaydı kendi sandığı şey olarak ayrıştırmaya kalkar. İki başarısızlık da aynı varsayımdan gelir: her .xlsin bir kapsayıcısı olduğundan. Excel 2.0, 3.0 ve 4.0 dosyalarının kapsayıcısı yoktur; kayıt akışının kendisi hangi kuşağın yazdığını söylemeli ve hücre kayıtları o kuşağın düzeniyle çözülmeli

Bu dosyalar hâlâ karşınıza çıkar; tüm bunların önem taşımasının tek nedeni budur. Mühendislik arşivleri, kayıt saklama depoları, kontrol yazılımı 1990'ların başından kalma aletlerin laboratuvar çıktıları ve yıllardır koşan muhasebe sistemlerinin hepsi BIFF2-BIFF4 çalışma kitapları geride bıraktı. Güncel Excel sürümleri, Dosya Engelleme ayarları üzerinden bu biçimlerden birkaçını varsayılan olarak engeller; veri kümesi çoğu zaman elinizdeki araçların açamadığı bir klasörde oturur

OLE öncesi bir çalışma kitabı kendini nasıl duyurur?

HotXLS, OLE öncesi bir çalışma kitabını ilk kaydından tanır; her öteki .xlsi yükleyen aynı TXLSWorkbook.Open çağrısının içinde. Excel 5.0 ve sonrasının her .xls dosyası bir OLE2 bileşik dosyadır — dosya içinde küçük bir dosya sistemi — ve çalışma kitabı Workbook ya da Book adlı bir akışta yaşar; birini ayrıştırmak o kapsayıcıyı ayrıştırmakla başlar, Pascal'da bileşik dosya ikili biçimi başlığında anlatıldığı gibi

BIFF2'den BIFF4'e kadar bütün bunlar yoktur. Dosya doğrudan bir BOF kaydıyla başlar ve BOF'un kayıt numarası kuşağı kodlar: BIFF2 için $0009, BIFF3 için $0209, BIFF4 için $0409. Sonraki biçimlerin dayandığı gövdenin içindeki sürüm sözcüğü bu kuşaklar tarafından yok sayılır; güvenilir tek sinyal kayıt kimliğidir. Ham yola karar vermeden önce HotXLS akış katmanı iki şey daha denetler: BOF gövdesi dört ile altı bayt uzunluğunda olmalı ve alt akış türü sözcüğü bir çalışma sayfası için $0010, bir grafik için $0020 ya da bir makro sayfası için $0040 olmalıdır. Gerisi, Openın hata kodu döndürmesini sağlar

Bu denetimler göründüklerinden daha önemlidir. Ofset sıfırda $09 $00 baytları rastgele ikili veride nadir değildir ve tanımlanamayan .xlslerden oluşan bir karantina klasörü çoğu zaman yeniden adlandırılmış metin çıktıları, yarım kalmış kopyalar ve ara sıra bambaşka bir programın dosyasını barındırır. Üç bağımsız alanın hemfikir olması, tek alandan çok daha güçlü bir sinyaldir

HotXLS'in Delphi'den OLE öncesi Excel dosyalarını nasıl algıladığının şeması: bir yanda modern OLE2 kapsayıcı çalışma kitapları, öte yanda ham BIFF2-BIFF4 kayıt akışları; eski yola karar verilmeden önce denetlenen BOF kayıt alanları
Eski dosyalar saklama sarmalayıcısı taşımaz; öndeki BOF kaydı, tek bir hücre okunmadan önce kuşağı, gövde uzunluğunu ve alt akış türünü kanıtlar

Üç kuşak, üç kayıt düzeni

Hücre kayıtları, kuşakların en görünür biçimde ayrıştığı yerdir. BIFF2, düşük kayıt numaralarının bitişik bir bloğunu kaplar: boş, tam sayı, sayı, etiket ve boolean-ya da-hata hücreleri için $0001 ile $0005 arası. O gövdelerin her biri, sonraki sürümlerin iki baytlık genişletilmiş biçim indeksi koyduğu yere üç baytlık bir öznitelik alanı taşır; böylece her değer kayıtta bir bayt daha geride durur. BIFF2 INTEGER kaydının kendine özgü bir tuhaflığı vardır: kesirli kısmı olmayan işaretsiz 16 bitlik bir değer; BIFF3'ün RK lehine bıraktığı bir kayıt türü

BIFF3 ve BIFF4 düşük numaraları bırakır ve BIFF5 kayıt kimliklerini ve düzenlerini yeniden kullanır: $0201, $0203, $0204, $0205 ve $027E; her biri iki baytlık bir XF indeksiyle. O yeniden kullanım, özel ve kolay yanlış teşhis edilen bir başarısızlığa yol açar. Bir BIFF3 ya da BIFF4 LABEL kaydı, BIFF5 muadiliyle yapısal olarak özdeştir: satır, sütun, biçim indeksi, sonra 6. ofsette iki baytlık karakter sayısı. O düzeni bir BIFF2 etiketine uygularsanız sayı, son öznitelik baytından ve gerçek tek baytlık uzunluktan kurulur; beş karakterlik bir etiket en az 1.280 karakter ister ve okuyucu kaydın sonuna kadar koşar. Öbür yöne gidin, bir BIFF3 kaydı üzerinde BIFF2 düzeni, uzunluğu sayının yüksek baytından okur; kısa bir dizge için o bayt sıfırdır. Her iki yönde de hiçbir şey fırlamaz. Çalışma kitabı boş etiketlerle ya da inandırıcı çöple döner ve HotXLS her ikisinden de, kayıt kimliğine BOF'tan alınan kuşakla birlikte dallanarak kaçınır

BIFF2 öznitelik alanı bir tuzak daha saklar. İlk baytı, düşük altı bitte biçim indeksini tutar ve $3F değeri "gerçek indeks altı bit için fazla büyük, önceki IXFE kaydına bak" demektir. HotXLS en güncel IXFE kaydını izler ve hücreye stilini atamadan önce kaçışı çözümler; böylece biçim indeksi altı biti aşan bir hücre yine kendi biçimlendirmesini alır

Delphi'de HotXLS'in okuduğu BIFF2 ile BIFF3/BIFF4 hücre kayıt düzenlerinin karşılaştırması: kuşak başına kayıt numaraları, LABEL alan düzeni farkı, yanlış okuma başarısızlık modu ve paralel formül ile dizge kayıt numaralandırması
BIFF2 gövdeleri üç baytlık bir öznitelik bloğu taşır; BIFF3 ve BIFF4, iki baytlık XF indeksiyle BIFF5 numaralarını yeniden kullanır ve düzenleri karıştırmak, kayıttan sonraki her şeyi sessizce kaydırır

Üç kuşakta da metin, hiçbir yerde Unicode olmayan düz bir bayt dizgesidir. Kodlama dosyanın CODEPAGE kaydından gelir; bu dosyalarda o kayıt, onu tutacak çalışma kitabı düzeyi bir akış olmadığı için tek çalışma sayfası akışının içindedir. HotXLS etiketleri ve dizge sonuçlarını o kod sayfası üzerinden çözer ve kayıt yoksa Windows-1252'ye döner; bu dosyaların çoğunun yazıldığı batı varsayılanını kapsar. Bu yüzden CODEPAGE kaydı olmayan bir Japon ya da Orta Avrupa çalışma kitabı Latin metin olarak çözülür ve tek çare, dosyanın nereden geldiğini bilmektir

Birini Delphi'den açmak

Algılama Openın içinde olduğundan çağıran kod her çalışma kitabındakiyle aynıdır. Aşağıdaki döngü kullanılan aralığı gezer ve formül tutan her hücreyi iki kez basar: çevrilmiş ifade ve Excel'in hücrenin sayı biçimiyle göstereceği değer

uses
  SysUtils, Variants, lxHandle;

var
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
  Cell: IXLSRange;
  R, C: Integer;
begin
  Book := TXLSWorkbook.Create;
  try
    try
      if Book.Open('archive\1992-inventory.xls') <> 1 then
      begin
        Writeln('not a readable workbook - quarantine for manual review');
        Exit;
      end;
    except
      // Dosya gerçekte bir ZIP paketi olduğunda Open fırlatır;
      // tipik olarak birinin .xls olarak yeniden adlandırdığı bir .xlsx
      on E: Exception do
      begin
        Writeln('rejected: ', E.Message);
        Exit;
      end;
    end;

    Sheet := Book.Sheets[1];          // Sheets[] 1 tabanlıdır
    for R := Sheet.UsedRange.FirstRow + 1 to Sheet.UsedRange.LastRow + 1 do
      for C := Sheet.UsedRange.FirstCol + 1 to Sheet.UsedRange.LastCol + 1 do
      begin
        Cell := Sheet.Cells[R, C];
        if VarIsEmpty(Cell.Value) or VarIsNull(Cell.Value) then
          Continue;
        if Cell.HasFormula then
          Writeln(Format('R%dC%d  %s  -> %s',
            [R, C, VarToStr(Cell.Formula), Cell.FormattedText]))
        else
          Writeln(Format('R%dC%d  %s', [R, C, Cell.FormattedText]));
      end;
  finally
    Book.Free;
  end;
end;

İndeks aritmetiğine dikkat. UsedRange sınırları sıfır tabanlıdır; sayfa koleksiyonu ve hücre erişimi ise 1 tabanlıdır — API, mevcut kodla uyum için taşıdığı bir tutarsızlık. Düzeltmeyi unutun ve döngü, hiçbir olağandışı şey bildirmeden yanlış dikdörtgeni denetler. Bu yolun kabul ettiği dosyalar tek bir çalışma sayfası, grafik ya da makro sayfası alt akışı taşır; Sheets[1] bulacağınız tek sayfadır. Bağlı bir Excel 4.0 çalışma kitabı (BOF türü $0100) o kümenin dışında kalır ve Open hata kodu döndürür. Dosyayı hiç yüklemeyen ucuz ön denetimler hafif çalışma kitabı incelemesi başlığında

Parola korumalı dosyalar da aynı tek akış kuralını izler. FILEPASS kaydı, çalışma sayfası akışında BOF'un hemen ardından görünür; HotXLS onu orada yakalar ve bu kuşakların kullandığı eski XOR düzleştirmeyi çözmeyi (deobfuscation) açar. Parolayı Open(FileName, APassWord) aşırı yüküyle geçirin

BIFF2-BIFF4 dosyasındaki formüllere ne olur?

HotXLS eski bir formülün iki yarısını da okur: Excel'in son hesaplamada sakladığı önbellek sonucu ve belirteç akışı — yük sırasında güncel hesaplama modeline çevrilen. Formül kayıtları üç kuşakta paralel numaralandırma paylaşır: $0006, $0206 ve $0406; ama gövdeler, tam olarak bir BIFF8 ayrıştırıcısının takılacağı yerlerde farklılaşır

Önbellek sonuç, BIFF2'de öznitelik alanı yüzünden 7. baytta, BIFF3 ve BIFF4'te 6. baytta başlar. Sayısal olmayan sonuçlar, o sekiz baytlık gözeneğin son iki baytında $FFFF işaret sözcüğü kullanır; ilk baytta bir ayırıcıyla: dizge için 0, boolean için 1, hata kodu için 2, boş için 3. Bir dizge sonucu gözeneğe hiç sığmaz; metin bir sonraki kayıtta gelir — BIFF2'de tek baytlık uzunlukla $0007, BIFF3 ve BIFF4'te iki baytlık uzunlukla $0207

Delphi'den HotXLS'in okuduğu BIFF2-BIFF4 FORMULA kayıtlarında önbellek formül sonucunun anatomisi: değer BIFF2'de 7. baytta, BIFF3 ve BIFF4'te 6. baytta başlar; dizgeler, booleanlar, hatalar ve boşlar için FFFF işaret sözcüğü kullanılır
HotXLS, Excel'in son hesapladığı değeri çevrilmiş ifadenin yanında tutar; sayısal olmayan sonuçlar işaret sözcüğü artı bir ayırıcı olarak gelir

Belirteç akışı, naif bir uygulamanın saçmaladığı yerdir. BIFF2 ifadeye 16. ofsette tek baytlık bir uzunluk koyar ve belirteçlere 17'de başlar; BIFF3 ve BIFF4 iki baytlık uzunluk kullanır ve 18'de başlar. Akışın içinde, işlev belirteçleri BIFF5'ten önce tek baytlık bir işlev indeksi, BIFF5'ten itibaren iki baytlık bir indeks taşır. BIFF4 belirteçlerini değiştirmeden bir BIFF8 çevirmenine verin ve her işlev çağrısı bir bayt fazla yer; sonuç hafif yanlış bir formül değil ilgisiz bir formüldür. HotXLS çevirmeni BIFF sürümünü parametre olarak alır ve her belirteci o kuşağın genişlikleriyle okur

İki yarımın elinizde olması pratik bir soru doğurur: aktarılan çalışma kitabına hangi sayı girer? Önbellek değer, dosyanın kullanıcılarının gördüğü, bastığı ve 1992'de imzaladığı şeydir. Modern bir motor üzerindeki yeniden hesaplama formülün bugün ne anlama geldiğini hesaplar ve işlev davranışı otuz yıldır durmuyor. Bir arşiv aktarımı için kayıt, önbellek değerdir; onu koruyun ve yeniden hesaplamayı yalnızca hangi hücrelerin insan gözünü hak ettiğini söyleyen bir çapraz kontrol olarak koşun:

// Çapraz kontrol: saklanan sonuçları taze bir yeniden hesaplamayla karşılaştırın.
// Bu yardımcı ve CachedText dizisi sizin kendi kodunuzdur
procedure CompareWithRecalc(Book: TXLSWorkbook; Sheet: TXLSWorksheet);
var
  CachedText: array of array of string;
  Rows, Cols, R, C: Integer;
begin
  Rows := Sheet.UsedRange.LastRow + 1;
  Cols := Sheet.UsedRange.LastCol + 1;
  SetLength(CachedText, Rows + 1, Cols + 1);
  for R := 1 to Rows do
    for C := 1 to Cols do
      if Sheet.Cells[R, C].HasFormula then
        CachedText[R, C] := VarToStr(Sheet.Cells[R, C].Value);

  Book.Recalculate;

  for R := 1 to Rows do
    for C := 1 to Cols do
      if Sheet.Cells[R, C].HasFormula and
         (VarToStr(Sheet.Cells[R, C].Value) <> CachedText[R, C]) then
        Writeln(Format('R%dC%d  stored=%s  now=%s  formula=%s',
          [R, C, CachedText[R, C], VarToStr(Sheet.Cells[R, C].Value),
           VarToStr(Sheet.Cells[R, C].Formula)]));
end;

Bunu bir kopya üzerinde ya da kaydetmeden önce çalıştırın; Recalculate saklanan sonuçları bellekte değiştirir. Boş bir rapor istediğiniz sonuçtur; kısa bir rapor genellikle NOW() gibi volatile işlevlere ya da özgün yazarın başıboş bıraktığı referanslara işaret eder

Biçimlendirme, tarihler ve General biçimi tuzağı

HotXLS bu kuşakların biçimlendirme kayıtlarını, modern bir .xlsin ürettiği aynı çalışma kitabı modeline çevirir. FONT kayıtları (ayrı BIFF2 FONTCOLOR kaydı da katılarak), FORMAT dizgeleri ve her kuşağın XF kayıtları — dolgu desenleri, kenarlık stilleri ve eski renk indeksleriyle — Font, NumberFormat ve öteki aralık özellikleri üzerinden okuyup düzenleyebileceğiniz sıradan yazı tipleri, sayı biçimleri ve hücre stillerine dönüşür

Tarihler doğrudan faydalanır. Sayısal bir hücre bir tarih sayı biçimi taşıdığında okuyucu ayrıca çalışma kitabının DATEMODE bayrağını uygular; böylece 1904 sistemli bir çalışma kitabı, 1900 sistemliyle aynı TDateTime ölçeğine düşer ve FormattedText tarihi Excel'in gösterdiği gibi gösterir. 1462 günlük ofsetin ardındaki kurallar tarih seri numaraları, 1904 sistemi ve sayı biçimleri başlığında

Tuzak, tarihleri General biçimi altında tutan sütundur. 1990'lar çalışma kitaplarının epeyisi, seri numaralarını biçimlendirmekle uğraşmayan makrolarla ya da içe aktarımlarla dolduruldu ve dosya, 33604'ün bir parça numarası değil Ocak 1992'de bir gün olduğunu söyleyen hiçbir sinyal vermez. O hücreler saklandıkları gibi döner; tarih sistemine göre düzeltilmemiş. Kaynak hakkında bildiklerinizden yola çıkıp sütun sütun karar verin ve o sütunları kendiniz çevirin:

// ColumnHoldsDates sizin kendi aramanızdır; kaynak sistem bilgisinden kurulur;
// eski dosya bu soruyu sizin için cevaplayamaz.
// Serial yerel bir Double'dır; Cell ve C ilk listing'den gelir
if ColumnHoldsDates(C) and (Pos('d', LowerCase(Cell.NumberFormat)) = 0) then
begin
  // General biçimli seri: henüz tarih sistemi düzeltmesi uygulanmadı
  Serial := Cell.Value;
  if Book.Date1904 then
    Serial := Serial + 1462;
  Writeln(DateToStr(TDateTime(Serial)))
end
else
  Writeln(Cell.FormattedText);

Pos('d', ...) testi bilinçli olarak kabadır ve yalnızca biçiminde zaten gün belirteci taşıyan hücreleri atlamak içindir; arşiv büyükse kendi bilinen tarih biçimleri listenize karşı daha sıkı bir denetim, fazladan satırlara değer

Aktarılan kopyayı yazmak

BIFF2-BIFF4 dosyasından açılan bir çalışma kitabı, BIFF5/BIFF8 ya da XLSX yazıcılarıyla kaydedilir; güncel Excel'in engellediği bir biçimde yeni dosya üretmenin bir anlamı yoktur. Book.SaveAs('migrated\1992-inventory.xls', xlExcel97), değerleri, çevrilmiş formülleri ve dönüştürülmüş stilleri taşıyan bir BIFF8 kopyası yazar; hattaki her sonraki araç onu özel işlem olmadan açar. Özgünü yanında tutun: iki yıl sonra bir hücre soru sorarsa, 1992 baytları tek yetkedir

Eski okuma, modern yazma, yeniden hesaplama ve biçimlendirme, Delphi ve C++Builder için tek bir kitaplıkta yaşar; biçim kapsamı HotXLS Delphi elektronik tablo bileşeni sayfasında listelenir