WFS Reader Add-In for Microsoft Excel 2003

Project Overview

The idea behind the project was to create a simple easy-to-use WFS client tool that will allow to import a WFS response straight into Excel spreadsheet. It doesn’t require any technical skills at all as add-in is implemented as a friendly GUI application. Due to the nature of Excel spreadsheets, all structured data (WFS responses) is then converted into denormalised form – a table where rows represent features and columns contain property values associated with those features. WFS Reader Add-In is implemented as an Application-Level add-in, thus it may be invoked from any workbook loaded into Excel. Generated worksheets have no dependencies on the tool and may be saved and then loaded on other machine, where WFS Reader Add-In is not installed.

The tool is not restricted to simple features only, but may also consume complex features. Features with multi-valued properties are also supported via the use of merged cells.

In the first instance, WFS Reader Add-In should be considered as a data visualisation tool and produced spreadsheet may or may not be suitable for automated data analysis. During the data denormalisation phase some valuable properties of structured data, such as relationships, metadata, some attributes, may be omitted.

What's new?

4 January 2011

Web service interface for WFS Reader has been released!
See WFS Reader Web Service for details.

Prerequisites

WFS Reader Add-In has been developed for and tested on Microsoft Office Excel 2003. Due to technical differences in Office versions and VSTO runtime libraries the installation won't proceed under newer versions of Microsoft Office. If you already have VSTO 2005 runtime installed on your PC, you will need to install VSTO 2005 SE runtime, which will replace its own predecessor.

Installing Add-In

  • In the first instance, installer will perform an environment check and then will attempt to install prerequisites if required. The following checks will be performed:
    • Microsoft Office/Excel 2003 Primary Interop Assemblies (PIAs)
    • Microsoft Visual Studio 2005 Tools for Office Second Edition runtime

  • Once all prerequisites are installed it will run WFS Reader Add-In Setup automatically.

  • Step 1:

  • Step 2: Select Installation Folder

  • Step 3: Confirm Installation

  • Step 4:

  • Step 5: Installation has been completed successfully
WFS Reader Add-In will appear in the “Control Panel / Add or Remove Programs” applet. To uninstall it see Uninstalling Add-In section below.

Usage

Once installation has completed, WFS Reader Add-In will automatically appear in your Microsoft Excel. To enable it right click on any toolbar and select WFS Reader from the context menu.

A new toolbar will come up:

The interface is consisted of:
  • Address bar
  • - Displays "Open file" dialog, which allows to import WFS response from an XML file.
  • - Makes WFS request and places results into a new worksheet.
  • - Displays “Advanced Import” interactive dialog that allows to specify additional query options interactively.
  • - Displays “Query Builder” dialog that allows to load saved queries, amend XML query filters interactively and to make HTTP requests using POST method.
  • - Displays “About” dialog box.

Configuring Feature Type Mappings

Note: Feature type mapping configuraton mechanism became available from version 1.1 (SVN Rev. 416).

WFS Reader Add-In uses local repository for feature type mapping configuration files. It scans the repository at start-up and loads all valid configuration files in memory (invalid or unrecognised files are skipped). Generally, it happens only once, when add-in is being loaded into Microsoft Office Excel. However, the user may force add-in to reload configuration files on demand.

A feature type mapping is an XML encoded file that defines rules how to map the structured XML data to the columns in an Excel spreadsheet. Its syntax allows to define multiple mappings for different feature types in a single configuration file. If multiple mappings for the same feature type are encountered in the same or different configuration files, the first mapping rule is applied.

Currently loaded configuration files and feature type mappings they define may be seen in "About" dialog. On the WFS Reader Add-In toolbar click button to open "About" dialog. All recognised mappings are listed in the "Configurations" list box.

All feature type mappings listed there are separated into groups defined by the configuration file name. Each of them contains a list of feature types defined in that configuration file.

