HotXLS Liste Doğrulaması: 255 Sınırı ve x14 Uzantıları

Excel, liste veri doğrulaması 255 karakteri aşan bir satır içi liste tutan bir XLSX çalışma kitabını açmayı reddeder ve bu, Excel'in kendisinin kaydettiği bir dosya için bile geçerlidir. HotXLS, Delphi ve C++Builder için yerel Excel bileşeni, o listeleri yükler ve sayfalar arası liste kurallarını x14 uzantıları olarak modeler; ama kalıcı çözüm her yerde aynıdır: uzun listeleri bir çalışma sayfası aralığında ya da bir tanımlı adde tutun

Hata raporu genelde sizin testlerinizden değil bir müşteriden gelir. Bir şablondaki Region ya da Product sütunu, açılır listesi sekiz girişten başlayıp altmışa büyümüştür. Birinin paylaşılan sürücüde tuttuğu XLSB kopyada her şey hâlâ çalışır. Sonra bir dışa aktarma işi aynı kuralı bir XLSX'e yazar ve Excel ya bir onarım istemiyle ya da külliyen bir reddetmeyle cevap verir. Kodunuzda değişen tek şey bir dizgenin uzunluğudur; sınır sizi bulmadan anlamanın tam da sebebi budur

255 karakterlik liste sınırı gerçekte nerede geçerli?

Sınır, XLSX'te liste doğrulamanın satır içi biçimine, yani formula1 içinde saklanan tırnaklı, virgülle ayrılmış dizgeye uygulanır ve Excel onu, listeyi Data Validation penceresinde yazarken değil, dosyayı açarken zorunlu kılar. Excel'e karşı doğrudan test temiz bir sonuç verdi: 253 karakterli bir liste açıldı; 260 ve 421 karakterli listeler reddedildi. Öğretici olan 421 karakterli durum, çünkü Excel'in kendi yazdığı bir çalışma kitabından geliyordu. Excel, okumayacağı bir dosyayı yazabiliyor

Şema sizi burada kurtarmıyor. ECMA-376 Part 1, §18.3.1.32, dataValidation öğesini ve formüllerini, 255 karakterlik bir tavan belirtmeksizin tanımlar; dolayısıyla bir dosya şema açısından geçerli olup yine de reddedilebilir. Bu bir uygulama kısıtıdır ve ona dair tek güvenilir referans, Excel'in kendi davranışıdır

HotXLS o tavanı sizin için zorunlu kılmıyor. TXLSXWorksheet.AddListValidation öğeleri tırnaklara sarar ve verildiği gibi saklar; uzun bir listeyi ne kırpır ne reddeder, dolayısıyla 400 karakterli bir liste istendiği gibi kaydedilen XLSX'e girer ve Excel sonucu reddeder. Bu bilinçli bir tercihtir, çünkü bir listeyi sessizce kısmak kimsenin fark etmediği bir veri bozulması olurdu; ama uzunluk kontrolünü sizin tarafınıza bırakır. Küçük bir yardımcı, kontrolü dürüst tutar:

const
  // Excel 253 karakterli bir listeyi açtı, 260 ve 421 karakterliyi reddetti.
  // Tam kenarı hedeflemek yerine bir pay bırakın.
  MaxInlineListChars = 250;

function InlineListLength(const AItems: array of WideString): Integer;
var
  I: Integer;
begin
  Result := 0;
  for I := 0 to High(AItems) do
    Inc(Result, Length(AItems[I]));
  if Length(AItems) > 1 then
    Inc(Result, High(AItems));  // ayıran virgüller
end;

Gömülü çift tırnakları iki karakter sayın, çünkü AddListValidation onları, formül dizgesini geçerli tutmak için ikisizler

XLSB neden XLSX'in taşıyamadığı listeleri taşıyabiliyor?

XLSB, satır içi listeyi uzunluğu 16 bitlik bir alan olan tek bir PtgStr dizge tokeni olarak saklar; ikili biçimde 255 karakterlik bir duvar yoktur. 60 öğeli ve 419 karakterli yerel bir örnek, tek tokende yaşar ve Excel'de şikayetsiz gidiş dönüş yapar. Aynı listenin XLSX'te tutunu, yukarıdaki 421 karakterlik durumdur

Penceredeki 255 karakterlik sınırı XLSB'ye uygulayan bir yükleyici o dosyayı reddederdi. HotXLS'nin XLSB doğrulama token kontrolü, bir dizge tokenini Excel hücre dizgesiyle aynı tavan olan 32.767 karakterde sınırlar. İki nüans açıkça söylenmeyi hak eder. Yükleyici ayrıca bütün bir doğrulama formülünü 16.384 baytta sınırlar; dolayısıyla tek bir satır içi listenin pratik tavanı, token kontrolünün tek başına ima ettiğinden düşüktür. Ve bir PtgStr içinde NUL karakterleriyle ayrılan listeler desteklenen profilin dışındadır; tahmin yürütülmek yerine desteklenmiyor olarak raporlanır

