Excel 导入导出

负责 Excel 导入导出功能开发,支持数据批量处理
- 基于 SheetJS 实现 Excel 文件导入导出,支持 .xlsx/.xls 格式
- 开发数据校验功能,支持必填项、格式、唯一性等 10+ 种校验规则
- 实现模板导出,用户按模板填写数据,导入成功率提升 80%

设计并实现企业级 Excel 处理方案,支撑核心业务数据流转
- 采用流式处理技术,10 万行 Excel 导出时间从 30s 降至 3s,内存占用减少 90%
- 实现复杂表头处理,支持多级表头、合并单元格、动态列,满足各类报表需求
- 开发智能错误提示系统,精确定位错误行列,修复时间缩短 70%
- 优化大文件导入,通过分片解析、Web Worker,100MB 文件处理时间 < 10s

主导 Excel 数据交换架构设计,建立统一的数据处理规范
- 抽象 Excel 处理引擎,支持模板化配置,新增导入导出场景开发时间从 2 天降至 2 小时
- 设计数据转换管道,支持字段映射、类型转换、数据清洗,准确率 99%+
- 建立 Excel 文件格式规范和校验体系,数据质量问题减少 85%
- 实现 Excel 与数据库的双向同步,支撑 20+ 业务系统的数据交换

import * as XLSX from 'xlsx'

async function importExcel(file) {
  // 1. 读取文件
  const arrayBuffer = await file.arrayBuffer()

  // 2. 解析工作簿
  const workbook = XLSX.read(arrayBuffer, {
    type: 'array',
    cellDates: true,  // 自动解析日期
    cellFormula: false, // 不需要公式
  })

  // 3. 获取第一个工作表
  const sheetName = workbook.SheetNames[0]
  const worksheet = workbook.Sheets[sheetName]

  // 4. 转换为 JSON
  const jsonData = XLSX.utils.sheet_to_json(worksheet, {
    header: 1, // 返回数组,第一行是表头
    defval: null, // 空单元格返回 null
    blankrows: false, // 跳过空行
  })

  // 5. 提取表头和数据
  const headers = jsonData[0]
  const rows = jsonData.slice(1)

  // 6. 转换为对象数组
  const data = rows.map(row => {
    const obj = {}
    headers.forEach((header, index) => {
      obj[header] = row[index]
    })
    return obj
  })

  return data
}

function exportExcel(data, filename = 'export.xlsx') {
  // 1. 创建工作表
  const worksheet = XLSX.utils.json_to_sheet(data)

  // 2. 创建工作簿
  const workbook = XLSX.utils.book_new()
  XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1')

  // 3. 生成文件
  XLSX.writeFile(workbook, filename)
}

const reader = new FileReader()
reader.onload = (e) => {
  const data = new Uint8Array(e.target.result)
  const workbook = XLSX.read(data, { type: 'array' })
}
reader.readAsArrayBuffer(file)

const arrayBuffer = await file.arrayBuffer()
const workbook = XLSX.read(arrayBuffer)

async function streamExport(dataSource, filename) {
  // 创建可写流
  const stream = XLSX.stream.to_csv(worksheet)

  // 分批写入数据
  const BATCH_SIZE = 1000
  let offset = 0

  while (true) {
    const batch = await dataSource.fetch(offset, BATCH_SIZE)
    if (batch.length === 0) break

    // 写入这批数据
    batch.forEach(row => {
      XLSX.stream.to_csv.write(stream, row)
    })

    offset += BATCH_SIZE

    // 释放内存
    if (global.gc) global.gc()
  }

  stream.end()
}

// main.js
const worker = new Worker('excel-worker.js')

worker.postMessage({
  type: 'export',
  data: data
})

worker.onmessage = (e) => {
  if (e.data.type === 'progress') {
    updateProgress(e.data.progress)
  } else if (e.data.type === 'complete') {
    const blob = e.data.blob
    downloadBlob(blob, 'export.xlsx')
  }
}

// excel-worker.js
import * as XLSX from 'xlsx'

self.onmessage = (e) => {
  const { type, data } = e.data

  if (type === 'export') {
    const workbook = XLSX.utils.book_new()

    // 分批处理
    const BATCH_SIZE = 1000
    for (let i = 0; i < data.length; i += BATCH_SIZE) {
      const batch = data.slice(i, i + BATCH_SIZE)

      if (i === 0) {
        // 第一批,创建工作表
        const worksheet = XLSX.utils.json_to_sheet(batch)
        XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1')
      } else {
        // 后续批次,追加数据
        const worksheet = workbook.Sheets['Sheet1']
        XLSX.utils.sheet_add_json(worksheet, batch, {
          skipHeader: true,
          origin: -1 // 追加到末尾
        })
      }

      // 报告进度
      self.postMessage({
        type: 'progress',
        progress: Math.round((i + batch.length) / data.length * 100)
      })
    }

    // 生成文件
    const wbout = XLSX.write(workbook, {
      bookType: 'xlsx',
      type: 'array'
    })

    self.postMessage({
      type: 'complete',
      blob: new Blob([wbout], { type: 'application/octet-stream' })
    })
  }
}

const workbook = XLSX.write(workbook, {
  bookType: 'xlsx',
  type: 'array',
  compression: true, // 启用压缩
  cellStyles: false,  // 不导出样式(如果不需要)
})

姓名  |    成绩    |  备注
      | 语文 | 数学 |

