Excel Editor for VUE 3

Vue3 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

For Vue 2 version, please refer to the repository: https://github.com/cscan/vue-excel-editor

Getting started

Get the package:

``bash npm install vue3-excel-editor`

Register VueExcelEditor in your app entrypoint:

`js import { createApp } from 'vue' import App from './App.vue' import VueExcelEditor from 'vue3-excel-editor'

const app = createApp(App) app.use(VueExcelEditor) ...`

In your template

`html

`Outlook`

!Simple Outlook

`Props List`

`$3`

`$3`

@ - Function can return a promise

`Hot Key List`

`Events List`

`$3`

| Name | Arguemnts | Description | | :--- | :--- | :--- | | update | updateItemArray | Update cell information | | delete | deleteItemArray | Delete row information | | select | selectIdArray, direction | Emit when rows are selected/unselected | | cell-click | rowPos, colPos | Emit when a cell be clicked before focus | | cell-focus | {rowPos, colPos, cell, rec} | Emit when a cell got focus | | cell-blur | {rowPos, colPos, cell, rec} | Emit when a cell got blur | | page-changed | pageTopPos, pageBottomPos | Emit when the page has changed | | setting | setting | Emit when setting (column width, invisible state) is changed | | validate-error | error, row, field | Emit when validation (both field and row level) occured |

`Methods List`

`$3`

* = optional argument

`Variable List`

`$3`

@ - allow v-model binding

AOA = Array of Array, i.e. [[...], [...]] AOO = Array of Object, i.e. [{...}, {...}]

I suppose you try to read all the above variables only. Do not try to modify any value of the above variables, unless you deeply walk through the codes and know the consequences.

`Example`

An example to show 5x6 table:

`html`

Note that the component will read the vue-excel-column when it is created. You may also skip all the column definitions. The control will help you to "guess" the rest:

`html`

The sample data contains 5 records:

`js 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'} ] } }`

`$3`

You may capture the @delete and the @update event for saving purpose.

`html ...`

Specified "key-field" is necessary, it will reflect in rec.keys in the following:

`js 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() } }`

`$3`

Set the reference by ref="..."

`html ...`

`js 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.

`$3`

`js 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.

`$3`

The grid setting such as column width and column label can be saved in the localStorage of client browser by specified "remember" prop:

`html`

You may also capture the @setting event to handle more specifics.

`$3`

You may specify the column label in vue-excel-column label prop. However, it will persist after mounted. If you want to change the column label after mounted, you may try to update the variable fields. For example

`js this.$refs.grid.fields.forEach((field) => { if (field.name === 'col23') field.label = 'Product' if (field.label === '') field.label = '(' + field.name + ')' }) this.$forceUpdate() // remember to call vue update to reflesh the display`

`$3`

Same as column label, you may make the column visible/invisible in vue-excel-column label prop. However, it will persist after mounted. If you want to change it after mounted, you may try to update the variable fields. For example

`js this.$refs.grid.fields.forEach((field) => { if (field.name === 'col23') field.invisible = false }) this.$forceUpdate() // remember to call vue update to reflesh the display`

`$3`

The following provides the button to export the grid content.

`html`

`js methods: { exportAsExcel () { const format = 'xlsx' const exportSelectedOnly = true const filename = 'test' this.$refs.grid.exportTable(format, exportSelectedOnly, filename) } exportAsCsv () { const format = 'csv' const exportSelectedOnly = true const filename = 'test' this.$refs.grid.exportTable(format, exportSelectedOnly, filename) } }`

Note that only xlsx format supports compression.

`$3`

The selected or unselected rows will be passed to the provided trigger method

`html`

`js methods: { onSelect (selectedRows) { console.log(selectedRows) } }`

You may also want to watch the selected records count for displaying the action buttons. For example:

