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.

45.2 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, IRange, Nullable, Workbook } from '@univerjs/core';
import type {
    IArrayFormulaEmbeddedMap,
    IArrayFormulaRangeType,
    IFeatureDirtyRangeType,
    IFormulaData,
    IFormulaDataItem,
    IRuntimeImageFormulaDataType,
    IRuntimeOtherUnitDataType,
    IRuntimeUnitDataType,
} from '../basics/common';
import type { BaseAstNode } from '../engine/ast-node/base-ast-node';
import type { IFormulaDependencyTreeJson } from '../engine/dependency/dependency-tree';
import type { BaseReferenceObject, FunctionVariantType } from '../engine/reference-object/base-reference-object';
import type { ArrayValueObject } from '../engine/value-object/array-value-object';
import type { BaseValueObject } from '../engine/value-object/base-value-object';
import type { StringValueObject } from '../engine/value-object/primitive-object';
import type { FormulaCalculationCache } from './formula-cache.service';
import {
    CellValueType,
    createIdentifier,
    Disposable,
    Inject,
    isNullCell,
    isRealNum,
    IUniverInstanceService,
    ObjectMatrix,
    UniverInstanceType,
} from '@univerjs/core';
import { isInDirtyRange } from '../basics/dirty';
import { ErrorType } from '../basics/error-type';
import { getRuntimeFeatureCell } from '../engine/utils/get-runtime-feature-cell';
import { clearNumberFormatTypeCache, clearStringToNumberPatternCache } from '../engine/utils/numfmt-kit';
import { clearReferenceToRangeCache } from '../engine/utils/reference-cache';
import { objectValueToCellValue } from '../engine/utils/value-object';
import { ErrorValueObject } from '../engine/value-object/base-value-object';
import { NumberValueObject } from '../engine/value-object/primitive-object';
import { hasDeferredFormulaDataSnapshot, IFormulaCurrentConfigService } from './current-data.service';
import { FormulaCacheService } from './formula-cache.service';
import { IHyperlinkEngineFormulaService } from './hyperlink-engine-formula.service';

/**
 * The formula engine has a lot of stages. IDLE and CALCULATION_COMPLETED can be considered as
 * the computing has completed.
 */
export enum FormulaExecuteStageType {
    IDLE,
    START,
    START_DEPENDENCY,
    START_CALCULATION,
    CURRENTLY_CALCULATING,
    START_DEPENDENCY_ARRAY_FORMULA,
    START_CALCULATION_ARRAY_FORMULA,
    CURRENTLY_CALCULATING_ARRAY_FORMULA,
    CALCULATION_COMPLETED,
}

export enum FormulaExecutedStateType {
    INITIAL,
    STOP_EXECUTION,
    NOT_EXECUTED,
    SUCCESS,
}

export interface ICircularReference {
    unitId: string;
    sheetId: string;
    row: number;
    column: number;
    groupId: string;
    iterations: number;
    converged?: boolean;
}

export interface IAllRuntimeData {
    circularReferences?: ICircularReference[];
    unitData: IRuntimeUnitDataType;
    sourceFormulaData?: IFormulaData;
    arrayFormulaRange: IArrayFormulaRangeType;
    arrayFormulaEmbedded: IArrayFormulaEmbeddedMap;
    unitOtherData: IRuntimeOtherUnitDataType;
    functionsExecutedState: FormulaExecutedStateType;
    arrayFormulaCellData: IRuntimeUnitDataType;
    clearArrayFormulaCellData: IRuntimeUnitDataType;
    imageFormulaData: IRuntimeImageFormulaDataType[];

    runtimeFeatureRange: { [featureId: string]: IFeatureDirtyRangeType };
    runtimeFeatureCellData: { [featureId: string]: IRuntimeUnitDataType };

    dependencyTreeModelData: IFormulaDependencyTreeJson[];
}

export interface IExecutionInProgressParams {
    totalFormulasToCalculate: number;
    completedFormulasCount: number;

    totalArrayFormulasToCalculate: number;
    completedArrayFormulasCount: number;

    circularReferences?: ICircularReference[];
    formulaCycleIndex: number;

    stage: FormulaExecuteStageType;
}

export interface IFormulaRuntimeService {
    currentRow: number;

    currentColumn: number;

    currentRowCount: number;

    currentColumnCount: number;

    currentSubUnitId: string;

    currentUnitId: string;

    dispose(): void;

    reset(): void;
    /** Custom runtimes without cache hooks continue to compute uncached values. */
    getCalculationCache?(): FormulaCalculationCache | undefined;
    clearInvertedIndexCache?(): void;
    releaseCalculationCache?(): void;
    /** Start empty when the engine will supply the complete current cycle set. */
    beginCircularReferences(clearPrevious?: boolean): void;
    clearCircularReferenceCells(cells: { unitId: string; subUnitId: string; row: number; column: number }[]): void;
    recordCircularReferences(cells: ICircularReference[]): void;
    completeCircularReferences(): void;
    preserveCurrentCellData(): void;

    setCurrent(
        row: number,
        column: number,
        rowCount: number,
        columnCount: number,
        sheetId: string,
        unitId: string
    ): void;

    setFunctionRefInfoOverride(rowCount: number, columnCount: number): () => void;

    hasFunctionRefInfoOverride(): boolean;

    registerFunctionDefinitionPrivacyVar(lambdaId: string, lambdaVar: Map<string, Nullable<BaseAstNode>>): void;

    getFunctionDefinitionPrivacyVar(lambdaId: string): Nullable<Map<string, Nullable<BaseAstNode>>>;

    markExternalReferenceUnavailable(): void;

    setRuntimeData(functionVariant: FunctionVariantType): void;

    getUnitData(): IRuntimeUnitDataType;

    getUnitArrayFormula(): IArrayFormulaRangeType;

    stopExecution(): void;

    setFormulaExecuteStage(type: FormulaExecuteStageType): void;

    setFormulaCycleIndex(index: number): void;

    isStopExecution(): boolean;

    getFormulaExecuteStage(): FormulaExecuteStageType;

    setRuntimeOtherData(formulaId: string, x: number, y: number, functionVariant: FunctionVariantType): void;

    getRuntimeOtherData(): IRuntimeOtherUnitDataType;

    getAllRuntimeData(): IAllRuntimeData;

    markedAsSuccessfullyExecuted(): void;

    markedAsNoFunctionsExecuted(): void;

    markedAsStopFunctionsExecuted(): void;

    markedAsInitialFunctionsExecuted(): void;

    setTotalFormulasToCalculate(value: number): void;

    getTotalFormulasToCalculate(): number;

    setCompletedFormulasCount(value: number): void;

    getCompletedFormulasCount(): number;

    getRuntimeState(): IExecutionInProgressParams;

    setTotalArrayFormulasToCalculate(value: number): void;

    getTotalArrayFormulasToCalculate(): number;

    setCompletedArrayFormulasCount(value: number): void;

    getCompletedArrayFormulasCount(): number;

    enableCycleDependency(): void;

    disableCycleDependency(): void;

    isCycleDependency(): boolean;

    getRuntimeArrayFormulaCellData(): IRuntimeUnitDataType;

    getRuntimeFeatureRange(): { [featureId: string]: IFeatureDirtyRangeType };

    getRuntimeFeatureCellData(): { [featureId: string]: IRuntimeUnitDataType };

    setRuntimeFeatureCellData(featureId: string, featureData: IRuntimeUnitDataType): void;

    setRuntimeFeatureRange(featureId: string, featureRange: IFeatureDirtyRangeType): void;

    clearReferenceAndNumberformatCache(): void;

    getUnitArrayFormulaEmbeddedMap(): IArrayFormulaEmbeddedMap;

    setUnitArrayFormulaEmbeddedMap(): void;

    clearArrayObjectCache(): void;

    getRuntimeImageFormulaData(): IRuntimeImageFormulaDataType[];

    setDependencyTreeModelData(data: IFormulaDependencyTreeJson[]): void;

    getDependencyTreeModelData(): IFormulaDependencyTreeJson[];
}

export class FormulaRuntimeService extends Disposable implements IFormulaRuntimeService {
    private _calculationCache: FormulaCalculationCache | undefined;
    private _formulaExecuteStage: FormulaExecuteStageType = FormulaExecuteStageType.IDLE;

    private _circularReferences = new Map<string, ICircularReference>();
    private _pendingCircularReferences = new Map<string, ICircularReference>();

    private _stopState = false;
    private _externalReferenceUnavailable = false;

    private _currentRow: number = -1;
    private _currentColumn: number = -1;

    private _currentRowCount: number = Number.NEGATIVE_INFINITY;
    private _currentColumnCount: number = Number.NEGATIVE_INFINITY;

    private _functionRefInfoOverrideStack: Array<{ rowCount: number; columnCount: number }> = [];

    private _currentSubUnitId: string = '';
    private _currentUnitId: string = '';

    private _runtimeData: IRuntimeUnitDataType = Object.create(null);
    private _sourceFormulaData: IFormulaData = Object.create(null);
    private _currentSourceFormula: IFormulaDataItem | undefined;

    private _runtimeOtherData: IRuntimeOtherUnitDataType = Object.create(null); // Data returned by other businesses through formula calculation, excluding the sheet.

    private _unitArrayFormulaRange: IArrayFormulaRangeType = Object.create(null);

    private _unitArrayFormulaEmbeddedMap: IArrayFormulaEmbeddedMap = Object.create(null);

    private _runtimeArrayFormulaCellData: IRuntimeUnitDataType = Object.create(null);

    private _runtimeClearArrayFormulaCellData: IRuntimeUnitDataType = Object.create(null);

    private _runtimeFeatureRange: { [featureId: string]: IFeatureDirtyRangeType } = Object.create(null);

    private _runtimeFeatureCellData: { [featureId: string]: IRuntimeUnitDataType } = Object.create(null);

    private _runtimeImageFormulaData: IRuntimeImageFormulaDataType[] = [];

    private _functionsExecutedState: FormulaExecutedStateType = FormulaExecutedStateType.INITIAL;

    // lambdaId: { key: BaseAstNode }
    private _functionDefinitionPrivacyVar: Map<string, Map<string, Nullable<BaseAstNode>>> = new Map();

    private _totalFormulasToCalculate: number = 0;

    private _completedFormulasCount: number = 0;

    private _totalArrayFormulasToCalculate: number = 0;

    private _completedArrayFormulasCount: number = 0;

    private _formulaCycleIndex: number = 0;

    private _isCycleDependency: boolean = false;

    private _dependencyTreeModelData: IFormulaDependencyTreeJson[] = [];

    constructor(
        @Inject(FormulaCacheService) private readonly _cacheService: FormulaCacheService,
        @IFormulaCurrentConfigService private readonly _currentConfigService: IFormulaCurrentConfigService,
        @IHyperlinkEngineFormulaService private readonly _hyperlinkEngineFormulaService: IHyperlinkEngineFormulaService,
        @IUniverInstanceService private readonly _univerInstanceService: IUniverInstanceService
    ) {
        super();

        this.disposeWithMe(
            this._univerInstanceService.getTypeOfUnitDisposed$<Workbook>(UniverInstanceType.UNIVER_SHEET).subscribe((workbook) => {
                for (const references of [this._circularReferences, this._pendingCircularReferences]) {
                    for (const [key, cell] of references) {
                        if (cell.unitId === workbook.getUnitId()) {
                            references.delete(key);
                        }
                    }
                }
            })
        );
    }

    get currentRow() {
        return this._currentRow;
    }

    get currentColumn() {
        return this._currentColumn;
    }

    get currentRowCount() {
        return this._functionRefInfoOverrideStack[this._functionRefInfoOverrideStack.length - 1]?.rowCount ??
            this._currentRowCount;
    }

    get currentColumnCount() {
        return this._functionRefInfoOverrideStack[this._functionRefInfoOverrideStack.length - 1]?.columnCount ??
            this._currentColumnCount;
    }

    get currentSubUnitId() {
        return this._currentSubUnitId;
    }

    get currentUnitId() {
        return this._currentUnitId;
    }

    override dispose(): void {
        super.dispose();

        this.reset();
        this._circularReferences.clear();
        this._pendingCircularReferences.clear();
        this._runtimeFeatureCellData = Object.create(null);
        this._runtimeFeatureRange = Object.create(null);
        this.clearReferenceAndNumberformatCache();
    }

    enableCycleDependency() {
        this._isCycleDependency = true;
    }

    disableCycleDependency() {
        this._isCycleDependency = false;
    }

    isCycleDependency() {
        return this._isCycleDependency;
    }

    setFormulaCycleIndex(index: number) {
        this._formulaCycleIndex = index;
    }

    getFormulaCycleIndex() {
        return this._formulaCycleIndex;
    }

    setTotalArrayFormulasToCalculate(value: number) {
        this._totalArrayFormulasToCalculate = value;
    }

    getTotalArrayFormulasToCalculate() {
        return this._totalArrayFormulasToCalculate;
    }

    setCompletedArrayFormulasCount(value: number) {
        this._completedArrayFormulasCount = value;
    }

    getCompletedArrayFormulasCount() {
        return this._completedArrayFormulasCount;
    }

    setTotalFormulasToCalculate(value: number) {
        this._totalFormulasToCalculate = value;
    }

    getTotalFormulasToCalculate() {
        return this._totalFormulasToCalculate;
    }

    setCompletedFormulasCount(value: number) {
        this._completedFormulasCount = value;
    }

    getCompletedFormulasCount() {
        return this._completedFormulasCount;
    }

    markedAsSuccessfullyExecuted() {
        this._functionsExecutedState = FormulaExecutedStateType.SUCCESS;
    }

    markedAsNoFunctionsExecuted() {
        this._functionsExecutedState = FormulaExecutedStateType.NOT_EXECUTED;
    }

    markedAsStopFunctionsExecuted() {
        this._functionsExecutedState = FormulaExecutedStateType.STOP_EXECUTION;
    }

    markedAsInitialFunctionsExecuted() {
        this._functionsExecutedState = FormulaExecutedStateType.INITIAL;
    }

    stopExecution() {
        this._stopState = true;

        this.setFormulaExecuteStage(FormulaExecuteStageType.IDLE);
    }

    isStopExecution() {
        return this._stopState || this._disposed;
    }

    setFormulaExecuteStage(type: FormulaExecuteStageType) {
        this._formulaExecuteStage = type;
    }

    getFormulaExecuteStage() {
        return this._formulaExecuteStage;
    }

    getCalculationCache(): FormulaCalculationCache | undefined {
        if (this._disposed || this._cacheService.closed) {
            return;
        }
        return this._calculationCache ??= this._cacheService.createCalculation();
    }

    clearInvertedIndexCache(): void {
        this._calculationCache?.invertedIndex.clear();
    }

    releaseCalculationCache(): void {
        if (this._calculationCache) {
            this._cacheService.releaseCalculation(this._calculationCache);
            this._calculationCache = undefined;
        }
    }

    reset() {
        this.releaseCalculationCache();
        this._formulaExecuteStage = FormulaExecuteStageType.IDLE;
        this._runtimeData = Object.create(null);
        this._sourceFormulaData = Object.create(null);
        this._currentSourceFormula = undefined;
        this._runtimeOtherData = Object.create(null);
        this._unitArrayFormulaRange = Object.create(null);
        this._unitArrayFormulaEmbeddedMap = Object.create(null);
        this._runtimeArrayFormulaCellData = Object.create(null);
        this._runtimeClearArrayFormulaCellData = Object.create(null);
        this._runtimeImageFormulaData = [];

        // this._runtimeFeatureCellData = {};
        // this._runtimeFeatureRange = {};

        this._functionDefinitionPrivacyVar.clear();
        this.markedAsInitialFunctionsExecuted();

        this._stopState = false;
        this._isCycleDependency = false;
        this._totalFormulasToCalculate = 0;
        this._completedFormulasCount = 0;
        this._functionRefInfoOverrideStack = [];
        this.clearReferenceAndNumberformatCache();
    }

    clearReferenceAndNumberformatCache() {
        clearNumberFormatTypeCache();
        clearStringToNumberPatternCache();
        clearReferenceToRangeCache();
    }

    beginCircularReferences(clearPrevious = false): void {
        this._pendingCircularReferences = clearPrevious ? new Map() : new Map(this._circularReferences);
        if (clearPrevious) return;
        if (this._pendingCircularReferences.size === 0 && hasDeferredFormulaDataSnapshot(this._currentConfigService)) return;
        const formulaData = this._currentConfigService.getFormulaData();
        for (const [key, cell] of this._pendingCircularReferences) {
            if (!formulaData[cell.unitId]?.[cell.sheetId]?.[cell.row]?.[cell.column]) {
                this._pendingCircularReferences.delete(key);
            }
        }
    }

    clearCircularReferenceCells(cells: { unitId: string; subUnitId: string; row: number; column: number }[]): void {
        if (this._pendingCircularReferences.size === 0) {
            return;
        }
        for (const cell of cells) {
            this._pendingCircularReferences.delete(JSON.stringify([cell.unitId, cell.subUnitId, cell.row, cell.column]));
            if (this._pendingCircularReferences.size === 0) {
                return;
            }
        }
    }

    recordCircularReferences(cells: ICircularReference[]): void {
        for (const cell of cells) {
            if (cell.row < 0 || cell.column < 0) {
                continue;
            }
            this._pendingCircularReferences.set(JSON.stringify([cell.unitId, cell.sheetId, cell.row, cell.column]), { ...cell });
        }
    }

    completeCircularReferences(): void {
        if (!this.isStopExecution()) {
            this._circularReferences = new Map(this._pendingCircularReferences);
        }
    }

    preserveCurrentCellData(): void {
        const unitId = this.currentUnitId;
        const sheetId = this.currentSubUnitId;
        const cell = this._currentConfigService.getUnitData()[unitId]?.[sheetId]?.cellData
            .getValue(this.currentRow, this.currentColumn);
        if (cell?.v != null) {
            return;
        }
        const sheets = this._runtimeData[unitId] ??= {};
        const matrix = sheets[sheetId] ??= new ObjectMatrix<ICellData>();
        matrix.setValue(this.currentRow, this.currentColumn, {
            v: cell?.v ?? 0,
            t: cell?.v == null ? CellValueType.NUMBER : cell.t,
        });
        if (this._currentSourceFormula) {
            const formulas = this._sourceFormulaData[unitId] ??= {};
            const sheet = formulas[sheetId] ??= {};
            const row = sheet[this.currentRow] ??= {};
            row[this.currentColumn] = { ...this._currentSourceFormula };
        }
    }

    setCurrent(
        row: number,
        column: number,
        rowCount: number,
        columnCount: number,
        sheetId: string,
        unitId: string
    ) {
        this._externalReferenceUnavailable = false;
        this._currentRow = row;
        this._currentColumn = column;
        this._currentRowCount = rowCount;
        this._currentColumnCount = columnCount;
        this._currentSubUnitId = sheetId;
        this._currentUnitId = unitId;
        const cell = this._currentConfigService.getUnitData()[unitId]?.[sheetId]?.cellData.getValue(row, column);
        const formula = this._currentConfigService.getFormulaData()[unitId]?.[sheetId]?.[row]?.[column];
        // The live cell may be cleared while an earlier asynchronous dependency is still executing.
        if (formula) {
            const isSharedFollower = (formula.x ?? 0) !== 0 || (formula.y ?? 0) !== 0;
            this._currentSourceFormula = { f: isSharedFollower ? '' : formula.f, si: formula.si };
        } else {
            this._currentSourceFormula = cell?.f || cell?.si ? { f: cell.f ?? '', si: cell.si ?? undefined } : undefined;
        }
    }

    setFunctionRefInfoOverride(rowCount: number, columnCount: number) {
        this._functionRefInfoOverrideStack.push({ rowCount, columnCount });

        return () => {
            this._functionRefInfoOverrideStack.pop();
        };
    }

    hasFunctionRefInfoOverride(): boolean {
        return this._functionRefInfoOverrideStack.length > 0;
    }

    clearFunctionDefinitionPrivacyVar() {
        this._functionDefinitionPrivacyVar.clear();
    }

    registerFunctionDefinitionPrivacyVar(lambdaId: string, lambdaVar: Map<string, Nullable<BaseAstNode>>) {
        this._functionDefinitionPrivacyVar.set(lambdaId, lambdaVar);
    }

    getFunctionDefinitionPrivacyVar(lambdaId: string): Nullable<Map<string, Nullable<BaseAstNode>>> {
        return this._functionDefinitionPrivacyVar.get(lambdaId);
    }

    setRuntimeOtherData(formulaId: string, x: number, y: number, functionVariant: FunctionVariantType) {
        if (this._disposed) {
            return;
        }
        const subUnitId = this._currentSubUnitId;
        const unitId = this._currentUnitId;

        if (this._runtimeOtherData[unitId] === undefined) {
            this._runtimeOtherData[unitId] = Object.create(null);
        }

        const unitData = this._runtimeOtherData[unitId]!;

        if (unitData[subUnitId] === undefined || unitData[subUnitId] === null) {
            unitData[subUnitId] = Object.create(null);
        }

        const subComponentData = unitData[subUnitId]!;

        let cellDatas: Nullable<ICellData>[][] = [];

        if (
            functionVariant.isReferenceObject() ||
            (functionVariant.isValueObject() && (functionVariant as BaseValueObject).isArray())
        ) {
            const objectValueRefOrArray = functionVariant as BaseReferenceObject | ArrayValueObject;
            const { startRow, startColumn } = objectValueRefOrArray.getRangePosition();
            objectValueRefOrArray.iterator((valueObject, rowIndex, columnIndex) => {
                const value = this._getValueObjectOfRuntimeData(valueObject);

                const row = rowIndex - startRow;
                const column = columnIndex - startColumn;
                if (cellDatas[row] == null) {
                    cellDatas[row] = [];
                }

                cellDatas[row][column] = value;
            });
        } else {
            cellDatas = [[this._getValueObjectOfRuntimeData(functionVariant as BaseValueObject)!]];
        }

        if (subComponentData[formulaId] === undefined || subComponentData[formulaId] === null) {
            subComponentData[formulaId] = Object.create(null);
        }

        if (subComponentData[formulaId][y] === undefined || subComponentData[formulaId][y] === null) {
            subComponentData[formulaId][y] = Object.create(null);
        }

        // x represents the column offset, y represents the row offset
        subComponentData[formulaId][y][x] = cellDatas;
    }

