Table Formats

The SaveToDB add-in allows loading and saving complete Excel table formats, including table views, anytime using the following ribbon controls:

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

It stores such formats in the xls.formats table.

It loads formats from the table when a user connects to an object for the first time. So, users can get formatted tables with table views.

DBEdit, DBGate, and ODataDB also load formats from the table. However, the products support a subset of Excel formatting rules.

Developers can define CSS-like format rules for DBEdit, DBGate, and ODataDB in a database. See below.

Also, developers can use common CSS styles 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 is a format sample:

[{"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.

The format objects can have the following properties: format, formula, columns, and stopIfTrue.

The format property contains a CSS format definition.

DBGate and ODataDB support any CSS rules. DBEdit supports the following rule 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 to apply the format. Use square brackets to define several conditions with the AND operator (like in CSS).

Use a field name, sign, and value like row_format=1, or just a sign and value like <0.

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

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

Use the stopIfTrue property to stop all the subsequent rules.

Note that DBEdit ignores the !important tag (used in DBGate and ODataDB).

You can use the following steps to create complicated formats in a visual mode:

• Install the SaveToDB add-in for Microsoft Excel (it's free).
• Connect to a database object in Microsoft Excel and create the required conditional formatting.
• Run Wizards, Developer Tools, Show Table Format.
• Copy the format and paste it into the HANDLER_CODE field.
• Edit the format and save it.