Wednesday 6 February 2013

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




No comments:

Post a Comment