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 |