Send Pipeline for Excel - Biztalk

Create custom pipeline component for convert XML to Excel .

Input XML:

<PARTS>

   <PART>

      <ITEM>Motherboard</ITEM>

      <MANUFACTURER>ASUS</MANUFACTURER>

      <MODEL>P3B-F</MODEL>

      <COST> 123.00</COST>

   </PART>

   <PART>

      <ITEM>Video Card</ITEM>

      <MANUFACTURER>ATI</MANUFACTURER>

      <MODEL>All-in-Wonder Pro</MODEL>

      <COST> 160.00</COST>

   </PART>

   <PART>

      <ITEM>Sound Card</ITEM>

      <MANUFACTURER>Creative Labs</MANUFACTURER>

      <MODEL>Sound Blaster Live</MODEL>

      <COST> 80.00</COST>

   </PART>

   <PART>

      <ITEM>Monitor</ITEM>

      <MANUFACTURER>LG Electronics</MANUFACTURER>

      <MODEL> 995E</MODEL>

      <COST> 290.00</COST>

   </PART>

</PARTS>

Create Encoder Component for convert  XML to Excel

Add reference of “Microsoft.Office.Interop.Excel.dll” from path:” C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Office.Interop.Excel.dll”

Code for Encoder

using System;

using System.Collections.Generic;

using System.Text;

using System.Xml;

using System.IO;

using Excel = Microsoft.Office.Interop.Excel;

using Microsoft.BizTalk.Message.Interop;

using Microsoft.BizTalk.Component.Interop;

using Microsoft.BizTalk.ParsingEngine;

using Microsoft.BizTalk.Component;

using System.Reflection;

using System.Data;

namespace CustomLibraries.CustomPipelineComponent

{

    [ComponentCategory(CategoryTypes.CATID_PipelineComponent)]

    [ComponentCategory(CategoryTypes.CATID_Encoder)]

    [System.Runtime.InteropServices.Guid("25984614-BCFD-4c47-82FC-4A2300B76411")]

    public class Encoder : IBaseComponent, IPersistPropertyBag, IComponentUI, IComponent

    {

        #region Private members

        IBaseMessagePart msgPart;

        System.Collections.Queue qOutputMsgs = new System.Collections.Queue();

        string textProperty = string.Empty;

        #endregion

        #region Constructors

        public Encoder()

        {

        }

        #endregion

        #region IBaseComponent Members

        public string Description

        {

            get { return "ExcelEncoder"; }

        }

        public string Name

        {

            get { return "Excel Encoder"; }

        }

        public string Version

        {

            get { return "1.0.0.0"; }

        }

        #endregion

        #region IPersistPropertyBag Members

        public void GetClassID(out Guid classID)

        {

            classID = new Guid("25984614-BCFD-4c47-82FC-4A2300B76438");

        }

        public void InitNew()

        {

        }

        public void Load(IPropertyBag propertyBag, int errorLog)

        {

            object val = ReadPropertyBag(propertyBag, "TextProperty");

            if (val != null)

                textProperty = (string)val;

        }

        public void Save(IPropertyBag propertyBag, bool clearDirty, bool saveAllProperties)

        {

            object val = textProperty;

            propertyBag.Write("Namespace", ref val);

        }

        #endregion

        #region IComponentUI Members

        public IntPtr Icon

        {

            get { return System.IntPtr.Zero; }

        }

        public System.Collections.IEnumerator Validate(object projectSystem)

        {

            return null;

        }

        #endregion

        #region IComponent Members

        public IBaseMessage Execute(IPipelineContext pContext, IBaseMessage pInMsg)