    markExternalReferenceUnavailable(): void {
        this._externalReferenceUnavailable = true;
    }

    // eslint-disable-next-line max-lines-per-function
    setRuntimeData(functionVariant: FunctionVariantType) {
        if (this._disposed) {
            return;
        }
        if (this._externalReferenceUnavailable) {
            const cell = this._currentConfigService.getUnitData()[this._currentUnitId]?.[this._currentSubUnitId]
                ?.cellData
                .getValue(this._currentRow, this._currentColumn);
            // Leaving the original cell in place also lets downstream formulas read its cached value.
            const formula = this._currentConfigService.getFormulaData()[this._currentUnitId]?.[this._currentSubUnitId]
                ?.[this._currentRow]?.[this._currentColumn]?.f;
            if (cell?.f && cell.v != null && (formula == null || formula === cell.f)) {
                return;
            }
        }
        const row = this._currentRow;
        const column = this._currentColumn;
        const rowCount = this._currentRowCount;
        const columnCount = this.currentColumnCount;
        const sheetId = this._currentSubUnitId;
        const unitId = this._currentUnitId;

        // Get current sheet data
        if (this._runtimeData[unitId] == null) {
            this._runtimeData[unitId] = Object.create(null);
        }

        const unitData = this._runtimeData[unitId]!;

        if (unitData[sheetId] == null) {
            unitData[sheetId] = new ObjectMatrix<Nullable<ICellData>>();
        }

        const sheetData = unitData[sheetId];

        // Get current sheet array formula range
        if (this._unitArrayFormulaRange[unitId] == null) {
            this._unitArrayFormulaRange[unitId] = Object.create(null);
        }

        const arrayFormulaRange = this._unitArrayFormulaRange[unitId]!;

        if (arrayFormulaRange[sheetId] === null || arrayFormulaRange[sheetId] === undefined) {
            arrayFormulaRange[sheetId] = Object.create(null);
        }

        const arrayData = new ObjectMatrix<IRange>(arrayFormulaRange[sheetId]);

        // Get current sheet array formula cell data
        if (this._runtimeArrayFormulaCellData[unitId] === undefined) {
            this._runtimeArrayFormulaCellData[unitId] = Object.create(null);
        }

        const runtimeArrayFormulaCellData = this._runtimeArrayFormulaCellData[unitId]!;

        if (runtimeArrayFormulaCellData[sheetId] == null) {
            runtimeArrayFormulaCellData[sheetId] = new ObjectMatrix<Nullable<ICellData>>();
        }

        const runtimeArrayUnitData = runtimeArrayFormulaCellData[sheetId];

        // Get current sheet clear array formula cell data
        if (this._runtimeClearArrayFormulaCellData[unitId] === undefined) {
            this._runtimeClearArrayFormulaCellData[unitId] = Object.create(null);
        }

        const clearArrayFormulaCellData = this._runtimeClearArrayFormulaCellData[unitId]!;

        if (clearArrayFormulaCellData[sheetId] == null) {
            clearArrayFormulaCellData[sheetId] = new ObjectMatrix<Nullable<ICellData>>();
        }

        const clearArrayUnitData = clearArrayFormulaCellData[sheetId];

        if (
            functionVariant.isReferenceObject() ||
            (functionVariant.isValueObject() && (functionVariant as BaseValueObject).isArray())
        ) {
            const objectValueRefOrArray = functionVariant as BaseReferenceObject | ArrayValueObject;

            const { startRow, startColumn, endRow, endColumn } = objectValueRefOrArray.getRangePosition();

            /**
             * If the referenced range or array only contains a single value, such as A5,
             * then it is not treated as an array range and is directly assigned.
             */
            if (startRow === endRow && startColumn === endColumn) {
                const firstCellRaw = objectValueRefOrArray.getFirstCell();
                const firstCell = firstCellRaw.isNull()
                    ? NumberValueObject.create(0)
                    : firstCellRaw;
                // TODO @Dushusir set pattern style
                const valueObject = this._getValueObjectOfRuntimeData(firstCell);
                sheetData.setValue(row, column, valueObject);
                clearArrayUnitData.setValue(row, column, valueObject);

                // Formula calculation results are saved to cache
                this.getCalculationCache()?.invertedIndex.set(
                    unitId,
                    sheetId,
                    column,
                    firstCell.getValue(),
                    row,
                    true
                );

                return;
            }

            if (this._isCurrentSingleCellArrayFormulaRange(unitId, sheetId, row, column)) {
                const errorObject = this._getValueObjectOfRuntimeData(ErrorValueObject.create(ErrorType.VALUE));
                sheetData.setValue(row, column, errorObject);
                clearArrayUnitData.setValue(row, column, errorObject);
                runtimeArrayUnitData.setValue(row, column, errorObject);

                this.getCalculationCache()?.invertedIndex.set(
                    unitId,
                    sheetId,
                    column,
                    ErrorType.VALUE,
                    row,
                    true
                );

                return;
            }

            const arrayRange = {
                startRow: row,
                startColumn: column,
                endRow: endRow - startRow + row,
                endColumn: endColumn - startColumn + column,
            };

            // Do not use getData to synchronize arrayData to arrayFormulaRange[sheetId] anymore, they are already linked, otherwise it will cause performance issues
            arrayData.setValue(row, column, arrayRange);

            const hasDataInArrayFormulaRange = this._checkIfArrayFormulaRangeHasData(unitId, sheetId, row, column, arrayRange);
            const isArrayFormulaExceeded = this._checkIfArrayFormulaExceeded(rowCount, columnCount, arrayRange);

            if (hasDataInArrayFormulaRange || isArrayFormulaExceeded) {
                const errorObject = this._getValueObjectOfRuntimeData(ErrorValueObject.create(ErrorType.SPILL));
                sheetData.setValue(row, column, errorObject);
                clearArrayUnitData.setValue(row, column, errorObject);

                // Formula calculation results are saved to cache
                this.getCalculationCache()?.invertedIndex.set(
                    unitId,
                    sheetId,
                    column,
                    ErrorType.SPILL,
                    row,
                    true
                );

                /**
                 * When there are values within the array formula range, the entire formula will result in an error.
                 * In this case, you need to clear the previous range data to prevent other formulas from referencing the old values.
                 */
                const unitData = this._currentConfigService.getUnitData();
                const arrayData = this._currentConfigService.getArrayFormulaCellData();
                const previousArrayFormulaRange =
                    this._currentConfigService.getArrayFormulaRange()[unitId]?.[sheetId]?.[row]?.[column];
                objectValueRefOrArray.iterator((_, rowIndex, columnIndex) => {
                    const currentRow = rowIndex - startRow + row;
                    const currentColumn = columnIndex - startColumn + column;
                    const cell = unitData[unitId]?.[sheetId]?.cellData.getValue(currentRow, currentColumn);
                    const arrayDataCell = arrayData?.[unitId]?.[sheetId]?.getValue(currentRow, currentColumn);
                    const shouldClearPreviousCellOfCurrentArrayFormula = this._arrayCellHasData(arrayDataCell) &&
                        this._isInArrayFormulaRange(previousArrayFormulaRange, currentRow, currentColumn) &&
                        (cell == null || this._isSameCellValue(cell, arrayDataCell));

                    if (rowIndex === startRow && columnIndex === startColumn) {
                        runtimeArrayUnitData.setValue(row, column, errorObject);
                    } else if (shouldClearPreviousCellOfCurrentArrayFormula) {
                        sheetData.setValue(currentRow, currentColumn, null);
                    } else if (cell != null) {
                        if (cell.v == null) {
                            cell.v = '';
                        }
                        sheetData.setValue(currentRow, currentColumn, { ...cell });
                    } // To determine whether a cell has a value, in addition to cell != null, other array formulas may get undefined (displayed as 0). In this case, the value of the existing array formula cannot be modified.
                    else if (this._isInOtherArrayFormulaRange(unitId, sheetId, row, column, currentRow, currentColumn)) {
                        return true;
                    }
                });
            } else {
                const spillError = ErrorValueObject.create(ErrorType.SPILL);
                objectValueRefOrArray.iterator((valueObject, rowIndex, columnIndex) => {
                    // Formula calculation results are saved to cache
                    this.getCalculationCache()?.invertedIndex.set(
                        unitId,
                        sheetId,
                        column - startColumn + columnIndex,
                        valueObject == null || valueObject.isNull() ? null : valueObject.getValue(),
                        row - startRow + rowIndex,
                        true
                    );

                    const value = this._getValueObjectOfRuntimeData(valueObject);
                    if (rowIndex === startRow && columnIndex === startColumn) {
                        /**
                         * If the referenced range contains an error in the spill of the array formula,
                         * then the current array formula should report an error together.
                         */
                        if (valueObject != null && valueObject.isError() && valueObject.isEqualType(spillError)) {
                            clearArrayUnitData.setValue(row, column, {});
                            sheetData.setValue(row, column, { ...this._getValueObjectOfRuntimeData(spillError) });
                            return false;
                        }
                        sheetData.setValue(row, column, { ...value });
                    }

                    const currentRow = rowIndex - startRow + row;
                    const currentColumn = columnIndex - startColumn + column;

                    runtimeArrayUnitData.setValue(currentRow, currentColumn, value);
                });
            }
        } else {
            const valueObject = this._getValueObjectOfRuntimeData(functionVariant as BaseValueObject);
            sheetData.setValue(row, column, valueObject);
            if (this._currentSourceFormula != null) {
                const unitFormulas = this._sourceFormulaData[unitId] ??= {};
                const sheetFormulas = unitFormulas[sheetId] ??= {};
                const rowFormulas = sheetFormulas[row] ??= {};
                rowFormulas[column] = { ...this._currentSourceFormula };
            }

            // If it is the result of the IMAGE formula, the image info needs to be saved to runtimeImageFormulaData
            if ((functionVariant as BaseValueObject).isString() && (functionVariant as StringValueObject).isImage()) {
                const imageInfo = (functionVariant as StringValueObject).getImageInfo();
                if (imageInfo) {
                    this._runtimeImageFormulaData.push({
                        ...imageInfo,
                        unitId,
                        sheetId,
                        row,
                        column,
                    });
                }
            }

            // Formula calculation results are saved to cache
            this.getCalculationCache()?.invertedIndex.set(
                unitId,
                sheetId,
                column,
                (functionVariant as BaseValueObject).isNull() ? null : (functionVariant as BaseValueObject).getValue(),
                row,
                true
            );

            clearArrayUnitData.setValue(row, column, valueObject);
        }
    }

