实现xlsx文件带上样式导入导出,表头单元格合并(附带代码仓库地址)

原创

已于 2025-06-27 13:58:41 修改 · 339 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#js #elementui #vue

于 2024-10-26 12:45:52 首次发布

xlsx导入导出

基于xlsx-js-style实现的xlsx文件带上样式导入导出,额外处理了表头单元格合并,封装了element plus的表格组件

关键知识点

xlsx-js-style 内容的格式和css样式的转换
导入导出时候的数据格式处理,例如单元格的数值格式
单元格合并时对数据的影响

关键代码

import {
   
    dayjs } from 'element-plus'
import XLSX from 'xlsx-js-style'
import type {
   
    CellObject } from 'xlsx-js-style'
interface DownLoadExcelParams {
   
   
  data: any[]
  props: TableXlsxColumnProp[]
  fileName: string
}

interface Style {
   
   
  backgroundColor?: string
  ['background-color']?: string
  color?: string
  fontWeight?: string
  ['font-weight']?: string
  fontStyle?: string
  ['font-style']?: string
  textDecoration?: string
  ['text-decoration']?: string
  textAlign?: string
  ['text-align']?: string
  fontSize?: string
  ['font-size']?: string
}
export function useXlsx() {
   
   
  const onFileData = (binary: string, xlsxProps: TableXlsxColumnProp[]) => {
   
   
    try {
   
   
      const workbook = XLSX.read(binary, {
   
    type: 'binary', cellDates: true })
      const dataList: unknown[][] = []
      workbook.SheetNames.forEach((sheetName) => {
   
   
        const worksheet = workbook.Sheets[sheetName]
        const data = XLSX.utils.sheet_to_json(worksheet)
        if (data.length) {
   
   
          dataList.push(onSheetJson(data as any[], xlsxProps))
        }
      })

      return dataList
    } catch (error) {
   
   
      console.error(error)
    }
  }

  const onSheetJson = (list: any[], xlsxProps: TableXlsxColumnProp[]) => {
   
   
    // 判断表头是否合并
    if (xlsxProps.some((p) => p.children)) {
   
   
      const keyMapData = list[0]
      list.forEach((item: any) => {
   
   
        for (let key in keyMapData) {
   
   
          if (item.hasOwnProperty(key)) {
   
   
            item[keyMapData[key]] = item[key]
          }
        }
      })
      list.shift()
    }
    return list.map((item) => onSheetItem(item, xlsxProps))
  }

  const onSheetItem = (item: any, xlsxProps: TableXlsxColumnProp[]) => {
   
   
    let data: CommonObject = {
   
   }
    const flatProps = onFlat(xlsxProps)
    for (let key in item) {
   
   
      const prop = flatProps.find((p) => p.label === key)
      if (!prop) continue
      const value = item[key]
      data[prop.key] = value
    }
    return data
  }
  const onDownLoadExcel = ({
   
    data, props, fileName }: DownLoadExcelParams) => {
   
   
    // 处理内容
    const {
   
    header, headerMerge, flatProps } = onGetExcelSetting(props)
    let contentArr = onContentArray(data, flatProps)
    let arr = [...header, ...contentArr]
    let sheet