vxe-table v4.8+ 分享导出 xlsx、支持导出合并、设置样式、宽高、边框、字体、背景、超链接、图片的使用

自由的云h
375 阅读4分钟

Vxe UI vue vxe-table v4.8+ 导出 xlsx、支持导出合并、设置样式、宽高、边框、字体、背景、超链接、图片等、所有常用的 Excel 格式都能自定义，使用非常简单，纯前端实现复杂的导出。

官网：vxetable.cn/
Gitee：gitee.com/x-extends/v…
Github：github.com/x-extends/v…

安装插件

npm install vxe-pc-ui@4.2.39 vxe-table@4.8.0 @vxe-ui/plugin-export-xlsx@4.0.7 exceljs@4.4.0

// ...
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>

默认导出

默认支持文本和单元格合并等导出，只需要开启对应的功能就可以直接使用了。

{D9D42CDB-59FA-48D4-931A-1835E317AF5F}.png

{1F0271CD-971C-441D-A85A-61A1F550CF60}.png

<template>
  <div>
    <vxe-button @click="exportEvent">高级导出</vxe-button>
    <vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
  </div>
</template>

<script setup>
import { ref, reactive } from 'vue'

const gridRef = ref()

const gridOptions = reactive({
  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 }
  ]
})

const exportEvent = () => {
  const $grid = gridRef.value
  if ($grid) {
    $grid.openExport()
  }
}
</script>

参数说明

通过 sheetMethod 自定义方法实现，参数说明 exportConfig.sheetMethod({ workbook 工作簿对象 worksheet 表对象 options 当前参数 })

自定义边框

{25C6DD9B-31F7-43E1-B40F-9170ADFF6C53}.png

<template>
  <div>
    <vxe-button @click="exportEvent">点击导出</vxe-button>
    <vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
  </div>
</template>

<script setup>
import { ref, reactive } from 'vue'

const gridRef = ref()

const gridOptions = reactive({
  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' }
  ]
})

const exportEvent = () => {
  const $grid = gridRef.value
  if ($grid) {
    $grid.exportData({
      type: 'xlsx'
    })
  }
}
</script>

自定义字体

{35FAE97E-A6D7-4524-8670-34D94DAB4E03}.png

<template>
  <div>
    <vxe-button @click="exportEvent">点击导出</vxe-button>
    <vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
  </div>
</template>

<script setup>
import { ref, reactive } from 'vue'

const gridRef = ref()

const gridOptions = reactive({
  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' }
  ]
})

const exportEvent = () => {
  const $grid = gridRef.value
  if ($grid) {
    $grid.exportData({
      type: 'xlsx'
    })
  }
}
</script>

自定义表头背景

{364E73E2-7F84-47B3-8331-67F6B3B675A2}.png

<template>
  <div>
    <vxe-button @click="exportEvent">点击导出</vxe-button>
    <vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
  </div>
</template>

<script setup>
import { ref, reactive } from 'vue'

const gridRef = ref()

const gridOptions = reactive({
  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' }
  ]
})

const exportEvent = () => {
  const $grid = gridRef.value
  if ($grid) {
    $grid.exportData({
      type: 'xlsx'
    })
  }
}
</script>

自定义列宽

覆盖默认的列宽

{A4B0E985-24A9-477D-8FA2-02A5641B3F17}.png

<template>
  <div>
    <vxe-button @click="exportEvent">点击导出</vxe-button>
    <vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
  </div>
</template>

<script setup>
import { ref, reactive } from 'vue'

const gridRef = ref()

const gridOptions = reactive({
  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' }
  ]
})

const exportEvent = () => {
  const $grid = gridRef.value
  if ($grid) {
    $grid.exportData({
      type: 'xlsx'
    })
  }
}
</script>

自定义行高

覆盖默认的行高

{A7279407-1A79-40E6-8752-60556A4A438F}.png

<template>
  <div>
    <vxe-button @click="exportEvent">点击导出</vxe-button>
    <vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
  </div>
</template>

<script setup>
import { ref, reactive } from 'vue'

const gridRef = ref()

const gridOptions = reactive({
  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' }
  ]
})

const exportEvent = () => {
  const $grid = gridRef.value
  if ($grid) {
    $grid.exportData({
      type: 'xlsx'
    })
  }
}
</script>

添加超链接

{57B96396-E8F4-4424-BE19-ACAE07C58A89}.png

<template>
  <div>
    <vxe-button @click="exportEvent">点击导出</vxe-button>
    <vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
  </div>
</template>

<script setup>
import { ref, reactive } from 'vue'

const gridRef = ref()

const gridOptions = reactive({
  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' }
  ]
})

const exportEvent = () => {
  const $grid = gridRef.value
  if ($grid) {
    $grid.exportData({
      type: 'xlsx'
    })
  }
}
</script>

添加图片

图片支持 buffer 和 base64 格式。

{BB13B7E1-0A55-41E4-BB9D-0C21491631CE}.png

<template>
  <div>
    <vxe-button @click="exportEvent">点击导出</vxe-button>
    <vxe-grid ref="gridRef" v-bind="gridOptions"></vxe-grid>
  </div>
</template>

<script setup>
import { ref, reactive } from 'vue'

const gridRef = ref()

const gridOptions = reactive({
  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' }
  ]
})
const exportEvent = () => {
  const $grid = gridRef.value
  if ($grid) {
    $grid.exportData({
      type: 'xlsx'
    })
  }
}
</script>

github github.com/x-extends/v…
gitee

avatar
文章
阅读
粉丝