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 of the week TODO
      • User provided names for the months of the year TODO
    • 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

• Formatting 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.

• Parsing

  • 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.

SpreadsheetComparators

All sorting is performed by using a selected SpreadsheetComparator, which is identical to a java.util.Comparator but also includes a type property of java.lang.Class. The type property is used to convert each value prior to the actual comparison.

This supports advanced features such as sorting a range of cells with

• 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

It is thus possible to sort a column(s) in the following possible ways

• 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).

• Additional SpreadsheetComparator(s) may be provided via a custom SpreadsheetComparatorProvider DONE
• Uploaded plugins TODO

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

General General

$0.00 number

• @ text

• hh:mm: ss time

• 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