    private _getValueObjectOfRuntimeData(variant: Nullable<BaseValueObject>): Nullable<ICellData> {
        if (variant?.isString() && variant.isHyperlink()) {
            return this._hyperlinkEngineFormulaService.generateCellValue(
                (variant as StringValueObject).getHyperlinkUrl(),
                variant.getValue() as string
            );
        }

        return objectValueToCellValue(variant);
    }

    getUnitData() {
        return this._runtimeData;
    }

    getUnitArrayFormula() {
        return this._unitArrayFormulaRange;
    }

    getUnitArrayFormulaEmbeddedMap() {
        return this._unitArrayFormulaEmbeddedMap;
    }

    setUnitArrayFormulaEmbeddedMap() {
        const unitId = this._currentUnitId;
        const sheetId = this._currentSubUnitId;
        const rowIndex = this._currentRow;
        const columnIndex = this._currentColumn;

        const arrayFormulaEmbeddedMap = this._unitArrayFormulaEmbeddedMap;
        if (arrayFormulaEmbeddedMap[unitId] == null) {
            arrayFormulaEmbeddedMap[unitId] = Object.create(null);
        }

        if (arrayFormulaEmbeddedMap[unitId]![sheetId] == null) {
            arrayFormulaEmbeddedMap[unitId]![sheetId] = Object.create(null);
        }

        if (arrayFormulaEmbeddedMap[unitId]![sheetId][rowIndex] == null) {
            arrayFormulaEmbeddedMap[unitId]![sheetId][rowIndex] = Object.create(null);
        }

        arrayFormulaEmbeddedMap[unitId]![sheetId][rowIndex][columnIndex] = true;
    }