function exportWithComplexHeader(data) {
  // 1. 手动构建表头
  const headers = [
    ['姓名', '成绩', '成绩', '备注'],  // 第一行
    ['', '语文', '数学', '']          // 第二行
  ]

  // 2. 添加数据
  const rows = data.map(item => [
    item.name,
    item.chinese,
    item.math,
    item.remark
  ])

  // 3. 合并 headers 和 rows
  const sheetData = [...headers, ...rows]

  // 4. 创建工作表
  const worksheet = XLSX.utils.aoa_to_sheet(sheetData)

  // 5. 定义合并单元格
  worksheet['!merges'] = [
    { s: { r: 0, c: 0 }, e: { r: 1, c: 0 } }, // 姓名,合并 A1:A2
    { s: { r: 0, c: 1 }, e: { r: 0, c: 2 } }, // 成绩,合并 B1:C1
    { s: { r: 0, c: 3 }, e: { r: 1, c: 3 } }, // 备注,合并 D1:D2
  ]

  // 6. 设置列宽
  worksheet['!cols'] = [
    { wch: 10 }, // A 列宽度
    { wch: 8 },  // B 列
    { wch: 8 },  // C 列
    { wch: 20 }, // D 列
  ]

  // 7. 生成工作簿
  const workbook = XLSX.utils.book_new()
  XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1')

  XLSX.writeFile(workbook, 'complex-header.xlsx')
}

function generateDynamicHeaders(dateRange) {
  const header1 = ['姓名']
  const header2 = ['']

  dateRange.forEach(date => {
    header1.push(date, date)  // 日期占两列
    header2.push('上班', '下班')
  })

  return [header1, header2]
}

function exportAttendance(data, dateRange) {
  const headers = generateDynamicHeaders(dateRange)

  const rows = data.map(person => {
    const row = [person.name]

    dateRange.forEach(date => {
      const record = person.records[date] || {}
      row.push(record.checkIn || '', record.checkOut || '')
    })

    return row
  })

  const sheetData = [...headers, ...rows]
  const worksheet = XLSX.utils.aoa_to_sheet(sheetData)

  // 计算合并单元格
  const merges = [
    { s: { r: 0, c: 0 }, e: { r: 1, c: 0 } } // 姓名列
  ]

  dateRange.forEach((date, index) => {
    const col = index * 2 + 1
    merges.push({
      s: { r: 0, c: col },
      e: { r: 0, c: col + 1 }
    })
  })

  worksheet['!merges'] = merges

  const workbook = XLSX.utils.book_new()
  XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1')

  XLSX.writeFile(workbook, 'attendance.xlsx')
}

// 设置表头样式
function styleHeader(worksheet, headerRows = 2) {
  const range = XLSX.utils.decode_range(worksheet['!ref'])

  for (let R = 0; R < headerRows; R++) {
    for (let C = range.s.c; C <= range.e.c; C++) {
      const cellAddress = { c: C, r: R }
      const cellRef = XLSX.utils.encode_cell(cellAddress)

      if (worksheet[cellRef]) {
        worksheet[cellRef].s = {
          font: { bold: true },
          fill: { fgColor: { rgb: 'CCCCCC' } },
          alignment: { horizontal: 'center', vertical: 'center' }
        }
      }
    }
  }
}

const validationRules = {
  name: [
    { type: 'required', message: '姓名不能为空' },
    { type: 'maxLength', value: 20, message: '姓名不能超过20个字符' }
  ],
  age: [
    { type: 'required', message: '年龄不能为空' },
    { type: 'number', message: '年龄必须是数字' },
    { type: 'range', min: 0, max: 150, message: '年龄必须在0-150之间' }
  ],
  email: [
    { type: 'required', message: '邮箱不能为空' },
    { type: 'email', message: '邮箱格式不正确' }
  ],
  phone: [
    { type: 'required', message: '手机号不能为空' },
    { type: 'pattern', value: /^1[3-9]\d{9}$/, message: '手机号格式不正确' }
  ],
  idCard: [
    { type: 'required', message: '身份证号不能为空' },
    {
      type: 'custom',
      validator: (value) => {
        // 自定义校验逻辑
        return validateIdCard(value)
      },
      message: '身份证号不正确'
    }
  ]
}

class Validator {
  constructor(rules) {
    this.rules = rules
  }

  // 校验单个字段
  validateField(field, value) {
    const fieldRules = this.rules[field]
    if (!fieldRules) return { valid: true }

    for (const rule of fieldRules) {
      const result = this.applyRule(rule, value)
      if (!result.valid) {
        return {
          valid: false,
          field,
          message: rule.message
        }
      }
    }

    return { valid: true }
  }

  // 应用规则
  applyRule(rule, value) {
    switch (rule.type) {
      case 'required':
        return {
          valid: value !== null && value !== undefined && value !== ''
        }

      case 'number':
        return {
          valid: !isNaN(Number(value))
        }

      case 'range':
        const num = Number(value)
        return {
          valid: num >= rule.min && num <= rule.max
        }

      case 'maxLength':
        return {
          valid: String(value).length <= rule.value
        }

      case 'email':
        return {
          valid: /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(value)
        }

      case 'pattern':
        return {
          valid: rule.value.test(value)
        }

      case 'custom':
        return {
          valid: rule.validator(value)
        }

      default:
        return { valid: true }
    }
  }

  // 校验整行数据
  validateRow(row, rowIndex) {
    const errors = []

    Object.keys(this.rules).forEach(field => {
      const result = this.validateField(field, row[field])
      if (!result.valid) {
        errors.push({
          row: rowIndex,
          field,
          value: row[field],
          message: result.message
        })
      }
    })

    return errors
  }

