excel 导入导出

导入

<script setup lang="ts">
import { ElMessage } from 'element-plus'
import { useExcel } from '@/hooks/useExcel'

// toTwoArray 将数据转为二维数组
const { importExcel, toTwoArray } = useExcel()

// 表格数据
const tableData = ref<Record<string, any>[]>([])
// 表格二维数据
const tableDataTwo = ref<Record<string, any>[]>([])
// 表头数据
const column = ref<Array<string>>(['A', 'B', 'C', 'D', 'E', 'F', 'G'])

const importFile = (file: File) => {
  importExcel(file).then(({ title, list }: any) => {
    column.value = title
    tableData.value = list

    tableDataTwo.value = toTwoArray(list)
  })

  return false
}

const getTwoData = () => {
  console.log(tableDataTwo.value)
  ElMessage.success('数据已打印在控制台')
}
</script>

<template>
  <el-card shadow="never">
    <div class="flex">
      <el-upload
        action=""
        accept=".xls,.xlsx"
        :show-upload-list="false"
        :before-upload="importFile"
      >
        <el-button type="primary">导入</el-button>
      </el-upload>
      <el-button type="primary" @click="getTwoData" class="ml-3"
        >获取表格二维数据</el-button
      >
    </div>
    <table class="table" border="1">
      <colgroup>
        <col width="52" />
        <col v-for="item in column" :key="item" />
      </colgroup>
      <thead>
        <tr>
          <th></th>
          <th v-for="item in column" :key="item" style="text-align: center">
            {{ item }}
          </th>
        </tr>
      </thead>
      <tbody>
        <tr v-for="(item, index) in tableData" :key="index">
          <td style="text-align: center">{{ index + 1 }}</td>
          <template v-for="key in column">
            <td
              v-if="item[key]?.colspan !== 0 && item[key].rowspan !== 0"
              :key="key"
              :colspan="item[key].colspan"
              :rowspan="item[key].rowspan"
              style="text-align: center"
            >
              {{ item[key].value }}
            </td>
          </template>
        </tr>
        <tr v-if="!tableData.length">
          <td
            :colspan="column.length + 1"
            style="text-align: center; background: none"
          >
            暂无数据
          </td>
        </tr>
      </tbody>
    </table>
  </el-card>
</template>

导出

<script setup lang="ts">
import { ElMessage } from 'element-plus'
import { useExcel, type IExcel } from '@/hooks/useExcel'

const { exportExcel } = useExcel()

const tableData = [
  {
    name: '张三',
    province: '山西',
    city: '太原',
    zone: '小店区',
    street: '平阳路街道',
    address: '平阳景苑30栋1单元',
    price: 18
  },
  {
    name: '李四',
    province: '山东',
    city: '青岛',
    zone: '市北区',
    street: '香港中路',
    address: '中心大厦1号楼2单元',
    price: 85
  },
  {
    name: '王五',
    province: '浙江',
    city: '杭州',
    zone: '西湖区',
    street: '文三路',
    address: '西溪风景区3栋9单元',
    price: 8
  },
  {
    name: '赵六',
    province: '福建',
    city: '泉州',
    zone: '丰泽区',
    street: '南洋街道',
    address: '南洋村6幢1单元',
    price: 16
  },
  {
    name: '孙七',
    province: '河北',
    city: '武汉',
    zone: '武昌区',
    street: '武昌大道',
    address: '两湖花园16幢2单元',
    price: 12
  },
  {
    name: '周八',
    province: '河北',
    city: '石家庄',
    zone: '黄山区',
    street: '汤口镇',
    address: '温泉村21号',
    price: 11
  }
]

/* 导出 */
const exportFile = () => {
  const excelParams: IExcel = {
    fileName: '用户数据',
    rows: [['用户名', '省', '市', '区', '街道', '详细地址', '金额']],
    width: [16, 16, 16, 16, 30, 40, 15],
    data: []
  }
  tableData.forEach((item: any) => {
    excelParams.data.push([
      item.name,
      item.province,
      item.city,
      item.zone,
      item.street,
      item.address,
      item.price
    ])
  })
  exportExcel(excelParams)
    .then(() => {
      ElMessage.success('导出成功')
    })
    .catch(e => {
      ElMessage.error(e.message)
    })
}
</script>

<template>
  <el-button type="primary" @click="exportFile">导出</el-button>
</template>

导出合并表格

<script setup lang="ts">
const excelParams: IExcel = {
  fileName: '用户数据',
  /* 将表格配置为二维数组 */
  rows: [
    ['用户名', '地址', '', '', '', '', '金额'],
    ['', '省', '市', '区', '街道', '详细地址', '']
  ],
  width: [16, 16, 16, 16, 30, 40, 15],
  data: [],
  merge: ['A1:A2', 'B1:F1', 'G1:G2'] // 传递合并参数
}
</script>