Having solved the multiple-target problem, it is easy to think of the multiple-source problem. In practice, the calculation may need to use multiple source data areas in Excel. However, the clipboard() function only holds the most-recently copied data area. How to solve this problem?

We can copy the clipboard content directly to a cell in the cellset.

The following is an example of “querying the orders that meet the specified conditions”.

There are two sheets in Excel, order details and employee list, where the order details table is as follows:

The employee list is as follows:

Calculation objective: query the order data in the last days, or the order data belonging to the department list depts. The required columns include the OrderID, OrderDate and Amount in the order details, and the Name and Dept in the employee list. In these conditions, days is an external parameter, and you can enter a different value for each execution, for example, entering 30 means querying the order data in the last 30 days; depts is also an external parameter, such as [“Markeding”,“Finance”]. This calculation objective involves dynamic query and multi-key-value query. Such queries are hard to be achieved in Excel, but is easy to get it done with SPL.

First, select the data area of "Order Details” sheet in Excel, and copy and paste it to the cell A1 in SPL cellset together with the column names; Likewise, copy the data area of “Employee list” sheet to cell A2 in SPL cellset. See the figure below:

Note: the paste action should be performed when the cell A1 is in the editing state, otherwise the content of the clipboard will be filled into a large range of cells, as shown below:

This figure shows a wrong result. Since it occupies a too large area, the code layout will be affected.

If the operation is correct, only a small part of data is displayed on the appearance of cell A1/A2, and the complete data will be presented only when clicking A1/A2.This is the unique feature of cell-style code, which is suitable for copying a large range of data without affecting reading and code layout.

After copying the two source data areas, edit the script, during which we can run the script many times and perform the copy and paste actions manually. The final code is as follows:

After the calculation is finished, we can use the “copy data” button to copy the calculation result in A5 back to Excel. It should be noted that the data in the cell A1/A2 should be cleared when saving the code, otherwise all source data will be saved.

esProc Desktop and Excel Processing
3.3 Multiple result data areas
4.1 Merge by row - same name and number of columns