  // 校验所有数据
  validateAll(data) {
    const allErrors = []

    data.forEach((row, index) => {
      const errors = this.validateRow(row, index + 2) // +2 因为 Excel 行号从1开始,且第1行是表头
      allErrors.push(...errors)
    })

    return allErrors
  }
}

async function validateUniqueness(data, field, apiCheck) {
  const errors = []
  const values = data.map(row => row[field])

  // 检查导入数据内部重复
  const duplicates = values.filter((v, i) => values.indexOf(v) !== i)
  if (duplicates.length > 0) {
    duplicates.forEach(value => {
      const rows = data
        .map((row, index) => row[field] === value ? index + 2 : null)
        .filter(r => r !== null)

      errors.push({
        rows,
        field,
        value,
        message: `${field} 在第 ${rows.join(',')} 行重复`
      })
    })
  }

  // 检查与数据库重复
  const response = await apiCheck(values)
  const existingValues = response.data

  existingValues.forEach(value => {
    const row = data.findIndex(r => r[field] === value) + 2
    errors.push({
      row,
      field,
      value,
      message: `${field} "${value}" 已存在`
    })
  })

  return errors
}

function generateErrorReport(data, errors) {
  // 创建工作表
  const worksheet = XLSX.utils.json_to_sheet(data)

  // 标注错误单元格(背景色红色)
  errors.forEach(error => {
    const cellAddress = XLSX.utils.encode_cell({
      r: error.row - 1,
      c: getColumnIndex(error.field)
    })

    if (worksheet[cellAddress]) {
      worksheet[cellAddress].s = {
        fill: { fgColor: { rgb: 'FFCCCC' } }
      }

      // 添加批注
      worksheet[cellAddress].c = [{
        a: 'System',
        t: error.message
      }]
    }
  })

  // 添加错误汇总工作表
  const errorSheet = XLSX.utils.json_to_sheet(errors.map(e => ({
    行号: e.row,
    字段: e.field,
    错误值: e.value,
    错误信息: e.message
  })))

  const workbook = XLSX.utils.book_new()
  XLSX.utils.book_append_sheet(workbook, worksheet, '数据')
  XLSX.utils.book_append_sheet(workbook, errorSheet, '错误汇总')

  return workbook
}

async function importWithValidation(file) {
  // 1. 解析 Excel
  const data = await parseExcel(file)

  // 2. 数据校验
  const validator = new Validator(validationRules)
  const syncErrors = validator.validateAll(data)

  // 3. 异步校验(唯一性)
  const asyncErrors = await validateUniqueness(data, 'email', checkEmailExists)

  // 4. 合并错误
  const allErrors = [...syncErrors, ...asyncErrors]

  if (allErrors.length > 0) {
    // 生成错误报告
    const errorReport = generateErrorReport(data, allErrors)
    XLSX.writeFile(errorReport, 'error-report.xlsx')

    return {
      success: false,
      errors: allErrors,
      errorCount: allErrors.length
    }
  }

  // 5. 导入数据
  await importData(data)

  return {
    success: true,
    count: data.length
  }
}

function generateTemplate(config) {
  const {
    fields,        // 字段定义
    sampleData,    // 示例数据
    instructions   // 使用说明
  } = config

  // 1. 生成表头
  const headers = fields.map(f => f.label)

  // 2. 生成字段说明(第二行)
  const descriptions = fields.map(f => f.description || '')

  // 3. 生成示例数据
  const samples = sampleData || [
    fields.map(f => f.example || '')
  ]

  // 4. 组合数据
  const sheetData = [
    headers,
    descriptions,
    ...samples
  ]

  const worksheet = XLSX.utils.aoa_to_sheet(sheetData)

  // 5. 设置列宽
  worksheet['!cols'] = fields.map(f => ({ wch: f.width || 15 }))

  // 6. 样式设置
  // 表头加粗、背景色
  // 字段说明灰色、斜体
  // 示例数据正常

  // 7. 数据验证(下拉框)
  fields.forEach((field, index) => {
    if (field.options) {
      // 添加下拉列表
      addDropdown(worksheet, index, field.options)
    }
  })

  // 8. 添加说明工作表
  const instructionSheet = XLSX.utils.aoa_to_sheet([
    ['字段说明'],
    [''],
    ...fields.map(f => [f.label, f.description, f.validation])
  ])

  // 9. 生成工作簿
  const workbook = XLSX.utils.book_new()
  XLSX.utils.book_append_sheet(workbook, worksheet, '数据')
  XLSX.utils.book_append_sheet(workbook, instructionSheet, '说明')

  return workbook
}

// 使用示例
const template = generateTemplate({
  fields: [
    {
      key: 'name',
      label: '姓名',
      description: '必填,不超过20字',
      example: '张三',
      width: 15,
      validation: { required: true, maxLength: 20 }
    },
    {
      key: 'gender',
      label: '性别',
      description: '必填,男/女',
      example: '男',
      width: 10,
      options: ['男', '女'],
      validation: { required: true, enum: ['男', '女'] }
    },
    {
      key: 'department',
      label: '部门',
      description: '必填,从列表选择',
      example: '技术部',
      width: 15,
      options: ['技术部', '销售部', '人事部'],
      validation: { required: true }
    }
  ],
  sampleData: [
    ['张三', '男', '技术部'],
    ['李四', '女', '销售部']
  ]
})

XLSX.writeFile(template, 'import-template.xlsx')