Tuzak, dönüştürmede oturur. XLSB'de kusursuz sağlıklı olan uzun bir liste, her neyse o çalışma kitabını XLSX olarak kaydettiği an, HotXLS dahil, reddedilen bir dosyaya dönüşür. Müşteri XLSB dosyalarını dönüştüren bir hattınız varsa uzunluk kontrolünü, müşteri rapor etmeden önce değil, XLSX kaydından önce her liste kuralında çalıştırın

Başka bir sayfaya işaret eden listelere ne oluyor?

Excel, kaynağı farklı bir çalışma sayfasında duran listeyi, sıradan dataValidations öğesine değil x14:dataValidations uzantısına saklar. Kural, çalışma sayfasının extLst içinde, [MS-XLSX] içinde tanımlanan {CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF} uzantı URI'si altında yaşar ve kaynak başvurusu, Lists!$A$2:$A$61 gibi bir xm:f öğesidir. XLSB aynı kuralı kendi uzantı kayıtlarında tutar

HotXLS o XLSX uzantı kurallarını tipli DataValidations koleksiyonuna çözümler ve her birini TXLSXDataValidation.IsExtension ile işaretler. Sayfalar arası bir liste bu yüzden sıradan kuralların yanında, aralığı, türü ve formülü okunur biçimde görünür ve düzenlemelerden, kaydetmelerden, yeniden açmalardan hayatta kalır. Kaydetmede yazıcı, uzantı kurallarını sıradan öğenin dışında tutar ve x14 uzantısını, çalışma sayfasının kuyruğundaki şema konumunda yeniden kurar. İki formül de korunur; böylece between ya da notBetween kullanan tam sayı, ondalık, metin uzunluğu, tarih ve saat kuralları iki sınırını da korur

Tipli uzantı profili kuralın tamamını kapsar. type, operator, allowBlank, iki show bayrağı, aralık ve iki formülün yanı sıra bir uzantı kuralı, giriş istemi başlığı ile metnini, hata uyarı başlığı ile metnini, uyarı stilini, IME modunu ve açılır listeyi gizle ayarını, yükleme ve yeniden kaydetme boyunca korur; sıradan bir doğrulamayla aynı kaçış kurallarıyla

uses
  SysUtils, lxHandleX, lxDiagnostics;

procedure AuditListRules(const FileName: WideString);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Dv: TXLSXDataValidation;
  S, I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then
      raise Exception.Create('Cannot open ' + FileName);
    for S := 1 to Book.Sheets.Count do        // Sheets[] 1 tabanlıdır
    begin
      Sheet := Book.Sheets[S];
      for I := 0 to Sheet.DataValidations.Count - 1 do
      begin
        Dv := Sheet.DataValidations[I];
        if Dv.ValidationType <> xlsxDvList then
          Continue;
        if Dv.IsExtension then
          Writeln(Sheet.Name, ' ', Dv.Range, ': x14 list from ', Dv.Formula1)
        else if (Dv.Formula1 <> '') and (Dv.Formula1[1] = '"') and
                (Length(Dv.Formula1) - 2 > MaxInlineListChars) then
          Writeln(Sheet.Name, ' ', Dv.Range, ': inline list of ',
            Length(Dv.Formula1) - 2, ' chars, refused by Excel as XLSX');
      end;
    end;
  finally
    Book.Free;
  end;
end;

Açılır listenin arkasındaki öğeleri okumak, girişleri seçmek ve yazılan girdiyi bu kurallara karşı kontrol etmek için bkz. HotXLS ile açılır liste öğelerini okuma ve girdiyi doğrulama; bu yazı saklama biçimlerinde kalıyor

Excel ve HotXLS bilinmeyen doğrulama türlerine nasıl davranıyor?

Excel, type özniteliğinde tanınmayan bir token taşıyan bir dataValidationı, türü none olan bir kural olarak açar, formülünü korur ve kaydetmede onu type özniteliği olmayan çıplak bir dataValidation öğesi olarak yeniden yazar; bilinmeyen özniteliği düşürerek. O tokenler elle düzenlenmiş XML'den, üçüncü taraf üreticilerden ve gelecekteki Office özelliklerinden gelir; Excel'in cevabı hoşgörüdür, reddetme değil

HotXLS aynı hoşgörüyü izler. Kuralı düşürmek yerine, ki o, yeniden kaydetmede iz bırakmadan kaybettirirdi, okuyucu onu formülleri ve aralığıyla xlsxDvNone olarak tutar ve normalleştirilen kural başına kod 1307 ile bir uyarı teşhisi kaydeder; lxDiagnostics içindeki xlsDiagnosticValidationTypeNormalized sabiti olarak açığa çıkar. Kaydetmede none kuralı, Excel'in ürettiği biçim olan çıplak öğe olarak yazılır. Bir none kuralı hiçbir şeyi doğrulamaz; teşhis, yazarın etkin sandığı bir kuralın artık girdiyi kısıtlamadığını haber verir

