LlosLab Software Development BlogNotatki inżynieryjne dla programistów PDF, Delphi i oprogramowania do dokumentów.

Artykuł techniczny

Czytanie i zapis binarnych skoroszytów XLSB w Delphi

HotXLS Delphi Component

HotXLS czyta i zapisuje pliki Excel Binary Workbook (XLSB) z Delphi przez tę samą fasadę TXLSXWorkbook co dla XLSX: Open wykrywa skoroszyt binarny z zawartości pakietu, a SaveAs albo SaveAsChecked z xlsxExcelBinaryWorkbook zapisuje go. Backend pokrywa zwalidowany podzbiór i odrzuca wszystko, czego nie potrafi przedstawić, zanim dotknie celu

Ta ostatnia klauzula jest tą wartą czytania powoli. XLSB wygląda jak prosta wariacja XLSX i w terminach pakowania nią jest. Kłopot mieszka w szczegółach, które oba formaty kodują inaczej, i w tym, co biblioteka po cichu zakłada, gdy jedno mostkuje w drugie. Najbardziej pouczający błąd w tym backendzie wcale nie był zniekształconym rekordem. Był to domyślny font, który zniknął, i kolumna dat, która zamieniła się w #### w Excelu, mimo że wartość, kod formatu i szerokość kolumny były wszystkie poprawne

Czym jest plik XLSB i czym różni się od XLSX?

Plik XLSB to pakiet OPC z dokładnie tą strukturą części co skoroszyt XLSX, tylko części skoroszytu, arkusza, stylów i wspólnych stringów przechowują binarne rekordy zamiast XML. Format jest specyfikowany w [MS-XLSB], a kodowanie rekordów bywa zwane BIFF12. Nadal dostajesz [Content_Types].xml, części relacji i kontener ZIP; zmienia się to, że xl/workbook.bin zastępuje xl/workbook.xml, xl/worksheets/sheet1.bin zastępuje XML arkusza i tak dalej

Każdy rekord BIFF12 startuje kompaktowym nagłówkiem zmiennej długości. Typ rekordu zajmuje jeden albo dwa bajty, siedem bitów na bajt z najwyższym bitem jako flagą kontynuacji, więc typ rekordu mieści się w 14 bitach. Rozmiar ładunku idzie za nim w jednym do czterech bajtów tym samym siedmiobitowym schematem. Stringi to 32-bitowa liczba znaków, po której idą jednostki kodu UTF-16. Wobec BIFF8 to znacznie czystszy układ: żadnego rozcinania CONTINUE po 8224 bajty, żadnych flag stringów skompresowany-szeroki w środku rekordu. Wobec XLSX pomija tokenizację XML całkowicie, dlatego duże pliki XLSB bywają mniejsze na dysku i szybsze do otwarcia dla Excela

Jeden szczegół pakowania wywraca kod detekcji. Excel rejestruje część skoroszytu XLSB z typem zawartości application/vnd.ms-excel.sheet.binary.macroEnabled.main, nawet dla skoroszytów bez żadnych makr. HotXLS sprawdza najpierw tę rejestrację w [Content_Types].xml i tylko cofa się do rozszerzenia części .bin, gdy typu zawartości brak, i każda z dróg i tak musi przejść ścisły walidator rekordów. Ogólne reguły o nazwach części, relacjach i typach zawartości to te same, które omawia artykuł o tym, dlaczego Excel naprawia poprawny XLSX i stojące za tym reguły pakietu OPC

Kiedy wybrać XLSB zamiast XLSX?

Wybieraj XLSB, gdy system downstream go wymaga albo gdy wymieniasz duże, bogate w wartości skoroszyty z użytkownikami Excela, dla których liczy się czas otwarcia; w przeciwnym razie XLSX pozostaje bezpieczniejszym domyślnym. XLSX to standard ISO (ISO/IEC 29500) z szerokim wsparciem narzędzi, daje się obejrzeć narzędziem ZIP i edytorem tekstu, a każdy produkt arkuszowy go czyta. XLSB to format Microsoft z opublikowaną specyfikacją, ale węższym ekosystemem

W praktyce prośby przychodzą z dwóch stron. Część potoków raportowych i archiwów ustandaryzowała się na .xlsb lata temu i odrzuca wszystko inne. Inne zespoły generują skoroszyty ze setkami tysięcy komórek numerycznych i odkryły, że użytkownicy otwierają wersję binarną wyraźnie szybciej. Oba to porządne powody. "Brzmi wydajniej" już nie, bo binarny backend HotXLS staguje kompletny pakiet w pamięci, więc nie jest narzędziem do serwerowych eksportów w ograniczonej pamięci. Do tego właściwa jest streamingowy writer XLSX