function addDropdown(worksheet, columnIndex, options) {
  // 这需要直接操作 Excel 的 XML
  // 或者使用 exceljs 库,它支持数据验证

  // 使用 exceljs 示例:
  const ExcelJS = require('exceljs')

  worksheet.getColumn(columnIndex + 1).eachCell({ includeEmpty: true }, (cell, rowNumber) => {
    if (rowNumber > 2) { // 跳过表头和说明
      cell.dataValidation = {
        type: 'list',
        allowBlank: false,
        formulae: [`"${options.join(',')}"`]
      }
    }
  })
}

function fillTemplate(template, data, mapping) {
  // template: 模板工作簿
  // data: 要填充的数据
  // mapping: 字段映射 { excel列: 数据字段 }

  const worksheet = template.Sheets[template.SheetNames[0]]
  const range = XLSX.utils.decode_range(worksheet['!ref'])

  // 找到数据起始行(跳过表头和说明)
  const dataStartRow = 3

  // 填充数据
  data.forEach((item, index) => {
    const row = dataStartRow + index

    Object.entries(mapping).forEach(([excelCol, dataField]) => {
      const col = XLSX.utils.decode_col(excelCol)
      const cellAddress = XLSX.utils.encode_cell({ r: row, c: col })

      worksheet[cellAddress] = {
        t: 's', // 字符串类型
        v: item[dataField]
      }
    })
  })

  // 更新范围
  worksheet['!ref'] = XLSX.utils.encode_range({
    s: { r: 0, c: range.s.c },
    e: { r: dataStartRow + data.length - 1, c: range.e.c }
  })

  return template
}

async function streamParseExcel(file) {
  const CHUNK_SIZE = 10 * 1024 * 1024 // 10MB 一块
  const chunks = []

  // 分块读取文件
  for (let offset = 0; offset < file.size; offset += CHUNK_SIZE) {
    const chunk = file.slice(offset, offset + CHUNK_SIZE)
    const arrayBuffer = await chunk.arrayBuffer()
    chunks.push(new Uint8Array(arrayBuffer))
  }

  // 合并 chunks
  const fullData = new Uint8Array(chunks.reduce((acc, chunk) => acc + chunk.length, 0))
  let position = 0
  chunks.forEach(chunk => {
    fullData.set(chunk, position)
    position += chunk.length
  })

  // 解析
  const workbook = XLSX.read(fullData, {
    type: 'array',
    sheetRows: 10000, // 只读取前 10000 行(可选)
  })

  return workbook
}

async function processBatches(data, batchSize = 1000) {
  const results = []
  const errors = []

  for (let i = 0; i < data.length; i += batchSize) {
    const batch = data.slice(i, i + batchSize)

    // 处理这一批
    const batchResult = await processBatch(batch, i)
    results.push(...batchResult.success)
    errors.push(...batchResult.errors)

    // 更新进度
    const progress = Math.round((i + batch.length) / data.length * 100)
    updateProgress(progress)

    // 让出主线程,避免阻塞
    await new Promise(resolve => setTimeout(resolve, 0))
  }

  return { results, errors }
}

async function processBatch(batch, startIndex) {
  const success = []
  const errors = []

  batch.forEach((row, index) => {
    const rowIndex = startIndex + index + 2 // Excel 行号

    try {
      // 数据转换
      const processed = transformRow(row)

      // 数据校验
      const validation = validateRow(processed)
      if (!validation.valid) {
        errors.push({
          row: rowIndex,
          errors: validation.errors
        })
      } else {
        success.push(processed)
      }
    } catch (error) {
      errors.push({
        row: rowIndex,
        error: error.message
      })
    }
  })

  return { success, errors }
}

// main.js
async function validateInWorker(data) {
  const workerCount = navigator.hardwareConcurrency || 4
  const chunkSize = Math.ceil(data.length / workerCount)

  const promises = []

  for (let i = 0; i < workerCount; i++) {
    const start = i * chunkSize
    const end = Math.min(start + chunkSize, data.length)
    const chunk = data.slice(start, end)

    const worker = new Worker('validation-worker.js')

    const promise = new Promise((resolve, reject) => {
      worker.onmessage = (e) => {
        resolve(e.data.errors)
        worker.terminate()
      }
      worker.onerror = reject
    })

    worker.postMessage({
      data: chunk,
      startIndex: start,
      rules: validationRules
    })

    promises.push(promise)
  }

  const results = await Promise.all(promises)
  return results.flat()
}

// validation-worker.js
self.onmessage = (e) => {
  const { data, startIndex, rules } = e.data
  const errors = []

  data.forEach((row, index) => {
    const rowIndex = startIndex + index + 2
    const validation = validateRow(row, rules)

    if (!validation.valid) {
      errors.push({
        row: rowIndex,
        errors: validation.errors
      })
    }
  })

  self.postMessage({ errors })
}

function validateRow(row, rules) {
  // 校验逻辑...
}

async function importLargeFile(file) {
  try {
    // 1. 显示进度条
    showProgress('解析文件...')

    // 2. 流式解析
    const workbook = await streamParseExcel(file)
    const data = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]])

    showProgress(`解析完成,共 ${data.length} 行`)

    // 3. 并行校验
    showProgress('校验数据...')
    const errors = await validateInWorker(data)

    if (errors.length > 0) {
      showProgress(`发现 ${errors.length} 个错误`)
      showErrors(errors)
      return
    }

    // 4. 分批导入
    showProgress('导入数据...')
    await processBatches(data, 1000)

    showProgress('导入完成!')

  } catch (error) {
    showError(error.message)
  }
}

