BizTalk Custom Pipeline for Excel 2007 (xlsx)


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!

Advertisements

12 Responses

  1. 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

    • 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

      • 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?

  2. 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

    • 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

  3. 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

    • 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

      • 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.

      • 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

  4. HI Dipesh,

    Am getting the below error while testing with the sample file you had provided in this solution.
    There was a failure executing the receive pipeline: “IntegrationExperts.Samples.Pipelines.ReceivePipeline1, IntegrationExperts.Samples.Pipelines, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b4b4fd79102b4431” Source: “XML disassembler” Receive Port: “ReceivePort5” URI: “C:\Users\Administrator\Desktop\Ranjeet\Source_v1.0\Excel2007Pipeline\In\*.xlsx” Reason: The envelope structure for document specification type “http://IntegrationExperts_v1.0#Root” is not valid.
    Could you please help me on the same.

    • Hi,

      Set the following property in the rec pipeline properties and retry.

      RootNodeName: CustomerInfo

  5. Even am getting the same error.Any resoolution for the same.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: