WDDX exists to allow us to share data. With it your ColdFusion application can "talk" to someone else's Perl script, and it in turn can talk to someone else's Microsoft Word document. Quite a powerful concept, but I have to admit I haven't had a lot of opportunities to use it.

Much of the focus on WDDX has centered on syndication. "Web syndication is the idea that the content and commerce assets of a corporate Web site can be exposed as services and data to other Web sites, allowing sites to syndicate their value to other Web sites" [ Wddx.org]. Now don't get me wrong. This is a very appealing idea and certainly could revolutionize many business relationships. Unfortunately, most of my clients are still trying to figure out how to best use their own data themselves, never mind sharing it with others! Accordingly, I'd like to take a moment to look into how WDDX can be used for more mundane data-sharing tasks.

I recently completed a large project in which users in many different organizations within a large company contributed to a fairly complex database. A rich Web interface was developed to input records and was made as painless as possible considering the number of fields to be completed. Only after we had trained users from all of these disparate units did we realize that many had grown their own local systems for collecting their own data, mostly Excel spreadsheets. The users all balked at the idea of reentering all of their information with the Web forms, asking, "Why can't we just upload our spreadsheets?"

Sure, we could have saved all of the spreadsheets as delimited text files and written an import procedure with CF or tried to use ODBC drivers for Excel to get at the data. But WDDX and its COM support seemed an attractive solution.

The Idea
Write a Visual Basic macro for Excel that would create a WDDX packet for the entire spreadsheet and post the packet to a ColdFusion template. The CF template would then deserialize the packet and insert each record into the database.

The biggest drawback with such an implementation was that all the contributors needed some custom software installed on their PC (with the required COM objects). After that, though, contributing all of their hard-gathered data was a breeze. If they didn't like our Web form, they could even continue to use their spreadsheets and periodically upload their records.

The Method
In Excel
A VBA macro will use the WDDX COM object to serialize the spreadsheet's contents into a WDDX packet. It then uses the freeware version of Softwing's ASPTear object to send the packet to the Web server. ASPTear is used in a number of examples in the WDDX SDK, including the "Insert List of Books" Word Macro. Of course, in the Word Macro example, the point was to get information from the Web server and put it into the Word document. Our project is the opposite: to take information out of Excel and put it into the Web application.

To begin, register the two objects on the machine on which the spreadsheet will be used by placing the WDDX_COM implementation files (wddx_com.dll, xmlparse.dll and xmltok.dll wddx_com.dll) and ASPtear.dll in the Windows system directory. Then open up a MS DOS prompt, use CD to navigate to the Windows system folder where you placed the files, and type the following:

regsvr32 wddx_com.dll

and

regsvr32 ASPTear.dll

You're now ready to start Excel and create a new spreadsheet. The macro will use the column-heading values as column names in the WDDX packet, so it's important to inform users what to enter in these fields. The order isn't important, just the values. Let's say that you're inputting information about books in a fictional bookstore (original, huh?). The sheet will begin with the column names as shown in Figure 1.

From the Tools menu choose Macro, then Visual Basic Editor. After a moment, Visual Basic for Applications will appear and you can begin writing the module. Right-click on Sheet 1 in the VBA Project tree and select Insert, then Module. This will open a blank editor window as shown in Figure 2.

Before creating the Macro, VBA must know that this procedure will be referencing the COM objects that were installed earlier. To do this, select References from the Tools menu. This will open a dialog listing all available references on the machine. In the list, find and check the box next to wddx_com 1.0 Type Library. Click OK to return to the editor.

The Macro is created as a public subprocedure in VBA, so begin by typing the following in the editor pane:

Public Sub SendToWeb()

After typing and pressing the enter key, the editor will write the closing End Sub statement. Your variable declarations consist of the WDDX objects, the ASPTear object and information about the spreadsheet such as the range of used rows and columns:

Dim MySer As WDDXSerializer   'Allaire's WDDX serializer
Dim MyRS As WDDXRecordset    'Allaire's WDDX recordset
Dim xobj As Object, strRetval As String    'Softwing's ASPTear
Dim HowManyRows, HowManyColumns
Dim rng1 As Excel.Range

Once declared, instances of each object can be created:

