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

将数据长度转化为字母匹配(描述的不准确,请看下面描述)

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

问题背景:如图

1.根据下面提供的数据生成
结果输出.png
2.效果图片展示表格.png

相关可获得的数据:

头部数据:
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,
},                

];
`

期望结果输出:

结果输出.png

相关链接:

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

阅读 529
评论 更新于 2月14日
    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)
    
    
    评论 赞赏 2月14日