Next to "Configurations" section name you may find two links:
  • "Browse repository" link opens the local configuration repository ( %INSTALLATION_DIRECTORY%/config/) in the file explorer.
  • "Reload" link forces add-in to reload configuration files from the repository.
By default, WFS Reader Add-In comes with two sample configuration files (for EarthResourceML and GeoSciML feature types) and configuration.xsd XML Schema. Basically, in order to add a new feature type mapping, you need to create a new valid configuration file that is conformant to that XML Schema and to place it into repository. On the next reload it'll be picked up automatically by the add-in.

Instructions on how to create your own configuration file see in the Configuration Cookbook.

Fetching WFS Results

To run a query with all default settings all you need to do is to specify WFS URL in the address bar and to click Fetch button. By default, all results will be placed into a separate worksheet of the current workbook. Maximum number of features is only limited by the maxFeatures query string attribute, if present. Otherwise, all features will be returned.

Once the operation is complete you will be brought to the newly created worksheet:

WFS query is issued as an HTTP GET request and may contain all attributes supported by the standard, including a filter. However, it should be taken into account, that due to restrictions of Excel embedded controls and HTTP protocol, the total length of URL is limited.

Advanced Fetching Dialog

Advanced Fetching Dialog shown below allows to set a few more options for a WFS request.

WFS URL is taken from address box of the WFS Reader toolbar.

Max features option specifies how many records should be retrieved from the WFS. If this option is set to "0" (zero), then all features will be imported. Be aware, that this value will overwrite the corresponding maxFeatures query string attribute in the WFS URL or WFS query if Query Builder has been used.

Next to WFS URL text field two more buttons are located. First button opens an interactive dialog box that allows the user to choose an XML file where to fetch the data from. When the data is fetched from a file "Max features" option is ignored. Second button opens the Query Builder dialog, which allows to construct a custom WFS request issued by HTTP POST method.

Feature type is automatically populated once pre-fetch operation is complete with the name of retrieved feature.

Insert mode options allows to instruct add-in where to place WFS results to. The options available are:
  • In a new sheet - Default. WFS results will be placed into a separate sheet.
  • At cursor position - WFS results will be placed into the current sheet at cursor position.
  • As object - WFS results will be placed into the current sheet as a vector graphics object.
  • As embedded document - WFS results will be placed into the current sheet as an OLE object.
Auto fit columns option – when it’s checked the width of columns will be automatically adjusted to fit the data.

Pre-fetch data link will populate columns list allowing the user to choose what data fields to import.

Columns list displays all properties of the feature returned by the WFS. The list is populated with the data once pre-fetch operation is complete, and the feature type returned is recognised by the WFS Reader Add-In.

Query Builder

Note: Query Builder feature became available from version 1.1.18 (SVN Rev. 436).

Query Builder dialog shown below allows the user to load saved queries, amend WFS filters interactively and to make HTTP requests using POST method.

Queries are automatically pre-loaded from personalized application settings. By default, at the first run this list is populated with four basic WFS query templates for different scenarios. You can switch between them using corresponding combo box.

To save your changes click Save button next to "Saved queries" combo box. To create a new query, give it a new name and click "Save As" button. If you want to delete a query from the list, choose it from the combo box and click "Delete" button.

Service URL text box displays the URL of WFS service that will be used for your request.

In the text box below you can edit your WFS query interactively.

Once you finished with it, click OK to proceed. If you haven't saved your changes, you will be prompted to do so. When done the query will be posted to the WFS service using HTTP POST method. The same processing logic will be applied to the WFS response as in case of using HTTP GET method.

Uninstalling Add-In

Before uninstalling WFS Reader Add-In ensure that all instances of Microsoft Office Excel are closed. Then open “Add or Remove Programs” applet from the Control Panel. Find and select “WFS Reader Add-In” program from the list, and then click Remove button.

In the dialog box appeared confirm your action. WFS Reader Add-In will be then uninstalled from your PC and all associated files will be removed from the system, including all feature type mapping configuration files, which you may have added into repository.

