Application

This repo contains the powerful engine that performs all the features and actions expected of a functional spreadsheet.

The application is logically separated into two parts.

• The client contains the web browser application. Actions performed by the user become REST API calls to the server.
• The server contains all the supporting REST APIs using JSON for request and response payloads that eventually interact with engine.

Global settings (SpreadsheetMetadata)

Each and every spreadsheet is represented by a single SpreadsheetMetadata object instance. A wide variety of items are stored for each spreadsheet including but not limited to:

• Each item is allocated a unique SpreadsheetMetadataPropertyName.

• Changes to any of these properties will force a recalculation and formatting of every cell in the spreadsheet.

• Spreadsheet identifier/name

  • SpreadsheetId The unique identifier for each spreadsheet.
  • SpreadsheetName A descriptive name of the spreadsheet.

• Audit metadata such as the creator/last modified users and timestamps.

  • creator
  • create timestamp
  • last modified by
  • last modified timestamp

• Locale

  • locale
  • It is possible to replace the initial Locale symbols used when formatting numbers and dates.
    • Date
      • User provided names for the days/months of the week SpreadsheetCell
      • Custom decimal number symbols for each cell SpreadsheetCell
    • Numbers
      • Currency symbol
      • Decimal separator
      • Exponent symbol
      • Group symbol
      • Negative sign
      • Percentage symbol
      • Positive sign
      • Value separator

• Date

  • DateTime offset Used to select the date for the numeric value of 0. This is used to select whether 1901 or 1904 is the starting epoch for date values.
  • Default Year
  • Two Digit Year

• Numbers

  • NumberKind
    • Mathematical computations in two flavours are supported
      • 64 bit fast with limited precision (12 decimal places) just like Excel and Google Sheets
      • Variable, slower supporting any number of digits of precision, more is slower.
        • Precision
        • Rounding mode

• Formatter Some default format pattern(s) for each of the spreadsheet value types. Note cells can have their own format pattern assigned which will be used instead.

  • Date
  • DateTime
  • Number
  • Text
  • Time
  • Custom formatters
    • Adding support for authoring (and uploading) or selecting from the store custom formatters SEE BELOW
  • General digit count Controls the number of digits that can appear when the General format pattern is selected.

• Parser

  • Some default parsing pattern(s) used to parse text into any of the supported spreadsheet value types.
    • Date
    • DateTime
    • Number
    • Time
    • Adding support for authoring (and uploading) or selecting from the store custom parsers.

• Viewport These properties control the spreadsheet grid view.

  • Frozen columns How many column(s) are frozen if any.
  • Frozen rows How many row(s) are frozen if any.
  • Hide Zero Value Control whether zero values are hidden or shown.
  • Viewport Includes the range of cells displayed and any Cell(s)/Column(s)/Row(s) selection.

Internal components

There are many internal components that contribute to the core functionality of a spreadsheet. Eventually each of these will be a plugin where users can contribute an alternative or supplementary choice.

SpreadsheetConverters

These Converters along with a few others belonging to other repos are used to convert values from one type to another. These support expressions where values are converters as necessary from one value type to another, eg

• basic
• collection
• color-to-number
• color-to-text
• error-throwing
• error-to-number
• format-pattern-to-string
• general
• has-style-style
• json-to
• null-to-number Handles converting null/missing cell values into 0, 10 + B2 when B2 is missing becomes 10 + 0.
• number-to-color
• number-to-number
• parser
• selection-to-selection
• selection-to-text
• simple
• spreadsheet-cell-to
• text-to-color
• text-to-expression
• text-to-form-name
• text-to-locale
• text-to-selection
• text-to-spreadsheet-color-name
• text-to-spreadsheet-formatter-selector
• text-to-spreadsheet-id
• text-to-spreadsheet-metadata
• text-to-spreadsheet-metadata-property-name
• text-to-spreadsheet-metadata-color
• text-to-spreadsheet-error
• text-to-spreadsheet-name
• text-to-spreadsheet-text
• text-to-template-value-name
• text-to-text
• text-to-text-node
• text-to-text-style
• text-to-text-style-property-name
• text-to-url
• text-to-validation-error
• text-to-validator-selector
• text-to-value-type
• to-json
• to-styleable
• to-text-node
• to-validation-error-list
• url-to-hyperlink
• url-to-image

SpreadsheetComparators

All sorting is performed by using a selected SpreadsheetComparator, which is identical to a java.util.Comparator. These may be enabled to supporting sorting one or more column/row/cell-range.

• Date
• DateTime
• Day of Month
• Day of Week
• Hour of AMPM
• Hour of Day
• Month of Year
• Nano of Second
• Number
• Second of Minute
• Text
• Text case-insensitive
• Time
• Year

Examples of combining multiple SpreadsheetComparators for a column range might include.

• day-of-month then month-of-year then year
• seconds-of-minute then minute-of-day then hour-of-day

When sorting a cell-range/column/rows it is possible to sort each column/row with different SpreadsheetComparator(s).

The plugin architecture allows authoring/installing custom comparators.

ExpressionFunction

Functions within a formula expressions are defined by individual ExpressionFunction.

Currently there are about 100+ functions available and these are listed HERE.

• Additional ExpressionFunction(s) may be provided via a custom ExpressionFunctionProvider DONE
• Uploaded plugins TODO

SpreadsheetFormatter

A SpreadsheetFormatter is used to format the cell value into text that is displayed within the grid of cells.

There are several built-in SpreadsheetFormatter(s) one for each Spreadsheet type, each supporting the standard patterns to allow user customisation of that value type along with a single color.

• dd/mm/yyyy date
• dd/mm/yyyy hh:mm:ss date-time
• default text default-text
• General General
• $0.00 number
• @ text
• hh:mm:ss time
• expression
• Additional SpreadsheetFormatter(s) may be provided via a custom SpreadsheetFormatterProvider DONE
• Uploaded plugins TODO
• SpreadsheetEngine integration (Currently only patterns are used refactoring work needs to be done) TODO

Other internal components

TODO Mention here TODO Dynamic plugin support