根据数据的长度来转化为字母数字，应用于Excel合并表格

Question

将数据长度转化为字母匹配（描述的不准确，请看下面描述）

阐述问题：在前端导出使用了vue-admin-element的方法，只要匹配好excel表格就可以合并单元格了，但是需求数据不确定，所以不能写死了，一旦数据条数增多，前端就会不断要改，所想根据自己要合并的单元个进行封装一下方法，本人数据算法较弱，尝试几个小时未能弄好，请大佬支援一下，感激不尽。

问题背景：如图

1.根据下面提供的数据生成

2.效果图片展示

相关可获得的数据：

头部数据：
const theadr = ['序号','公司名称','赎回基金','申请日期','规模','巨额赎回客户','客户渠道','申请赎回量','赎回比率','基金代码'];
需要渲染的数据：
`const tdata =
[

{
  "channe": "直销",
  "cust": "中国银行",
  "amt": "55.28",
  "company": '成都有限公司',
  "code": "32606",
  "name":"南方通融",
  "red_amt":"45.28",
  "red_ratio":"9.6%",
  "requt_date":"20200206",
  "scale":"558",
  "no":1,
},
{
  "channe": "直销",
  "cust": "中国银行",
  "amt": "55.28",
  "company": '成都有限公司',
  "code": "32606",
  "name":"南方通融",
  "red_amt":"45.28",
  "red_ratio":"9.6%",
  "requt_date":"20200206",
  "scale":"558",
  "no":1,
},
{
  "channe": "直销",
  "cust": "中国银行",
  "amt": "55.28",
  "company": '成都有限公司',
  "code": "32606",
  "name":"南方通融",
  "red_amt":"45.28",
  "red_ratio":"9.6%",
  "requt_date":"20200206",
  "scale":"558",
  "no":1,
},
{
  "channe": "直销",
  "cust": "中国银行",
  "amt": "55.28",
  "company": '成都有限公司',
  "code": "32606",
  "name":"南方通融",
  "red_amt":"45.28",
  "red_ratio":"9.6%",
  "requt_date":"20200206",
  "scale":"558",
  "no":1,
},
{
  "channe": "直销",
  "cust": "中国银行",
  "amt": "55.28",
  "company": '成都有限公司',
  "code": "32606",
  "name":"南方通融",
  "red_amt":"45.28",
  "red_ratio":"9.6%",
  "requt_date":"20200206",
  "scale":"558",
  "no":1,
},
{
  "channe": "直销",
  "cust": "中国银行",
  "amt": "55.28",
  "company": '成都有限公司',
  "code": "32606",
  "name":"南方通融",
  "red_amt":"45.28",
  "red_ratio":"9.6%",
  "requt_date":"20200206",
  "scale":"558",
  "no":1,
},                

];
`

期望结果输出：

相关链接：

1.项目展示链接：https://panjiachen.github.io/vue-element-admin/#/excel/export-merge-header
2.项目代码位置;https://github.com/PanJiaChen/vue-element-admin/blob/master/src/views/excel/merge-header.vue

Answer 1

正好有类似的需求，试试看

const primaryKey = 'name' //行数据的主键
const indexKey = 'no'
const startRow = 2 //2=ignoreRows+1
const header = ['序号', '名称', '日期']
const alias = ['no', 'name', 'date']
const result = []
const data = [
    {
        no: 1,
        name: '老王',
        date: '20190201'
    },
    {
        no: 2,
        name: '老王',
        date: '20190202'
    },
    {
        no: 3,
        name: '老李',
        date: '20190201'
    },
    {
        no: 4,
        name: '老李',
        date: '20190202'
    },
    {
        no: 5,
        name: '老李',
        date: '20190203'
    },
    {
        no: 6,
        name: '老李',
        date: '20190203'
    },
]

const merge = alias.map(_ => [1])
const temp = alias.map(_ => 1)

function compare(currentRow, lastRow, nextRow, colIndex) {
    const attr = alias[colIndex]

    //若与上一行的primaryKey相同且属性相同，则temp对应项+1
    if (currentRow[primaryKey] === lastRow[primaryKey]
        && currentRow[attr] === lastRow[attr]) {
        temp[colIndex]++
        //最后一行特殊处理
        if (!nextRow) {
            merge[colIndex].push(temp[colIndex])
        }
        else {
            //若与下一行相同
            if (currentRow[primaryKey] === nextRow[primaryKey]
                && currentRow[attr] === nextRow[attr]) {
                merge[colIndex].push(1)
            }
            //否则存入
            else merge[colIndex].push(temp[colIndex])
        }
    }
    //否则清零
    else {
        temp[colIndex] = 1
        merge[colIndex].push(1)
    }
}

let indexAfterMerge = 1
for (let i = 1; i < data.length; i++) {
    const currentRow = data[i]
    const lastRow = data[i - 1]
    const nextRow = data[i + 1]

    //重写序号
    if (currentRow[primaryKey] === lastRow[primaryKey]) {
        currentRow[indexKey] = lastRow[indexKey]
    }
    else currentRow[indexKey] = ++indexAfterMerge
    if (nextRow && nextRow[primaryKey] === currentRow[primaryKey]) {
        nextRow[indexKey] = currentRow[indexKey]
    }

    alias.forEach((_, colIndex) => compare(currentRow, lastRow, nextRow, colIndex))
}

function number2excelColumnHeader(n) {
    let s = ""
    while (n >= 0) {
        s = String.fromCharCode(n % 26 + 65) + s
        n = Math.floor(n / 26) - 1
    }
    return s
}

function mergeResultConstructor(arr, colIndex) {
    const colHeader = number2excelColumnHeader(colIndex)
    arr.forEach((colspan, rowIndex) => {
        if (colspan > 1) {
            const start = colHeader + (startRow + rowIndex - (colspan - 1))
            const end = colHeader + (startRow + rowIndex)
            result.push(`${start}:${end}`)
        }
    })
}

merge.forEach((arr, index) => {
    mergeResultConstructor(arr, index)
})

console.log(result)