BizTalk Server: Receive and Send Excel Files from Existing Templates

Article
1/17/2024

Introduction

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.

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

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

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

Share via