IntegrationExperts.Samples.Pipelines.XlsxReceive

Description:

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 sample application can be downloaded where schemas are defined to facilitate debatching of this sample excel file. You will need to define your own schemas based on the structure of your excel files. The pipeline uses SharpZipLib libraries (http://www.sharpziplib.com/) to unzip xlsx files.

Regular expressions are used to ensure blank cells are handled in the excel 2007 format.

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.

Note:
This solution is based on the VirtualStream supported in BizTalk Server 2006 and up. The caching is set to to disk. The default location is for this file is 'C:\Documents and Settings\<BTSHostInstanceName>\Local Settings\Temp'. For performance reasons this location should be moved to a non OS-Drive. Make sure that BizTalk Host Instance account has full control of this folder.

I have created a sample xlsx file as under which will be processed (and debatched) by this pipeline.

There are 10 records within this spreadsheet which we need to convert and debatch. Once this file is dropped in the input folder, BizTalk grabs the file and writes debatched records to file.

One of the xml files looks like this:

---------------------

Visit my blog for more BizTalk articles and posts:

http://dipeshavlani.net

Dipesh Avlani

 

Last edited Jun 14, 2011 at 5:32 AM by avlanidipesh, version 14