Prerequisites will remain installed because both Microsoft Office Excel 2003 Primary Interop Assemblies (PIAs) and VSTO 2005 SE runtime are system-wide shared libraries, which may also be used by other applications. You should NOT remove/uninstall them manually unless you are fully confident that they aren’t used by anything else on your system.

Related Links

Ideas

  • Automatic feature type recognition for simple features using DescribeFeatureType request. (Added in WFS Reader Web Service version)
  • Connection via the proxy server
  • Asynchronous processing of WFS requests
  • Support for Microsoft Office Excel 2007
  • Unit tests
-- PavelGolodoniuc - 08 Jun 2009
Topic attachments
I Attachment Action Size Date Who Comment
BtnAbout.pngpng BtnAbout.png manage 0.7 K 08 Jun 2009 - 09:37 PavelGolodoniuc About button
BtnFetch.pngpng BtnFetch.png manage 0.4 K 08 Jun 2009 - 09:37 PavelGolodoniuc Fetch button
BtnFetchAdvanced.pngpng BtnFetchAdvanced.png manage 0.4 K 08 Jun 2009 - 09:37 PavelGolodoniuc Advanced fetch button
BtnOpen.pngpng BtnOpen.png manage 0.5 K 11 Aug 2009 - 10:01 PavelGolodoniuc Open button
BtnQueryBuilder.pngpng BtnQueryBuilder.png manage 0.5 K 20 Aug 2009 - 08:45 PavelGolodoniuc Query builder button
DlgAbout.pngpng DlgAbout.png manage 42.0 K 05 Aug 2009 - 07:18 PavelGolodoniuc About dialog box
DlgFetchAdvanced.pngpng DlgFetchAdvanced.png manage 33.3 K 20 Aug 2009 - 08:46 PavelGolodoniuc Advanced fetch dialog box
DlgQueryBuilder.pngpng DlgQueryBuilder.png manage 21.7 K 20 Aug 2009 - 08:46 PavelGolodoniuc Query builder dialog box
Excel.pngpng Excel.png manage 88.1 K 08 Jun 2009 - 09:36 PavelGolodoniuc Imported WFS response
Install0.pngpng Install0.png manage 7.6 K 24 Jun 2009 - 06:57 PavelGolodoniuc Add-in installer wizard - Step 0
Install1.pngpng Install1.png manage 22.1 K 08 Jun 2009 - 10:03 PavelGolodoniuc Add-in installer wizard - Step 1
Install2.pngpng Install2.png manage 20.7 K 08 Jun 2009 - 10:03 PavelGolodoniuc Add-in installer wizard - Step 2
Install3.pngpng Install3.png manage 18.4 K 08 Jun 2009 - 10:03 PavelGolodoniuc Add-in installer wizard - Step 3
Install4.pngpng Install4.png manage 18.8 K 08 Jun 2009 - 10:04 PavelGolodoniuc Add-in installer wizard - Step 4
Install5.pngpng Install5.png manage 19.5 K 08 Jun 2009 - 10:04 PavelGolodoniuc Add-in installer wizard - Step 5
Interface.pngpng Interface.png manage 3.3 K 20 Aug 2009 - 08:46 PavelGolodoniuc Add-in interface
Prerequisites1.pngpng Prerequisites1.png manage 8.0 K 24 Jun 2009 - 06:58 PavelGolodoniuc Installing prerequisites - Office/Excel 2003 PIA
Toolbar.pngpng Toolbar.png manage 16.5 K 08 Jun 2009 - 09:35 PavelGolodoniuc Enabling add-in in Excel
Uninstall.pngpng Uninstall.png manage 43.9 K 08 Jun 2009 - 10:04 PavelGolodoniuc Uninstalling add-in
Topic revision: r20 - 17 Mar 2011, JacquelineGithaiga
 

This site is powered by FoswikiCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding AuScopeWiki? Send feedback