Quick reference
Considering the following example:
<html>
<script src="https://jspreadsheet.com/v7/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v7/jspreadsheet.css" type="text/css" />
<div id="spreadsheet"></div>
<script>
let spreadsheet = jspreadsheet(document.getElementById('spreadsheet'), {
minDimensions: [4,10]
});
</script>
</html>
Methods
Method | Example |
---|---|
Data related | |
getData : Get the full or partial table data | spreadsheet.getData(Boolean onlyHighlighedCells) |
setData: Set the table data from a JavaScript array, null will reload what is in memory. | spreadsheet.setData(Array data) |
setJson: Set the table data from a json object, null will reload what is in memory. | spreadsheet.getJson(Object data) |
refresh: Refresh the data. | spreadsheet.refresh(); |
getJson: Get the full or partial table data in JSON format @Param boolan onlyHighlighedCells - Get only highlighted cells |
spreadsheet.getJson(Boolean onlyHighlightedCells); |
filter: Programatically apply filters to a column. | spreadsheet.filter(Integer columnNumber, Object with values); spreadsheet.filter(0, { US: true, BR: true}); |
getRowData: Get the data from one row by number @Param integer rowNumber - Row number |
spreadsheet.getRowData([int]); |
setRowData: Set the data from one row by number @Param integer rowNumber - Row number @param array rowData - Row data |
spreadsheet.setRowData([int], [array]); |
getColumnData: Get the data from one column by number @Param integer columnNumber - Column number |
spreadsheet.getColumnData([int]); |
setColumnData: Set the data from one column by number @Param integer columnNumber - Column number @param array colData - Column data |
spreadsheet.setColumnData([int], [array]); |
setMerge: Merge cells @Param string columnName - Column name, such as A1. @Param integer colspan - Number of columns @Param integer rowspan - Number of rows |
spreadsheet.setMerge([string], [int], [int]); |
getMerge: Get merged cells properties @Param string columnName - Column name, such as A1. |
spreadsheet.getMerge([string]); |
removeMerge: Destroy merged by column name @Param string columnName - Column name, such as A1. |
spreadsheet.removeMerge([string]); |
destroyMerged: Destroy all merged cells | spreadsheet.destroyMerge(); |
getCell : get current cell DOM @Param string columnName - str compatible with excel, or as object. |
spreadsheet.getCell([string]); |
getLabel : get current cell DOM innerHTML @Param string columnName - str compatible with excel, or as object. |
spreadsheet.getLabel([string]); |
getValue: get current cell value @Param mixed cellIdent - str compatible with excel, or as object. |
spreadsheet.getValue([string]); |
getValueFromCoords: get value from coords @Param integer x @Param integer y |
spreadsheet.getValueFromCoords([integer], [integer]); |
setValue: change the cell value @Param mixed cellIdent - str compatible with excel, or as object. @Param string Value - new value for the cell @Param bool force - update readonly columns |
spreadsheet.setValue([string], [string], [bool]); |
setValueFromCoords: get value from coords @Param integer x @Param integer y @Param string Value - new value for the cell @Param bool force - update readonly columns |
spreadsheet.getValueFromCoords([integer], [integer], [string], [bool]); |
resetSelection: Reset the table selection @Param boolean executeBlur - execute the blur from the table |
spreadsheet.resetSelection([bool]); |
updateSelection: select cells @Param object startCell - cell object @Param object endCell - cell object @Param boolean ignoreEvents - ignore onselection event |
spreadsheet.updateSelection([cell], [cell], true); |
updateSelectionFromCoords: select cells @Param integer x1 @Param integer y1 @Param integer x2 @Param integer y2 |
spreadsheet.updateSelectionFromCoords([integer], [integer], [integer], [integer]); |
getWidth: get the current column width @Param integer columnNumber - column number starting on zero |
spreadsheet.getWidth([integer]); |
setWidth: change column width @Param integer columnNumber - column number starting on zero @Param string newColumnWidth - New column width |
spreadsheet.setWidth([integer], [integer]); |
getHeight: get the current row height @Param integer rowNumber - row number starting on zero |
spreadsheet.getHeight([integer]); |
setHeight: change row height @Param integer rowNumber - row number starting on zero @Param string newRowHeight- New row height |
spreadsheet.setHeight([integer], [integer]); |
getHeader: get the current header by column number @Param integer columnNumber - Column number starting on zero |
spreadsheet.getHeader([integer]); |
getHeaders: get all header titles | spreadsheet.getHeaders(); |
setHeader: change header by column @Param integer columnNumber - column number starting on zero @Param string columnTitle - New header title |
spreadsheet.setHeader([integer], [string]); |
getStyle: get table or cell style @Param mixed - cell identification or null for the whole table. |
spreadsheet.getStyle([string])); |
setStyle: set cell(s) CSS style @Param mixed - json with whole table style information or just one cell identification. Ex. A1. @param k [optional]- CSS key @param v [optional]- CSS value |
spreadsheet.setSyle([object], [string], [string]); |
resetStyle: remove all style from a cell @Param string columnName - Column name, example: A1, B3, etc |
spreadsheet.resetStyle([string]); |
getComments: get cell comments @Param mixed - cell identification or null for the whole table. |
spreadsheet.getComments([string]); |
setComments: set cell comments @Param cell - cell identification @Param text - comments |
spreadsheet.setComments([string], [string]); |
orderBy: reorder a column asc or desc @Param integer columnNumber - column number starting on zero @Param smallint sortType - One will order DESC, zero will order ASC, anything else will toggle the current order |
spreadsheet.orderBy([integer], [boolean]); |
getConfig: Get the table configuration variables | spreadsheet.getConfig(); |
setConfig: Set any new table configuration variables | spreadsheet.setConfig([object]); |
insertColumn: add a new column @param mixed - num of columns to be added or data to be added in one single column @param int columnNumber - number of columns to be created @param boolean insertBefore @param object properties - column properties |
spreadsheet.insertColumn([mixed], [integer], [boolean], [object]); |
deleteColumn: remove column by number @Param integer columnNumber - Which column should be excluded starting on zero @param integer numOfColumns - number of columns to be excluded from the reference column |
spreadsheet.deleteColumn([integer], [integer]); |
moveColumn: change the column position @Param integer columnPosition @Param integer newColumnPosition |
spreadsheet.moveColumn([integer], [integer]); |
insertRow: add a new row @Param mixed - number of blank lines to be insert or a single array with the data of the new row @Param integer rowNumber - reference row number @param boolean insertBefore |
spreadsheet.insertRow([mixed], [integer], [boolean]); |
deleteRow: remove row by number @Param integer rowNumber - Which row should be excluded starting on zero @Param integer numOfRows - number of lines to be excluded |
spreadsheet.deleteRow([integer], [integer]); |
moveRow: change the row position @Param integer rowPosition @Param integer newRowPosition |
>spreadsheet.moveRow([integer], [integer]); |
download: get the current data as a CSV file @Param bool - true to download parsed formulas. |
spreadsheet.download([bool]); |
getMeta: get the table or cell meta information @Param mixed - cell identification or null for the whole table. |
spreadsheet.getMeta([string]); |
setMeta: set the table or cell meta information @Param mixed - json with whole table meta information. |
spreadsheet.setMeta[mixed]); |
fullscreen: Toogle table fullscreen mode @Param boolan fullscreen - define fullscreen status as true or false |
spreadsheet.fullscreen([bool]); |
getSelectedRows: Get the selected rows @Param boolan asIds - Get the rowNumbers or row DOM elements |
spreadsheet.getSelectedRows([bool]); |
getSelectedColumns: Get the selected columns @Param boolan asIds - Get the colNumbers or row DOM elements |
spreadsheet.getSelectedColumns([bool]); |
showColumn: show column by number | spreadsheet.showIndex([int]); |
hideColumn: hide column by number | spreadsheet.hideColumn([int]); |
showIndex: show column of index numbers | spreadsheet.showIndex(); |
hideIndex: hide column of index numbers | spreadsheet.hideIndex(); |
search: search in the table, only if directive is enabled during inialization. @Param string - Search for word |
spreadsheet.search([string]); |
resetSearch: reset search table | spreadsheet.resetSearch(); |
whichPage: Which page showing on Jspreadsheet - Valid only when pagination is true. | spreadsheet.whichPage(); |
page: Go to page number- Valid only when pagination is true. @Param integer - Go to page number |
spreadsheet.page([integer]); |
undo: Undo last changes | spreadsheet.undo(); |
redo: Redo changes | spreadsheet.redo(); |
getColumnOptions: Get all options from a column or custom cell. @Param integer - columnNumber @Param integer - rowNumber. |
spreadsheet.getColumnOptions([integer], [integer]); |
getType: Get a column type and all column attributes. @Param integer - columnNumber |
spreadsheet.getType([integer]); |
setType: Set the column and all column attributes. @Param integer - columnNumber @Param mixed - string for type, or object with type and all attributes. |
spreadsheet.setType([integer], [mixed]); |
updateNestedHeader: Update a nested header attributes. @Param integer x @Param integer y @Param object properties - Properties are title, colspan, rowspan. |
spreadsheet.updateNestedHeader([integer], [integer], [object]); |
getNestedColumns: Get all columns below a nested header. @Param integer x @Param integer y |
spreadsheet.getNestedColumns([integer], [integer]); |
setBorder: Create or update a border. @Param integer x1 @Param integer y1 @Param integer x2 @Param integer y2 @Param string borderName |
spreadsheet.setBorder([integer], [integer],[integer], [integer], [string]); |
setFooter: Create a fix footer row. @Param array data |
spreadsheet.setFooter([array]); |
getHighlighted: Get the highlighted coordinates | spreadsheet.getHighlighted(); |
selectAll: Select all cells | spreadsheet.selectAll(); |
copy: Copy data from the spreadsheet | spreadsheet.copy() |
Events
Event | description |
---|---|
onevent | onevent(arguments) This is the general event and it is called together with any other events. The arguments are different depending on the event. |
onbeforesave | onbeforesave(DOMElement el, Object instance, Array data) Before any data is sent to the backend. Can be used to overwrite the data or to cancel the action when return false. |
onsave | onsave(DOMElement el, Object instance, Array data) After the data is sent to the server. |
onload | onload(DOMElement el, Object instance) This method is called when the data in the spreadsheet is ready. |
onbeforechange | onbeforechange(DOMElement el, DOMElement cell, Number x, Number y, Mixed value) Before a column value is changed. NOTE: It is possible to overwrite the original value, by return a new value on this method. v3.4.0+ |
onchange | onchage(DOMElement el, DOMElement cell, Number x, Number y, Mixed newValue, Mixed oldValue) After a column value is changed. |
onafterchanges | onafterchanges(DOMElement el, Array records) After all changes are applied in the table. |
onbeforepaste | onbeforepaste(DOMElement el, Array data, Number x, Number y) Before the paste action is performed. Can return parsed or filtered data, can cancel the action when return false. From 7.9.11 the data receives an Array. |
onpaste | onpaste(DOMElement el, Array data) After a paste action is performed in the spreadsheet. |
onbeforeinsertrow | onbeforeinsertrow(DOMElement el, Number rowNumber, Number numOfRows, Boolean insertBefore) Before a new row is inserted. You can cancel the insert event by returning false. |
oninsertrow | oninsertrow(DOMElement el, Number rowNumber, Number numOfRows, Boolean insertBefore) After a new row is inserted. |
onbeforedeleterow | onbeforedeleterow(DOMElement el, Number rowNumber, Number numOfRows) Before a row is deleted. You can cancel the delete event by returning false. |
ondeleterow | ondeleterow(DOMElement el, Number rowNumber, Number numOfRows, Array rowDOMElements, Array rowData, Array cellAttributes) After a row is excluded. |
onbeforeinsertcolumn | onbeforeinsertcolumn(DOMElement el, Number columnNumber, Number numOfColumns, Boolean insertBefore) Before a new column is inserted. You can cancel the insert event by returning false. |
oninsertcolumn | oninsertcolumn(DOMElement el, Number columnNumber, Number numOfColumns, Array historyRecords, Boolean insertBefore) After a new column is inserted. |
onbeforedeletecolumn | onbeforedeletecolumn(DOMElement el, Number columnNumber, Number numOfColumns) Before a column is excluded. You can cancel the insert event by returning false. |
ondeletecolumn | ondeletecolumn(DOMElement el, Number columnNumber, Number numOfColumns, Array affectedDOMElements, Array historyProperties, Array cellAttributes) After a column is excluded. |
onmoverow | onmoverow(DOMElement el, Number origin, Number destination) After a row is moved to a new position. |
onmovecolumn | onmovecolumn(DOMElement el, Number origin, Number destination) After a column is moved to a new position. |
onresizerow | onresizerow(DOMElement el, Mixed row, Mixed height, Mixed oldHeight) After a height change for one or more rows. |
onresizecolumn | onresizecolumn(DOMElement el, Mixed column, Mixed width, Mixed oldWidth) After a column width change for one or more columns. |
onselection | onselection(DOMElement el, Number px, Number py, Number ux, Number uy, Number origin) When the selection is changed. |
onbeforesort | onbeforesort(DOMElement el, Number column, Number direction, Array newOrderValues) Before a column is sorted. It is possible to cancel the action when returning false. |
onsort | onsort(DOMElement el, Number column, Number direction) When a column is sorted. |
onfocus | onfocus(DOMElement el) On table focus |
onblur | onblur(DOMElement el) On table blur |
onmerge | onmerge(DOMElement el, String cellName, Number colspan, Number rowspan) On column merge |
onchangeheader | onchangeheader(DOMElement el, Number column, String newValue, String oldValue) When the title is changed |
onundo | onundo(DOMElement el, Object historyRecord) On undo is applied |
onredo | onredo(DOMElement el, Object historyRecord) On redo is applied |
oneditionstart | oneditionstart(DOMElement el, DOMElement cell, Number x, Number y) When an openEditor is called. |
oneditionend | oneditionend(DOMElement el, DOMElement cell, Number x, Number y, Mixed newValue, Boolean save) When a closeEditor is called. |
onchangestyle | onchangestyle(DOMElement el, Mixed mixed, String key, String value) When a setStyle is called. |
onchangemeta | onchangemeta(DOMElement el, Mixed mixed, String key, String value) When a setMeta is called. |
onbeforechangepage | onbeforechangepage(DOMElement el, Number pageNumber, Number oldPage, Number quantityPerPage) Before the page is changed. Can cancel the action when return is false. |
onchangepage | onchangepage(DOMElement el, Number pageNumber, Number oldPageNumber) When pagination is enabled and the page is changed. |
onbeforecreateworksheet | onbeforecreateworksheet(JSON worksheetOptions) Add or change the options of a new worksheet. |
oncreateworksheet | oncreateworksheet(JSON worksheetOptions) Add or change the options of a new worksheet. |
ondeleteworksheet | ondeleteworksheet(DOMElement el, Number worksheetNumber) When a worksheet is removed. |
onmoveworksheet | onmoveworksheet(DOMElement el, Number from, Number to) When a worksheet position is changed. |
onopenworksheet | onopenworksheet(DOMElement el, Number worksheet) When a worksheet is opened. |
onchangerowid | onopenworksheet(DOMElement el, Object Instance, Array rows) When update the id from rows. |
onbeforesearch | onbeforesearch(DOMElement el, String str, Array rowNumbers, Object search) Action to be executed before the search. It can be used to cancel or to intercept and customize the searching process. |
onsearch | onsearch(DOMElement el, String str, Array rowNumbers, Object search) After the filter has been applied to the rows. |
onbeforefilter | onbeforefilter(DOMElement el, Array filters, Array data) Action to be executed before filtering rows. It can cancel the action by returning false. |
onfilter | onfilter(DOMElement el, Array filters, Array data) After the filter has been applied to the rows. |
oncomments | oncomments(DOMElement el, Object cells) After a new comment is added or updated. |
Example on handling events on Jspreadsheet
Initialization
Parameter | Description |
---|---|
cloud | Hash identification for the Jspreadsheet Cloud Engine for realtime spreadsheet sharing: guid |
url | Load a external json file from this URL: string |
data | Load this data into the javascript table: array |
json | Load this json into the javascript table: json |
persistance | Remote data persistance: mixed. Bool when the URL to load is the same for the updates. String for a custom backend persistance server. |
copyCompatibility | When is true copy and export will bring formula results, if false will bring formulas: boolean |
rows | Row properties: height.: object |
columns | Column type, title, width, align, dropdown options, text wrapping, mask, etc.: object |
cells | Cell type, title, width, align, dropdown options, text wrapping, mask, etc.: object |
nestedHeaders | Define the nested headers, including title, colspan, etc: object |
defaultColWidth | Default width for a new column: integer |
defaultColAlign | Default align for a new column: [center, left, right] |
minSpareRows | Minimum number of spare rows: [integer] |
minSpareCols | Minimum number of spare cols: [integer] |
minDimensions | Minimum table dimensions: [cols,rows] |
allowExport | Allow table export: bool |
includeHeadersOnDownload | Include header titles on download: bool |
columnSorting | Allow column sorting: bool |
columnDrag | Allow column dragging: bool |
columnResize | Allow column resizing: bool |
rowResize | Allow row resizing: bool |
rowDrag | Allow row dragging: bool |
editable | Allow table edition: bool |
allowInsertRow | Allow insert a new row: bool |
allowManualInsertRow | Allow user to insert a new row: bool |
allowInsertColumn | Allow insert a new column: bool |
allowManualInsertColumn | Allow user to create a new column: bool |
allowDeleteRow | Allow delete a row: bool |
allowDeleteColumn | Allow delete a column: bool |
allowRenameColumn | Allow rename a column: bool |
allowComments | Allow comments over the cells: bool |
wordWrap | Global text wrapping: bool |
csv | Load a external CSV file from this URL: string |
csvFileName | Default filename for a download method: string |
csvHeaders | Load header titles from the CSV file: bool |
csvDelimiter | Default delimiter for the CSV file: string |
selectionCopy | Allow selection copy: bool |
mergeCells | Cells to be merged in the table innitialization: object |
toolbar | Add custom toolbars: object |
search: bool | Allow search in the table |
pagination: integer | Break the table by pages |
paginationOptions: [array of numbers] | Number of records per page: 25,50,75,100 for example |
paginationWheel: bool | Use the mouse wheel to change pages in pagination is available |
fullscreen: bool | Fullscreen mode |
lazyLoading: bool | Activate the table lazyloading |
loadingSpin: bool | Activate the loading spin |
tableOverflow | Allow table overflow: bool |
tableOverflowResizable | Allow table resize: bool |
tableHeight | Force the max height of the table: CSS String |
tableWidth | Force the max width of the table: CSS String |
comments | Initial table comments: object |
meta | Meta information: object |
style | Cells style in the table innitialization: object |
parseFormulas | Enable execution of formulas inside the table |
autoIncrement | Auto increment actions when using the dragging corner |
autoCastings | Convert strings into numbers when is possible |
cache | Cache formula results. It would bring faster results and it is mandatory for cross-worksheet calculations. Should be avoided together with large number of records. |
secureFormulas | Force formulas to be capital letters. |
stripHTML | Remove HTML from cells. |
freezeColumns | Number of freezed columns |
freezeNested | Number of nested headers freezed. |
orderBy | Initial table order |
tabs | Allow tabs |
allowCreateTabs | Allow create new tabs |
parseTableFirstRowAsHeader | When creating a spreadsheet based on a HTML table, parse the first row as headers. |
parseTableAutoCellType | Change the column type to based on frequency of value repetions in a column or in the format of the data. |
filters | Allow column filters |
footers | Fixed footer data |
plugins | Plugin extensions |
updateTable | Method to config custom script execution. NOTE: This does not work with lazyLoading, Pagination or Search options. |
contextMenu | Context menu content: function() { return customMenu } |
text: object | All messages to be customized |
Translations
Key | Default value |
---|---|
noRecordsFound | No records found |
showingPage | Showing page {0} of {1} entries |
show | Show |
entries | entries |
insertANewColumnBefore | Insert a new column before |
insertANewColumnAfter | Insert a new column after |
deleteSelectedColumns | Delete selected columns |
renameThisColumn | Rename this column |
orderAscending | Order ascending |
orderDescending | Order descending |
insertANewRowBefore | Insert a new row before |
insertANewRowAfter | Insert a new row after |
deleteSelectedRows | Delete selected rows |
editComments | Edit comments |
addComments | Add comments |
comments | Comments |
clearComments | Clear comments |
copy | Copy... |
paste | Paste... |
saveAs | Save as... |
about | About |
areYouSureToDeleteTheSelectedRows | Are you sure to delete the selected rows? |
areYouSureToDeleteTheSelectedColumns | Are you sure to delete the selected columns? |
thisActionWillDestroyAnyExistingMergedCellsAreYouSure | This action will destroy any existing merged cells. Are you sure? |
thisActionWillClearYourSearchResultsAreYouSure | This action will clear your search results. Are you sure? |
thereIsAConflictWithAnotherMergedCell | There is a conflict with another merged cell |
invalidMergeProperties | Invalid merged properties |
cellAlreadyMerged | Cell already merged |
noCellsSelected | No cells selected |