XLSExport - export data to Excel file according template

It uses prepared template file in XLS or XLSX format with special TAGs and fill data into it instead of TAGs. Result is again Excel file in the same format but filled by data from variables, datasets etc.

See example of report template Employee_list_template1.xlsx and the result of export Employee_list_result1.xlsx

List of special tags which can be used inside of Excel template file:
(X) in tags specify one dataset, datasets are passed as parameter and are marked by character from A to Z

FIELD tags

FIELDS tags is used for inserting of field value from selected dataset (X) and current record

<<X.FieldName>>

Insert value of specified field in their default format, example: <<A.LastName>>

<<X.FieldName:Format>>

Insert value of specified field in specified format, Format has the same format string as in Delphi
example: <<A.Salary:# ##0.00>> <<A.OrderDate:mm/dd/yy>>

<<X.FieldName:Width:Format>>

Width is used for TXT template and must starts by < (left alignment) or > (right alignment), field value is extended by separators to specified width
Example: <<A.Name:>20>> - name extended to 20 characters and aligned to left; <<A.Phone:>12>> - phone extended to 12 characters and aligned to right

<<X.#FieldIndex>> <<X.#FieldIndex:Format>>

Insert value of field specified by their index, example: <<A.#2>>

Special format strings:

if Format is set to string LABEL then "Display Label" of the field is inserted (instead of field value)

VARIABLES tags:

<<VariableName>>

insert value of variable from list of variables which is passed to ExportToTemplate function
Example: <<UserName>>

SCAN tags

Scan tags are used for repeating some part of report for more dataset records.

<<ScanRow(X)>>

• defines one row which is repeated for each dataset record
• must be placed in first left column from used range

<<Scan(X);PageBreak>>

• defines start point of scan function
• must be placed in first left column from used range
• whole row is removed from the output
• PageBreak insert page break after each record
• all rows between Scan(X) and EndScan(X) is repeated for each dataset record

<<EndScan(X)>>

defines last point of scan function, whole row is removed from the output

Typical use:

Example 1 - simple loop:
<<Scan(B)>>
body of table - is repeated for each record
<<EndScan(B)>>


RECORD MOVE tags

This tags can be used for moving dataset to another record. Usually is not necessary to use it.

<<Next(X)>>

<<Prev(X)>>

<<First(X)>>

<<Last(X)>>

SPECIAL tags

<<SepList("sep",X.Field1,X.Field2,...)>>

insert list of values separated by text defined in first parameter "sep", it is usefull in creating separated list of any fields, because this function skips empty fields

<<FirstValue(X.Field1,X.Field2,...)>>

display first not empty value from list

<<ListAllFields>>

insert list of all dataset fields name, it is usefull only during creating new report

STRING functions and tags

String functions can be used in calc tags or as parameter in string routine

=StrLen[expression]

- evaluate expression and returns its length

=StrPos[expression;substring]

- evaluate expression and returns position of substring in expression

=StrPosCS[expression;substring]

- same as StrPos but case sensitive String tags can be used as common tags anywhere in report

<<Str_Part(expression,from,count)>> or <<Str_Part(expression,startstr,endstr)>>

- evaluate expression and return part of it - e.g.: A.email = name@company.com

• <<Str_Part(A.email,1,"@")>> returns "name"
• <<Str_Part(A.email,"@",255)>> returns "@company.com"
• <<Str_Part(A.email,"@",".")>> returns "@company"
• <<Str_Part(A.email,=StrPos[A.email,"@"]+1,".")>> returns "company"
• <<Str_Part(A.email,=StrLen[A.email]-3,4)>> returns ".com"

<<Str_PartCS(expression,from,count)>>

- same as Str_Part but case sensitive

<<Str_Replace(expression,oldsubstr,newsubstr)>>

- evaluate expression and replace all oldsubstr by newsubstr - e.g.: A.email = name@company.com ; A.LastName = "King"

• <<Str_Replace(A.email,"@","(at)")>> returns "name(at)company.com"
• <<Str_Replace(A.email,"name",A.Lastname)>> returns "King@company.com"
• <<Str_Replace(A.email,Str_Part(A.email,1,"@"),A.Firstname)>> returns "King@company.com"

<<Str_ReplaceCS(expression,oldsubstr,newsubstr)>>

- same as Str_Replace but case sensitive

<<Str_Upper(expression)>>

- evaluate expression and change to uppercase

<<Str_Lower(expression)>>

- evaluate expression and change to lowercase

<<Str_Proper(expression)>>

- evaluate expression and change to propercase (first letter uppercase)

<<Str_Trim(expression)>>

- evaluate expression and trim spaces