[Quick Ticket] When the Row, Column, or Cell Positions Change, the Formula Data Updates the Position.

[Dushusir]

Initial checklist

• Is this really a problem?
• I have searched the Github Issues for similar issues, but did not find anything.

Problem

当插入行，插入列等操作时，数组公式数据需要跟随变化

Solution

close #963
close #1086
close #1129

update formula 流程

1. command 拦截，转换 command 信息适配 refRange，对公式内容做偏移，得到需要偏移内容的公式
2. 用 refRange 偏移公式位置，返回 undo/redo 数据到 setRangeValues mutation
   • redo 数据：匹配上的旧位置删除旧值，新的位置增加新值（新值先检验旧位置是否有偏移内容，有的话用偏移的新内容，没有则取旧值）
   • undo 数据：匹配上的旧位置保存旧值，新的位置删除值。撤销时采用undos会在偏移位置后再操作数据。
3. onCommandExecuted，在公式计算之前，用 setRangeValues 的信息，删除旧的 formulaData, ArrayFormula 和ArrayFormulaCellData ，发送worker（协同冲突后做补充的setRangeValues，正常操作触发公式更新，undo/redo都在此捕获处理）

[univer-bot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

Origin Title: [Quick Ticket] When the Row, Column, or Cell Positions Change, the Formula Data Updates the Position.

Title: [Quick Ticket] When the Row, Column, or Cell Positions Change, the Formula Data Updates the Position.

---

Initial checklist

• Is this really a problem?
• I have searched the Github Issues for similar issues, but did not find anything.

Problem

When inserting rows, inserting columns, etc., the array formula data needs to follow changes

Solution

close #963
close #1086
close #1129

update formula process

1. Command interception, convert the command information to adapt refRange, offset the formula content, and obtain the formula that requires offset content
2. Use refRange to offset the formula position and return undo/redo data to setRangeValues ​​mutation
   • redo data: delete the old value at the matching old position, and add a new value at the new position (the new value first checks whether the old position has offset content, if so, use the new offset content, if not, take the old value)
   • undo data: The old position on the match saves the old value, and the new position deletes the value. Using undos when undoing will operate the data after the offset position.
3. onCommandExecuted, before formula calculation, use the setRangeValues ​​information to delete the old formulaData, ArrayFormula and ArrayFormulaCellData, and send the worker (complementary setRangeValues ​​is done after collaborative conflicts, normal operations trigger formula updates, and undo/redo are captured and processed here)

[Dushusir]

移动公式选区后预期结果显示#ref

2024_2_2.14_29_18.video.webm

[univer-bot]

Bot detected the issue body's language is not English, translate it automatically. 👯👭🏻🧑‍🤝‍🧑👫🧑🏿‍🤝‍🧑🏻👩🏾‍🤝‍👨🏿👬🏿

Expected results after moving formula selection #ref

https://github-production-user-asset-6210df.s3.amazonaws.com/154207126/301752594-4246b7d8-04f5-4f86-b6b1-366ab1948984.webm?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz- Credential=AKIAVCODYLSA53PQK4ZA%2F20240322%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20240322T075641Z&X-Amz-Expires=300&X-Amz-Signature=4dc4c8e7675292060b0c2ff 4da154bda0c641951d83be478967e8b16a5e42510&X-Amz-SignedHeaders=host&actor_id=26371161&key_id=0&repo_id=543101941

[Dushusir]

Closed by #1694