Otwieranie i zapis XLSB przez TXLSXWorkbook

TXLSXWorkbook.Open nie potrzebuje podpowiedzi formatu: to samo wywołanie ładuje XLSX, XLSM albo XLSB, a po udanym ładowaniu binarnym TXLSXWorkbook.SourceFormat raportuje xlsxExcelBinaryWorkbook. Zapis jest jawny. SaveAs(FileName) bez formatu zawsze produkuje skoroszyt Open XML, nawet gdy nazwa pliku kończy się na .xlsb, więc wyjście binarne zawsze nazywa format

uses
  SysUtils, lxHandleX;

procedure ConvertToXlsb(const Source, Target: string);
var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    // Open wykrywa XLSX, XLSM albo XLSB z samego pakietu
    if Book.Open(Source) <> 1 then
      raise Exception.Create('Input is unsupported or invalid');

    // Samo rozszerzenie nigdy nie wybiera wyjścia binarnego
    if Book.SaveAsChecked(Target, xlsxExcelBinaryWorkbook) <> 1 then
      raise Exception.Create('XLSB output is unsupported or failed');
  finally
    Book.Free;
  end;
end;

Wyjście strumieniowe idzie przez SaveAs(Stream, xlsxExcelBinaryWorkbook) albo przeciążenie strumieniowe SaveAsChecked. Wewnątrz obie drogi prowadzą do prywatnych metod SaveAsXlsbStream i SaveAsXlsbFile; nie wołasz ich nigdy wprost. Wyjście plikowe jest stagowane do sąsiedniego pliku tymczasowego i commitowane jedną końcową podmianą. Cel strumieniowy musi być czytelny i przeszukiwalny, bo writer robi snapshot bajtów, rozmiaru i pozycji i przywraca je, gdy końcowe kopiowanie pada. TXLSXWorkbook.LastSaveUsedStreaming raportuje False po zapisie binarnym, co jest ścisłe: cały pakiet istnieje w pamięci, zanim pojedynczy bajt dotrze do twojego celu

Dlaczego zwalidowany podzbiór zamiast pełnej wierności?

HotXLS nie utrzymuje drugiego modelu obiektowego dla XLSB. Przy otwarciu XlsbToOpenXml konwertuje pakiet binarny do pakietu Open XML w pamięci i zwykły parser XML go ładuje; przy zapisie writer XML buduje pakiet, a OpenXmlToXlsb koduje go jako binarne rekordy. Każda funkcja musi więc przeżyć dwie translacje, a funkcja, której most nie potrafi przetłumaczyć dokładnie, jest odrzucana, a nie aproksymowana

Zestaw wspierany od HotXLS 2.384.113 jest na tyle szeroki, że starcza dla większości skoroszytów danych: wartości typowane, stringi współdzielone i wbudowane, popularne fonty, wypełnienia, obramowania i formaty liczb, wysokości wierszy i szerokości kolumn, zwykłe formuły komórkowe z wynikami w cache, nazwy zdefiniowane, scalone komórki, hiperłącza, panele zamrożone i podzielone, zakresy AutoFilter z prostymi kryteriami własnymi oraz zwykłe tabele arkuszowe. Poza nim siedzą formuły współdzielone i tablicowe, funkcje przyszłe i użytkownika, odwołania do zewnętrznych skoroszytów, wykresy i rysunki, PivotTables i slicery, VBA, formatowanie warunkowe, walidacja danych i nienaddomyślne ustawienia obliczeń, jak obliczanie ręczne albo iteracja

Odrzucenie jest obowiązkowe, nie doradcze. TXLSXWorkbook.GetConversionReport(xlsConversionExcelBinaryWorkbook) wypisuje niewspierane funkcje przed zapisem, i te pozycje wywracają zapis nawet z AAllowLossy = True albo niesprawdzanym SaveAs. To zamierzone. Po cichu wycięty PivotTable z binarnej kopii to ta strata, której nikt nie zauważa, dopóki skoroszyt nie stanie przed klientem

uses
  Classes, lxHandleX, lxDiagnostics;

