Excel 操作示例

使用 SheetJS (xlsx) 实现 Excel 文件的导入和导出功能。

功能特性

• 📤 导出 Excel - 将表格数据导出为 Excel 文件
• 📥 导入 Excel - 从 Excel 文件读取数据
• 🎨 样式定制 - 支持单元格样式设置

安装依赖

npm install xlsx file-saver

基础用法

导出 Excel

<template>
  <el-button type="primary" @click="exportExcel">
    <el-icon><Download /></el-icon>导出 Excel
  </el-button>
</template>

<script setup>
import * as XLSX from 'xlsx'
import { saveAs } from 'file-saver'

const tableData = ref([
  { name: '张三', age: 28, department: '技术部' },
  { name: '李四', age: 25, department: '产品部' }
])

const exportExcel = () => {
  // 创建工作簿
  const wb = XLSX.utils.book_new()
  
  // 创建工作表
  const ws = XLSX.utils.json_to_sheet(tableData.value)
  
  // 将工作表添加到工作簿
  XLSX.utils.book_append_sheet(wb, ws, 'Sheet1')
  
  // 生成 Excel 文件
  const wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'array' })
  
  // 保存文件
  const blob = new Blob([wbout], { type: 'application/octet-stream' })
  saveAs(blob, '用户数据.xlsx')
}
</script>

导入 Excel

<template>
  <el-upload
    accept=".xlsx,.xls"
    :auto-upload="false"
    :on-change="handleFileChange"
  >
    <el-button type="primary">
      <el-icon><Upload /></el-icon>导入 Excel
    </el-button>
  </el-upload>
</template>

<script setup>
import * as XLSX from 'xlsx'

const handleFileChange = (file) => {
  const reader = new FileReader()
  
  reader.onload = (e) => {
    const data = new Uint8Array(e.target.result)
    const workbook = XLSX.read(data, { type: 'array' })
    
    // 获取第一个工作表
    const firstSheetName = workbook.SheetNames[0]
    const worksheet = workbook.Sheets[firstSheetName]
    
    // 转换为 JSON
    const jsonData = XLSX.utils.sheet_to_json(worksheet)
    
    console.log('导入的数据:', jsonData)
  }
  
  reader.readAsArrayBuffer(file.raw)
}
</script>

完整示例

<template>
  <div class="page-container">
    <div class="toolbar">
      <el-button type="primary" @click="exportExcel">
        <el-icon><Download /></el-icon>导出 Excel
      </el-button>
      
      <el-upload
        accept=".xlsx,.xls"
        :auto-upload="false"
        :on-change="handleFileChange"
        :show-file-list="false"
      >
        <el-button type="success">
          <el-icon><Upload /></el-icon>导入 Excel
        </el-button>
      </el-upload>
    </div>
    
    <el-table :data="tableData" border>
      <el-table-column prop="name" label="姓名" />
      <el-table-column prop="age" label="年龄" />
      <el-table-column prop="department" label="部门" />
    </el-table>
  </div>
</template>

<script setup>
import { ref } from 'vue'
import * as XLSX from 'xlsx'
import { saveAs } from 'file-saver'
import { Download, Upload } from '@element-plus/icons-vue'

const tableData = ref([
  { name: '张三', age: 28, department: '技术部' },
  { name: '李四', age: 25, department: '产品部' },
  { name: '王五', age: 32, department: '运营部' }
])

// 导出 Excel
const exportExcel = () => {
  const wb = XLSX.utils.book_new()
  const ws = XLSX.utils.json_to_sheet(tableData.value)
  
  // 设置列宽
  ws['!cols'] = [
    { wch: 10 },
    { wch: 10 },
    { wch: 15 }
  ]
  
  XLSX.utils.book_append_sheet(wb, ws, '用户数据')
  
  const wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'array' })
  const blob = new Blob([wbout], { type: 'application/octet-stream' })
  saveAs(blob, '用户数据.xlsx')
}

// 导入 Excel
const handleFileChange = (file) => {
  const reader = new FileReader()
  
  reader.onload = (e) => {
    const data = new Uint8Array(e.target.result)
    const workbook = XLSX.read(data, { type: 'array' })
    
    const firstSheetName = workbook.SheetNames[0]
    const worksheet = workbook.Sheets[firstSheetName]
    const jsonData = XLSX.utils.sheet_to_json(worksheet)
    
    tableData.value = jsonData
    ElMessage.success('导入成功')
  }
  
  reader.readAsArrayBuffer(file.raw)
}
</script>

更多功能

设置表头

const ws = XLSX.utils.json_to_sheet(data, {
  header: ['name', 'age', 'department']
})

设置单元格样式

// 设置单元格样式（需要额外的库支持）
ws['A1'].s = {
  font: { bold: true, color: { rgb: 'FF0000' } },
  fill: { fgColor: { rgb: 'FFFF00' } }
}

多 Sheet 导出

const wb = XLSX.utils.book_new()

const ws1 = XLSX.utils.json_to_sheet(data1)
const ws2 = XLSX.utils.json_to_sheet(data2)

XLSX.utils.book_append_sheet(wb, ws1, 'Sheet1')
XLSX.utils.book_append_sheet(wb, ws2, 'Sheet2')