As a service provider we provide a service to multiple customers and channels.
This is a new requirement that we are expecting to receive from each customer a specific Excel file and send 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.
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
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
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 each. I concluded that the best API I which
can be used and supports both xls and xlsx extensions with the best performance is NPOI API.
You can find the api in this link http://npoi.codeplex.com
I will give one example how to prepare a template file from a sample of customer Excel file.
Figure 3. Sample Excel Document
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 built 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 schema
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:
Figure 7. Adding Excel decoder and encoder
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
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_ExcelLocand I will configure the Rcv_ExcelLoc as figure 11
Configuring receive port
Figure 12. Configuring custom receive pipeline properties
Generated xml file
In this article I demonstrated the BizTalk approach for receiving and sending Excel documents using existing templates for multiple customers.
The challenge is how to work and deal with APIs, then this approach will be easy to maintain and reuse.
Another important place to find an extensive of BizTalk related articles is the TechNet Wiki
itself. The best entry point is BizTalk
Server Resources on the TechNet Wiki.