Documentation
Quick reference
Considering the following example:
<html> <script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script> <script src="https://jsuites.net/v4/jsuites.js"></script> <link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" /> <div id="spreadsheet"></div> <script> var spreadsheet = jspreadsheet(document.getElementById('spreadsheet'), { minDimensions: [4,10] }); </script> </html>
1
Methods
Method | Example |
---|---|
Data related | |
getData: Get the full or partial table data |
// 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. |
// Set the data from a JavaScript array spreadsheet.setData(Array data) |
setJson: Set the table data from a json object, null will reload what is in memory. |
// Set the data from a json format spreadsheet.getJson(Object data) |
refresh: Refresh the data. |
// Refresh the table with current data spreadsheet.refresh(); |
getJson: Get the full or partial table data in JSON format @Param boolan onlyHighlighedCells - Get only highlighted cells |
// Refresh the table with current data spreadsheet.getJson(Boolean onlyHighlightedCells); |
filter: Programatically apply filters to a column. |
// Apply filters programmatically spreadsheet.filter(Integer columnNumber, Object with values); |
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 jExcel - 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() |
2
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, String data, Number x, Number y) Before the paste action is performed. Can return parsed or filtered data, can cancel the action when return false. |
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 |
onchangefooters | onchangefooters(Object worksheet, Number column, String newValue, String oldValue) When the footers are 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. |
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 jExcel
3
Initialization
Parameter | Description |
---|---|
cloud | Hash identification for the Jexcel 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 |
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 |
4
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 |