Excel Reading Custom Pipeline for BizTalk Server





4.00/5 (2 votes)
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.