Excel Reading Custom Pipeline for BizTalk Server

• Download source code - 553 KB

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.

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