1. Tutorial

Please open XXE_install_dir/doc/spreadsheet/tutorial/invoice_table.html[1] and immediately save it as invoice.html in the same directory.

Note

Standard Edition users, do not be surprised when you'll see this popup:

XMLmind XML Editor Standard Edition will display it

  • when you insert the first formula in a document not containing formulas,

  • and if you open a document already containing formulas (created using Professional Edition).

This popup informs you that XMLmind XML Editor Standard Edition will strip all the formulas from the files you are going to save.

1.1. Basics

Use Tools|Spreadsheet|Show Table Labels (shortcut Ctrl-Shift-Y) to make the table look like a spreadsheet.

Click in cell E2 and use Tools|Spreadsheet|Insert/Edit Formula (shortcut Ctrl-Shift-I) to insert a new formula at caret position.

The Formula Editor is displayed:

After sign '=', type A2*D2 and then click OK (shortcut Ctrl-Enter).

You have inserted your first formula in the document. A formula is visually represented by a small F icon. Don't worry if you find it ugly: it will disappear when you'll print the document or when you'll convert it to other formats.

Click on the F icon. Notice that:

  • The node path bar displays: /html/body/table/tbody/tr/td/#processing-instruction(xxe-formula), which means that a formula is processing instruction xxe-formula (more on this later).

  • A red line is drawn around the F icon, which means that you have explicitly selected this processing instruction.

  • The status bar displays: =(A2*D2). The small F icon is in fact a special purpose button (more on this later).

Copy the formula (Ctrl-C) to the clipboard and paste it (Ctrl-V) in cells E3 and E4.

Now type in cell A2: 2, tab to cell B2 to type: xe-1u, tab again to cell D2 and type: 200. Do the same in the next two rows: 1,xe-5u,800 and 1,fc-uu,3000. Then click in cell E5 to force an update. The table should now look like this:

1.1.1. Three more formulas to finish first version of the invoice

  1. Click in cell E5 and insert formula: =sum(E2:E4)

  2. Click in cell E6 and insert formula: =rounddown(E5 * left(D6, len(D6)-1)%, 2)

    left(D6, len(D6)-1) is string "19.6%" without its last character '%'.

    Note that you can use spaces in a formula and that a formula is case-insensitive. For example: SUM(e2:e4) works fine too.

  3. Click in cell E7 and insert formula: =E5+E6

1.2. How does this really work?

In XMLmind XML Editor, a formula is stored as the xxe-formula processing instruction. A processing instruction such as xxe-formula is allowed by the XML standard. Such processing instructions will be ignored by all XML software except XMLmind XML Editor.

Unlike in ``real'' spreadsheet software:

  • A formula is not a ``computed table cell'', which is itself a value you can reference in other formulas. A formula is a special XML object which can be inserted anywhere in the XML document (including inside a table cell element, at an arbitrary nesting level).

  • A formula computes a value, but is not itself a value you can reference in other formulas. This computed value is added just after the xxe-formula processing instruction. If there is already some text just after the xxe-formula processing instruction, this text is replaced.

    Optionally the computed value can be used to set/replace the value of an attribute of the element containing the xxe-formula processing instruction.

  • A formula can access any part of the XML document (using XPath escapes). When the formula has an ancestor element which is formatted as a table cell, using the customary A1 notation to reference table cells is possible. When no styled view is used to render the XML document or when the formula has no ancestor element which is formatted as a table cell, A1-style cell references will not work.

In a styled view, a formula is rendered using a special purpose gadget, which is at the same time an indicator and a button. Its color gives you a hint about the state of the formula.

IconDescription
Unknown state. Formulas contained in document modules included in the document being edited are ignored by the spreadsheet engine.
Parse error. Should not happen if you use the Formula Editor.
Evaluation error.
OK.

Disabled.

Disabling a formula means passivating it. That is, it is no longer used to update the document. In some cases, this is a handy alternative to removing it.

Clicking on the icon triggers special actions:

Simple click

If the formula cannot be parsed (red icon) or evaluated (orange icon), displays the corresponding error message in the status bar. If the formula is working, displays the last statement of the formula (a formula can contain several statements, see The language used to write formulas).

Double click

Opens the Formula Editor to edit the formula.

Click with middle button

Disables (gray icon) or enables (green icon) the formula.

1.3. Relative references

Select row #4 (for example by Ctrl-clicking 3 times in a cell of row #4), copy it to the clipboard (Ctrl-C) and paste the copied row after row #4 (Ctrl-W).

