Converting Excel To XML
Overview
This sample demonstrates how you can use the Excel To XML process step to convert an incoming Excel worksheet into an XML document. The Excel To XML process step allows you to select which worksheet in the incoming message to convert. It also provides options to add row numbers, restrict the number of rows, and a column constraint to filter which data is returned from the file.
Note: The Excel To XML process step uses the Excel ODBC driver which is installed by default with Microsoft Office. This driver can also be downloaded from here: https://www.microsoft.com/download/en/details.aspx?id=13255. If you are running the 64-bit version of Neuron, you must also install either the 64-bit version of Microsoft Office or Excel ODBC driver. Microsoft does not allow side-by-side installation of this driver.
Process Components Demonstrated:
- Excel to XML
- Code
- Execute Process
- Trace
Solution
The GetTaxRates process depicted in Figure 1 has an Excel To XML process step and a Code process step. These steps are contained within the Try execution block of an Exception process step. Inside the Catch execution block, a Process Execute step calls the Exception Handling process to process any exceptions that might arise.
The Excel To XML process step is configured to return all the rows from the Rates worksheet where TaxType = ‘STS’. If successful, the trace viewer will display the number of rows returned and the resulting message. See Figure 2 for the Excel To XML process step properties.
Running the Sample
Open the Sample
To open this sample, see the topic Using the Neuron Samples and select the Converting Excel To XML sample.
Run the Sample
- The sample launcher opens two Neuron Test Clients. Connect one to TaxPublisher another to TaxSubscriber.
- On the Send tab of the TaxPublisher test client, click the Load Body button. In the Open file dialog box, browse to the Samples\Processes\ConvertingExcelToXML folder and select the file TaxRates.xlsx. ClickOpen.
- On the Send tab of TaxPublisher make sure that the Tax topic is selected in the Topic Dropdown, verify the Semantic is set to Multicast and the Send Binary option is selected. Click the Send button.
- View the result in the Receive tab of the TaxSubscriber test client.
- Click on the Message History tab of the TaxSubscriber test client. Select the last message received by the client and click the View Custom Properties link. If the file was successfully processed you will see properties similar to those in Figure 3.
Note: If you need the schema for the XML document returned by the Excel To XML process step, set the Include Schema property to true:
The message received by the TaxSubscriber test client will include the schema that represents the output message.
Configuration Notes
All processes with the exception of the Code process step are configured by selecting and setting their properties in the property grid located at the bottom right of the process designer. The Code process step is configured by selecting the “Edit” option from the short cut menu that is available when right-clicking the Code step in the process designer. See the process documentation for more information.
