In the previous blog we introduced the Flowgear-Excel integration and how it adds awesomeness to using Excel. In this post we will go into some basic examples. To recap, Excel integration is useful for outsourcing domain-specific number crunching and application logic that a business user can own and understand. A developer can treat the user’s business rules as a black box and focus on getting the data in and mapped against the appropriate rules.
Microsoft has supported the XML format since 2000, and with the release of the Office Open XML(OpenXML) format, the company took another major step forward in enhancing the ability to exchange data between applications. OpenXML lets developers write XML scripts that can read and update Excel spreadsheets.
For an Excel integration, Flowgear requires that a DropPoint be used. You’ll also need to set up your DropPoint to run under a named user account. Next, set up a connection as shown in the example below and be sure to reference the DropPoint you registered.
Now it’s time to build the workflow. Here are the available connectors – you can learn more by dropping them on the Flowgear design canvas, right-clicking and choosing Help.
The connector we’re interested in today is “Excel Workbook”.
Create a spreadsheet. In the example below, the blue cells are named input ranges while the green cells are named output ranges.
Save your spreadsheet on the same server your DropPoint is installed. On the Flowgear design canvas, drop an Excel Workbook connector, select the connection you set up earlier and fill in the worksheet name.
Provide XML to set input ranges as shown below.
Finish connecting up your workflow and click Play.
When the Workflow completes, OutputRangeXml will contain a value like this.
This very simple example shows how we have made Excel reuseable via Flowgear. Of course, in the real world, you won’t be providing hardcoded XML. Instead, that XML could be coming from anywhere. For example, you might need to provide XML containing rows of sales order lines so pricing can be calculated.
We have provided a sample spreadsheet with a simple pricing matrix and sales order calculation which you can download.
