vue：功能：table动态合并+前端导出

场景：第一列WM 名字相同，行内合并，后面的列合并的行数根据第一列合并的行来。第二列‘累计请假天数'根据合并的几列显示数值。后续需合并的列以第一列合并行数为基准

注）当前数据表头固定，行内数据不固定。以第一列WM为判断条件，相同名字的那几行数据合并单元格。合并相同名字的那几行数据，后面的列有需要和第一列合并条件一样。

1、根据查询条件：日期。查询近三月日期数据。表头的日期是根据查询的日期条件的前三个月来。日期格式月份转换成英文格式。

2、导出的数据和页面保持一致

注）合并数据，后端提供的数据可能不是按照人在一起的顺序，前端需要处理下数据格式，把同一个人的放在一起

注）本示例是用 vue3+element plus 实现的。

效果图：

一、完整代码实现

1、页面代码

 
<script lang="ts" setup>
import dayjs from 'dayjs'
import * as api from '@/api/trade/LeaveDay'
import useConfig from '@/views/trade/LeavePay/hooks/useConfig'
const { handleFilterMonth } = useConfig()
import OutExcelSheet from '@/hooks/web/outToExcelManySheet'
 
defineOptions({ name: 'LeaveDay' })
 
const message = useMessage() // 消息弹窗
const { t } = useI18n() // 国际化
 
const loading = ref(true) // 列表的加载中
const total = ref(0) // 列表的总页数
const list = ref([]) // 列表的数据
const queryParams = reactive({
  startDate: dayjs().subtract(1, 'month').format('YYYY-MM')
})
const queryFormRef = ref() // 搜索的表单
 
/** 搜索按钮操作 */
const handleQuery = () => {
  getList()
}
 
/** 重置按钮操作 */
const resetQuery = () => {
  queryFormRef.value.resetFields()
  handleQuery()
}
 
/** 查询列表 */
const getList = async () => {
  // mock 数据
  // loading.value = false
  // list.value = tableList.value
  // 列表数据
  loading.value = true
  try {
    // const data = await api.getLeavePayPage(queryParams)
    // mock数据
    const data = tableList.value
 
    // 1、TotalLeavePayOut 根据 同一个name 的 LeavePayOut 的总和
    const names = [...new Set(data.map((item) => item.name))]
    const updatedDemo = names.map((name) => {
      const list = data.filter((val) => val.name === name)
      const sum = list.reduce((accumulator, current) => {
        return accumulator + Number(current.leavePayout || 0)
      }, 0)
 
      return {
        name,
        list,
        sum: sum.toString()
      }
    })
    // console.log('updatedDemo', updatedDemo)
    // 2、导出表格时所需的合计值
    totalSum.value = updatedDemo.reduce((accumulator, item) => {
      if (item.sum) {
        return accumulator + parseFloat(item.sum)
      }
      return accumulator
    }, 0)
    console.log('累计值', totalSum.value)
 
    // 3、数据是没有按顺序来的，需处理成一个人的假期放在一起，方便合并数据
    const newList = updatedDemo.flatMap((item) => item.list)
    console.log(newList)
 
    // 3、合计的值放到每行对应的数据里
    const result = newList.map((item) => {
      const matchingItem = updatedDemo.find((val) => val.name === item.name)
      if (matchingItem) {
        item.totalLeavePayout = matchingItem.sum
      }
      return item
    })
    console.log('result', result)
 
    // 4、最终数据
    list.value = result
  } finally {
    loading.value = false
  }
}
 
