Excel add-ins

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

1. Functionality

An Excel add-in calls the result set of executing an esProc dfx script from an Excel file, and inserts the data 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

 

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).

If regedit\java jdk\jvm.dll in the system registry isn’t installed or invalid, Excel will load the JDK embedded in esProc (its path is [esProc installation root directory]\common). The JDK in the system registry will always take priority over the esProc built-in JDK.

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. dfx() function

Description:

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

Syntax:

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

dfx(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 dfx() 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 dfx(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 =dfx("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 =dfx("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 A1 and M in B1. Select the grid area A2:D8 and enter expression =dfx("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 =dfx("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 dfx() function in VBA

You can also call the dfx() 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("dfx(""tsum"")") 

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

End Sub