BizTalk Custom Pipeline for Excel 2007 (xlsx)
Posted by DipeshA on May 12, 2011
Hi there,
I have just released a custom pipeline to process and debatch xlsx (Excel 2007) files in BizTalk Server 2009 and up. The purpose of this custom pipeline is to decode the xlsx files and convert it to xml. This component also adds a namespace and debatches the xml files into individual records.
The following properties are available to the developer at design time:
NamespaceBase: – The new namespace to be inserted.
NamespacePrefix: – The namespace prefix to use.
RootNodeName: – Name of the root node after debatching the records.
IsFirstRowHeader: – Flag to indicate if the first row represents column names.
You can download it from codeplex:
http://excel2007pipeline.codeplex.com/
Cheers!
Vivek said
I am getting this error while I run your app, can you help ?
A message received by adapter “FILE” on receive location “Receive Location1″ with URI “D:\TestExcel\In\*.xlsx” is suspended.
Error details: There was a failure executing the receive pipeline: “IntegrationExperts.Samples.Pipelines.XslxReceive, IntegrationExperts.Samples.Pipelines, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b4b4fd79102b4431″ Source: “XML disassembler” Receive Port: “Rp1″ URI: “D:\TestExcel\In\*.xlsx” Reason: The body part is NULL.
MessageId: {5E1637A6-4F22-4F06-A8B9-1EB92CC269D8}
InstanceID: {F80DC350-7C96-4A48-893A-9E3937F186B6}
vivek
DipeshA said
Hi Vivek,
Are you using the same document in the sample? The issue is the document body was not found after unzipping the xlsx file.
Cheers,
Dipesh
Vivek said
Could you be more elobrate ? Can you tell me what changes i need to do in sample.xlsx file to get this working ?
You are using a third party dll ICSharpCode.SharpZipLib, can we avoid this and write code in .net itself or with some microsoft dlls?
I am very curious to know more about why all the codes using memory stream when it come to pipeline component?
Vivek said
Hi Dipesh,
I got it working I added the ICSharpCode.SharpZipLib.dll in both pipeline components folder as well as added this as resource in application, it started working great. Can you explain the need of this Zip functionality here ?
Thank you very much for your valuable post
DipeshA said
Hi Vivek,
Office 2007+ files are open XML format. What that means is the office files are made up of xml data and mapping files to form the xlsx, docx etc. To get access to these data and mapping files the office documents need to be “unzipped”. This is where the SharpZipLib dll comes in. All it does is unzips the office document into a folder where additional parsing (mapping and data extraction) is done via code.
Hope this helps!
Cheers,
Dipesh
Remco said
Hi Dipesh,
Great article. Just what I needed.
But…… I installed the MSI file at a Win 2008 R2 server with BizTalk 2010. After the xlsx file, which you included is picked up, the file will be suspend. The error message:
There was a failure executing the receive pipeline: “IntegrationExperts.Samples.Pipelines.XslxReceive, IntegrationExperts.Samples.Pipelines, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b4b4fd79102b4431″ Source: “Unknown ” Receive Port: “Rp1″ URI: “c:\TestExcel\In\*.xlsx” Reason: Could not load file or assembly ‘file:///C:\Program Files\Microsoft BizTalk Server 2010\Pipeline Components\IntegrationExperts.Samples.PipelineComponents.dll’ or one of its dependencies. The system cannot find the file specified.
Thus re-added the resource files and point the receive location to the XlsxReceive pipeline component. But the error will stay.
I imported the source code into VS 2010, but there is an conflict in the code. The errors:
Error 1 The namespace ‘IntegrationExperts.Samples.PipelineComponents’ already contains a definition for ‘Excel2007Decoder’ C:\Users\A153655\Documents\Visual Studio 2010\Projects\Excel 2007 BT Pipeline\Excel2007Pipeline\IntegrationExperts.Samples.PipelineComponents\Excel2007Decoder.Designer.cs 25 20 IntegrationExperts.Samples.PipelineComponents
Error 2 Partial declarations of ‘IntegrationExperts.Samples.PipelineComponents.Excel2007Decoder’ have conflicting accessibility modifiers C:\Users\A153655\Documents\Visual Studio 2010\Projects\Excel 2007 BT Pipeline\Excel2007Pipeline\IntegrationExperts.Samples.PipelineComponents\Excel2007Decoder.cs 45 18 IntegrationExperts.Samples.PipelineComponents
Please can you help me out?
Remco
DipeshA said
Try copying the pipeline component dll in the ‘pipeline components’ folder under the BT folder in program files and restart host instance to test.
Dipesh
Ruslan said
Hi, Whenever users click the existing XLSX link in our SharePoint site, they seem to get a borekn link. DOCX and PPTX files work just fine.I tried the solution in your article, but to no avail. Do you have any suggestions how I can try to resolve this issue?Thank you.
DipeshA said
Hi Ruslan,
I dont think I understand your question. Could you please explain your scenario in details and what is it you want to do?
Cheers,
Dipesh