前端实现将数据（含文字、图片）导出到Excel中

最新推荐文章于 2024-05-07 14:45:30 发布

Xxxuaneer

最新推荐文章于 2024-05-07 14:45:30 发布

阅读量317

点赞数 10

文章标签：前端 excel vue

本文链接：https://blog.csdn.net/XUANEER/article/details/137824665

版权

安装exceljs

创建工作簿

import ExcelJS from 'exceljs'

// 创建工作簿
  const workbook = new ExcelJS.Workbook()

添加工作表

// 添加工作表
  const worksheet = workbook.addWorksheet('sheet1')

在工作表中设置列

const columns = ref([
  {
    title: 'name',
    dataIndex: 'name',
    width: 200
  },
  {
    title: 'color',
    dataIndex: 'color',
    width: 100
  },
  {
    title: 'img',
    dataIndex: 'img'
  }
])
// 在工作表中，设置列
  worksheet.columns = columns.value.map((item) => {
    const { title, dataIndex, width } = item
    return {
      header: title,
      key: dataIndex,
      width: width ? width/10 : 40
    }
  })

在工作表中添加行

const dataSource = ref([
  {
    name: '玫瑰花',
    color: '红色',
    img: 'https://img2.baidu.com/it/u=4020335929,583313623&fm=253&fmt=auto&app=138&f=JPEG?w=512&h=384'
  },
  {
    name: '菊花',
    color: '黄色',
    img: 'https://img2.baidu.com/it/u=4020335929,583313623&fm=253&fmt=auto&app=138&f=JPEG?w=512&h=384'
  },
  {
    name: '牡丹花',
    color: '紫色',
    img: 'https://img2.baidu.com/it/u=4020335929,583313623&fm=253&fmt=auto&app=138&f=JPEG?w=512&h=384'
  },
  {
    name: '梅花',
    color: '白色',
    img: 'https://img2.baidu.com/it/u=4020335929,583313623&fm=253&fmt=auto&app=138&f=JPEG?w=512&h=384'
  },
  {
    name: '桃花花',
    color: '粉色',
    img: 'https://img2.baidu.com/it/u=4020335929,583313623&fm=253&fmt=auto&app=138&f=JPEG?w=512&h=384'
  }
])

// 在工作表中，添加行数据
  const data = JSON.parse(JSON.stringify(dataSource.value)).map((item) => {
    item.img = ''
    return item
  })

  worksheet.addRows(data)
  let wbImg: any = []
  for (const item of dataSource.value) {
    const { img, name } = item
    const index = dataSource.value.findIndex((item) => item.name === name)
    // 将图片转换为base64, 并在工作簿中添加图片
    console.log(img);
    
    const base64str = await urlToBase64(img)
    wbImg[index] = workbook.addImage({
      base64: base64str,
      extension: 'png'
    })

    // 将图片添加到工作表中
    worksheet.addImage(wbImg[index], {
      tl: { col: 2, row: index + 1},
      ext: { width: 200, height: 200}
    })

    // 设置行高
    const row = worksheet.getRow(index + 2)
    row.height = 150
  }

const urlToBase64 = (url: string) => {
  return new Promise((resolve, reject) => {
    let image = new Image()
    image.onload = () => {
      let canvas = document.createElement('canvas')
      canvas.width = 500
      canvas.height = 500
      // 将图片插入画布并开始绘制
      canvas.getContext('2d').drawImage(image, 0, 0)
      let result = canvas.toDataURL('image/png')
      resolve(result)
    }
    // CORS 策略，会存在跨域问题https://stackoverflow.com/questions/20424279/canvas-todataurl-securityerror
    image.setAttribute('crossOrigin', 'Anonymous')
    image.src = url
    image.onerror = () => {
      reject(new Error('urlToBase64 error'))
    }
  })
}

写入 buffer, 并下载excel文件

// 写入buffer
  const buffer = await workbook.xlsx.writeBuffer()
  download(buffer, '我的工作簿.xlsx')

// 通过blob的方式进行下载
const download = (_blob: any, filename: any) => {
  const blob = new Blob([_blob])
  const downloadElement = document.createElement('a')
  downloadElement.style.display = 'none'
  const href = window.URL.createObjectURL(blob) // 创建下载的链接
  downloadElement.href = href
  downloadElement.download = filename // 下载后文件名
  document.body.appendChild(downloadElement)
  downloadElement.click() // 点击下载
  document.body.removeChild(downloadElement) // 下载完成移除元素
  window.URL.revokeObjectURL(href) // 释放掉blob对象
}

附上所有代码