function CanSaveAsXlsb(Book: TXLSXWorkbook; Log: TStrings): Boolean;
var
  Report: TXLSConversionReport;
  I: Integer;
begin
  Report := Book.GetConversionReport(xlsConversionExcelBinaryWorkbook);
  try
    Result := True;
    for I := 0 to Report.Count - 1 do
      if Report[I].Fidelity = xlsConversionUnsupported then
      begin
        Result := False;
        Log.Add(Report[I].SheetName + ' ' + Report[I].Location + ': ' +
          Report[I].Feature + ' - ' + Report[I].Message);
      end;
  finally
    Report.Free;   // raport należy do wołającego
  end;
end;

Raport pokrywa żywy model. Końcowa walidacja pakietu pozostaje ostatnią bramą dla rzeczy, których model nigdy nie widzi, jak zniekształcone rekordy albo obce atrybuty w pliku źródłowym, więc czysty raport, po którym idzie nieudany zapis, jest możliwy i to wynik zapisu jest tym, na czym działasz

Dlaczego zniknęły font domyślny i format 0.00?

Font domyślny i wbudowane formaty liczb zniknęły, bo czytnik stylów XML poczynił dwa założenia, które trzymają dla plików, które HotXLS sam zapisuje, ale nie dla skoroszytów pisanych przez Excel. Most zachował oba zasoby doskonale; strata wydarzyła się warstwę wyżej, w publicznej ścieżce Open. Ta luka to główna lekcja tego backendu: poprawny most formatów nie gwarantuje wiernej publicznej podróży w obie strony

Pierwsze założenie dotyczyło fonts[0]. Gdy HotXLS zapisuje XLSX, zapisuje niejawny font domyślny do slotu zero, a jego czytnik XML ten slot pomija, więc kolekcja Fonts widziana przez użytkownika startuje od indeksu zero. XLSB autorstwa Excela niesie w tym slocie własny, jawny domyślny, a jego pominięcie wyrzucało ten wybór. Drugie założenie dotyczyło formatów liczb. Czytnik mapował do NumberFormats tylko własne wartości numFmtId (164 i wyżej); wbudowane identyfikatory zostawały jako zero. Wystylowana pusta komórka niosąca wbudowany format 2, czyli 0.00, wracała jako General

Poprawka mieszka całkowicie w binarnej ścieżce importu. Po tym, jak cały pakiet przejdzie preflight, otwarcie XLSB ustawia prywatny tryb stylów wyłącznie dla synchronicznego parsowania XML. W tym trybie czytnik ładuje każdy font, slot zero włącznie, i przesuwa każdy fontId o jeden, konwertuje każdy wspierany wbudowany identyfikator na jawny kod formatu w modelu i wciąż używa oryginalnego numFmtId do detekcji dat, więc wbudowany format daty zachowuje semantykę daty. Blok finally zawsze czyści tryb, a późniejsze zwykłe Open XLSX albo ODS czyści też zatrzymany stan fontu domyślnego

Jak fonts[0] wpływa na szerokości kolumn Excela?

Excel liczy szerokości kolumn z fontu domyślnego skoroszytu w fonts[0], nie z fontów nałożonych na komórki tej kolumny. Szerokość kolumny width jest przechowywana w jednostkach znakowych maksymalnej szerokości cyfry fontu domyślnego, więc zmiana fonts[0] zmienia szerokość pikselową każdej kolumny, także kolumn, których komórki wszystkie używają innego fontu

To wyprodukowało najbardziej mylący symptom całego dochodzenia. Zapisany ponownie skoroszyt miał identyczne wartości komórek, identyczne kody formatów i identyczne atrybuty width, font komórki i styl Normal oba wskazywały zachowany font, a Excel wciąż renderował kolumnę dat jako ####. Zapisane fonts[0] wróciło do Calibri 11, maksymalna szerokość cyfry się skurczyła, a daty już się nie mieściły. HotXLS teraz trzyma źródłowy font domyślny i zapisuje go z powrotem jako fonts[0], cofając się do Calibri 11 tylko dla skoroszytów, które nigdy nie miały binarnego źródła. Pełna arytmetyka szerokości jest w artykule o szerokości kolumny Excel i maksymalnej szerokości cyfry (MDW) w Delphi

Formaty wbudowane zależne od ustawień regionalnych są odrzucane, nie zgadywane

