HotXLS: database export to spreadsheet reports in Delphi

Att omvandla ett frågeresultat till en Excel-rapport är egentligen tre problem i en och samma kappa. Varje Delphi-fälttyp måste hamna i en cell som rätt Excel-typ, rubrikraden måste läsas som en rapport och inte som en schemadump, och tal, datum och belopp måste bära format som överlever resan. Hoppa över något av detta och filen öppnas ändå, ser ändå trovärdig ut, och misslyckas ändå i samma stund som en ekonomianvändare markerar en kolumn och väntar på en summa som aldrig dyker upp. Värdena skrevs som text, Excel behandlar dem som etiketter, och inget undantag utlöstes någonsin för att varna dig

HotXLS är ett nativt Object Pascal-kalkylbibliotek som skriver XLS- och XLSX-filer direkt från Delphi och C++Builder, helt utan Excel-automation. Det erbjuder två vägar från ett TDataset till en arbetsbok: den insticksfärdiga komponenten TDataToXLS, och en handskriven loop mot arbetsboks-API:et. De är inte utbytbara mot varandra. Komponenten är en fullvärdig VCL-medborgare byggd ovanpå XLS-fasaden, så rätt val beror på var koden körs och vilket filformat mottagaren förväntar sig. Nedan följer båda vägarna, gränsen där komponenten slutar vara rätt verktyg, och hur du håller fälttyperna intakta oavsett vilken du väljer

Fälttyper är det verkliga exportkontraktet

Innan något API-anrop görs, bestäm hur varje Delphi-fälttyp ska hamna i en cell. En cell som tar emot en Delphi-sträng förblir en sträng. HotXLS gissar inte att '1,234.50' var tänkt som ett tal, och det ska den inte heller göra, eftersom lokalberoende omtolkning är precis hur ett tyskt decimalkomma blir en tusentalsavgränsare på en engelsk server. Det pålitliga mönstret är att tilldela via de typade accessorerna: AsFloat eller AsCurrency för numeriska fält, AsDateTime för datum så att cellen innehåller ett äkta Excel-datumserienummer i stället för en formaterad sträng, och AsString bara för fält som faktiskt är text

Hantering av null förtjänar ett uttryckligt beslut i stället för ett standardval. Att konvertera ett fältvärde med VarToStr gör SQL NULL till en tom sträng, vilket är en textcell, medan man genom att hoppa över tilldelningen lämnar cellen genuint tom, vilket är vad AVERAGE, COUNT och pivottabellkonsumenter förväntar sig. För beloppskolumner, bestäm innan loopen skrivs om NULL betyder noll eller okänt. De två ser identiska ut så fort någon formaterar kolumnen, och skillnaden förändrar varje aggregat som beräknas i efterföljande steg

Komponentvägen: TDataToXLS i VCL-applikationer

För en klassisk VCL-applikation med en fråga som redan är kopplad till en datamodul är TDataToXLS vägen som klaras med ett enda anrop. Den går igenom vilken TDataset-ättling som helst, oavsett om det är FireDAC, ADO, IBX eller något annat som implementerar det abstrakta dataset-gränssnittet, och producerar ett stiliserat kalkylblad med rubriktexter, typsnitt, ramlinjer, valfria gruppsummeringar och automatisk bladdelning för stora resultatmängder

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // valfri TDataset-ättling
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // rubriktexter, inte råa kolumnnamn
    Exporter.GroupFields.Add('CustomerID');   // summeringsblock per kund
    Exporter.RowsPerSheet := 50000;           // håll dig under BIFF8:s radtak
    Exporter.VisibleFieldsOnly := True;             // respektera Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Två egenskaper bär den mesta produktionstyngden här. HeaderSource := hsDisplayLabel skriver varje fälts DisplayLabel i stället för det råa SQL-kolumnnamnet, så att arbetsboken säger "Customer Name" i stället för CUST_NM. RowsPerSheet finns eftersom komponenten skriver BIFF8, vars rutnät stannar vid 65 536 rader gånger 256 kolumner; att sätta den till 50 000 delar upp en stor resultatmängd över flera blad innan formattaket beskär den. Utseendet hanteras av egenskaperna HeaderFont, DetailFont, GroupColor och kantlinjestil, och mängden DisableFormat slår av hela formateringskategorier när mottagaren vill ha rena celler. För allt skräddarsytt ger händelserna AfterCell och AfterRow dig det precis skrivna intervallet för efterbearbetning

Var komponenten tar slut

Tre begränsningar är inbyggda i TDataToXLS, och att känna till dem i förväg undviker en besvärlig omkonstruktion två sprintar senare

• Det är en fullvärdig VCL-komponent. Dess enhet drar in Forms, Controls och Dialogs, så att länka in den i ett konsoljobb eller en Windows-tjänst drar med sig hela VCL i binären. Kärnenheterna för arbetsboken har inget sådant beroende. De behöver bara Windows, Classes, SysUtils och Variants, vilket är anledningen till att serversidig kod i stället bör använda loopen som visas nedan
• Den är byggd på XLS-fasaden. Komponenten fyller i ett IXLSWorkbook och skriver .xls (BIFF8). Det finns ingen egenskap som växlar den till OOXML-utdata
• Dess händelser talar XLS-dialekten. Parametern Cell: IXLSRange i AfterCell tillhör XLS-objektmodellen, så anpassningar per cell som skrivs där är XLS-stilkod även om filen konverteras till .xlsx efteråt

