Group By Excel Spreadsheet

$MSExcelGroup function can be used to create a value group based on data from Microsoft Excel file.
The function has following parameters:

  1. Group number. Positive integer.
  2. File Name*. String, a path to the file.
  3. Sheet Name. String, identifier quotation not required.
  4. Column List. List of strings, comma delimited. Quotation not required.
  5. (optional) Use data from the table sequentially. 0 or 1. Optional, default is 0 (false). The first row will be used after the last of necessary.

* - the following macros can be used as a part of the file name: %APPDATA%, %DOCUMENTS%, %USERPROFILE%, %ALLUSERSPROFILE%

Example: $MSExcelGroup(1,c:\data files\countries.xls,Sheet1,Name,Country) - specifies sheet 'Sheet1' in the 'c:\data files\countries.xls' file as data source and uses the first column 'Name'.
$Group(1,2) - refers to the second column 'Country'.

Limitation: Java and Web editions of the pattern engine do not support this function.