Wednesday, November 27, 2013

Receive and Send Excel Files from Existing Templates using BizTalk Server



Introduction

As a service provider that we provide a service to multiple customers and channels.
There is a new requirement that we are expecting to receive from each customer a specific excel file and sending a different excel file.
You can imagine that we could receive hundreds of different excel files from customers.
Also, we have an integration point for each customer that we need to map the customer format file to our canonical schema.

BizTalk Solution Approach


We need to build two pipelines one for receiving and one for sending excel files.
For receiving we need to parse an excel file using the configured template file to generate an xml file matching an existing customer schema as demonstrated in Figure 1
Figure 1. Receiving Excel Pipeline Diagram
For sending, we need to convert xml file to generate an excel file via custom sending pipeline using an existing excel template as demonstrated in figure 2
Figure 2. Sending Excel Pipeline Diagram



What is the best excel API to be used?



There are some of free excel APIs that you can use to process excel file like ExtremeML, Microsoft Excel Object Library, epplus and NPOI.I read a lot of articles and reviews and I tried to do some of POCs for eachI concluded that the best API I can be used and support both xls and xlsx extensions with the best performance is by using NPOI API.
You can find the api in this link http://npoi.codeplex.com


How to Prepare Excel template file?



I will give one example how to prepare a template file from a sample of customer excel file.

Let's say we got the file from customer A as Figure 3

Figure 3. Sample Excel Document

We need to know where the location of repeated records and where are the other fields.
First we need to prepare the template manually by making a place holder for each column name of repeated records and a label as a comment (DATAROW) where we can find the first repeated record then the place holders of other fields like figure 4



Figure 4. Preparing Excel Template Document

Then we need to generate a schema depending on a template file that we build it on figure 4
I build a simple tool to generate a schema from an existing template  as Figure 5 or you can create schema manually



Figure 5. Tool to generate xsd from Existing Template


The tool will generate the schema as Figure 6


Figure 6. Generated xml scehma





How to configure the excel processor custom pipeline?




I checked NPOI api examples and I built my own custom pipeline component ExcelProcessor.BizTalk.PipelineComponents.dll and I copy it to this folder %BTSInstallPath%/Pipeline Components and you need to add any dependents dlls to GAC like the following dlls:

ICSharpCode.SharpZipLib.dll
NPOI.dll
NPOI.OOXML.dll
NPOI.OpenXml4Net.dll
NPOI.OpenXmlFormats.dll


for more information check Developing Custom Pipeline Components

Then you need to add a new receive and send pipeline then right click to Toolbox then select choose items and select  as Figure 7



 Figure 7. Adding excel decoder and encoder

Open Your receive pipeline then add ExcelPipelineDecoder from ToolBox and drag it to Decode part as Figure 8

Figure 8. Excel Receive Pipeline

Open Your send pipeline then add ExcelPipelineEncoder from ToolBox and drag it to Encode part as Figure 9


Figure 9. Excel Send Pipeline
Now your pipelines are ready to be deployed. Deploy your BizTalk project then open BizTalk Administration to configure pipelines
For testing purposes, I just added CustomerA.xsd and receive and send pipelines as Figure 10


Figure 10. BizTalk Solution Explorer


Now I will create one receive port as Rcv_Excel and a receive location as Rcv_ExcelLoc
and I will configure the Rcv_ExcelLoc as figure 11


Figure 11. Configuring receive port

Then we need to configure path of the template and TargetNameSpace of schema that related to excel template for ExcelReceivePipeline as figure 12


Figure 12. Configuring custom receive pipeline properties

for testing purposes I created a send port with XmlTransmit Send Pipeline and filtered it to BTS.ReceivePortName==Rcv_Excel
and it generates the following output as figure 13


Figure 13. Generated xml file




Conclusion



In this article I demonstrated the BizTalk approach for receiving and sending excel documents using existing templates for multiple customers.

You can use this approach for building BizTalk pipelines from existing templates for Excel, pdf ,word and any type of templates.

The challenge is how to work and deal with APIs then this approach will be easy to maintain and reuse.





See Also


Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.

3 comments:

Unknown said...
This comment has been removed by the author.
Unknown said...

hey, I'm doing this exercise and i'm having some problems developing the pipeline, decode, can you publish the pipeline code if possible! best regards!

Suleiman Shakhtour said...

Hi Pedro,

You can find my comments in the following link

https://social.msdn.microsoft.com/Forums/en-US/5ce981f2-d5b7-41f6-b553-c717c565529d/reading-and-processing-excel-file-xlsx-in-biztalk-2010?forum=biztalkgeneral#da9b1769-7c6d-4a4c-ada0-e800e7233349