3.1 Basic usage


esProc provides the clipboard() function, which can exchange data with the clipboard.

Let’s take the example of “finding the top 3 students in each subject”.

The following is the source data in Excel, where column A contains the student names, and column B-D contain the scores in math, English and physics respectively.

1 name math english physics
2 lily 97 100 99
3 Joshua 100 99 100
4 Sarah 98 99 96
5 Bertram 94 95 85
6 Paula 91 88 91
7 Sophia 92 81 76
8 Ben 87 80 76
9 Ruth 92 91 87
10 Pag 95 87 87

Calculation objective: find the top 3 students in score in each subject, and append their names to the end of score column of the corresponding subject.

To achieve the objective, we need to use some features such as the row set TopN, join by sequence number. However, it’s hard to solve in Excel, we use SPL to make it easy.

Operation steps: i)select the source data area (A1:D10) in Excel; ii)press Ctrl+C to copy it to system clipboard; iii) open the esProc to write and execute the following script:

1 =clipboard().import@t()
2 =A1.top(-3;math).(name)
3 =A1.top(-3;english).(name)
4 =A1.top(-3;physics).(name)
5 =join@p(A2;A3;A4).export()
6 =clipboard(A5)

A1: Read the data from clipboard
A2: Get the names of top 3 students in math
A5: Join the names of these students to form a two-dimensional table and convert it to a string
A6: Write the string to clipboard

In this code, the clipboard()function is used in two ways. When it is called without parameter, the string in the clipboard will be taken out, such as the cell A1; When it is called using a variable or cell name as the parameter, the string will be written to the clipboard, such as the clipboard(…) in A6.

After executing the above script, select the cell B11 in Excel, and press Ctrl+V to copy the data from clipboard to B11-D13, as shown below:

10 Pag 95 87 87
11 Joshua Lily Joshua
12 Sarah Sarah Lily
13 lily Joshua Sarah

esProc Desktop and Excel Processing
2.3 Editing SPL code
3.2 Edit the script at will