Use esProc addin in Excel
This ariticle is out of date, the new one is User Guide to esProc Excel Addin
esProc supports the Excel addin, which can call esProc expressions in cells or call esProc script files in VBA.
I. Configuration
JDK
JDK, esProc, and Excel should be configured under the windows operating system. For the most common 64bit windows, the bits of the three software must be the same, that is, all 32bits or 64bits.
esProc comes with a JDK. If the machine does not have a JDK, it is recommended to skip this step and install it in the next step. If the JDK of this machine can be uninstalled, it is recommended to use the tools provided by JAVA or Windows to uninstall normally and then go to the next step.
If the machine has installed JDK (version 1.8 or above), and it is not suitable for uninstalling for important purposes, you should ensure that the registry information is correct. The location of the 64bit JDK is:
\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\javasoft\Java\Runtime Enviroment 
esProc
To install esProc normally, you can choose to install the builtin JDK or point to the JDK already installed on the machine.
Excel
Open the dialog box in Excel: file>Option>addins
Click the Go... button in the dialog box and navigate to esProc addin: [esProc installation directory]\bin\ExcelRaq.xll.
Restart Excel for the configuration to take effect.
II. Basic usage
With the function named esproc, you can call esProc expressions in Excel cells. For example, column A is a commaseparated number, and column B is a single number:
A 
B 

1 
1,2,3,4,5,6 
3 
2 
7,45,31,12,10 
4 
3 
9,7,5,3,2 
5 
Now we need to calculate in each row of column C: which number is the smallest number in column A that is larger than column B. The result should be as follows:
A 
B 
C 

1 
1,2,3,4,5,6 
3 
4 
2 
7,45,31,12,10 
4 
7 
3 
9,7,5,3,2 
5 
7 
To calculate this problem, write an expression in C1:
=esproc("=?.split@cp().sort().select@1(~>?)",A1,B1)
After pressing Enter, pulldown or copy cell C1 to C3 to complete the calculation.
The function split in the expression splits the text into an array (sequence) according to the separator. @c means split by comma, and @p means automatic data type conversion. The function sort implements sorting, select implements conditional query, and @1 means to return the first one.
Multiple Parameters The question mark in the expression is a parameter placeholder. There are several question marks for several parameters. This is a feature of esProc expressions.
Parameters follow the Excel specification. It can be a parameter that automatically changes coordinates when copying, such as B1, or a fixedcoordinate parameter such as $B$1, as well as the parameters of associated sheets and files. EsProc also supports area parameters, such as A1:D5. You can also select and generate area parameters through the mouse.
III. An area as a parameter
The format of the area parameters follows the Excel specification, which is essentially the sequence data type of esProc. For example, in the alphanumeric table below, oddnumbered rows are unique letters, and evennumbered rows are numbers:
A 
B 
C 
D 
E 

1 
A 
H 
M 
B 
T 
2 
39 
2 
14 
9 
20 
3 
F 
W 
P 
L 
C 
4 
6 
35 
11 
4 
22 
5 
Y 
D 
V 
E 
U 
6 
6 
5 
7 
18 
27 
Now we need to splice the letters of the odd rows in the order of horizontal first, then vertical, namely: AHMBTFWPLCYDVEU.
To calculate this question, just enter the expression in F1:
=esproc("=?.step(2,1).conj().concat()",A1:E6)
Step (2,1) in the expression takes the members (rows) at odd positions in the sequence, conj can merge the multidimensional sequence into a onedimensional sequence, and concat concatenates the sequence into a text.
If the area parameter is multicolumn, it will be parsed as a sequence (horizontal) composed of a sequence (horizontal). You can use ?(N) to access the Nth row and ?(N)(M) to access the Mth column of the Nth row. If the area parameter is a single column, it is parsed as a onedimensional sequence, and the Nth member can be accessed with ?(N).
IV. Output single row area
If the calculation result is a single column area, you should select all the cells to be output, then enter the formula in the formula bar, and finally press ctrl+shift+enter.
For example, according to the alphanumeric table, the letters in the odd rows are displayed in column H in the order of horizontal first, then vertical, as shown in the following figure:
A 
H 
M 
B 
T 
F 
W 
P 
L 
C 
Y 
D 
V 
E 
U 
To calculate this question, formula 1: =esproc("=?.step(2,1).conj()",A1:E6) But before outputting, we need to know how many cells will be filled, either by oral calculation or formula 2: =esproc("=?.step(2,1).conj().len()",A1:E6)
The calculation result of formula 2 is 15, so you should select H1:H15 and then enter formula 1. Finally, press ctrl+shift+enter to get the calculation result.
V. Output multiple columns
To output as a singlecolumn area, the data must be sorted into an array (sequence) composed of simple types; to output as a multicolumn area, the data must be sorted into a structured data (table sequence) composed of records. The data types and generation methods of the two are different.
For example, the first row of the D1:I6 area is the column name, some cells in other rows are empty, some cells have data, and the cells with data must be continuous. As follows:
A 
B 
C 
D 
E 
F 
G 
H 
I 

1 
4.5.2020 
5.5.2020 
6.5.2020 
7.5.2020 
8.5.2020 
9.5.2020 

2 
Data 
data 

3 
Data 
data 
Data 
Data 
data 

4 
data 
Data 
Data 
data 
data 