Wbudowany identyfikator formatu liczb bez jawnego kodu formatu jest przyjmowany tylko, gdy jego znaczenie jest stałe; identyfikatory zależne od ustawień regionalnych są odrzucane podczas preflightu pakietu. HotXLS mapuje identyfikatory 0 do 22, 37 do 40 i 45 do 49 na znane kody, jak 0.00, 0%, mm:ss i @. Pozostałe identyfikatory poniżej 164 należą do formatów, których renderowanie zależy od ustawień regionalnych instalacji Excela, i import odmawia ich z komunikatem "A locale-dependent number format requires an explicit format code"

Wymyślenie kodu dla tych identyfikatorów byłoby gorsze niż porażka. Zgadnięty wzorzec daty wschodnioazjatyckiej wygląda wiarygodnie, robi podróż w obie strony bez błędu i pokazuje coś złego połowie twoich użytkowników. Timing też ma znaczenie: sprawdzenie biegnie w preflighcie całego pakietu, zanim istniejący skoroszyt zostanie wyczyszczony, więc odrzucony plik zostawia wcześniej wczytany model, nazwę pliku i format źródła nietknięte. Interakcje wbudowanych identyfikatorów dat z systemami dat 1900 i 1904 omawia artykuł o numerze seryjnym daty Excel i numFmt

procedure LoadBinaryWorkbook(Book: TXLSXWorkbook; const FileName: string);
var
  I: Integer;
begin
  // Wejście binarne wymaga pełnego ładowania od razu; tryby selektywne odrzucają z góry
  Book.MetadataOnly := False;
  Book.SelectedSheets.Clear;

  if Book.Open(FileName) <> 1 then
  begin
    for I := 0 to Book.Diagnostics.Count - 1 do
      Writeln(Book.Diagnostics[I].Message);
    Exit;   // wcześniej wczytany skoroszyt wciąż jest nietknięty
  end;

  // Wyjście formuł potrzebuje cache, chyba że zapis przelicza
  Book.RecalcBeforeSave := xrmAlways;
end;

Praktyczne reguły dla XLSB w kodzie produkcyjnym

Binarny backend jest bezpieczny do polegania, gdy traktujesz jego odrzucenia jak dane, a nie jak błędy do stłumienia. Z zachowania powyżej wynika kilka reguł

  • Nazywaj format przy każdym zapisie binarnym; samo rozszerzenie .xlsb wciąż produkuje XLSX
  • Puszczaj GetConversionReport(xlsConversionExcelBinaryWorkbook) przed zapisem skoroszytów edytowanych przez użytkownika i wypychaj niewspierane funkcje do użytkownika
  • Zostawiaj MetadataOnly wyłączone, a SelectedSheets puste dla wejścia binarnego, bo leniwe ładowanie wymagałoby trzymania skonwertowanego archiwum
  • Daj formułom wynik w cache albo przelicz przed zapisem, bo brakujący cache odrzuca pod xrmManual i pod czystym xrmIfDirty
  • Planuj pamięć na kompletny skonwertowany pakiet plus wyjście binarne; używaj streamingowego XLSX, gdy pamięć musi zostać ograniczona

Testując, porównuj z Excelem przez publiczne API, a nie na poziomie mostu. Regresje fontu domyślnego i 0.00 przeszły obie testy mostu i obie wyszły od razu w skoroszycie autorstwa Excela otwartym i zapisanym ponownie przez Open i SaveAsChecked

FAQ

Czy HotXLS wykrywa pliki XLSB automatycznie?

Tak. TXLSXWorkbook.Open ogląda typ zawartości zarejestrowany dla części skoroszytu i waliduje rekordy binarne, więc to samo wywołanie ładuje XLSX i XLSB, a SourceFormat raportuje, który został przeczytany

Dlaczego SaveAs z nazwą .xlsb wciąż zapisuje XLSX?

HotXLS nigdy nie wnioskuje formatu wyjścia z rozszerzenia. Przekaż xlsxExcelBinaryWorkbook do SaveAs albo SaveAsChecked, by zapisać skoroszyt binarny

Co się dzieje, gdy skoroszyt używa funkcji poza podzbiorem XLSB?

Zapis pada, zanim cel się zmieni, nawet gdy zapis stratny jest dozwolony. GetConversionReport wypisuje blokujące funkcje z wyprzedzeniem, a Diagnostics tłumaczy porażkę po fakcie

Wsparcie XLSB, raport konwersji i reszta API skoroszytu są w HotXLS, natywnym komponencie arkuszowym Delphi i C++Builder