Skip to content

Converting Excel To XML

Note: It is recommended that the Neuron process documentation be reviewed before running this sample. The documentation thoroughly describes each process component individually and describes how to configure each one as well as how to construct a process and associate it with a Neuron Publisher or Subscriber. See the Configuration Notes section at the end of this document for more information.

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: http://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.

Figure 1: GetTaxRates process as displayed in the Neuron Process Designer.

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.

Figure 2: Properties for the Excel To XML process step in the GetTaxRates process.

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

  1. The sample launcher opens two Neuron Test Clients. Connect one to TaxPublisher another to TaxSubscriber.
  2. 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.
  3. 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.
  4. View the result in the Receive tab of the TaxSubscriber test client.
  5. 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.
Figure 3: Custom Properties for the Excel To XML process step in the GetTaxRates process.

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.

Note: To test this process with a file adapter, click Connections in the left navigation menu and then select Adapter Endpoints. In the endpoint ExcelFileEndpoint, click the Properties tab and modify the Folder Path to point to the location of the In folder (i.e. <Install Directory>\Samples\Processes\ConvertingExcelToXML\In). Then copy the Excel file TaxRates.xlsx into the In directory. The results will still appear in theTaxSubscriber test client.

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.

Figure 5: The Neuron Process Designer displaying the GetTaxRates process. Property Grid at the bottom right displaying the properties for the Excel To XML process step.
Was this article helpful?
Dislike 0
Previous: Accessing SQL Data From Processes
Next: Correlated Messaging with the MSMQ Process Step