Creating Custom Pipeline Component and Pipeline for Reading Excel File in BizTalk

In this blog I will explain how can we read an Excel file using pipeline in BizTalk . A custom pipeline components need to be develop to parse the excel file to generate an xml file matching an existing defined schema. A custom pipeline component is just simple .NET DLL that implements a class with some predefined interfaces. This interface represents the layer between a .NET program and the BizTalk Server.

When we create custom pipeline class we need to Implement  IBaseMessage, IComponent, IComponentUI, and IPersistPropBag interfaces.You can use the below code as is. The logic for reading an Excel file will be implemented inside the Execute method under IComponent Interface.

ExcelDecoder.cs (Custom Component File)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using Microsoft.BizTalk.Component.Interop;
using Microsoft.BizTalk.Message.Interop;
using System.IO;
using System.Xml;

namespace Excel.PipelineComponent
{
    [ComponentCategory(CategoryTypes.CATID_PipelineComponent),
    ComponentCategory(CategoryTypes.CATID_Decoder),
    System.Runtime.InteropServices.Guid("C6F8D757-72C3-4587-906A-E1FFCA471B1D")]
    public class ExcelDecoder : Microsoft.BizTalk.Component.Interop.IComponent, IBaseComponent, IPersistPropertyBag, IComponentUI
    {
        private string _RootTag;

        private string _NameSpace;

        public string RootTag
        {
            get
            {
                return _RootTag;
            }
            set
            {
                _RootTag = value;
            }
        }

        public string TargetNameSpace
        {
            get
            {
                return _NameSpace;
            }
            set
            {
                _NameSpace = value;
            }
        }

        #region IBaseComponent members
        /// <summary>
        /// Name of the component
        /// </summary>
        [Browsable(false)]
        public string Name
        {
            get
            {
                return "Decode Excel"; 
            }
        }

        /// <summary>
        /// Version of the component
        /// </summary>
        [Browsable(false)]
        public string Version
        {
            get
            {
                return "1.0"; 
            }
        }

        /// <summary>
        /// Description of the component
        /// </summary>
        [Browsable(false)]
        public string Description
        {
            get
            {
                return "Decodes incoming Excel 2007/2010 File into an XML Message treading first Row as Header"; 
            }
        }
        #endregion

        #region IPersistPropertyBag members
        /// <summary>
        /// Gets class ID of component for usage from unmanaged code.
        /// </summary>
        /// <param name="classid">
        /// Class ID of the component
        /// </param>
        public void GetClassID(out System.Guid classid)
        {
            classid = new System.Guid("C6F8D757-72C3-4587-906A-E1FFCA471B1D");
        }

        /// <summary>
        /// not implemented
        /// </summary>
        public void InitNew()
        {
        }

        /// <summary>
        /// Loads configuration properties for the component
        /// </summary>
        /// <param name="pb">Configuration property bag</param>
        /// <param name="errlog">Error status</param>
        public virtual void Load(Microsoft.BizTalk.Component.Interop.IPropertyBag pb, int errlog)
        {
            object val = null;
            val = this.ReadPropertyBag(pb, "RootTag");
            if ((val != null))
            {
                this._RootTag = ((string)(val));
            }
            val = this.ReadPropertyBag(pb, "NameSpace");
            if ((val != null))
            {
                this._NameSpace = ((string)(val));
            }
        }

        /// <summary>
        /// Saves the current component configuration into the property bag
        /// </summary>
        /// <param name="pb">Configuration property bag</param>
        /// <param name="fClearDirty">not used</param>
        /// <param name="fSaveAllProperties">not used</param>
        public virtual void Save(Microsoft.BizTalk.Component.Interop.IPropertyBag pb, bool fClearDirty, bool fSaveAllProperties)
        {
            this.WritePropertyBag(pb, "RootTag", this.RootTag);
            this.WritePropertyBag(pb, "NameSpace", this.TargetNameSpace);
        }

        #region utility functionality
        /// <summary>
        /// Reads property value from property bag
        /// </summary>
        /// <param name="pb">Property bag</param>
        /// <param name="propName">Name of property</param>
        /// <returns>Value of the property</returns>
        private object ReadPropertyBag(Microsoft.BizTalk.Component.Interop.IPropertyBag pb, string propName)
        {
            object val = null;
            try
            {
                pb.Read(propName, out val, 0);
            }
            catch (System.ArgumentException)
            {
                return val;
            }
            catch (System.Exception e)
            {
                throw new System.ApplicationException(e.Message);
            }
            return val;
        }

