Генериране на Excel файлове в Delphi без автоматизация на Office

Ако единствената задача на сървъра е да генерира Excel файлове, той няма никаква работа да изпълнява Excel. Инсталирането на Office на изграждащ агент (build agent) или услуга за отчети, за да се управлява чрез COM автоматизация, е грешен дизайн и е бил такъв, откакто съществува тази практика. Самите Microsoft казват това в своите насоки, които не са се променили през последните двадесет години: Office е нито създаден, нито лицензиран да бъде автоматизиран от автоматизиран сървърен процес без потребителска намеса. Правилният отговор е да се пишат байтовете на BIFF и OOXML директно, без изобщо Excel да присъства в картинката. Това е цялата концепция на HotXLS - оригинална Object Pascal библиотека, която сама чете и записва форматите на електронните таблици, така че няма настолно приложение, което да блокира, да изразходва ресурси или да изисква плащане на лиценз за всяко работно място

Защо управлението на EXCEL.EXE от услуга се проваля

COM автоматизацията управлява дистанционно настолна програма, а настолната програма тихо предполага три неща, които услуга на Windows не може да й предостави: зареден потребителски профил, интерактивна станция за прозорци (window station) и човек, който наблюдава екрана. Премахнете тези фактори и сривовете ще започнат да се случват във вид, който нито една машина на разработчик не може да възпроизведе. Диалог за възстановяване на файлове, грешка в добавка (add-in) или съобщение за активиране на лиценз се отварят на десктоп, който никой не може да види, и извикването на автоматизацията, което ги е задействало, никога не се връща. Извикващият процес в крайна сметка изтича и се прекратява; инстанцията на Excel обаче често оцелява като процес-сирак, който държи заключени файлове и проваля следващото изпълнение. Всеки човек, който е виждал как единадесет излишни процеса EXCEL.EXE се натрупват под сервизен акаунт, знае останалата част от тази история

Историята с мащабирането не е по-добра, дори когато нищо не се срива. Инстанцията на Excel е конвейер за една работна книга, всеки достъп до свойство заплаща цената на междупроцесния COM маршалинг (marshaling), а машината, изпълняваща кода, носи лиценз за Office, чиито условия изключват точно тази употреба. Повечето екипи се сблъскват с тези ограничения срив по срив, което в общи линии обяснява как "пенсионирането на COM слоя" попада в плановете за развитие

Преди да започне това пренаписване, решете един въпрос относно обхвата, защото той определя каква част от работата е съществена. COM кодът почти никога не се ограничава само до задаване на стойности на клетки. Той извиква Workbook.SaveAs с константи за формат, форсира преизчисляване, задава настройки за печат, понякога посяга към клипборда. Прегледайте стария код и запишете кои от тези поведения действително влияят на крайния изход, тъй като всяко от тях се вписва в различен ъгъл на оригиналната библиотека, а някои (като взаимодействието с клипборда) нямат смисъл от страна на сървъра и трябва да бъдат премахнати, вместо пренесени

Два оригинални двигателя, два модела на собственост

HotXLS заменя процеса Excel с две директни имплементации на форматите. Двигател за BIFF8 потоци от записи (TXLSWorkbook, модул lxHandle) се занимава с .xls. Писател на OOXML пакети (TXLSXWorkbook, модул lxHandleX) генерира .xlsx, съответстващ на ECMA-376 / ISO/IEC 29500. Няма нищо за регистриране или инсталиране на сървъра и можете да държите отворени толкова работни книги едновременно, колкото паметта позволява

Това, което обърква хората в началото, е, че двете фасади управляват паметта си по различен начин и тази разлика е тиха, докато не се срине:

var
  Book: IXLSWorkbook;          // interface reference: released automatically
  Sheet: IXLSWorksheet;
  BookX: TXLSXWorkbook;        // plain object: you free it
  SheetX: TXLSXWorksheet;
begin
  // BIFF8 .xls output - no Free; the interface refcount owns it
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.Name := 'Report';
  Sheet.Cells.Item[1, 1].Value := 'Generated without Excel';
  Book.SaveAs('report.xls');

  // OOXML .xlsx output - explicit lifetime
  BookX := TXLSXWorkbook.Create;
  try
    SheetX := BookX.Sheets.Add('Report');
    SheetX.Cells[1, 1].Value := 'Generated without Excel';
    BookX.SaveAs('report.xlsx');
  finally
    BookX.Free;
  end;
end;

Фасадата XLS е с отчитане на препратките (reference-counted) през интерфейса IXLSWorkbook. Декларирайте променливата като тип интерфейс и никога не извиквайте Free за нея; ако държите същия обект в променлива за обикновен обект и го освободите сами, броячът на препратките ще го освободи втори път. Фасадата XLSX е обикновен обект, който изисква обикновен блок try..finally. Адресирането на клетки е 1-базирано и от двете страни, което е единственото нещо, по което двете са съгласни. Колекциите от листове обаче се различават: Entries от страна на XLS е 1-базирана, докато индексът на XLSX Items е 0-базиран, и тази разлика с едно се компилира чисто, независимо как сгрешите, и се проявява едва по време на изпълнение

Записване на работна книга директно в HTTP отговор

Експортирането от страна на сървъра обикновено няма причина да докосва диска. Временните файлове изискват политика за почистване, сблъскват се при едновременни заявки и оставят клиентски данни на дискове, които никой не се е сетил да провери. И двете фасади приемат TStream чрез своите претоварвания на SaveAs, така че работната книга може да отиде директно в отговора:

