Click here to Skip to main content
Click here to Skip to main content

Excel Reading Custom Pipeline for BizTalk Server

, 22 Jun 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

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

Share

About the Author

Pubudu Kasakara
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 pls send the Biztalk Applicaion for this sample !! Pinmembervpng17-Oct-13 23:57 
Questionhow to create schema for excel file? Pinmembersangeetha m1-Nov-12 1:25 
Questioncan u share corresponding application of biztalk server 2010 for this excel custom pipeline? Pinmembersangeetha m1-Nov-12 1:01 
QuestionDoes this require Excel to be installed on the server? PinmemberRod Hall24-Sep-12 23:34 
QuestionHow to debug this custom pipleline? PinmemberMember 26374251-Mar-10 1:39 
hai Kasakara thank for ur article i run this sample..
but i could not able to get output.. its showing 0 kb bytes file in output folder.
is it possible to debug this component?
AnswerRe: How to debug this custom pipleline? PinmemberMember 26374251-Mar-10 2:27 
GeneralRe: How to debug this custom pipleline? PinmemberPubudu Kasakara24-Mar-10 4:13 
AnswerRe: How to debug this custom pipleline? PinmemberPubudu Kasakara15-Jun-10 0:16 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141216.1 | Last Updated 22 Jun 2009
Article Copyright 2009 by Pubudu Kasakara
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid