Refreshing One Column based on the value of Another Column in ADFdi Table
When using ADF Desktop Integration, quite frequently, we get into a situation where we would like to refresh one column based on the value of another column. In ADF Faces, we can achieve this by setting the autoSubmit property and partialTriggers property for the corresponding columns.
However, in ADFdi, we do not have such option. Though we can achieve this by using LOVs and Dependent LOVs. But, in some scenarios we would like to achieve this when using an Input Text Component.
In this article, we will simulate this Auto Refresh functionality in a ADFdi Table.
Note : Since we would be using VBA code to achieve this, we can use this only on the Macro Enabled Excel Workbooks.
Let us assume that we have a View Object based on the Emp table. We could take an example of having a transient attribute in the VO, that gives the sum of Salary and Commission attributes.
However, in ADFdi, we do not have such option. Though we can achieve this by using LOVs and Dependent LOVs. But, in some scenarios we would like to achieve this when using an Input Text Component.
In this article, we will simulate this Auto Refresh functionality in a ADFdi Table.
Note : Since we would be using VBA code to achieve this, we can use this only on the Macro Enabled Excel Workbooks.
Let us assume that we have a View Object based on the Emp table. We could take an example of having a transient attribute in the VO, that gives the sum of Salary and Commission attributes.
In the above example, we've added a new transient attribute (SalPlusComm) to the EmpVO, that would give the sum of Sal and Comm attributes. Since we need this attribute to get refreshed when either Sal or Comm attribute changes, we set the Sal and Comm attributes as Dependencies. Also, we set the AutoSubmit property (under UI Hints tab) for the Sal and Comm attributes.
Now, we are done with the model layer. We can now, create a jspx page and then Drag and Drop EmpView as ADF Table. After this, we create an Excel Workbook (macro enabled), enable ADF Desktop Integration for it, set the required Workbook Properties, and then add a Table based on the EmpView.
As there are no straight forward way in ADFdi to trigger a request to server when a value of a cell is changed, we will now add a DoubleClickActionSet for the Sal and Comm columns. This DoubleClickActionSet will have the Table.RowUpSync and Table.RowDownSync actions.
Above example image shows the DoubleClickActionSet for Sal column. In the same manner, we need to add the DoubleClickActionSet for the Comm column as well.
Now, we have the workbook, that would fetch the SalPlusComm attribute (after recalculation in the model), when we change the Sal / Comm attribute and then double click on that column. To do this automatically when the user tabs out / presses enter key on the cells, we'll write a bit of VBA Code on the Worksheet where we've this table (Go to Developer Tab and Click on Visual Basic).
Excel would trigger Worksheet_Change event when a cell in the worksheet is modified. So, we would code our logic in that event. The above code assumes that Sal column is present in the N (14th) column on the worksheet and Comm column is present in O (15th) column. So, we would execute our logic only when the contents in these two columns change.
ADFdi would provide a context menu (Invoke Action...) when a DoubleClickActionSet is added to a particular column. We'll make use of that context menu and invoke it programatically.
We invoke that context menu programatically using the following line of code
Now, we are done with the model layer. We can now, create a jspx page and then Drag and Drop EmpView as ADF Table. After this, we create an Excel Workbook (macro enabled), enable ADF Desktop Integration for it, set the required Workbook Properties, and then add a Table based on the EmpView.
As there are no straight forward way in ADFdi to trigger a request to server when a value of a cell is changed, we will now add a DoubleClickActionSet for the Sal and Comm columns. This DoubleClickActionSet will have the Table.RowUpSync and Table.RowDownSync actions.
Above example image shows the DoubleClickActionSet for Sal column. In the same manner, we need to add the DoubleClickActionSet for the Comm column as well.
Now, we have the workbook, that would fetch the SalPlusComm attribute (after recalculation in the model), when we change the Sal / Comm attribute and then double click on that column. To do this automatically when the user tabs out / presses enter key on the cells, we'll write a bit of VBA Code on the Worksheet where we've this table (Go to Developer Tab and Click on Visual Basic).
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 14 Or Target.Column = 15 Then
Target.Select
Application.CommandBars("Cell").Controls("Invoke Action...").Execute
End If
End Sub
Excel would trigger Worksheet_Change event when a cell in the worksheet is modified. So, we would code our logic in that event. The above code assumes that Sal column is present in the N (14th) column on the worksheet and Comm column is present in O (15th) column. So, we would execute our logic only when the contents in these two columns change.
ADFdi would provide a context menu (Invoke Action...) when a DoubleClickActionSet is added to a particular column. We'll make use of that context menu and invoke it programatically.
We invoke that context menu programatically using the following line of code
Application.CommandBars("Cell").Controls("Invoke Action...").Execute
Now, we run our workbook, modify the value of Sal column for any row and tab out of that field would automatically update the value of SalPlusComm column.
Here, a simple example (Transient Attribute) is taken for the explanation. In the similar fashion, we can also have a DoubleClickActionSet to contain a method in the Impl that would perform this calculation as well.
Tip: If you are not able to view the image fully, right click on the image and choose View Image option to see it completely.
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