function excelDateToJS(serial) {
  // Excel 日期基准: 1900-01-01
  const excelEpoch = new Date(1900, 0, 1)
  const days = Math.floor(serial)
  const milliseconds = Math.round((serial - days) * 86400000)

  const date = new Date(excelEpoch.getTime() + (days - 1) * 86400000 + milliseconds)

  // Excel 有个 bug: 1900 年误认为是闰年
  if (serial >= 60) {
    date.setDate(date.getDate() - 1)
  }

  return date
}

// 或者用 SheetJS 内置的转换
const workbook = XLSX.read(data, {
  cellDates: true, // 自动转换日期
  dateNF: 'yyyy-mm-dd' // 日期格式
})

function parseNumber(value) {
  if (typeof value === 'number') {
    return value
  }

  if (typeof value === 'string') {
    // 移除千分位逗号
    const cleaned = value.replace(/,/g, '')
    const number = parseFloat(cleaned)

    if (isNaN(number)) {
      throw new Error(`Invalid number: ${value}`)
    }

    return number
  }

  return null
}

function parsePercentage(value) {
  if (typeof value === 'number') {
    // Excel 中 50% 存储为 0.5
    return value * 100
  }

  if (typeof value === 'string') {
    // 用户可能输入 "50%" 或 "0.5"
    if (value.endsWith('%')) {
      return parseFloat(value.slice(0, -1))
    } else {
      return parseFloat(value) * 100
    }
  }

  return null
}

function parseBoolean(value) {
  if (typeof value === 'boolean') {
    return value
  }

  if (typeof value === 'string') {
    const lower = value.toLowerCase().trim()

    // 中文
    if (lower === '是' || lower === '对' || lower === '真') {
      return true
    }
    if (lower === '否' || lower === '错' || lower === '假') {
      return false
    }

    // 英文
    if (lower === 'true' || lower === 'yes' || lower === 'y') {
      return true
    }
    if (lower === 'false' || lower === 'no' || lower === 'n') {
      return false
    }

    // 数字
    if (lower === '1') return true
    if (lower === '0') return false
  }

  if (typeof value === 'number') {
    return value !== 0
  }

  return null
}

class TypeConverter {
  constructor(config) {
    this.config = config
  }

  convert(value, type) {
    if (value === null || value === undefined || value === '') {
      return null
    }

    switch (type) {
      case 'string':
        return String(value).trim()

      case 'number':
        return this.parseNumber(value)

      case 'integer':
        return Math.floor(this.parseNumber(value))

      case 'float':
        const num = this.parseNumber(value)
        return Math.round(num * 100) / 100 // 保留两位小数

      case 'percentage':
        return this.parsePercentage(value)

      case 'boolean':
        return this.parseBoolean(value)

      case 'date':
        return this.parseDate(value)

      case 'datetime':
        return this.parseDateTime(value)

      case 'enum':
        return this.parseEnum(value)

      default:
        return value
    }
  }

  parseNumber(value) {
    // ... (前面的实现)
  }

  parseDate(value) {
    // 尝试多种解析方式

    // 1. Excel 日期序列号
    if (typeof value === 'number') {
      return excelDateToJS(value)
    }

    // 2. ISO 字符串
    if (typeof value === 'string') {
      // "2024-01-15"
      const iso = new Date(value)
      if (!isNaN(iso.getTime())) {
        return iso
      }

      // "2024/01/15"
      const slash = new Date(value.replace(/\//g, '-'))
      if (!isNaN(slash.getTime())) {
        return slash
      }

      // "15/01/2024" (DD/MM/YYYY)
      const parts = value.split('/')
      if (parts.length === 3) {
        const date = new Date(parts[2], parts[1] - 1, parts[0])
        if (!isNaN(date.getTime())) {
          return date
        }
      }
    }

    throw new Error(`Invalid date: ${value}`)
  }

  parseEnum(value, options) {
    const str = String(value).trim()

    if (options.includes(str)) {
      return str
    }

    // 尝试不区分大小写匹配
    const lowerValue = str.toLowerCase()
    const matched = options.find(opt => opt.toLowerCase() === lowerValue)

    if (matched) {
      return matched
    }

    throw new Error(`Invalid enum value: ${value}, expected one of: ${options.join(', ')}`)
  }
}

const converter = new TypeConverter()

const fieldTypes = {
  name: 'string',
  age: 'integer',
  salary: 'float',
  rate: 'percentage',
  isActive: 'boolean',
  birthday: 'date',
  gender: 'enum'
}

const fieldEnums = {
  gender: ['男', '女', '其他']
}

function transformRow(row) {
  const transformed = {}

  Object.entries(fieldTypes).forEach(([field, type]) => {
    try {
      const value = row[field]
      const options = fieldEnums[field]

      transformed[field] = converter.convert(value, type, options)
    } catch (error) {
      throw new Error(`Field "${field}": ${error.message}`)
    }
  })

  return transformed
}

import ExcelJS from 'exceljs'

async function exportWithStyle(data) {
  const workbook = new ExcelJS.Workbook()
  const worksheet = workbook.addWorksheet('Sheet1')

  // 1. 设置列
  worksheet.columns = [
    { header: '姓名', key: 'name', width: 15 },
    { header: '年龄', key: 'age', width: 10 },
    { header: '工资', key: 'salary', width: 15 }
  ]

  // 2. 样式化表头
  worksheet.getRow(1).eachCell(cell => {
    cell.font = { bold: true, color: { argb: 'FFFFFFFF' } }
    cell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FF4472C4' }
    }
    cell.alignment = { horizontal: 'center', vertical: 'middle' }
    cell.border = {
      top: { style: 'thin' },
      left: { style: 'thin' },
      bottom: { style: 'thin' },
      right: { style: 'thin' }
    }
  })

  // 3. 添加数据
  data.forEach(item => {
    const row = worksheet.addRow(item)

    // 根据条件设置行样式
    if (item.salary > 10000) {
      row.getCell('salary').font = { color: { argb: 'FFFF0000' } }
      row.getCell('salary').fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFFFEB9C' }
      }
    }
  })