// mock数据
const tableList = ref([
  {
    name: 'karla',
    leaveDays: null,
    paidLeaveDate: '2023-01-01',
    actualDaysOneMonth: '10',
    actualDaysTwoMonth: '18',
    actualDaysThreeMonth: '18',
    payoutCommissionOneMonthPrice: '10000',
    payoutCommissionTwoMonthPrice: '20000',
    payoutCommissionThreeMonthPrice: '30000',
    averageCommission: '1400',
    commissionDuringLeave: '640',
    leavePayout: '760',
    totalLeavePayout: ''
  },
  {
    name: 'karla',
    leaveDays: null,
    paidLeaveDate: '2023-01-04',
    actualDaysOneMonth: '10',
    actualDaysTwoMonth: '18',
    actualDaysThreeMonth: '18',
    payoutCommissionOneMonthPrice: '10000',
    payoutCommissionTwoMonthPrice: '20000',
    payoutCommissionThreeMonthPrice: '30000',
    averageCommission: '1400',
    commissionDuringLeave: '1600',
    leavePayout: '0',
    totalLeavePayout: ''
  },
  {
    name: 'karla',
    leaveDays: null,
    paidLeaveDate: '2023-01-06',
    actualDaysOneMonth: '10',
    actualDaysTwoMonth: '18',
    actualDaysThreeMonth: '18',
    payoutCommissionOneMonthPrice: '10000',
    payoutCommissionTwoMonthPrice: '20000',
    payoutCommissionThreeMonthPrice: '30000',
    averageCommission: '1400',
    commissionDuringLeave: '1800',
    leavePayout: '0',
    totalLeavePayout: ''
  },
  {
    name: 'karla',
    leaveDays: null,
    paidLeaveDate: '2023-01-24',
    actualDaysOneMonth: '10',
    actualDaysTwoMonth: '18',
    actualDaysThreeMonth: '18',
    payoutCommissionOneMonthPrice: '10000',
    payoutCommissionTwoMonthPrice: '20000',
    payoutCommissionThreeMonthPrice: '30000',
    averageCommission: '1400',
    commissionDuringLeave: '',
    leavePayout: '0',
    totalLeavePayout: ''
  },
  {
    name: 'karla',
    leaveDays: null,
    paidLeaveDate: '2023-01-18',
    actualDaysOneMonth: '10',
    actualDaysTwoMonth: '18',
    actualDaysThreeMonth: '18',
    payoutCommissionOneMonthPrice: '10000',
    payoutCommissionTwoMonthPrice: '20000',
    payoutCommissionThreeMonthPrice: '30000',
    averageCommission: '1400',
    commissionDuringLeave: '1200',
    leavePayout: '200',
    totalLeavePayout: ''
  },
  {
    name: 'York',
    leaveDays: null,
    paidLeaveDate: '2023-01-18',
    actualDaysOneMonth: '28',
    actualDaysTwoMonth: '24',
    actualDaysThreeMonth: '18',
    payoutCommissionOneMonthPrice: '10000',
    payoutCommissionTwoMonthPrice: '20000',
    payoutCommissionThreeMonthPrice: '30000',
    averageCommission: '1500',
    commissionDuringLeave: '1800',
    leavePayout: '0',
    totalLeavePayout: ''
  },
  {
    name: 'York',
    leaveDays: null,
    paidLeaveDate: '2023-01-24',
    actualDaysOneMonth: '28',
    actualDaysTwoMonth: '24',
    actualDaysThreeMonth: '18',
    payoutCommissionOneMonthPrice: '10000',
    payoutCommissionTwoMonthPrice: '20000',
    payoutCommissionThreeMonthPrice: '30000',
    averageCommission: '1500',
    commissionDuringLeave: '700',
    leavePayout: '800',
    totalLeavePayout: ''
  },
  {
    name: 'Caleb',
    leaveDays: null,
    paidLeaveDate: '2023-01-29',
    actualDaysOneMonth: '22',
    actualDaysTwoMonth: '15',
    actualDaysThreeMonth: '17',
    payoutCommissionOneMonthPrice: '8899.12',
    payoutCommissionTwoMonthPrice: '7833',
    payoutCommissionThreeMonthPrice: '1455.63',
    averageCommission: '1366.8',
    commissionDuringLeave: '734.8',
    leavePayout: '632',
    totalLeavePayout: ''
  }
])
const exportList = ref([
  [
    'WM',
    'Total Leave days',
    'Paid Leave Date',
    'Actual working days of previous 3 months\t（第一个月）',
    'Actual working days of previous 3 months\t（第二个月）',
    'Actual working days of previous 3 months\t（第三个月）',
    'Payout of commission 3 months\t第一个月）',
    'Payout of commission 3 months\t（第二个月）',
    'Payout of commission 3 months\t（第三个月）',
    'Average commission / day',
    'Commission during leave',
    'Leave Payout',
    'Total Leave Payout'
  ],
  [
    'karla',
    5,
    '2023-01-01',
    '10',
    '18',
    '18',
    '10000',
    '20000',
    '30000',
    '1400',
    '640',
    '760',
    '0.00'
  ],
  [
    'karla',
    5,
    '2023-01-04',
    '10',
    '18',
    '18',
    '10000',
    '20000',
    '30000',
    '1400',
    '1600',
    '0.00',
    '0.00'
  ],
  [
    'karla',
    5,
    '2023-01-06',
    '10',
    '18',
    '18',
    '10000',
    '20000',
    '30000',
    '1400',
    '1800',
    '0.00',
    '0.00'
  ],
  [
    'karla',
    5,
    '2023-01-24',
    '10',
    '18',
    '18',
    '10000',
    '20000',
    '30000',
    '1400',
    '0.00',
    '0.00',
    '0.00'
  ],
  [
    'karla',
    5,
    '2023-01-18',
    '10',
    '18',
    '18',
    '10000',
    '20000',
    '30000',
    '1400',
    '1600',
    '0.00',
    '0.00'
  ],
  [
    'York',
    2,
    '2023-01-18',
    '28',
    '24',
    '18',
    '10000',
    '20000',
    '30000',
    '1500',
    '1800',
    '0.00',
    '666'
  ],
  [
    'York',
    2,
    '2023-01-24',
    '28',
    '24',
    '18',
    '10000',
    '20000',
    '30000',
    '1500',
    '700',
    '800',
    '666'
  ],
  [
    'Caleb',
    1,
    '2023-01-29',
    '22',
    '15',
    '17',
    '8899.12',
    '7833',
    '1455.63',
    '1366.8',
    '734.8',
    '632',
    '0.00'
  ]
])
 