' Create instance of WDDX.Serializer, Recordset and ASPTear object
Set MyRS = CreateObject("WDDX.Recordset.1")
Set MySer = New WDDXSerializer
Set xobj = CreateObject("SOFTWING.ASPtear")

The recordset has been created but is empty at this point. Populating it will be done in three steps:

1. Add a column for each used column in the spreadsheet.
2. Add an empty row for each used row in the sheet.
3. Populate the values of each column row by row.

All three steps are concerned only with the area of the sheet that has been used - a range that Excel exposes with the UsedRange variable. With that value, count the used rows and columns through which you will later loop. Notice that the number of rows is decreased by one since your column headers are in row number one.

Determine the used range of the sheet and count the Rows and Columns

' Set rng1 = ActiveSheet.UsedRange
HowManyRows = rng1.Rows.Count - 1
HowManyColumns = rng1.Columns.Count

The WDDX recordset needs one column for each column in the sheet. Loop through each cell in the first row and call the WDDX addColumn function to add each column name to your recordset.

For counter = 1 To HowManyColumns
ThisColumn = ActiveSheet.Cells(1, counter).Value
MyRS.addColumn (ThisColumn)
Next counter

The number of rows in the recordset is the number of rows in the used range of your sheet, again decremented by one because the first row contains our field names.blockquote> ' Add a row for each row in the sheet
MyRS.addRows (HowManyRows) So now you have the WDDX recordset with columns named for each spreadsheet column and an empty row for each spreadsheet row waiting to be populated with data. Looping through each row and within the row looping through each cell, every value is put into the recordset (see Listing 1).

Once populated, the recordset is serialized into a finished WDDX packet.

' Serialize it into a WDDX Packet
MyPacket = MySer.serialize(MyRS)

The packet is to be sent to ColdFusion via HTTP Post with the ASPTear object. If you were posting from a Web form, a name/value pair would be sent. With ASPTear, you specify the name/value pair(s) as a PostData parameter in the function.

Const Request_POST = 1
strPostData = "WDDXPacket=" & MyPacket
strRetval = xobj.Retrieve("http://www.your-
server.com/GetWDDXFromExcel.cfm", 1, strPostData, "", "")

strRetval will be set to whatever our ColdFusion template returns after the HTTP post is processed. This value may be used for a verification message, perhaps, informing the user that x number of rows were received and processed (hopefully) successfully. Finally, end the Sub routine by displaying the return in a message box.

MsgBox (strRetval)

In ColdFusion
Meanwhile, back on the Web server, a ColdFusion template is patiently waiting to receive the HTTP post containing the WDDX recordset.

When the post arrives, CF first uses the CFWDDX tag to deserialize the packet out of WDDX and into a format that it can use. This is the "wddx2cfml" action. Since your packet variable (strPostData) was named WDDXPacket in the last piece of the VBA Macro, that's the variable used in your CFWDDX tag.

<CFWDDX ACTION='wddx2cfml' input=#WDDXPacket# output='FromExcel'>

ColdFusion now has a recordset called FromExcel.

Back in Excel, data was added to the recordset by looping through each row and through each column in each row. Getting data out works in the same way, except that it uses the FromExcel recordset instead of the used range value.

In your real application the innermost nest would contain the database insert. For this demonstration, however, simply output the name/value pairs and display them back in Excel in our message box.

<cfloop query="FromExcel">
<cfloop index="ThisColumn" list="#FromExcel.ColumnList#">
<cfoutput>#ThisColumn# = #Evaluate(ThisColumn)#</cfoutput>
</cfloop>
</cfloop>

Notice that we use ThisColumn twice. By itself, #ThisColumn# outputs the field name. For its value, use #Evaluate(ThisColumn)# (meaning "the value of the column called #ThisColumn#").

And that's it! No matter how many records are in the spreadsheet or how the columns are named or ordered, ColdFusion can receive the information and do with it whatever you can dream up. Later, you might extend the process by having CF return a WDDX recordset back to Excel. This direction - from the Web to office applications - is well documented. Essentially, complex "conversations" between desktop and Web server can take place since you now have the "universal" data language of WDDX.

• Figure 1
• Figure 2
• Source Code