Skip to main content

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

The first time you sign into developerWorks, a profile is created for you. Select information in your developerWorks profile is displayed to the public, but you may edit the information at any time. Your first name, last name (unless you choose to hide them), and display name will accompany the content that you post.

All information submitted is secure.

  • Close [x]

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.

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

All information submitted is secure.

  • Close [x]

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

Ben Thompson (bthomps@uk.ibm.com), Consulting IT Specialist, IBM
Author photo
Ben 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.
(An IBM developerWorks Contributing Author)
Matthew Golby-Kirk (mgk@uk.ibm.com), Software Developer, IBM
Matthew Golby-Kirk photo
Matthew 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.

Summary:  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.

View more content in this series

Date:  01 Feb 2012
Level:  Intermediate

Comments:  

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.

4 of 9 | Previous | Next

Comments



Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere
ArticleID=791518
TutorialTitle=Using Microsoft .NET in WebSphere Message Broker V8: Part 3: Integrating with Microsoft Excel
publish-date=02012012
author1-email=bthomps@uk.ibm.com
author1-email-cc=
author2-email=mgk@uk.ibm.com
author2-email-cc=

Tags

Help
Use the search field to find all types of content in My developerWorks with that tag.

Use the slider bar to see more or fewer tags.

Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).

My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).

Use the search field to find all types of content in My developerWorks with that tag. Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere). My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).