Online spreadsheet -> Sheet API
Sheet APIs
The SheetAPI class provides an API access to the Sheet. In general, this class is not instanced directly,
instead a openFileByOnlyLoadDataFlag option is passed to the class.
SheetAPI Usage Example
SHEET_API = Ext.create('EnterpriseSheet.api.SheetAPI', {
openFileByOnlyLoadDataFlag: true
});
SHEET_API_HD = SHEET_API.createSheetApp({
renderTo: 'sheet-markup',
height: '100%'
});
SHEET_API.loadData(SHEET_API_HD, {
fileName: 'Example Sheet',
sheets: [{
name: 'First tab',
id: 1
}],
cells: [{
sheet: 1,
row: 1,
col: 1,
json: {data: 110}
}]
});
This does three things as follows:
- First it creates two global variables called 'SHEET_API' and 'SHEET_API_HD'.
You can use those two global variables in your code to manipulate sheet data later.
- Second it renders the sheet component to the HTML document DIV 'sheet-markup' which should be defined in your html page.
- Third initial data are loaded into the sheet.
When the page is ready and all of your JavaScript has loaded and sheet are successfully rendered,
the initial related data are loaded into this sheet. In this case, a sheet with file name as
'Example Sheet' appears, and this file include one tab "First tab", and cell A1 in tab "First tab" is
filled with number data 110.
Config options
openFileByOnlyLoadDataFlag : Boolean
Set the open file flag, if true then only load the data not refresh the url.
Available since: 3.0.0
Methods
addCalculates( calculates )
This method is used to add customized calculate operation. It allows you to define your calculation.
These calculations will be passed to the sheet calculate system, so users can use these calculations in the cell.
Available since: 3.0.0
Click add Calculates method to see detail information.
addCalculateExceptions( calExceptions )
This method is used to add customized calculate exception. It allows you to define your calculate exception.
These exceptions will be passed to the sheet calculate system, so when there is a matched exception happened
during the calculating, there will be a red tip on the cell to show some exception information to the user.
Available since: 3.0.0
Click add calculate exceptions method to see detail information.
addFilter2Span( SHEET_API_HD, span, filterCdts, persist )
This method is used to add filter to a list of selected range with specific condition.
Available since: 3.4.0
Click addFilter2Span method to see detail information.
addSheetTab( hd, sheetTabConfig, callback, scope, notSwitchToNewSheet )
This method will add a new sheet tab to the sheet file. Example code
Available since: 3.0.0
Parameters
The handle information of the Sheet application you created.
The adding sheet tab information. Name is the added sheet tab name and this should be unique.
Sheet position is start with 0. If position is not
defined, the new sheet will be added in the last position.
{
name: "Sheet5",
color: "red",
position: 1
}
The callback function which will be called after sheet tab is added.
The scope for the callback function.
- notSwitchToNewSheet : Boolean
If it is true, it means not switch to the sheet tab after added.
alignSet( hd, dir )
Set the selected cell text align
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
The direction of the content to be aligned. Should be one of the following item:
left
middle
right
top
center
bottom
applyCellsBorder(hd, cood, config)
Apply the border style to the cell range.
Available since: 3.0.11
Click applyCellsBorder method to see detail information.
applyTableTpl(hd, sheetId, startRow, startCol, endRow, endCol, tpl, trigger)
Apply table template to a range of cells.
Available since: 3.0.8
Click applyTableTpl method to see detail information.
average( hd )
This function is used to popup a average formula
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
bold(hd)
Make the selected cell text become bold or un-bold
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
cancelMergeCell( hd )
Make the merged cells un-merge
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
cancelGroup(hd, dir, start, end)
Cancel the first group in the selected area
Available since: 3.0.11
Parameters
The handle information of the Sheet application your created.
row or col. Col means column direction.
The start row or column number.
The end row or column number.
clean( hd )
Clean all content and format in the selected range.
Available since: 4.0.0
The handle information of the Sheet application your created.
cleanContent( hd )
Clean content in the selected range.
Available since: 4.0.0
The handle information of the Sheet application your created.
cleanStyle( hd )
Clean all format in the selected range.
Available since: 4.0.0
The handle information of the Sheet application your created.
clearAllVariables(hd)
Clear all defined variables in the file.
Available since: 3.0.12
Parameters
The handle information of the Sheet application your created.
clearCellsBorder(hd, cood)
Clear the border style to the defined cell range.
Available since: 3.0.11
Parameters
The handle information of the Sheet application your created.
The specific array list to be applied border.
[[sheetId, startRow, startCol, endRow, endCol]]
which define a list of sheetId and a range of cells need to be cleaned border style.
clearCondition( hd, coord )
Clean the created condition for the existing sheet.
Available since: 3.0.11
The handle information of the Sheet application your created.
The specific array list to be applied for clean condition.
[[sheetId, x1, y1, x2, y2]]
which define a list of sheetId and a range of cells need to be clean condition.
clearItem( hd )
Clean all content and format in the selected range.
Available since: 4.0.0
The handle information of the Sheet application your created.
clearTableTpl( hd, span )
Clean the applied table template for the existing sheet.
Available since: 3.0.11
The handle information of the Sheet application your created.
The specific span to be applied for clean table template.
[sheetId, x1, y1, x2, y2]
which the sheetId and a range of cells need to be cleared table template.
count( hd )
This function is used to popup a count formula
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
createSheetApp( config ) : Object
Create a Sheet application based on the passed configuration.
Available since: 3.0.0
Click createSheetApp method to see detail information.
createNamedRange( hd, nameRangeObj )
creates new named range based on the specified named range objects.
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
name range object, the format such as:
{
name: 'TTTT',
rangeOfAddress: "'Sheet1'!$A$1:$A$2",
comment: "This is test",
scopeSheetId: 1235
}
If scopeSheetId is NOT appear, it is default to whole document level
Returns
{result: true}
OR
{msg: "name already in use", result: false, errorcode: 3}
For errorcode:
1 - name is already used in formula
2 - name is not valid, only accept a-zA-Z_
3 - range name already in used
commaFormat( hd )
Apply comma format to the selected cells
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
copy(hd)
Add copy action to the selected range.
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
copySheetTab( hd, sheetTabId, newName, callback, scope )
This method will copy the existing sheet tab and create a new tab. Example code
Available since: 3.0.0
Parameters
The handle information of the Sheet application your created.
The sheet tab need to be copied.
The new created sheet tab name.
The callback function which will be called after sheet tab is added.
The scope for the callback function.
createSheetWin( sheetConfig, winConfig ) : Object
Create a window instance contains the sheet application and return the handle information.
Available since: 3.0.0
Click createSheetWin method to see detail information.
currencyFormat( hd, currencyName, decimalNo, negativeFormat, showName )
Add currency format to the selected range
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
The name of currency. Such as: 'usd', 'rmb', 'eur' etc as following.
usd -- 'money_us_dollar'
rmb -- 'money_china_rmb'
eur -- 'money_european_euro'
all -- 'money_albania'
afn -- 'money_afghan'
ars -- 'money_argentina_peso'
aud -- 'money_australian_dollar'
bob -- 'money_bolivia'
brl -- 'money_brazil_real'
bgn -- 'money_bulgaria'
cad -- 'money_canada_dollar'
clp -- 'money_chile_peso'
cop -- 'money_columbia_peso'
cup -- 'money_cuba'
czk -- 'money_czech'
dkk -- 'money_denmark_krone'
egp -- 'money_egypt'
hkd -- 'money_hongkong'
huf -- 'money_hungary'
isk -- 'money_iceland'
irr -- 'money_iran'
inr -- 'money_indian_rupee'
idr -- 'money_indonsia_rupiah'
ils -- 'money_israel_shekel'
jmd -- 'money_jamaica'
jpy -- 'money_japanese_yen'
kzt -- 'money_kazakhstan'
won -- 'money_korean_won'
lbp -- 'money_lebanon'
ltl -- 'money_lithuania'
myr -- 'money_malaysia'
mxn -- 'money_mexico'
nzd -- 'money_newzealand'
ngn -- 'money_nigeria'
nok -- 'money_norway'
pkr -- 'money_pakistan'
pln -- 'money_poland'
ron -- 'money_romania'
rub -- 'money_russian_rouble'
sar -- 'money_saudi'
sgd -- 'money_sigapore'
zar -- 'money_southAf_rand'
sek -- 'money_sweden'
chf -- 'money_swit_franc'
twd -- 'money_taiwan_yen'
thb -- 'money_thailand'
try -- 'money_turky_lira'
uah -- 'money_ukraine'
gbp -- 'money_english_pound'
vnd -- 'money_viet'
zwd -- 'money_zimbabwe'
The decimal number will be keep after dot. Default value is 2. And the format will be like this: $23.56.
Choose one of format that negative number will be show. Default value is: none.
negative1 -- -$1,234.00
negative2 -- $1,234.00
negative3 -- -$1,234.00
Default is false. True means use currency name instead of money symbol
currencyFormatWin( hd, currencyName )
Popup a window for currency format to the selected range
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
The name of currency. Such as: 'usd', 'rmb', 'eur' etc as defined in above method.
cut( hd )
Add cut action to the selected range.
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
dateFormat( hd, format )
Apply date format to the selected cells
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
The format to be applied.
'Y-m-d'
'd-m-Y'
'F d, Y'
'M d, Y'
'm/d/Y'
'y-m-d'
'd-m-y'
'd-M-y'
'l, M d, Y'
dateTimeFormat( hd, format )
Apply date / time format to the selected cells
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
The format to be applied.
'Y-m-d H:i:s'
'Y-m-d H:i'
'Y-m-d g:i:s A'
'Y-m-d g:i A'
'Y-m-d G:i:s A'
desFontSize( hd )
Decrease the selected cell text size
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
deleteCommentForCoord( hd, coord, suspendEvent, suspendRefresh )
This method will delete the comments in the passed coord
Available since: 3.1.0
Parameters
The handle information of the Sheet application your created.
The coordinate array to be removed comment. Such as: [1, 2, 2, 4, 4] which shows [sheetId, x1, y1, x2, y2]
true to not fire event
true to not refresh the cells
Example: SHEET_API.deleteCommentForCoord(SHEET_API_HD, [1, 2, 2, 4, 4]);
deleteNamedRangeByName( hd, name )
This method will delete named range by name.
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
The name for the range object to be deleted.
Returns
True or False
deletePageBreak( hd )
This method will delete page break at the focus.
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
deleteRepeat( hd )
This method will delete the repeat items in the selected ranges.
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
deleteSheetTab( hd, sheetTabId, callback, scope )
This method will delete the sheet tab data of the required sheet. Example code
Available since: 3.0.0
Parameters
The handle information of the Sheet application your created.
The sheet tab need to be deleted.
The callback function which will be called after sheet tab is added.
The scope for the callback function.
fillBackgroundColor(hd, color)
Set the selected cell background Color
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
The cell background color to be set. Such as: '#FF00FF'
filter( hd )
Add filter to the selected cell ranges.
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
fontColor(hd, fontColor)
Set the selected cell text font Color
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
The font color to be set. Such as: '#FF00FF'
fontFamily(hd, fontfamily)
Set the selected cell text fontFamily
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
The font family to be set. Such as: 'Arial'
fontSize(hd, fontSize)
Set the selected cell text font size
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
The font size to be set. Such as: 12
freezeSheet( hd, row, column )
This method is used to freeze sheet in the row and column position.
Available since: 3.0.8
Parameters
The handle information of the Sheet application your created.
The row number to be frozen. Integer number >= 0.
The column number to be frozen. Integer number >= 0.
getAllChanges( hd )
This method is used to get all changes in the history. Return a list of object.
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
Returns
A list of data objects with format as:
[{
text: "Input "1" at C5"
}]
getAllDefinedNamedRangeNames( hd )
This method is used to get the specific cell json data.
Available since: 4.0.0
Parameters
The handle information of the Sheet application your created.
Returns
A list of data objects with format as:
[{
id: id,
name: name,
scopeSheetId: null, // null or 1230 -- sheetId
rangeOfAddress: 'Sheet1'!$A$1:$A$2,
comment: jsonIt.comment
}]
getCell( hd, sheetId, rowIndex, colIndex )
This method is used to get the specific cell json data.
Available since: 3.0.0
Click getCell method to see detail information.
getCellValue( hd, sheetId, rowIndex, colIndex )
This method is used to get the specific cell json data. Same as getCell but not return the cell format.
Available since: 3.0.12
Click getCell method to see detail information.
getCellsComment( hd, sheetId )
This method is used to get all cell comments in the file.
Available since: 3.0.12
Parameters
The handle information of the Sheet application your created.
The identified sheet tab id, if not defined, then use the current active tab id.
Returns
A list of data objects with format as:[{sheetId: 1, x: 2, y: 2, comment: "great work"}, {sheetId: 1, x: 2, y: 3, comment: "test", data: "cell data"}].
getCellsByAddress( hd, address )
This method is used to get all cell objects based on address value.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
Address such as: 'Sheet1'!$A$1:$A$2
Returns
A list of cell objects with format as:[{sheetId: 1, row: 2, col: 2, val: "great work"}].
getCellVariables( hd )
This method is used to get all cell variables in the file.
Available since: 3.0.12
Parameters
The handle information of the Sheet application you created.
Returns
A list of variables defined in the file in object format. Such as: {'company_name':'ABC Company', 'address':'1 infinity loop'}
getDefinedNameContent( hd, definedName )
This method is used to get defined content of name range in the file.
Available since: 3.2.0
Click getDefinedNameContent method to see detail information.
getDefinedNamedRange( hd, name )
This method is used to get range object with passed name.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
the name for the name range object
Returns
An objects with format as:
{
name: "name", // Name of named range;
rangeOfAddress: "'Sheet1'!$A$1:$A$2",
comment: "This is test", // Comment of named range; if it is null, return undefined
scopeSheetId: 1230, // Scope of named range; if it is null, this means in the whole document level.
// otherwise, it return the specific sheet range with sheetId
cells: cells // Set of cells belonging to the named range.
}.
getSelectedRangeData( hd )
This method is used to get the selected a range of cell data value.
Available since: 3.0.11
Parameters
The handle information of the Sheet application you created.
Returns
A list of data objects with format as:[{row: 2, col: 2, val: 100}, {row: 2, col: 3, val: "test"}].
getFilename( hd )
This method is used to get the file name.
Available since: 3.0.8
Parameters
The handle information of the Sheet application you created.
Returns
Currently file name
getItemValueByName( hd, name )
get the item (radio or checkbox) value by the passed name.
Available since: 3.0.12
Parameters
The handle information of the Sheet application you created.
The defined radio or checkbox name which will be returned value.
Returns
Defined radio or checkbox item value.
getJsonData( hd, compress ) : Object
This method will get the json data of the passed sheet.
Available since: 3.0.0
Click getJsonData method to see detail information.
getSheetTabData( hd ) : Object
This method will get the sheet tab data of the passed sheet. Example code
Available since: 3.0.0
Parameters
The handle information of the Sheet application you created.
Returns
The array of list tab json object
[{
id: 1,
name: 'sheet 1',
color: 'red',
}]
hideRow( hd, startRowNumber, endRowNumber, sheetId )
This method will hide the row of the related sheet with sheetId
Available since: 3.0.11
Parameters
The handle information of the Sheet application you created.
The start row number which will be hidden.
The end row number which will be hidden.
the identified sheet tab, if not defined, then use the current tab
hideColumn( hd, startColNumber, endColNumber, sheetId )
This method will hide the columns of the related sheet with sheetId
Available since: 3.0.11
Parameters
The handle information of the Sheet application you created.
The start column number which will be hidden.
The end column number which will be hidden.
the identified sheet tab, if not defined, then use the current tab
incFontSize( hd )
Increase the selected cell text size
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
insertDatePicker( hd )
Insert date picker to the selected cell
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
insertFloatingItem( hd, sheetTabId, config, callback, scope )
This function is used to insert floating item to the existing sheet tab. Currently,
enterpriseSheet supports the following floating items: wedgit, chart, and image.
Available since: 3.0.0
Click insertFloatingItem method to see detail information.
insertBackgroundImage( hd )
This function is used to popup a window for insert background image.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
insertComment( hd, scope )
This function is used to insert comments to the selected cell.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
The scope for the callback function.
insertColumn( hd, sheetId, column, colSpan )
This function is used to insert column to the existing sheet tab.
Available since: 3.0.10
Parameters
The handle information of the Sheet application you created.
The existing sheet tab.
The place that will be inserted new column. Integer number >= 0. This column will be moved right.
Option. The column number to be inserted. Integer number >= 1. Default is 1.
insertPageBreak( hd )
This function is used to insert page break at the focus.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
insertHyperlink( hd, scope )
This function is used to insert a link to the selected cell.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
The scope for the callback function.
insertRadio( hd )
This function is used to insert radio button to the selected cells.
Available since: 3.0.10
Parameters
The handle information of the Sheet application you created.
insertRow( hd, sheetId, row, rowSpan )
This function is used to insert row to the existing sheet tab.
Available since: 3.0.10
Parameters
The handle information of the Sheet application you created.
The existing sheet tab.
The place that will be inserted new row. Integer number >= 0. This row will be moved down.
Option. The row number to be inserted. Integer number >= 1. Default is 1.
insertFormula( hd )
This function is used to popup a formulas window
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
insertCheckbox( hd )
Insert checkbox to the selected cells.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
insertDropList( hd, scope )
This function is popup dropdown list.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
The scope for the callback function.
isMergedCell( hd, sheetId, row, col )
This function is used to check whether the specific cell is merged or not.
Available since: 3.0.12
Parameters
The handle information of the Sheet application you created.
The existing sheet tab.
The row number of the checked cell.
The column number of the checked cell.
italic(hd)
Make the selected cell text become italic or un-italic
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
loadData( hd, json, callback, scope )
Load the Json data into the passed sheet application.
Available since: 3.0.0
Click loadData method to see detail information.
loadTabData( hd, json, callback, scope )
Load the tab Json data into the created sheet application.
Available since: 3.1.0
Click loadTabData method to see detail information.
loadMoreData( hd, json, callback, scope )
Load more data into the created sheet file. This method should be combined with loadData method
to load big data into the sheet to improve performance.
Available since: 3.0.8
Click loadMoreData method to see detail information.
loadFile( hd, fileId, callback, scope )
Load the existing file to the passed sheet application
Available since: 3.0.0
Parameters
The handle information of the Sheet application you created.
The existing file id need to be loaded in the sheet application.
The callback function which will be called after the file is loaded.
The scope for the callback function.
loadRowsData( hd, sheetId, startRow, endRow, callback, scope )
Load the existing file to the passed sheet application
Available since: 3.3.0
Parameters
The handle information of the Sheet application you created.
The existing sheet id need to be loaded in the sheet application.
The start row number which data will be retrieved.
The end row number which data will be retrieved.
The callback function which will be called after the file is loaded.
The scope for the callback function.
max( hd )
This function is used to popup a max formula
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
min( hd )
This function is used to popup a min formula
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
mergeCell( hd )
Merge the selected cells
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
mergeCellInColumn( hd )
Merge the selected cells in column level
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
mergeCellInRow( hd )
Merge the selected cells in row level
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
mergeCellForSpan( hd, span, suspendEvent, suspendRefresh )
Merge the cells in the passed coord span
Available since: 3.1.0
Parameters
The handle information of the Sheet application you created.
The coordinate span array, it should like [sheetId, minrow, mincol, maxrow, maxcol]
true to not fire event
true to not refresh the cells
Example: SHEET_API.mergeCellForSpan(SHEET_API_HD, [1, 2, 2, 4, 4]);
moveDecimalPoint( hd, number )
Move the decimal point to left or right
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
The number of decimal point need to be moved, positive is left, negative is right
nameRange( hd )
Pop up name range window.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
paste( hd )
Add paste action to the selected range.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
percentFormat( hd, format )
Apply percent format to the selected cells.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
The format to be applied. Default is: '0.00%'.
purgeChangeList( hd, step )
Purge the changes in the history. Need pass the steps which will be purged. Max step is 20.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
the back steps to be purged. Between 1 - 20, default is 1
Example: SHEET_API.purgeChangeList(SHEET_API_HD, 5);
redo( hd )
This method is used to redo one step back for the undo processed action.
Available since: 4.0.0
The handle information of the Sheet application you created.
redoChange( hd, step )
Redo the undo changes in the history. Need pass the steps which will be redo. Max step is 20.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
the back steps to be redo. Between 1 - 20, default is 1
Example: SHEET_API.redoChange(SHEET_API_HD, 5);
removeFilter( hd, sheetId, persist )
This method is used to remove filter from sheet.
Available since: 3.4.0
Parameters
The handle information of the Sheet application you created.
The sheet which will be used to remove filter.
True means remove filter result will be saved to the backend. Otherwise, it just UI action.
Example: SHEET_API.removeFilter(SHEET_API_HD, SHEET_API_HD.sheet.getSheetId(), true);
resetHistory( hd )
This method is used to reset all the history of processed actions.
Available since: 4.0.0
The handle information of the Sheet application you created.
rotateText( hd, angle )
This method is used to set the selected cell text rotate with specific angle.
Available since: 4.0.0
The handle information of the Sheet application you created.
The angle to be rotated. Should be one of items: 45, 90, 270, 315
saveData( hd, callback, scope )
This method saves the current sheet to the backend, it will popup a window to ask input a file name if this sheet is
not created in the backendend, or it will save all data into the backend.
Available since: 3.0.0
Parameters
The handle information of the Sheet application you created.
The callback function which will be called after the file is loaded.
The scope for the callback function.
scienceFormat( hd )
Apply science format to the selected cells
Available since: 4.0.0
The handle information of the Sheet application you created.
setRangeBorder( hd, dir, color, width, style, suspendRefresh )
This function provides an interface to set a border for the selection range
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
The direction of the border, default is null which means all. And can be one of the following:
top
bottom
left
right
topbottom
outside
all
The color of the border
The width of the border
The line style of the border. Such as: solid, double, dotted, dashed
A flag to suspend the refresh action
SHEET_API.setRangeBorder(SHEET_API_HD, 'all', 'black', 1, 'solid', false);
SHEET_API.setRangeBorder(SHEET_API_HD);
setReadOnly( hd, readonly )
This method set the sheet cells as read only.
Available since: 3.0.0
Parameters
The handle information of the Sheet application you created.
The boolean value true or false.
setValueToVariable( hd, variableValues )
This method is used to assign the value to the variable.
Available since: 3.0.12
Parameters
The handle information of the Sheet application you created.
A list of variables with assigned value. Using list.*(DOT) to set a list of value to table. Such as:
{
'company_name': 'Apple Inc.',
'street_address': '1 Infinite Loop',
'city_state_zip': 'Cupertino, CA 95014',
'city_state_zip': 'Cupertino, CA 95014',
'phone': '1 800-692-7753',
'bill_to_name': 'John Doe',
'bill_to_company_name': 'ABC Company',
'bill_to_phone': '1 613 2222 222',
'bill_to_email': 'info@abc.com',
'invoice_no': '20150515001',
'list.description': ['Service Fee', 'Labor: 5 hours @ $75/hr', 'Tax(10%)'],
'list.amount': [200.00, 375.00, 57.50]
}
setCondition( hd, coord, cdtName, config )
This method will add condition to the existing file.
Available since: 3.0.11
Click setCondition method to see detail information.
setFocus( hd, row, column )
This method will set cell focus on the specific row and column to the existing file.
Available since: 3.1.1
Parameters
The handle information of the Sheet application you created.
The row number for the focus cell.
The column number for the focus cell.
setMaxColNumber( maxCol )
This method will set the sheet tab max column number.
Available since: 3.1.2
Parameters
Max column to be defined in this tab.
setMaxRowNumber( maxRow )
This method will set the sheet tab max row number.
Available since: 3.1.2
Parameters
Max row to be defined in this tab.
showSidebarBtnWin( hd, btn, callback, scope )
This method will display the related btn popup window in the sidebar.
Available since: 3.0.8
Parameters
The handle information of the Sheet application you created.
Should be one of the folling string:
chart
cellStyle
tableStyle
search
picture
widget
condition
The callback function which will be called after the event is fired.
The scope for the callback function.
showValidation( hd )
This method will display the validation popup window for the selected cells.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
showRow( hd, startRowNumber, endRowNumber, sheetId )
This method will show the row of the related sheet with sheetId
Available since: 3.0.11
Parameters
The handle information of the Sheet application you created.
The start row number which will be shown.
The end row number which will be shown.
the identified sheet tab, if not defined, then use the current tab
showColumn( hd, startColNumber, endColNumber, sheetId )
This method will show the columns of the related sheet with sheetId
Available since: 3.0.11
Parameters
The handle information of the Sheet application you created.
The start column number which will be shown.
The end column number which will be shown.
the identified sheet tab, if not defined, then use the current tab
sortCellByAsc( hd, span )
This method will sort the list of cells in ascending order
Available since: 3.0.12
Parameters
The handle information of the Sheet application you created.
The specific array list to be sorted.
[sheetId, startRow, startCol, endRow, endCol]
which define the sheetId and a range of cells need to be sorted.
sortCellByDesc( hd, span )
This method will sort the list of cells in descending order
Available since: 3.0.12
Parameters
The handle information of the Sheet application you created.
The specific array list to be sorted.
[sheetId, startRow, startCol, endRow, endCol]
which define the sheetId and a range of cells need to be sorted.
strikeline(hd)
Make the selected cell text become strikeline or un-strikeline
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
sum( hd )
This function is used to popup a sum formula
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
timeFormat( hd, format )
Apply time format to the selected cells
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
The format to be applied.
'H:i:s'
'H:i'
'g:i:s A'
'g:i A'
'G:i:s A'
toggleColumn( hd )
This method will hide or show the column title name (toggle)
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
toggleRow( hd )
This method will hide or show the row title name (toggle)
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
toggleEditable( hd )
This method will cancel or show editable cell on the selected range.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
toggleFreeze( hd )
This method will cancel or show freeze on the selected cursor position.
Available since: 3.0.8
Parameters
The handle information of the Sheet application you created.
toggleGridLine( hd, hideGridline, sheetId )
This method will hide or show the grid line. Example code
Available since: 3.0.0
Parameters
The handle information of the Sheet application you created.
true or false. If true, it means hide the grid line. Otherwise, the grid line is shown.
the id of the sheet to toggle the grid line, if not defined then use the actived sheet.
toggleSplit( hd )
This method will cancel or show split on the selected cursor position.
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
toggleTitleBar( hd, visible )
This method will hide or show the title bar.
Available since: 3.3.1
Parameters
The handle information of the Sheet application you created.
true or false. If true, it means titlebar visible. Otherwise, the titlebar is hidden.
SHEET_API.toggleTitleBar(SHEET_API_HD, false);
toggleToolBar( hd, visible )
This method will hide or show the tool bar.
Available since: 3.3.1
Parameters
The handle information of the Sheet application you created.
true or false. If true, it means toolbar visible. Otherwise, the toolbar is hidden.
SHEET_API.toggleToolBar(SHEET_API_HD, false);
undo( hd )
This method is used to undo one step back for the processed action.
Available since: 4.0.0
The handle information of the Sheet application you created.
underline(hd)
Make the selected cell text become underline or un-underline
Available since: 4.0.0
Parameters
The handle information of the Sheet application you created.
updateCells( hd, cellObjs, callback, scope )
This method is used to update a list of cells for the passed sheet object.
Available since: 3.0.0
Click updateCells method to see detail information.
updateFloatings( hd, cellObjs, callback, scope )
This method is used to update a list of floatings for the passed sheet object.
Available since: 3.1.2
Click updateFloatings method to see detail information.
updateGroups( hd, groupObjs )
This method is used to update a list of groups.
Available since: 3.0.11
The handle information of the Sheet application you created.
The updated group json information.
[{
dir: 'row',
start: 2,
end: 5
}, {
dir: 'row',
start: 3,
end: 5
}, {
dir: 'col',
start: 2,
end: 5
}, {
dir: 'col',
start: 2,
end: 4
}]
updateSheetTab( hd, sheetTabConfig, callback, scope, notSwitchToNewSheet )
This method will update the sheet tab data of the required sheet. Example code
Available since: 3.0.0
Parameters
The handle information of the Sheet application you created.
The updating sheet tab information. Name is the updating sheet tab name and this should be unique.
Position is the updating sheet tab new position. sheetId is the required updating sheet id. If it is ignore,
default is currently active sheet tab.
{
name: 'Market',
color: 'darkblue',
sheetId: 0,
position: 0
}
The callback function which will be called after sheet tab is added.
The scope for the callback function.
- notSwitchToNewSheet : Boolean
If it is true, it means not switch to the sheet tab after added.
wordWrap( hd )
Set the selected cell text wrap.
Available since: 4.0.0
The handle information of the Sheet application you created.
zoom( hd, size )
Zoom the selected sheet with passed zoom size
Available since: 4.0.0
The handle information of the Sheet application you created.
The size to be zoomed, such as 2, 1, 0.5, 0.25
« Keyboard shortcuts
Create sheet »