XMLmind XML Editor - Using the Integrated Spreadsheet Engine

Hussein Shafie

Pixware

91 rue Gambetta,
78120 Rambouillet,
France,
Phone: +33 (0)1 30 59 81 44,
Web: www.xmlmind.com/xmleditor
Email:  (public mailing list)

July 9, 2014

Abstract

This guide contains everything you need to know to use the spreadsheet engine integrated in XXE. This document starts with an easy-to-follow tutorial.


Table of Contents

I. Tutorial
1. Introduction
2. Tutorial
1. Basics
1.1. Three more formulas to finish first version of the invoice
2. How does this really work?
3. Relative references
4. Absolute references
4.1. Better absolute references
5. External references
6. Formatting
II. Reference
3. The language used to write formulas
1. Statements and comment lines
2. Expressions
3. Function calls
4. Literals
4.1. Numbers
4.2. Strings
4.3. Booleans
5. References
5.1. Reference to a variable
5.2. Reference to an element having a specific ID
5.3. Reference to table cells
5.3.1. The $[row,column] notation
5.3.2. Reference to cells in another table of the document
6. XPath escapes
7. Value types
8. Automatic conversion between different value types
4. Predefined functions
1. Date and time functions
1.1. datevalue
1.2. timevalue
1.3. datetimevalue
1.4. date
1.5. time
1.6. datetime
1.7. year
1.8. month
1.9. day
1.10. weekday
1.11. hour
1.12. minute
1.13. second
1.14. today
1.15. now
2. Logical functions
2.1. and
2.2. or
2.3. not
2.4. if
2.5. true
2.6. false
3. Mathematical functions
3.1. numbervalue
3.2. checknumber
3.3. sum
3.4. product
3.5. abs
3.6. acos
3.7. asin
3.8. atan
3.9. atan2
3.10. cos
3.11. cosh
3.12. sin
3.13. sinh
3.14. tan
3.15. tanh
3.16. degrees
3.17. radians
3.18. pi
3.19. exp
3.20. acosh
3.21. asinh
3.22. atanh
3.23. log
3.24. mod
3.25. ln
3.26. log10
3.27. sign
3.28. sqrt
3.29. trunc
3.30. int
3.31. rand
3.32. countif
3.33. sumif
3.34. round
3.35. rounddown
3.36. roundup
4. Spreadsheet functions
4.1. union
4.2. intersection
4.3. difference
4.4. apply
5. Statistical functions
5.1. avedev
5.2. stdev
5.3. var
5.4. max
5.5. min
5.6. average
5.7. count
6. Text functions
6.1. char
6.2. code
6.3. text
6.4. mid
6.5. left
6.6. right
6.7. trim
6.8. lower
6.9. upper
6.10. len
6.11. substitute
6.12. replace
6.13. find
6.14. search
5. Defining custom spreadsheet functions
1. Registering custom spreadsheet functions with XXE
2. Specifying custom spreadsheet functions
3. Custom spreadsheet functions written in the Java™ programming language

List of Figures

5.1. Custom function factorial() edited in XXE using the Spreadsheet Functions configuration