5 
Data 
data 
Data 
Data 
data 
data 

6 
Data 
Data 
data 
data 
Now we need to calculate the column name of the starting column and the ending column of the cells with continuous data in column A and column B, respectively. As follows:
A 
B 
C 
D 
E 
F 
G 
H 
I 

1 
start date 
end date 
4.5.2020 
5.5.2020 
6.5.2020 
7.5.2020 
8.5.2020 
9.5.2020 

2 
4.5.2020 
5.5.2020 
Data 
data 

3 
4.5.2020 
8.5.2020 
Data 
data 
Data 
data 
data 

4 
5.5.2020 
9.5.2020 
data 
Data 
data 
data 
data 

5 
4.5.2020 
9.5.2020 
Data 
data 
Data 
Data 
data 
data 

6 
6.5.2020 
9.5.2020 
Data 
Data 
data 
data 
To calculate this question, just select A1:B6 and enter the expression:
=esproc("=?.new(~.pselect(~):start,~.pselect@z(~):end).new(?2(start):'start date',?2(end):'end date')",D2:I6,D1:I1)
The esProc function pselect can calculate the position of the first nonempty member in the sequence, and pselect@z() can calculate the position of the first nonempty member in the reverse order. The function new can generate a new table sequence.
There are two places in the expression? 2, which means the second parameter, D1:I1. This form of expression is convenient for referencing the same parameter multiple times. Similarly, the first? It is written as ?1 is also correct. Finally, press ctrl+shift+enter to get the calculation result.
Note: If you don't want to output the column header, you can use the esprocT function. In this example, not only the output result is multicolumn, but the input parameter is also multicolumn. The difference from the output is that the data type of the input parameter is unified as a sequence regardless of singlecolumn or multicolumn.
VI. Auto output area
It is a cumbersome operation to manually calculate the location first and then output data to the area. To automate this process, you can use the esProc addin macro file esproc_template.xla. The original location of the file is [esProc installation directory]\bin.
The following is an example of calculating the starting and ending column names.
1. Copy the esProc addin loading macro file from the original location to [Excel installation directory]\XLSTART.
2. Restart the Excel file and insert a blank line in line 1, as follows:
A 
B 
C 
D 
E 
F 
G 
H 
I 

1 

2 
4.5.2020 
5.5.2020 
6.5.2020 
7.5.2020 
8.5.2020 
9.5.2020 

3 
Data 
data 

4 
Data 
data 
Data 
Data 
data 

5 
data 
Data 
Data 
data 
data 

6 
Data 
data 
Data 
Data 
data 
data 

7 
Data 
Data 
data 
data 
3. Enter the expression in cell A1:
=esproc("=?.new(~.pselect(~):start,~.pselect@z(~):end).new(?2(start):'start date',?2(end):'end date')",D3:I7,D2:I2)
Note that the original data is moved down by one line, so the parameters need to be changed accordingly.
Then press ctrl+enter to automatically output the result from the next line, as follows:
A 
B 
C 
D 
E 
F 
G 
H 
I 

1 
start date 

2 
start date 
end date 
4.5.2020 
5.5.2020 
6.5.2020 
7.5.2020 
8.5.2020 
9.5.2020 

3 
4.5.2020 
5.5.2020 
Data 
data 

4 
4.5.2020 
8.5.2020 
Data 
data 
Data 
Data 
data 

5 
5.5.2020 
9.5.2020 
data 
Data 
Data 
data 
data 

6 
4.5.2020 
9.5.2020 
Data 
data 
Data 
Data 
data 
data 

7 
6.5.2020 
9.5.2020 
Data 
Data 
data 
data 
Generally, data and expressions may change in the future, so the first row can be temporarily hidden; if it does not change, the first row can also be deleted.
Note: This method is suitable for any output data, including single column and single value (single value is of little significance).
VII. Cell call script file
If the expression is complicated (for example, involving flow control statements), or if you want to reuse and debug, you should write the expression in an esProc script file and then call the script file in the Excel cell.
The following is an example of calculating the starting and ending column names.
1. Create a new esProc script file and add two parameters: data and title, which represent the input data and column names, respectively.
2. Write the following script.
A 

1 
=data.new(~.pselect(~):start,~.pselect@z(~):end) 
2 
=A1.new(title(start):'start date',title(end):'end date') 
3 
return A2 
3. Save the script as getColName.dfx and save it in the Main path directory of esProc. Refer to the picture below:
4. Enter the formula in Excel A1:B6: =esproc("getColName",D2:I6,D1:I1)
5. Finally, press ctrl+shift+enter to get the calculation result.
Note: When esProc is loaded, the macro file is also valid for the esProc script file in the cell.
VIII. VBA call script file
To call a script file in VBA, you can use the Application.Run function. The following is an example of calculating the starting column name and the ending column name.
Sub Test()：
rowStart = 1
colStart = 1
Application.ScreenUpdating = False
With Sheets(1)
ret = Application.Run("esproc", "getColName", Range("D2:I6"), Range("D1:I1"))
r = UBound(ret, 1)
c = UBound(ret, 2)
rowEnd = rowStart + r  1
colEnd = colStart + c  1
Range(Cells(rowStart, colStart), Cells(rowEnd, colEnd)) = ret
End With
End Sub
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL