Експортиране на Excel работни книги в CSV, TSV, HTML и RTF в Delphi с HotXLS

Представете си автоматизирана задача, която генерира фактура в код и я записва като CSV за импортиране в друга система. Числата изглеждат вярно в Excel, а CSV файлът се отваря без проблем в текстов редактор. Но импортерът дава грешка на колоната за обща сума, тъй като клетката съдържа =SUM(D2:D41), формулата как текст, а не изчислената стойност. Всичко работи според спецификацията, това е очакваното поведение в HotXLS. Модулът за експорт записва модела на клетките в точния му вид, а клетка с формула, която никога не е изчислявана, съдържа само текста на самата формула.

Защо вашият CSV файл съдържа формули вместо числа

HotXLS съхранява текста на формулата и изчислената стойност как два отделни параметъра. Методът SaveAsCSV не стартира машината за изчисления при запис, експортът не трябва да променя работната книга или да рискува забавяне при сложни вериги от формули. Файловете, записани от самия Excel, съдържат кеширани резултати до формулите, поради което повторният им експорт работи според очакванията. Проблемът засяга генерираните от вас файлове, при които формулите са записани, но не са изчислени. Решението е да изчислите стойностите преди експорт чрез метода Calculate, който разпознава връзките между листовете и потребителските функции:

var

  Book: TXLSXWorkbook;

  Sheet: TXLSXWorksheet;

  R: Integer;

