Using Microsoft .NET in WebSphere Message Broker V8: Part 3: Integrating with Microsoft Excel

This series of four tutorials demonstrates the new support for Microsoft .NET in WebSphere Message Broker V8. Part 3 describes integration with Microsoft Excel. Readers should be familiar with either Microsoft .NET or WebSphere Message Broker but need not be familiar with both.

Share:

Ben Thompson (bthomps@uk.ibm.com), Consulting IT Specialist, IBM

Author photoBen Thompson is a Consulting IT Specialist working on the WebSphere Message Broker development team at the IBM Hursley Software Lab in the UK. He is currently working on the design and implementation of .NET support in Message Broker. In the past, he has worked for IBM Software Services for WebSphere designing and implementing WebSphere solutions for IBM customers worldwide. You can contact Ben at bthomps@uk.ibm.com.


developerWorks Contributing author
        level

Matthew Golby-Kirk (mgk@uk.ibm.com), Software Developer, IBM

Matthew Golby-Kirk photoMatthew Golby-Kirk is a software developer working on the WebSphere Message Broker development team at the IBM Hursley Software Lab in the UK. He works on the design and implementation of the HTTP and Web services support, along with the ESQL language run time in WebSphere Message Broker. You can contact Matthew at mgk@uk.ibm.com.



01 February 2012

Before you start

IBM® WebSphere® Message Broker V8 (hereafter called Message Broker) provides the capability to integrate with existing Microsoft® .NET® Framework (hereafter called .NET) applications. You can do this integration by wiring the new Message Broker .NETCompute node into a message flow, or by calling a .NET application from an ESQL Compute node. This series of tutorials shows you how to use the .NETCompute node integration technique.

WebSphere Message Broker and Microsoft .NET

About this tutorial series

Youtube tutorial: Integrating Microsoft .NET code in a WebSphere Message Broker message flow

This five-minute youtube tutorial shows you how simple it is to use WebSphere Message Broker V8 to build a message flow that includes Microsoft .NET code. Microsoft Visual Studio is used to build .NET code in C#, which is then integrated into a message flow using Message Broker and an HTTP RESTful interface.

This series of four tutorials shows you how to write and build C# code in Microsoft Visual Studio 2010 (either Express Edition or Professional Edition) using an embedded template provided by WebSphere Message Broker Toolkit. The four tutorials explore the following topics:

  1. Using the .NETCompute node sample
  2. Using the .NETCompute node to integrate with Microsoft Word
  3. Using the .NETCompute node to integrate with Microsoft Excel
  4. Using the .NETCompute node for exception handling

About this tutorial

This .NETCompute node scenario receives inbound files and then uses code written in C# to interact with the logical message tree and a Microsoft Excel spreadsheet. You can use the .NETCompute node on Microsoft Windows brokers to construct output messages and to interact with .NET or Component Object Model (COM) applications.

WebSphere Message Broker enables you to host and run .NET code inside an execution group. The new .NETCompute node routes or transforms messages by using any Common Language Runtime (CLR) compliant .NET programming language, such as C#, Visual Basic (VB), F#, or C++/Common Language Infrastructure (CLI). This tutorial describes the new .NET API provided by WebSphere Message Broker, which enables .NET developers to interact with Message Broker's logical tree.

Prerequisites and system requirements

This tutorial is written for WebSphere Message Broker programmers who want to learn about the new .NETCompute node, and for .NET programmers who want to learn about using WebSphere Message Broker. If you have a general familiarity with C# or with Message Broker, then you should find the tutorial relatively easy to complete.

To build and execute the example in this tutorial, you will need:

  • A Windows installation that includes Microsoft .NET Framework V4
  • WebSphere Message Broker (Toolkit and Runtime) V8
  • Microsoft Visual Studio 2010 (Express Edition or Professional Edition) to write and build the required C# code

