← Back to blog

How to make Excel awesome with Flowgear – Part 2

August 7, 2015

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.

flowgear-connector2

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.

excel-connectors

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.

spreadsheet1

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.

excel-workbook-connector

Provide XML to set input ranges as shown below.

flowgear-input-xml

Finish connecting up your workflow and click Play.

flowgear-workflow2

When the Workflow completes, OutputRangeXml will contain a value like this.

flowgear-output-xml

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.

Flowgear excels against magic quadrant leaders

See what Gartner and G2 Crowd have to say.

Flowgear 101

Have a quick look under the hood to see how Flowgear can help drive value for your business.

Build Powerful Application Data API Integrations