Producera .xlsx från komponentens utdata

När mottagaren insisterar på .xlsx men exportlogiken redan finns i TDataToXLS, konverterar brofunktionen i enheten lxXlsxExport den ifyllda arbetsboken med ett enda anrop:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// komponenten exponerar det IXLSWorkbook den fyllde i
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Betrakta bron som en bärare av tabelldata, inte som en fullständigt fidelitetstrogen konverterare. Den kopierar värden, formler, talformat, fyllnadsfärger, typsnittsattribut, kolumnbredder och visningsinställningar. Den kopierar medvetet inte ramlinjer, sammanslagna områden, kommentarer, diagram eller villkorlig formatering. För ett platt rutnät med rubrik plus rader är det precis tillräckligt. För en stiliserad rapport är det inte det, och den ärliga lösningen är att generera XLSX-filen direkt i stället för att lappa den konverterade filen

Den handskrivna loopen för tjänster och batchjobb

Serversidig kod bör rikta sig direkt mot TXLSXWorkbook. Notera livstidsskillnaden mellan de två fasaderna innan du kopierar något exempel. Det XLS-sidiga TXLSWorkbook hålls via ett referensräknat gränssnitt och får inte frigöras manuellt, medan TXLSXWorkbook är en vanlig klass som kräver try..finally Free. Att blanda de två konventionerna är ett pålitligt sätt att skapa antingen en läcka eller en dubbelfrigöring

procedure ExportOrders(Q: TDataSet; const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 'Order No';
    Sheet.Cells[1, 2].Value := 'Customer';
    Sheet.Cells[1, 3].Value := 'Ordered';
    Sheet.Cells[1, 4].Value := 'Amount';

    Row := 2;
    Q.First;
    while not Q.Eof do
    begin
      Sheet.Cells[Row, 1].Value := Q.FieldByName('OrderNo').AsInteger;
      Sheet.Cells[Row, 2].Value := Q.FieldByName('Customer').AsString;
      if not Q.FieldByName('Ordered').IsNull then
        Sheet.Cells[Row, 3].Value := Q.FieldByName('Ordered').AsDateTime;
      Sheet.Cells[Row, 4].Value := Q.FieldByName('Amount').AsFloat;
      Inc(Row);
      Q.Next;
    end;

    Book.StreamingWrite := True;  // strömma kalkylbladets XML direkt in i zip-arkivet
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

Raderna som spelar roll är de typade tilldelningarna och IsNull-vakten. Datum anländer som datumserienummer, belopp anländer som double-värden, och NULL-orderdatum förblir genuint tomma i stället för att bli tomma strängar. StreamingWrite := True ändrar bara sparvägen: kalkylbladets XML strömmas direkt in i zip-behållaren i stället för att först sättas ihop som en enda stor sträng, vilket plattar ut minnestoppen vid SaveAs-tillfället för sexsiffriga radantal. Varje sparmetod har också en TStream-överlagring, så att arbetsboken kan gå direkt in i ett HTTP-svar utan att röra disken. Artikeln om strömmande skrivning och batchjobb går igenom det driftsättningsmönstret, och artikeln om prestanda för stora arbetsböcker tar upp vad man ska göra när radantalet fortsätter att öka

Den här loopen är också vägen som skalar över trådar. Båda motorerna är nativa Object Pascal-skrivare, BIFF8-postströmmar på ena sidan och OOXML-zip plus XML på den andra, så ingen del av en export rör COM-automation eller kräver en Excel-licens på servern. Det du får är parallellism utan en flaskhals av en enda instans, förutsatt att varje tråd bygger sin egen arbetsbok. Arbetsboksobjekten är inte trådsäkra för delad användning, så regeln är en instans per export, aldrig en delad som skyddas med ett lås

En gräns är värd att känna till innan du designar kring den. XLSX-rutnätet stannar vid 1 048 576 rader gånger 16 384 kolumner, så den bladdelning som RowsPerSheet hanterar på XLS-sidan behövs sällan här. En arbetsbok med en miljon rader är också sällan vad en mänsklig mottagare vill ha. När resultatmängden verkligen är så stor är en avgränsad fil vanligtvis det bättre kontraktet, och artikeln om CSV- och TSV-export tar upp avgränsare, BOM-beteende och den formelutvärderingsförbehåll som gäller där

Att välja en utgångspunkt

Om exporten finns i ett VCL-skrivbordsverktyg och .xls-utdata är godtagbart, börja med TDataToXLS och dess stöd för gruppering. Det är den minsta mängden kod, och bron via SaveXLSWorkbookAsXLSX finns där när någon senare ber om .xlsx, så länge du accepterar de redan beskrivna fidelitetsgränserna. Om koden körs obevakat, eller mottagaren kräver .xlsx från början, skriv loopen. Båda vägarna levereras med fungerande demoprojekt och är en del av paketet HotXLS Delphi Component