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.
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;
OriginalfilePath = Path.Combine("C:\temp", Originalfilename);
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();
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;
FileStream fileStream = new FileStream(filename, FileMode.Create,
FileAccess.Write, FileShare.None);
BinaryWriter w = new BinaryWriter(fileStream);
w.BaseStream.Seek(0, SeekOrigin.End);
if (inMsgStream != null)
{
inMsgStream.Seek(0, SeekOrigin.Begin);
int n = 0;
do
{
n = inMsgStream.Read(buffer, 0, bufferSize);
if (n == 0)
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.
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