Spreadsheet Editor: User Interface to hold Excel-spreadsheet-like data

The spreadsheet is a useful and powerful tool in many applications, allowing the addition of tables, computations of totals, and many other features.
Orixa includes a spreadsheet component in its application offering. Spreadsheet controls can be placed within any Business Object, allowing users to add data directly to a data-record.
This can be useful, for example a Product record might include a Spreadsheet element to hold costings or details of ingredients.

The Spreadsheet Editor in an Orixa Edit Form

Spreadsheet control within an Application Edit Form

The image above shows an Orixa App into which a Spreadsheet has been added. Note columns can automatically include alignment, bold and other formatting, varied column widths and formulae.

The Orixa Spreadsheet control mimicks many of the features of Excel, and even allows cutting and pasting of sections of cells between Excel and Orixa. The main limitation of the Orixa Spreadsheet control is that it does not feature multiple "sheets", all data is stored in a single sheet.

It is very useful to understand basic features of Excel in order to work most effectively with the Orixa SpreadSheet control. For example, pressing F2 to edit a cell works in the same way as with Excel. Other common short-cuts such as [ctrl] + B for Bold text are also mimicked across directly from Excel.

Many other short-cut keys work exactly as in other programs, the [ctrl] + R combination activates "Find and Replace" actions, and [ctrl] + Z activates "Undo" functions.

Features of the Orixa Spreadsheet Editor

Spreadsheet, cell formulae

Standard Excel-like formulae are supported. Users can type formulae (where they understand them) directly into the Spreadsheet control.

If a user is unsure of how to write a formulae, they can open the dedicated editor window (detailed below in this document) to use additional tools to help them.

Spreadsheet: Useful Context Menu

Right-clicking on any cell immediately activates a context menu for the Spreadsheet control, which gives the user access to a range of commands for formatting the data in the sheet.

Note that commands act on the cells selected before the context menu is activated, exactly as in Excel, allowing a selection of cells to be reformatted.

Spreadsheet: Context Menu, cell formatting

The initial context menu accesses additional formatting windows. for example if the user selected "Format Cells" rom the context menu, then the window shown at 1., in the image on the left will open.

This gives the user access to a wide range of formatting options.

SpreadSheet Cut and Paste from Excel

To move data between the Orixa Spreadsheet control and Excel, simply outline the set of cells you wish to move and press [ctrl] + C to Copy. Shift to the destination application and press [ctrl] + P to Paste the data into the destination application.

Spreadsheet Editor Window: Open this to access extra spreadsheet capabilities

The space for editing data in a spreadsheet may be a bit limited if the user is restricted to the space available on an Edit Form Window. As Spreadsheets may contain larger amounts of data, a SpreadSheet Editor Window has been added to Orixa.

Double Click on any Spreadsheet control and a dedicated editor window will open containing the same data. The user can re-size this window as they see fit, enabling them to see more information, and take advantage of the extra capabilities detailed below.

Spreadsheet Form Toolbar

Additional Spreadsheet form Toolbar

Navigation cell. This shows the "A1" formatted position of the current cell the user is editing.
Formulae creation buttons (described below)
Current Cell data editor. The user can click here to edit data in the selected cell.
Additional menu button (described below)

Spreadsheet Formulae Helper Form

Formulae Creation Buttons

Click on the "f" button to open the "Insert Formula" window. This mimicks a similar window in Excel, and provides the user with help in writing a wide range of functions that can be added to your spreadsheet.

Spreadsheet Toolbar: Additional Actions Button

Additional Actions Button

Click at 1., in the image on the left to access an additional menu, containing useful extra capabilities.

Commonly used Alignmenu actions.
Reformatting cell(s) so their text "wraps"
"Undo" actions (which can also be accessed with the [ctrl] + Z key-combination.)
Find and Replace actions (which can also be accessed with the [ctrl] + Z key combintation.
Saave the data in the window to its own file on disk.

Additional Capabilities of the Spreadsheet

Spreadsheet: Add Comment

"Comments" are supported

Right click on any cell and select "Insert Comment", and an Excel-style comment can be added to the data.

You can also select "Format Cells" to open the dialog which is described in the next section

CAUTION: Comments can only be added to cells which already contain text, otherwise an error will occur.

Spreadsheet Comments and Cell Format Dialog

Comments and Cell Format Dialog

Once activated, the user can use any of the features of the dialog.

To add a comment which will display as shown.
To enter text for the comment or change any other features of the cell formatting, such as border-styles, cell colours etc.

Spreadsheet, viewing, editing and removing existing Comments

Once a Comment has been added, it will appear on-screen if the user holds their mouse over the cell which contains the comment.

The comment can be reviewed, edited and deleted from the Context Menu as shown at 1., in the image.

Adding the Spreadsheet control to your Orixa App

Any Business Object can support a spreadsheet control. Remember that external Excel Files can also be linked to any BusinessObject using the "FileNotes" feature. The Spreadsheet control should be used where it is useful for the data to be guaranteed to be linked directly to the data-record and the data needs to be accessed by all users across multiple platforms.

All that is needed is to add a declaration to the Business Object's SQL Definition

ALTER TABLE "Customers"
ADD COLUMN "SpreadSheet" AS BLOB DESCRIPTION 'SpreadSheet'