cCell

cCell is a spreadsheet formula language that combines common spreadsheet syntax (LibreOffice Calc, Microsoft Excel, Google Sheets) with cScript for database-enabled spreadsheets.

cCell will be available in webCOMAND 4.

Grid

Like spreadsheet cells, cCells exists in a two dimensional grid. Each cell can be referenced in a formula by it's column and row in the grid.

While not typically exposed directly to the user, a cCell is a standard object that extends Content with the following fields:

Column - The column of the cCell represented as letters A-Z, AA-ZZ, etc.
Row - The row of the cCell represented as a positive integer.
Value - A literal value or formula. Formulas start with an equal sign (=).

cCell References

A cCell value can contain literal text or a spreadsheet formula to reference functions and the values/results of other cCells. The following cCell value references the cCell at column A, row 1.

=A1

Like a spreadsheet, when the contents of a cCell are copied or moved from one cCell to antoher, the cCell references are transposed to the destination cCell. To indicate an "absolute" cCell column or row reference that should not be transposed on copy or move, precede the column or row with a $.

=A$1

External cCell References

cCells in another worksheet or object are referenced with a preceding cPath followed by an exclamation point (!), similar to how a worksheet is referenced in a traditional spreadsheet. The cPath can be in one of the following forms.

Local

A hierarchical cPath starts with a slash ('/') to reference a worksheet or object from the root folder.

=/Contacts/[:Worksheet]Employees.Cells!A1

A cPath that starts with an open bracket ('[') will reference a worksheet or object with a query.

=[:Worksheet AND OID=123].Cells!A1

Remote

Remote content can be imported with COMAND::IMPORT(). A Web Service request can also be used to access a cCell on a remote webCOMAND server. The first parameter is a URL to JSON or a COMAND web service end point, such as "http://hostname/ws?get=Folder/Object". Assign the results to a cell to access the entire result of the import through the cell.

=COMAND::IMPORT('https://account.webcomand.com/ws', '/Folder/[:Worksheet]Object.Cells')

A security token can be passed as the second parameter.

=COMAND::IMPORT('https://account.webcomand.com/ws', '/Folder/[:Worksheet]Object.Cells', COMAND::TOKEN('1234'))

IMPORT() can also be followed by a ! to treat it as a worksheet and manipulate the results with subsequent cCell.

=COMAND::IMPORT('https://account.webcomand.com/ws', '/Folder/[:Worksheet]Object.Cells')!A1

Arithmetic & Functions

Like traditional spreadsheet cells, cCell formulas can perform math operations.

= (A1 + A2) / A3

Similarly, cCell formulas can call functions and reference ranges of values using start:end notation.

= SUM(A1:A25)

cScript

Going beyond traditional spreadsheets, cCell formulas can also contain cScript to access the repository and perform more complex calculations and tasks, dramatically extending their capabilities.

If a return statement is encountered, the return value will be used as the result of the cell. Otherwise, the output (from echo) will be used. This is similar to how the result of a cTemplate is determine.

Variables

In cScript, a dollar sign is used to reference a variable by name. In some cases, the syntax to reference a variable may be indistinguishable from the syntax to reference an absolute column and row (ie. $AB1). In these cases, cCell will assume it is an absolute column and row reference, so it is a best practice to use ${} syntax to reference variables in cCell. However, it is not required if the variable is not followed by a number or a dollar sign and number.

The following cCell only references other cCells, not variables that might have the same name.

=$A1+$ABS$22

The following cCell only references cScript variables, not cCells.

=${A}1+${ABS}$22

The following combines cCell and variable references, spreadsheet functions and cScript statements.

= ${Sum}=SUM($A$1:$A22); (${Sum}==12 ? 'dozen' : ${Sum});

cCell Worksheets

While cCells can be contained in any content type field that is a List of cCells, the cCell Worksheet content type is an object designed with that sole purpose. cCellWorksheet extends Content with the following fields.

Title - The worksheet title
cCells - List of cCells

cCell Workbooks

The cCellWorkbook content type is a named ordered list of cCell Worksheets, similar to a spreadsheet file.

Title - Name for the workbook.
Worksheets - Ordered list of cCell Worksheets.

HTML

The main webCOMAND cCell View renders a collection of cCell values in an HTML table. Each cell is assigned a unique HTML element ID equal to the cCell column/row address (ie. the top-left cCell will have id="a1"). cCell values can contain HTML, including tags, inline styles, classes and "data-" attributes. A simple rendered cCell View might produce the following source code. cCell View makes it possible to edit the values and formulas in each cell, but the resulting table can also be shared or published to the web as a static table.

<table>
    <tbody>
        <tr>
            <td id="a1">Hello</td>
            <td id="b1">World</td>
        </tr>
        <tr>
            <td id="a2">Rendered</td>
            <td id="b2">Values</td>
        </tr>
    </tbody>
</table>

cCellAttributes

cCellAttributes apply one or more HTML attributes, including styles and classes, to one or more cCell table data elements (<td>). Classes that define shared styles are assigned to cCells this way.

cCells - Reference to one or more cCells.
Attributes - List of Attribute/Value pairs to apply to the rendered HTML <td>.

CSS

cCellCSS objects can reference the parent object of cCells (ie. a cCell Worksheet) to define CSS that will be applied to the entire table, including all cCells and their inline HTML. That CSS can define ids and classes applied to the cCell (<td>). In other words, a cCellCSS object is an attribute of the parent object of a cCell collection. The cCell View will include the CSS in the <iframe> that contains all of the rendered cCells (in a <style> reference above the <table> in the <iframe>).

JavaScript

Similar to cCellCSS, cCellJavaScript can reference the parent object of a cCells to define JavaScript that will run once the cCells are rendered. The JavaScript can manipulate and bind to cCells by their ids, classes, etc. jQuery, D3 and other JavaScript libraries can be leveraged to manipulate the grid.

Dimensions (Matrix)

Extend cCells with one or more dimension fields to turn a simple two-dimension grid into a multi-dimensional matrix. Similar to Form View, cCell View includes a Dimension sidebar to create and navigate cCell dimensions.

cCell View

cCell View is typically used instead of Form View in a cPath Panel when an object that contains a list of cCells or cCell Worksheets is displayed. This view renders the <table> and provides a JavaScript editor to access and update the value or underlying formula of each cCell. It also provides a sidebar for editing formulas and formatting cells.

Formatting Sidebar

The formatting sidebar is used to apply CSS styles to selected cells. It should work similar to how formatting is applied to cells in a traditional spreadsheet, but actually assigns classes to cells with cCell Attributes, and then defines those styles in a cCell CSS object. The sidebar provides a way to re-select and adjust the selection of cells that share a class of styles. The style editor will likely have a lot of similarities to the Sites style editor.