  // 4. 设置行高
  worksheet.getRow(1).height = 25

  // 5. 冻结首行
  worksheet.views = [
    { state: 'frozen', xSplit: 0, ySplit: 1 }
  ]

  // 6. 导出
  const buffer = await workbook.xlsx.writeBuffer()
  const blob = new Blob([buffer], {
    type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
  })

  const url = URL.createObjectURL(blob)
  const a = document.createElement('a')
  a.href = url
  a.download = 'export.xlsx'
  a.click()
  URL.revokeObjectURL(url)
}

async function exportFromTemplate(templateFile, data) {
  // 1. 读取模板
  const templateBuffer = await templateFile.arrayBuffer()
  const workbook = new ExcelJS.Workbook()
  await workbook.xlsx.load(templateBuffer)

  // 2. 获取工作表
  const worksheet = workbook.getWorksheet(1)

  // 3. 找到数据起始行(假设第2行开始是数据)
  const startRow = 2

  // 4. 填充数据
  data.forEach((item, index) => {
    const row = worksheet.getRow(startRow + index)

    // 复制模板行的样式
    if (index > 0) {
      const templateRow = worksheet.getRow(startRow)
      row.height = templateRow.height

      templateRow.eachCell((cell, colNumber) => {
        const targetCell = row.getCell(colNumber)
        targetCell.style = { ...cell.style }
      })
    }

    // 填充数据
    row.getCell(1).value = item.name
    row.getCell(2).value = item.age
    row.getCell(3).value = item.salary

    row.commit()
  })

  // 5. 导出
  const buffer = await workbook.xlsx.writeBuffer()
  downloadBuffer(buffer, 'export.xlsx')
}

function exportHTMLTable(tableElement) {
  const html = tableElement.outerHTML

  // 转换为 Excel XML 格式
  const excelXML = `
    <html xmlns:o="urn:schemas-microsoft-com:office:office"
          xmlns:x="urn:schemas-microsoft-com:office:excel">
      <head>
        <xml>
          <x:ExcelWorkbook>
            <x:ExcelWorksheets>
              <x:ExcelWorksheet>
                <x:Name>Sheet1</x:Name>
                <x:WorksheetOptions>
                  <x:DisplayGridlines/>
                </x:WorksheetOptions>
              </x:ExcelWorksheet>
            </x:ExcelWorksheets>
          </x:ExcelWorkbook>
        </xml>
      </head>
      <body>${html}</body>
    </html>
  `

  const blob = new Blob([excelXML], {
    type: 'application/vnd.ms-excel'
  })

  const url = URL.createObjectURL(blob)
  const a = document.createElement('a')
  a.href = url
  a.download = 'export.xls'
  a.click()
  URL.revokeObjectURL(url)
}

// utils/ExcelImporter.js
import * as XLSX from 'xlsx'

export class ExcelImporter {
  constructor(config = {}) {
    this.config = {
      sheetIndex: 0,
      headerRow: 1,
      dataStartRow: 2,
      ...config
    }
    this.errors = []
  }

  async import(file) {
    try {
      // 1. 读取文件
      const data = await this.readFile(file)

      // 2. 解析工作表
      const rows = this.parseSheet(data)

      // 3. 转换数据
      const records = this.transformData(rows)

      // 4. 校验数据
      const validRecords = this.validateData(records)

      return {
        success: this.errors.length === 0,
        data: validRecords,
        errors: this.errors,
        total: records.length
      }
    } catch (error) {
      return {
        success: false,
        data: [],
        errors: [{ message: error.message }],
        total: 0
      }
    }
  }

  async readFile(file) {
    return new Promise((resolve, reject) => {
      const reader = new FileReader()

      reader.onload = (e) => {
        try {
          const data = new Uint8Array(e.target.result)
          const workbook = XLSX.read(data, {
            type: 'array',
            cellDates: true,
            cellFormula: false,
            cellStyles: false
          })
          resolve(workbook)
        } catch (error) {
          reject(error)
        }
      }

      reader.onerror = () => reject(new Error('文件读取失败'))
      reader.readAsArrayBuffer(file)
    })
  }

  parseSheet(workbook) {
    const sheetName = workbook.SheetNames[this.config.sheetIndex]
    const worksheet = workbook.Sheets[sheetName]

    // 转换为二维数组
    const data = XLSX.utils.sheet_to_json(worksheet, {
      header: 1,
      defval: null,
      blankrows: false,
      raw: false // 所有值转为字符串
    })

    return data
  }

  transformData(rows) {
    if (rows.length < this.config.dataStartRow) {
      throw new Error('文件中没有数据')
    }

    // 获取表头
    const headers = rows[this.config.headerRow - 1]

    // 获取数据行
    const dataRows = rows.slice(this.config.dataStartRow - 1)

    // 转换为对象数组
    return dataRows.map((row, index) => {
      const obj = { _row: this.config.dataStartRow + index }

      headers.forEach((header, colIndex) => {
        obj[header] = row[colIndex]
      })

      return obj
    })
  }

  validateData(records) {
    // 子类实现具体校验逻辑
    return records
  }
}

// utils/ExcelExporter.js
import ExcelJS from 'exceljs'