const saveRow = ref() // 存放的就是第一列中同name的合并
const objectSpanMethod = ({ row, column, rowIndex, columnIndex }) => {
  // 第一列（WM）行合并
  if (columnIndex === 0) {
    // 1、取出tableList中每条数据的name，并去重
    const names = [...new Set(list.value.map((item) => item.name))]
 
    // 2、对表格数据按照name进行排序，并保持原始顺序
    const sortedData = names.reduce((acc, name) => {
      const matchingItems = list.value.filter((item) => item.name === name)
      return [...acc, ...matchingItems]
    }, [])
 
    // 3、匹配出names中的符合名字的第条数据的索引和最后一条索引。放在arr的数组里
    const arr = names.map((name) => {
      const firstIndex = sortedData.findIndex((item) => item.name === name)
      const lastIndex = sortedData.reduceRight((acc, item, index) => {
        if (item.name === name && acc === -1) {
          return index
        }
        return acc
      }, -1)
      return { firstNameIndex: firstIndex, lastNameIndex: lastIndex }
    })
    saveRow.value = arr
 
    // 4、把当列数据中相同的数据起始和结束的单元格进行合并
    const matchingData = arr.find(
      (item) => item.firstNameIndex <= rowIndex && rowIndex <= item.lastNameIndex
    )
 
    // 5、结果
    if (matchingData) {
      if (rowIndex === matchingData.firstNameIndex) {
        return {
          rowspan: matchingData.lastNameIndex - matchingData.firstNameIndex + 1,
          colspan: 1
        }
      } else {
        return {
          rowspan: 0,
          colspan: 0
        }
      }
    }
  }
  // 需合并列：根据第一列中需要合并的行数saveRow.value 来合并对应列下的行内合并
  if ([1, 3, 4, 5, 6, 7, 8, 12].includes(columnIndex)) {
    const matchingData = saveRow.value.find(
      (item) => item.firstNameIndex <= rowIndex && rowIndex <= item.lastNameIndex
    )
 
    if (matchingData) {
      if (rowIndex === matchingData.firstNameIndex) {
        const params = {
          rowspan: matchingData.lastNameIndex - matchingData.firstNameIndex + 1,
          colspan: 1,
          value: matchingData.lastNameIndex - matchingData.firstNameIndex + 1
        }
        list.value[rowIndex].leaveDays = params.value
        return params
      } else {
        return {
          rowspan: 0,
          colspan: 0
        }
      }
    }
  }
}
 
