Excelling With WebFOCUS: Updating Server Data from a WebFOCUS Excel Template
by Mario Delgado
The ability to merge WebFOCUS data with an Excel template is a powerful and popular feature. It allows a developer to host a custom Excel Workbook in a centralized location, insert real-time data and dynamically serve-up the Excel file from a browser-accessible application.
This article will demonstrate how this feature can be exploited to include WebFOCUS generated data into an Excel application that not only displays data, but also sends it back to a WebFOCUS server for updating.
 |
Screen 1 |
 |
Screen 2 |
This solution is appropriate for closed-loop applications, such as budgeting apps, where a developer integrates live data with Excel templates that allow for “what-if” analyses and the eventual posting of any changes back to the server.
Background
First, I have a few words on how this solution differs from the Maintain/WebFOCUS Connector for Excel. (See the reference section below for a link detailing this feature.)
This solution uses a centralized Excel template and, unlike the Connector for Excel, does not require the ActiveX MSINET.OCX file. The solution takes advantage of the built-in Microsoft XML HTTP control to perform an HTTP post of the Excel-based data back to the server. While Maintain can be utilized server-side to insert/update data, one can also utilize Modify or straight SQL procedures to process the data.
The Application Lifecycle
Screen 1 shows the demonstration application in Excel
The application displays a single worksheet with data generated by a WebFOCUS report against the CAR file and a button to upload data back to the server. Users will make changes to the data and then click the “Upload Changes” button to initiate the transaction that posts the data back to the server.
Screen 2 shows the display message detailing the server-side statistics of the uploaded data transaction, which is processed by a FOCEXEC.
WebFOCUS Code
Listing 1 contains the code that will both generate the Excel application and process the updated data.
-DEFAULT &GOTO_LABEL = 'REPORT';
APP PATH EXCEL_MHT
-GOTO &GOTO_LABEL
-REPORT
TABLE FILE CAR
PRINT
COUNTRY
CAR
MODEL
BODYTYPE
RETAIL_COST
DEALER_COST
COMPUTE MARGIN/D5.2 = (RETAIL_COST - DEALER_COST) / DEALER_COST;
ON TABLE PCHOLD FORMAT EXL2K FORMULA TEMPLATE 'EXCEL_TEMPLATE' SHEETNUMBER 2
END
-EXIT
-INSERT
-IF &COUNTRY.EXISTS NE 1 THEN GOTO NODATA;
MODIFY FILE CAR
FREEFORM COUNTRY CAR MODEL BODYTYPE DEALER_COST RETAIL_COST
MATCH COUNTRY CAR MODEL BODYTYPE
ON NOMATCH REJECT
ON MATCH UPDATE DEALER_COST RETAIL_COST
DATA
-IF &COUNTRY0.EXISTS EQ 1 THEN GOTO MANY;
-JUST_ONE
COUNTRY=&COUNTRY ,CAR=&CAR , MODEL=&MODEL , BODYTYPE=&BODYTYPE, DEALER_COST=&DEALER_COST
,RETAIL_COST=&RETAIL_COST ,$
-GOTO LOOP
-MANY
-SET &I = 0;
-REPEAT LOOP &COUNTRY0 TIMES
-SET &I = &I + 1;
COUNTRY=&COUNTRY.&I ,CAR=&CAR.&I , MODEL=&MODEL.&I , BODYTYPE=&BODYTYPE.&I, DEALER_COST=&DEALER_COST.&I
,RETAIL_COST=&RETAIL_COST.&I ,$
-LOOP
END
-NODATA
-EXIT
Listing 1
By default the logic generates the Excel application (-DEFAULT &GOTO_LABEL=’REPORT’;). The update logic is invoked when the FOCEXEC receives a value of ‘INSERT’ in the &GOTO_LABEL variable. The critical line in the report is as follows:
ON TABLE PCHOLD FORMAT EXL2K FORMULA TEMPLATE 'EXCEL_TEMPLATE' SHEETNUMBER 2
This line instructs WebFOCUS to merge the report data into the ‘EXCEL_TEMPLATE’ file within the second worksheet. Executing this procedure will display the Excel application in the user’s browser assuming Internet Explorer was set up to host Microsoft Office applications (see reference below). Otherwise, the Excel program will be activated in a separate window to host the downloaded application.
The label ‘-INSERT’ starts the section that contains the logic for parsing out the response data from the Excel application. It uses MODIFY syntax to update the database. The Dialogue Manager logic is written to handle indexed and single amper variables. As noted earlier, the parsed-out variables could also be utilized in a Maintain application or as parameters to SQL procedures.
The Excel Template
I recommend that a developer first code and test the Excel template as an .xls file before saving it into the require .mht format. As per the WebFOCUS feature requirements, the template used for this demonstration has a separate Worksheet (called ‘DownLoaded Data’) to receive the merged WebFOCUS data. This worksheet is hidden from the user at runtime.
Listing 2 contains all the Visual Basic for application code hosted in the ThisWorkbook and ‘User’s Data’ modules:
‘’’’’’Code inside ThisWorkbook module
Private Sub Workbook_Open()
Dim UserWS As Worksheet
Dim DownLoadWS As Worksheet
Set UserWS = Worksheets("User's Worksheet")
Set DownLoadWS = Worksheets("DownLoaded Data")
'Hide the downloaded Worksheet
DownLoadWS.Visible = False
'Copy the Downloaded data to the User's Worksheet
Call DownLoadWS.UsedRange.Copy(UserWS.Range("A1"))
'Reformat the copied data
With UserWS.UsedRange
.WrapText = False
.MergeCells = False
End With
UserWS.UsedRange.Columns.AutoFit
End Sub
‘’’’’’Code inside of ‘User’s Data’ module
Option Explicit
Private Sub CommandButton1_Click()
Call xmlQuery
End Sub
Sub xmlQuery()
Const Server_URL = "http://localhost:8080/ibi_apps/WFServlet"
Const IBIAPP_app = "EXCEL_MHT"
Const IBIF_ex = "EXCEL_MHT"
Dim xmlhttp As xmlhttp
Set xmlhttp = New xmlhttp
Dim strRequest As String
strRequest = getRequest(IBIAPP_app, IBIF_ex)
Call xmlhttp.Open("POST", Server_URL, False)
Call xmlhttp.setRequestHeader("Content-Type", "application/x-www-form-urlencoded")
Call xmlhttp.send(strRequest)
If CheckBox1.Value = True Then
Call MsgBox(xmlhttp.responseText)
End If
Set xmlhttp = Nothing
End Sub
Function getRequest(IBIAPP_app As String, IBIF_ex As String) As String
Dim strRequest As String
strRequest = getChangedData()
If strRequest = "" Then Exit Function
getRequest = "RANDOM=" & Int(Timer()) & _
"&IBIF_wfdescribe=OFF" & _
"&GOTO_LABEL=INSERT" & _
"&IBIAPP_app=" & IBIAPP_app & _
"&IBIF_ex=" & IBIF_ex & _
"&" & strRequest
End Function
Function getChangedData() As String
Dim UserWS As Worksheet
Dim DownLoadWS As Worksheet
Dim UserRange, OriginalRange, rw, cel As Range
Dim strRequest As String
Set UserWS = Worksheets("User's Worksheet")
Set DownLoadWS = Worksheets("DownLoaded Data")
Set UserRange = UserWS.UsedRange
Set OriginalRange = DownLoadWS.UsedRange
strRequest = ""
For Each rw In UserRange.Rows
If (rw.Row <> 1) Then 'Row 1 contains column heading, so skip it
For Each cel In rw.Cells
strRequest = IIf(strRequest = "", strRequest, strRequest & "&")
'COUNTRY
strRequest = strRequest & _
OriginalRange.Rows(1).Cells(1).Value & "=" & _
escape(UserRange.Cells.Rows(rw.Row).Cells(1).Value) & "&"
'CAR
strRequest = strRequest & _
OriginalRange.Rows(1).Cells(2).Value & "=" & _
escape(UserRange.Cells.Rows(rw.Row).Cells(2).Value) & "&"
'MODEL
strRequest = strRequest & _
OriginalRange.Rows(1).Cells(3).Value & "=" & _
escape(UserRange.Cells.Rows(rw.Row).Cells(3).Value) & "&"
'BODYTYPE
strRequest = strRequest & _
OriginalRange.Rows(1).Cells(4).Value & "=" & _
escape(UserRange.Cells.Rows(rw.Row).Cells(4).Value) & "&"
'RETAIL_COST
strRequest = strRequest & _
OriginalRange.Rows(1).Cells(5).Value & "=" & _
escape(UserRange.Cells.Rows(rw.Row).Cells(5).Value) & "&"
'DEALER_COST
strRequest = strRequest & _
OriginalRange.Rows(1).Cells(6).Value & "=" & _
escape(UserRange.Cells.Rows(rw.Row).Cells(6).Value)
Next cel
End If
Next rw
getChangedData = strRequest
End Function
Public Function escape(stringIn As String) As String
'URL Encode the string
Dim temp As String
temp = Replace(stringIn, "%", "%25")
temp = Replace(temp, "+", "%2B")
temp = Replace(temp, " ", "%20")
temp = Replace(temp, ";", "%3B")
temp = Replace(temp, "/", "%2F")
temp = Replace(temp, "?", "%3F")
temp = Replace(temp, ":", "%3A")
temp = Replace(temp, "@", "%40")
temp = Replace(temp, "=", "%3D")
temp = Replace(temp, "&", "%26")
temp = Replace(temp, "<", "%3C")
temp = Replace(temp, ">", "%3E")
temp = Replace(temp, Chr(34), "%22")
temp = Replace(temp, "#", "%23")
temp = Replace(temp, "{", "%7B")
temp = Replace(temp, "}", "%7D")
temp = Replace(temp, "|", "%7C")
temp = Replace(temp, "\", "%5C")
temp = Replace(temp, "^", "%5E")
temp = Replace(temp, "~", "%7E")
temp = Replace(temp, "[", "%5B")
temp = Replace(temp, "]", "%5D")
temp = Replace(temp, Chr(96), "%60")
temp = Replace(temp, Chr(10), "%0A")
temp = Replace(temp, Chr(13), "%0D")
escape = temp
End Function
The Visual Basic environment can be activated from the Excel menus by selecting Tools/Macro/Visual Basic Editor of off the menu. (Note: Users of this application must set the macro security for their environment to medium. This is set in Excel by Tools/Macro/Security…)
The Workbook_Open() subroutine handles the copying of the merged data from the ‘DownLoaded Data’ worksheet to the ‘User’s Worksheet’ worksheet. It also does some minor formatting and hides the ‘DownLoaded Data’ worksheet.
The subroutine xmlQuery() processes the actual posting of data back to the server. The constants Server_URL, IBIAPP_app and IBIF_ex can be customized as needed to properly refer to the appropriate WebFOCUS environment.
The xmlhttp object referenced in xmlQuery() is made available to the Excel application by including it as an object reference. This is accomplished in the Visual Basic environment via the Tools/References… menu and selecting the latest version of the Microsoft XML control (see reference below).
Within the xmlQuery subroutine the line strRequest = getRequest(IBIAPP_app, IBIF_ex) will generate an HTTP post request that collects the data from the spreadsheet. More on this below.
Call xmlhttp.Open("POST", Server_URL, False) invokes the open method to open a connection to WebFOCUS via the Server_URL. Setting the Asynch, the third parameter, property to false instructs the control to operate in a synchronous; i.e., wait until the server completes processing, mode.
Call xmlhttp.setRequestHeader("Content-Type", "application/x-www-form-urlencoded") sets the request header.
Call xmlhttp.send(strRequest) invokes the method that sends the request to the server.
Messages from the server are available by displaying the xmlhttp.responseText property.
The getRequest function calls the getChangeData function and builds the post request. Note that one of the GOTO_LABEL variables is set to ‘INSERT’, instructing the FOCEXEC to branch to the appropriate label.
The getChangeData function utilizes methods exposed by the Excel Object mode (see reference below) to navigate through the ‘User’s Worksheet’ worksheet and build a post request in Variable=Value format. This is accomplished by looping through each row of the range that contains the data in the spreadsheet. The rows collection of the range and cells collection of an individual row are iterated through to process the information.
The range with the data is determined by utilizing the UsedRange property for a worksheet. It conveniently determines which area of the spreadsheet actually contains data that needs to be processed.
A user might change the column heading in the spreadsheet. Therefore, the original column heading values, which represent the database column names, are obtained from the original data in the ‘DownLoaded Data’ worksheet by referring to the OriginalRange range, which is a reference to the UsedRange property of the ‘DownLoaded Data’ worksheet. Note that the logic does not refer to static values, but rather the dynamically generated column names, which makes the code logic generically available for file other than the Car file.
Finally, an escape function is used to properly encode the data for the HTTP protocol.
Conclusion
The demo application presented here illustrates another innovate use of Excel and WebFOCUS. Exploiting Excel templates with dynamic WebFOCUS reports can be the foundation of low maintenance, highly interactive applications that take advantage of the strength each component offers.
References
The following resources compliment the information and techniques presented here.
Resource |
URL |
The Maintain/WebFOCUS Connector |
http://techsupport.informationbuilders.com |
How to configure Internet Explorer to open Office documents in the appropriate Office program instead of in Internet Explorer |
http://support.microsoft.com/kb/162059 |
Using Excel Templates |
http://documentation.informationbuilders.com |
Super Easy Guide to Microsoft Office Excel 2003 Object Model |
http://msdn.microsoft.com/office/understanding/excel |
MSXML Control |
http://msdn2.microsoft.com/en-us/library/ms763742.aspx |
Excel 2002 VBA: Programmer’s Reference |
http://www.amazon.com/exec/obidos/tg/detail/-/0764543717/qid=1080750571/sr=1-4/ref=sr_1_4/104-9731657-4599105?v=glance&s=books |
