Excel add-ins

Read(413) Label: excel add-in, excel grid,

1. Functionality

The Excel add-ins let users call the result set of executing an esProc dfx script from an Excel file and then populate data in the result set into the Excel grid.

 

2. Necessary files

ExcelRaq.xll: The add-in file, which is located in /esproc/bin under the installation directory.

 

EsprocXll.jar: The necessary jar, which is located in /esproc/lib under the installation directory.

  ExcelRaq.ini: The built-in configuration file located in /esproc/bin under the installation directory. Users need to first configure esProc path and JVM path in this file before they can use the Excel add-ins:

esproc.path="D:\Program Files\raqsoft\esProc"  // esProc installation directory

vm.location="D:\Program Files\raqsoft\common\jre\bin\server\jvm.dll"  // JVM path

vm.regedit=0  // Use JVM under vm.location when the value is 0; first use JVM in registry when the value is 1

vmarg.1=-Xdebug

vmarg.2=-Xnoagent

vmarg.3=-Xrunjdwp:transport=dt_socket,address=8787,server=y,suspend=n

Note: When ExcelRaq.ini is absent, use esProc built-in JDK by default.

3. Load the ExcelRaq.xll file in Excel

Open Excel, clickOptions->Add-inson the File tab. In the Manage box, click Excel Add-ins, and then click Go. In the Add-ins available box, click Browse and select the add-in file ExcelRaq.xll in /esproc/bin under esProc’s installation directory. Make sure the newly added add-in is checked, and then restart Excel to bring the change into effect.

4JDK

Excel and Java JDK should be of x86 architecture or of x64 architecutre at the same time. Different architecutres will result in exception (You can rn java-version to learn whether it is a x64 architecture).

 

5. Configure raqsoftConfig.xml file

Configure license file, main directory and searching directory in the raqsoftConfig.xml file. The dfx file to be called must be placed under the main directory or the searching directory in case it can’t be found. If the dfx file uses database data, the data source information should be configured in raqsoftConfig.xml.

 

6. Log

The log file is %appdata%\raqsoft\tmp\excel.log, you can check error information in it.

 

7. esproc() function

Description:

Populate the result set of computing a dfx file into the Excel grid.

Syntax:

esproc (dfxName, arg1,…)  Caculate the dfx file

esproc(express,arg1,…)    Parse and calculate expression dynamically

Remark:

The function is used in the Excel grid to obtain data of the result of computing a dfx file.

Here’s how to use the function. Select a stretch of grid area, type a function expression into the input box, and press ctrl+shift+enter. Now data in the result set of the dfx file is populated into the selected grid area.

If the selected grid area is smaller than the range of the array in the result set, only the populated data will be displayed; if the selected area is bigger, the extra grid area will be populated with #N/A.

If the dfx file calls the esProc function clipboard(s), you can select an Excel grid area to copy the clipboard content into it after the esproc() function is executed in Excel.

Parameters:

dfxName

dfx file name, without an extension

arg1,…

Parameters in the dfx file, and can be omitted; the number can be one or more; 50 at most. You can assign a value to a parameter in the function expression, or use data in the current Excel grid area as its value.

Data type of the parameter can be string, int, float, double, one-dimensional array, and two-dimensional array.

express

An expression string headed by an equal sign “=”. The esProc counterpart of Excel syntax esproc(express,arg1,…) is eval(StringExp,{argExp}), which can be dynamically parsed and calculated. Parameter express corresponds to parameters StringExp. You can see Function Reference to learn details.

Return value:

An array

Example:

1. Call stu.dfx

 

A

 

1

=connect("dbN")

 

2

=A1.query("select  *  from  students")

3

return A2

 

 

Select the grid area A1:E5 in an Excel file, type the expression =esproc("stu") in the input box, and press ctrl+shift+enter to get this:

The result set of the dfx file has 8 rows 4 columns. The selected Excel grid area is 5 rows * 5 columns, which has one more column but 3 rows less. In the final displayed grid area, the extra column E is populated with #N/A and only the first 5 records in the result set are displayed.

 

2. Call a dfx file using cellset parameters

Define two cellset parameters arg1 and arg2 for the stu.dfx file.

 

