Vue2 plugin for displaying and editing the array-of-object in Excel style. It supports the following features:
- Excel-like UI
- Real 2-way data binding
- Column filtering
- Column sorting
- Export to Excel/CSV
- Pagination
- Row selection
- Update the cells in all selected rows
- Edit key support: up, down, left, right, page-up, page-down, tab, del, bs, enter, esc
- Ctrl/meta key support: Ctrl-A, Ctrl-C, Ctrl-V, Ctrl-Z, Ctrl-F, Ctrl-G, Ctrl-L
- Column validation
- Cell error tooltip
- Custom column header
- Custom Row style
- Readonly column
- Column visibility
- Column sequence
- Column width adjustment
- Undo update
- Copy & Paste
- Mass import Excel data
Get the package:
npm install vue-excel-editor
Register VueExcelEditor in your app entrypoint:
import Vue from 'vue'
import VueExcelEditor from 'vue-excel-editor'
Vue.use(VueExcelEditor)
In your template
<template>
<vue-excel-editor v-model="jsondata">
<vue-excel-column field="user" label="User" />
<vue-excel-column field="name" label="Name" />
<vue-excel-column field="phone" label="Contact" />
<vue-excel-column field="gender" label="Gender" />
<vue-excel-column field="age" label="Age" />
<vue-excel-column field="birth" label="Date Of Birth" />
</vue-excel-editor>
</template>
Name | Mandatory | Type | Description |
---|---|---|---|
v-model | Mandatory | Array | Edited data in Array Of Object |
page | Optional | Number | Specific page size, default is auto-calculating by screen height |
no-paging | Optional | Boolean | Disable paging feature, default is false |
no-num-col | Optional | Boolean | No number column, default is false |
filter-row | Optional | Boolean | Show fixed filter row, default is false |
no-footer | Optional | Boolean | No footer row, default is false |
no-finding | Optional | Boolean | Disable find key (ctrl-f) and finding dialog, default is false |
no-finding-next | Optional | Boolean | Disable find-next key (ctrl-g), default is false |
free-select | Optional | Boolean | Select multiple rows without pressing ctrl/meta key |
autocomplete | Optional | Boolean | Enable autocomplete of all columns, default is false |
readonly | Optional | Boolean | Set all columns read only, default is false |
readonly-style | Optional | Object | The style of the read-only cell |
height | Optional | String | Define the exact height in px of the component, default is 'auto' |
width | Optional | String | Define the maximum width in px of the component, default is '100%' |
row-style | Optional | Function | Conditional row formatting, default is null |
cell-style | Optional | Function | Conditional cell formatting, default is null |
header-label | Optional | Function | Func to return the label, parameter are the field label, field object |
record-label | Optional | Function | Func to return the label, parameters are recordPosition, record object |
localized-label | Optional | Object | Customize labels and messages for localization purpose |
n-filter-count | Optional | Number | Number of items to be listed in filter dialog. Default is 200 |
remember | Optional | Boolean | Remember the setting in localStorage, default is false |
enterToEast | Optional | Boolean | Move the cell to right instead of bottom when hits enter |
allow-add-col | Optional | Boolean | Allow to show the add column button during column resize |
add-col | Optional | Function | Define the column definition when column is adding |
no-header-edit | Optional | Boolean | Not allow header label editing |
Name | Mandatory | Type | Description |
---|---|---|---|
field | Mandatory | String | Field name, row object key |
label | Optional | String | Header label, default is field name |
type | Optional | String | The column type |
readonly | Optional | Boolean | Read-only, default is parent prop: readonly |
init-style | Optional | Object | Cell inital style in css |
sticky | Optional | Boolean | Fixed column at left of the table, no response on horizontal scrolling |
invisible | Optional | Boolean | Column visibility, default is false |
width | Optional | String | Specified column width, default is '100px' |
change | Optional | Function@ | The function to be triggered when the data of this column changed |
validate | Optional | Function | The function to validate and return the error message |
key-field | Optional | Boolean | Specified the key field which is included in keys parameter in @update event |
allow-keys | Optional | Array, Function | Array of char which allow to input |
mandatory | Optional | String | If not empty, it is showed as error when it modified as blank, default is '' |
length-limit | Optional | Number | Not allow to input when the content length reaches the limit |
autocomplete | Optional | Boolean | Allow autocomplete popup when editing, default is parent prop: autocomplete |
pos | Optional | Number | Specified column sequence |
text-transform | Optional | String | Force the input to upppercase or lowercase when editing |
text-align | Optional | String | Text alignment, default is 'left' |
options | Optional | Array, Function@ | For type = 'select', define the selectable options in array |
options | Optional | Object, Function | For type = 'map', define the selectable options in hash |
summary | Optional | String | Summary: 'sum', 'avg', 'max', 'min'. Default is null |
link | Optional | Function | The function to react to the alt-click on cell text |
to-text | Optional | Function | The function to convert from object value to edit-text |
to-value | Optional | Function | The function to convert from edit-text to object value |
@ - Function can return a promise
Type | Value | Display text | Justify | Validation | Allow Keys | Allow Null |
---|---|---|---|---|---|---|
string | string | string | left | none | all | Y |
number | numeric | numberic | right | none | -.0123456789 | Y |
select | array | string | left | value with options | all | Y |
map | hash | string | left | value with options | all | Y |
check10 | 1 or 0 | 1 or 0 | center | none | 1 or 0 | Y |
checkYN | Y or N | Y or N | center | none | ynYN | Y |
checkTF | T or F | T of F | center | none | tfTF | Y |
date | yyyy-mm-dd | yyyy-mm-dd | left | valid date | none | Y |
datetime | yyyy-mm-dd hh:mn | yyyy-mm-dd hh:mn | left | valid datetime | none | Y |
datetimesec | yyyy-mm-dd hh:mn:ss | yyyy-mm-dd hh:mn:ss | left | valid datetimesec | none | Y |
datetick | unix timestamp | yyyy-mm-dd | left | valid date | none | Y |
datetimetick | unix timestamp | yyyy-mm-dd hh:mn | left | valid datetime | none | Y |
datetimesectick | unix timestamp | yyyy-mm-dd hh:mn:ss | left | valid datetimesec | none | Y |
Name | Condition | Description |
---|---|---|
Ctrl/Meta A | Table Focus | Select all rows |
Ctrl/Meta C | Cell Focus | Select the cell text to clipboard |
Ctrl/Meta V | Cell Focus | Place the clipboard text to cell |
Ctrl/Meta Z | Table Focus | Undo the last update |
Ctrl/Meta F | Table Focus | Popup the "Find" dialog |
Ctrl/Meta G | After Find | Continue to find the text |
Ctrl/Meta L | Cell Focus | Force to show autocomplete list, or the option list for "select" typed column |
Name | Argument | Description |
---|---|---|
update | AOA | Update cell information |
delete | AOA | Delete row information |
select | AOO | Emit when rows are selected/unselected |
setting | setting | Emit when setting (column width, invisible state) is changed |
AOA = Array of Array, i.e. [[...], [...]]
AOO = Array of Object, i.e. [{...}, {...}]
Name | Arguments | Description |
---|---|---|
firstPage | Move to the first page | |
lastPage | Move to the last page | |
prevPage | Move to the previous page | |
nextPage | Move to the next page | |
moveNorth | Move the cursor cell to upper cell | |
moveSouth | Move the cursor cell to lower cell | |
moveWest | Move the cursor cell to previous cell | |
moveEast | Move the cursor cell to next cell | |
moveTo | row, col* | Move the cursor cell to cell(row, col) |
moveToNorthWest | Move the cursor cell to 1st row 1st col | |
moveToNorthEast | Move the cursor cell to 1st row last col | |
moveToSouthWest | Move the cursor cell to last row 1st col | |
moveToSouthEast | Move the cursor cell to last row last col | |
doFind | text | Find the specified text in whole table and locate the cursor cell |
doFindNext | Contnue the last find | |
sort | n, pos | Sort the column specified by pos, n = 1 (ascending) or -1 (descending) |
newRecord | rec* | Call this to new an empty record, return the rec pointer |
deleteRecord | rowpos | Delete the record in pos rowpos |
deleteSelectedRecords | Delete all the selected records | |
selectRecord | row | Select the row |
selectRecordByKeys | keys | Select the row by keys hash |
selectRecordById | id | Select the row by $id |
unSelectRecord | row | UnSelect the row |
clearAllSelected | Unselect all selected rows | |
getSelectedRecords | Get an array of the selected row hash | |
exportTable | fmt* | Export the filtered table as xlsx/csv |
importTable | callback* | Import the specified formatted xlsx |
undoTransaction | Undo the last update | |
setFilter | name, text | Set the filter text on column name |
clearFilter | name* | Clear the filter text on column name |
columnSuppress | Hide the column if all values are null or empty | |
calSummary | Calculate the summary of all columns |
* = optional argument
Name | Type | Description |
---|---|---|
fields | AOO | It contains the column spec create when mount |
filterColumn | Object | Contains the current filters, developer can access the filter string via this |
selected | Object | Contains all the selected rows, the key is row number and the value is internal $id |
selectedCount | Number | Number of rows are selected |
errmsg | Object | Contains all the validation error messages, the key is internal $id plus field name |
redo | AOA | The buffer of undo, it will be removed after undo or table changed |
pageTop | Number | The top row number of the current page |
AOA = Array of Array, i.e. [[...], [...]]
AOO = Array of Object, i.e. [{...}, {...}]
An example to show 5x6 table:
<template>
<vue-excel-editor v-model="jsondata" filter-row>
<vue-excel-column field="user" label="User ID" type="string" width="80px" />
<vue-excel-column field="name" label="Name" type="string" width="150px" />
<vue-excel-column field="phone" label="Contact" type="string" width="130px" />
<vue-excel-column field="gender" label="Gender" type="select" width="50px" :options="['F','M','U']" />
<vue-excel-column field="age" label="Age" type="number" width="70px" />
<vue-excel-column field="birth" label="Date Of Birth" type="date" width="80px" />
</vue-excel-editor>
</template>
You may also skip all the column definitions. The control will help you to "guess" the rest
<template>
<vue-excel-editor v-model="jsondata" filter-row />
</template>
export default {
name: 'app',
data: {
jsondata: [
{user: 'hc', name: 'Harry Cole', phone: '1-415-2345678', gender: 'M', age: 25, birth: '1997-07-01'},
{user: 'sm', name: 'Simon Minolta', phone: '1-123-7675682', gender: 'M', age: 20, birth: '1999-11-12'},
{user: 'ra', name: 'Raymond Atom', phone: '1-456-9981212', gender: 'M', age: 19, birth: '2000-06-11'},
{user: 'ag', name: 'Mary George', phone: '1-556-1245684', gender: 'F', age: 22, birth: '2002-08-01'},
{user: 'kl', name: 'Kenny Linus', phone: '1-891-2345685', gender: 'M', age: 29, birth: '1990-09-01'}
]
}
}
You may capture the @delete and @update event for saving purpose.
<vue-excel-editor v-model="jsondata" @delete="onDelete" @update="onUpdate">
<vue-excel-column field="user" label="User ID" type="string" width="80px" key-field />
...
</vue-excel-editor>
Specified "key-field" is necessary, it will reflect in rec.keys in the following:
methods: {
onDelete (records) {
records = records.map(rec => ['del', rec.keys.join()])
redis.multi(records).exec()
},
onUpdate (records) {
records = records.map(rec => ['hset', rec.keys.join(), rec.name, rec.newVal])
redis.multi(records).exec()
}
}
Set the reference by ref="..."
<vue-excel-editor ref="grid" v-model="jsondata">
<vue-excel-column field="user" label="User ID" type="string" width="80px" key-field />
...
</vue-excel-editor>
methods: {
newRecord () {
const rec = {
user: 'nm',
name: 'Norman Morris',
phone: '1-222-3333333',
gender: 'M',
age: 28,
birth: '1993-05-16'
}
// Call this to new record
this.$refs.grid.newRecord(rec)
}
}
After the record created, a set of @update events will be fired. If you undo a newRecord transaction, component will generate the corresponding @delete events. In case you do not care about the undo, you may skip this by appending the new record in v-model variable (jsondata array) directly.
methods: {
delRecord () {
this.$refs.grid.deleteRecord(0) // delete the 1st record: Harry Cole
}
}
The component will generate the corresponding @delete events. You may also interest in the deleteSelectedRecords() method.
The grid setting such as column width can be saved in the localStorage of client browser by specified "remember" prop:
<template>
<vue-excel-editor v-model="jsondata" remember>
...
</vue-excel-editor>
</template>
You may also capture the @setting event to handle more specifics.
The selected rows will be passed to the provided trigger method
<template>
<vue-excel-editor v-model="jsondata" @select="onSelect">
...
</vue-excel-editor>
</template>
methods: {
onSelect (selectedRows) {
console.log(selectedRows)
}
}
You could achieve this by placing the method in change prop
<vue-excel-column field="name" label="Name" type="string" width="150px" :change="onBeforeNameChange" />
<vue-excel-column field="phone" label="Contact" type="string" width="130px" :change="onBeforePhoneChange" />
<vue-excel-column field="birth" label="Date Of Birth" type="date" width="80px" :change="onBeforeBirthChange" />
methods: {
onBeforeNameChange (...args) {
console.log(...args) // show all the arguments: newVal, oldVal, oldRow, field
},
onBeforePhoneChange (newVal) {
// This example demonstrate how to ensure the uniqueness of the phone number
if (this.jsondata.findIndex(row => row.phone === newVal) >= 0)
return false // return false to reject the update
},
onBeforeBirthChange (newVal, oldVal, row) {
row.age = moment().diff(newVal, 'years') // calculate the age if birth is changed
}
}
The change function can return a promise.
<vue-excel-column field="phone" label="Contact" type="string" width="130px" :change="onBeforePhoneChange" />
methods: {
onBeforePhoneChange (newVal) {
return new Promise((done) => {
axios.post('checkPhoneNumber', {
phone: newVal
})
.then(done(true))
.catch((e) => {
console.error(e)
done(false)
})
})
}
}
However, it is possible to make your webpage has performance issue. I suggest you use validation prop to show the wrong content in grid with the validation error.
<template>
<vue-excel-editor v-model="jsondata" no-paging autocomplete filter-row>
<vue-excel-column field="user" label="User ID" type="string" width="80px" key-field />
<vue-excel-column field="name" label="Name" type="string" width="150px" />
<vue-excel-column field="phone" label="Contact" type="string" width="130px" :validate="validPhoneNum" />
<vue-excel-column field="gender" label="Gender" type="select" width="50px" :options="['F','M','U']" />
<vue-excel-column field="age" label="Age" type="number" width="70px" />
<vue-excel-column field="birth" label="Date Of Birth" type="date" width="80px" />
</vue-excel-editor>
</template>
The filtering fesature of this component is very strong. It suuports regular expression and windows wild card syntax.
Component supports the prefx likes <, >, =, >=, <=, <>, ~ (regular expression) and wild-card * and ? symbol. Examples:
Example | Description |
---|---|
>= 100 | The values are greater or equal to 100 |
< 0 | The values are smaller than 0 |
<>mary | The values do not equal to MARY |
mon* | The values start by MON |
*mon | The values end by MON |
mon | The values contain MON |
=mon | The values equal to MON |
= | The values are empty |
po-18*5?? | The values start from PO-18 and the 3rd-last char is 5 |
~.*tpx[ ]+ck | The values have TPX and CK text and they have spaces in between |
~. | The values are not empty |
~[ ] | The values contain space |
~^so|ary$ | The values start by SO or end by ARY |
~[ ]+$|^[ ]+ | The values end or start by spaces |
~^[^ ]*$ | The values have no space |
Note that all filters are case-insensitive.
When user enters text in cell and holds a second, component will show 10 matched occurences for user to choose.
<vue-excel-column field="gender" label="Gender" type="select" width="50px" :options="['F','M','U']" />
Specified the type = "select" for Options column. This works like Autocomplete, but the list is provided and fixed.
The "map" typed column is the same as "select" typed column, but the record value is not the same as the display text. The options prop required to provide the mapping of the value and text.
<vue-excel-column field="gender" label="Gender" type="map" width="50px" :options="{M:'Male','F':'Female','U':'Unknown'}" />
Click the row label to select the row. Component supports Excel-style which using shift-click and ctrl-click (Meta for OSX) combination to select multiple rows. You may also intereset in the free-select prop to select the multiple rows without holding the shift key.
When user updates any cell during selecting multiple rows, all cells of the same column of those selected rows will be updated.
<vue-excel-column field="user" label="User ID" type="string" width="80px" key-field sticky />
Specified "sticky" means the specified column is freeze when horizontal scrolling. Most likely sticky columns are leftmost.
<vue-excel-editor v-model="jsondata" autocomplete filter-row>
...
</vue-excel-editor>
To gain better performance, I suggest you use paging by not specify "no-paging" prop. The component automatically calculate the page size once detecting the outer boundary resized. If you want to set the page size manually, try the prop "page". If more than 1 page is detected, the footer (horizontal scroll bar) will show the first/previous/next/last links for page navigation. You may customize these links by "localized-label" prop.
<vue-excel-column field="phone" label="Contact" type="string" width="130px" :validate="validPhoneNum" />
methods: {
validPhoneNum (content) {
if (content === '') return 'Mandatory field'
if (!/^[0-9]{1}-[0-9]{3}-[0-9]{7}$/.test(content)) return 'Invalid Phone Number'
return '' // return empty string if there is no error
}
}
<vue-excel-column field="age" label="Age" type="number" width="70px" summary="sum" />
Summary prop supports "sum", "min", "max", "avg" and "count".
Note that "count" will instruct the component to count the cell which hold the following condition
0 for number-typed column
non-empty for string-typed column
"Y", "1" or "T" for check-typed column
at or late than this time for date/time-typed column
Use this with care. The summary calculation eats resource, so it only calculates when the number of records changed (i.e. New, delete, filter). It does not recalculate if user changes the cell content. You may trigger the calculation manually by calling calSummary method by the @update event.
Actually this nice feature I was learnt from SAP UI - When user holds the function key (Alt-key here) and move the mouse over the cell, the cell text will show as a link. If user clicks on it, your custom function will be triggered. The following shows how to route to user profile page by clicking on the name column cell.
<vue-excel-column field="name" label="Name" type="string" width="150px" :link="routeToUserFunc" />
methods: {
// Hold Alt Key and click on any name, program will route to the page "User Profile"
routeToUserFunc (content, record) {
this.$router.push(`/user/${record.user}`)
}
}
Sometimes displaying text and the store value will be different. In order to deal with this, you could use column proproties to-text and to-value.
<vue-excel-column field="phone" label="Contact" type="string" width="130px" :to-text="phoneToText" :to-value="phoneToVal" />
methods: {
phoneToText (val) {
// convert number to hyphenated i.e. 14152345678 => 1-415-2345678
return val.replace(/^(.)(...)(.*)$/, '$1-$2-$3')
},
phoneToVal (text) {
// convert hyphenated text to number i.e. 1-415-2345678 => 14152345678
return text.replace(/-/g, '')
},
}
Developer may override the default values through localized-label prop.
<template>
<vue-excel-editor v-model="jsondata" :localized-label="myLabels">
...
</vue-excel-editor>
</template>
data: {
myLabels = {
footerLeft: (top, bottom, total) => `Record ${top} to ${bottom} of ${total}`,
first: 'First',
previous: 'Previous',
next: 'Next',
last: 'Last',
footerRight: {
selected: 'Selected:',
filtered: 'Filtered:',
loaded: 'Loaded:'
},
processing: 'Processing',
tableSetting: 'Table Setting',
exportExcel: 'Export Excel',
importExcel: 'Import Excel',
back: 'Back',
reset: 'Default',
sortingAndFiltering: 'Sorting And Filtering',
sortAscending: 'Sort Ascending',
sortDescending: 'Sort Descending',
near: '≒ Near',
exactMatch: '= Exact Match',
notMatch: '≠ Not Match',
greaterThan: '> Greater Than',
greaterThanOrEqualTo: '≥ Greater Than or Equal To',
lessThan: '< Less Than',
lessThanOrEqualTo: '≤ Less Than Or Equal To',
regularExpression: '~ Regular Expression',
customFilter: 'Custom Filter',
listFirstNValuesOnly: n => `List first ${n} values only`,
apply: 'Apply',
noRecordIsRead: 'No record is read',
readonlyColumnDetected: 'Readonly column detected',
columnHasValidationError: (name, err) => `Column ${name} has validation error: ${err}`,
noMatchedColumnName: 'No matched column name',
invalidInputValue: 'Invalid input value',
missingKeyColumn: 'Missing key column'
}
}
This is for Chinese user
data: {
myLabels = {
footerLeft: (top, bottom) => `纪录 ${top} 至 ${bottom}`,
first: '头页',
previous: '上一页',
next: '下一页',
last: '尾页',
footerRight: {
selected: '选择:',
filtered: '过滤:',
loaded: '载入:'
},
processing: '工作中',
tableSetting: '表格设定',
exportExcel: '汇出 Excel',
importExcel: '汇入 Excel',
back: '关',
reset: '预设',
sortingAndFiltering: '排序及过滤',
sortAscending: '小至大排序',
sortDescending: '大至小排序',
near: '≒ 接近',
exactMatch: '= 等于',
notMatch: '≠ 不等于',
greaterThan: '> 大于',
greaterThanOrEqualTo: '≥ 大于或等于',
lessThan: '< 少于',
lessThanOrEqualTo: '≤ 少于或等于',
regularExpression: '~ 正规表示式',
customFilter: '过滤内容',
listFirstNValuesOnly: n => `只列出 ${n} 项`,
apply: '应用',
noRecordIsRead: '没有纪录被读取',
readonlyColumnDetected: '不可更新唯读纪录',
columnHasValidationError: (name, err) => `纪录栏位 ${name} 发生核实错误: ${err}`,
noMatchedColumnName: '没有能配对之栏位',
invalidInputValue: '输入错误内容',
missingKeyColumn: '找不到关键栏位'
}
}
Chrome 79+, FireFox 71+, Safari 13+
MIT
This project is in an early stage of development. Any contribution is welcome. :D