export class ExcelExporter {
  constructor(config = {}) {
    this.config = {
      sheetName: 'Sheet1',
      headerStyle: {
        font: { bold: true, color: { argb: 'FFFFFFFF' } },
        fill: {
          type: 'pattern',
          pattern: 'solid',
          fgColor: { argb: 'FF4472C4' }
        },
        alignment: { horizontal: 'center', vertical: 'middle' },
        border: {
          top: { style: 'thin' },
          left: { style: 'thin' },
          bottom: { style: 'thin' },
          right: { style: 'thin' }
        }
      },
      ...config
    }
  }

  async export(data, columns, filename = 'export.xlsx') {
    const workbook = new ExcelJS.Workbook()
    const worksheet = workbook.addWorksheet(this.config.sheetName)

    // 设置列
    worksheet.columns = columns.map(col => ({
      header: col.title,
      key: col.key,
      width: col.width || 15
    }))

    // 样式化表头
    worksheet.getRow(1).eachCell(cell => {
      cell.style = this.config.headerStyle
    })
    worksheet.getRow(1).height = 25

    // 添加数据
    data.forEach(item => {
      const row = worksheet.addRow(item)

      // 应用行样式
      if (this.config.rowStyle) {
        row.eachCell(cell => {
          cell.style = this.config.rowStyle
        })
      }

      // 应用列样式
      columns.forEach((col, index) => {
        if (col.style) {
          row.getCell(index + 1).style = {
            ...row.getCell(index + 1).style,
            ...col.style
          }
        }
      })
    })

    // 冻结首行
    worksheet.views = [
      { state: 'frozen', xSplit: 0, ySplit: 1 }
    ]

    // 导出
    const buffer = await workbook.xlsx.writeBuffer()
    this.download(buffer, filename)
  }

  download(buffer, filename) {
    const blob = new Blob([buffer], {
      type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    })

    const url = URL.createObjectURL(blob)
    const a = document.createElement('a')
    a.href = url
    a.download = filename
    document.body.appendChild(a)
    a.click()
    document.body.removeChild(a)
    URL.revokeObjectURL(url)
  }
}

<!-- views/ExcelImportExport.vue -->
<template>
  <div class="excel-tools">
    <h2>Excel 导入导出</h2>

    <!-- 导入 -->
    <div class="import-section">
      <h3>数据导入</h3>

      <input
        ref="fileInputRef"
        type="file"
        accept=".xlsx,.xls"
        @change="handleFileChange"
        style="display: none"
      />

      <a-space>
        <a-button @click="downloadTemplate">
          下载模板
        </a-button>

        <a-button type="primary" @click="selectFile">
          选择文件
        </a-button>
      </a-space>

      <div v-if="importing" class="progress">
        <a-progress :percent="importProgress" />
        <span>{{ importStatus }}</span>
      </div>

      <div v-if="importResult" class="result">
        <a-alert
          :type="importResult.success ? 'success' : 'error'"
          :message="importResult.message"
          show-icon
        />

        <div v-if="importResult.errors.length > 0" class="errors">
          <h4>错误列表:</h4>
          <a-table
            :dataSource="importResult.errors"
            :columns="errorColumns"
            size="small"
            :pagination="{ pageSize: 10 }"
          />

          <a-button @click="downloadErrorReport">
            下载错误报告
          </a-button>
        </div>
      </div>
    </div>

    <!-- 导出 -->
    <div class="export-section">
      <h3>数据导出</h3>

      <a-space>
        <a-button type="primary" @click="handleExport">
          导出数据
        </a-button>

        <a-button @click="handleExportWithStyle">
          导出(带样式)
        </a-button>
      </a-space>

      <div v-if="exporting" class="progress">
        <a-progress :percent="exportProgress" />
        <span>生成中...</span>
      </div>
    </div>
  </div>
</template>

<script setup>
import { ref } from 'vue'
import { message } from 'ant-design-vue'
import { ExcelImporter } from '@/utils/ExcelImporter'
import { ExcelExporter } from '@/utils/ExcelExporter'

const fileInputRef = ref(null)
const importing = ref(false)
const importProgress = ref(0)
const importStatus = ref('')
const importResult = ref(null)

const exporting = ref(false)
const exportProgress = ref(0)

const errorColumns = [
  { title: '行号', dataIndex: 'row', width: 80 },
  { title: '字段', dataIndex: 'field', width: 120 },
  { title: '错误信息', dataIndex: 'message', ellipsis: true },
]

// 下载模板
function downloadTemplate() {
  const exporter = new ExcelExporter()

  const columns = [
    { title: '姓名', key: 'name', width: 15 },
    { title: '年龄', key: 'age', width: 10 },
    { title: '部门', key: 'department', width: 15 },
    { title: '邮箱', key: 'email', width: 25 },
  ]

  const sampleData = [
    { name: '张三', age: 28, department: '技术部', email: 'zhangsan@example.com' },
    { name: '李四', age: 32, department: '销售部', email: 'lisi@example.com' },
  ]

  exporter.export(sampleData, columns, 'import-template.xlsx')
  message.success('模板下载成功')
}

// 选择文件
function selectFile() {
  fileInputRef.value.click()
}

