Use this approach for creating documents from templates. See code comments for more information.
Please download the XLSX template you have to use in this code snippet!
uses OExport, OExport_Vcl, OExport_VclForms, OExportTemplate, FormatSQL; type TDBRow = record //THIS IS JUST AN EXAMPLE - YOU WILL PROBABLY USE A DB QUERY Index: Integer; Name: String; Description: String; Price: Double; Amount: Integer; end; procedure GetRow(const I: Integer; out Row: TDBRow); //THIS IS JUST AN EXAMPLE - YOU WILL PROBABLY USE A DB QUERY begin Row.Index := I+1; case I of 0: begin Row.Name := 'Dell Computer'; Row.Description := 'Notebook 13 inch, Intel Core Duo'; Row.Price := 999; Row.Amount := 1; end; 1: begin Row.Name := 'Computer mouse'; Row.Description := '2 buttons, with wheel'; Row.Price := 15; Row.Amount := 2; end; 2: begin Row.Name := 'AVG Professional'; Row.Description := 'Antivirus programm, 1 user license'; Row.Price := 99; Row.Amount := 5; end; end; end; procedure TForm1.BtnEasyTemplateClick(Sender: TObject); var xExportTemplate: TOExportTemplate; I, L: Integer; xRow: TDBRow; xCurrentProcessRows: TOExportTemplateProcessRows; xWholeTotal: Double; begin xExportTemplate := TOExportTemplate.Create; with xExportTemplate do try //LOAD TEMPLATE if not Template.LoadFromFileWithDialog then Exit; LoadTemplate; //REPLACE GLOBAL DEFINITIONS ReplaceSqlValue('$CUSTOMERNAME', 'Ondrej Pokorny', TExportCellString); ReplaceSqlValue('$DATE', DateToSqlStr(Now()), TExportCellDate); ReplaceSqlValue('$INVOICENUMBER', '2013/125', TExportCellString); //PROCESS ROWS StartProcessingRows(['$ROW_NO', '$ROW_NAME', '$ROW_PRICE', '$ROW_AMOUNT', '$ROW_TOTAL', '$ROW_DESCRIPTION']); try xWholeTotal := 0; for I := 0 to 2 do begin ProcessRows.PrepareNextRow; //JUST EXAMPLE - YOU WILL PROBABLY USE A DATABASE QUERY TO FETCH A ROW HERE GetRow(I, xRow); for L := 0 to ProcessRows.Count -1 do begin xCurrentProcessRows := ProcessRows[L]; //REPLACE TAGS xCurrentProcessRows.ReplaceSqlValue('$ROW_NO', IntToStr(xRow.Index), TExportCellString); xCurrentProcessRows.ReplaceSqlValue('$ROW_NAME', xRow.Name, TExportCellString); xCurrentProcessRows.ReplaceSqlValue('$ROW_DESCRIPTION', xRow.Description, TExportCellString); xCurrentProcessRows.ReplaceSqlValue('$ROW_PRICE', FloatToSqlStr(xRow.Price), TExportCellCurrency); xCurrentProcessRows.ReplaceSqlValue('$ROW_AMOUNT', FloatToSqlStr(xRow.Amount), TExportCellNumber); xCurrentProcessRows.ReplaceSqlValue('$ROW_TOTAL', FloatToSqlStr(xRow.Price*xRow.Amount), TExportCellCurrency); xWholeTotal := xWholeTotal + (xRow.Price*xRow.Amount); end; end; finally FinishProcessingRows; end; //END PROCESSING ROWS //NOW ASSIGN TOTAL ReplaceSqlValue('$TOTAL', FloatToSqlStr(xWholeTotal), TExportCellCurrency); //SAVE DOCUMENT FinalizeDocument; Document.SaveToFileWithDialog('', '', True); finally Free; end; end;