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.

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:

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:

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