/** 导出 */
const exportLoading = ref(false)
const handleExport = async () => {
  try {
    // 导出的二次确认
    await message.exportConfirm()
    exportLoading.value = true
 
    const columnsHeader = {
      name: 'WM',
      leaveDays: 'Total Leave days',
      paidLeaveDate: 'Paid Leave Date',
      actualDaysOneMonth: handleFilterMonth(
        dayjs(queryParams.startDate).subtract(3, 'month').format('YYYY-MM')
      ),
      actualDaysTwoMonth: handleFilterMonth(
        dayjs(queryParams.startDate).subtract(2, 'month').format('YYYY-MM')
      ),
      actualDaysThreeMonth: handleFilterMonth(
        dayjs(queryParams.startDate).subtract(1, 'month').format('YYYY-MM')
      ),
      payoutCommissionOneMonthPrice: handleFilterMonth(
        dayjs(queryParams.startDate).subtract(3, 'month').format('YYYY-MM')
      ),
      payoutCommissionTwoMonthPrice: handleFilterMonth(
        dayjs(queryParams.startDate).subtract(2, 'month').format('YYYY-MM')
      ),
      payoutCommissionThreeMonthPrice: handleFilterMonth(
        dayjs(queryParams.startDate).subtract(1, 'month').format('YYYY-MM')
      ),
      averageCommission: 'Average commission/day',
      commissionDuringLeave: 'Commission during leave',
      leavePayout: 'Leave Payout',
      totalLeavePayout: 'Total Leave Payout'
    }
 
    const sheet1 = {
      name: 'LeavePay',
      data: [
        [
          'WM',
          'Total Leave days',
          'Paid Leave Date',
          'Actual working days of previous 3 months',
          '',
          '',
          'Payout of commission 3 months',
          '',
          '',
          'Average commission / day',
          'Commission during leave',
          'Leave Payout',
          'Total Leave Payout'
        ],
        // ...exportList.value,
        ...OutExcelSheet.handleExcelTable(columnsHeader, list.value),
        ['', '', '', '', '', '', '', '', '', '', '', 'Total', '999']
      ],
      merges: [],
      rowHeights: [{ hpx: 20 }, { hpx: 20 }]
    }
 
    // 合并：第0列、第1列、第三列、第四列、第五列、第六列、第七列和第八列的相同值进行行合并
    const mergedRows = new Map()
    for (let i = 1; i < sheet1.data.length; i++) {
      const cellValue0 = sheet1.data[i][0]
      const cellValue1 = sheet1.data[i][1]
      const cellValue3 = sheet1.data[i][3]
      const cellValue4 = sheet1.data[i][4]
      const cellValue5 = sheet1.data[i][5]
      const cellValue6 = sheet1.data[i][6]
      const cellValue7 = sheet1.data[i][7]
      const cellValue8 = sheet1.data[i][8]
      const prevValue0 = sheet1.data[i - 1][0]
      const prevValue1 = sheet1.data[i - 1][1]
      const prevValue3 = sheet1.data[i - 1][3]
      const prevValue4 = sheet1.data[i - 1][4]
      const prevValue5 = sheet1.data[i - 1][5]
      const prevValue6 = sheet1.data[i - 1][6]
      const prevValue7 = sheet1.data[i - 1][7]
      const prevValue8 = sheet1.data[i - 1][8]
 
      if (
        cellValue0 === prevValue0 &&
        cellValue1 === prevValue1 &&
        cellValue3 === prevValue3 &&
        cellValue4 === prevValue4 &&
        cellValue5 === prevValue5 &&
        cellValue6 === prevValue6 &&
        cellValue7 === prevValue7 &&
        cellValue8 === prevValue8
      ) {
        if (mergedRows.has(cellValue0)) {
          // 更新合并的结束行索引
          mergedRows.get(cellValue0).end = i
        } else {
          // 添加新的合并信息
          mergedRows.set(cellValue0, { start: i - 1, end: i })
        }
      }
    }
 
    // 添加行合并信息到 mergesHeader
    for (const [value, { start, end }] of mergedRows.entries()) {
      sheet1.merges.push({ s: { r: start, c: 0 }, e: { r: end, c: 0 } })
      sheet1.merges.push({ s: { r: start, c: 1 }, e: { r: end, c: 1 } })
      sheet1.merges.push({ s: { r: start, c: 3 }, e: { r: end, c: 3 } })
      sheet1.merges.push({ s: { r: start, c: 4 }, e: { r: end, c: 4 } })
      sheet1.merges.push({ s: { r: start, c: 5 }, e: { r: end, c: 5 } })
      sheet1.merges.push({ s: { r: start, c: 6 }, e: { r: end, c: 6 } })
      sheet1.merges.push({ s: { r: start, c: 7 }, e: { r: end, c: 7 } })
      sheet1.merges.push({ s: { r: start, c: 8 }, e: { r: end, c: 8 } })
      sheet1.merges.push({ s: { r: start, c: 12 }, e: { r: end, c: 12 } })
    }
 
    const mergesHeader = [
      // 行合并
      { s: { r: 0, c: 3 }, e: { r: 0, c: 5 } },
      { s: { r: 0, c: 6 }, e: { r: 0, c: 8 } },
      // 列合并（r 表示行索引，c 表示列索引）
      { s: { r: 0, c: 0 }, e: { r: 1, c: 0 } }, // 第0列的第0行和第1行合并
      { s: { r: 0, c: 1 }, e: { r: 1, c: 1 } }, // 第1列的第0行和第1行合并
      { s: { r: 0, c: 2 }, e: { r: 1, c: 2 } }, // 第2列的第1行和第1行合并
      { s: { r: 0, c: 9 }, e: { r: 1, c: 9 } },
      { s: { r: 0, c: 10 }, e: { r: 1, c: 10 } },
      { s: { r: 0, c: 11 }, e: { r: 1, c: 11 } },
      { s: { r: 0, c: 12 }, e: { r: 1, c: 12 } }
    ]
 
    const sheetData = [sheet1]
 
    OutExcelSheet.exportSheetExcel(sheetData, mergesHeader, `LeavePay导出.xlsx`, true)
  } catch {
  } finally {
    exportLoading.value = false
  }
}
 
