Biztalk Development Series (33) BizTalk ultimate Excel Solution

Excel is widely used as an excellent client data processing program. because of its simple and powerful functions, it occupies a very important position in the Data Processing of many companies or individuals. while BizTalk, the main product of Microsoft SOA, provides many adapters for free to support various protocols and applications. however, it is a pity that the "original ecology" Support for rich client Excel is not provided. therefore, we can only find the solution by ourselves.

There are already many solutions for the application to support excel on the Internet. One part is to use the ODBC Excel driver to process excel as a data source. This method is applicable to Excel documents such as standard data tables. karen also released a BizTalk pipeline Excel component. in addition, there is a solution for complicated types of Excel documents, which is to use Excel APIs to Operate Excel cells for processing. however, this method cannot meet the needs of rapid development. when the customer's excel template changes, you need to write the corresponding code. time-consuming and laborious.

So what is a good solution? There are several expectations listed:

• Quick and friendly development methods.
• Strong verification functions
• Retain the original powerful functions of Excel.
• Good End User Experience.

Next we will invite our leading role to play-"XML ing". xml ing allows you to add a custom schema to the workbook, improving support for using the local XML. In this way, you can create an XML ing to make data more meaningful when importing or exporting data. You can also import data that complies with the custom XML vocabulary or architecture, and then write the data back using the same schema. Is an XML schema node associated with an Excel table.

 

After learning about XML ing, we will introduce the Excel solution in BizTalk more intuitively in the form of a case. We all know that many companies require new employees to fill in a New resume based on the company's unified resume template. the following is an Excel document filled out through the resume template. we want employees to submit documents via email or web site. then, store the resume information in the database or send it to the corresponding directory through the BizTalk system.

To achieve this goal, the ODBC Excel driver cannot be used, because it does not have a standard data table. If you use APIs to operate cells, the development workload is too large. If the template needs to be changed, you have to re-develop the program. With XML ing, our process is simple and interesting.

1. First, create a schema document to define the nodes and their structures we need. As shown in.

2. Create an Excel document. In order to give end users a better user experience, we also need to add some necessary instructions and tips to the document. After the creation, load the XML schema and associate the schema node with the file cell. Is a GIF animation that shows how to associate a schema node with a cell.

3. the Excel template has been created. You only need to distribute the template to the user.

4. Now we will make some development on the BizTalk Server. Biztalk does not provide the Excel document operation. Therefore, we need to develop a Pipeline component based on the Excel API to convert the Excel stream into XML Stream by XML ing before it is stored in the MessageBox. the Application of Excel component in receive pipeline design.

5. After deploying the BizTalk application, we can start testing our program. First, use the send and receive document adapters. To process the Excel file into XML. After we copy the Excel file to the receiving directory, we can see the following XML file in the sending directory.

Generated XML document

 

6. OK. Our minimum prototype is implemented. Other business processes and sending targets can be achieved according to actual needs.

7. For the validation of the document content, we can combine the schema validation function XML for verification. For details about this part, see BizTalk Development Series (13) Schema Design Value constraints.

Summary

Although we only introduced the XML ing application in BizTalk for parsing Excel documents today, XML ing can be applied not only to BizTalk applications, but also to BizTalk for receiving and parsing Excel files. You can also export XML files to excel in ASP. NET and other applications. In short, XML ing in Excel and XML-related solutions brings us a very flexible and good experience.

 

[Updated in 2008.12.28]

Excel management and resolution component of this solutionBizexcelAlpha has been released in codeplex. Download and use.

Biztalk Development Series (33) BizTalk ultimate Excel Solution