The C# code in this tutorial takes data from the Message Broker logical tree and uses it to create a Microsoft Excel spreadsheet. The code uses the Open XML Format SDK (Version 2), which is an API for the .NET Framework that enables developers in both client and server environments to interact with Microsoft Word. Before starting the tutorial:

  1. Download Microsoft Open XML SDK V2.
  2. Launch OpenXMLSDKv2.msi, click Next on the splash panel, and accept the license agreement.
  3. Choose an install location or accept the default: C:\Program Files\Open XML SDK\V2.0\.
  4. Confirm the settings, wait for the installation to complete, and then click Close.

If you do not have a full copy of Microsoft Excel, you can download and install the Microsoft Excel Viewer and the Microsoft Office Compatibility Pack:

  • Download the Microsoft Excel Viewer.
  • Launch ExcelViewer.exe, accept the licence terms, click Next, accept additional licence terms, and then click Next.
  • Use the suggested default install location: C:\Program Files\Microsoft Office\ and click Install.
  • Wait for the installation to complete and then click OK.
  • Download the Microsoft Office Compatibility Pack.
  • Launch FileFormatConverter.exe, accept the licence terms, and then click Continue.
  • Wait for the installation to complete and then click OK.

Sample files

This tutorial provides instructions to create and execute all elements of the scenario. To test the scenario, you can download test data at the bottom of the article. Unzip the download file and use it as explained below. No further downloads are required in order to complete this tutorial.


Introduction

This tutorial provides a simple scenario to demonstrate how the Message Broker .NETCompute node can use the Open XML SDK API to interact with Microsoft Excel. A broker message flow receives an input XML file and records the hierarchy of the data into a Microsoft Excel spreadsheet. The flow parses the input file using the Broker's XMLNSC parser, so any well-formed XML input is acceptable. Having received and parsed the data, the message flow uses C# code within a .NETCompute node in order to update a Microsoft Excel spreadsheet. It records the broker's logical tree structure, which is the internal message broker representation of a message.

If you are experienced with the broker, the data written into the spreadsheet will look very similar to the output of a trace node when recording the logical message tree. Each row of the spreadsheet describes an element, recording its namespace, name, value, and the data type of the value. The spreadsheet document is written to a directory on the file system by the .NETCompute node, and then the message flow File Output node copies the original input xml structure into an output file.

Scenario


Creating the message flow

Download the zip file at the bottom of the article and extract the contents into the directory C:\student\DOTNET\lab_msexcel. This zip file provides a template spreadsheet file that the message flow interacts with, and a test file that you will use later to test the completed scenario.

  1. Assuming that the WebSphere Message Broker Toolkit has not yet been started, from the Start menu, choose Start => Programs => IBM WebSphere Message Broker Toolkit => IBM WebSphere Message Broker Toolkit 8.0 => WebSphere Message Broker Toolkit 8.0. You will be asked for the location of a workspace -- the rest of this tutorial assumes that you are using C:\student\DOTNET\lab_msexcel\workspace.
  2. Close the Welcome screen and from your empty workspace, launch the Quick Start wizard by clicking Start by creating an application, as shown below with the red box. If you are using your own existing workspace, you can also launch the Quick Start menu by clicking on New at the top of the Broker Development view on the left side of the window.

    Quick Start menu
  3. The Quick Start wizard requires an Application name. Specify App_DotNet_MSExcel and click Finish:

    Quick Start wizard
  4. In the Application Development window, click New, which is underneath the application you just created. Then on the pop-up New Artifact window, select Message Flow, as shown below:

    New Artifact window
  5. In the New Message Flow wizard that is launched, specify a Message Flow name of MyFlow. Click Finish:

    New Message Flow wizard
  6. After the message flow has been created, drag and drop nodes from the message flow palette onto the empty flow canvas. Take a File Input node and a File Output node from the File drawer. Later in the tutorial you will add a .NETCompute node from the Transformation drawer by simply dragging and dropping a .NET assembly .dll file on to the flow canvas. You will also wire the node terminals and set the properties of the flow nodes, but for the moment, the flow should look like this:

    Message Flow
  7. Go to the Windows start menu and launch Microsoft Visual Studio, which you will use to develop the C# code that the .NETCompute node will use to integrate with the logical message tree and produce the Microsoft Excel spreadsheet.

