In the method SheetAPI.loadData and SheetAPI.updateCells, it allows user define different kinds of cell properties to decorate the cell: .
SHEET_API.updateCells(SHEET_API_HD, [{
sheet: 1,
row: 1,
col: 1,
json: {
data: "hello world",
bgc: 'black',
color: 'white',
fw: "bold",
fs: "italic",
u: "underline"
}
}]
);
The following table shows the detail cell properties.
| Name | Property | Example | Description |
|---|---|---|---|
| sheet id | sheet | sheet: 1 | Set defined sheet id for the cell. If it is ignore, the default value is currently active tab id. |
| row number | row | row: 1 | Set defined row number for the cell. Such as: A1 row number is 1. B2 row number is 2. |
| column number | col | col: 1 | Set defined column number for the cell. Such as: A1 column number is 1. B2 column number is 2. |
| json properties | json | json: {data: 100} | See the following for the detail json properties. |
The following table shows the detail json properties which allow you to create your style cell.
| Name | Property | Example | Description |
|---|---|---|---|
| background color | bgc | bgc: 'orange' bgc: '#FF0000' |
Set cell background color. The color needs to be inside '', such as: 'red', '#FF0000'. The 17 colors from the HTML specification are accepted: aqua, black, blue, fuchsia, gray, green, lime, maroon, navy, olive, orange, purple, red, silver, teal, white, and yellow. And any hex color value are also accepted. |
| border-width left border-width right border-width top border-width bottom |
blw, brw, btw, bbw |
blw:0, brw:1, btw:0, bbw:1 |
Set left right top and bottom border-width for the cell. Default value for border-width left is 0. border-width right is 1. border-width top is 0. border-width bottom is 1. |
| border-style left border-style right border-style top border-style bottom |
bls, brs, bts, bbs |
bls: 'dotted', brs: 'dotted', bts: 'dotted', bbs: 'dotted' |
Set left right top and bottom border-style for the cell. border-style value can be one of them: dotted|dashed|solid. Default value is none. |
| border-color left border-color right border-color top border-color bottom |
blc, brc, btc, bbc |
blc: 'red', brc: '#F79646', btc: '#F79646', bbc: '#F79646' |
Set left right top and bottom border-color for the cell. border-color value can be one of them: 'red' or hex value '#F79646'. Default value is none. |
| border-type left border-type right border-type top border-type bottom |
blt, brt, btt, bbt |
blt: 'double', brt: 'double', btt: 'double', bbt: 'double' |
Set left right top and bottom border-type for the cell. border-double value can be one of them: double|solid. Default value is none. |
| comment | comment | comment: 'Great work' | Set comment for the cell data. |
| commentEdit | commentEdit | comment: "Great work", commentEdit: "hide" | Hide the edit/delete buttons in the comment field. Default value for commentEdit is show. |
| comma format | fm | data: "12223.45678", fm: "comma" | Set comma format for the cell data. Result will be: 12,223.45678 for the example. |
| cell Data | data | data: 'Hello world' data: 100 |
Set cell Data. It is the data to be shown in the cell. |
| Checkbox | it, itn and itchk | it: "checkbox", itn: "fruit", itchk: false | Set checkbox for cells. "it" need to be defined as "checkbox". "itn" is used to define a group of checkbox. "itchk" is used to decide whether it is checked or not. |
| column width | width | width: 20, bgc: "#FBD5B5" | Set width for the column. |
| custom format | fm | data: "-12323.45678", fm: "number", dfm: "$#,##0.00;[Red]-$#,##0.00;[Red]ZERO" | Set custom format for the number of cell data. fm is "number". dfm is "$#,##0.00;[Red]-$#,##0.00;[Red]ZERO". For more detail, please see Customer Format. |
| date format | fm and dfm | data: "2015-01-27", fm: "date", dfm: "M d, Y" | Set date format for the cell data. fm is "date".
You also need set detail format "dfm". It is used to define different kind of date type.
For example: dfm: "Y-m-d H:i:s" will display as 2015-01-27 12:12:12 Here lists the symbol for the date format.
|
| disable cell | dsd | dsd: 'ed' | Set disable for the cell. |
| DropDown List | drop | data: "5", drop: Ext.encode({data: "2,5,10,20,50"}) | Set dropdown for cells. |
| font color | color | color: 'orange' color: '#FF0000' |
Set cell data font color. The colors need to be inside '', such as: 'red', '#FF0000'. The 17 colors from the HTML specification are accepted: aqua, black, blue, fuchsia, gray, green, lime, maroon, navy, olive, orange, purple, red, silver, teal, white, and yellow. And any hex color value are also accepted. |
| font-family | ff | ff: 'Courier New' | Set font-family for the font of the cell data. The value needs to be inside ''. The name of a font-family, like "times", "courier", "arial", etc. |
| font-size | fz | fz: 12 | Set font-size for the font of the cell data. Default font-size value is 10. Only accept number. |
| font-style | fs | fs: 'italic' | Set font-style for the font of the cell data. font-style value can be one of them: normal|italic|oblique|initial|inherit. And default value is normal. |
| font-weight | fw | fw: 'bold' fw: '900' |
Set font-weight for the font of the cell data. Default font-weight value is normal. font-weight value can be one of them: normal|bold|bolder|lighter|number. Number is 100, 200, 300, 400, 500, 600, 700, 800, 900 which defines from thin to thick characters. 400 is the same as normal, and 700 is the same as bold. |
| formula | cal | cal: true data: '=if(c2>100, ">100","<=100")' |
If the cell value is formula. cal: true need to be added in the cell json to identify it, and data need to be started with "=". |
| fraction format | fm | data: "12.6", fm: "number", dfm: "# ?/?" | Set fraction format for the number of cell data. Fraction is defined in the dfm and fm is number. |
| insert image | icp | icp:"http://www.google.com/images/srpr/logo11w.png" | Add an inner image inside the cell. |
| Link | link | link: 'www.enterpriseSheet.com' | Set hyperlink for the cell data. |
| money format | fm | data: "123.45678", fm: "money|$|2|negative1" | Set money format for the number of cell data. fm is "money|$|2|none". 1st character is format, should be "money". 2nd character is currency symbol, such as: $, RMB etc 3rd character is 2 decimals to be round. 4th character is one of the option: none, negative1, negative2, negative3. And this is for decorate negative number only. |
| overline | o | o: 'overline' | Set overline for the cell data. |
| padding left padding right padding top padding bottom | pl, pr, pt, pb |
pl:2, pr:2, pt:2, pb:2 |
Set padding left right top and bottom for the cell data. Default 2 are set for all padding. |
| Percent format | fm | data: "0.12345", fm: "percent", dfm: "0.00%" | Set percent format for the number of cell data. Percent format is defined in the dfm and fm is "percent". |
| radio | it, itn and itchk | it: "radio", itn: "sports", itchk: false | Set radio for cells. "it" need to be defined as "radio". "itn" is used to define a group of radio button. "itchk" is used to decide whether it is checked or not. |
| row height | height | height: 10, bgc: "#FBD5B5" | Set height for the row. |
| science format | fm | data: "123.6", fm: "science" | Set science format for the number of cell data. fm is "science". |
| strike | s | s: 'line-through' | Set strike for the cell data. |
| table template | tpl | tpl: '{id: "tpl_27", span: [1,2,2,5,4]}' | Set table templates for cells.
In the tpl json, it includes id and span.
id can be selected from tpl_0 to tpl_59 with different color and style for the table. For light color, id is from tpl_0 to tpl_20. For dark color, id is from tpl_21 to tpl_48. For deep dark color, id is from tpl_49 to tpl_59. span includes 5 parameters. Sheet_id, x1, y1, x2, y2. |
| text-align | ta | ta: 'left' | Set text-align for the cell data. The text-align property specifies the horizontal alignment of text in a cell. Default text-align value is left. text-align value can be one of them: left|right|center|justify. |
| text-indent | ti | ti: '10px' | Set text-indent for the cell data. The text-indent property specifies the indentation of the first line in a cell. Default text-indent value is 0. text-indent value need be inside '', for example: '10px'. |
| underline | u | u: 'underline' | Set underline for the cell data. |
| vairable | vname | vname: 'definedVariable' | Bind variable for the cell data. |
| vnameEdit | vnameEdit | vname: 'definedVariable', vnameEdit: "hide" | Hide the popup window of variable when mouse focus. Default value for vnameEdit is show. |
| vertical-align | va | va: 'bottom' | Set vertical-align for the cell data. The vertical-align property specifies the vertical alignment in a cell. Default vertical-align value is baseline. vertical-align value can be one of them: baseline|top|middle|bottom. |
| word-wrap white-space | ww and ws | ww: 'break-word', ws: 'normal' |
Set word-wrap for the cell data. Default word-wrap value is normal.
word-wrap value can be one of them: normal|break-word. Set white-space for the cell data. Default white-space value is normal. white-space value can be one of them: normal|nowrap|pre|pre-line|pre-wrap. |