jExcel api指引

前端操作Excel的api工具:https://github.com/SheetJS/sheetjs

 

下面是UI工具

jExcel quick reference

 

 

METHODS

Method	Example
getData: Get the full or partial table data @Param boolan onlyHighlighedCells - Get only highlighted cells	myTable.getData([bool]);
getJson: Get the full or partial table data in JSON format @Param boolan onlyHighlighedCells - Get only highlighted cells	myTable.getData([bool]);
getRowData: Get the data from one row by number @Param integer rowNumber - Row number	myTable.getRowData([int]);
setRowData: Set the data from one row by number @Param integer rowNumber - Row number @param array rowData - Row data	myTable.setRowData([int], [array]);
getColumnData: Get the data from one column by number @Param integer columnNumber - Column number	myTable.getColumnData([int]);
setColumnData: Set the data from one column by number @Param integer columnNumber - Column number @param array colData - Column data	myTable.setColumnData([int], [array]);
setData: Set the table data @Param json newData - New json data, null will reload what is in memory.	myTable.setData([json]);
setMerge: Merge cells @Param string columnName - Column name, such as A1. @Param integer colspan - Number of columns @Param integer rowspan - Number of rows	myTable.setMerge([string], [int], [int]);
getMerge: Get merged cells properties @Param string columnName - Column name, such as A1.	myTable.getMerge([string]);
removeMerge: Destroy merged by column name @Param string columnName - Column name, such as A1.	myTable.removeMerge([string]);
destroyMerged: Destroy all merged cells	myTable.destroyMerge();
getCell: get current cell DOM @Param string columnName - str compatible with excel, or as object.	myTable.getCell([string]);
getLabel: get current cell DOM innerHTML @Param string columnName - str compatible with excel, or as object.	myTable.getLabel([string]);
getValue: get current cell value @Param mixed cellIdent - str compatible with excel, or as object.	myTable.getValue([string]);
getValueFromCoords: get value from coords @Param integer x @Param integer y	myTable.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	myTable.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	myTable.getValueFromCoords([integer], [integer], [string], [bool]);
resetSelection: Reset the table selection @Param boolean executeBlur - execute the blur from the table	myTable.resetSelection([bool]);
updateSelection: select cells @Param object startCell - cell object @Param object endCell - cell object @Param boolean ignoreEvents - ignore onselection event	myTable.updateSelection([cell], [cell], true);
updateSelectionFromCoords: select cells @Param integer x1 @Param integer y1 @Param integer x2 @Param integer y2	myTable.updateSelectionFromCoords([integer], [integer], [integer], [integer]);
getWidth: get the current column width @Param integer columnNumber - column number starting on zero	myTable.getWidth([integer]);
setWidth: change column width @Param integer columnNumber - column number starting on zero @Param string newColumnWidth - New column width	myTable.setWidth([integer], [integer]);
getHeight: get the current row height @Param integer rowNumber - row number starting on zero	myTable.getHeight([integer]);
setHeight: change row height @Param integer rowNumber - row number starting on zero @Param string newRowHeight- New row height	myTable.setHeight([integer], [integer]);
getHeader: get the current header by column number @Param integer columnNumber - Column number starting on zero	myTable.getHeader([integer]);
getHeaders: get all header titles	myTable.getHeaders();
setHeader: change header by column @Param integer columnNumber - column number starting on zero @Param string columnTitle - New header title	myTable.setHeader([integer], [string]);
getStyle: get table or cell style @Param mixed - cell identification or null for the whole table.	myTable.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	myTable.setSyle([object], [string], [string]);
resetStyle: remove all style from a cell @Param string columnName - Column name, example: A1, B3, etc	myTable.resetStyle([string]);
getComments: get cell comments @Param mixed - cell identification or null for the whole table.	myTable.getComments([string]);
setComments: set cell comments @Param cell - cell identification @Param text - comments	myTable.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	myTable.orderBy([integer], [boolean]);
getConfig: get table definitions	myTable.getConfig();
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	myTable.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	myTable.deleteColumn([integer], [integer]);
moveColumn: change the column position @Param integer columnPosition @Param integer newColumnPosition	myTable.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	myTable.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	myTable.deleteRow([integer], [integer]);
moveRow: change the row position @Param integer rowPosition @Param integer newRowPosition	>myTable.moveRow([integer], [integer]);
download: get the current data as a CSV file @Param bool - true to download parsed formulas.	myTable.download([bool]);
getMeta: get the table or cell meta information @Param mixed - cell identification or null for the whole table.	myTable.getMeta([string]);
setMeta: set the table or cell meta information @Param mixed - json with whole table meta information.	myTable.setMeta[mixed]);
fullscreen: Toogle table fullscreen mode @Param boolan fullscreen - define fullscreen status as true or false	myTable.fullscreen([bool]);
getSelectedRows: Get the selected rows @Param boolan asIds - Get the rowNumbers or row DOM elements	myTable.getSelectedRows([bool]);
getSelectedColumns: Get the selected columns @Param boolan asIds - Get the colNumbers or row DOM elements	myTable.getSelectedColumns([bool]);
showColumn: show column by number	myTable.showIndex([int]);
hideColumn: hide column by number	myTable.hideColumn([int]);
showIndex: show column of index numbers	myTable.showIndex();
hideIndex: hide column of index numbers	myTable.hideIndex();
search: search in the table, only if directive is enabled during inialization. @Param string - Search for word	myTable.search([string]);
resetSearch: reset search table	myTable.resetSearch();
whichPage: Which page showing on jExcel - Valid only when pagination is true.	myTable.whichPage();
page: Go to page number- Valid only when pagination is true. @Param integer - Go to page number	myTable.page([integer]);
undo: Undo last changes	myTable.undo();
redo: Redo changes	myTable.redo();

Working example

 

2

 

EVENTS

Event	description
onload	This method is called when the method setData
onbeforechange	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	After a column value is changed.
onafterchanges	After all changes are applied in the table.
onpaste	After a paste action is performed in the javascript table.
onbeforepaste	Before the paste action is performed. Used to parse any input data, should return the data.
oninsertrow	After a new row is inserted.
onbeforeinsertrow	Before a new row is inserted. You can cancel the insert event by returning false.
ondeleterow	After a row is excluded.
onbeforedeleterow	Before a row is deleted. You can cancel the delete event by returning false.
oninsertcolumn	After a new column is inserted.
onbeforeinsertcolumn	Before a new column is inserted. You can cancel the insert event by returning false.
ondeletecolumn	After a column is excluded.
onbeforedeletecolumn	Before a column is excluded. You can cancel the insert event by returning false.
onmoverow	After a row is moved to a new position.
onmovecolumn	After a column is moved to a new position.
onresizerow	After a change in row height.
onresizecolumn	After a change in column width.
onselection	On the selection is changed.
onsort	After a colum is sorted.
onfocus	On table focus
onblur	On table blur
onmerge	On column merge
onchangeheader	On header change
onundo	On undo is applied
onredo	On redo is applied
oneditionstart	When a openEditor is called.
oneditionend	When a closeEditor is called.
onchangestyle	When a setStyle is called.
onchangemeta	When a setMeta is called.
onchangepage	When the page is changed.

Example on handling events on jExcel

 

3

 

INITIALIZATION

Parameter	description
url	Load a external json file from this URL: string
data	Load this data into the javascript table: array
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
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.
fullscreen: boll	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
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
updateTable	Method to config custom script execution. NOTE: This does not work with lazyLoading, Pagination or Search options.
nestedHeaders	Define the nested headers, including title, colspan, etc: object
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

Working example