Ouch! SubTotal E6 is wrong. It should be 7200. What happened here?

The first formula we have created was =A2*D2. This formula was inserted in cell E2. This formula uses relative cell references which means that XXE understands it as: add the cell which is 4 columns to my left to the cell which is 1 column to my left.

If you really wanted to add cell at (row 2,column A) to cell at (row 2,column D), whichever is the cell containing the formula, you should have typed =$A$2*$D$2.

Using relative references in a formula is handy because it allows the formula to be copied and pasted elsewhere. Remember that we have copied first formula to E3 and E4 and that we have duplicated row #4, which means that we have copied the formula of E4 to E5.

If you click on the formula of E3 (which is a copy of the one in E2), you'll see =(A3 * D3). Similarly, on E4, you'll see =(A4 * D4) and on E5, =(E5 * D5).

No, XXE has not modified the formulas that you have copied. XXE has stored exactly the same formula in E2, E3, E4, E5 but it displays it differently when you click on different cells.

And because, unlike ``real spreadsheet software'', XXE never modifies your formulas, SubTotal E6 is wrong! Click on E6 and you'll see =sum(E3:E5). Real spreadsheet software would have modified the formula to be =sum(E2:E5).

Fortunately, there is a way to fix this kind of problem. Double-click on the formula of E6 to open the Formula Editor and replace =sum(E3:E5) by =sum(difference(E:E,E6:E1000)). This means add everything in column E (E:E) except all cells after E6 (E6:E1000). Et voilà, this is fixed once for all! You can now freely add and remove purchased products to the table without worrying about the SubTotal.

Duplicate row #5 as we did for row #4. In new row #6, replace 1,fc-uu,3000 by 1,sc-cs,100. Then delete row #5 which is a copy of row #4.

1.4. Absolute references

Use the Attribute tool to add an ID to the table. Select the table using the node path bar, click on the line starting with id in the Attribute tool and type invoice_table, then press Enter.

Add a paragraph containing sentence "The VAT rate is the VAT rate of France.". After the "The VAT rate ", use Ctrl-Shift-I to insert a new formula.

Try to specify this formula as ="invoice_table"!D7. This does not work.

Outside a table, relative references are not allowed. Now specify ="invoice_table"!$D$7.

It works because we have used absolute references but this has deleted everything after "The VAT rate ". This is normal. Remember that the value computed by a formula is added just after the xxe-formula processing instruction. If there is already some text just after the xxe-formula processing instruction, this text is replaced.

Click on the formula to select it. Use Select|Extend Selection to Following Sibling (shortcut Esc Right-Arrow) and then Edit|Convert (shortcut Ctrl-T) to convert the two selected nodes to a span.

Now press Insert to insert a new text node after the newly created span and type once again " is the VAT rate of France.".

What you have learned here is that, unless a formula is inserted in a table cell which contains nothing else, you'll almost always have to wrap it in its own element (typically span for XHTML and phrase for DocBook).

1.4.1. Better absolute references

Using ="invoice_table"!$D$7 to copy the content of cell D7 is not a good idea. Adding and removing purchased products to the table would change the last sentence to something that does not make sense.

Formulas can very easily reference elements by their ID and that's what we are going to do. Click on cell D7 and specify an attribute id for it. We have already done that for the table. This time, specify VAT as the value of attribute id of td D7.

Now double click on the formula contained in last sentence and, using the Formula Editor, change ="invoice_table"!$D$7 to =$(VAT).

Notice that this time, the end of the sentence, "is the VAT rate of France.", has not disappeared.

We are going to double-check this by manually triggering a full calculation of the spreadsheet. Use Tools|Spreadsheet|Update (shortcut Ctrl-Shift-R) for that.

About automatic calculation of the spreadsheet

By default, the spreadsheet engine is in auto-update mode.

In auto-update mode, a full calculation is automatically performed, if needed to, when the editing context changes. For example: type some text in a paragraph, then click in (or tab to) another paragraph to trigger a spreadsheet calculation.

In manual update mode, only newly inserted formulas are computed. To force a full calculation, the user has to explicitly use Tools|Spreadsheet|Update.

Note that in both modes, a full calculation is automatically performed, if needed to, before validating or saving the document.

Using manual update mode is recommended if you have a slow computer or if you have inserted a lot of formulas in your document or if your formulas access many external documents (more about this in next section).

1.5. External references

