Eksport w Delphi zapisuje numer konta 123456789012 jako liczbę; szeroka kolumna Excela pokazuje 1.23457E+11, podczas gdy =A1&"" zwraca wszystkie dwanaście cyfr. Excel zamienia liczby na tekst według dwóch reguł, jedenastu znaków dla wyświetlania General i piętnastu cyfr znaczących wewnątrz formuł, więc eksport HotXLS czyta pierwszą z FormattedText i buduje drugą silnikiem formuł
Ile znaków pozwala format General?
Jedenaście, z minusem liczonym poza budżetem. ECMA-376 Part 1 §18.8.30 mówi, że aplikacja „ma usiłować wyświetlić pełną liczbę do 11 cyfr (wliczając punkt dziesiętny)”, a jej reguła zmiennoprzecinkowa precyzuje to ostrzej: „maksymalna łączna długość wyświetlania komórki to 11, nie licząc znaku minusa”. Standard szkicuje potem cztery warunki przejścia na formę wykładniczą i na tym się kończy. Zaokrąglanie pozostawia otwarte. Podobnie punkt przełączenia dla małych liczb. Zachowanie poniżej to to, co Excel pokazuje w szerokiej kolumnie, i to, co HotXLS odtwarza w tekście wyświetlanym komórki oraz w TEXT(A1, "General"):
- Liczba, której część całkowita mieści się w jedenastu znakach, zostaje w zapisie stałopozycyjnym i poświęca miejsca dziesiętne:
12345.6789012345pokazuje się jako12345.6789, a12345678901.5zaokrągla się do12345678902 - Dwunastocyfrowa część całkowita nie mieści się, więc
123456789012staje się1.23457E+11, z mantysą zaokrągloną, żeby zrobić miejsce na wykładnik - Liczba poniżej 1 trzyma zapis stałopozycyjny, dopóki ten pokazuje przynajmniej tyle cyfr znaczących, ile sześciocyfrowa forma naukowa:
0.00012345678wyświetla się jako0.000123457, ale0.0000123456789staje się1.23457E-05 - Wykładnik zawsze niesie znak i co najmniej dwie cyfry, więc 1020 wyświetla się jako
1E+20
Znak siedzi poza budżetem. -12345678901 wyświetla się z wszystkimi jedenastoma cyframi za swoim minusem, dwanaście znaków łącznie, podczas gdy -123456789012 staje się -1.23457E+11. Typ Variant, który przypiszesz, nie zmienia wyniku. Excel trzyma każdą numeryczną wartość komórki jako double, a HotXLS przepuszcza wartości Integer, Int64, Currency i Double przez tę samą regułę jedenastu znaków, więc Cells[1, 1].Value := Int64(123456789012) też wyświetla się jako 1.23457E+11. Reguła przechodzi też do formatów niestandardowych: tekst literal w sekcji jest dokładany wokół wyniku General, więc General" kg" pokazuje tę samą liczbę jako 1.23457E+11 kg. Formaty z sekcjami pustymi albo wyłącznie literalnymi podążają za własnymi regułami i potrafią schować liczbę całkiem
Jeszcze jeden limit siedzi poza jakąkolwiek biblioteką. Standard dodaje, że „optymalna metoda wyświetlania zależy od dostępnej szerokości komórki”, i w kolumnie o domyślnej szerokości Excel ściska dalej, aż do 1.2E+11 albo rzędu znaków #, zależnie od metryk fontów maszyny pokazującej arkusz. Serwer generujący pliki nie ma kolumny na ekranie, więc HotXLS zostawia ten ostatni krok temu, kto rysuje komórkę. Poszerz kolumnę, zanim zaczniesz porównywać. Inaczej mierzysz awaryjny tryb szerokości Excela przeciwko regule General, a te dwie rozmijają się na każdej wartości dłuższej niż kolumna
Tekst formuły trzyma piętnaście cyfr znaczących
Kiedy liczba staje się tekstem wewnątrz formuły — operator &, CONCAT, TEXTJOIN, LEN, LEFT — przejmuje inna reguła. ECMA-376 §18.17 definiuje gramatykę formuł, ale nie definiuje formatu liczba-na-tekst dla tego kroku, więc jedynym odniesieniem jest wyjście Excela. Excel zaokrągla do piętnastu cyfr znaczących i zapisuje je w gołym zapisie stałopozycyjnym, dopóki kosztuje to najwyżej dwadzieścia znaków poza znakiem. Stąd =1/3&"" zwraca 0.333333333333333, piętnaście trójek. =1E+19&"" zwraca 10000000000000000000, a =1E-18&"" zwraca 0.000000000000000001. Po przekroczeniu dwudziestu znaków tekst przełącza się na zapis naukowy z tym samym podpisanym, dwucyfrowym wykładnikiem, którego używa siatka: 1E+20, 1.23456789012345E-05. Separatorem jest zawsze kropka. HotXLS liczy formuły bez zaglądania do ustawień regionalnych maszyny, na której biegnie twój program, więc klucz wyszukiwania zbudowany & wychodzi identycznie na serwerze angielskim i niemieckim
Tekst formuły ma jeden zakamarek, w którym sam Excel zależy od maszyny. CHAR i CODE mapują liczby powyżej 127 przez stronę kodową ANSI komputera, na którym biegnie Excel, więc =CHAR(128) to znak euro na instalacji Windows zachodnioeuropejskiej, a pod innymi stronami kodowymi inny znak. Serwis w Delphi nie może wiedzieć, jakiej strony kodowej użyje ostateczny czytelnik. HotXLS liczy te kody jako Windows-1252, stronę zachodnioeuropejską, na każdej maszynie, więc =CHAR(150) daje półpauzę na każdym serwerze. Jeśli twoje skoroszyty są przeznaczone dla innej strony kodowej, policz te znaki w Delphi i zapisz je jako tekst
FloatToStr nie podąża za żadną z reguł
| Wartość | Wyświetlanie General / TEXT | Tekst formuły (&"") | Delphi FloatToStr |
|---|---|---|---|
| 123456789012 | 1.23457E+11 | 123456789012 | 123456789012 |
| 1234567890123456 | 1.23457E+15 | 1234567890123460 | 1.23456789012346E15 |
| 1/3 | 0.333333333 | 0.333333333333333 | 0.333333333333333 |
| 1E20 | 1E+20 | 1E+20 | 1E20 |
| 0.000001 | 0.000001 | 0.000001 | 1E-6 |
| 0.00000123456789 | 1.23457E-06 | 0.00000123456789 | 1.23456789E-6 |
Ostatnia kolumna wyjaśnia dryf. FloatToStr używa formatu ogólnego Delphi z piętnastoma cyframi, pisze wykładniki bez znaku plusa i bez wyściełania zerami i przełącza się na zapis wykładniczy na własnych progach: poniżej 0.00001 albo gdy część całkowita potrzebuje więcej niż piętnaście cyfr. Bierze też separator dziesiętny z FormatSettings gospodarza. To porządna funkcja do logów. Nie odtwarza żadnej z reguł Excela, a jeden pomocnik proszony o obsługę i tekstu wyświetlanego, i tekstu formuły będzie się mylił w każdym wierszu, w którym te reguły się rozmijają — w czterech z sześciu wierszy powyżej
Jakiego tekstu powinien użyć eksport albo klucz wyszukiwania?
Wybierz regułę, zadając pytanie, gdzie string będzie porównywany. Tekst dla ludzi — raport PDF, tabela HTML, CSV, które ktoś otworzy obok skoroszytu — powinien zgadzać się z siatką, więc czytaj tekst wyświetlany komórki. Tekst używany jako dane powinien podążać za regułą formuły: klucz sklejany, wartość porównywana z czymś, co wyprodukowała formuła Excela, string wyszukiwania. CSV, który wróci do Excela albo bazy danych, też liczy się jako dane. Zapisanie 1.23457E+11 do takiego pliku wyrzuca pięć cyfr numeru konta bezpowrotnie, bo importer parsuje to, co czyta, i nie umie odtworzyć oryginału. Pierwszy przykład drukuje wszystkie trzy stringi obok siebie. Próbki to Double, ale wartość Int64 albo Currency wyświetla się tak samo:
uses
SysUtils, Variants, lxHandleX;
procedure CompareNumberTexts;
const
Samples: array[0..5] of Double =
(123456789012.0, 1234567890123456.0, 1 / 3, 1E20, 0.000001,
0.00000123456789);
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Numbers');
for I := 0 to High(Samples) do
begin
Sheet.Cells[1, 1].Value := Samples[I]; // format General domyślnie
Writeln(Format('%-22s %-22s %s', [
string(Sheet.Cells[1, 1].FormattedText), // co pokazuje siatka
VarToStr(Sheet.Calculate('A1&""')), // co widzi formuła
FloatToStr(Samples[I])])); // pomysł samego RTL
end;
finally
Book.Free;
end;
end;
Każdy wydrukowany wiersz powinien zgadzać się z odpowiednim wierszem tabeli. Ruszają się tylko separatory. Na gospodarzu niemieckim kolumna wyświetlania i kolumna FloatToStr przełączają się na przecinek dziesiętny, tak jak siatka Excela na tej maszynie, podczas gdy kolumna formuły trzyma swoją kropkę. Drugi przykład wkłada wybór do eksportera, który pisze tekst raportu tak, jak Excel wyświetla, a kolumny kluczy tak, jak formuły Excela budują:
uses
SysUtils, Classes, Variants, lxHandleX, lxCalc;
// Tekst raportu tak, jak pokazuje Excel, plus kolumna kluczy niezależna od locale
procedure ExportInvoiceLines(Sheet: TXLSXWorksheet; FirstRow, LastRow: Integer;
Output: TStrings);
var
Row: Integer;
Shown, Key: string;
begin
for Row := FirstRow to LastRow do
begin
Shown := Sheet.Cells[Row, 3].FormattedText; // kwota, jak wyświetlana
Key := VarToStr(Sheet.EvaluateFormulaAt(Row, 6,
Format('A%d&"-"&C%d', [Row, Row])).Value); // klucz kwoty faktury
Output.Add(Format('%s;%s', [Shown, Key]));
end;
end;
EvaluateFormulaAt liczy formułę tak, jakby siedziała w danej komórce, bez jej zapisywania, więc klucz pochodzi z tej samej reguły, którą Excel stosuje do kolumny kluczy wewnątrz skoroszytu. Jeśli kwota niesie format walutowy albo tysięcy, FormattedText stosuje ten format, a reguła General rządzi tylko komórkami bez formatu. Wkładanie zaokrąglonej wartości wyświetlanej z powrotem do obliczeń to inny przełącznik Excela, precyzja jak wyświetlana, i przepisuje on zapisanego double'a samego w sobie, więc trzymaj go z dala od eksportu, który potrzebuje tylko tekstu
Co się dzieje z identyfikatorem dłuższym niż piętnaście cyfr?
Traci swój ogon w momencie, gdy zamienia się w tekst. Double pomieści każdą liczbę całkowitą do 253, czyli około 9,007 × 1015, więc szesnastocyfrowy numer klienta 1234567890123456 przeżywa nietknięty w komórce i w pliku, gdzie siedemnaście cyfr znaczących wystarcza do bezzstratnego round tripa double'a w XLSX. Z tekstem jest inna historia. Reguła General pokazuje 1.23457E+15, a reguła formuły zaokrągla do piętnastu cyfr znaczących i dopełnia zerem, więc =A1&"" zwraca 1234567890123460 — innego klienta. Excel trzyma też tylko piętnaście cyfr znaczących liczby wpisanej do komórki, więc użytkownik, który przepisze wartość, wyląduje na 1234567890123450. Jeden klient kończy z trzema liczbami: 1234567890123456 w komórce, 1234567890123460 w kluczu formuły i 1234567890123450 po przepisaniu
Trzymaj identyfikatory jako tekst od początku. Numery kont, numery kart i ID zamówień to etykiety zapisane cyframi; nikt ich nie sumuje, a wiodące zero należy do etykiety. Przypisane jako stringi docierają do siatki i do każdej formuły jako te same szesnaście znaków, a wyszukiwanie po nich nigdy nie zboczy. Excel oflagowuje takie komórki małym zielonym trójkącikiem liczb zapisanych jako tekst, uczciwa cena za klucz, który trzyma swoją ostatnią cyfrę:
// Identyfikatory wchodzą jako tekst; kwoty zostają numeryczne
procedure WriteCustomerRow(Sheet: TXLSXWorksheet; Row: Integer;
const CustomerNo: string; Balance: Currency);
begin
Sheet.Cells[Row, 1].Value := WideString(CustomerNo); // '1234567890123456' zostaje w całości
Sheet.Cells[Row, 2].Value := Balance; // pokazywana regułą General
end;
Zanim ruszysz eksporter, otwórz jeden wyprodukowany przez niego skoroszyt w Excelu, poszerz kolumnę z numerem konta i postaw obok =A1&"". Jeśli twój system downstream porównuje z lewym stringiem, proś komponent HotXLS Delphi Excel o FormattedText; jeśli z prawym, buduj tekst przez EvaluateFormulaAt