for I := 0 to Book.Diagnostics.Count - 1 do
  if Book.Diagnostics[I].Code = xlsDiagnosticValidationTypeNormalized then
    Writeln('Untyped rule kept on sheet ', Book.Diagnostics[I].SheetName,
      ': ', Book.Diagnostics[I].Message);

Excel, gidiş dönüşten sonra bir çalışma kitabını neden reddedebilir?

Excel, yeniden oynatılmış bir uzantı parçası, yeni dosyanın hiç beyan etmediği bir ad alanı öneki kullandığında yeniden kaydedilmiş çalışma kitabını reddeder; çünkü sonuç, iyi biçimli XML değildir. Uzantı parçaları rutin olarak xr:uid gibi önekli öznitelikler taşır ve onun xmlns:xr beyanı, parçanın içinde değil orijinal çalışma sayfası kökünde durur. Parçayı, o beyanı olmayan yeni bir çalışma sayfası köküne harfiyen kopyalayın; XML ad alanı kuralları, Excel tek bir hücreye bakmadan önce bozulur

HotXLS bunu yakalama adımında hallediyor. Okurken her yeniden oynatılmış parçayı, parçanın kendi beyan etmediği öğe ve öznitelik ön ekleri için tarar, her ön eki okuyucu üzerinden URI'sine çözümler ve o beyanları kaydetme sırasında çalışma sayfası köküne yazar. Akış halindeki kaydetme yolu kökü aynı yordamla kurar; böylece iki kaydetme modu da iyi biçimli çıktı üretir ve yeniden kaydedilmiş bir dosya, yeniden oynatılmış sayfalar arası x14 liste dahil her doğrulama kuralı yerindeyken Excel'de açılır. HotXLS'nin modellemediği extLst içeriğini taşıma resmi, temalar, extLst ve calcChain için kayıpsız XLSX gidiş dönüşleri yazısında; Excel'in onarım isteminin arkasındaki genel kurallar ise Excel neden onarım istemi gösterir yazısındadır

Uzun listeler bir aralıkta ya da bir tanımlı adda durmalı

Büyüyebilecek her liste için sağlam tasarım, öğeleri çalışma sayfası hücrelerinde saklamak ve doğrulamayı bir tanımlı ad üzerinden onlara yönlendirmektir. Aralık kaynaklı bir listenin iki biçimde de 255 karakterlik sınırı yoktur; öğeler kurala dokunmadan düzenlenebilir ve bir tanımlı ad, hücreler ayrı bir sayfada bile kuralı, dosyanın her tüketıcisinin okuyabildiği sıradan dataValidation öğesinde tutar

// Lists adanmış bir sayfadır; ör. Book.Sheets.Add('Lists')
function AddListRule(Book: TXLSXWorkbook; Target, Lists: TXLSXWorksheet;
  ACol: Integer; const ARange, AName: WideString;
  const AItems: array of WideString): Integer;
var
  I: Integer;
  ColRef: WideString;
begin
  if InlineListLength(AItems) <= MaxInlineListChars then
  begin
    Result := Target.AddListValidation(ARange, AItems);
    Exit;
  end;
  for I := 0 to High(AItems) do
    Lists.WriteAutoValue(I + 1, ACol, AItems[I]);   // 1 tabanlı satır, sütun
  ColRef := Chr(Ord('A') + ACol - 1);               // A..Z sütunları
  Book.DefinedNames.Add(AName, Lists.Name + '!$' + ColRef + '$1:$' +
    ColRef + '$' + IntToStr(Length(AItems)));
  // Baştaki '=' bir başvuru işaretler; onsuz ad, metin olarak tırnaklanır
  Result := Target.AddListValidation(ARange, '=' + AName);
end;

Baştaki eşittir işareti önemli. AddListValidation, = ile başlayan argümanı başvuru sayar ve işareti soyar; ! içeren argümanı tırnaklamadan aralık bırakır; geri kalan her şeyi satır içi liste olarak tırnaklar. 'StatusList' değerini işaretsiz geçirin ve gerçekten StatusList yazan tek öğelik bir açılır liste alırsınız. İki küçük not: WriteAutoValue metni Excel'in hücre girişi gibi dönüştürür, dolayısıyla TRUE gibi bir öğe Boolean olur; boşluk içeren bir sayfa adı, başvuruda tek tırnak ister

Ad olmadan doğrudan başka bir sayfaya işaret etmesi gereken bir kural için, AddListValidation çağrısının döndürdüğü indeksteki kural üzerinde IsExtension := True kurun ve HotXLS onu, Excel'in kendi kullandığı biçim olan x14 uzantısı olarak yazar. Tanımlı ad yolu daha basittir ve uzantı çözümlemeyen araçlar için de çalışmayı sürdürür; bu yüzden buradaki varsayılan tavsiye odur

Burada anlatılan bütün davranışlar; desteklenen doğrulama türlerini, dosya biçimlerini ve tam teşhis referansını ürün sayfasında listeleyen, Delphi, C++Builder ve Lazarus için HotXLS Delphi Excel bileşeninde gelir