        {

            msgPart = pInMsg.BodyPart;

            Stream originalStream = pInMsg.BodyPart.GetOriginalDataStream();

            Stream OutStream = originalStream;

            try

            {

                if (msgPart != null)

                {

                    if (originalStream != null)

                    {

                        Excel.Application oXL;

                        Excel.Workbook oWB;

                        Excel.Worksheet oSheet;

                        // Start Excel and get Application object.

                        oXL = new Excel.Application();

                        // Set some properties

                        oXL.Visible = true;

                        oXL.DisplayAlerts = false;

                        // Get a new workbook.

                        oWB = oXL.Workbooks.Add(Missing.Value);

                        // Get the active sheet

                        oSheet = (Excel.Worksheet)oWB.ActiveSheet;

                        oSheet.Name = "DataQuery";

                        System.Data.DataSet _ds = new DataSet();

                        _ds.ReadXml(originalStream);

                        // Process the DataTable

                        System.Data.DataTable dt = _ds.Tables[0];

                        int rowCount = 1;

                        foreach (DataRow dr in dt.Rows)

                        {

                            rowCount += 1;

                            for (int i = 1; i < dt.Columns.Count + 1; i++)

                            {

                                // Add the header the first time through

                                if (rowCount == 2)

                                {

                                    oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;

                                }

                                oSheet.Cells[rowCount, i] = dr[i - 1].ToString();

                            }

                        }

                     

                        oSheet = null;

                        string FileName = System.IO.Path.GetTempFileName();

                        oWB.SaveAs(FileName, Excel.XlFileFormat.xlWorkbookNormal,

                  Missing.Value, Missing.Value, Missing.Value, Missing.Value,

                  Excel.XlSaveAsAccessMode.xlExclusive,

                  Missing.Value, Missing.Value, Missing.Value,

                  Missing.Value, Missing.Value);

                        oWB.Close(Missing.Value, Missing.Value, Missing.Value);

                        oWB = null;

                        oXL.Quit();

                        OutStream = ReadFileToMemoryStream(FileName);

                    }

                }

            }

            catch (Exception)

            {

                throw;

            }

            OutStream.Seek(0, SeekOrigin.Begin);

            msgPart.Data = OutStream;

            IBaseMessage outMsg = pInMsg;

            outMsg.BodyPart.Data = OutStream;

            return outMsg;

        }

        #endregion

        private Stream ReadFileToMemoryStream(string FileName)

        {

            FileStream file = new FileStream(FileName, System.IO.FileMode.Open);

            MemoryStream memStream = new MemoryStream();

            byte[] tmpBuff = new byte[200];

            int bytesRead = 0;

            bytesRead = file.Read(tmpBuff, 0, tmpBuff.Length);

            memStream.Write(tmpBuff, 0, bytesRead);

            bytesRead = file.Read(tmpBuff, 0, tmpBuff.Length);

            while (bytesRead != 0)

            {

                memStream.Write(tmpBuff, 0, bytesRead);

                bytesRead = file.Read(tmpBuff, 0, tmpBuff.Length);

            }

            file.Close();

            memStream.Position = 0;

            return memStream;

        }

        private object ReadPropertyBag(IPropertyBag propertyBag, string propName)

        {

            object val = null;

            try

            {

                propertyBag.Read(propName, out val, 0);

            }

            catch (ArgumentException)

            {

                return val;

            }

            catch (Exception e)

            {

                throw new System.ApplicationException(e.Message);

            }

            return val;

        }

    }

}

If you have some Error like

Exception from HRESULT: 0x800A03EC

Do Following Steps

Then you have to give iis or server permission

1. Login to the server as a administrator.
2. Go to "Start" -> "Run" and enter "taskmgr"
3. Go to the process tab in task manager and check "Show Processes from all
   users"
4. If there are any "Excel.exe" entries on the list, right click on the entry and select "End Process"
5. Close task manager.
6. Go to "Start" -> "Run" and enter "services.msc"
7. Stop the service automating Excel if it is running.
8. Go to "Start" -> "Run" and enter "dcomcnfg"
9. This will bring up the component services window, expand out "Console Root" -> "Computers" -> "DCOM Config"

10.  Find "Microsoft Excel Application" in the list of components.

11.  Right click on the entry and select "Properties"

12.  Go to the "Identity" tab on the properties dialog.

13.   Select "The interactive user."

14.  Click the "OK" button.

15.  Switch to the services console

16.  Start the service automating Excel

17.  Test you application again.

Output Excel :

ITEM	MANUFACTURER	MODEL	COST
Motherboard	ASUS	P3B-F	123
Video Card	ATI	All-in-Wonder Pro	160
Sound Card	Creative Labs	Sound Blaster Live	80
Monitor	LG Electronics	995E	290

The sample code is available for download in Code Gallery:.