// 文件选择变化
async function handleFileChange(e) {
  const file = e.target.files[0]
  if (!file) return

  importing.value = true
  importProgress.value = 0
  importStatus.value = '解析文件...'
  importResult.value = null

  try {
    // 创建导入器
    const importer = new UserImporter()

    // 模拟进度
    const progressInterval = setInterval(() => {
      if (importProgress.value < 90) {
        importProgress.value += 10
      }
    }, 200)

    // 导入
    const result = await importer.import(file)

    clearInterval(progressInterval)
    importProgress.value = 100

    if (result.success) {
      importStatus.value = '导入成功!'
      importResult.value = {
        success: true,
        message: `成功导入 ${result.data.length} 条数据`,
        errors: []
      }

      // 调用接口保存数据
      // await saveImportData(result.data)

    } else {
      importStatus.value = '导入失败'
      importResult.value = {
        success: false,
        message: `发现 ${result.errors.length} 个错误`,
        errors: result.errors
      }
    }

  } catch (error) {
    message.error('导入失败: ' + error.message)
  } finally {
    importing.value = false
    // 清空 input
    e.target.value = ''
  }
}

// 下载错误报告
function downloadErrorReport() {
  // 实现错误报告下载
  message.info('功能开发中')
}

// 导出数据
async function handleExport() {
  exporting.value = true
  exportProgress.value = 0

  try {
    // 获取数据
    const data = await fetchExportData()

    const columns = [
      { title: 'ID', key: 'id', width: 10 },
      { title: '姓名', key: 'name', width: 15 },
      { title: '年龄', key: 'age', width: 10 },
      { title: '部门', key: 'department', width: 15 },
      { title: '邮箱', key: 'email', width: 25 },
    ]

    const exporter = new ExcelExporter()
    await exporter.export(data, columns, 'users.xlsx')

    message.success('导出成功')

  } catch (error) {
    message.error('导出失败: ' + error.message)
  } finally {
    exporting.value = false
  }
}

// 带样式导出
async function handleExportWithStyle() {
  // 实现带样式的导出
  message.info('功能开发中')
}

// 模拟数据获取
async function fetchExportData() {
  return [
    { id: 1, name: '张三', age: 28, department: '技术部', email: 'zhangsan@example.com' },
    { id: 2, name: '李四', age: 32, department: '销售部', email: 'lisi@example.com' },
    { id: 3, name: '王五', age: 25, department: '人事部', email: 'wangwu@example.com' },
  ]
}

// 用户导入器
class UserImporter extends ExcelImporter {
  validateData(records) {
    const validRecords = []

    records.forEach(record => {
      const errors = []

      // 姓名校验
      if (!record['姓名']) {
        errors.push({ field: '姓名', message: '姓名不能为空' })
      }

      // 年龄校验
      const age = Number(record['年龄'])
      if (isNaN(age) || age < 0 || age > 150) {
        errors.push({ field: '年龄', message: '年龄必须是0-150的数字' })
      }

      // 邮箱校验
      if (record['邮箱'] && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(record['邮箱'])) {
        errors.push({ field: '邮箱', message: '邮箱格式不正确' })
      }

      if (errors.length > 0) {
        errors.forEach(error => {
          this.errors.push({
            row: record._row,
            ...error
          })
        })
      } else {
        validRecords.push({
          name: record['姓名'],
          age: Number(record['年龄']),
          department: record['部门'] || '',
          email: record['邮箱'] || ''
        })
      }
    })

    return validRecords
  }
}
</script>

<style scoped>
.excel-tools {
  padding: 24px;
}

.import-section,
.export-section {
  margin-bottom: 40px;
  padding: 20px;
  background: #fafafa;
  border-radius: 8px;
}

.progress {
  margin-top: 16px;
}

.result {
  margin-top: 16px;
}

.errors {
  margin-top: 16px;
}
</style>

const workbook = XLSX.read(data, {
  cellFormula: true // 保留公式
})

// 使用 exceljs
worksheet.getCell('D2').value = {
  formula: 'A2+B2',
  result: 10 // 可选,设置默认显示值
}
```"

### Q: 如何处理 Excel 中的图片?

"SheetJS 免费版不支持图片,需要用 exceljs:

```javascript
// 添加图片
const imageId = workbook.addImage({
  buffer: imageBuffer,
  extension: 'png'
})

worksheet.addImage(imageId, {
  tl: { col: 0, row: 0 }, // top-left
  ext: { width: 200, height: 200 }
})

// 读取图片
const images = worksheet.getImages()
images.forEach(image => {
  const imageData = workbook.getImage(image.imageId)
  // imageData.buffer 是图片数据
})
```"

### Q: 如何导出百万行数据?

"百万行数据不能一次性加载到内存,要用流式处理:

1. 后端分页返回数据
2. 前端分批请求,逐批写入 Excel
3. 使用 exceljs 的 stream API
4. 或者直接让后端生成 Excel,前端下载

我们项目里,超过 10 万行的数据都是后端导出。"

### Q: 如何实现 Excel 的在线预览?

"有几种方案:

1. 转成 HTML 表格显示(SheetJS 支持)
2. 转成 JSON,用表格组件显示
3. 用 Office Online 或 Google Sheets API
4. 用第三方服务如 Zoho Viewer

我们用的是转 JSON + 表格组件,简单有效。"

---

## 项目经验总结

### 踩过的坑

1. **日期格式问题** - Excel 的日期是数字,没有时区概念,需特殊处理
2. **编码问题** - CSV 文件可能是 GBK 编码,需要转换
3. **大文件卡死** - 没做流式处理,10 万行卡死浏览器
4. **公式丢失** - 导入后公式变成值,需要 cellFormula: true

### 性能数据

- 10 万行数据导出:< 5s
- 100MB 文件导入:< 10s
- 内存占用:< 200MB
- 文件体积:压缩后减少 30%

### 可以吹的点

- 支持 10+ 种数据类型自动转换
- 智能错误提示,精确到行列
- 模板化导入,成功率 95%+
- 流式处理,支持超大文件
- 样式完整保留,报表专业
```text