For certain computations, the SPL expression may be very complex, even more than one line, which makes it inconvenient to edit directly in Excel. To solve this problem, esProc provides the Excel Copy/Paste feature to copy the edited code to Excel.

2.3.1 Generate complex SPL script with esProc

We have an Excel file as follows:

Now we want to add a row at the bottom to calculate the average of the sampling data for each hour in January. We would write one expression that can calculate the average of every cell in the new row without copying the expression to each cell.

The calculation result is as follows:

In this example, it needs to take B2:Y33 as the parameter. Open the esProc, and define the parameter as follows:

Note: The parameter name here must be B2:Y33, which is the same as the parameter passed from Excel.

First, edit the script:

	A
1	=E(‘B2:Y33’)
2	=to(24).(“avg(’”+string(~)+“‘):’”+string~)+“’”)
3	=A1.groups(;${A2.concat@c()})
4	return A3(1).array()

A1: Convert the passed-in two-dimensional array parameter ‘B2:Y33’ to a table sequence, with the first row as column name; The parameter name needs to be enclosed in single quotation marks to distinguish it from the cell of esProc.

A2: Dynamically piece together an aggregate expression for 24 columns.

A3: Concatenate the aggregate expression into the groups function to calculate the average of every column.

A4: Convert the result to a single-level sequence and return.

Then, Click Edit > Copy > Excel Copy, at this point, this code can be copied to the clipboard.

Now, go back to the Excel file, select cell B34, and press Ctrl-V to paste the code in, as shown below:

This code will return a one-dimensional array, press Ctrl-Enter to fill the returned array values rightward into every cell of the row in sequence. The final result is as follows:

Notes:

1. When the returned result is a one-dimensional array, the result set will be filled rightward into the cells in sequence after pressing Ctrl-Enter.

2. If the parameter passed to SPL script is a range of cells (a single row, a single column, multi-row and multi-column), it will automatically form a two-level sequence. In this case, it can be converted to a table sequence in the SPL script using E() function;

3. The rule for parameter is the same as that for a one-line code. That is, use ?1 to represent the first parameter, ?2 to represent the second, and so on.

4. In the SPL script, the Excel cell (or a range of cells), as parameter, is enclosed in single quotation marks.

2.3.2 Back to esProc to modify script

In the above example, after the code is copied to Excel, we might close the esProc and didn’t save the code. In this case, if the code needs to be modified, it is inconvenient to modify it directly in Excel. To solve this problem, we can use the Excel Paste feature of esProc to copy SPL script back to esProc for modifying. The specific method is as follows:

Select the whole SPL code in Excel, and press Ctrl-C to copy it to the clipboard. Then open esProc and click Edit > Paste > Excel Paste, at his point, the original code can be restored to esProc, as shown below:

2.3.3 Extremely long SPL script

We have an Employee.xlsx file as follows (the file must be stored under esProc main path):

We want to query the file to display the following columns only in the query result: EID, NAME, GENDER, DEPT, SALARY, BIRTHDAY, HIREDATE and STATE, and the display of column SURNAME is not required. The query condition is shown as below:

In this example, the parameters to be passed to SPL script include B1, B2, D1, D2, F1 and F2.

First, Open the esProc, and define the following parameters:

Then write the script as follows:

Next, click Edit > Copy > Excel Copy to copy the code to clipboard.

Now open the Excel file, select cell A4, and press Ctrl-V to paste the code in, as shown below:

Due to the restriction on the length of string in Excel expression, extremely-long code will be automatically split into multiple parts when it is copied to Excel, with each part not longer than 240 characters. If a part of code ends with a slash \, it indicates that the code has not ended, and the next parameter is still the code.

Finally, press Ctrl-Enter to get the final result:

2.3.4 Invoke SPL script directly

For longer SPL code, you can write the SPL script to a file and invoke it directly in the spl() function.

For example, in the above example, we save the script file as Example15.splx under esProc main path, and then write the following expression in cell A4 of Excel:

As you can see from the expression that the syntax for directly invoking the script file is:

=spl("script file name (?n……)",parameter n….)

The number of question marks to be written in parentheses depends on that of parameters in the script. The corresponding relationship rule with passed-in parameters is the same as that for directly writing code, that is, ?1 represents the first parameter, ?2 represents the second one, and so on.

Now press Ctrl-Enter to get the final result:

---

esProc Desktop and Excel Processing
2.2 Using spl() function
3.1 Basic usage