This ariticle is out of date, the new one is User Guide to esProc Excel Add-in

esProc supports the Excel add-in, 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 64-bit windows, the bits of the three software must be the same, that is, all 32-bits or 64-bits.

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 64-bit JDK is:

\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\javasoft\Java\Runtime   Enviroment

esProc

To install esProc normally, you can choose to install the built-in JDK or point to the JDK already installed on the machine.

 

Excel

Open the dialog box in Excel: file->Option->add-ins

Click the Go... button in the dialog box and navigate to esProc add-in: [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 comma-separated 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, pull-down 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 fixed-coordinate 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, odd-numbered rows are unique letters, and even-numbered 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 multi-dimensional sequence into a one-dimensional sequence, and concat concatenates the sequence into a text.

If the area parameter is multi-column, 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 one-dimensional 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 single-column area, the data must be sorted into an array (sequence) composed of simple types; to output as a multi-column 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 non-empty member in the sequence, and pselect@z() can calculate the position of the first non-empty 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 multi-column, but the input parameter is also multi-column. The difference from the output is that the data type of the input parameter is unified as a sequence regardless of single-column or multi-column.

 

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 add-in 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 add-in 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