Skip to content

Build your own AI office with Univer Office SDK. Bring editing, calculation, collaboration, and file processing into your product for people and AI agents. Built on an extensible open-source core.

83.6 KB
Raw Blame
/**
 * Copyright 2023-present DreamNum Co., Ltd.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

import type { ICellData, Injector, IWorkbookData, Nullable, Univer, Workbook } from '@univerjs/core';
import type { ISetDefinedNameMutationParam } from '@univerjs/engine-formula';
import type { IDeleteRangeMoveLeftCommandParams, IDeleteRangeMoveUpCommandParams, IInsertColCommandParams, IInsertRangeMoveDownCommandParams, IInsertRangeMoveRightCommandParams, IInsertRowCommandParams, IMoveColsCommandParams, IMoveRangeCommandParams, IMoveRowsCommandParams, IRemoveRowColCommandParams, IRemoveSheetCommandParams, ISetRangeValuesCommandParams, ISetWorksheetNameCommandParams } from '@univerjs/sheets';
import type { ISetRowHiddenCommandParams } from '@univerjs/sheets/commands/commands/set-row-visible.command.js';
import { CellValueType, Direction, ICommandService, IUniverInstanceService, LocaleType, RANGE_TYPE, RedoCommand, UndoCommand } from '@univerjs/core';
import { RemoveDefinedNameMutation, SetArrayFormulaDataMutation, SetDefinedNameMutation, SetFormulaDataMutation } from '@univerjs/engine-formula';
import { DeleteRangeMoveLeftCommand, DeleteRangeMoveUpCommand, InsertColByRangeCommand, InsertColCommand, InsertColMutation, InsertRangeMoveDownCommand, InsertRangeMoveRightCommand, InsertRowByRangeCommand, InsertRowCommand, InsertRowMutation, MoveColsCommand, MoveColsMutation, MoveRangeCommand, MoveRangeMutation, MoveRowsCommand, MoveRowsMutation, RemoveColByRangeCommand, RemoveColCommand, RemoveColMutation, RemoveDefinedNameCommand, RemoveRowByRangeCommand, RemoveRowCommand, RemoveRowMutation, RemoveSheetCommand, RemoveSheetMutation, SetColHiddenCommand, SetColHiddenMutation, SetColVisibleMutation, SetDefinedNameCommand, SetRangeValuesCommand, SetRangeValuesMutation, SetRowHiddenCommand, SetRowHiddenMutation, SetRowVisibleMutation, SetSelectionsOperation, SetSpecificColsVisibleCommand, SetSpecificRowsVisibleCommand, SetWorksheetNameCommand, SetWorksheetNameMutation, SheetsSelectionsService } from '@univerjs/sheets';
import { UpdateFormulaController } from '@univerjs/sheets-formula';
import { afterEach, beforeEach, describe, expect, it } from 'vitest';
import { createCommandTestBed } from './create-command-test-bed';

const TEST_WORKBOOK_DATA_DEMO = (): IWorkbookData => ({
    id: 'test',
    appVersion: '3.0.0-alpha',
    sheets: {
        sheet1: {
            id: 'sheet1',
            cellData: {
                0: {
                    6: {
                        f: '=A1:B2',
                    },
                },
                1: {
                    2: {
                        v: 1,
                        t: CellValueType.NUMBER,
                    },
                },
                2: {
                    1: {
                        v: 1,
                        t: CellValueType.NUMBER,
                    },
                    2: {
                        f: '=A1:B2',
                    },
                },
                5: {
                    2: {
                        f: '=SUM(A1:B2)',
                    },
                    3: {
                        v: 1,
                        t: CellValueType.NUMBER,
                    },
                },
                6: {
                    2: {
                        v: 1,
                        t: CellValueType.NUMBER,
                    },
                },
                7: {
                    0: {
                        v: 1,
                        t: CellValueType.NUMBER,
                    },
                    1: {
                        v: 1,
                        t: CellValueType.NUMBER,
                    },
                    2: {
                        v: 1,
                        t: CellValueType.NUMBER,
                    },
                    3: {
                        f: '=SUM(A8)',
                    },
                    4: {
                        f: '=SUM(B8)',
                    },
                    5: {
                        f: '=SUM(C8)',
                    },
                    7: {
                        f: '=SUM(A8:C10)',
                    },
                    8: {
                        f: '=SUM(B8:D10)',
                        si: 'CarNau',
                    },
                    9: {
                        si: 'CarNau',
                    },
                },
                8: {
                    0: {
                        v: 1,
                        t: CellValueType.NUMBER,
                    },
                    1: {
                        v: 1,
                        t: CellValueType.NUMBER,
                    },
                    2: {
                        v: 1,
                        t: CellValueType.NUMBER,
                    },
                    3: {
                        f: '=SUM(A9)',
                    },
                    4: {
                        f: '=SUM(B9)',
                    },
                    5: {
                        f: '=SUM(C9)',
                    },
                    7: {
                        f: '=SUM(A9:C11)',
                        si: 'y0gLJX',
                    },
                    8: {
                        si: 'y0gLJX',
                    },
                    9: {
                        si: 'y0gLJX',
                    },
                },
                9: {
                    0: {
                        v: 1,
                        t: CellValueType.NUMBER,
                    },
                    1: {
                        v: 1,
                        t: CellValueType.NUMBER,
                    },
                    2: {
                        v: 1,
                        t: CellValueType.NUMBER,
                    },
                    3: {
                        f: '=SUM(A10)',
                    },
                    4: {
                        f: '=SUM(B10)',
                    },
                    5: {
                        f: '=SUM(C10)',
                    },
                    7: {
                        si: 'y0gLJX',
                    },
                    8: {
                        si: 'y0gLJX',
                    },
                    9: {
                        si: 'y0gLJX',
                    },
                },
                10: {
                    0: {
                        f: '=SUM(A8)',
                    },
                    1: {
                        f: '=SUM(B8)',
                    },
                    2: {
                        f: '=SUM(C8)',
                    },
                },
                11: {
                    0: {
                        f: '=SUM(A9)',
                    },
                    1: {
                        f: '=SUM(B9)',
                    },
                    2: {
                        f: '=SUM(C9)',
                    },
                },
                12: {
                    0: {
                        f: '=SUM(A10)',
                    },
                    1: {
                        f: '=SUM(B10)',
                    },
                    2: {
                        f: '=SUM(C10)',
                    },
                },
                14: {
                    0: {
                        f: '=A1:B2',
                    },
                    2: {
                        f: '=Sheet2!A1:B2',
                    },
                },
                18: {
                    0: {
                        v: 1,
                    },
                    1: {
                        f: '=SUM(A19)',
                        t: 2,
                        v: 1,
                    },
                },
                19: {
                    0: {
                        v: 2,
                    },
                    1: {
                        f: '=SUM(A20)',
                        si: 'id1',
                        t: 2,
                        v: 2,
                    },
                },
                20: {
                    0: {
                        v: 3,
                    },
                    1: {
                        si: 'id1',
                        t: 2,
                        v: 3,
                    },
                },
                21: {
                    2: {
                        f: '=OFFSET(A1,1,1)',
                        v: 0,
                        t: 2,
                    },
                    3: {
                        f: '=OFFSET(B1,1,1)',
                        si: 'id2',
                        v: 1,
                        t: 2,
                    },
                    4: {
                        si: 'id2',
                        v: 1,
                        t: 2,
                    },
                },
            },
            name: 'Sheet1',
        },
        sheet2: {
            id: 'sheet2',
            cellData: {
                2: {
                    0: {
                        f: '=SUM(DefinedName1)',
                    },
                },
                9: {
                    0: {
                        f: '=SUM(Sheet1!A1:A3)',
                    },
                    9: {
                        f: '=SUM(A2:C4)',
                    },
                },
            },
            name: 'Sheet2',
        },
        sheet3: {
            id: 'sheet3',
            cellData: {
                0: {
                    0: {
                        v: 1,
                        t: 2,
                    },
                    3: {
                        f: '=A1:A5',
                        v: 1,
                        t: 2,
                    },
                },
                1: {
                    0: {
                        v: 2,
                        t: 2,
                    },
                },
                2: {
                    0: {
                        v: 3,
                        t: 2,
                    },
                },
                3: {
                    0: {
                        v: 4,
                        t: 2,
                    },
                },
                4: {
                    0: {
                        v: 5,
                        t: 2,
                    },
                },
                5: {
                    0: {
                        f: '=SUBTOTAL(109,A1:A5)',
                        v: 15,
                        t: 2,
                    },
                    1: {
                        f: '=SUBTOTAL(109,A3)',
                        v: 3,
                        t: 2,
                    },
                    2: {
                        f: '=A3',
                        v: 3,
                        t: 2,
                    },
                },
            },
            name: 'Sheet3',
        },
        sheet4: {
            id: 'sheet4',
            cellData: {
                0: {
                    0: {
                        f: '=SUBTOTAL(109,Sheet3!A1:A5)',
                        v: 15,
                        t: 2,
                    },
                },
            },
            name: 'Sheet4',
        },
        sheet5: {
            id: 'sheet5',
            name: 'Sheet5',
            cellData: {
                1: {
                    1: { // B2
                        v: 5,
                        t: CellValueType.NUMBER,
                    },
                    3: { // D2
                        f: '=$B2',
                    },
                    4: { // E2
                        f: '=$B2',
                        si: 'MFYkBI',
                    },
                    5: { // F2
                        si: 'MFYkBI',
                    },
                    6: { // G2
                        si: 'MFYkBI',
                    },
                    7: { // H2
                        si: 'MFYkBI',
                    },
                    8: { // I2
                        si: 'MFYkBI',
                    },
                },
            },
        },
        sheet6: {
            id: 'sheet6',
            name: 'Sheet6',
            cellData: {
                0: {
                    0: {
                        v: 1,
                        t: 2,
                    },
                    1: {
                        f: '=A1',
                        v: 1,
                        t: 2,
                    },
                },
                1: {
                    0: {
                        v: 2,
                        t: 2,
                    },
                    1: {
                        f: '=A2',
                        si: 'W8Hdfc',
                        v: 2,
                        t: 2,
                    },
                },
                2: {
                    0: {
                        v: 3,
                        t: 2,
                    },
                    1: {
                        si: 'W8Hdfc',
                        v: 3,
                        t: 2,
                    },
                },
                3: {
                    0: {
                        v: 4,
                        t: 2,
                    },
                    1: {
                        si: 'W8Hdfc',
                        v: 4,
                        t: 2,
                    },
                },
            },
        },
    },
    locale: LocaleType.ZH_CN,
    name: '',
    sheetOrder: ['sheet1', 'sheet2', 'sheet3', 'sheet4'],
    styles: {},
    resources: [
        {
            name: 'SHEET_DEFINED_NAME_PLUGIN',
            data: '{"soAI3OK4sq":{"id":"soAI3OK4sq","name":"DefinedName1","formulaOrRefString":"Sheet2!$A$1:$B$2","comment":"","localSheetId":"AllDefaultWorkbook"}}',
        },
    ],
});

describe('Test update formula ', () => {
    let univer: Univer;
    let get: Injector['get'];
    let commandService: ICommandService;
    let getValues: (
        startRow: number,
        startColumn: number,
        endRow: number,
        endColumn: number,
        sheetId?: string
    ) => Array<Array<Nullable<ICellData>>> | undefined;

    beforeEach(() => {
        const testBed = createCommandTestBed(TEST_WORKBOOK_DATA_DEMO(), [
            [UpdateFormulaController],
        ]);

        univer = testBed.univer;
        get = testBed.get;

        commandService = get(ICommandService);
        commandService.registerCommand(MoveRangeCommand);
        commandService.registerCommand(MoveRangeMutation);

        commandService.registerCommand(MoveRowsCommand);
        commandService.registerCommand(MoveRowsMutation);

        commandService.registerCommand(MoveColsCommand);
        commandService.registerCommand(MoveColsMutation);

        commandService.registerCommand(InsertRowCommand);
        commandService.registerCommand(InsertRowMutation);

        commandService.registerCommand(InsertColCommand);
        commandService.registerCommand(InsertColMutation);

        commandService.registerCommand(RemoveRowCommand);
        commandService.registerCommand(RemoveRowMutation);

        commandService.registerCommand(RemoveColCommand);
        commandService.registerCommand(RemoveColMutation);

        commandService.registerCommand(DeleteRangeMoveLeftCommand);
        commandService.registerCommand(DeleteRangeMoveUpCommand);
        commandService.registerCommand(InsertRangeMoveDownCommand);
        commandService.registerCommand(InsertRangeMoveRightCommand);

        commandService.registerCommand(SetWorksheetNameCommand);
        commandService.registerCommand(SetWorksheetNameMutation);
        commandService.registerCommand(RemoveSheetCommand);
        commandService.registerCommand(RemoveSheetMutation);

        commandService.registerCommand(SetSelectionsOperation);
        commandService.registerCommand(SetRangeValuesCommand);
        commandService.registerCommand(SetRangeValuesMutation);
        commandService.registerCommand(SetFormulaDataMutation);
        commandService.registerCommand(SetArrayFormulaDataMutation);

        // set defined name
        commandService.registerCommand(SetDefinedNameCommand);
        commandService.registerCommand(RemoveDefinedNameCommand);
        commandService.registerCommand(SetDefinedNameMutation);
        commandService.registerCommand(RemoveDefinedNameMutation);

        [
            RemoveColByRangeCommand,
            RemoveRowByRangeCommand,
            InsertRowByRangeCommand,
            InsertColByRangeCommand,
        ].forEach((command) => {
            commandService.registerCommand(command);
        });
        // hide row/column
        commandService.registerCommand(SetRowHiddenCommand);
        commandService.registerCommand(SetRowHiddenMutation);
        commandService.registerCommand(SetSpecificRowsVisibleCommand);
        commandService.registerCommand(SetRowVisibleMutation);
        commandService.registerCommand(SetColHiddenCommand);
        commandService.registerCommand(SetColHiddenMutation);
        commandService.registerCommand(SetSpecificColsVisibleCommand);
        commandService.registerCommand(SetColVisibleMutation);

        getValues = (
            startRow: number,
            startColumn: number,
            endRow: number,
            endColumn: number,
            sheetId: string = 'sheet1'
        ): Array<Array<Nullable<ICellData>>> | undefined =>
            get(IUniverInstanceService)
                .getUnit<Workbook>('test')
                ?.getSheetBySheetId(sheetId)
                ?.getRange(startRow, startColumn, endRow, endColumn)
                .getValues();

        get(UpdateFormulaController);
    });

    afterEach(() => {
        univer.dispose();
    });

    describe('update formula', () => {
        it('Move range, update reference', async () => {
            const params: IMoveRangeCommandParams = {
                fromRange: {
                    startRow: 0,
                    startColumn: 0,
                    endRow: 1,
                    endColumn: 1,
                    rangeType: 0,
                },
                toRange: {
                    startRow: 0,
                    startColumn: 3,
                    endRow: 1,
                    endColumn: 4,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(MoveRangeCommand.id, params)).toBeTruthy();
            const values = getValues(5, 2, 5, 2);
            expect(values).toStrictEqual([[{ f: '=SUM(D1:E2)' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(5, 2, 5, 2);
            expect(valuesUndo).toStrictEqual([[{ f: '=SUM(A1:B2)' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(5, 2, 5, 2);
            expect(valuesRedo).toStrictEqual([[{ f: '=SUM(D1:E2)' }]]);
        });

        it('Move range, update position', async () => {
            const params: IMoveRangeCommandParams = {
                fromRange: {
                    startRow: 5,
                    startColumn: 2,
                    endRow: 5,
                    endColumn: 2,
                    rangeType: 0,
                },
                toRange: {
                    startRow: 5,
                    startColumn: 3,
                    endRow: 5,
                    endColumn: 3,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(MoveRangeCommand.id, params)).toBeTruthy();
            const values = getValues(5, 2, 5, 3);
            expect(values).toStrictEqual([[null, { f: '=SUM(A1:B2)' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(5, 2, 5, 3);
            expect(valuesUndo).toStrictEqual([[{ f: '=SUM(A1:B2)' }, { v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(5, 2, 5, 3);
            expect(valuesRedo).toStrictEqual([[null, { f: '=SUM(A1:B2)' }]]);
        });

        it('Move range, update reference, release si', async () => {
            const workbook = get(IUniverInstanceService).getUnit<Workbook>('test');
            const sheetId = 'sheet6';
            const sheet6 = workbook?.getSheetBySheetId(sheetId);
            if (!sheet6) {
                throw new Error(`${sheetId}not found`);
            }
            workbook?.setActiveSheet(sheet6);

            const params: IMoveRangeCommandParams = {
                fromRange: {
                    startRow: 0,
                    startColumn: 1,
                    endRow: 3,
                    endColumn: 1,
                    rangeType: 0,
                },
                toRange: {
                    startRow: 2,
                    startColumn: 1,
                    endRow: 5,
                    endColumn: 1,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(MoveRangeCommand.id, params)).toBeTruthy();
            const values = getValues(0, 1, 5, 1, sheetId);
            expect(values).toStrictEqual([
                [null],
                [null],
                [{ f: '=A1' }],
                [{ f: '=A2' }],
                [{ f: '=A3' }],
                [{ f: '=A4' }],
            ]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(0, 1, 5, 1, sheetId);
            expect(valuesUndo).toStrictEqual([
                [{ f: '=A1' }],
                [{ f: '=A2', si: 'W8Hdfc' }],
                [{ si: 'W8Hdfc' }],
                [{ si: 'W8Hdfc' }],
                [null],
                [null],
            ]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(0, 1, 5, 1, sheetId);
            expect(valuesRedo).toStrictEqual([
                [null],
                [null],
                [{ f: '=A1' }],
                [{ f: '=A2' }],
                [{ f: '=A3' }],
                [{ f: '=A4' }],
            ]);
        });

        it('Move range with f/si', async () => {
            const workbook = get(IUniverInstanceService).getUnit<Workbook>('test');
            const sheetId = 'sheet6';
            const sheet6 = workbook?.getSheetBySheetId(sheetId);
            if (!sheet6) {
                throw new Error(`${sheetId}not found`);
            }
            workbook?.setActiveSheet(sheet6);

            const params: IMoveRangeCommandParams = {
                fromRange: {
                    startRow: 1,
                    startColumn: 1,
                    endRow: 1,
                    endColumn: 1,
                    rangeType: 0,
                },
                toRange: {
                    startRow: 1,
                    startColumn: 3,
                    endRow: 1,
                    endColumn: 3,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(MoveRangeCommand.id, params)).toBeTruthy();
            const values1 = getValues(1, 3, 1, 3, sheetId);
            expect(values1).toStrictEqual([[{ f: '=A2' }]]);
            const values2 = getValues(1, 1, 3, 1, sheetId);
            expect(values2).toStrictEqual([
                [null],
                [{ f: '=A3' }],
                [{ f: '=A4' }],
            ]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo1 = getValues(1, 3, 1, 3, sheetId);
            expect(valuesUndo1).toStrictEqual([[null]]);
            const valuesUndo2 = getValues(1, 1, 3, 1, sheetId);
            expect(valuesUndo2).toStrictEqual([
                [{ f: '=A2', si: 'W8Hdfc' }],
                [{ si: 'W8Hdfc' }],
                [{ si: 'W8Hdfc' }],
            ]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo1 = getValues(1, 3, 1, 3, sheetId);
            expect(valuesRedo1).toStrictEqual([[{ f: '=A2' }]]);
            const valuesRedo2 = getValues(1, 1, 3, 1, sheetId);
            expect(valuesRedo2).toStrictEqual([
                [null],
                [{ f: '=A3' }],
                [{ f: '=A4' }],
            ]);
        });

        it('Move range with si only, but the si is not source formula cell', async () => {
            const workbook = get(IUniverInstanceService).getUnit<Workbook>('test');
            const sheetId = 'sheet6';
            const sheet6 = workbook?.getSheetBySheetId(sheetId);
            if (!sheet6) {
                throw new Error(`${sheetId}not found`);
            }
            workbook?.setActiveSheet(sheet6);

            const params: IMoveRangeCommandParams = {
                fromRange: {
                    startRow: 2,
                    startColumn: 1,
                    endRow: 2,
                    endColumn: 1,
                    rangeType: 0,
                },
                toRange: {
                    startRow: 2,
                    startColumn: 3,
                    endRow: 2,
                    endColumn: 3,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(MoveRangeCommand.id, params)).toBeTruthy();
            const values1 = getValues(2, 3, 2, 3, sheetId);
            expect(values1).toStrictEqual([[{ f: '=A3' }]]);
            const values2 = getValues(1, 1, 3, 1, sheetId);
            expect(values2).toStrictEqual([
                [{ f: '=A2', si: 'W8Hdfc', t: 2, v: 2 }],
                [null],
                [{ si: 'W8Hdfc', t: 2, v: 4 }],
            ]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo1 = getValues(2, 3, 2, 3, sheetId);
            expect(valuesUndo1).toStrictEqual([[null]]);
            const valuesUndo2 = getValues(1, 1, 3, 1, sheetId);
            expect(valuesUndo2).toStrictEqual([
                [{ f: '=A2', si: 'W8Hdfc', t: 2, v: 2 }],
                [{ si: 'W8Hdfc' }],
                [{ si: 'W8Hdfc', t: 2, v: 4 }],
            ]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo1 = getValues(2, 3, 2, 3, sheetId);
            expect(valuesRedo1).toStrictEqual([[{ f: '=A3' }]]);
            const valuesRedo2 = getValues(1, 1, 3, 1, sheetId);
            expect(valuesRedo2).toStrictEqual([
                [{ f: '=A2', si: 'W8Hdfc', t: 2, v: 2 }],
                [null],
                [{ si: 'W8Hdfc', t: 2, v: 4 }],
            ]);
        });

        it('Move range with f/si and si', async () => {
            const workbook = get(IUniverInstanceService).getUnit<Workbook>('test');
            const sheetId = 'sheet6';
            const sheet6 = workbook?.getSheetBySheetId(sheetId);
            if (!sheet6) {
                throw new Error(`${sheetId}not found`);
            }
            workbook?.setActiveSheet(sheet6);

            const params: IMoveRangeCommandParams = {
                fromRange: {
                    startRow: 1,
                    startColumn: 1,
                    endRow: 2,
                    endColumn: 1,
                    rangeType: 0,
                },
                toRange: {
                    startRow: 1,
                    startColumn: 3,
                    endRow: 2,
                    endColumn: 3,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(MoveRangeCommand.id, params)).toBeTruthy();
            const values1 = getValues(1, 3, 2, 3, sheetId);
            expect(values1).toStrictEqual([[{ f: '=A2' }], [{ f: '=A3' }]]);
            const values2 = getValues(1, 1, 3, 1, sheetId);
            expect(values2).toStrictEqual([
                [null],
                [null],
                [{ f: '=A4' }],
            ]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(1, 1, 3, 1, sheetId);
            expect(valuesUndo).toStrictEqual([
                [{ f: '=A2', si: 'W8Hdfc' }],
                [{ si: 'W8Hdfc' }],
                [{ si: 'W8Hdfc' }],
            ]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo1 = getValues(1, 3, 2, 3, sheetId);
            expect(valuesRedo1).toStrictEqual([[{ f: '=A2' }], [{ f: '=A3' }]]);
            const valuesRedo2 = getValues(1, 1, 3, 1, sheetId);
            expect(valuesRedo2).toStrictEqual([
                [null],
                [null],
                [{ f: '=A4' }],
            ]);
        });

        it('Move range, update reference with si ', async () => {
            const params: IMoveRangeCommandParams = {
                fromRange: {
                    startRow: 18,
                    startColumn: 1,
                    endRow: 20,
                    endColumn: 1,
                    rangeType: 0,
                },
                toRange: {
                    startRow: 18,
                    startColumn: 2,
                    endRow: 20,
                    endColumn: 2,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(MoveRangeCommand.id, params)).toBeTruthy();
            const values = getValues(18, 1, 20, 2);
            expect(values).toStrictEqual([
                [null, { f: '=SUM(A19)' }],
                [null, { f: '=SUM(A20)' }],
                [null, { f: '=SUM(A21)' }],
            ]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(18, 1, 20, 2);
            expect(valuesUndo).toStrictEqual([
                [{ f: '=SUM(A19)' }, null],
                [{ f: '=SUM(A20)', si: 'id1' }, null],
                [{ si: 'id1' }, null],
            ]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(18, 1, 20, 2);
            expect(valuesRedo).toStrictEqual([
                [null, { f: '=SUM(A19)' }],
                [null, { f: '=SUM(A20)' }],
                [null, { f: '=SUM(A21)' }],
            ]);
        });

        it('Move rows, update reference', async () => {
            const selectionManager = get(SheetsSelectionsService);

            // A1
            selectionManager.addSelections([
                {
                    range: { startRow: 1, startColumn: 0, endRow: 1, endColumn: 0, rangeType: RANGE_TYPE.ROW },
                    primary: null,
                    style: null,
                },
            ]);

            const params: IMoveRowsCommandParams = {
                fromRange: {
                    startRow: 1,
                    startColumn: 0,
                    endRow: 1,
                    endColumn: 19,
                    rangeType: 1,
                },
                toRange: {
                    startRow: 4,
                    startColumn: 0,
                    endRow: 4,
                    endColumn: 19,
                    rangeType: 1,
                },
            };

            expect(await commandService.executeCommand(MoveRowsCommand.id, params)).toBeTruthy();
            const values = getValues(5, 2, 5, 2);
            expect(values).toStrictEqual([[{ f: '=SUM(A1:B4)' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(5, 2, 5, 2);
            expect(valuesUndo).toStrictEqual([[{ f: '=SUM(A1:B2)' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(5, 2, 5, 2);
            expect(valuesRedo).toStrictEqual([[{ f: '=SUM(A1:B4)' }]]);
        });

        it('Move rows, update reference and position', async () => {
            const selectionManager = get(SheetsSelectionsService);
            // A1
            selectionManager.addSelections([
                {
                    range: { startRow: 1, startColumn: 0, endRow: 1, endColumn: 0, rangeType: RANGE_TYPE.ROW },
                    primary: null,
                    style: null,
                },
            ]);

            const params: IMoveRowsCommandParams = {
                fromRange: {
                    startRow: 1,
                    startColumn: 0,
                    endRow: 1,
                    endColumn: 19,
                    rangeType: 1,
                },
                toRange: {
                    startRow: 9,
                    startColumn: 0,
                    endRow: 9,
                    endColumn: 19,
                    rangeType: 1,
                },
            };

            expect(await commandService.executeCommand(MoveRowsCommand.id, params)).toBeTruthy();
            const values = getValues(1, 2, 2, 2);
            expect(values).toStrictEqual([[{ f: '=A1:B9' }], [null]]);
            const values2 = getValues(4, 2, 5, 2);
            expect(values2).toStrictEqual([[{ f: '=SUM(A1:B9)' }], [{ v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(1, 2, 2, 2);
            expect(valuesUndo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }], [{ f: '=A1:B2' }]]);
            const valuesUndo2 = getValues(5, 2, 6, 2);
            expect(valuesUndo2).toStrictEqual([[{ f: '=SUM(A1:B2)' }], [{ v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(1, 2, 2, 2);
            expect(valuesRedo).toStrictEqual([[{ f: '=A1:B9' }], [null]]);
            const valuesRedo2 = getValues(4, 2, 5, 2);
            expect(valuesRedo2).toStrictEqual([[{ f: '=SUM(A1:B9)' }], [{ v: 1, t: CellValueType.NUMBER }]]);
        });

        it('Move rows, reduce scope', async () => {
            const workbook = get(IUniverInstanceService).getUnit<Workbook>('test');
            const sheet2 = workbook?.getSheetBySheetId('sheet2');
            if (!sheet2) {
                throw new Error('sheet2 not found');
            }
            workbook?.setActiveSheet(sheet2);

            const selectionManager = get(SheetsSelectionsService);
            // A1
            selectionManager.addSelections([
                {
                    range: { startRow: 2, startColumn: 0, endRow: 2, endColumn: 19, rangeType: RANGE_TYPE.ROW },
                    primary: null,
                    style: null,
                },
            ]);

            const params: IMoveRowsCommandParams = {
                fromRange: {
                    startRow: 2,
                    startColumn: 0,
                    endRow: 2,
                    endColumn: 19,
                    rangeType: RANGE_TYPE.ROW,
                },
                toRange: {
                    startRow: 7,
                    startColumn: 0,
                    endRow: 7,
                    endColumn: 19,
                    rangeType: RANGE_TYPE.ROW,
                },
            };

            expect(await commandService.executeCommand(MoveRowsCommand.id, params)).toBeTruthy();
            const values = getValues(9, 9, 9, 9, 'sheet2');
            expect(values).toStrictEqual([[{ f: '=SUM(A2:C3)' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo2 = getValues(9, 9, 9, 9, 'sheet2');
            expect(valuesUndo2).toStrictEqual([[{ f: '=SUM(A2:C4)' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(9, 9, 9, 9, 'sheet2');
            expect(valuesRedo).toStrictEqual([[{ f: '=SUM(A2:C3)' }]]);
        });

        it('Move columns, update reference', async () => {
            const selectionManager = get(SheetsSelectionsService);

            // A1
            selectionManager.addSelections([
                {
                    range: { startRow: 0, startColumn: 0, endRow: 0, endColumn: 0, rangeType: RANGE_TYPE.COLUMN },
                    primary: null,
                    style: null,
                },
            ]);

            const params: IMoveColsCommandParams = {
                fromRange: {
                    startRow: 0,
                    startColumn: 0,
                    endRow: 999,
                    endColumn: 0,
                    rangeType: 2,
                },
                toRange: {
                    startRow: 0,
                    startColumn: 4,
                    endRow: 999,
                    endColumn: 4,
                    rangeType: 2,
                },
            };

            expect(await commandService.executeCommand(MoveColsCommand.id, params)).toBeTruthy();
            const values = getValues(0, 6, 0, 6);
            expect(values).toStrictEqual([[{ f: '=A1:A2' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(0, 6, 0, 6);
            expect(valuesUndo).toStrictEqual([[{ f: '=A1:B2' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(0, 6, 0, 6);
            expect(valuesRedo).toStrictEqual([[{ f: '=A1:A2' }]]);
        });

        it('Move columns, update reference and position', async () => {
            const selectionManager = get(SheetsSelectionsService);

            // A1
            selectionManager.addSelections([
                {
                    range: { startRow: 0, startColumn: 1, endRow: 0, endColumn: 1, rangeType: RANGE_TYPE.COLUMN },
                    primary: null,
                    style: null,
                },
            ]);

            const params: IMoveColsCommandParams = {
                fromRange: {
                    startRow: 0,
                    startColumn: 1,
                    endRow: 999,
                    endColumn: 1,
                    rangeType: 2,
                },
                toRange: {
                    startRow: 0,
                    startColumn: 9,
                    endRow: 999,
                    endColumn: 9,
                    rangeType: 2,
                },
            };

            expect(await commandService.executeCommand(MoveColsCommand.id, params)).toBeTruthy();
            const values = getValues(2, 1, 2, 2);
            expect(values).toStrictEqual([[{ f: '=A1:I2' }, null]]);
            const values2 = getValues(5, 1, 5, 2);
            expect(values2).toStrictEqual([[{ f: '=SUM(A1:I2)' }, { v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(2, 1, 2, 2);
            expect(valuesUndo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }, { f: '=A1:B2' }]]);
            const valuesUndo2 = getValues(5, 2, 5, 3);
            expect(valuesUndo2).toStrictEqual([[{ f: '=SUM(A1:B2)' }, { v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(2, 1, 2, 2);
            expect(valuesRedo).toStrictEqual([[{ f: '=A1:I2' }, null]]);
            const valuesRedo2 = getValues(5, 1, 5, 2);
            expect(valuesRedo2).toStrictEqual([[{ f: '=SUM(A1:I2)' }, { v: 1, t: CellValueType.NUMBER }]]);
        });

        it('Move columns, reduce scope', async () => {
            const workbook = get(IUniverInstanceService).getUnit<Workbook>('test');
            const sheet2 = workbook?.getSheetBySheetId('sheet2');
            if (!sheet2) {
                throw new Error('sheet2 not found');
            }
            workbook?.setActiveSheet(sheet2);

            const selectionManager = get(SheetsSelectionsService);
            // A1
            selectionManager.addSelections([
                {
                    range: { startRow: 0, startColumn: 1, endRow: 999, endColumn: 1, rangeType: RANGE_TYPE.COLUMN },
                    primary: null,
                    style: null,
                },
            ]);

            const params: IMoveRowsCommandParams = {
                fromRange: {
                    startRow: 0,
                    startColumn: 1,
                    endRow: 999,
                    endColumn: 1,
                    rangeType: RANGE_TYPE.COLUMN,
                },
                toRange: {
                    startRow: 0,
                    startColumn: 7,
                    endRow: 999,
                    endColumn: 7,
                    rangeType: RANGE_TYPE.COLUMN,
                },
            };

            expect(await commandService.executeCommand(MoveColsCommand.id, params)).toBeTruthy();
            const values = getValues(9, 9, 9, 9, 'sheet2');
            expect(values).toStrictEqual([[{ f: '=SUM(A2:B4)' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo2 = getValues(9, 9, 9, 9, 'sheet2');
            expect(valuesUndo2).toStrictEqual([[{ f: '=SUM(A2:C4)' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(9, 9, 9, 9, 'sheet2');
            expect(valuesRedo).toStrictEqual([[{ f: '=SUM(A2:B4)' }]]);
        });

        it('Insert row, update reference', async () => {
            const params: IInsertRowCommandParams = {
                unitId: 'test',
                subUnitId: 'sheet1',
                range: {
                    startRow: 1,
                    endRow: 1,
                    startColumn: 0,
                    endColumn: 19,
                },
                direction: Direction.UP,
            };

            expect(await commandService.executeCommand(InsertRowCommand.id, params)).toBeTruthy();
            const values = getValues(0, 6, 0, 6);
            expect(values).toStrictEqual([[{ f: '=A1:B3' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(0, 6, 0, 6);
            expect(valuesUndo).toStrictEqual([[{ f: '=A1:B2' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(0, 6, 0, 6);
            expect(valuesRedo).toStrictEqual([[{ f: '=A1:B3' }]]);
        });

        it('Insert row, update reference and position', async () => {
            const params: IInsertRowCommandParams = {
                unitId: 'test',
                subUnitId: 'sheet1',
                range: {
                    startRow: 1,
                    endRow: 1,
                    startColumn: 0,
                    endColumn: 19,
                },
                direction: Direction.UP,
            };

            expect(await commandService.executeCommand(InsertRowCommand.id, params)).toBeTruthy();
            const values = getValues(2, 2, 3, 2);
            expect(values).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }], [{ f: '=A1:B3' }]]);
            const values2 = getValues(6, 2, 7, 2);
            expect(values2).toStrictEqual([[{ f: '=SUM(A1:B3)' }], [{ v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(1, 2, 2, 2);
            expect(valuesUndo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }], [{ f: '=A1:B2' }]]);
            const valuesUndo2 = getValues(5, 2, 6, 2);
            expect(valuesUndo2).toStrictEqual([[{ f: '=SUM(A1:B2)' }], [{ v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(2, 2, 3, 2);
            expect(valuesRedo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }], [{ f: '=A1:B3' }]]);
            const valuesRedo2 = getValues(6, 2, 7, 2);
            expect(valuesRedo2).toStrictEqual([[{ f: '=SUM(A1:B3)' }], [{ v: 1, t: CellValueType.NUMBER }]]);
        });

        it('Insert row, update reference and position, adjacent formula', async () => {
            const params: IInsertRowCommandParams = {
                unitId: 'test',
                subUnitId: 'sheet1',
                range: {
                    startRow: 8,
                    endRow: 8,
                    startColumn: 0,
                    endColumn: 19,
                },
                direction: Direction.UP,
            };

            expect(await commandService.executeCommand(InsertRowCommand.id, params)).toBeTruthy();
            const values = getValues(10, 0, 13, 0);
            expect(values).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }], [{ f: '=SUM(A8)' }], [{ f: '=SUM(A10)' }], [{ f: '=SUM(A11)' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(10, 0, 13, 0);
            expect(valuesUndo).toStrictEqual([[{ f: '=SUM(A8)' }], [{ f: '=SUM(A9)' }], [{ f: '=SUM(A10)' }], [null]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(10, 0, 13, 0);
            expect(valuesRedo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }], [{ f: '=SUM(A8)' }], [{ f: '=SUM(A10)' }], [{ f: '=SUM(A11)' }]]);
        });

        it('Insert row, update range of absolute reference type', async () => {
            const result = await commandService.executeCommand(InsertRowCommand.id, {
                unitId: 'test',
                subUnitId: 'sheet5',
                range: {
                    startRow: 0,
                    endRow: 0,
                    startColumn: 0,
                    endColumn: 19,
                },
                direction: Direction.UP,
            });
            expect(result).toBeTruthy();

            const cellB3 = getValues(2, 1, 2, 1, 'sheet5');
            expect(cellB3).toStrictEqual([
                [{ v: 5, t: CellValueType.NUMBER }],
            ]);
            const cellD3 = getValues(2, 3, 2, 3, 'sheet5');
            expect(cellD3).toStrictEqual([
                [{ f: '=$B3' }],
            ]);
            const cellE3 = getValues(2, 4, 2, 4, 'sheet5');
            expect(cellE3).toStrictEqual([
                [{ f: '=$B3' }],
            ]);
            const cellF3 = getValues(2, 5, 2, 5, 'sheet5');
            expect(cellF3).toStrictEqual([
                [{ f: '=$B3' }],
            ]);
            const cellG3 = getValues(2, 6, 2, 6, 'sheet5');
            expect(cellG3).toStrictEqual([
                [{ f: '=$B3' }],
            ]);
            const cellH3 = getValues(2, 7, 2, 7, 'sheet5');
            expect(cellH3).toStrictEqual([
                [{ f: '=$B3' }],
            ]);
            const cellI3 = getValues(2, 8, 2, 8, 'sheet5');
            expect(cellI3).toStrictEqual([
                [{ f: '=$B3' }],
            ]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();

            const cellB2 = getValues(1, 1, 1, 1, 'sheet5');
            expect(cellB2).toStrictEqual([
                [{ v: 5, t: CellValueType.NUMBER }],
            ]);
            const cellD2 = getValues(1, 3, 1, 3, 'sheet5');
            expect(cellD2).toStrictEqual([
                [{ f: '=$B2' }],
            ]);
            const cellE2 = getValues(1, 4, 1, 4, 'sheet5');
            expect(cellE2).toStrictEqual([
                [{ f: '=$B2', si: 'MFYkBI' }],
            ]);
            const cellF2 = getValues(1, 5, 1, 5, 'sheet5');
            expect(cellF2).toStrictEqual([
                [{ si: 'MFYkBI' }],
            ]);
            const cellG2 = getValues(1, 6, 1, 6, 'sheet5');
            expect(cellG2).toStrictEqual([
                [{ si: 'MFYkBI' }],
            ]);
            const cellH2 = getValues(1, 7, 1, 7, 'sheet5');
            expect(cellH2).toStrictEqual([
                [{ si: 'MFYkBI' }],
            ]);
            const cellI2 = getValues(1, 8, 1, 8, 'sheet5');
            expect(cellI2).toStrictEqual([
                [{ si: 'MFYkBI' }],
            ]);
        });

        it('Insert column, update reference', async () => {
            const params: IInsertColCommandParams = {
                unitId: 'test',
                subUnitId: 'sheet1',
                range: {
                    startColumn: 1,
                    endColumn: 1,
                    startRow: 0,
                    endRow: 14,
                },
                direction: Direction.LEFT,
                cellValue: {},
            };

            expect(await commandService.executeCommand(InsertColCommand.id, params)).toBeTruthy();
            const values = getValues(14, 0, 14, 0);
            expect(values).toStrictEqual([[{ f: '=A1:C2' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(14, 0, 14, 0);
            expect(valuesUndo).toStrictEqual([[{ f: '=A1:B2' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(14, 0, 14, 0);
            expect(valuesRedo).toStrictEqual([[{ f: '=A1:C2' }]]);
        });

        it('Insert column, update reference and position', async () => {
            const params: IInsertColCommandParams = {
                unitId: 'test',
                subUnitId: 'sheet1',
                range: {
                    startColumn: 1,
                    endColumn: 1,
                    startRow: 0,
                    endRow: 14,
                },
                direction: Direction.LEFT,
                cellValue: {},
            };

            expect(await commandService.executeCommand(InsertColCommand.id, params)).toBeTruthy();
            const values = getValues(2, 2, 2, 3);
            expect(values).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }, { f: '=A1:C2' }]]);
            const values2 = getValues(5, 3, 5, 4);
            expect(values2).toStrictEqual([[{ f: '=SUM(A1:C2)' }, { v: 1, t: CellValueType.NUMBER }]]);
            const values3 = getValues(7, 2, 7, 6);
            expect(values3).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }, { v: 1, t: CellValueType.NUMBER }, { f: '=SUM(A8)' }, { f: '=SUM(C8)' }, { f: '=SUM(D8)' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(2, 1, 2, 2);
            expect(valuesUndo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }, { f: '=A1:B2' }]]);
            const valuesUndo2 = getValues(5, 2, 5, 3);
            expect(valuesUndo2).toStrictEqual([[{ f: '=SUM(A1:B2)' }, { v: 1, t: CellValueType.NUMBER }]]);
            const valuesUndo3 = getValues(7, 2, 7, 6);
            expect(valuesUndo3).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }, { f: '=SUM(A8)' }, { f: '=SUM(B8)' }, { f: '=SUM(C8)' }, null]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(2, 2, 2, 3);
            expect(valuesRedo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }, { f: '=A1:C2' }]]);
            const valuesRedo2 = getValues(5, 3, 5, 4);
            expect(valuesRedo2).toStrictEqual([[{ f: '=SUM(A1:C2)' }, { v: 1, t: CellValueType.NUMBER }]]);
            const valuesRedo3 = getValues(7, 2, 7, 6);
            expect(valuesRedo3).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }, { v: 1, t: CellValueType.NUMBER }, { f: '=SUM(A8)' }, { f: '=SUM(C8)' }, { f: '=SUM(D8)' }]]);
        });

        it('Remove row, update reference', async () => {
            const params: IRemoveRowColCommandParams = {
                range: {
                    startRow: 1,
                    endRow: 1,
                    startColumn: 0,
                    endColumn: 19,
                },
            };

            expect(await commandService.executeCommand(RemoveRowCommand.id, params)).toBeTruthy();
            const values = getValues(0, 6, 0, 6);
            expect(values).toStrictEqual([[{ f: '=A1:B1' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(0, 6, 0, 6);
            expect(valuesUndo).toStrictEqual([[{ f: '=A1:B2' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(0, 6, 0, 6);
            expect(valuesRedo).toStrictEqual([[{ f: '=A1:B1' }]]);
        });

        it('Remove rows on non-active sheet, update reference and position', async () => {
            const workbook = get(IUniverInstanceService).getUnit<Workbook>('test');
            if (!workbook) {
                throw new Error('workbook not found');
            }
            const sheet2 = workbook.getSheetBySheetId('sheet2');
            if (!sheet2) {
                throw new Error('sheet2 not found');
            }
            workbook.setActiveSheet(sheet2);

            const params = {
                unitId: 'test',
                subUnitId: 'sheet1',
                range: {
                    startRow: 1,
                    endRow: 1,
                    startColumn: 0,
                    endColumn: 19,
                },
            };

            expect(commandService.syncExecuteCommand(RemoveRowByRangeCommand.id, params)).toBeTruthy();
            const values = getValues(1, 2, 2, 2);
            expect(values).toStrictEqual([[{ f: '=A1:B1' }], [null]]);
            const values2 = getValues(4, 2, 5, 2);
            expect(values2).toStrictEqual([[{ f: '=SUM(A1:B1)' }], [{ v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(1, 2, 2, 2);
            expect(valuesUndo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }], [{ f: '=A1:B2' }]]);
            const valuesUndo2 = getValues(5, 2, 6, 2);
            expect(valuesUndo2).toStrictEqual([[{ f: '=SUM(A1:B2)' }], [{ v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(1, 2, 2, 2);
            expect(valuesRedo).toStrictEqual([[{ f: '=A1:B1' }], [null]]);
            const valuesRedo2 = getValues(4, 2, 5, 2);
            expect(valuesRedo2).toStrictEqual([[{ f: '=SUM(A1:B1)' }], [{ v: 1, t: CellValueType.NUMBER }]]);
        });

        it('Remove row, update reference and position, adjacent formula', async () => {
            const params: IRemoveRowColCommandParams = {
                range: {
                    startRow: 8,
                    endRow: 8,
                    startColumn: 0,
                    endColumn: 19,
                },
            };

            expect(await commandService.executeCommand(RemoveRowCommand.id, params)).toBeTruthy();
            const values = getValues(9, 0, 12, 0);
            expect(values).toStrictEqual([[{ f: '=SUM(A8)' }], [{ f: '=SUM(#REF!)' }], [{ f: '=SUM(A9)' }], [null]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(9, 0, 12, 0);
            expect(valuesUndo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }], [{ f: '=SUM(A8)' }], [{ f: '=SUM(A9)' }], [{ f: '=SUM(A10)' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(9, 0, 12, 0);
            expect(valuesRedo).toStrictEqual([[{ f: '=SUM(A8)' }], [{ f: '=SUM(#REF!)' }], [{ f: '=SUM(A9)' }], [null]]);
        });

        it('Remove row, removed row contains formula', async () => {
            const params: IRemoveRowColCommandParams = {
                range: {
                    startRow: 10,
                    endRow: 10,
                    startColumn: 0,
                    endColumn: 19,
                },
            };

            expect(await commandService.executeCommand(RemoveRowCommand.id, params)).toBeTruthy();
            const values = getValues(10, 0, 10, 0);
            expect(values).toStrictEqual([[{ f: '=SUM(A9)' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(10, 0, 10, 0);
            expect(valuesUndo).toStrictEqual([[{ f: '=SUM(A8)' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(10, 0, 10, 0);
            expect(valuesRedo).toStrictEqual([[{ f: '=SUM(A9)' }]]);
        });

        it('Remove row, changes to other sheet reference ranges', async () => {
            const params: IRemoveRowColCommandParams = {
                range: {
                    startRow: 1,
                    endRow: 1,
                    startColumn: 0,
                    endColumn: 19,
                },
            };

            expect(await commandService.executeCommand(RemoveRowCommand.id, params)).toBeTruthy();

            const values1 = getValues(1, 0, 2, 0, 'sheet2');
            expect(values1).toStrictEqual([[null], [{ f: '=SUM(DefinedName1)' }]]);
            const values2 = getValues(8, 0, 9, 0, 'sheet2');
            expect(values2).toStrictEqual([[null], [{ f: '=SUM(Sheet1!A1:A2)' }]]);
            const values3 = getValues(8, 9, 9, 9, 'sheet2');
            expect(values3).toStrictEqual([[null], [{ f: '=SUM(A2:C4)' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo1 = getValues(1, 0, 2, 0, 'sheet2');
            expect(valuesUndo1).toStrictEqual([[null], [{ f: '=SUM(DefinedName1)' }]]);
            const valuesUndo2 = getValues(8, 0, 9, 0, 'sheet2');
            expect(valuesUndo2).toStrictEqual([[null], [{ f: '=SUM(Sheet1!A1:A3)' }]]);
            const valuesUndo3 = getValues(8, 9, 9, 9, 'sheet2');
            expect(valuesUndo3).toStrictEqual([[null], [{ f: '=SUM(A2:C4)' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo1 = getValues(1, 0, 2, 0, 'sheet2');
            expect(valuesRedo1).toStrictEqual([[null], [{ f: '=SUM(DefinedName1)' }]]);
            const valuesRedo2 = getValues(8, 0, 9, 0, 'sheet2');
            expect(valuesRedo2).toStrictEqual([[null], [{ f: '=SUM(Sheet1!A1:A2)' }]]);
            const valuesRedo3 = getValues(8, 9, 9, 9, 'sheet2');
            expect(valuesRedo3).toStrictEqual([[null], [{ f: '=SUM(A2:C4)' }]]);
        });

        it('Remove column, update reference', async () => {
            const params: IRemoveRowColCommandParams = {
                range: {
                    startColumn: 1,
                    endColumn: 1,
                    startRow: 0,
                    endRow: 2,
                },
            };

            expect(await commandService.executeCommand(RemoveColCommand.id, params)).toBeTruthy();
            const values = getValues(14, 0, 14, 0);
            expect(values).toStrictEqual([[{ f: '=A1:A2' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(14, 0, 14, 0);
            expect(valuesUndo).toStrictEqual([[{ f: '=A1:B2' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(14, 0, 14, 0);
            expect(valuesRedo).toStrictEqual([[{ f: '=A1:A2' }]]);
        });

        it('Remove columns on non-active sheet, update reference and position', async () => {
            const workbook = get(IUniverInstanceService).getUnit<Workbook>('test');
            if (!workbook) {
                throw new Error('workbook not found');
            }
            const sheet1 = workbook.getSheetBySheetId('sheet1');
            const sheet2 = workbook.getSheetBySheetId('sheet2');
            if (!sheet1 || !sheet2) {
                throw new Error('sheet not found');
            }
            workbook.setActiveSheet(sheet2);

            const params = {
                unitId: 'test',
                subUnitId: 'sheet1',
                range: {
                    startColumn: 1,
                    endColumn: 1,
                    startRow: 0,
                    endRow: sheet1.getRowCount() - 1,
                },
            };

            expect(commandService.syncExecuteCommand(RemoveColByRangeCommand.id, params)).toBeTruthy();
            const values = getValues(2, 1, 2, 2);
            expect(values).toStrictEqual([[{ f: '=A1:A2' }, null]]);
            const values2 = getValues(5, 1, 5, 2);
            expect(values2).toStrictEqual([[{ f: '=SUM(A1:A2)' }, { v: 1, t: CellValueType.NUMBER }]]);
            const values3 = getValues(7, 2, 7, 5);
            expect(values3).toStrictEqual([[{ f: '=SUM(A8)' }, { f: '=SUM(#REF!)' }, { f: '=SUM(B8)' }, null]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(2, 1, 2, 2);
            expect(valuesUndo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }, { f: '=A1:B2' }]]);
            const valuesUndo2 = getValues(5, 2, 5, 3);
            expect(valuesUndo2).toStrictEqual([[{ f: '=SUM(A1:B2)' }, { v: 1, t: CellValueType.NUMBER }]]);
            const valuesUndo3 = getValues(7, 2, 7, 5);
            expect(valuesUndo3).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }, { f: '=SUM(A8)' }, { f: '=SUM(B8)' }, { f: '=SUM(C8)' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(2, 1, 2, 2);
            expect(valuesRedo).toStrictEqual([[{ f: '=A1:A2' }, null]]);
            const valuesRedo2 = getValues(5, 1, 5, 2);
            expect(valuesRedo2).toStrictEqual([[{ f: '=SUM(A1:A2)' }, { v: 1, t: CellValueType.NUMBER }]]);
            const valuesRedo3 = getValues(7, 2, 7, 5);
            expect(valuesRedo3).toStrictEqual([[{ f: '=SUM(A8)' }, { f: '=SUM(#REF!)' }, { f: '=SUM(B8)' }, null]]);
        });

        it('Remove column, update reference and position, contains #REF', async () => {
            const params: IRemoveRowColCommandParams = {
                range: {
                    startColumn: 0,
                    endColumn: 1,
                    startRow: 0,
                    endRow: 2,
                },
            };

            expect(await commandService.executeCommand(RemoveColCommand.id, params)).toBeTruthy();
            const values = getValues(21, 0, 21, 4);

            // Ignore the calculation results and only verify the offset information
            expect(values).toStrictEqual([
                [{ f: '=OFFSET(#REF!,1,1)' }, { f: '=OFFSET(#REF!,1,1)' }, { f: '=OFFSET(A1,1,1)' }, null, null],
            ]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(21, 0, 21, 4);
            expect(valuesUndo).toStrictEqual([
                [null, null, { f: '=OFFSET(A1,1,1)' }, { f: '=OFFSET(B1,1,1)', si: 'id2' }, { si: 'id2' }],
            ]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(21, 0, 21, 4);
            expect(valuesRedo).toStrictEqual([
                [{ f: '=OFFSET(#REF!,1,1)' }, { f: '=OFFSET(#REF!,1,1)' }, { f: '=OFFSET(A1,1,1)' }, null, null],
            ]);
        });

        it('Remove column, removed column contains formula', async () => {
            const params: IRemoveRowColCommandParams = {
                range: {
                    startColumn: 3,
                    endColumn: 3,
                    startRow: 0,
                    endRow: 2,
                },
            };

            expect(await commandService.executeCommand(RemoveColCommand.id, params)).toBeTruthy();
            const values = getValues(7, 3, 7, 3);
            expect(values).toStrictEqual([[{ f: '=SUM(B8)' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(7, 3, 7, 3);
            expect(valuesUndo).toStrictEqual([[{ f: '=SUM(A8)' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(7, 3, 7, 3);
            expect(valuesRedo).toStrictEqual([[{ f: '=SUM(B8)' }]]);
        });

        it('Delete move left, value on the left', async () => {
            const params: IDeleteRangeMoveLeftCommandParams = {
                range: {
                    startRow: 2,
                    startColumn: 1,
                    endRow: 2,
                    endColumn: 1,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(DeleteRangeMoveLeftCommand.id, params)).toBeTruthy();
            const values = getValues(2, 1, 2, 2);
            expect(values).toStrictEqual([[{ f: '=A1:B2' }, null]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(2, 1, 2, 2);
            expect(valuesUndo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }, { f: '=A1:B2' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(2, 1, 2, 2);
            expect(valuesRedo).toStrictEqual([[{ f: '=A1:B2' }, null]]);
        });

        it('Delete move left, value on the right', async () => {
            const params: IDeleteRangeMoveLeftCommandParams = {
                range: {
                    startRow: 5,
                    startColumn: 1,
                    endRow: 5,
                    endColumn: 1,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(DeleteRangeMoveLeftCommand.id, params)).toBeTruthy();
            const values = getValues(5, 1, 5, 2);
            expect(values).toStrictEqual([[{ f: '=SUM(A1:B2)' }, { v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(5, 2, 5, 3);
            expect(valuesUndo).toStrictEqual([[{ f: '=SUM(A1:B2)' }, { v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(5, 1, 5, 2);
            expect(valuesRedo).toStrictEqual([[{ f: '=SUM(A1:B2)' }, { v: 1, t: CellValueType.NUMBER }]]);
        });

        it('Delete move left, update reference', async () => {
            const params: IDeleteRangeMoveLeftCommandParams = {
                range: {
                    startRow: 0,
                    startColumn: 1,
                    endRow: 1,
                    endColumn: 1,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(DeleteRangeMoveLeftCommand.id, params)).toBeTruthy();
            const values = getValues(14, 0, 14, 0);
            expect(values).toStrictEqual([[{ f: '=A1:A2' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(14, 0, 14, 0);
            expect(valuesUndo).toStrictEqual([[{ f: '=A1:B2' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(14, 0, 14, 0);
            expect(valuesRedo).toStrictEqual([[{ f: '=A1:A2' }]]);
        });

        it('Delete move up, value on the top', async () => {
            const params: IDeleteRangeMoveUpCommandParams = {
                range: {
                    startRow: 1,
                    startColumn: 2,
                    endRow: 1,
                    endColumn: 2,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(DeleteRangeMoveUpCommand.id, params)).toBeTruthy();
            const values = getValues(1, 2, 2, 2);
            expect(values).toStrictEqual([[{ f: '=A1:B2' }], [null]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(1, 2, 2, 2);
            expect(valuesUndo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }], [{ f: '=A1:B2' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(1, 2, 2, 2);
            expect(valuesRedo).toStrictEqual([[{ f: '=A1:B2' }], [null]]);
        });

        it('Delete move up, value on the bottom', async () => {
            const params: IDeleteRangeMoveUpCommandParams = {
                range: {
                    startRow: 4,
                    startColumn: 2,
                    endRow: 4,
                    endColumn: 2,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(DeleteRangeMoveUpCommand.id, params)).toBeTruthy();
            const values = getValues(4, 2, 5, 2);
            expect(values).toStrictEqual([[{ f: '=SUM(A1:B2)' }], [{ v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(5, 2, 6, 2);
            expect(valuesUndo).toStrictEqual([[{ f: '=SUM(A1:B2)' }], [{ v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(4, 2, 5, 2);
            expect(valuesRedo).toStrictEqual([[{ f: '=SUM(A1:B2)' }], [{ v: 1, t: CellValueType.NUMBER }]]);
        });

        it('Delete move up, update reference', async () => {
            const params: IDeleteRangeMoveUpCommandParams = {
                range: {
                    startRow: 1,
                    startColumn: 0,
                    endRow: 1,
                    endColumn: 1,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(DeleteRangeMoveUpCommand.id, params)).toBeTruthy();
            const values = getValues(0, 6, 0, 6);
            expect(values).toStrictEqual([[{ f: '=A1:B1' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(0, 6, 0, 6);
            expect(valuesUndo).toStrictEqual([[{ f: '=A1:B2' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(0, 6, 0, 6);
            expect(valuesRedo).toStrictEqual([[{ f: '=A1:B1' }]]);
        });

        it('Insert move down, value on the top', async () => {
            const params: IInsertRangeMoveDownCommandParams = {
                range: {
                    startRow: 1,
                    startColumn: 2,
                    endRow: 1,
                    endColumn: 2,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(InsertRangeMoveDownCommand.id, params)).toBeTruthy();
            const values = getValues(2, 2, 3, 2);
            expect(values).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }], [{ f: '=A1:B2' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(1, 2, 2, 2);
            expect(valuesUndo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }], [{ f: '=A1:B2' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(2, 2, 3, 2);
            expect(valuesRedo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }], [{ f: '=A1:B2' }]]);
        });

        it('Insert move down, value on the bottom', async () => {
            const params: IInsertRangeMoveDownCommandParams = {
                range: {
                    startRow: 4,
                    startColumn: 2,
                    endRow: 4,
                    endColumn: 2,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(InsertRangeMoveDownCommand.id, params)).toBeTruthy();
            const values = getValues(6, 2, 7, 2);
            expect(values).toStrictEqual([[{ f: '=SUM(A1:B2)' }], [{ v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(5, 2, 6, 2);
            expect(valuesUndo).toStrictEqual([[{ f: '=SUM(A1:B2)' }], [{ v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(6, 2, 7, 2);
            expect(valuesRedo).toStrictEqual([[{ f: '=SUM(A1:B2)' }], [{ v: 1, t: CellValueType.NUMBER }]]);
        });

        it('Insert move down, update reference', async () => {
            const params: IInsertRangeMoveDownCommandParams = {
                range: {
                    startRow: 1,
                    startColumn: 0,
                    endRow: 1,
                    endColumn: 1,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(InsertRangeMoveDownCommand.id, params)).toBeTruthy();
            const values = getValues(0, 6, 0, 6);
            expect(values).toStrictEqual([[{ f: '=A1:B3' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(0, 6, 0, 6);
            expect(valuesUndo).toStrictEqual([[{ f: '=A1:B2' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(0, 6, 0, 6);
            expect(valuesRedo).toStrictEqual([[{ f: '=A1:B3' }]]);
        });

        it('Insert move right, value on the left', async () => {
            const params: IInsertRangeMoveRightCommandParams = {
                range: {
                    startRow: 2,
                    startColumn: 1,
                    endRow: 2,
                    endColumn: 1,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(InsertRangeMoveRightCommand.id, params)).toBeTruthy();
            const values = getValues(2, 2, 2, 3);
            expect(values).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }, { f: '=A1:B2' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(2, 1, 2, 2);
            expect(valuesUndo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }, { f: '=A1:B2' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(2, 2, 2, 3);
            expect(valuesRedo).toStrictEqual([[{ v: 1, t: CellValueType.NUMBER }, { f: '=A1:B2' }]]);
        });

        it('Insert move right, value on the right', async () => {
            const params: IInsertRangeMoveRightCommandParams = {
                range: {
                    startRow: 5,
                    startColumn: 1,
                    endRow: 5,
                    endColumn: 1,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(InsertRangeMoveRightCommand.id, params)).toBeTruthy();
            const values = getValues(5, 3, 5, 4);
            expect(values).toStrictEqual([[{ f: '=SUM(A1:B2)' }, { v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(5, 2, 5, 3);
            expect(valuesUndo).toStrictEqual([[{ f: '=SUM(A1:B2)' }, { v: 1, t: CellValueType.NUMBER }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(5, 3, 5, 4);
            expect(valuesRedo).toStrictEqual([[{ f: '=SUM(A1:B2)' }, { v: 1, t: CellValueType.NUMBER }]]);
        });

        it('Insert move right, update reference', async () => {
            const params: IInsertRangeMoveRightCommandParams = {
                range: {
                    startRow: 0,
                    startColumn: 1,
                    endRow: 1,
                    endColumn: 1,
                    rangeType: 0,
                },
            };

            expect(await commandService.executeCommand(InsertRangeMoveRightCommand.id, params)).toBeTruthy();
            const values = getValues(14, 0, 14, 0);
            expect(values).toStrictEqual([[{ f: '=A1:C2' }]]);

            expect(await commandService.executeCommand(UndoCommand.id)).toBeTruthy();
            const valuesUndo = getValues(14, 0, 14, 0);
            expect(valuesUndo).toStrictEqual([[{ f: '=A1:B2' }]]);

            expect(await commandService.executeCommand(RedoCommand.id)).toBeTruthy();
            const valuesRedo = getValues(14, 0, 14, 0);
            expect(valuesRedo).toStrictEqual([[{ f: '=A1:C2' }]]);
        });

        it('set worksheet name', async () => {
            const params: ISetWorksheetNameCommandParams = {
                subUnitId: 'sheet2',
                name: 'Sheet2Rename',
            };

            expect(await commandService.executeCommand(SetWorksheetNameCommand.id, params)).toBeTruthy();
            const values = getValues(14, 2, 14, 2);
            expect(values).toStrictEqual([[{ f: '=Sheet2Rename!A1:B2' }]]);
        });

        it('set defined name', async () => {
            const params: ISetDefinedNameMutationParam = {
                unitId: 'test',
                id: 'soAI3OK4sq',
                name: 'DefinedName2',
                formulaOrRefString: 'Sheet2!$A$1:$B$2',
                comment: '',
                localSheetId: 'AllDefaultWorkbook',
            };

            expect(await commandService.executeCommand(SetDefinedNameCommand.id, params)).toBeTruthy();
            const values = getValues(2, 0, 2, 0, 'sheet2');
            expect(values).toStrictEqual([[{ f: '=SUM(DefinedName2)' }]]);
        });

        it('remove defined name', async () => {
            const params: ISetDefinedNameMutationParam = {
                unitId: 'test',
                id: 'soAI3OK4sq',
                name: 'DefinedName1',
                formulaOrRefString: 'Sheet2!$A$1:$B$2',
                comment: '',
                localSheetId: 'AllDefaultWorkbook',
            };

            expect(await commandService.executeCommand(RemoveDefinedNameCommand.id, params)).toBeTruthy();
            const values = getValues(2, 0, 2, 0, 'sheet2');
            expect(values).toStrictEqual([[{ f: '=SUM(#REF!)' }]]);
        });

        it('remove worksheet', async () => {
            const params: IRemoveSheetCommandParams = {
                unitId: 'test',
                subUnitId: 'sheet2',
            };

            expect(await commandService.executeCommand(RemoveSheetCommand.id, params)).toBeTruthy();
            const values = getValues(14, 2, 14, 2);
            expect(values).toStrictEqual([[{ f: '=#REF!' }]]);
        });

        it('set range values, set new formula string and formula id', async () => {
            const params: ISetRangeValuesCommandParams = {
                range: {
                    startRow: 7,
                    startColumn: 8,
                    endRow: 7,
                    endColumn: 8,
                },
                value: {
                    f: '=A1',
                    si: 'qwerty',
                },
            };

            expect(await commandService.executeCommand(SetRangeValuesCommand.id, params)).toBeTruthy();
            const values = getValues(7, 8, 7, 9);
            expect(values).toStrictEqual([[{ f: '=A1', si: 'qwerty' }, { f: '=SUM(C8:E10)', si: 'CarNau' }]]);
        });
        it('set range values, set same formula string and formula id', async () => {
            const params: ISetRangeValuesCommandParams = {
                range: {
                    startRow: 7,
                    startColumn: 8,
                    endRow: 7,
                    endColumn: 8,
                },
                value: {
                    f: '=SUM(B8:D10)',
                    si: 'CarNau',
                },
            };

            expect(await commandService.executeCommand(SetRangeValuesCommand.id, params)).toBeTruthy();
            const values = getValues(7, 8, 7, 9);
            expect(values).toStrictEqual([[{ f: '=SUM(B8:D10)', si: 'CarNau' }, { si: 'CarNau' }]]);
        });
        it('set range values, set same formula id', async () => {
            const params: ISetRangeValuesCommandParams = {
                range: {
                    startRow: 7,
                    startColumn: 9,
                    endRow: 7,
                    endColumn: 9,
                },
                value: {
                    si: 'CarNau',
                },
            };

            expect(await commandService.executeCommand(SetRangeValuesCommand.id, params)).toBeTruthy();
            const values = getValues(7, 8, 7, 9);
            expect(values).toStrictEqual([[{ f: '=SUM(B8:D10)', si: 'CarNau' }, { si: 'CarNau' }]]);
        });
        it('set range values, set new formula string', async () => {
            const params: ISetRangeValuesCommandParams = {
                range: {
                    startRow: 7,
                    startColumn: 8,
                    endRow: 7,
                    endColumn: 8,
                },
                value: {
                    f: '=A1',
                    si: null,
                },
            };

            expect(await commandService.executeCommand(SetRangeValuesCommand.id, params)).toBeTruthy();
            const values = getValues(7, 8, 7, 9);
            expect(values).toStrictEqual([[{ f: '=A1' }, { f: '=SUM(C8:E10)', si: 'CarNau' }]]);
        });

        it('set range values, set new formula id', async () => {
            const params: ISetRangeValuesCommandParams = {
                range: {
                    startRow: 7,
                    startColumn: 8,
                    endRow: 7,
                    endColumn: 8,
                },
                value: {
                    f: null,
                    si: 'y0gLJX',
                },
            };

            expect(await commandService.executeCommand(SetRangeValuesCommand.id, params)).toBeTruthy();
            const values = getValues(7, 8, 7, 9);
            expect(values).toStrictEqual([[{ si: 'y0gLJX' }, { f: '=SUM(C8:E10)', si: 'CarNau' }]]);
        });

        it('set range values, remove formula string and id', async () => {
            const params: ISetRangeValuesCommandParams = {
                range: {
                    startRow: 7,
                    startColumn: 8,
                    endRow: 7,
                    endColumn: 8,
                },
                value: {
                    f: null,
                    si: null,
                },
            };

            expect(await commandService.executeCommand(SetRangeValuesCommand.id, params)).toBeTruthy();
            const values = getValues(7, 8, 7, 9);
            expect(values).toStrictEqual([[null, { f: '=SUM(C8:E10)', si: 'CarNau' }]]);
        });

        it('hide row and show row', async () => {
            const params: ISetRowHiddenCommandParams = {
                unitId: 'test',
                subUnitId: 'sheet3',
                ranges: [{
                    startRow: 2,
                    startColumn: 0,
                    endRow: 2,
                    endColumn: 19,
                    rangeType: RANGE_TYPE.ROW,
                }],
            };

            expect(await commandService.executeCommand(SetRowHiddenCommand.id, params)).toBeTruthy();
            const values = getValues(5, 0, 5, 2, 'sheet3');

            // No change in the formula
            expect(values?.[0][0]?.f).toStrictEqual('=SUBTOTAL(109,A1:A5)');
            expect(values?.[0][1]?.f).toStrictEqual('=SUBTOTAL(109,A3)');
            expect(values?.[0][2]?.f).toStrictEqual('=A3');
        });
    });
});