Click here to Skip to main content
15,867,141 members
Articles / Web Development / ASP.NET

Excel Reading Custom Pipeline for BizTalk Server

Rate me:
Please Sign up or sign in to vote.
4.00/5 (2 votes)
22 Jun 2009CPOL1 min read 54K   1.1K   10   11
This article will describe how to read an Excel file using a custom pipeline.

Introduction

This document aims to discuss how to read an Excel file from a pipeline in BizTalk. A custom pipeline need to be developed to read Excel files. Excel file will be read by taking the file adaptor and the pipeline will process it. A custom pipeline component is nothing more than a simple .NET DLL that implements a class with some predefined interfaces. This interface represents the layer between a .NET program and the BizTalk Server.

Using the code

We need to implement the IBaseMessage, IComponent, IComponentUI, and IPersistPropBag interfaces.

IBaseMessage

The logic for reading an Excel file will be implemented inside the Execute method.

C#
/// <summary>
/// Implements IComponent.Execute method.
/// </summary>
/// <param name="pc">Pipeline context</param>
/// <param name="inmsg">Input message.</param>
/// <returns>Processed input message with appended or prepended data.</returns>
/// <remarks>
/// IComponent.Execute method is used to initiate
/// the processing of the message in pipeline component.
/// </remarks>
public IBaseMessage Execute(IPipelineContext pc, IBaseMessage inmsg)
{
    IBaseMessagePart bodyPart = inmsg.BodyPart;
    IBaseMessageContext context = inmsg.Context;
    string Originalfilename = "";
    string OriginalfilePath = "";
    string Exceptionsfilename = "";
    string ExceptionsfilePath = "";
    Originalfilename = context.Read("ReceivedFileName", 
        "http://schemas.microsoft.com/BizTalk/2003/file-properties").ToString();
    Originalfilename = Originalfilename.Substring(
             Originalfilename.LastIndexOf("\\") + 1);
    Exceptionsfilename = Originalfilename;
    //Get the Original file path to save
    OriginalfilePath = Path.Combine("C:\temp", Originalfilename);
    //Get the Exception file path to save
    ExceptionsfilePath = Path.Combine("C:\temp", Exceptionsfilename);
    if (bodyPart!=null)
    {
        byte[] prependByteData = ConvertToBytes(prependData);
        byte[] appendByteData = ConvertToBytes(appendData);
        Stream originalStrm = bodyPart.GetOriginalDataStream();
        string Archivepath = string.Empty;
        
        if (originalStrm != null)
        {
            MemoryStream memmorystream = new MemoryStream();
            XmlDocument xmldocument = new XmlDocument();
            //Save file in temp directory
            Archivepath = SaveStreamToFile(originalStrm, OriginalfilePath);
            bodyPart.Data = memmorystream;
        }

    }
    return inmsg;
}

public string SaveStreamToFile(Stream inMsgStream, string filename)
{
    int bufferSize = 4096;
    byte[] buffer = new byte[4096];

    int numBytesRead = 0;
    //string tempPath = Path.Combine(Path.GetTempPath(), Path.GetTempFileName());
    FileStream fileStream = new FileStream(filename, FileMode.Create, 
                                FileAccess.Write, FileShare.None);

    // Setup the stream writter and reader
    BinaryWriter w = new BinaryWriter(fileStream);

    w.BaseStream.Seek(0, SeekOrigin.End);

    if (inMsgStream != null)
    {
        inMsgStream.Seek(0, SeekOrigin.Begin);

        // Copy the data from the msg to the file
        int n = 0;

        do
        {
            n = inMsgStream.Read(buffer, 0, bufferSize);

            if (n == 0) // We're at EOF
                break;

            w.Write(buffer, 0, n);
            numBytesRead += n;

        } while (n > 0);
    }

    w.Flush();

    w.Close();
    return filename;
}

Compiling the project

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

  • Right click on the Excel pipeline and deploy the project.
  • Go to BizTalk Server and create a new application.
  • Configure the file adaptor to read the Excel file.
  • Select the pipeline as an Excel pipeline just before you deploy it.
  • Drop the Excel file in to an inbound folder
  • It will read from the BizTalk custom pipeline and copy to your configure folder.

You will be able to read cell by cell by writing a method and calling from the above method.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Singapore Singapore
Pubudu Kasakara is working as Solution Architect in Singapore. He has more than 11 years of professional involvement in the IT industry. Worked in successful projects involving mainly the Service Oriented Application by contributing to all requirements gathering, design and implementation phases. He is having experience with SOA applications development, Microsoft .NET, BizTalk, He is member of Charted It professional in UK.

Solution Architect
http://www.kasakara.com

Comments and Discussions

 
Questioncan u share corresponding application of biztalk server 2010 for this excel custom pipeline? Pin
sangeetha m1-Nov-12 0:01
sangeetha m1-Nov-12 0:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.