Please open
[1] and immediately save it as XXE_install_dir
/doc/spreadsheet/tutorial/invoice_table.htmlinvoice.html
in the same directory.
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.
Use Ctrl-Shift-Y) to make the table look like a spreadsheet.
| | (shortcutClick in cell E2 and use Ctrl-Shift-I) to insert a new formula at caret position.
| | (shortcutThe Formula Editor is displayed:
After sign '=', type A2*D2
and then click (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:
Click in cell E5 and insert formula: =sum(E2:E4)
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.
Click in cell E7 and insert formula: =E5+E6
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.
Icon | Description |
---|---|
![]() | 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:
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).
Opens the Formula Editor to edit the formula.
Disables (gray icon) or enables (green icon) the formula.
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.
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 Esc Right-Arrow) and then | (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).
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 Ctrl-Shift-R) for that.
| | (shortcutBy 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
| | .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).
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.
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.
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 Ctrl-Shift-Z).
| | (shortcut[1] In
, you'll also find:XXE_install_dir
/doc/spreadsheet/tutorial/
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
".