Vxe UI vue vxe-table v4.8+ 與 v3.10+ 匯出 xlsx、支援匯出合併、設定樣式、寬高、邊框、字型、背景、超連結、圖片等、所有常用的 Excel 格式都能自定義，使用非常簡單，純前端實現複雜的匯出。
安裝外掛
npm install vxe-pc-ui@3.2.9 vxe-table@3.10.1 @vxe-ui/plugin-export-xlsx@3.0.6 exceljs@4.2.1// ...
import { VxeUI } from 'vxe-pc-ui'
import VxeUIPluginExportXLSX from '@vxe-ui/plugin-export-xlsx'
// ...
VxeUI.use(VxeUIPluginExportXLSX)在 index.html 引入對應的解析庫，把對應的 js 下載到本地引入。
<script src="https://vxeui.com/umd/exceljs@4.4.0/dist/exceljs.min.js"></script>預設匯出
預設支援文字和單元格合併等匯出，只需要開啟對應的功能就可以直接使用了。
<template>
<div>
<vxe-button @click="exportEvent">高階匯出</vxe-button>
<vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
</div>
</template>
<script>
export default {
data () {
const gridOptions = {
border: true,
showFooter: true,
mergeCells: [
{ row: 0, col: 2, rowspan: 2, colspan: 2 },
{ row: 2, col: 1, rowspan: 3, colspan: 2 }
],
exportConfig: {
type: 'xlsx'
},
columns: [
{ field: 'seq', type: 'seq', width: 70 },
{
title: '分組1',
children: [
{ field: 'name', title: 'Name' },
{ field: 'role', title: 'Role' }
]
},
{ field: 'sex', title: 'Sex' },
{ field: 'age', title: 'Age' }
],
data: [
{ id: 10001, name: '張三', role: 'Develop', sex: 'Man', age: 28, address: 'test abc' },
{ id: 10002, name: '李四', role: 'Test', sex: 'Women', age: 22, address: '廣東省' },
{ id: 10003, name: '王五', role: 'PM', sex: 'Man', age: 32, address: '上海' },
{ id: 10004, name: '老六', role: 'Designer', sex: 'Women', age: 24, address: 'Shanghai' },
{ id: 10005, name: '小七', role: 'Designer', sex: 'Man', age: 37, address: '廣東省' },
{ id: 10006, name: '小八', role: 'Designer', sex: 'Man', age: 39, address: 'Shanghai' }
],
footerData: [
{ seq: '合計', name: '12人', no1: 356 }
]
}
return {
gridOptions
}
},
methods: {
exportEvent () {
const $grid = this.$refs.gridRef
if ($grid) {
$grid.openExport()
}
}
}
}
</script>
引數說明
透過 sheetMethod 自定義方法實現，引數說明 exportConfig.sheetMethod({
workbook 工作簿物件
worksheet 表物件
options 當前引數
})
自定義邊框
<template>
<div>
<vxe-button @click="exportEvent">點選匯出</vxe-button>
<vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
</div>
</template>
<script>
export default {
data () {
const gridOptions = {
border: true,
showFooter: true,
exportConfig: {
sheetMethod (params) {
const { worksheet } = params
worksheet.eachRow(excelRow => {
excelRow.eachCell(excelCell => {
// 設定單元格邊框
excelCell.border = {
top: {
style: 'thin',
color: {
argb: 'ff0000'
}
},
left: {
style: 'thin',
color: {
argb: 'ff0000'
}
},
bottom: {
style: 'thin',
color: {
argb: 'ff0000'
}
},
right: {
style: 'thin',
color: {
argb: 'ff0000'
}
}
}
})
})
}
},
columns: [
{ field: 'seq', type: 'seq', width: 70 },
{
title: '分組1',
children: [
{ field: 'name', title: 'Name' },
{ field: 'role', title: 'Role' }
]
},
{ field: 'sex', title: 'Sex' },
{ field: 'no1', title: 'NO1' },
{ field: 'no2', title: 'NO2 String', cellType: 'string' }
],
data: [
{ id: 10001, name: '張三', role: 'Develop', sex: 'Man', no1: '028', no2: '028' },
{ id: 10002, name: '李四', role: '研發', sex: 'Women', no1: '220', no2: '220' },
{ id: 10003, name: '王五', role: '產品經理', sex: 'Man', no1: '003200', no2: '003200' },
{ id: 10004, name: '老六', role: 'Designer', sex: 'Women', no1: '02040', no2: '02040' }
],
footerData: [
{ seq: '合計', name: '12人', no1: '356' }
]
}
return {
gridOptions
}
},
methods: {
exportEvent () {
const $grid = this.$refs.gridRef
if ($grid) {
$grid.exportData({
type: 'xlsx'
})
}
}
}
}
</script>
自定義字型
<template>
<div>
<vxe-button @click="exportEvent">點選匯出</vxe-button>
<vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
</div>
</template>
<script>
export default {
data () {
const gridOptions = {
border: true,
showFooter: true,
exportConfig: {
sheetMethod (params) {
const { worksheet } = params
worksheet.eachRow(excelRow => {
excelRow.eachCell(excelCell => {
// 設定單元格字型
excelCell.font = {
bold: true,
size: 16,
color: {
argb: 'ff0000'
}
}
})
})
}
},
columns: [
{ field: 'seq', type: 'seq', width: 70 },
{
title: '分組1',
children: [
{ field: 'name', title: 'Name' },
{ field: 'role', title: 'Role' }
]
},
{ field: 'sex', title: 'Sex' },
{ field: 'no1', title: 'NO1' },
{ field: 'no2', title: 'NO2 String', cellType: 'string' }
],
data: [
{ id: 10001, name: '張三', role: 'Develop', sex: 'Man', no1: '028', no2: '028' },
{ id: 10002, name: '李四', role: '研發', sex: 'Women', no1: '220', no2: '220' },
{ id: 10003, name: '王五', role: '產品經理', sex: 'Man', no1: '003200', no2: '003200' },
{ id: 10004, name: '老六', role: 'Designer', sex: 'Women', no1: '02040', no2: '02040' }
],
footerData: [
{ seq: '合計', name: '12人', no1: '356' }
]
}
return {
gridOptions
}
},
methods: {
exportEvent () {
const $grid = this.$refs.gridRef
if ($grid) {
$grid.exportData({
type: 'xlsx'
})
}
}
}
}
</script>
自定義表頭背景
<template>
<div>
<vxe-button @click="exportEvent">點選匯出</vxe-button>
<vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
</div>
</template>
<script>
export default {
data () {
const gridOptions = {
border: true,
showFooter: true,
exportConfig: {
sheetMethod (params) {
const { worksheet } = params
worksheet.eachRow((excelRow, rowIndex) => {
if (rowIndex <= 2) {
excelRow.eachCell(excelCell => {
// 填充單元格背景
excelCell.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: {
argb: 'c5d9f1'
}
}
})
}
})
}
},
columns: [
{ field: 'seq', type: 'seq', width: 70 },
{
title: '分組1',
children: [
{ field: 'name', title: 'Name' },
{ field: 'role', title: 'Role' }
]
},
{ field: 'sex', title: 'Sex' },
{ field: 'no1', title: 'NO1' },
{ field: 'no2', title: 'NO2 String', cellType: 'string' }
],
data: [
{ id: 10001, name: '張三', role: 'Develop', sex: 'Man', no1: '028', no2: '028' },
{ id: 10002, name: '李四', role: '研發', sex: 'Women', no1: '220', no2: '220' },
{ id: 10003, name: '王五', role: '產品經理', sex: 'Man', no1: '003200', no2: '003200' },
{ id: 10004, name: '老六', role: 'Designer', sex: 'Women', no1: '02040', no2: '02040' }
],
footerData: [
{ seq: '合計', name: '12人', no1: '356' }
]
}
return {
gridOptions
}
},
methods: {
exportEvent () {
const $grid = this.$refs.gridRef
if ($grid) {
$grid.exportData({
type: 'xlsx'
})
}
}
}
}
</script>
自定義列寬
覆蓋預設的列寬
<template>
<div>
<vxe-button @click="exportEvent">點選匯出</vxe-button>
<vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
</div>
</template>
<script>
export default {
data () {
const gridOptions = {
border: true,
showFooter: true,
exportConfig: {
async sheetMethod (params) {
const { worksheet } = params
worksheet.columns.forEach(sheetColumn => {
// 設定列寬
sheetColumn.width = 16
})
}
},
columns: [
{ field: 'seq', type: 'seq', width: 70 },
{
title: '分組1',
children: [
{ field: 'name', title: 'Name' },
{ field: 'role', title: 'Role' }
]
},
{ field: 'sex', title: 'Sex' },
{ field: 'no1', title: 'NO1' },
{ field: 'no2', title: 'NO2 String', cellType: 'string' }
],
data: [
{ id: 10001, name: '張三', role: 'Develop', sex: 'Man', no1: '028', no2: '028' },
{ id: 10002, name: '李四', role: '研發', sex: 'Women', no1: '220', no2: '220' },
{ id: 10003, name: '王五', role: '產品經理', sex: 'Man', no1: '003200', no2: '003200' },
{ id: 10004, name: '老六', role: 'Designer', sex: 'Women', no1: '02040', no2: '02040' }
],
footerData: [
{ seq: '合計', name: '12人', no1: '356' }
]
}
return {
gridOptions
}
},
methods: {
exportEvent () {
const $grid = this.$refs.gridRef
if ($grid) {
$grid.exportData({
type: 'xlsx'
})
}
}
}
}
</script>
自定義行高
覆蓋預設的行高
<template>
<div>
<vxe-button @click="exportEvent">點選匯出</vxe-button>
<vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
</div>
</template>
<script>
export default {
data () {
const gridOptions = {
border: true,
showFooter: true,
exportConfig: {
async sheetMethod (params) {
const { worksheet } = params
worksheet.eachRow((excelRow, rowIndex) => {
// 設定行高
excelRow.height = 60
})
}
},
columns: [
{ field: 'seq', type: 'seq', width: 70 },
{
title: '分組1',
children: [
{ field: 'name', title: 'Name' },
{ field: 'role', title: 'Role' }
]
},
{ field: 'sex', title: 'Sex' },
{ field: 'no1', title: 'NO1' },
{ field: 'no2', title: 'NO2 String', cellType: 'string' }
],
data: [
{ id: 10001, name: '張三', role: 'Develop', sex: 'Man', no1: '028', no2: '028' },
{ id: 10002, name: '李四', role: '研發', sex: 'Women', no1: '220', no2: '220' },
{ id: 10003, name: '王五', role: '產品經理', sex: 'Man', no1: '003200', no2: '003200' },
{ id: 10004, name: '老六', role: 'Designer', sex: 'Women', no1: '02040', no2: '02040' }
],
footerData: [
{ seq: '合計', name: '12人', no1: '356' }
]
}
return {
gridOptions
}
},
methods: {
exportEvent () {
const $grid = this.$refs.gridRef
if ($grid) {
$grid.exportData({
type: 'xlsx'
})
}
}
}
}
</script>
新增超連結
<template>
<div>
<vxe-button @click="exportEvent">點選匯出</vxe-button>
<vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
</div>
</template>
<script>
export default {
data () {
const gridOptions = {
border: true,
showFooter: true,
exportConfig: {
sheetMethod (params) {
const { worksheet } = params
worksheet.eachRow((excelRow, rowIndex) => {
if (rowIndex > 2) {
excelRow.eachCell((excelCell, columnIndex) => {
if (columnIndex === 2) {
// 設定指定單元格為超連結
excelCell.value = {
text: `${excelCell.value}`,
hyperlink: 'https://vxeui.com',
tooltip: 'vxeui.com'
}
// 設定單元格字型
excelCell.font = {
color: {
argb: '0000ff'
}
}
}
})
}
})
}
},
columns: [
{ field: 'seq', type: 'seq', width: 70 },
{
title: '分組1',
children: [
{ field: 'name', title: 'Name' },
{ field: 'role', title: 'Role' }
]
},
{ field: 'sex', title: 'Sex' },
{ field: 'no1', title: 'NO1' },
{ field: 'no2', title: 'NO2 String', cellType: 'string' }
],
data: [
{ id: 10001, name: '張三', role: 'Develop', sex: 'Man', no1: '028', no2: '028' },
{ id: 10002, name: '李四', role: '研發', sex: 'Women', no1: '220', no2: '220' },
{ id: 10003, name: '王五', role: '產品經理', sex: 'Man', no1: '003200', no2: '003200' },
{ id: 10004, name: '老六', role: 'Designer', sex: 'Women', no1: '02040', no2: '02040' }
],
footerData: [
{ seq: '合計', name: '12人', no1: '356' }
]
}
return {
gridOptions
}
},
methods: {
exportEvent () {
const $grid = this.$refs.gridRef
if ($grid) {
$grid.exportData({
type: 'xlsx'
})
}
}
}
}
</script>新增圖片
圖片支援 buffer 和 base64 格式。
<template>
<div>
<vxe-button @click="exportEvent">點選匯出</vxe-button>
<vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
</div>
</template>
<script>
export default {
data () {
const gridOptions = {
border: true,
showFooter: true,
exportConfig: {
async sheetMethod (params) {
const { worksheet, workbook } = params
// 載入圖片
const buffer1 = await fetch('https://vxeui.com/logo.png').then(res => res.arrayBuffer())
const imageId1 = workbook.addImage({
buffer: buffer1,
extension: 'png'
})
worksheet.eachRow((excelRow, rowIndex) => {
if (rowIndex > 2) {
// 設定行高
excelRow.height = 60
excelRow.eachCell((excelCell, columnIndex) => {
if (columnIndex === 2) {
// 將圖片新增到單元格
worksheet.addImage(imageId1, {
tl: { col: columnIndex - 1, row: rowIndex - 1 },
ext: { width: 40, height: 40 }
})
}
})
}
})
}
},
columns: [
{ field: 'seq', type: 'seq', width: 70 },
{
title: '分組1',
children: [
{ field: 'name', title: 'Name' },
{ field: 'role', title: 'Role' }
]
},
{ field: 'sex', title: 'Sex' },
{ field: 'no1', title: 'NO1' },
{ field: 'no2', title: 'NO2 String', cellType: 'string' }
],
data: [
{ id: 10001, name: '張三', role: 'Develop', sex: 'Man', no1: '028', no2: '028' },
{ id: 10002, name: '李四', role: '研發', sex: 'Women', no1: '220', no2: '220' },
{ id: 10003, name: '王五', role: '產品經理', sex: 'Man', no1: '003200', no2: '003200' },
{ id: 10004, name: '老六', role: 'Designer', sex: 'Women', no1: '02040', no2: '02040' }
],
footerData: [
{ seq: '合計', name: '12人', no1: '356' }
]
}
return {
gridOptions
}
},
methods: {
exportEvent () {
const $grid = this.$refs.gridRef
if ($grid) {
$grid.exportData({
type: 'xlsx'
})
}
}
}
}
</script>github https://github.com/x-extends/vxe-table
gitee