Instead of typing product descriptions, we are going to use a formula to fetch them from an external XML document. This document is XXE_install_dir/doc/spreadsheet/tutorial/products.html.

Click in cell C2 and use Ctrl-Shift-I to insert a new formula. Enter this multi-line formula:

location = "products.html#" & trim(B2) & "_desc"
=`document($location, .)`

First line is easy to understand. It assigns to local variable location a string built using the code of the product: "products.html#xe-1u_desc". "product.html" is the URL, relative to the location of the document being edited, of the external XML document containing product descriptions. "xe-1u_desc" is the ID of the element containing the description of product xe-1u.

Second line contains an XPath escape. The expression between backquotes '`' , which uses standard XPath function document(), allows to fetch a node found in an external document. Without the "#xe-1u_desc" fragment identifier, the whole document node is fetched. With the "#xe-1u_desc" fragment identifier, we fetch the element node having xe-1u_desc as its ID.

Copy the new formula to the clipboard (Ctrl-C) and paste it (Ctrl-V) into C3, C4, C5.

1.6. Formatting

There is something obviously wrong in our invoice: numbers are very poorly formatted. Replace the unit prices of column D by $200.00, $800.00, $3,000.00, $100.00.

The problem now is that the formulas no longer work[2]. The reason is that, if string "3000" can be automatically be converted to a number when used in formula =A4*D4, a string such as "$3,000.00" cannot be automatically be converted to a number.

In order to fix this, we need to use spreadsheet function numbervalue(). This function must be used to convert a string representing a localized number to something usable by the spreadsheet functions and operators. Function numbervalue() must be passed a number format and optionally, a locale which specifies how to interpret this format.

Click on the formula of cell E2 and replace =(A2 * D2) by =(A2 * numbervalue(D2, "$#,##0.00", "en-US")). Specifying "en-US" is normally not necessary when you work on a computer using the en-US locale. We could have written =(A2 * numbervalue(D2, "$#,##0.00")). But we want this tutorial to run unchanged whatever is the computer used to learn XMLmind XML Editor.

While we are at it, we'll also make the value computed by the formula of cell E2 good looking. In order to do that:

  • Click on the Format toggle below the text area.

  • Type "$" in first text field.

  • Type "#,##0.00" in second text field.

  • Choose "en-US" from the combobox containing locales.

We could have used spreadsheet function text() to accomplish the same formatting task, but separating the calculation from the formatting of the result by the means of the Format fields will make your formulas easier to read.

Do not bother fixing by hand the formulas of cells E3, E4, E5. Simply copy the formula found in cell E2 (Ctrl-C) then click to select the formula of cell E3 and finally use paste (Ctrl-V) to replace it by the content of the clipboard. Repeat the operation with cell E4 and cell E5.

The formula of cell E6 is trickier to fix: =sum(difference(E:E, E6:E1000)). In order to do this, we need to use spreadsheet function apply().

Function apply() applies a transformation to each node of a nodeset. Here the nodeset is: difference(E:E, E6:E1000). The transformation that we need to apply is numbervalue(x, "$#,##0.00", "en-US"), where x represents the string value of the iterated node. This gives:

Note that the transform argument of apply is specified as a string and that we have alternated single and double quotes to make this string easier to read.

Tip

The documentation of spreadsheet functions is available online. If, for example, you don't remember how to use function apply(), simply select the word apply in the text area of the Formula Editor and press F1.

Finally fix the formulas found in cells E7 and E8:

  • Double-click on the formula of cell E7 and replace =rounddown((E6 * (left(D7, (len(D7) - 1)))%), 2) by =rounddown((numbervalue(E6, "$#,##0.00", "en-US") * (left(D7, (len(D7) - 1)))%), 2). Also use the Format fields.

  • Double-click on the formula of cell E8 and replace =(E6 + E7) by =(numbervalue(E6, "$#,##0.00", "en-US") + numbervalue(E7, "$#,##0.00", "en-US")). Also use the Format fields.

The invoice is finished. It is now possible to remove the labels we put around the rows and columns of the table. This is done by using Tools|Spreadsheet|Hide Table Labels (shortcut Ctrl-Shift-Z).



[1] In XXE_install_dir/doc/spreadsheet/tutorial/, you'll also find:

  • products.html, the product list used in this tutorial.

  • invoice_done.html, the invoice after finishing this tutorial.

  • invoice_template.html, a ready-to-use, empty, invoice, having all the needed formulas.

[2] NaN is a special number which means "Not a Number".