Table Formats

The SaveToDB add-in enables you to load and save complete Excel table formats, including table views, using the following ribbon controls:

• Save Table Format
• Reload Table Format
• Table Format Wizard

These formats are stored in the xls.formats table.

When a user connects to an object for the first time, formats are loaded from this table, allowing users to access formatted tables with table views.

DBEdit, DBGate, and ODataDB also load formats from the table, but they support only a subset of Excel formatting rules.

Developers can define CSS-like format rules for DBEdit, DBGate, and ODataDB in the database, as described below. Additionally, common CSS styles can be used with DBGate and ODataDB.

CSS Formats

Database developers can define table formats in the HANDLER_CODE field with the Format value in the EVENT_NAME column of the xls.handlers table.

For example:

Here’s a sample format definition:

[{"format":"font-family: Calibri; font-size: 11pt; border-style: none"},
{"formula":"row_format=1","format":"background-color: rgb(33,89,103); color: white !important; font-weight: bold","stopIfTrue":true},
{"formula":"row_format=2","format":"font-weight: bold"},
{"formula":"row_format=9","format":"background-color: rgb(0,33,96); color: white !important; font-weight: bold"},
{"formula":"<0","format":"color:red","columns":"data00,data01,data02,data03,data04,data05,data06,data07,data08,data09,data10,data11,data12"},
{"formula":"[cf_sign=0][pl_sign=-1][=null]","format":"background-color: rgb(255,255,0);","columns":"asset_account_id"}]

The format definition is a JSON array of format objects.

Each format object can have the following properties: format, formula, columns, and stopIfTrue.

• The format property contains the CSS format definition.

DBGate and ODataDB support any CSS rules, while DBEdit supports a limited subset:

• border-style (none)

• background-color

• color

• font-weight (bold | 600 | 700)

• font-family

• font-size (pt | px)

• font-style (italic)

• text-decoration (underline | line-through)

• The formula property defines conditions for applying the format. Use square brackets to combine multiple conditions with the AND operator (similar to CSS).

You can specify a field name, sign, and value (e.g., row_format=1), or just a sign and value (e.g., <0).

The formula field also supports special formulas: even, odd, tr:nth-child(even), and tr:nth-child(odd).

• The columns property specifies the target columns. Alternatively, you can use the COLUMN_NAME field to define the format for a single column (usually set automatically by views).

• The stopIfTrue property stops the evaluation of subsequent rules.

Note that DBEdit ignores the !important tag, which is applicable in DBGate and ODataDB.

To create complex formats visually, follow these steps:

1. Install the SaveToDB add-in for Microsoft Excel (it's free).
2. Connect to a database object in Microsoft Excel and create the desired conditional formatting.
3. Run Wizards, Developer Tools, and select Show Table Format.
4. Copy the format and paste it into the HANDLER_CODE field.
5. Edit the format as needed and save it.