`html ...

`js data () { return { count: 0 } } computed: { showDeleteAction () { return this.count > 0 // Show if any records selected }, showSendEmailInvitationAction () { return this.count === 1 // Show if single record is selected }, showSendBirthdayGreetingAction () { // Show only if any selected people birthday matched today if (this.count > 0) { return this.$refs.grid.getSelectedRecords().filter(item => item.birth === today).length > 0 else return false } }`

`$3`

You could achieve this by placing the method in change prop

`html`

`js 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.

`html`

`js methods: { onBeforePhoneChange (newVal) { return new Promise((done) => { axios.post('checkPhoneNumber', { phone: newVal }) .then(done(true)) .catch((e) => { console.error(e) done(false) }) }) } }`

However, the promise routine may cause your web page has low performance. I suggest you use a validation prop to show the wrong content in a grid with the validation error.

`$3`

`html`

#### Filter + Footer Rows

Specified filter-row prop to show filter row

`html`

If you don't want to show the footer, specified the no-footer prop

`html`

!Filter + Footer Rows

#### Filtering

The filtering is one of the focusing features. It supports regular expression and windows wild card syntax.

!Filtering

Component supports the prefx likes <, >, =, >=, <=, <>, ~ (regular expression) and wild-card * and ? symbol. Examples: | Example | Description | | :--- | :--- | | abc | The values contain ABC | | abc* | The values start by ABC | | *abc | The values end by ABC | | =abc | The values equal to ABC | | <>abc | The values do not equal to ABC | | >= 100 | The values are greater or equal to 100 | | < 0 | The values are smaller than 0 | | po-18*5?? | The values start from PO-18 and the 3rd-last char is 5 | | = | The values are empty | | ~. | The values are not empty | | ~.*tpx[ ]+ck | The values have TPX and CK text and they have spaces in between | | ~[ ] | The values contain space | | ~^so|ary$ | The values start by SO or end by ARY | | ~[ ]+$|^[ ]+ | The values start or end by spaces | | ~^[^ ]*$ | The values have no space |

Note that all filters are case-insensitive.

#### Sorting

!Sorting

It is possible to assign custom sorting function for specified column:

`html`

`js const moment = require('moment')

methods: { sortingBirth (a, b) { return moment(a).diff(moment(b), 'days') } }`

#### Autocomplete

When user enters text in cell and holds a second, component will show 10 matched occurences for user to choose. !Autocomplete

#### Options

`html`

Specified the type = "select" for Options column. This works like Autocomplete, but the list is provided and fixed. !Options

#### Map

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.

`html`

!Map

#### Select

Click the row label to select the row. The component supports Excel-style which using Shift-click and Ctrl-click (Meta for OSX) combination to select multiple rows. You may also interested in the free-select prop to select the multiple rows without holding the shift key.

!Select

#### Multi-Update

When the user updates any cell during select multiple rows, all cells of the same column of those selected rows will be updated.

!Multi-Update

#### Sticky column

`html`

Specified "sticky" means the specified column is frozen when horizontal scrolling. Most likely sticky columns are leftmost.

#### Paging

`html ...`

To gain better performance, I suggest you use paging by not specify "no-paging" prop. The component automatically calculates 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.

`$3`

The following is for column validation.

`html`

`js methods: { validPhoneNum (content, oldContent, record, field) { 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 } }`

!Validation

The following is for row validation. It will be triggered for any changes. The error message will be shown at number column.

`html`

`js methods: { validWholeRecord (content, oldContent, record, field) { if (record.age !== moment().diff(record.birth, 'years')) return 'The age and birth do not match' return '' // return empty string if there is no error } }`

You may want to receive the validation error. Component will emit an event "validation-error" if row or field validation error changed.

`html`

`js methods: { logValidationError (error, row, field) { // For row validation, the field argument will be null value console.log(error, row, field) } }`

`$3`

`html`

!Summary

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 Number: >0 String: non-empty Check: "Y", "1" or "T" DateTime: at or later than the current time 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 in the @update event.

`$3`

This is a nice feature in enterprise applications. Actually, I was learning from SAP UI. When the user holds the function key (Alt-key here) and move the mouse over the cell content, the text will show as a link. If user clicks on the link, your custom function will be triggered. The following example shows how to route to the user profile page by clicking on the name column cell.

`html`

`js 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}) }, showLinkOrString(record) { return record.gender === 'M' // Only gender M show the link } }`

All links will be set as readonly by default.

`$3`

Sometimes displaying text and the store value will be different. In order to deal with this, you could use column properties to-text and to-value.

`html`

`js 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, '') }, }`

`$3`

Sometimes we want to display the text in badge style.

`html`

`js methods: { badgeColor (rec) { return 'blue' } }`

All badge will be set as readonly by default.

`$3`

Sometimes we want to display the list of actions for record processing, such as Edit/Remove.

`html`

`js methods: { doAction (rec) { if (rec.do === 'Edit') // do Edit process else if (rec.do === 'Remove') // do Remove process } }`

All action will be set as non-readonly to make it selectable. The component will assign a to-text function to return empty string. If you want to show a static text in cell, you could use placeholder prop.

`$3`

For password editing:

`html`

`$3`

If a column set to hide-duplicaiton, the component will compare the cell content with the above content and hide it by setting to transparent color and remove the border-top. It also affects the next column which also set to hide-duplication. Here is an example:

`html`

!Hide Duplication

All field set to hide duplication will be set as readonly by default.

`$3`

Grouping is a little bit tricky. Sometimes we want to show the first row of a set of record and hide the rest of the row based on the cell value. For example, we want to group the gender by M and F and the first record are the header that holds the statistics of each group. If this is the case, you could use grouping attribute. For example:

`html`

`js jsondata = [ {region: 'US', area: 'AZ', user: "hc", name: "Harry Cole", phone: "1-415-2345678", gender: "M", age: 25, birth: "1997-07-01"}, {region: 'US', area: 'AZ', user: "sm", name: "Simon Minolta", phone: "1-123-7675682", gender: "M", age: 25, birth: "1999-11-12"}, {region: 'US', area: 'CL', user: "ra", name: "Rachael Amy", phone: "1-456-9981212", gender: "F", age: 19, birth: "2000-06-11"}, {region: 'US', area: 'CL', user: "ag", name: "Mary George", phone: "1-556-1245684", gender: "F", age: 19, birth: "2002-08-01"}, {region: 'CN', area: 'GZ', user: "kl", name: "Kenny Linus", phone: "1-891-2345685", gender: "F", age: 29, birth: "1990-09-01"} ])`

The above shows the way in specifying 2 groups (region + area). Both group set to collapse by default.

!Grouping

All field set to be grouping will be set as readonly and hide duplication by default.

`$3`

The developer may override the default values through localized-label prop.

`html`

`js 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', noRecordIndicator: 'No record' } }`

This is for Chinese user

`js 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: '找不到关键栏位', noRecordIndicator: '沒有纪录' } }``

Compatibility

Chrome 79+, FireFox 71+, Safari 13+

License

MIT

Status

Thanks to edutucci and reinerBa in helping to migrate to Vue3. This project is in an early stage of development. Any contribution is welcome. :D

Excel Editor for VUE 3

Vue3 plugin for displaying and editing the array-of-object in Excel style. It supports the following features:

For Vue 2 version, please refer to the repository: https://github.com/cscan/vue-excel-editor

Getting started

Get the package:

``bash npm install vue3-excel-editor`

Register VueExcelEditor in your app entrypoint:

`js import { createApp } from 'vue' import App from './App.vue' import VueExcelEditor from 'vue3-excel-editor'

const app = createApp(App) app.use(VueExcelEditor) ...`

In your template

`html

`Outlook`

!Simple Outlook

`Props List`

`$3`

@ - Function can return a promise

`Hot Key List`

`Events List`

`$3`

`Methods List`

`$3`

* = optional argument

`Variable List`

`$3`

@ - allow v-model binding

AOA = Array of Array, i.e. [[...], [...]] AOO = Array of Object, i.e. [{...}, {...}]

I suppose you try to read all the above variables only. Do not try to modify any value of the above variables, unless you deeply walk through the codes and know the consequences.

`Example`

An example to show 5x6 table:

`html`

Note that the component will read the vue-excel-column when it is created. You may also skip all the column definitions. The control will help you to "guess" the rest:

`html`

The sample data contains 5 records:

`$3`

You may capture the @delete and the @update event for saving purpose.

`html ...`

Specified "key-field" is necessary, it will reflect in rec.keys in the following:

`$3`

Set the reference by ref="..."

`html ...`

`$3`

`js 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.

`$3`

The grid setting such as column width and column label can be saved in the localStorage of client browser by specified "remember" prop:

`html`

You may also capture the @setting event to handle more specifics.

`$3`

`js this.$refs.grid.fields.forEach((field) => { if (field.name === 'col23') field.invisible = false }) this.$forceUpdate() // remember to call vue update to reflesh the display`

`$3`

The following provides the button to export the grid content.

`html`

Note that only xlsx format supports compression.

`$3`

The selected or unselected rows will be passed to the provided trigger method

`html`

`js methods: { onSelect (selectedRows) { console.log(selectedRows) } }`

You may also want to watch the selected records count for displaying the action buttons. For example:

`html ...

`$3`

You could achieve this by placing the method in change prop

`html`

The change function can return a promise.

`html`

However, the promise routine may cause your web page has low performance. I suggest you use a validation prop to show the wrong content in a grid with the validation error.

`$3`

`html`

#### Filter + Footer Rows

Specified filter-row prop to show filter row

`html`

If you don't want to show the footer, specified the no-footer prop

`html`

!Filter + Footer Rows

#### Filtering

The filtering is one of the focusing features. It supports regular expression and windows wild card syntax.

!Filtering

Note that all filters are case-insensitive.

#### Sorting

!Sorting

It is possible to assign custom sorting function for specified column:

`html`

`js const moment = require('moment')

methods: { sortingBirth (a, b) { return moment(a).diff(moment(b), 'days') } }`

#### Autocomplete

When user enters text in cell and holds a second, component will show 10 matched occurences for user to choose. !Autocomplete

#### Options

`html`

Specified the type = "select" for Options column. This works like Autocomplete, but the list is provided and fixed. !Options

#### Map

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.

`html`

!Map

#### Select

!Select

#### Multi-Update

When the user updates any cell during select multiple rows, all cells of the same column of those selected rows will be updated.

!Multi-Update

#### Sticky column

`html`

Specified "sticky" means the specified column is frozen when horizontal scrolling. Most likely sticky columns are leftmost.

#### Paging

`html ...`

`$3`

The following is for column validation.

`html`

!Validation

The following is for row validation. It will be triggered for any changes. The error message will be shown at number column.

`html`

You may want to receive the validation error. Component will emit an event "validation-error" if row or field validation error changed.

`html`

`js methods: { logValidationError (error, row, field) { // For row validation, the field argument will be null value console.log(error, row, field) } }`

`$3`

`html`

!Summary

`$3`

`html`

All links will be set as readonly by default.

`$3`

Sometimes displaying text and the store value will be different. In order to deal with this, you could use column properties to-text and to-value.

`html`

`$3`

Sometimes we want to display the text in badge style.

`html`

`js methods: { badgeColor (rec) { return 'blue' } }`

All badge will be set as readonly by default.

`$3`

Sometimes we want to display the list of actions for record processing, such as Edit/Remove.

`html`

`js methods: { doAction (rec) { if (rec.do === 'Edit') // do Edit process else if (rec.do === 'Remove') // do Remove process } }`

`$3`

For password editing:

`html`

`$3`

`html`

!Hide Duplication

All field set to hide duplication will be set as readonly by default.

`$3`

`html`

The above shows the way in specifying 2 groups (region + area). Both group set to collapse by default.

!Grouping

All field set to be grouping will be set as readonly and hide duplication by default.

`$3`

The developer may override the default values through localized-label prop.

`html`

This is for Chinese user

Compatibility

Chrome 79+, FireFox 71+, Safari 13+

License

MIT

Status

Thanks to edutucci and reinerBa in helping to migrate to Vue3. This project is in an early stage of development. Any contribution is welcome. :D