Cell object is created after sheet object is completely initialized, and is stored in the cells registry inside the sheet object. You can retreive this cell object using two methods, via calx api, and via sheet object
$(selector).calx('getCell', cellAddress)
or
sheet.getCell(cellAddress)
After cell object is retrieved, you can access all the method available in cell object
calculate
cell.calculate()
Evaluate the formula of the current cell, and all it’s dependant (all cells that depends on this cell)
evaluateFormula
cell.evaluateFormula()
Calculate only formula of this cell, and return the value.
getAddress
cell.getAddress()
Get the cell address of current cell object.
getFormat
cell.getFormat()
Get the formatting rule.
getFormattedValue
cell.getFormattedValue()
Get the formatted value
getFormula
cell.getFormula()
Get the formula
getValue
cell.getValue()
Get the raw value of the cell, if cell has formula defined, it will return the calculated value
renderComputedValue
cell.renderComputedValue()
Render the computed value to the cell’s element
setConditionalStyle
cell.setConditionalStyle(function(value, element){})
Setup conditional styling for the cell element, it should be function with the cell value as first parameter, and jQuery object of the cell element as second parameter
cell.setConditionalStyle(function(cellValue, cellElement){
if(cellValue < 0){
cellElement.css('color', 'red');
}else{
cellElement.css('color', 'green');
}
});
setFormat
cell.setFormat(format)
Set the formatting rule of the current cell.
Please note that you must not set the format on the fly via $(selector).attr('data-format', format) since jQuery Calx will not notice the change.
setFormula
cell.setFormula(formula)
Set the calculation formula of the current cell.
Please note that you must not set the formula on the fly via $(selector).attr('data-formula', formula) since jQuery Calx will not notice the change.
setValue
cell.setValue(value)
Set the value of the current cell.
Please note that you must not set the value on the fly via $(selector).val(value) since jQuery Calx will not notice the change.
Cell with formula defined, will not affected by this change since it will always return the calculated value.
Cell with data-format caontains % like 0%, 0.00 %, will parse 10 as 10% (0.1), 10% as 10%.
Hi Ikhsan,
When we use ‘setValue’ method to set an udpated value to the cell, the cursor in the text control moves to the last which is not expected. Is there a way to retain the current cursor position while setting value in the API
Can someone please prove provide me with an example of setConditionalStyle. I can’t seem to find an example. Help is appreciated
Hi Navin,
You can simply set the conditional style right after initializing calx
$('#form').calx();
$('#form').calx('getCell', 'A1').setConditionalStyle(function(val, el){
if (val == condition) {
$(el).css('color', 'red');
}
});
Hi, i try to realize a cellformat-condition like excel does. For example: A1==0 (or empty) and B1 == 0 (or empty) then C1 background = ‘red’ else C1 background = ‘none’
Is there a way to do like this?
Very thanks for a suggestion,
Your Bernd from Germany
Hi Bernd,
You can use the
setConditionalStyle, but it currently only receive related cell object and set style based on its own value, and not other cell’s valueHey Ikhsan
Can you please tell me is is there any limitation to define data-cell ?
I tried Number_One ,NumberOne,Number,NUMBERONE all these case are not working.
Only NUMBERONE1 is working.
Is this necessary to add a number with data-sell value ?
Thanks
KR
Pooran Prakash
Yes, data cell should follow excel like cell address, like A1, AD23 etc
Hi Ikhsan,
I’m not sure what you mean by “current cell?” How is the “current cell” chosen? Maybe this is a typo and you mean cell.setValue(value) ??
Thanks, that was a typo 🙂
Hi,
I had a look at the dynamic form example, I get the syntax of adding a dynamic row. I can’t get my head around deleting rows. I fetch – say – 5 rows from a database, add rownumbers to the data-cell. then append rows, update itemlist but then my problem starts when I delete – say – row 3 and add a new row. The updated index is length+1, so it will add row 5 again, messing up all calculations.
In a big sheet when adding and deleting multiple rows, this gets messy very quickly. Do you have any ideas how to keep track of this?
Hi Paul,
just updated the dynamic form sample, it is now depends on counter instead of element count.
The samples is mean to show the features and abilities of jQuery Calx, and actually you can implement any algorithm which you feel is better, such as shifting the cell address when row is deleted and keep track of the row number, etc.
Best regards
Ikhsan