A

1

=connect("dbN")

2

=A1.query("select *  from  students  where age>? and  gender=?",arg1,arg2)

3

return A2

 

Select the grid area A2:A8, and enter expression =esproc("stu",15,"F") in the input box. You’ll get this:

You can also enter the parameter values into the Excel grid, and then call them by the cell names in the expression. For example:

Type 15 in cell A1 and M in cell B1. Select the grid area A2:D8 and enter expression =esproc("stu",A1,B1). You’ll get this:

3. Call a dfx file using cellset parameters whose values will be two-dimensional arrays passed from Excel

Define two cellset parameters arg1 and arg2 for the NewTable.dfx file

 

A

1

=create(Id, Name,Sex)

2

=A1.record(arg1)

3

=A1.record(arg2)

4

return A1

 

A1:C2 contains a two-dimensional array. Select an Excel grid area A4:C8 and enter expression =esproc("NewTable",A1:C1,A2:C2) to insert data within A1:C2 into a table generated by the dfx file. Here’s the result:

 

4. Parse an expression

Calculate 2 to the power of 5:

Perform string split:

8. Call esproc() function in VBA

You can also call the esproc() function from VBA. For example, to populate the result set of tsum.dfx file into the current Excel file, you can use the following code:

 

Sub callDfx()

[A1].Resize(5, 6) = Application.Evaluate("esproc(""tsum"")") 

//The area receiving the data is 5 rows * 6 columns starting from A1

End Sub

9. Excel macro import

  esProc offers macro file esproc_template.xla to import a macro. The macro importing enables automatic population of returned data into Excel. The functionality supports multiple types of returned values, including a single value, multiple values and Table data, as well as using a whole row or column as the pass-in parameter. You can do the configurations as follows:

The macro file esproc_template.xla is located ininstallation root directory\esProc\documents. You need to first put it under XLSTART directory in the local Excel installation directory, like C:\Program Files\Microsoft Office\Office16\XLSTART. Then you can enable macros through File -> Excel Options -> Trust Center -> Macro Settings in Excel.

Example 1: Generate Table data

Enter formula =esproc(“=create(AA,BB,CC).record(to(12))”) in A1  and press Ctrl+Enter to return the result. The returned data will be displayed from the row next to that containing the formula, as shown below:

Example 2: Whole row processing

To sum all values in row 4, for instance, we enter formula =esproc(“=?.sum()”,4:4) in A8. 4:4 represents the whole row 4. The formula returns result to A9, the nearest cell in the next row.

Same way to process a whole column. To compute column C we can represent the column as C:C.

Example 3: Return multiple values

To perform distinct, for instance, we enter formula =esproc(“=?.id().select(~)”,A1:A10) in C1:

If the input parameter covers a whole row, a whole column, or contains multiple returned values or Table data, the returned result will be displayed beginning from the next row to that containing the formula.

Example 4: Return Table data

To process data in the area of A1:C4 and return result as a table, we enter formula =esproc("=t=create(AA, BB,CC).record(?.conj()), t.derive(~.array().sum():SUM)",A1:C4) in A6. Then we press Ctrl+Enter to populate data from A7.

  

Example 5: Return a matrix

esProc provides esprocT() to return result as a matrix. To sum each row in the Excel, for instance, we enter formula =esprocT("=t=create(AA, BB,CC).record(?.conj()), t.derive(~.array().sum():SUM)",A1:C4) in A6. Then we press Ctrl+Enter to populate data from A7.


Both esprocT() and esproc have same uses. The former, however, returns result that doesn’t include column headers.

Example 6: Call dfx script

Take script D:\tmp\demo.dfx as an example. It contains content =create(AA,BB,CC).record(to(12)). To call the script, we type in formula =esproc(“D:\tmp\demo”) in A1 and then press Ctrl+Enter to populate result data from A2.

 

esProc lets users to use the shortcut key Ctrl+Enter to automatically populate the returned result into Excel when they import a macro through the macro import file esproc_template.xla. Or they can populate a selected area using Excel method Ctrl+Shift+Enter. Note that it would be the best that the cell nearest to the one holding the formula on the next row is empty, just in case the returned data overwrites the existing data.