/** 初始化 **/
onMounted(() => {
  getList()
})
script>

2、封装的方法（导出）

import XLSX from 'xlsx-js-style'
import FileSaver from 'file-saver'
 
export default {
  exportSheetExcel(sheetData, mergerArr, fileName = 'karlaExport.xlsx', statusBorder = false) {
    const wb = XLSX.utils.book_new() // 创建一个新工作簿
 
    for (let i = 0; i < sheetData.length; i++) {
      const sheet = sheetData[i]
 
      // 检查数据项是否存在
      if (!sheet.data) {
        continue // 如果数据项不存在，则跳过当前循环
      }
 
      const ws = XLSX.utils.aoa_to_sheet(sheet.data) // 将数据数组转换为工作表
 
      // 设置合并单元格
      ws['!merges'] =
        sheet.merges && sheet.merges.length > 0
          ? [...sheet.merges, ...(mergerArr || [])]
          : mergerArr
 
      // 设置列宽为自适应
      if (sheet.data.length > 0) {
        ws['!cols'] = sheet.data[0].map((_, index) => ({ wch: 15 }))
      }
 
      // 设置行高
      if (sheet.rowHeights && sheet.rowHeights.length > 0) {
        ws['!rows'] = sheet.rowHeights.map((height) => ({ hpt: height, hpx: height }))
      }
 
      const borderAll = {
        top: { style: 'thin' },
        bottom: { style: 'thin' },
        left: { style: 'thin' },
        right: { style: 'thin' }
      }
 
      // 设置单元格样式
      for (const key in ws) {
        if (ws.hasOwnProperty(key)) {
          const cell = ws[key]
          if (cell && typeof cell === 'object') {
            // 判断是否为最后一行【核心】
            const rowIndex = parseInt(key.replace(/[A-Z]/g, ''))
            if (statusBorder && rowIndex === sheet.data.length) {
              if (cell.s && cell.s.border) {
                delete cell.s.border.right // 删除右侧边框样式
              }
              // 内容居中
              cell.s = cell.s || {}
              cell.s.alignment = cell.s.alignment || {}
              cell.s.alignment.horizontal = 'center'
              cell.s.alignment.vertical = 'center'
              cell.s.alignment.wrapText = true
            } else {
              cell.s = {
                border: borderAll,
                alignment: {
                  horizontal: 'center',
                  vertical: 'center',
                  wrapText: true
                },
                font: {
                  sz: 12,
                  color: {
                    rgb: '000000'
                  }
                },
                numFmt: 'General',
                fill: {
                  fgColor: { rgb: 'FFFFFF' }
                }
              }
            }
          }
        }
      }
 
      XLSX.utils.book_append_sheet(wb, ws, sheet.name) // 将工作表添加到工作簿并指定名称
    }
 
    const wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'array' }) // 将工作簿转换为数组
 
    const file = new Blob([wbout], { type: 'application/octet-stream' }) // 创建Blob对象
    FileSaver.saveAs(file, fileName) // 下载文件
  },
  // 二维数组中空的数据设置为 0【数据处理】
  emptyValues(array, defaultValue) {
    for (let i = 0; i < array.length; i++) {
      for (let j = 0; j < array[i].length; j++) {
        if (array[i][j] === null || array[i][j] === undefined || array[i][j] === '') {
          array[i][j] = defaultValue
        }
      }
    }
    return array
  },
  // 生成excel列表数据【格式转换】
  handleExcelTable(columnHeader, list) {
    if (list.length === 0) return []
 
    // 表头
    const tableColumn = Object.keys([columnHeader][0])
 
    // 表格生成的数据
    const sheet = [tableColumn]
    list.forEach((item) => {
      const row = tableColumn.map((column) => item[column])
      sheet.push(row)
    })
 
    // 表头匹配对应的中文
    const firstRow = sheet[0].map((column) => columnHeader[column])
    sheet[0] = firstRow
 
    return sheet || []
  }
}