You will return to the message flow once you have written and built the C# code in Microsoft Visual Studio.


Writing the C# code in Microsoft Visual Studio

The following images were taken with Microsoft Visual Studio Professional Edition, but the tutorial can also be completed using Microsoft Visual Studio Express Edition.

  1. After Microsoft Visual Studio has launched, you will see it displaying the Start page below. Select New Project:

    Microsoft Visual Studio New Project

    Microsoft Visual Studio Template Extensions

    If the WebSphere Message Broker Toolkit is installed after Microsoft Visual Studio, then the Message Broker project templates will be automatically installed ready for you to use. However, if the Broker Toolkit is installed first, then you will need to manually install the templates by executing the file IBM.Broker.DotNet.vsix and stepping through the wizard. If you have used the default installation location, you will find this file at
    C:\Program Files (x86)\IBM\WMBT800\wmbt.

  2. The New Project wizard offers you the three types of Project templates. The Modify template makes a copy of the incoming message tree into a new NBMessage object, similar to the ESQL Compute node's CopyEntireMessage() function. The Create template provides an empty NBMessage object that you can use to construct an output message tree. The Filter template assumes that the incoming message tree will be propagated out of the node unchanged. Although this tutorial uses the same outgoing message tree as the incoming message tree, it uses the modify template to let you make further changes to the output message if you wish to. Select the template named Project to modify a Message Broker message, specify the properties at the bottom of the window as follows, and then click OK:

    Name = MSExcelDotNetProject
    Location = C:\student\DOTNET\lab_msexcel\visual studio 2010\Projects
    Solution Name = MSExcelDotNetProject

    New Project dialog
  3. Once the project is created, expand the Solution Explorer view. You should see that the ModifyNode.cs file has been created to contain the C# class. Also, notice that a reference to IBM.Broker.Plugin has been added. This assembly contains the API provided by Message Broker to transform messages in a .NETCompute node. You will also be interacting with two additional .NET Framework assemblies -- one provided by the Open XML SDK 2.0 API, and one that is included in the Windows .NET framework. Right-click the References level of the hierarchy in Solution Explorer and select Add Reference:

    Solution Explorer
  4. In the Add Reference dialog, switch to the .NET tab and locate the component named DocumentFormat.OpenXml. If you cannot find the entry, first make sure that you have sorted the components alphabetically. If the entry is definitely not in the list, then return to the System requirements section above and follow the instructions to download and install the Open XML SDK 2.0 API. Once you locate the DocumentFormat.OpenXml component, click OK:

    Add Reference to DocumentFormat.OpenXml
  5. Repeat the last two steps in order to add a second reference to the project. This time, locate the reference named WindowsBase on the .NET tab, as shown below, and click OK:

    Add Reference to WindowsBase
  6. The Solution Explorer should now show that the assembly references have been added successfully:

    Solution Explorer References
  7. The template class file ModifyNode.cs should be open and ready for you to edit. At the top of the file, add some using directives so that you can use the classes and methods contained in the assembly references that were just added without having to fully qualify them in the C# code. This technique enables you to write more concise and more readable code. The image below shows where to insert the statements, and the listing that follows provides the statements that should be added:

    Insertion of using statements
    Listing 1. Using directives to be placed at the top of ModifyNode.cs
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    using Tuple = System.Tuple;
    using XMLNSC = IBM.Broker.Plugin.NBParsers.XMLNSC;
  8. The Evaluate method template created within ModifyNode.cs provides the C# code entry point for messages arriving at the .NETCompute node in the message flow. Before creating the Evaluate code, create six methods that the code within Evaluate will invoke. Insert each of these methods in the hierarchy of the ModifyNode.cs class file as siblings of the Evaluate method, as shown below:

    Location of methods in ModifyNode.cs
  9. ClearData -- This method stores the first row of the spreadsheet (whose cells provide titles for the columns) in a variable, and then removes all the rows from the SheetData object. The title row is then reinserted. The method is called before any new data is inserted into the spreadsheet, so that if the message flow is run multiple times, the output data in the sheet is not confused with entries made by previous executions:
    Listing 2. Sample code for the ClearData method
    private static void ClearData(WorksheetPart XLWS)
    {
        Worksheet ws = XLWS.Worksheet;
        SheetData sdata = ws.GetFirstChild<SheetData>();
        var titleRow = 
          sdata.Elements<Row>().Where(r => r.RowIndex.Value == 1).FirstOrDefault();
        sdata.RemoveAllChildren<Row>();
        sdata.InsertAt(titleRow, 0);    
    }
  10. InsertSpreadsheetRowForCursor -- This method fills the cells of a row in the spreadsheet to describe the part of the logical message tree at which the NBElement cursor is currently pointing. This method is invoked from within a loop that iterates over the logical tree, so it is called once for each element level in the tree's hierarchy. Each row that is inserted contains cells describing the current element's Value Type, Namespace (if it has one), Name, and Value:
    Listing 3. Sample code for the InsertSpreadsheetRowForCursor method
    private static void InsertSpreadsheetRowForCursor(WorksheetPart XLWS,
      NBElement cursor, uint RowNum, uint IndentNum)
    {
        // Each row in the spreadsheet will contain an entry for one element
        // from the Broker's logical tree hierarchy. 
        // Every entry in the Broker logical tree can have:
        // A Namespace, a Name, a Type and a Value
        string insertnamespace = "";
        string insertname = "";
        string inserttype = "";
        string insertvalue = "";
    
        if (cursor.GenericType == NBElementGenericType.Name)
        {
            InsertCell(XLWS, "Folder", "A", RowNum);
        }
        else
        {
            inserttype = cursor.ValueType.ToString();
            InsertCell(XLWS, inserttype, "A", RowNum);
        }
    
        if (IndentNum > 0)
        {
            insertnamespace = cursor.Namespace ?? "NULL";
            InsertCell(XLWS, insertnamespace, "B", RowNum);
        }
    
        insertname = cursor.Name ?? "NULL";
        InsertCell(XLWS, insertname, CalcIndentLetter(2 + IndentNum), RowNum);
        if (cursor.ElementType == XMLNSC.PCDataField || 
            cursor.ElementType == XMLNSC.DoubleNamespaceDeclaration ||
            cursor.ElementType == XMLNSC.SingleNamespaceDeclaration ||
            cursor.ElementType == XMLNSC.DoubleAttribute ||
            cursor.ElementType == XMLNSC.SingleAttribute)
        {
            insertvalue = cursor.ValueAsString ?? "NULL";
            InsertCell(XLWS, insertvalue, CalcIndentLetter(3 + IndentNum), RowNum);
        }
    }
  11. NavigateCursor -- This method is also invoked from within a loop to locate the next element in the tree's hierarchy. If the current element has children, then the cursor is moved deeper into the hierarchy to the first available child. If there are no children of the current element, then the cursor is moved to the next sibling of the current position. If there are no siblings either, then the cursor is moved up the hierarchy to the next sibling of the cursor's parent. If the cursor's parent has no siblings, then the algorithm continues to iterate through positions nested less deep in the hierarchy, until a sibling of its ancestor elements is located:
    Listing 4. Sample code for the NavigateCursor method
    private static Tuple<NBElement,uint> NavigateCursor(NBElement cursor, uint IndentNum)
    {
        if (cursor.Children().Count() > 0)
        {
            // This means the cursor has at least one child
            // So move to the first child ...
            cursor = cursor.FirstChild;
            IndentNum++;
            return Tuple.Create(cursor, IndentNum);
        }
        else
        {
            if (cursor.NextSibling != null)
            {
                // Arrival here means no children, but siblings are present
                cursor = cursor.NextSibling;
                return Tuple.Create(cursor, IndentNum);                    
            }
            else
            {
                while (cursor.Parent != null)
                {
                    if (cursor.Parent.NextSibling != null)
                    {
                        cursor = cursor.Parent.NextSibling;
                        IndentNum--;
                        return Tuple.Create(cursor, IndentNum);
                    }
                    else
                    {                            
                        if (IndentNum > 0)
                        {
                            IndentNum--;
                        }
                        cursor = cursor.Parent;
                    }
                }
                return Tuple.Create(cursor, IndentNum);
            }
        }           
    }
  12. InsertCell This method inserts a cell into the WorksheetPart object at the location specified by the column and index:
    Listing 5. Sample code for the InsertCell method
    private static void InsertCell(WorksheetPart wspart,
        string text, string column, uint index)
    {
        string location = column + index;
        Worksheet ws = wspart.Worksheet;
        SheetData sdata = ws.GetFirstChild<SheetData>();
        // Locate the row number of index value
        var currentRow = 
            sdata.Elements<Row>().Where(r => r.RowIndex.Value == index).FirstOrDefault();
        // If the row does not already exist then create it 
        if (currentRow == null)
        {
            currentRow = new Row();
            currentRow.RowIndex = index;
            sdata.Append(currentRow);
        }           
        // If the row does not have a cell with the 
        // specified column letter then insert one.
        Cell cellFinal = new Cell() { CellReference = location };
        if (currentRow.Elements<Cell>().
            Where(c => c.CellReference.Value == location).Count() > 0)
        {
            cellFinal = currentRow.Elements<Cell>().
                        Where(c => c.CellReference.Value == location).First();
        }
        else
        {                
            // Loop over the elements in the row to locate the insert position
            Cell cellCursor = null;
            foreach (Cell cell in currentRow.Elements<Cell>())
            {                    
                if (string.Compare(cell.CellReference.Value, location, true) > 0)
                {
                    // Once the correct location is reached, update the cursor and exit
                    cellCursor = cell;
                    break;
                }
            }
            // Insert the cell in the correct position
            currentRow.InsertBefore(cellFinal, cellCursor);
        }
        // Now the cell definitely exists, set its Value and DataType
        cellFinal.CellValue = new CellValue(text);
        cellFinal.DataType = new EnumValue<CellValues>(CellValues.String);
        // Save the worksheet
        ws.Save();
    }
  13. CalcIndentLetter -- This method returns a string value containing a letter that corresponds to the column number passed into the method using the uint data type:
    Listing 6. Sample code for the CalcIndentLetter method
    private static string CalcIndentLetter(uint indentNumber)
    {
        object[] LetterArray = new object[]{"A","B","C","D","E","F",
                                            "G","H","I","J","K","L",
                                            "M","N","O","P","Q","R",
                                            "S","T","U","V","W","X",
                                            "Y","Z","AA","AB","AC","AD"};
        return (string)LetterArray[indentNumber-1];
    }
  14. SaveAndClose -- This method is called after construction of the spreadsheet is completed. It saves the spreadsheet and closes it, so that other applications (such as Microsoft Excel) can open the file once the message flow has completed its processing:
    Listing 7. Sample code for the SaveAndClose method
    private static void SaveAndClose(WorksheetPart XLWS, SpreadsheetDocument XLDoc)
    {
        XLWS.Worksheet.Save();
        XLDoc.Close();
    }
  15. Having created the helper methods, now consider the Evaluate method, which opens a Microsoft Excel spreadsheet file and represents it as a SpreadsheetDocument object. The spreadsheet file is expected to be at the hard-coded file system location C:\student\DOTNET\lab_msexcel\Test.xlsx. Once open, the first sheet is opened, and the ClearData method is used in order to blank any existing data from the sheet that may be there from previous executions of the message flow. The code then uses a loop to iterate through the logical message tree, invoking the two methods InsertSpreadsheetRowForCursor and NavigateCursor in turn. The RowNumber variable records the index of the next row for insertion, and the IndentNumber variable records how many nested levels into the message hierarchy have been navigated. As the cursor iterates more deeply into the message structure, this value increases, and then as tags are closed, it decreases again and ends at zero when the cursor reaches the end of the message. At the end of the UserCode region of the Evaluate method, invoke the SaveAndClose method to save the Excel worksheet and close the file:
    Listing 8. Sample code for the UserCode region of the Evaluate method
    #region UserCode
    // Add user code in this region to create a new output message
    
    // Hard-code the location of the spreadsheet file
    string filename = "Test.xlsx";
    string path = @"C:\student\DOTNET\lab_msexcel\";
    string sourceFile = System.IO.Path.Combine(path, filename);
    // Open the existing spreadsheet document
    SpreadsheetDocument XLDoc = SpreadsheetDocument.Open(sourceFile, true);
    WorkbookPart XLWBP = XLDoc.WorkbookPart;
    WorksheetPart XLWS = XLWBP.WorksheetParts.First();
    SheetData sheetData = XLWS.Worksheet.GetFirstChild<SheetData>();
    // Clear any existing data from spreadsheet
    ClearData(XLWS);
    uint RowNumber = 2;
    uint IndentNumber = 0;
    // Create an NBElement pointing at the root of the logical message tree
    NBElement treeRoot = inputMessage.RootElement;               
    NBElement cursorElement = treeRoot.FirstChild;
    // Iterate through the logical message tree
    while (!cursorElement.Name.Equals(treeRoot.Name))
    {
        // Insert the next row into the spreadsheet!
        InsertSpreadsheetRowForCursor(XLWS, cursorElement, RowNumber, IndentNumber);
        // Increment the counter for recording which row we are up to
        RowNumber = RowNumber + 1;
        // Navigate the cursor to the next item in the tree
        var result = NavigateCursor(cursorElement, IndentNumber);
        cursorElement = result.Item1;
        IndentNumber = result.Item2;
    }
    SaveAndClose(XLWS, XLDoc);
    #endregion UserCode
  16. With the development work now complete, build an assembly file that you will reference from the .NETCompute node. From the Solution Explorer, right-click on the Solution and select Build Solution:

    Build the Solution

    Opening the Output window in Microsoft Visual Studio

    If the Output window is not visible, and you are using Microsoft Visual Studio Professional Edition, then you can open it by selecting Debug => Windows => Output. If the Output window is not visible, and you are using Microsoft Visual Studio Express Edition, open it by selecting View => Output.

  17. The output window will show you where the built assembly file has been saved on your file system. Depending on how you have Microsoft Visual Studio configured, the Build Output window may not immediately be visible. If it is not, follow the instructions in the sidebar to open the Output window. Build Output

    If you have used the default naming suggested throughout this tutorial, the assembly file will be saved at:

    C:\student\DOTNET\lab_msexcel\visual studio 2010\Projects\MSExcelDotNetProject\ MSExcelDotNetProject\bin\Debug\MSExcelDotNetProject.dll

