luckysheet-修复排序时单元格公式没有变化的问题

问题描述

        首先对范围单元格进行排序设置。比如第3行单元格E3的公式为‘=SUM(B3:D3,Sheet8!A3)’，经过排序后单元格会替换到第四行，而公式应该变为‘=SUM(B4:D4,Sheet8!A3)’，但是没有变化，也就是说luckysheet在进行排序时并没有对公式进行处理

【设置排序】

解决思路

        在排序前，往单元格对象里面插入此时的行坐标。排序后，用前面插入的行坐标和现在排序后单元格所在行进行对比，不相同就证明单元格所在的行有进行替换，则需要对公式的数字部分进行修补。

        具体补多少，由前后的行坐标的差值决定。

具体代码

1、为单元格插入行坐标

在orderBy.js的确定排序的click回调中，在确定有无合并单元格的循环中为单元格插入坐标

                for(let r = str; r <= r2; r++){
                    let data_row = [];

                    for(let c = c1; c <= c2; c++){
                        if(d[r][c] != null && d[r][c].mc != null){
                            hasMc = true;
                            break;
                        }
                        // nby 修复排序时公式的引用 先为单元格添加行列坐标
                        let cell = d[r][c]
                        if(d[r][c] != null){
                            cell = { ...cell , _r : r, _c : c}
                        }
                        data_row.push(cell);
                        // nby 修复排序时公式的引用 END
                        // 源码
                        // data_row.push(d[r][c]);
                    }

                    data.push(data_row);
                }

2、排序后，通过比较行坐标，修补公式的数字部分。

        继续往下找， data = orderbydata([].concat(data), i, asc);就是对数据进行排序，点进去，会来到sort.js。

        在orderbydata方法的最后进行公式修补。

        修补步骤

        遍历范围数组时拿到单元格的公式f，通过正则（正则规则：字母前面不能有!感叹号【因为不需要对其他sheet页的引用进行修补】，字母后面需要跟数字）匹配，

        对于英文部分是其他sheet页name的字符不进行替换，其他符合条件的字符的数字部分会加上“前后行坐标的差值”。

//数据排序方法
function orderbydata(data, index, isAsc) {
    if (isAsc == null) {
        isAsc = true;
    }
    let a = function (x, y) {
       // ......
    }
    let d = function (x, y) {
      // ......
    }
    //修改的找这里==========================
    let resultData;
    if (isAsc) {
         resultData = data.sort(a);
        // return data.sort(a);
    }
    else {
         resultData = data.sort(d);
        // return data.sort(d);
    }

    // nby 修复排序时公式的引用
    let sheetNameArr = luckysheet.getAllSheets()?.map(sheet=>sheet.name)
    let last = Store.luckysheet_select_save[0];
    let r1 = last["row"][0], r2 = last["row"][1];   //这里只需要r
    let c1 = last["column"][0], c2 = last["column"][1];

    let rowArr=[]
    for(let i = r1;i <= r2;i++){    //范围单元格的row
        rowArr.push(i)
    }
    resultData.forEach((arr,index)=>{
        arr.forEach(cell=>{
            if(cell?.f!=null){
                let r = rowArr[index]
                if(cell._r != r){//当前单元格的r不能和之前的位置对应
                    // 证明有进行替换，需要为公式附上差值
                    let f = cell.f
                    // let resultF = f.replace(/(?!\!)[a-zA-Z]+\d+/g, matchStr => {
                    let resultF = f.replace(/[^!][a-zA-Z]+\d+/g, matchStr => {
                        if(sheetNameArr.includes(matchStr.substr(1))){
                            // 是sheetName不进行计算
                            return matchStr
                        }
                        let number = parseInt(matchStr.match(/\d+/)[0]); // 提取数字部分并转换为整数
                        let incrementedNumber = number + (r - cell._r); // 为公式补上差值
                        return matchStr.replace(/\d+/, incrementedNumber); // 替换原来的数字部分
                    });
                    cell.f = resultF
                }
            }
        })
    })
    return resultData
    // nby 修复排序时公式的引用
}

【点击进入orderbydata】

【orderbydata方法的修改】

3、清除之前为单元格插入的行坐标

回到orderBy.js，在排序后进行剔除。

                // nby 修复排序时公式的引用 最后清除单元格坐标
                for(let r = str; r <= r2; r++){
                    for(let c = c1; c <= c2; c++){
                        if(data[r - str][c - c1] != null ){
                            delete data[r - str][c - c1]?._r
                            delete data[r - str][c - c1]?._c
                        }
                    }
                }
                // nby 修复排序时公式的引用

结束

        一开始想的是“在sort排序的时候修改公式f，然后每次补上的差值是正负1”，但是发现不行，sort循环的次数不能完好的体现差值。在排序后进行修补实现更简单。