- Integrate & Set WOW with Existing Excel Files

Integrating WOW with Existing Excel Files

WOW provides the ability to generate a new spreadsheet from any existing data. It may be desirable to

have a preformatted spreadsheet, which contains titles, business charts, etc and have WOW update the

data dynamically from your database. This is possible with new support in WOW 7.0. A spreadsheet is

placed on the web server at specific location. WOW can be configured to read in the spreadsheet, update

the data, and send the merged Excel file to the users browser.

Setting WOW Operations to use Existing Excel Templates

An application and operation must be created that provides access to your data. For example, the following

operation does a simple select of states and the balance due by each state. Next, the operation needs to

have some properties set so that it knows which Excel file to use. Shown below is the operation with the

SQL that returns the states and balances due for each state.

After the operation is open for edit there are some properties that need to be added. In the Properties field of

the operation there are property groups like DisplayColumns, DetailDisplay and TableDisplay. These are the

standard property groups but there may be others. In the group, TableDisplay, add the following property:

excelXls:true;

NOTE: Properties and PropertyGroups ARE case-sensitive.

This property tells the operation to display the chart icon when the operation runs.

Now that WOW knows to show the Excel Chart icon, it needs to know which Excel file to open when the chart

icon is pressed. The specifics of the property need to be set, such as what excel file to open, and what worksheet

inside of that excel file to write the data to.

To set the specifics of the excelXls property we need to insert a Property Group called XLS, as shown below. XLS

has three properties that need to be set, including:

XLS {

fileName:excel_reports.xls;

sheetIndex:2;

directToFile:false;

}

filename - This is the Excel template file that will be opened when the chart icon is pressed.

sheetIndex - This is the worksheet in the Excel file where the data will be written.

directToFile - true: go directly to Excel chart when the operation runs. false: have to click on chart icon to

open Excel chart.

Now that the operation display properties have been set, update the operation. After execution of the operation,

a graph icon ( ) shows up alongside the Microsoft Word and Excel icons.

After the data has been returned, click on the graph icon to open the data in an Excel graph and/or spreadsheet.

The data will be automatically imported and shown on an Excel chart, there is also a tab called data that has the

returned results in table format.

An Excel macro updates the chart after the data has been updated. Users may see a security warning that a

macro is being run. They can safely grant permission.

NOTE: It is necessary to use a template provided by PlanetJ because of the included macros. However, a

template can be changed to any graph or display and can be made to handle any number of columns or rows.

The PlanetJ template is located in the ‘wowexcel’ folder inside your application server. For example:

C:\Program Files\Apache Software Foundation\Tomcat 5\webapps\wow64\web-inf\wowexcel\

There are a few samples included that you can use as is or copy, rename, and change to make custom chart or

reports. When using the XLS property filename just specify the file name and WOW will automatically look in the

wowexcel folder.