The next section of the tutorial shows you how to unite your message flow development and the C# assembly you have built, and deploy to Message Broker.


Preparation and deployment to Message Broker

Having built an assembly file from the C# code, you can drag and drop the assembly file from a Windows Explorer window directly onto the message flow canvas in order to create a .NETCompute node that is associated with the assembly file's code. This technique results in a hard-coded, absolute location for the assembly (set to the location that you dragged from), and is intended as a quick method when developing, testing, and hot-swapping the .NET code that the Broker is executing. For production situations, a more typical approach is to define a Message Broker Configurable Service that specifies to a .NETCompute node where to locate the assembly file. Here is the drag and drop technique for the configuration of the current scenario:

  1. To prepare for the drag and drop, return to the WebSphere Message Broker Toolkit, where the message flow MyFlow.msgflow that you created earlier should still be open. Open a Windows Explorer window in front of the Toolkit and navigate to the location where you built the assembly file in the previous section. If you have followed the default naming so far, the location will be:
    C:\student\DOTNET\lab_msexcel\visual studio 2010\Projects\MSExcelDotNetProject\
    MSExcelDotNetProject\bin\Debug\MSExcelDotNetProject.dll

    Drag and drop the assembly on top of the .NETCompute node in the flow, as shown by the red arrow:



    Drag and drop assembly to configure the .NETCompute node
  2. Wire up the Out terminal of the FileInput node to the In terminal of the .NETCompute node. Also wire the Out terminal of the .NETCompute node to the In terminal of the FileOutput node. The message flow should now look like this:

    Wired up message flow
  3. Inspect the properties of the .NETCompute node and you should see that the Assembly name property has now been configured, as shown below. Save the message flow by pressing Ctrl-S:

    Assembly name property of the .NETCompute node
  4. Select the FileInput node and inspect its properties. On the Basic properties panel, set the Input directory to be C:\student\DOTNET\lab_msexcel\input and set the File name or pattern to be *.xml. These properties mean that the message flow will start whenever a file with the extension .xml is found in the above directory. Here are the Basic node properties:

    Basic properties panel of the File Input node
    Switch to the Input Message Parsing properties panel and set the Message Domain property (you can use the drop-down) to the value XMLNSC : For XML messages (namespace aware, validation, low memory use). This setting means that the message flow will expect inbound files to contain well-formed XML, and will construct the logical message tree using the broker's XMLNSC parser. If you wished, you could specify a particular message model (XML schema) to which inbound data should conform, but for the purposes of this tutorial, just specify the XMLNSC message domain so that any XML document can be used to test the scenario. Here are the Input Message Parsing node properties:

    Input Message Parsing properties panel of the File Input node
  5. Select the FileOutput node and inspect its properties. On the Basic properties panel, set the Directory to be C:\student\DOTNET\lab_msexcel\output and set the File name or pattern to be *.xml . The output file name used by the node will be the same as the input file name, because the .NETCompute node copies the LocalEnvironment tree, which provides a dynamic override for the file name. Here are the Basic node properties:

    Basic properties panel of the File Output node
  6. Save the message flow.
  7. Before deploying the message flow, using a Windows Explorer window, create the file directories that it uses to read input files and write output files:

    C:\student\DOTNET\lab_msexcel\input
    C:\student\DOTNET\lab_msexcel\output

    The image below shows the C:\student\DOTNET\lab_msexcel directory after the two subdirectories have been created:

    Windows explorer directory creation
  8. This tutorial assumes that you have already set up a runtime broker using the default configuration, which provides a message broker named MB8BROKER. For more information on the default configuration, see Creating the Default Configuration in the Message Broker information center.
  9. In order to deploy the application containing the message flow that you created, simply drag and drop it onto a runtime execution group, such as the default configuration shown below. The red arrow shows the drag and drop movement:

    Application deployment via drag and drop