        /// <summary>
        /// Writes property values into a property bag.
        /// </summary>
        /// <param name="pb">Property bag.</param>
        /// <param name="propName">Name of property.</param>
        /// <param name="val">Value of property.</param>
        private void WritePropertyBag(Microsoft.BizTalk.Component.Interop.IPropertyBag pb, string propName, object val)
        {
            try
            {
                pb.Write(propName, ref val);
            }
            catch (System.Exception e)
            {
                throw new System.ApplicationException(e.Message);
            }
        }
        #endregion
        #endregion

        #region IComponentUI members
        /// <summary>
        /// Component icon to use in BizTalk Editor
        /// </summary>
        [Browsable(false)]
        public IntPtr Icon
        {
            get
            {
                return System.IntPtr.Zero; // ((System.Drawing.Bitmap)(this.resourceManager.GetObject("COMPONENTICON", System.Globalization.CultureInfo.InvariantCulture))).GetHicon();
            }
        }

        /// <summary>
        /// The Validate method is called by the BizTalk Editor during the build 
        /// of a BizTalk project.
        /// </summary>
        /// <param name="obj">An Object containing the configuration properties.</param>
        /// <returns>The IEnumerator enables the caller to enumerate through a collection of strings containing error messages. These error messages appear as compiler error messages. To report successful property validation, the method should return an empty enumerator.</returns>
        public System.Collections.IEnumerator Validate(object obj)
        {
            // example implementation:
            // ArrayList errorList = new ArrayList();
            // errorList.Add("This is a compiler error");
            // return errorList.GetEnumerator();
            System.Collections.IEnumerator enumerable = null;
            return enumerable;
        }
        #endregion

        #region IComponent members
        /// <summary>
        /// Implements IComponent.Execute method.
        /// </summary>
        /// <param name="pc">Pipeline context</param>
        /// <param name="inmsg">Input message</param>
        /// <returns>Original input message</returns>
        /// <remarks>
        /// IComponent.Execute method is used to initiate
        /// the processing of the message in this pipeline component.
        /// </remarks>
        public Microsoft.BizTalk.Message.Interop.IBaseMessage Execute(Microsoft.BizTalk.Component.Interop.IPipelineContext pc, Microsoft.BizTalk.Message.Interop.IBaseMessage inmsg)
        {
            if (inmsg == null || inmsg.BodyPart == null || inmsg.BodyPart.Data == null)
            {
                throw new ArgumentNullException("pInMsg");
            }

            System.IO.Stream st = inmsg.BodyPart.GetOriginalDataStream();
            string body = string.Empty;

            // CreateXML Message
            body = ReadExcelFileDOMGeneric(st, this._RootTag, this._NameSpace);
            
            System.IO.MemoryStream m = new System.IO.MemoryStream();
            System.IO.StreamWriter writer = new System.IO.StreamWriter(m);
            writer.AutoFlush = true;
            writer.Write(body);
            m.Position = 0;
            inmsg.BodyPart.Data = m;

            return inmsg;
        }


 public string ReadExcelFileDOMGeneric(Stream sdata, string strRoot, string strNamespace)
        {
            string xmlStr = string.Empty;
            using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(sdata, false))
            {
                //Implement the logic to Create string of XML document 
                //on Basis of Give Spread Sheet
            }
            return xmlStr;
        }


        #endregion
    }
}

Once the Custom Pipeline Component is complete we need to Create a pipeline to use this component.

Using pipeline component within a pipeline

-Build the Custom Pipeline Component project and GAC the DLL using gacutil -i command.

-Custom pipeline components  must be placed in a specific BizTalk folder (\Program Files\Microsoft BizTalk Server 2013\Pipeline Components).

-Add a new Pipeline »ExcelReceive.btp »

-In Toolbox, right click and « Choose Items »,and click « Biztalk Pipeline Component » tab and browse to reference « Excel.PipelineComponent.dll » from the GAC « C:\Windows\Microsoft.NET\assembly\GAC_MSIL »

-Drag&Drop the custom component to decode stage as shown below :

Now Deploy the Pipeline and Use it on Receive Location as show below :

Open Pipeline Configurations and Set the Schema Name along with the Root Tag which will be used by pipeline component while creating xml as show below : 

Now the Pipeline to receive Excel File is fully Configured and Ready to Use.

Tip :  You can copy and paste all the code form the above section in the pipeline component and all you need to do is to Implement the logic according to the schema in the ReadExcelFileDOMGeneric function to generate XML.

Regards,

Farhan Jamil

Senior Software Engineer

Systems Limited,Pakistan