Eksport Excela do CSV, TSV i HTML w Delphi (HotXLS)

Wyobraź sobie nocne zadanie, które buduje w kodzie skoroszyt faktur i zapisuje go jako CSV do zaimportowania przez system niżej w łańcuchu. Liczby wyglądają dobrze w Excelu. CSV otwiera się czysto w edytorze tekstu. A potem importer krztusi się na kolumnie sum, bo pole kwoty w wierszu 42 brzmi =SUM(D2:D41), czyli formuła jako dosłowny tekst, a nie liczba, którą powinna wyliczyć. Nic nie jest zepsute. To udokumentowane zachowanie i pierwsza rzecz do zrozumienia przy eksporcie z HotXLS: zapisujący serializuje model komórek dokładnie w takim stanie, w jakim ten stoi, a komórka formuły, której wartość nigdy nie została policzona, ma do oddania tylko tekst swojej formuły

Dlaczego twój CSV zawiera formuły zamiast liczb

HotXLS przechowuje tekst formuły i policzoną wartość jako dwie osobne rzeczy. SaveAsCSV celowo nie uruchamia silnika obliczeń po drodze na zewnątrz: eksport nie powinien mutować skoroszytu ani ryzykować utknięcia na patologicznym łańcuchu formuł. Pliki, które zapisał sam Excel, niosą buforowane wyniki obok formuł, więc ich ponowny eksport zachowuje się tak, jak oczekujesz. Pułapka jest specyficzna dla skoroszytów wygenerowanych przez twój własny kod, w których formuły zapisano, ale nigdy nie wyliczono. Lekarstwem jest sprawić, by wartości istniały przed eksportem, przy użyciu tego samego silnika Calculate, który rozwiązuje referencje międzyarkuszowe i funkcje własne:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('invoice-run.xlsx');
    Sheet := Book.Sheets[0];

    // Zmaterializuj wyniki formuł, żeby CSV niósł liczby, a nie tekst '=...'
    for R := 2 to 41 do
      if Sheet.Cells[R, 4].Formula <> '' then
        Sheet.Cells[R, 4].Value := Book.Calculate(Sheet.Cells[R, 4].Formula);

    Book.SaveAsCSV('feed.csv', 0, ',');    // arkusz 0, przecinek
    Book.SaveAsCSV('feed.tsv', 0, #9);     // ten sam arkusz jako TSV
  finally
    Book.Free;
  end;
end;

Zauważ, co ta pętla faktycznie robi: nadpisuje komórki formuł ich policzonymi wartościami. To dokładnie właściwe dla jednorazowego przebiegu eksportu i błędne, jeśli zamierzasz potem znowu zapisać skoroszyt jako .xlsx, bo właśnie zastąpiłeś żywe formuły zamrożonymi liczbami. Eksportuj z kopii albo ogranicz zapis zwrotny tak, by dotykał wyłącznie przebiegu eksportu. Silnik stojący za Calculate idzie dalej niż to, wraz z rejestrowaniem twoich własnych funkcji, co jest tematem artykułu silnik formuł HotXLS i funkcje własne

Co gwarantuje zapisujący z separatorami

Ścieżka CSV produkuje UTF-8 ze znacznikiem kolejności bajtów, zakończenia linii CRLF i cytowanie zgodne z RFC 4180. Każde pole zawierające separator, cudzysłów albo złamanie linii zostaje opakowane, a osadzone cudzysłowy są podwajane. Daty renderują się jako yyyy-mm-dd hh:nn:ss niezależnie od formatu wyświetlania komórki. To właściwa decyzja dla konsumenta maszynowego, choć zaskakuje każdego, kto oczekiwał, że formatowanie z ekranu przejdzie dalej. Komórki z tekstem formatowanym są spłaszczane przez sklejenie ich przebiegów

Te ustawienia domyślne rozstrzygają większość sporów z importerem, zanim się zaczną, ale dwa z nich i tak należą do twojego kontraktu interfejsu. Pierwsze to BOM. To on pozwala Excelowi otworzyć plik z nietkniętymi znakami diakrytycznymi, a jednak garstka rygorystycznych parserów traktuje te trzy bajty jako dane; jeśli twój jest jednym z nich, obetnij je przy przekazaniu. Drugie to TSV. To wcale nie osobna funkcja, tylko ten sam zapisujący wywołany z #9 jako separatorem, więc wszystko powyżej stosuje się do niego bez zmian. Arkusz do wyeksportowania wybiera się indeksem liczonym od zera w przeciążeniu wieloargumentowym, podczas gdy jednoargumentowy skrót SaveAsCSV(FileName) bierze arkusz aktywny

Eksport HTML to migawka, a nie format wymiany

Tam gdzie CSV wyrzuca wszystko poza wartościami, SaveAsHTML próbuje zachować wygląd: jedna <table> na arkusz, scalone obszary wyrażone jako colspan i rowspan, podstawowe stylowanie komórek wstawione w linii jako CSS. Kolory względne wobec motywu są pomijane, a nie rozwiązywane, więc szablon opierający się na gniazdach motywu wychodzi skromniej, niż wygląda w Excelu. Ustaw jawne kolory RGB na wszystkim, co ma przeżyć tę podróż. Obiekt opcji kontroluje kopertę:

var
  Opts: TXLSXHtmlExportOptions;
begin
  Opts := TXLSXHtmlExportOptions.Create;
  try
    Opts.Title := 'Weekly settlement';
    Opts.TableClass := 'report-grid';     // zaczep dla arkusza stylów strony hosta
    Opts.WriteDocument := True;           // pełna strona, nie fragment
    if Book.SaveAsHTML('settlement.html', 0, Opts) <> 0 then
      raise Exception.Create('Sheet index out of range');
  finally
    Opts.Free;
  end;
end;

Dwa szczegóły w tym fragmencie zwracają uwagę z nawiązką. Przestaw WriteDocument na False, a wyjściem staje się goły fragment tabeli zamiast pełnej strony, czego właśnie chcesz, wstrzykując podgląd do istniejącego układu: ustaw TableClass i pozwól arkuszowi stylów hosta zająć się motywem. Konwencja zwracania jest tu też odwrotna niż w większości wywołań HotXLS. SaveAsHTML zwraca 0 przy powodzeniu i -1 przy złym indeksie arkusza, więc odruchowe sprawdzenie = 1 zgłosi każdy udany eksport jako porażkę. Kiedy potrzebujesz obszaru, a nie całego arkusza, choćby po to, by wysłać go mailem albo osadzić pojedynczy blok, TXLSXRange.SaveAsHTML eksportuje dowolny prostokątny zakres według tych samych reguł renderowania

Wyjście RTF i to, gdzie nadal zarabia na swoje miejsce

Czwarty cel zapisuje tabele RTF 1.6, jeden arkusz na wywołanie przez SaveAsRTF. Szerokości kolumn są przybliżane mniej więcej 96 twipami na znak szerokości kolumny. Ograniczenie strukturalne, o którym trzeba wiedzieć, jest takie, że scalone komórki nie rozciągają się w wyjściu: tylko komórka kotwicząca niesie swoją treść, a komórki przykryte wychodzą jako puste. To wyklucza RTF przy szablonach mocno opartych na układzie. Nadal zarabia on na swoje miejsce jako ścieżka najmniejszego oporu przy wrzucaniu wyników tabelarycznych do edytora tekstu albo do starszego systemu zarządzania dokumentami, który powstał przed przyjmowaniem HTML

Podróż w obie strony: import CSV jest z założenia destrukcyjny

Wczytywanie CSV z powrotem ma własny kontrakt. OpenCSV czyści cały skoroszyt i buduje go od nowa jako pojedynczy arkusz o nazwie Sheet1. To w duchu konstruktor, a nie scalenie, więc nigdy nie wywołuj go na skoroszycie, który wciąż trzyma niezapisaną treść. Podanie #0 jako separatora włącza automatyczne wykrywanie separatora. Flaga ADetectTypes kontroluje promocję typów: przy włączonej ciągi numeryczne stają się liczbami, ciągi ISO-8601 stają się datami, a true/false stają się wartościami logicznymi. Wyłącz ją, gdy strumień niesie identyfikatory z wiodącymi zerami, kody pocztowe albo kody produktów — promocja po cichu przemiela je wszystkie w liczby (wiodące zero po prostu znika w chwili, gdy 00123 staje się 123). Obie fasady wystawiają ten sam import. Sparuj go z wywołaniami eksportu powyżej, a masz most formatów, który nie potrzebuje Excela zainstalowanego nigdzie w potoku — scenariusz omówiony w artykule generowanie raportów Excela z bazy danych w HotXLS

Eksport prosto do strumienia

Każdy zapisujący tutaj ma obok wersji z nazwą pliku przeciążenie strumieniowe: CSV, HTML, RTF i same formaty skoroszytów. W kodzie serwerowym to po te przeciążenia sięgasz. Punkt końcowy sieciowy, który serwuje pobranie CSV, może zapisać do TMemoryStream i podać go prosto obiektowi odpowiedzi, bez pliku tymczasowego, bez zadania sprzątającego i bez kolizji między dwoma żądaniami, którym trafiła się ta sama wygenerowana nazwa. To samo dotyczy wypychania eksportów do magazynu obiektów albo dołączania ich do poczty wychodzącej. System plików wypada z obrazka całkowicie

Ten wzorzec dobrze składa się ze sposobem wdrażania biblioteki. Obie fasady to natywne czytniki i zapisujące w Object Pascalu, więc nie ma instalacji Excela, nie ma automatyzacji COM i nie ma wąskiego gardła na poziomie procesu szeregującego żądania na serwerze. Każde żądanie może mieć własny obiekt skoroszytu, uruchomić zapis zwrotny obliczeń z pierwszej sekcji i strumieniować swój eksport równolegle z sąsiadami. Pamięć jest tym jednym zasobem, który trzeba mieć na oku. Model skoroszytu żyje w RAM przez czas trwania eksportu, więc usługa otwierająca bardzo duże pliki tylko po to, by wyemitować je ponownie jako CSV, powinna ograniczyć liczbę równoczesnych zadań albo kolejkować te przerośnięte, zamiast pozwalać skokowi ruchu decydować o zbiorze roboczym

Jedno mniejsze pokrętło: ustaw IncludeBOM w opcjach HTML, gdy fragment będzie zapisany jako samodzielny plik, którego kodowanie jakieś narzędzie niżej w łańcuchu wywęszy. Kiedy serwujesz HTML wprost przez HTTP, zostaw deklarację zestawu znaków nagłówkom odpowiedzi

Kiedy bajty i tak wychodzą złe

Najczęstsze pytanie do wsparcia o eksport CSV to problem z otwarcia artykułu w innym kostiumie: Excel pokazuje krzaczki zamiast znaków diakrytycznych. Odruch każe winić zapisującego, ale on emituje BOM UTF-8 dokładnie z tego powodu i plik prawie zawsze jest poprawny, gdy opuszcza twój kod. Coś między tym miejscem a Excelem zjadło BOM. Transfer FTP w trybie tekstowym, kopiowanie strumienia pomijające pierwsze trzy bajty, proxy przekodowujące po drodze: każde z nich zdejmie znacznik i zostawi Excelowi zgadywanie kodowania, co ten robi kiepsko. Diagnozuj to na granicy, a nie w wywołaniu eksportu. Otwórz dostarczony plik w podglądzie szesnastkowym i potwierdź, że EF BB BF nadal jest w nim pierwszą rzeczą

To wspólna nić wszystkich czterech formatów. Wywołanie eksportu jest łatwą częścią, a HotXLS podejmuje dającą się obronić decyzję przy każdym wyborze, przed jakim staje zapisujący. Awarie mieszkają na szwach: tam, gdzie tekst formuły spotyka parser, który chciał liczby, gdzie BOM spotyka transport, który go nie zachowuje, gdzie scalona komórka spotyka płaski model tabeli w RTF. Każdy z nich to fakt do wpisania w kontrakt między twoim eksporterem a tym, co go konsumuje, bo konsument nie odczyta twoich zamiarów z bajtów. Pełną listę metod w obu fasadach skoroszytu niesie strona produktu HotXLS Delphi Component