<template>
  <a-button type="primary" @click="onClick" class="w-200px">导出为EXCEL</a-button>
  <a-table
    :columns="columns"
    :data-source="dataSource"
    :pagination="false"
    bordered
  >
    <template #bodyCell="{ column, text}">
      <template v-if="column.dataIndex === 'img'">
        <a-image :width="50" :height="50" :src="text" />
      </template>
    </template>
  </a-table>
</template>

<script setup lang="ts">
import ExcelJS from 'exceljs'

const columns = ref([
  {
    title: 'name',
    dataIndex: 'name',
    width: 200
  },
  {
    title: 'color',
    dataIndex: 'color',
    width: 100
  },
  {
    title: 'img',
    dataIndex: 'img'
  }
])

const dataSource = ref([
  {
    name: '玫瑰花',
    color: '红色',
    img: 'https://img2.baidu.com/it/u=4020335929,583313623&fm=253&fmt=auto&app=138&f=JPEG?w=512&h=384'
  },
  {
    name: '菊花',
    color: '黄色',
    img: 'https://img2.baidu.com/it/u=4020335929,583313623&fm=253&fmt=auto&app=138&f=JPEG?w=512&h=384'
  },
  {
    name: '牡丹花',
    color: '紫色',
    img: 'https://img2.baidu.com/it/u=4020335929,583313623&fm=253&fmt=auto&app=138&f=JPEG?w=512&h=384'
  },
  {
    name: '梅花',
    color: '白色',
    img: 'https://img2.baidu.com/it/u=4020335929,583313623&fm=253&fmt=auto&app=138&f=JPEG?w=512&h=384'
  },
  {
    name: '桃花花',
    color: '粉色',
    img: 'https://img2.baidu.com/it/u=4020335929,583313623&fm=253&fmt=auto&app=138&f=JPEG?w=512&h=384'
  }
])

const onClick = async() => {

  // 创建工作簿
  const workbook = new ExcelJS.Workbook()

  // 添加工作表
  const worksheet = workbook.addWorksheet('sheet1')

  // 在工作表中，设置列
  worksheet.columns = columns.value.map((item) => {
    const { title, dataIndex, width } = item
    return {
      header: title,
      key: dataIndex,
      width: width ? width/10 : 40
    }
  })
  
  // 在工作表中，添加行数据
  const data = JSON.parse(JSON.stringify(dataSource.value)).map((item) => {
    item.img = ''
    return item
  })

  worksheet.addRows(data)
  let wbImg: any = []
  for (const item of dataSource.value) {
    const { img, name } = item
    const index = dataSource.value.findIndex((item) => item.name === name)
    // 将图片转换为base64, 并在工作簿中添加图片
    console.log(img);
    
    const base64str = await urlToBase64(img)
    wbImg[index] = workbook.addImage({
      base64: base64str,
      extension: 'png'
    })

    // 将图片添加到工作表中
    worksheet.addImage(wbImg[index], {
      tl: { col: 2, row: index + 1},
      ext: { width: 200, height: 200}
    })

    // 设置行高
    const row = worksheet.getRow(index + 2)
    row.height = 150
  }

  // 写入buffer
  const buffer = await workbook.xlsx.writeBuffer()
  download(buffer, '我的工作簿.xlsx')
}

const urlToBase64 = (url: string) => {
  return new Promise((resolve, reject) => {
    let image = new Image()
    image.onload = () => {
      let canvas = document.createElement('canvas')
      canvas.width = 500
      canvas.height = 500
      // 将图片插入画布并开始绘制
      canvas.getContext('2d').drawImage(image, 0, 0)
      let result = canvas.toDataURL('image/png')
      resolve(result)
    }
    // CORS 策略，会存在跨域问题https://stackoverflow.com/questions/20424279/canvas-todataurl-securityerror
    image.setAttribute('crossOrigin', 'Anonymous')
    image.src = url
    image.onerror = () => {
      reject(new Error('urlToBase64 error'))
    }
  })
}

// 通过blob的方式进行下载
const download = (_blob: any, filename: any) => {
  const blob = new Blob([_blob])
  const downloadElement = document.createElement('a')
  downloadElement.style.display = 'none'
  const href = window.URL.createObjectURL(blob) // 创建下载的链接
  downloadElement.href = href
  downloadElement.download = filename // 下载后文件名
  document.body.appendChild(downloadElement)
  downloadElement.click() // 点击下载
  document.body.removeChild(downloadElement) // 下载完成移除元素
  window.URL.revokeObjectURL(href) // 释放掉blob对象
}
</script>

<style lang="scss" scoped>
</style>