Mem := TMemoryStream.Create;
Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Data');
  Sheet.Cells[1, 1].Value := 'Generated ' + DateTimeToStr(Now);
  Book.SaveAs(Mem);          // writes from the CURRENT stream position
  Mem.Position := 0;         // rewind before handing the stream over
  Response.ContentType :=
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
  Response.ContentStream := Mem;   // the framework now owns Mem
finally
  Book.Free;
end;

Превъртането назад е редът, който заслужава своя коментар. SaveAs(Stream) пише от текущата позиция на потока и никога не го превърта обратно до нула след това. Забравете Mem.Position := 0 и клиентът ще получи изтегляне от нула байта, или Excel ще обяви файла за повреден. Това е най-често срещаният бъг в кода за работни книги, насочен към уеб, и е най-жесток, тъй като преминава безпроблемно през всеки единичен тест, който просто твърди, че потокът има дължина, различна от нула

Една рутина за изграждане на работни книги достига до всеки друг формат за доставка без преструктуриране. SaveAsCSV отговаря на заявката "просто ми дай суровите данни", SaveAsHTML се занимава с "пусни го в портална страница", SaveAsRTF захранва конвейери за документи, а SaveAsODS покрива мандат за OpenDocument - всички те както с файлови, така и с поточни претоварвания. Една-единствена рутина за експортиране плюс параметър за формат заменя това, което обикновено се състоеше от четири отделни COM макроса. Опциите TXLSXHtmlExportOptions на HTML експортьора съдържат заглавие, CSS клас и превключвател за фрагмент или пълен документ, което предпазва портала от това да се занимава с редакции на генерирания HTML чрез регулярни изрази

Стойности на формули без Excel процес за тяхното изчисляване

При COM автоматизацията Excel преизчисляваше всичко безплатно, а премахването на COM тихо отнема тази възможност. SaveAs съхранява формулите как текст, без да ги оценява; числата се появяват едва когато Excel отвори файла и го преизчисли - поведение, което XLS фасадата ви позволява да настроите чрез RecalcOnSave и CalculationMode. За файл, изпратен на човек, това е абсолютно правилно. То обаче е грешно за услуга, която трябва да потвърди общата сума преди изпращането, и грешно за CSV експорта, който записва текста на формулата, а не нейния резултат. И в двата случая изчислението трябва да се извърши на сървъра с вградения двигател:

SheetX.Cells[1, 1].Value := 1200;
SheetX.Cells[2, 1].Value := 950;
SheetX.Cells[3, 1].Formula := 'SUM(A1:A2)';   // XLSX facade: no '=' prefix
Total := BookX.Calculate('SUM(A1:A2)');       // evaluate on the server, now
if Total <> 2150 then
  raise Exception.Create('reconciliation failed before delivery');

Конвенцията на фасадата отново създава пречки тук. Страната XLSX присвоява изрази чрез Cell.Formula без знак за равенство; страната XLS ги записва чрез Cell.Value с водещ символ '='. Пренесете кода от едното към другото непроменен и грешната конвенция ще запише текстов низ, който само прилича на формула, без да се появи грешка, която да го сигнализира. Когато формулите в работната книга трябва да достигнат до вашата собствена бизнес логика, обратната функция OnUserFunction позволява на двигателя да прехвърли непознати имена на функции към Delphi код по време на оценяването. Това е оригиналният заместител на UDF добавките, които често се крият в самите електронни таблици, около които се е развила една система за COM автоматизация

Тънкости при разгръщането, които се проявяват само на сървъра

Няколко детайла решават дали внедряването ще бъде чисто или озадачаващо, и първият е графът на зависимостите на модулите. Drag-and-drop експортьорът на масиви от данни TDataToXLS привлича VCL модулите Forms, Controls и Dialogs. Безвредно в десктоп инструмент; в конзолна услуга обаче това влачи целия VCL след себе си. Основните модули lxHandle и lxHandleX използват само Windows, Classes, SysUtils и Variants, така че за чиста услуга е по-добре да напише свой собствен цикъл за данни спрямо основния API, вместо да внася компонента за удобство

След това идват нишките. Инстанциите на работни книги не са нишково безопасни (thread-safe), но също така не споделят глобално състояние, така че моделът, който се мащабира, е най-простият: по един обект за работна книга на задача или на работен поток (worker thread). Това осигурява паралелно генериране на отчети, което една единствена споделена инстанция на Excel никога не би могла да направи. Манипулатор на заявки, който създава, попълва, записва и освобождава своя собствена работна книга, не се нуждае от никакви заключвания (locks), а мащабът на срива се свива от "споделената инстанция на Excel е блокирала за всички" до "тази конкретна заявка предизвика изключение", с което вашето съществуващо управление на грешки вече знае как да се справи

Изборът на целевия формат е последното от тях. TXLSWorkbook.SaveAs записва BIFF (xlExcel97) по подразбиране, а вкарването на XLS съдържание в .xlsx минава през моста SaveXLSWorkbookAsXLSX с намалена точност. Изберете фасадата според формата, който възнамерявате да доставите още по време на проектирането, вместо да изграждате в едната и да конвертирате в края на конвейера

За данните в клетките на типичен проект за подмяна, моделите за експорт от база данни към работна книга обхващат както компонента, така и ръчно написания цикъл, а когато редовете достигнат шестцифрени стойности, техниките за производителност на големи работни книги стават разликата между минути и секунди. Отчетите, изградени по поддържани от дизайнери оформления, са описани в ръководството за генериране на отчети по шаблони

HotXLS се разпространява като Object Pascal изходен код за Delphi и C++Builder; изданията, лицензирането и пълният справочник за API се намират на продуктовата страница на HotXLS Component