The next section of the tutorial shows you how to test the scenario.


Testing the scenario

  1. If you have not yet done so, you can download the zip file at the bottom of the article. The zip file contains the spreadsheet file (Test.xlsx) that the message flow opens and updates, and an xml input file (TestData.xml) that you can use to test the flow. Extract the zip file to the directory C:\student\DOTNET\lab_msexcel. Here is the directory, which by this stage of the tutorial should also contain the subdirectories shown below:

    Test Directory
  2. Before running data through the scenario, open the spreadsheet template file Test.xlsx. When successfully executed, the scenario adds data into this spreadsheet. The tutorial was written this way, rather than creating the spreadsheet from scratch, to simplify the C# code. The template spreadsheet should look like this:

    Spreadsheet Template
  3. Close Microsoft Excel, which must be done before running the scenario to allow the message flow to open the file.
  4. Copy the xml file TestData.xml into the input subdirectory C:\student\DOTNET\lab_msexcel\input. The XML data in the provided input file is shown below:
    Listing 9. xml file test data: TestData.xml
    <ns1:ElementA xmlns:ns1="www.ibm.com/one">
     <ns2:ElementB xmlns:ns2="www.ibm.com/two">
      <ElementC AttrD="This is an attribute value">
       This element has no namespace and contains this string value
      </ElementC>
     </ns2:ElementB>
    </ns1:ElementA>
  5. After a few seconds, the message flow reads the input data and copies it into an output file of the same name, which is then saved into the output directory C:\student\DOTNET\lab_msexcel\output. Check that the output file has successfully appeared, and then navigate to the directory C:\student\DOTNET\lab_msexcel. The spreadsheet should have a recent Date modified property. Open Test.xlsx using Microsoft Excel. The columns should be populated with information from the logical message tree, which represents the xml file data as it passes through the message flow:

    Output Spreadsheet