    getRuntimeOtherData() {
        return this._runtimeOtherData;
    }

    getRuntimeArrayFormulaCellData() {
        return this._runtimeArrayFormulaCellData;
    }

    getRuntimeClearArrayFormulaCellData() {
        return this._runtimeClearArrayFormulaCellData;
    }

    getRuntimeFeatureRange() {
        return this._runtimeFeatureRange;
    }

    setRuntimeFeatureRange(featureId: string, featureRange: IFeatureDirtyRangeType) {
        this._runtimeFeatureRange[featureId] = featureRange;
    }

    getRuntimeFeatureCellData() {
        return this._runtimeFeatureCellData;
    }

    setRuntimeFeatureCellData(featureId: string, featureData: IRuntimeUnitDataType) {
        this._runtimeFeatureCellData[featureId] = featureData;
    }

    setDependencyTreeModelData(data: IFormulaDependencyTreeJson[]) {
        this._dependencyTreeModelData = data;
    }

    getDependencyTreeModelData() {
        return this._dependencyTreeModelData;
    }

    getRuntimeImageFormulaData() {
        return this._runtimeImageFormulaData;
    }

    getAllRuntimeData(): IAllRuntimeData {
        return {
            unitData: this.getUnitData(),
            circularReferences: Array.from(this._circularReferences.values(), (cell) => ({ ...cell })),
            sourceFormulaData: this._sourceFormulaData,
            arrayFormulaRange: this.getUnitArrayFormula(),
            arrayFormulaEmbedded: this.getUnitArrayFormulaEmbeddedMap(),
            unitOtherData: this.getRuntimeOtherData(),
            functionsExecutedState: this._functionsExecutedState,
            arrayFormulaCellData: this.getRuntimeArrayFormulaCellData(),
            clearArrayFormulaCellData: this.getRuntimeClearArrayFormulaCellData(),
            imageFormulaData: this.getRuntimeImageFormulaData(),

            runtimeFeatureRange: this.getRuntimeFeatureRange(),
            runtimeFeatureCellData: this.getRuntimeFeatureCellData(),

            dependencyTreeModelData: this.getDependencyTreeModelData(),
        };
    }

    getRuntimeState(): IExecutionInProgressParams {
        return {
            totalFormulasToCalculate: this.getTotalFormulasToCalculate(),

            completedFormulasCount: this.getCompletedFormulasCount(),

            totalArrayFormulasToCalculate: this.getTotalArrayFormulasToCalculate(),

            completedArrayFormulasCount: this.getCompletedArrayFormulasCount(),

            stage: this.getFormulaExecuteStage(),

            circularReferences: Array.from(this._circularReferences.values(), (cell) => ({ ...cell })),
            formulaCycleIndex: this.getFormulaCycleIndex(),
        };
    }

    clearArrayObjectCache() {
        this._calculationCache?.references.clear();
    }