begin

  Book := TXLSXWorkbook.Create;

  try

    Book.Open('invoice-run.xlsx');

    Sheet := Book.Sheets[0];



    // Materialize formula results so the CSV carries numbers, not '=...' text

    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, ',');    // sheet 0, comma

    Book.SaveAsCSV('feed.tsv', 0, #9);     // same sheet as TSV

  finally

    Book.Free;

  end;

end;

Обърнете внимание на цикъла: той заменя формулите в клетките с техните изчислени стойности. Това е правилно за временен експорт, но не и ако планирате да запишете книгата отново като .xlsx по-късно, тъй като формулите ще бъдат заменени от статични числа. Експортирайте от копие или ограничете записването само до текущия проход. Възможностите на Calculate, включително регистриране на собствени функции, са разгледани в машината за формули на HotXLS и потребителски функции.

Какво гарантира модулът за текстови файлове с разделители

CSV експортът генерира UTF-8 файл с BOM маркер (byte order mark), CRLF завършеци на редовете и правила за цитиране по стандарта RFC 4180. Всяко поле, съдържащо разделител, кавичка или прекъсване на ред, се огражда с кавички, а вградените кавички се удвояват. Датите се записват във формат yyyy-mm-dd hh:nn:ss независимо от зададеното форматиране в клетката. Това е правилното решение за машинна обработка, макар да изненадва потребители, които очакват визуалното оформление на екрана да се запази. Клетките с форматиран текст се обединяват в един чист низ.

Тези настройки решават повечето проблеми с импортирането, но две от тях трябва да присъстват във вашите спецификации. Първата е BOM маркерът, той позволява на Excel да отваря файла с правилно кодирани специални символи, но някои строги парсери го приемат като данни; в такъв случай го премахнете ръчно. Втората е TSV форматът, той ползва същия механизъм, извикан с разделител табулация (#9), така че всичко описано по-горе е в сила. Листът за експорт се избира по индекс (базиран на 0) при по-дългото претоварване на метода, докато краткият вариант SaveAsCSV(FileName) записва активния лист.

HTML експортът е моментна снимка, а не формат за обмен

Докато CSV запазва само стойностите, методът SaveAsHTML се опитва да съхрани външния вид: една <table> на лист, обединените области как colspan и rowspan и основни стилове, вградени като CSS. Цветовете от темите се пропускат, вместо да се изчисляват, така че шаблони с цветове от теми се показват по-семпли в HTML. Задавайте изрични RGB цветове на всичко, което трябва да се запази. Опциите се управляват от съответния обект:

var

  Opts: TXLSXHtmlExportOptions;

begin

  Opts := TXLSXHtmlExportOptions.Create;

  try

    Opts.Title := 'Weekly settlement';

    Opts.TableClass := 'report-grid';     // hook for the host page stylesheet

    Opts.WriteDocument := True;           // full page, not a fragment

    if Book.SaveAsHTML('settlement.html', 0, Opts) <> 0 then

      raise Exception.Create('Sheet index out of range');

  finally

    Opts.Free;

  end;

end;

Две подробности в този пример заслужават внимание. Задайте WriteDocument на False и изходът ще бъде чист табличен фрагмент вместо цяла страница, което е подходящо при вграждане на преглед в съществуващ дизайн, настройте TableClass и оставете стиловете на хост страницата да се погрижат за оформлението. Върнатата стойност също е различна: SaveAsHTML връща 0 при успех и -1 при грешен индекс на лист, така че обичайната проверка за = 1 ще отчете успешния експорт като грешка. Когато се нуждаете от определена област, методът TXLSXRange.SaveAsHTML експортира правоъгълен диапазон по същите правила.

RTF изход и къде все още се използва

Четвъртият формат генерира RTF 1.6 таблици за един лист чрез SaveAsRTF. Ширините на колоните се изчисляват приблизително като 96 twips за всеки знак ширина на колоната. Структурното ограничение тук е, че обединените клетки не се поддържат в изхода, само водещата клетка съдържа информация, а покритите се записват като празни. Това прави RTF неподходящ за сложни оформления, но той остава лесно решение за прехвърляне на таблични данни в текстообработващи програми или по-стари системи за управление на документи, които не поддържат HTML.

Двупосочен обмен: импортирането на CSV изтрива данните по дизайн

Четенето на CSV има свои особености. Методът OpenCSV изчиства цялата работна книга и я преструктурира като единичен лист с име Sheet1. Той работи като конструктор, а не като обединяване на данни, така че никога не го извиквайте за книга със незаписано съдържание. Подаването на #0 като разделител активира автоматично откриване. Флагът ADetectTypes управлява разпознаването на типове: при стойност true цифровите низове стават числа, ISO-8601 низовете, дати, а стойностите true/false, булеви променливи. Изключете го, когато данните съдържат идентификатори с водещи нули (например пощенски кодове или продуктови кодове), тъй като водещата нула ще изчезне при автоматичното преобразуване на 00123 в 123. Двете фасади предлагат еднакъв импорт, в съчетание с експорта това осигурява преобразуване без нужда от Excel, както е описано в генериране на Excel отчети от бази данни с HotXLS.

Експортиране директно в поток

Всеки метод за запис тук има версия за работа с поток (TStream), разположена до файловата версия (за CSV, HTML, RTF и самите работни книги). В сървърен код това е препоръчителният път. Уеб услуга, предлагаща сваляне на CSV, може да запише данните в TMemoryStream и да го препредаде директно към обекта за отговор, без временни файлове, задачи за почистване или конфликти между паралелни заявки с еднакви имена. Същото важи при запис в облачни хранилища или изпращане по поща, като файловата система се изключва напълно.

Този подход съответства на модела за внедряване на библиотеката. Двете фасади са разработени на чист Object Pascal и не зависят от Excel, COM автоматизация или системни процеси на сървъра. Всяка заявка може да създаде своя работна книга, да изпълни изчисленията и да запише изхода паралелно. Основният ресурс за наблюдение е оперативната памет. Тъй като моделът на книгата се съхранява в RAM по време на експорта, за много големи файлове е препоръчително да ограничите паралелните задачи или да ги опаковате на опашка, за да избегнете пикове в потреблението.

Малка подробност: задайте IncludeBOM на опциите за HTML, когато фрагментът ще се записва като отделен файл, чието кодиране се проверява от други инструменти. При директно изпращане по HTTP оставете дефинирането на кодировката на заглавните части (response headers) на сървъра.

Когато байтовете се показват грешно

Най-честият въпрос за поддръжка при CSV експорт е свързан с грешно кодиране, в Excel се показват нечетливи символи (mojibake) вместо специалните букви. Първата реакция е да се обвини модулът за запис, но той генерира UTF-8 BOM маркер точно за да предотврати това, и файлът обикновено е коректен. Проблемът е, че някой процес по трасето е премахнал BOM маркера, например трансфер по FTP в текстов режим, копиране на поток, което пропуска първите три байта, или прокси сървър с прекодиране. Диагностицирайте това на границата, а не в самото извикване. Отворете получения файл в шестнадесетичен (hex) редактор и потвърдете, че първите три байта са EF BB BF.

Това е общото правило за четирите формата. Самият експорт е лесен, а HotXLS прави оптимален избор при всяко решение на модула за запис. Проблемите възникват в точките на свързване, когато формулата срещне парсер, очакващ число, когато BOM маркерът срещне транспорт, който не го съхранява, или когато обединена клетка срещне плоския модел на RTF. Всеки от тези факти трябва да бъде описан в договора между вашия модул за експорт и системата, която чете данните, тъй като тя не може да гадае вашите намерения по байтовете. Пълният списък с методи е достъпен на продуктовата страница на HotXLS Component.