You have completed Part 3 of this tutorial series, and learned how to combine the WebSphere Message Broker .NETCompute node with C# code and the Open XML SDK V2 API to access Microsoft Excel. You can continue on to the download links, resource links, and author information by clicking Next below, or you can go on to Part 4 of the tutorial series.


Download

DescriptionNameSize
Code sampledotnet_msexcel_resources.zip7 KB

Resources

  • WebSphere Message Broker resources
  • WebSphere resources
    • developerWorks WebSphere developer resources
      Technical information and resources for developers who use WebSphere products. developerWorks WebSphere provides product downloads, how-to information, support resources, and a free technical library of more than 2000 technical articles, tutorials, best practices, IBM Redbooks, and online product manuals.
    • developerWorks WebSphere application integration developer resources
      How-to articles, downloads, tutorials, education, product info, and other resources to help you build WebSphere application integration and business integration solutions.
    • Most popular WebSphere trial downloads
      No-charge trial downloads for key WebSphere products.
    • WebSphere forums
      Product-specific forums where you can get answers to your technical questions and share your expertise with other WebSphere users.
    • WebSphere on-demand demos
      Download and watch these self-running demos, and learn how WebSphere products and technologies can help your company respond to the rapidly changing and increasingly complex business environment.
    • developerWorks WebSphere weekly newsletter
      The developerWorks newsletter gives you the latest articles and information only on those topics that interest you. In addition to WebSphere, you can select from Java, Linux, Open source, Rational, SOA, Web services, and other topics. Subscribe now and design your custom mailing.
    • WebSphere-related books from IBM Press
      Convenient online ordering through Barnes & Noble.
    • WebSphere-related events
      Conferences, trade shows, Webcasts, and other events around the world of interest to WebSphere developers.
  • developerWorks resources
    • Trial downloads for IBM software products
      No-charge trial downloads for selected IBM® DB2®, Lotus®, Rational®, Tivoli®, and WebSphere® products.
    • developerWorks blogs
      Join a conversation with developerWorks users and authors, and IBM editors and developers.
    • developerWorks cloud computing resources
      Access the IBM or Amazon EC2 cloud, test an IBM cloud computing product in a sandbox, see demos of cloud computing products and services, read cloud articles, and access other cloud resources.
    • developerWorks tech briefings
      Free technical sessions by IBM experts to accelerate your learning curve and help you succeed in your most challenging software projects. Sessions range from one-hour virtual briefings to half-day and full-day live sessions in cities worldwide.
    • developerWorks podcasts
      Listen to interesting and offbeat interviews and discussions with software innovators.
    • developerWorks on Twitter
      Check out recent Twitter messages and URLs.
    • IBM Education Assistant
      A collection of multimedia educational modules that will help you better understand IBM software products and use them more effectively to meet your business requirements.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere
ArticleID=791518
ArticleTitle=Using Microsoft .NET in WebSphere Message Broker V8: Part 3: Integrating with Microsoft Excel
publish-date=02012012