Using Microsoft Excel 2002 and Microsoft BizTalk Server To Build B2B Solutions
Joe Fütty, Uma Subramanian, and Acey Bunch
Microsoft® Excel 2002
Microsoft BizTalk™ Server 2000
Summary: This technical article discusses the advantages of using Microsoft Excel 2002 and BizTalk Server 2000 to develop business-to-business e-commerce systems. (17 printed pages)
Understanding the Championzone Solution
Excel 2002 as the Client
XML for Data Transport
XML Web Services with SOAP
Active Server Pages as the SOAP Listener
BizTalk for XML Messaging and Orchestration
SQL Server for Data Storage
Message Queue Services for Data Flow
COM Components for Application Logic and Processing
Summary of the Complete Solution
One of the challenges that system architects building business-to-business (B2B) e-commerce systems face is integrating with business partners. In today's fast-paced economy, solutions must be quick, easy-to-use, and cost-effective. Although many companies use electronic data interchange (EDI) to exchange business documents, EDI systems are costly to set up and maintain. Many smaller companies are unable to justify the setup and operational costs associated with EDI servers. Even companies that trade with Extensible Markup Language (XML) face challenges in integrating with the smaller suppliers who simply cannot manage a server. However, trading with smaller businesses usually generates the greatest overhead in terms of manual processes. Therefore, the need exists for a solution that enables the smallest of businesses to participate in B2B activities without large investments of time and money.
One asset that a solution developer can leverage is Microsoft® Office XP. Since many smaller businesses already depend on and are quite familiar with the Office suite, Office XP can provide an effective way for a solution developer to deploy applications while requiring little change on the part of the clients. In addition, using Office as the client provides for a rich editing experience and, unlike browser-based solutions, allows users to work on the documents offline.
To complete the B2B scenario in conjunction with Office, a back-end server must be used to process the incoming data, applying business rules and processes as needed. However, the clients may be geographically disparate from the server environment, thus enabling the larger company to deploy the appropriate infrastructure and the smaller companies to deploy rich clients. Microsoft BizTalk™ Server 2000 can create a strategic advantage as an orchestration server that enables you to build and deploy integrated business processes with your trading partners. It offers a quick and cost-effective solution to building B2B applications and enables you to respond rapidly to your customers' needs and competitive pressures. Additionally, BizTalk Server offers a suite of tools and services that help automate and streamline internal business processes by integrating internal business applications such as accounting and inventory systems.
A solution based on Office XP increases the accessibility and cost-effectiveness of a BizTalk Server environment by enabling the smallest of businesses to establish automated electronic trading relationships. For small businesses, Office XP provides a fast, easy, and cost-effective way to get electronically connected with trading partners. Using Office XP as a BizTalk client enables companies to enhance their reach within their trading communities.
The following sections describe a hypothetical solution that demonstrates the architecture used to establish a B2B application employing Microsoft® Excel 2002 and BizTalk Server 2000. Although this example is only hypothetical, large businesses have already deployed real-world Office XP and BizTalk Server 2000 solutions.
To better understand the different concepts and technologies used in a B2B application, let's imagine that we have a fictitious company, Championzone, which is in the business of bringing other businesses together online. Championzone has realized the need to expand its customer base to include both very small customers as well as large organizations. To date, the cost and complexity of integration needed to enter the B2B marketplace has limited Championzone's ability to attract new business. Customers had been required to undertake time-consuming and expensive integration projects to connect to the hub for placing orders and receiving confirmation notices.
Championzone needed to find a solution in which even the smallest customer could participate in this B2B activity without a large investment and without a server environment. In this case, Championzone wanted to automate the customer's credit approval process for the purchase of Championzone goods. At the same time, it sought to enable the solution to be extensible to include the exchange of inventory information, tracking information, and other inventory management data.
The Championzone solution uses Excel 2002 at the customer's site to communicate with Championzone's own BizTalk Server. A Championzone customer begins the process by downloading an Excel spreadsheet from the Championzone server. The spreadsheet contains an order form for Championzone's products and services. The customer fills out the order and clicks a submission button on the spreadsheet. A simple Microsoft Visual Basic® for Applications (VBA) script embedded in the spreadsheet takes the order information and sends it as XML to an XML Web service on Championzone's Web site.
Championzone then uses BizTalk Orchestration to graphically define and then execute the approval process. Championzone uses orchestration to interact with its back-end credit check system to evaluate the customer's credit and then returns a message accepting or rejecting that credit for the transaction. This information is then passed back to the customer through an XML Web service. In the final step, the customer receives a message a few moments later inside the Excel spreadsheet, informing him or her whether or not the credit has been accepted. From the customer's perspective, simply pressing a button in an Excel spreadsheet resulted in a B2B order that was sent to Championzone and returned with a confirmation—all inside the familiar Excel environment.
In the following sections, we will discuss the various technologies used to implement a B2B application based on Excel 2002 and BizTalk Server.
With the introduction of native XML support in Office XP, organizations can now begin to combine XML integration with the familiar user interface of Office and the ease of VBA programming. The ubiquity, robustness, and familiarity of Office make it an effective tool to link current processes into a B2B network. Using BizTalk Server, organizations can set up B2B processes to exchange documents and data with their customers and suppliers regardless of size or platform. Then, using Office XP, these customers and suppliers can quickly begin to interface through the network without incurring tremendous entry costs. The rich client environment in Office XP gives companies easy access to rich validation and presentation facilities that would otherwise be costly to develop. Consequently, the inherent value of a B2B solution can benefit both enterprise-sized organizations and small businesses, which can begin transactions electronically by opening an Excel spreadsheet, filling in the appropriate data, and sending it to the Web server.
One reason for using Office XP as the client interface in a B2B application is that using Hypertext Markup Language (HTML) through a Web interface does not make for a high-capacity data entry experience. HTML forms are primarily a single record-editing methodology; if you have a catalog of 500 items to update, entering information will take a long time. The data is also very prone to being lost by an accidental "back" action or a dropped Internet connection. In addition, going to a separate Web site for data entry takes the knowledge worker out of his or her working environment, which in this case is Excel 2002. Finally, Office XP is much better suited than an HTML interface for saving, sharing, and reusing data effectively.
For our Championzone solution, we create an Excel 2002 template for entering purchase orders. This template includes VBA programming code to automate the process of sending the purchase order to a hub, fronted by an XML Web service interface and using BizTalk Server 2000 orchestration on the back end. The user would simply need to enter his or her purchase information into the Excel spreadsheet and then click the Submit button, as shown in Figure 1.
The key to this scenario is the native XML support in Office XP, which enables communication directly with BizTalk Server. XML has quickly become the standard format of choice for data interchange. Office XP offers full support for XML in Excel 2002.
Because Excel 2002 recognizes XML as a native file format, Excel can now read XML directly and load it into spreadsheets. Excel can also be used to extract XML from the worksheets and the data sent using HTTP to any Web-enabled business process. For our solution, Championzone uses an Excel-based business document for a purchase order that needs to be shared by different business partners. The template could extract the relevant data programmatically from Excel as XML, and then push that data into any number of XML-enabled business processes or applications, which in this case is BizTalk Server.
However, why should we use XML specifically? The reason is that using XML as the data transport removes application dependencies, because XML is focused purely on the data itself. This means that we can achieve true platform independence; using a tool such as BizTalk Server allows an organization much easier translation, transportation, manipulation, and orchestration of the data it receives.
One of the new features of Excel 2002 is the ability to convert a range of values to the XML format. In our scenario, when the user clicks the Submit button, VBA code takes a range of entered values from the spreadsheet and converts them to the XML format as the function receives them. The following lines of code demonstrates how you can take a range of values and, using the xlRangeValueXMLSpreadsheet constant, pass that range in the native XML format:
sRvalue = _ Client.sendmsg _ (ActiveSheet.Range("a1:o104").Value(xlRangeValueXMLSpreadsheet), _ Guid)
In addition to converting a range of values to XML, Excel 2002 also enables the user to persist an entire spreadsheet to XML. Figure 2 shows our complete purchase order in XML as viewed with Microsoft® Internet Explorer:
Another critical piece of our B2B scenario is the use of XML Web services, which are units of application logic that provide data and services to other applications. Other applications access XML Web services via ubiquitous Web protocols and data formats such as HTTP, XML, and SOAP. Using these Internet-based protocols eliminates the need to worry about how each XML Web service is implemented. These XML Web services combine aspects of component-based software development and the Web, and make up the cornerstone of the Microsoft .NET programming model.
When using SOAP, a client-side component allows an application to invoke XML Web service operations by using a Web Services Description Language (WSDL) file. Also, a server-side component maps invoked XML Web service operations described by WSDL and the Web Services Meta Language (WSML) file.
Note The WSML file is used in Microsoft's implementation of SOAP on the server for its internal operations.
In the Championzone solution, the Microsoft SOAP Type Library supplied in the SOAP Toolkit 2.0 calls XML Web services. Of course, the client could easily be written using the .NET framework. In the following code sample, we declare the variable Client as a SOAP client, we initialize the SOAP client, and then we call the sendmsg method to submit our purchase order in native XML format to the XML Web service.
Dim Client As SoapClient Dim sRvalue As Boolean ... ' Create and initialize a SOAP client object. Set Client = New SoapClient ' Take the value from cell 104, this is where the SOAP init ' value is stored. Client.mssoapinit wsdlURL ... ' Submit the XML spreadsheet and GUID value to sendmsg. ' The SOAP server will pass these values to the BizTalk Server. sRvalue = _ Client.sendmsg(ActiveSheet.Range("a1:o104"). _ Value(xlRangeValueXMLSpreadsheet), Guid)
You will notice in the code sample above that to initialize the SOAP client, you call the mssoapinit method, passing the URL to the WSDL file located in a virtual directory on the Web server. The WSDL file is created using the SOAP Toolkit 2.0 Wizard, which takes a COM type library and exposes its methods as an XML Web service; it also generates the accompanying WSML file. The WSDL file is an XML file that describes the network services offered by the Web server. It essentially serves as the contract between the Web server, which offers the services, and the client, which consumes the services. On the other hand, the WSML file is used to provide information that maps the operations of a service (as described in the WSDL file) to specific methods in the custom COM object. The WSML file determines which COM object to load to service the request for each operation. The following is a sample WSDL file.
<?xml version='1.0' encoding='UTF-8' ?> <!-- Generated 05/15/01 by Microsoft SOAP Toolkit WSDL File Generator, Version 1.00.623.0 --> <definitions name ='wsBizTalkOfficeXP' targetNamespace = 'http://tempuri.org/wsdl/' xmlns:wsdlns='http://tempuri.org/wsdl/' xmlns:typens='http://tempuri.org/type' xmlns:soap='http://schemas.xmlsoap.org/wsdl/soap/' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:stk='http://schemas.microsoft.com/soap-toolkit/wsdl-extension' xmlns='http://schemas.xmlsoap.org/wsdl/'> <types> <schema targetNamespace='http://tempuri.org/type' xmlns='http://www.w3.org/2001/XMLSchema' xmlns:SOAP-ENC='http://schemas.xmlsoap.org/soap/encoding/' xmlns:wsdl='http://schemas.xmlsoap.org/wsdl/' elementFormDefault='qualified'> </schema> </types> <message name='clsMain.GetResponse'> <part name='sResponse' type='xsd:string'/> <part name='Guid' type='xsd:string'/> <part name='status' type='xsd:string'/> </message> <message name='clsMain.GetResponseResponse'> <part name='Result' type='xsd:boolean'/> <part name='sResponse' type='xsd:string'/> <part name='status' type='xsd:string'/> </message> <message name='clsMain.SendMsg'> <part name='doc' type='xsd:anyType'/> <part name='Guid' type='xsd:anyType'/> </message> <message name='clsMain.SendMsgResponse'> <part name='Result' type='xsd:boolean'/> </message> <portType name='clsMainSoapPort'> <operation name='GetResponse' parameterOrder='sResponse Guid status'> <input message='wsdlns:clsMain.GetResponse' /> <output message='wsdlns:clsMain.GetResponseResponse' /> </operation> <operation name='SendMsg' parameterOrder='doc Guid'> <input message='wsdlns:clsMain.SendMsg' /> <output message='wsdlns:clsMain.SendMsgResponse' /> </operation> </portType> <binding name='clsMainSoapBinding' type='wsdlns:clsMainSoapPort' > <stk:binding preferredEncoding='UTF-8'/> <soap:binding style='rpc' transport='http://schemas.xmlsoap.org/soap/http' /> <operation name='GetResponse' > <soap:operation soapAction='http://tempuri.org/action/clsMain.GetResponse' /> <input> <soap:body use='encoded' namespace='http://tempuri.org/message/' encodingStyle='http://schemas.xmlsoap.org/soap/encoding/' /> </input> <output> <soap:body use='encoded' namespace='http://tempuri.org/message/' encodingStyle='http://schemas.xmlsoap.org/soap/encoding/' /> </output> </operation> <operation name='SendMsg' > <soap:operation soapAction='http://tempuri.org/action/clsMain.SendMsg' /> <input> <soap:body use='encoded' namespace='http://tempuri.org/message/' encodingStyle='http://schemas.xmlsoap.org/soap/encoding/' /> </input> <output> <soap:body use='encoded' namespace='http://tempuri.org/message/' encodingStyle='http://schemas.xmlsoap.org/soap/encoding/' /> </output> </operation> </binding> <service name='wsBizTalkOfficeXP' > <port name='clsMainSoapPort' binding='wsdlns:clsMainSoapBinding' > <soap:address location='http://localhost/wsBizTalkOfficeXP/wsBizTalkOfficeXP.ASP' /> </port> </service> </definitions>
In addition to creating the WSDL and WSML files, to enable exposed COM services through a Web server, you must also create a SOAP listener, which is the handler of incoming SOAP requests. You can create two types of listeners: an Internet Server API (ISAPI) listener or an Active Server Pages (ASP) listener. Regardless of whether the SOAP requests invoke an ISAPI or ASP listener, the server handles the incoming and outgoing data in the same way. The following is a sample ASP file used as a SOAP listener.
<%@ LANGUAGE=VBScript %> <% Option Explicit On Error Resume Next Response.ContentType = "text/xml" Dim SoapServer If Not Application("SoapServerInitialized") Then Application.Lock If Not Application("SoapServerInitialized") Then Dim WSDLFilePath Dim WSMLFilePath WSDLFilePath = Server.MapPath("wsBizTalkOfficeXP.wsdl") WSMLFilePath = Server.MapPath("wsBizTalkOfficeXP.wsml") Set SoapServer = Server.CreateObject("MSSOAP.SoapServer") If Err Then SendFault "Cannot create SoapServer object. " & Err.Description SoapServer.Init WSDLFilePath, WSMLFilePath If Err Then SendFault "SoapServer.Init failed. " & Err.Description Set Application("wsBizTalkOfficeXPServer") = SoapServer Application("SoapServerInitialized") = True End If Application.UnLock End If Set SoapServer = Application("wsBizTalkOfficeXPServer") SoapServer.SoapInvoke Request, Response, "" If Err Then SendFault "SoapServer.SoapInvoke failed. " & Err.Description Sub SendFault(ByVal LogMessage) Dim Serializer On Error Resume Next ' "URI Query" logging must be enabled for AppendToLog to work Response.AppendToLog " SOAP ERROR: " & LogMessage Set Serializer = Server.CreateObject("MSSOAP.SoapSerializer") If Err Then Response.AppendToLog "Could not create SoapSerializer object. " & Err.Description Response.Status = "500 Internal Server Error" Else Serializer.Init Response If Err Then Response.AppendToLog "SoapSerializer.Init failed. " & Err.Description Response.Status = "500 Internal Server Error" Else Serializer.startEnvelope Serializer.startBody Serializer.startFault "Server", "The request could not be processed due to a problem in the server. Please contact the system admistrator. " & LogMessage Serializer.endFault Serializer.endBody Serializer.endEnvelope If Err Then Response.AppendToLog "SoapSerializer failed. " & Err.Description Response.Status = "500 Internal Server Error" End If End If End If Response.End End Sub %>
Now that we have a basic understanding of what is used on the client in our B2B scenario, we should look at what happens on the server side. As mentioned previously, BizTalk Server 2000 is the tool that we use for orchestrating our business processes from the enterprise to the Internet.
BizTalk Server 2000 provides the infrastructure and tools for building successful B2B solutions, enabling organizations to build and deploy integrated business processes within their own organization and with their trading partners. BizTalk Server allows companies to move solutions to market more quickly, using fewer resources. This allows companies to respond rapidly to customer needs and competitive pressures. BizTalk Server offers a suite of tools and services that make orchestrating business processes and integrating applications fundamentally easier and faster. You can quickly implement secure, reliable trading partner relationships independent of operating systems, programming models, or programming languages.
The BizTalk Server infrastructure helps companies to quickly integrate, manage, and automate dynamic business processes by exchanging business documents among applications, within or across organizational boundaries. BizTalk Server helps companies build business processes that span not only applications, but also organizations, over the Internet. Graphical tools make it easy for business analysts and application developers to model and implement solutions. For example, you can use the BizTalk Orchestration Designer, based on Microsoft Visio®, to layout and integrate business processes visually.
BizTalk Server 2000 also makes it easy for developers to integrate applications and businesses together. Business analysts and application developers benefit from a host of rich graphical tools for building XML. BizTalk Server 2000 also leverages XML to support legacy file formats such as EDI and flat files. BizTalk Server 2000 includes tools like the BizTalk Editor, which can be used to generate XML schemas for your file formats, or the BizTalk Mapper, a powerful tool for creating custom transformations of XML. The BizTalk Messaging Manager features a graphical tool for setting up communications agreements. BizTalk also supports the Extensible Stylesheet Language Transformations (XSLT) standard for creating Extensible Stylesheet Language (XSL) style sheets to transform XML data.
With support for public standards and specifications, such as XML, MIME, S/MIME, SMTP, HTTP, and security standards like HTTPS, public key encryption, and digital signatures, BizTalk Server 2000 ensures interoperability and security with applications and business partners.
Within BizTalk Server, messaging is the functionality that you use for mapping the Excel 2002 XML schema to a more generic data representation. While the details of this process are outside the scope of this article, Figure 3 displays the second of two mappings used to convert our source Excel XML data to our destination format.
BizTalk Orchestration Services is used to design and develop business processes that manage the overall business logic. Traditionally, business-process design and implementation have been performed in two distinct phases: the visual design phase and the coding phase. BizTalk Orchestration Services integrates these phases within a unified design environment, which provides a versatile drawing surface and a comprehensive set of implementation tools.
To implement a business process, you need to create and compile XLANG schedule drawings. This typically involves the following steps:
- Draw a representation of the business process that the XLANG schedule will run.
- Create the port implementations that the business process requires.
- Define the flow of data between messages.
- Compile the XLANG schedule drawing into XLANG schedules. XLANG schedules are executable XML representations of the information contained within the drawings.
You can perform all the above tasks using a single design tool, the BizTalk Orchestration Designer. Based on Visio 2000, the BizTalk Orchestration Designer enables you to create business process drawings that can be compiled and run as XLANG schedules. In addition, within BizTalk Server, you can use orchestration to validate the incoming data against business rules. While the details this process are outside the scope of this article, Figure 4 displays a sample orchestration as built in the BizTalk Server Visual Process Design Environment. Of particular note is the representation in the left-hand pane. First, the business process receives the purchase order mapped to a data friendly XML format. Subsequently, a credit check is performed. If this is successful, the purchase order is saved and a "success" response is generated. If, on the other hand, it is unsuccessful, then a "failure" response is generated. The responses are then delivered to the Excel client, who is calling a second XML Web service that is waiting for the response document
One of the requirements in our B2B scenario was to have the ability to update the client with a status of their order through an XML Web service interface in an asynchronous manner. This requires the use of two XML Web services and a means to persist and correlate the state of the transaction. To do this, we needed to persist status information to a central data store, and for this, we chose to use Microsoft SQL Server™ 2000. Figure 5 depicts the schema of our single database table.
Figure 5. SQL Server table schema
To increase the scalability, reliability, and asynchronous nature of our B2B application, we chose to use Microsoft Message Queuing (MSMQ). MSMQ enabled our application to send and receive messages from established queues, thereby guaranteeing message delivery, routing, and security of all messages received and sent through the XML Web service interface.
Figure 6 displays our two message queues as seen from the Microsoft Windows® 2000 Computer Management Console.
The final piece of the Championzone application is the use of Component Object Model (COM) components, which house much of the processing code that handles the manipulation and routing of the XML purchase order data. These components are the business logic that ties the system together and allows us to process the data received from the Excel client through the XML Web service interface. Once the XML data is received from the XML Web service interface and placed in the message queue, that data is then sent to BizTalk Server for further processing and validation against our established business rules. In addition, the components also handle updating the status information in the SQL Server database.
The COM components can be implemented using any COM-compliant programming language such as Visual Basic. While a thorough discussion of the implementation details of the COM components is outside the scope of this article, the following code shows the logic used in the SendMsg method that is called from the Excel client through the XML Web service interface:
Public Function SendMsg(ByVal doc, ByVal Guid) As Boolean On Error GoTo Error_Handle Dim oDocUtil As DocUtil.Utility Set oDocUtil = New DocUtil.Utility ' We use the database as the persistent store ' so we can always see the status based on what's in the database. oDocUtil.insertrow (Guid) ' This will put the message on the message queue. oDocUtil.SendMsg (doc) Set oDocUtil = Nothing SendMsg = True Exit Function Error_Handle: SendMsg = False Err.Raise Err.Number, "wsBizTalk.clsMain.SendMsg", Err.Description End Function
As an overview of the Championzone B2B scenario, the following list describes what happens to the purchase order as it is routed through the system:
- The user enters data into the Excel purchase order template then clicks the Submit button.
- Using VBA programming code, the data is converted to native XML format and then passed to the XML Web service interface using SOAP.
- An Active Server Page (ASP) on the Web server takes the incoming data and, using custom COM components, puts that data in the form of a message and places it in a message queue.
- The queue sees the new message, calls other COM components to process the data further, and then sends it on to BizTalk Server, updating a SQL Server database with status information.
- BizTalk Server takes the XML data and maps it to the desired output format, then uses orchestration to validate the data, given established business rules.
- The validated data is passed back through the XML Web service interface to the Excel client.
- The user is notified about the status of his or her order.
Figure 7 depicts the complete solution and all of the various components and processes involved:
By creating the B2B application using Office XP and BizTalk Server 2000, Championzone was able to deploy the solution quickly to its customers. The customers were able to begin placing orders immediately through the exchange without any customization or integration effort. Because everything was XML-based, they were able to simply use Office XP as the interface. Although Championzone's development effort was minimal, it was able to create a fully functional business-to-business solution, which can be easily expanded in the future to include data exchange from other back-end systems using XML. Championzone's customers valued the solution because it leveraged the XML support in a product they were already using for creating reports and orders, Microsoft Excel, and therefore eliminated the learning curve required with the introduction of a customized solution.
The combination of Office XP and BizTalk Server 2000 provides a powerful set of B2B technologies that enable organizations establish B2B quick, powerful, and cost-effective solutions. With the introduction of native XML support in Office XP, organizations can now combine XML integration with the familiar user interface of Office, linking current processes into an effective B2B network.
For more information about the different technologies discussed in this article, please visit the following Web sites:
- Office Developer Center
- SQL Server Developer Center
- BizTalk Server Web site
- XML Developer Center
- SOAP Developer Center
- Web Services Developer Center
- Microsoft Message Queuing Web site
- Visual Studio Developer Center