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

头像
abc26296
     广东
    阅读 15 分钟

    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>

    默认导出

    默认支持文本和单元格合并等导出，只需要开启对应的功能就可以直接使用了。
    {1568D446-0444-4937-B5B9-92151ABD2F09}.png

    {FBDC2E76-F4FE-404B-A12B-D82F9DF97D58}.png

    <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 当前参数
    })

    自定义边框

    {CAC16927-434D-465A-A7AD-68905F81726A}.png

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

    自定义字体

    {548C2F35-5764-4AC2-A3BE-E478120FAE8B}.png

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

    自定义表头背景

    {7E1EBEC2-DE7A-4B95-B409-08F8430DAC4F}.png

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

    自定义列宽

    覆盖默认的列宽

    {7F9618A2-4906-468E-A64C-E91012EC53A9}.png

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

    自定义行高

    覆盖默认的行高

    {666FD0C7-AA66-4454-8280-C1701248A1DA}.png

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

    添加超链接

    {D52B0C08-38CB-444F-B9FE-DA6DA66ACB72}.png

    <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 格式。

    {CDDFF2DF-0892-4677-9A62-42EF835A2AFF}.png

    <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

    vue.js前端javascript
    abc26296
    172 声望23 粉丝
    « 上一篇
    Vxe UI vue vxe-upload image-preview 图片预览组件的使用

    引用和评论

    推荐阅读
    头像
    如何实现一个楼中楼的评论系统

    小蚊酱赞 22阅读 9.9k

    头像
    腾讯新闻中心首页改版啦

    小蚊酱赞 11阅读 8.7k评论 7

    头像
    网页设计的垂直居中

    寒青赞 39阅读 4.2k评论 3

    头像
    CSS团队协作规范

    寒青赞 35阅读 3.1k

    头像
    前端如何入门 Go 语言

    robin赞 25阅读 7.2k评论 8

    头像
    前站 - 前端导航，搜索社区，阅读文章，提升技术

    bestvist赞 25阅读 13.6k评论 3

    头像
    不使用 JS 纯 CSS 获取屏幕宽高

    南城FE赞 15阅读 1.6k评论 3

    0 条评论
    得票最新