    private _checkIfArrayFormulaRangeHasData(
        formulaUnitId: string,
        formulaSheetId: string,
        formulaRow: number,
        formulaColumn: number,
        arrayRange: IRange
    ) {
        const { startRow, startColumn, endRow, endColumn } = arrayRange;

        const unitData = this._currentConfigService.getUnitData();
        // this._runtimeArrayFormulaCellData data is incomplete, use the data on configService
        const arrayData = this._currentConfigService.getArrayFormulaCellData();

        const previousArrayFormulaRange =
            this._currentConfigService.getArrayFormulaRange()[formulaUnitId]?.[formulaSheetId]?.[formulaRow]?.[formulaColumn];

        for (let r = startRow; r <= endRow; r++) {
            for (let c = startColumn; c <= endColumn; c++) {
                if (r === formulaRow && formulaColumn === c) {
                    continue;
                }

                const cell = this._runtimeData?.[formulaUnitId]?.[formulaSheetId]?.getValue(r, c);

                const arrayDataCell = arrayData?.[formulaUnitId]?.[formulaSheetId]?.getValue(
                    r,
                    c
                );
                const currentCell = unitData?.[formulaUnitId]?.[formulaSheetId]?.cellData?.getValue(r, c);

                const featureCell = this._getRuntimeFeatureCellValue(r, c, formulaSheetId, formulaUnitId);
                const isPreviousCellOfCurrentArrayFormula = this._arrayCellHasData(arrayDataCell) &&
                    this._isInArrayFormulaRange(previousArrayFormulaRange, r, c) &&
                    (currentCell == null || this._isSameCellValue(currentCell, arrayDataCell));
                const hasRuntimeCell = !isNullCell(cell);
                const isInOtherArrayFormulaRange = this._isInOtherArrayFormulaRange(formulaUnitId, formulaSheetId, formulaRow, formulaColumn, r, c);
                const currentCellBlocks = !isNullCell(currentCell) && !isPreviousCellOfCurrentArrayFormula;
                const featureCellBlocks = !isNullCell(featureCell);

                // arrayDataCell may display 0 as {v: null}. Although it is an empty cell, it is considered to have a value.
                if (
                    hasRuntimeCell ||
                    isInOtherArrayFormulaRange ||
                    currentCellBlocks ||
                    featureCellBlocks
                ) {
                    return true;
                }
            }
        }

        return false;
    }

    private _getRuntimeFeatureCellValue(row: number, column: number, sheetId: string, unitId: string) {
        return getRuntimeFeatureCell(row, column, sheetId, unitId, this._runtimeFeatureCellData);
    }

    private _arrayCellHasData(cell: Nullable<ICellData>) {
        if (cell === null || cell === undefined) {
            return false;
        }

        if (cell.v !== undefined) {
            return true;
        }

        return false;
    }

    /**
     * If the current array formula in the extended area intersects with the existing array formula, a #SPILL! error will be reported. Note that if other array formulas are already #SPILL!, they will not conflict with the current array formula
     * @param formulaUnitId
     * @param formulaSheetId
     * @param formulaRow
     * @param formulaColumn
     * @param r
     * @param c
     * @returns
     */
    private _isInOtherArrayFormulaRange(formulaUnitId: string, formulaSheetId: string, formulaRow: number, formulaColumn: number, r: number, c: number) {
        const arrayFormulaRange = this._currentConfigService.getArrayFormulaRange()[formulaUnitId]?.[formulaSheetId];

        if (arrayFormulaRange == null) {
            return false;
        }

        let isCellOverlapping = false;

        const arrayFormulaRangeMatrix = new ObjectMatrix(arrayFormulaRange);
        arrayFormulaRangeMatrix.forValue((rangeRow, rangeCol, range) => {
            // skip the current range
            if (rangeRow === formulaRow && rangeCol === formulaColumn) {
                return;
            }

            // Check if the cell is part of any other range in arrayFormulaRangeMatrix, and value is not #SPILL!
            const isOverlapping = this._isInArrayFormulaRange(range, r, c);
            const cell = this._runtimeData[formulaUnitId]?.[formulaSheetId]?.getValue(rangeRow, rangeCol);
            if (isOverlapping && cell?.v !== ErrorType.SPILL) {
                isCellOverlapping = true;
            }
        });

        return isCellOverlapping;
    }

    private _isCurrentSingleCellArrayFormulaRange(unitId: string, sheetId: string, row: number, column: number) {
        const range = this._currentConfigService.getArrayFormulaRange()[unitId]?.[sheetId]?.[row]?.[column];

        return range != null &&
            range.startRow === range.endRow &&
            range.startColumn === range.endColumn;
    }

    private _isInArrayFormulaRange(range: Nullable<IRange>, r: number, c: number) {
        if (range == null) {
            return false;
        }

        const { startRow, startColumn, endRow, endColumn } = range;

        if (r >= startRow && r <= endRow && c >= startColumn && c <= endColumn) {
            return true;
        }

        return false;
    }

    private _isSameCellValue(cell: Nullable<ICellData>, arrayDataCell: Nullable<ICellData>) {
        if (cell?.t !== arrayDataCell?.t) {
            return false;
        }
        if (cell?.v === arrayDataCell?.v) {
            return true;
        }
        // Imported numeric caches may store strings; calculation results store numbers.
        return cell?.t === CellValueType.NUMBER &&
            isRealNum(cell.v) && isRealNum(arrayDataCell?.v) &&
            Number(cell.v) === Number(arrayDataCell?.v);
    }

    private _checkIfArrayFormulaExceeded(rowCount: number, columnCount: number, arrayRange: IRange) {
        if (arrayRange.endRow >= rowCount || arrayRange.endColumn >= columnCount) {
            return true;
        }

        return false;
    }

    private _isInDirtyRange(unitId: string, sheetId: string, row: number, column: number) {
        const dirtyRanges = this._currentConfigService.getDirtyRanges();
        // Handle the deletion and add of sheet.
        if (dirtyRanges.length === 0) {
            return true;
        }
        return isInDirtyRange(dirtyRanges, unitId, sheetId, row, column);
    }
}

export const IFormulaRuntimeService = createIdentifier<IFormulaRuntimeService>('univer.formula.runtime.service');