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.BtnTemplateWithFormulaClick(Sender: TObject); var xExportTemplate: TOExportTemplate; I: Integer; xRow: TDBRow; xCurrentProcessRows: TOExportTemplateProcessRows; xPriceCell, xAmountCell, xSubTotalCell: TExportCell; xSubTotalFormula: String; xWholeTotalFormula: String; 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 xWholeTotalFormula := ''; 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); if ProcessRows.Count <> 1 then raise Exception.Create('Wrong template!'); xCurrentProcessRows := ProcessRows[0]; //REPLACE SIMPLE TAGS xCurrentProcessRows.ReplaceSqlValue('$ROW_NO', IntToStr(xRow.Index), TExportCellString); xCurrentProcessRows.ReplaceSqlValue('$ROW_NAME', xRow.Name, TExportCellString); xCurrentProcessRows.ReplaceSqlValue('$ROW_DESCRIPTION', xRow.Description, TExportCellString); //FIND CELLS TO USE THEM IN FORMULAS xPriceCell := xCurrentProcessRows.FindSingleCellBySqlValue('$ROW_PRICE'); xAmountCell := xCurrentProcessRows.FindSingleCellBySqlValue('$ROW_AMOUNT'); xSubTotalCell := xCurrentProcessRows.FindSingleCellBySqlValue('$ROW_TOTAL'); if not Assigned(xPriceCell) or not Assigned(xAmountCell) or not Assigned(xSubTotalCell) then raise Exception.Create('Wrong template!'); //GET FORMULAS FOR TOTAL SUMS xSubTotalFormula := '='+xPriceCell.RangeNoSpan+'*'+xAmountCell.RangeWithSpan; if xWholeTotalFormula <> '' then xWholeTotalFormula := xWholeTotalFormula + '+'; xWholeTotalFormula := xWholeTotalFormula + xSubTotalCell.RangeNoSpan; //REPLACE PRICE TAGS xCurrentProcessRows.ReplaceSqlValue('$ROW_PRICE', FloatToSqlStr(xRow.Price), TExportCellCurrency); xCurrentProcessRows.ReplaceSqlValue('$ROW_AMOUNT', FloatToSqlStr(xRow.Amount), TExportCellNumber); xCurrentProcessRows.ReplaceFormula('$ROW_TOTAL', xSubTotalFormula, TExportCellCurrency); end; finally FinishProcessingRows; end; //END PROCESSING ROWS //NOW ASSIGN TOTAL ReplaceFormula('$TOTAL', xWholeTotalFormula, TExportCellCurrency); //SAVE DOCUMENT FinalizeDocument; Document.SaveToFileWithDialog('', '', True); finally Free; end; end;