3、处理方法代码

import dayjs from 'dayjs'
export default function useConfig() {
  const monthArray = [
    { month: '一月', abbreviation: 'Jan', number: '01' },
    { month: '二月', abbreviation: 'Feb', number: '02' },
    { month: '三月', abbreviation: 'Mar', number: '03' },
    { month: '四月', abbreviation: 'Apr', number: '04' },
    { month: '五月', abbreviation: 'May', number: '05' },
    { month: '六月', abbreviation: 'Jun', number: '06' },
    { month: '七月', abbreviation: 'Jul', number: '07' },
    { month: '八月', abbreviation: 'Aug', number: '08' },
    { month: '九月', abbreviation: 'Sep', number: '09' },
    { month: '十月', abbreviation: 'Oct', number: '10' },
    { month: '十一月', abbreviation: 'Nov', number: '11' },
    { month: '十二月', abbreviation: 'Dec', number: '12' }
  ]
 
  const handleFilterMonth = (val) => {
    const year = val.substring(0, 4)
    const month = val.substring(5, 7)
    const matchedData = monthArray.find((item) => month === item.number)?.abbreviation || month
    return `${year}-${matchedData}`
  }
 
  /** 获取两月之间的那个月 */
  const getMiddleMonth = (startDateStr, endDateStr) => {
    const startDate = dayjs(startDateStr).startOf('month')
    const endDate = dayjs(endDateStr).startOf('month')
 
    const middleMonth = startDate.add(1, 'month').format('YYYY-MM')
    return middleMonth
  }
 
  return {
    monthArray,
    handleFilterMonth,
    getMiddleMonth
  }
}