Linking Domino to Excel via XML (Page 3)

Search DominoPower's 11,441 Lotus-related article archive

Home
EasyPrint
News details

Articles-only

Twitter Feed

Linking Domino to Excel via XML (continued)

A table header for our example table could be:


<TABLE BORDER=1 CELLPADDING=2>

<TR>

<TH>Year

<TH>Quarter

<TH>Area

<TH>Revenue

<TH>Net Profit

The footer need be no more than:


</TABLE>

That's it! Your Notes view is now a neatly formatted table that can be easily viewed by browsers. When saved to disk, the view can be opened by Excel as a spreadsheet. The only thing it lacks, as your customers will soon tell you, is a total.

Summing it Up
Excel 97 supports some basic XML additions that allow you to build in formula to this table. In principle, this is trivially simple, for example:


<TD FORMULA="=SUM(D2:D34)">

There are some complications in actually using this simple code, however:

If you only use the formula, browser users will see no totals.

When summing up a dynamic table, you have no idea of what range to use for the sum.

When using formula within the rows, how do you know the current row number?

The key to solving the first two obstacles is to create another, parallel Notes view. Doing so allows you to use the @SUM and @DBCOLUMN commands to work out a static total for browser users, and the @ELEMENTS together with @DBCOLUMN to calculate how big that dynamic range is.

To sum up the Net Revenue column, you must create two computed fields on the Notes form, as in this example (where Net Profit is the 8th column in the parallel Notes view):


Total_NetProfit, formula @Sum(@DbColumn("";"";"Notes View";8))

RowCount_NetProfit, formula @Elements(@DbColumn("";"";"Notes View";8))+1

(The row count is incremented by one to take into account the header row).

With these you have everything need to build the full formula, as you can see Figure C:

FIGURE C

Here's some sample code to provide a column total for both Excel and Web browser.

When browser users see this view, they will have the value of the Notes field Total_NetProfit as the total. When the sheet is loaded into Excel, the =SUM formula will be used instead, allowing the Excel user to play with the figures and have the totals automatically recalculate.

Row formulae
If you've grasped the previous concept, you might think that putting formulae into each row would be as simple as:


"<TD ALIGN=RIGHT FORMULA=\" =E"+@text(@docnumber)+"-D"+@text(@docnumber)\">"+@Text(GrossProfit;"(F,2")+"</TD>"

This will work for tables without headers, where @DocNumber is the same as the row number. However, if you have a header row you need @DocNumber plus 1, and that's where you hit a Notes wall. It simply can't be done, not even by converting the text to a number and back again. The @DocNumber formula returns the current line number as "special text" which is prevented from ever being turned into a number.