Artykuł techniczny

Dlaczego Excel pokazuje 1.23457E+11 dla liczby 12-cyfrowej

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.6789012345 pokazuje się jako 12345.6789, a 12345678901.5 zaokrągla się do 12345678902
  • Dwunastocyfrowa część całkowita nie mieści się, więc 123456789012 staje 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.00012345678 wyświetla się jako 0.000123457, ale 0.0000123456789 staje 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

Przepływ decyzyjny reguły jedenastu znaków wyświetlania formatu General Excela w HotXLS dla Delphi: zapis stałopozycyjny, przełączenie naukowe i minus poza budżetem
Szeroka kolumna daje formatowi General jedenaście znaków na cyfry i punkt dziesiętny, a znak minusa czeka poza budżetem

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 / TEXTTekst formuły (&"")Delphi FloatToStr
1234567890121.23457E+11123456789012123456789012
12345678901234561.23457E+1512345678901234601.23456789012346E15
1/30.3333333330.3333333333333330.333333333333333
1E201E+201E+201E20
0.0000010.0000010.0000011E-6
0.000001234567891.23457E-060.000001234567891.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

Jeden zapisany double mapowany na trzy stringi w HotXLS dla Delphi: wyświetlanie siatki Excela, tekst formuły z piętnastoma cyframi znaczącymi i wynik Delphi FloatToStr
Wyświetlanie siatki, tekst formuły i FloatToStr każde stosują własną regułę, więc wybieraj według tego, gdzie string będzie porównywany

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

Anatomia identyfikatora szesnastocyfrowego w HotXLS dla Delphi: zapisana wartość komórki, piętnastocyfrowy tekst formuły i przepisany wpis dają trzy różne liczby
Identyfikatory to etykiety zapisane cyframi, więc trzymaj je jako tekst, a żadna reguła nie zaokrągli ostatniej cyfry

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