Processing Excel Documents in Biztalk 2004 (Excel Disassembler)
We all know that excel format is not supported in Biztalk 2004 and in order to process excel you must first buy a third-party excel adapter which I think Itemfield offers and this comes way to expensive since we are going to use it just once. And for this I've develop a solution wherein I can still process excel in Biztalk 2004 without any use of a third party software.
My solution involves creating a customize disassembler pipeline that:
1) converts the stream to excel file using the Binary Writer same as what the File Adapter is using (in this step I also perform backup since it was converted back to its original form), and 2) from excel I save it to Unicode TAB Delimited format using the Excel Interop and save it to a temporary folder 3) open the file using Stream Reader 4) Clean the stream using regular expression (this is where unwanted data are deleted, like the header the subtotals etc. 5) After cleaning pass it back ( inMsg.BodyPart.Data = sw.BaseStream; inMsg.BodyPart.Data.Position = 0; ) 6) Delete the Temporary File
And also the Schema I'll be using is the TAB Delimited format of the data since It was already a clean data.
This is how you can save the Stream back to Excel File or any other format:
public void SaveStreamToFile(Stream inMsgStream)
{
int bufferSize = 4096;
byte[] buffer = new byte[4096];
int numBytesRead = 0;
FileStream fs = new FileStream( tmpExcelFileName,FileMode.CreateNew );
// Setup the stream writter and reader
BinaryWriter w = new BinaryWriter(fs);
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();
}
Legacy Comments
|
ba
2006-03-02 |
re: Processing Excel Documents in Biztalk 2004 (Excel Disassembler) Thanks, looking for something like this! |