Delphi ile üretilmiş bir çalışma kitabı =GAMMA(5) ve =XIRR(B2:B9,A2:A9) taşır ve Excel onu GAMMA hücresinde #NAME?, XIRR hücresinde ise doğru bir sonuçla açar. HotXLS'te XLSX içinde _xlfn. ön ekini taşıyan ya da BIFF8 .xls'te name tabanlı bir çağrıyla saklanan fonksiyonlar, Excel'in de o şekilde kaydettiğidir — MS-XLSX dilbilgisinden sapan bir liste
Excel neden GAMMA için #NAME? gösterir de XIRR için göstermez?
Formula metni doğrudur; saklama yanlıştır. Bir XLSX çalışma sayfasının içinde bir formül, bir <f> ögesinde düz metindir ve Excel 2007'den sonra eklenen bir fonksiyon _xlfn.NAME olarak yazılır. Ön ek okuyucuya "bu, henüz bilmeyebileceğin bir gömülü fonksiyon" der; böylece eski bir Excel çağrıyı kullanıcı tanımlı bir adla karıştırmadan #NAME? gösterir ve güncel Excel görüntüde ön eki sıyırır, kullanıcılar =GAMMA(5) görür. Ön eki bırakırsanız güncel Excel, GAMMA adında bir tanımlı ad arar, bulamaz ve #NAME? gösterir. XIRR daha eskidir. Analysis ToolPak ile geldi, Excel onu çıplak yazar ve önekli bir _xlfn.XIRR, Excel'in ürettiği token'dan farklı bir token'dır. GAMMA, GAUSS, PHI, SHEET, SHEETS ve XOR hepsi ön ek ister; Excel 2013'ün mühendislik ve karmaşık sayı kümesine eklediği sekiz fonksiyon da (IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH, IMSINH ve IMTAN) öyle, eski IMSIN, IMSQRT ve IMSUM ise çıplak kalır. Yalnızca çalışma sayfasında geçerli dizi fonksiyonları ikinci bir ön ek taşır: MS-XLSX §2.2.2'nin FILTER, SORT ve PY için ayırdığı _xlfn._xlws.. Okurken HotXLS her iki ön eki de kaldırır; dolayısıyla Cell.Formula temiz fonksiyon adını döndürür
Excel hangi fonksiyonlara ön ek koyar da MS-XLSX dilbilgisi onları çıplak bırakır?
MS-XLSX §2.2.2 listeyi ABNF olarak şöyle yazar: future-function-list = ("_xlfn." ("AGGREGATE" / "ACOT" / … / "Z.TEST")) / "ECMA.CEILING" / "ISO.CEILING" / "NETWORKDAYS.INTL" / "WORKDAY.INTL" / "FORECAST.ETS" / …. Harfi harfine okursanız Z.TEST'ten sonraki kapanış parantezi önekli grubu bitirir; dolayısıyla ondan sonraki her şey çıplak bir addır. İkinci bir üretim kuralı, sıradan gömülü fonksiyon listesi, SUM ve VLOOKUP'un yanında CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS ve SWITCH'i içerir. Excel iki konuda da katılmaz:
| Fonksiyon | MS-XLSX dilbilgisinin ona verdiği yer | Excel'in yazdığı |
|---|---|---|
| NETWORKDAYS.INTL, WORKDAY.INTL | _xlfn. grubundan sonra, yani çıplak | _xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL |
| ISO.CEILING | _xlfn. grubundan sonra, yani çıplak | _xlfn.ISO.CEILING |
| FORECAST.LINEAR ve FORECAST.ETS ailesi | _xlfn. grubundan sonra, yani çıplak | Önekli, örneğin _xlfn.FORECAST.LINEAR |
| CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCH | Sıradan gömülü fonksiyon listesi | Önekli, örneğin _xlfn.TEXTJOIN |
| XIRR, XNPV, ERFC | future-function listesinde değil | Çıplak |
Dilbilgisinden üretilmiş bir yazıcı bu yüzden NETWORKDAYS.INTL(…)'i ön eksiz üretir ve Excel, yayımlanmış sözdizimine göre doğrulanan bir dosyada #NAME? ile cevap verir. Listeyi kaydedilmiş Excel dosyalarından alın ve ABNF'yi yalnızca bir başlangıç noktası sayın. HotXLS hesapladığı her fonksiyon için bunu yapar; önekli CONCAT da önekli NETWORKDAYS.INTL de Excel'in yazdığı gibi çıkar. Aşağıdaki kod aynı türden formülleri iki biçimde kaydeder ve birini geri okur:
uses
SysUtils, lxCalc, lxHandle, lxHandleX;
procedure SaveModernFunctions;
var
XBook, XCheck: TXLSXWorkbook;
XSheet: TXLSXWorksheet;
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
begin
// XLSX: ön ekler kaydetmede eklenir, yüklemede sıyırılır
XBook := TXLSXWorkbook.Create;
try
XSheet := XBook.Sheets.Add('Calc');
XSheet.Cells[1, 1].Value := 46000.0; // bir tarih serial'i
XSheet.Cells[1, 2].Formula := 'EDATE(A1,1)'; // ToolPak: ön ek yok
XSheet.Cells[1, 3].Formula := 'GAMMA(5)'; // _xlfn.GAMMA
XSheet.Cells[1, 4].Formula := 'NETWORKDAYS.INTL(A1,A1+30,11)';
XSheet.Cells[1, 5].Formula := 'TEXTJOIN("-",TRUE,B1:D1)';
if XBook.SaveAs('modern.xlsx', xlsxOpenXMLWorkbook) <> lxOk then
raise Exception.Create('XLSX save failed');
finally
XBook.Free;
end;
XCheck := TXLSXWorkbook.Create;
try
if XCheck.Open('modern.xlsx') <> lxOk then
raise Exception.Create('XLSX open failed');
// Beklenen: NETWORKDAYS.INTL(A1,A1+30,11), ön ek zaten kaldırılmış
Writeln(string(XCheck.Sheets[1].Cells[1, 4].Formula));
finally
XCheck.Free;
end;
// BIFF8: aynı çağrılar name token'ları ve Ftab 255 girdisine dönüşür
Book := TXLSWorkbook.Create;
try
Sheet := Book.Sheets.Add;
Sheet.Cells[1, 1].Value := 46000.0;
Sheet.Cells[1, 2].Formula := '=EDATE(A1,1)'; // add-in EXTERNNAME
Sheet.Cells[1, 3].Formula := '=ISOWEEKNUM(A1)'; // gizli _xlfn NAME
if Book.SaveAs('modern.xls') <> lxOk then // varsayılan olarak BIFF8
raise Exception.Create('XLS save failed');
finally
Book.Free;
end;
end;
Her iki dosya da güncel Excel'de tüm fonksiyonlar yerinde açılmalı. Ön ekleri görmek için paketi bir ZIP olarak açın ve çalışma sayfası parçasının ham <f> metnini listeleyin; modern.xlsx için aşağıdaki rutin EDATE(A1,1), _xlfn.GAMMA(5), _xlfn.NETWORKDAYS.INTL(A1,A1+30,11) ve _xlfn.TEXTJOIN("-",TRUE,B1:D1) basmalı. Tırnaklar " çıkar, çünkü rutin XML metnini escape'siz basar; bu bir teşhis için zararsızdır. Herhangi bir üreticinin dosyalarında çalışır; dolayısıyla üçüncü taraf bir çalışma kitabı, sizin Excel kopyanızın desteklediği bir fonksiyonda #NAME? gösterdiğinde en hızlı kontrol budur
uses
SysUtils, Classes, Zip;
// Bir çalışma sayfası parçasının her <f> ögesini ön eklerle birlikte bas
procedure DumpRawFormulas(const XlsxFile: string;
const Part: string = 'xl/worksheets/sheet1.xml');
var
Zip: TZipFile;
Bytes: TBytes;
Xml: string;
P, StartTag, Close: Integer;
begin
Zip := TZipFile.Create;
try
Zip.Open(XlsxFile, zmRead);
Zip.Read(Part, Bytes);
finally
Zip.Free;
end;
Xml := TEncoding.UTF8.GetString(Bytes);
P := 1;
while True do
begin
StartTag := Pos('<f', Xml, P);
if StartTag = 0 then
Break;
P := StartTag + 2;
if not CharInSet(Xml[P], ['>', ' ', '/']) then
Continue; // <formula>, <font>: hücre formülü değil
P := Pos('>', Xml, P) + 1; // t="shared" gibi nitelikleri atla
if Xml[P - 2] = '/' then
Continue; // paylaşımlı bir formülün boş <f .../>'su
Close := Pos('</f>', Xml, P);
if Close = 0 then
Break;
Writeln(Copy(Xml, P, Close - P)); // XML-escape'li: " & <
P := Close + 4;
end;
end;
Bir BIFF8 .xls dosyası numarası olmayan bir fonksiyonu nasıl çağırır?
BIFF8 formülleri hiçbir fonksiyon adı içermez. Her gömülü fonksiyon, sabit bir fonksiyon tablosundaki (Ftab) bir sayıdır ve bir formül, o sayıyla ptgFunc veya ptgFuncVar saklar. Daha yeni fonksiyonların numarası yoktur. MS-XLS o çağrıları 0x00FF Ftab girdisi, yani "User Defined Function" üzerinden yönlendirir: ilk argümanı gerçek fonksiyona işaret eden bir name token'ı olan, fonksiyon indeksi 255'lik bir ptgFuncVar. Token'da saklanan argüman sayısı kullanıcının yazdığından bir fazladır, çünkü baştaki o ad bir argüman sayılır. Excel hangi name token'ını yazacağına fonksiyonun ailesine göre karar verir:
| Fonksiyon ailesi | Name token'ı | Adın gösterdiği şey |
|---|---|---|
| Analysis ToolPak fonksiyonları: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR … | ptgNameX | Çalışma kitabının add-in SUPBOOK'undaki bir EXTERNNAME kaydı |
Excel'in _xlfn. ile yazdığı fonksiyonlar: XLOOKUP, ISOWEEKNUM, IFS, CONCAT … | ptgName | Tanımı #NAME? olan _xlfn.XLOOKUP (veya _xlfn._xlws.FILTER) adında gizli bir NAME kaydı |
| Kaydeden Excel kopyasının tanımadığı fonksiyonlar | ptgName | Boş tanımlı sıradan bir çalışma kitabı NAME kaydı |
İlk satırdaki add-in SUPBOOK, bir okuyucunun dış referansları sıralarken karşılaştığı kaydın kendisidir ve BIFF SUPBOOK kayıtlarının dış referansları sınıflandırması, onun öbür türler arasında nerede oturduğunu gösterir. Add-in'e benzeyen her şey bu yoldan gitmez. DATEDIF, USDOLLAR, FINDB ve ISPMT başta olmak üzere bir kısmının yerli Ftab numarası vardır ve düz fonksiyon çağrıları olarak saklanır. Üçüncü satır kırılgan olandır: dosyayı kaydeden Excel'den çok daha yeni bir fonksiyon — örneğin REGEXTEST ve REGEXEXTRACT — tanımsız sıradan bir ad üzerinden çağrıya dönüşür. Boş tanımlı adları çöp diye atan bir okuyucu, formülü artık var olmayan bir ad indeksine işaretçi bırakır ve Excel, yazdığı bütün dosyayı açmayı reddeder. HotXLS böyle bir adı #NAME? tanımıyla tutar ve kaydetmede özgün kaydı yeniden yazar
Her ad, ilk çalışma sayfasından önce var olmak zorundadır
Sıralama bir sonraki kısıttır. Bir BIFF8 dosyası global bölümünü — NAME tablosu ve EXTERNSHEET'i — herhangi bir çalışma sayfası alt akışından önce yazar; dolayısıyla bir formülün işaret edeceği her ad, ilk hücre yazılmadan önce biliniyor olmalıdır. Üçüncü sayfanın ortasında XLOOKUP ile karşılaşan bir yazıcı, ihtiyaç duyduğu gizli adı artık ekleyemez. BIFF8'i siz üretiyorsanız global bölümü yazmadan önce her formül kaynağını tarayın: hücre formülleri, çalışma kitabı ve sayfa düzeyindeki adlar, veri doğrulama kuralları ve koşullu biçimlendirme. Boyutlar token'lardan çıkar. Bir name çağrısı argümanların üstüne 9 bayt gelir (5'i ptgName, 4'ü ptgFuncVar), add-in çağrısı 11 (7'si ptgNameX, 4'ü ptgFuncVar); yazmadan önce hesaplanan kayıt uzunlukları, sonradan yazılan baytlarla aynı token seçimini kullanmak zorundadır. HotXLS bu adların hepsini hiçbir şey yazmadan tek geçişte çözer
İşlenen sınıfları Excel'in hesapladığını değiştirir
BIFF8 formülündeki her argüman bir referans, değer veya dizi işleneni olarak etiketlenir ve Excel etikete kelimesi kelimesine uyar. AGGREGATE'in üçüncü argümanını değer olarak etiketlerseniz Excel #VALUE! döndürür. SUM(SORTBY(…))'in iç sonucunu değer olarak etiketlerseniz Excel örtülü bir kesişim ekler, SUM(@SORTBY(…)) gösterir ve tek bir hücreyi toplar. Bir yazıcı bu argümanları Excel'in yaptığı gibi etiketlemek zorundadır; bu da her zaman fonksiyonun belgelendirmesinin ima ettiği sınıf değildir: add-in fonksiyon argümanları referans olarak yazılır, dinamik dizi fonksiyonları dizi döndürür ve XLOOKUP, TAKE, DROP, IFS ve SWITCH, bir referansın beklendiği yerde oturduklarında referans döndürür. Yanlış sınıflar dosyayı bozmaz. Cevabı değiştirir
VBA fonksiyonları çalışma kitabını XLSB ikili biçiminde tutar
XLSB üçüncü saklama durumunu ekler. Bir makro çalışma kitabı kendi VBA Function prosedürlerini hücrelerden çağırabilir ve ikili biçim her birini, formülü boş, prosedür olarak işaretlenmiş bir ad olarak beyan eder; hücre sonra onu, BIFF8'deki ptgName artı 255 fonksiyonu kalıbının aynısıyla çağırır. XML biçimlerinin eşdeğeri yoktur. Boş tanımlı bir ad orada geçerli değildir ve #NAME? olarak tanımlı bir ad, aynı adlı VBA fonksiyonunu gizlerdi. HotXLS böyle bir çalışma kitabını beyanlar yerinde olarak XLSB'ye geri kaydeder ve tek bir bayt yazmadan önce başka hiçbir biçime dönüştürmeyi reddeder. Dönüştürmeden önce dönüştürme raporunu isteyin, kaydetme başarısız olduktan sonra değil: TXLSXWorkbook.GetConversionReport, bu çalışma kitapları için desteklenmeyen olarak işaretlenmiş bir VBAFunctionNames girdisi döndürür ve kod bunun üzerinden dallanabilir
uses
SysUtils, lxCalc, lxDiagnostics, lxHandleX;
procedure ConvertOrKeepBinary(const XlsbFile: string);
var
Book: TXLSXWorkbook;
Report: TXLSConversionReport;
I: Integer;
Blocked: Boolean;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open(XlsbFile) <> lxOk then
raise Exception.Create('Open failed');
Report := Book.GetConversionReport(xlsConversionOpenXmlWorkbook);
try
Blocked := False;
for I := 0 to Report.Count - 1 do
if Report[I].Fidelity = xlsConversionUnsupported then
begin
Blocked := True;
Writeln(string(Report[I].Feature), ': ', string(Report[I].Message));
end;
finally
Report.Free;
end;
if Blocked then // örn. VBAFunctionNames
Book.SaveAs(ChangeFileExt(XlsbFile, '.edited.xlsb'),
xlsxExcelBinaryWorkbook)
else
Book.SaveAs(ChangeFileExt(XlsbFile, '.xlsx'), xlsxOpenXMLWorkbook);
finally
Book.Free;
end;
end;
Başka bir aracın çalışma kitabı Excel'in açıkça bildiği bir fonksiyonda #NAME? gösterdiğinde önce her şeyi bırakın DumpRawFormulas çalıştırın: <f> metnindeki çıplak bir NETWORKDAYS.INTL veya TEXTJOIN, teşhisin tamamıdır. Aynı formül metnini HotXLS Delphi Excel component ile Cell.Formula üzerinden atayın; XLSX Excel'in beklediği ön eki alır, çalışma kitabının .xls kaydı ise gizli adı ve 255 fonksiyonlu çağrıyı alır