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.

24.8 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, IUnitRange, Nullable } from '@univerjs/core';
import type {
    IArrayFormulaRangeType,
    IRuntimeUnitDataType,
    IUnitData,
    IUnitSheetNameMap,
    IUnitStylesData,
} from '../../basics/common';
import type { FormulaCalculationCache } from '../../services/formula-cache.service';
import type { BaseValueObject, IArrayValueObject } from '../value-object/base-value-object';
import {
    CellValueType,
    DateSystem,
    isFormulaId,
    isFormulaString,
    isNullCell,
    isTextFormat,
    moveRangeByOffset,
    ObjectMatrix,
} from '@univerjs/core';
import { ERROR_TYPE_SET, ErrorType } from '../../basics/error-type';
import { ObjectClassType } from '../../basics/object-class-type';
import { getCellValue } from '../utils/cell';
import { getRuntimeFeatureCell } from '../utils/get-runtime-feature-cell';
import { ArrayValueObject, ValueObjectFactory } from '../value-object/array-value-object';
import { ErrorValueObject } from '../value-object/base-value-object';
import {
    createBooleanValueObjectByRawValue,
    createNumberValueObjectByRawValue,
    NullValueObject,
    NumberValueObject,
    StringValueObject,
} from '../value-object/primitive-object';

export type NodeValueType = BaseValueObject | BaseReferenceObject | AsyncObject | AsyncArrayObject;

export type FunctionVariantType = BaseValueObject | BaseReferenceObject;

export class BaseReferenceObject extends ObjectClassType {
    private _calculationCache: FormulaCalculationCache | undefined;

    setCalculationCache(cache: FormulaCalculationCache | undefined): void {
        this._calculationCache = cache;
    }

    getCalculationCache(): FormulaCalculationCache | undefined {
        return this._calculationCache;
    }

    private _dateSystem = DateSystem.Date1900;

    private _forcedSheetId: Nullable<string> = '';

    private _forcedSheetName: string = '';

    private _defaultSheetId: string = '';

    private _rangeData: IRange = {
        startColumn: -1,
        startRow: -1,
        endRow: -1,
        endColumn: -1,
    };

    private _unitData: IUnitData = {};

    private _unitStylesData: IUnitStylesData = {};

    private _filteredOutRows: number[] = [];

    private _defaultUnitId: string = '';

    private _forcedUnitId: string = '';

    private _unitQualifier: string = '';

    private _runtimeData: IRuntimeUnitDataType = {};

    private _arrayFormulaCellData: IRuntimeUnitDataType = {};

    private _arrayFormulaRange: IArrayFormulaRangeType = {};

    private _runtimeArrayFormulaCellData: IRuntimeUnitDataType = {};

    private _runtimeArrayFormulaRange: IArrayFormulaRangeType = {};

    private _runtimeFeatureCellData: { [featureId: string]: IRuntimeUnitDataType } = {};

    private _refOffsetX = 0;

    private _refOffsetY = 0;

    private _currentRow?: number;

    private _currentColumn?: number;

    constructor(private _token: string) {
        super();
    }

    override dispose(): void {
        this._calculationCache = undefined;
        this._unitData = {};

        this._unitStylesData = {};

        this._runtimeData = {};
    }

    getToken() {
        return this._token;
    }

    setToken(token: string) {
        this._token = token;
    }

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

        if (startRow < 0 || startColumn < 0 || endRow >= this.getActiveSheetRowCount() || endColumn >= this.getActiveSheetColumnCount()) {
            return true;
        }
        return false;
    }

    setRefOffset(x: number = 0, y: number = 0) {
        this._refOffsetX = x;
        this._refOffsetY = y;
    }

    getRefOffset() {
        return {
            x: this._refOffsetX,
            y: this._refOffsetY,
        };
    }

    getRangePosition() {
        const { x, y } = this.getRefOffset();
        const newRange = moveRangeByOffset(this.getRangeData(), x, y);

        if (Number.isNaN(newRange.startRow)) {
            newRange.startRow = 0;
        }

        if (Number.isNaN(newRange.startColumn)) {
            newRange.startColumn = 0;
        }

        if (Number.isNaN(newRange.endRow)) {
            newRange.endRow = this.getActiveSheetRowCount() - 1;
        }

        if (Number.isNaN(newRange.endColumn)) {
            newRange.endColumn = this.getActiveSheetColumnCount() - 1;
        }

        return newRange;
    }

    override isReferenceObject() {
        return true;
    }

    getDateSystem() {
        return this._dateSystem;
    }

    withDateSystem(dateSystem: DateSystem): this {
        this._dateSystem = dateSystem;
        return this;
    }

    iterator(
        callback: (valueObject: Nullable<BaseValueObject>, rowIndex: number, columnIndex: number) => Nullable<boolean>
    ) {
        const { startRow, endRow, startColumn, endColumn } = this.getRangePosition();

        if (this._checkIfWorksheetMiss()) {
            return callback(ErrorValueObject.create(ErrorType.REF), startRow, startColumn);
        }

        const unitId = this._forcedUnitId || this._defaultUnitId;
        const sheetId = this._forcedSheetId || this._defaultSheetId;

        for (let r = startRow; r <= endRow; r++) {
            for (let c = startColumn; c <= endColumn; c++) {
                if (r < 0 || c < 0) {
                    return callback(ErrorValueObject.create(ErrorType.REF), r, c);
                }

                const cell = this.getCellData(r, c)!;
                let result: Nullable<boolean> = false;
                if (isNullCell(cell) && !isTypedEmptyStringCell(cell)) {
                    result = callback(null, r, c);
                    continue;
                }

                let resultObjectValue = this.getCellValueObject(cell);

                // Set numfmt pattern for first cell
                // TODO@Dushusir it does not have to be a number, the string can also be set a number format
                if (r === startRow && c === startColumn) {
                    const pattern = this.getCellPattern(unitId, sheetId, r, c);
                    if (pattern && resultObjectValue.isNumber()) {
                        const value = Number(resultObjectValue.getValue());
                        resultObjectValue = NumberValueObject.create(value, pattern).withDateSystem(this._dateSystem);
                    }
                }

                result = callback(resultObjectValue, r, c);

                if (result === false) {
                    return;
                }
            }
        }
    }

    getFirstCell() {
        if (this._checkIfWorksheetMiss()) {
            return ErrorValueObject.create(ErrorType.REF);
        }

        const { startRow, startColumn } = this.getRangePosition();
        const cell = this.getCellData(startRow, startColumn);

        if (!cell) {
            return NumberValueObject.create(0);
        }

        let cellValueObject = this.getCellValueObject(cell);

        // Set numfmt pattern,
        // TODO@Dushusir it does not have to be a number, the string can also be set a number format
        const unitId = this._forcedUnitId || this._defaultUnitId;
        const sheetId = this._forcedSheetId || this._defaultSheetId;
        const pattern = this.getCellPattern(unitId, sheetId, startRow, startColumn);
        if (pattern && cellValueObject.isNumber()) {
            const value = Number(cellValueObject.getValue());
            cellValueObject = NumberValueObject.create(value, pattern).withDateSystem(this._dateSystem);
        }

        return cellValueObject;
    }

    getRangeData() {
        return this._rangeData;
    }

    setRangeData(range: IRange) {
        this._rangeData = range;
    }

    getUnitId() {
        if (this._forcedUnitId && this._forcedUnitId.length > 0) {
            return this._forcedUnitId;
        }
        return this._defaultUnitId;
    }

    getSheetId() {
        if (this._forcedSheetId && this._forcedSheetId.length > 0) {
            return this._forcedSheetId;
        }
        return this._defaultSheetId;
    }

    setForcedUnitIdDirect(unitId: string) {
        if (unitId.length > 0) {
            this._forcedUnitId = unitId;
        }
    }

    setUnitQualifier(unitQualifier: string) {
        this._unitQualifier = unitQualifier;
    }

    getUnitQualifier() {
        return this._unitQualifier;
    }

    getForcedUnitId() {
        return this._forcedUnitId;
    }

    setForcedSheetId(sheetNameMap: IUnitSheetNameMap) {
        if (!this._forcedSheetName) {
            return;
        }
        const names = sheetNameMap[this.getUnitId()];
        this._forcedSheetId = names?.[this._forcedSheetName];
        if (names && this._forcedSheetId == null) {
            const name = this._forcedSheetName.toLocaleLowerCase();
            const matchedName = Object.keys(names).find((candidate) => candidate.toLocaleLowerCase() === name);
            this._forcedSheetId = matchedName == null ? undefined : names[matchedName];
        }
    }

    setForcedSheetIdDirect(sheetId: string) {
        this._forcedSheetId = sheetId;
    }

    getForcedSheetId() {
        return this._forcedSheetId;
    }

    setForcedSheetName(sheetName: string) {
        if (sheetName.length > 0) {
            this._forcedSheetName = sheetName;
        }
    }

    getForcedSheetName() {
        return this._forcedSheetName;
    }

    setDefaultSheetId(sheetId: string) {
        this._defaultSheetId = sheetId;
    }

    getDefaultSheetId() {
        return this._defaultSheetId;
    }

    setDefaultUnitId(unitId: string) {
        this._defaultUnitId = unitId;
    }

    getDefaultUnitId() {
        return this._defaultUnitId;
    }

    getUnitData() {
        return this._unitData;
    }

    setUnitData(unitData: IUnitData) {
        this._unitData = unitData;
    }

    getUnitStylesData() {
        return this._unitStylesData;
    }

    setUnitStylesData(unitStylesData: IUnitStylesData) {
        this._unitStylesData = unitStylesData;
    }

    getFilteredOutRows() {
        return this._filteredOutRows;
    }

    setFilteredOutRows(filteredOutRows: number[]) {
        this._filteredOutRows = filteredOutRows;
    }

    getRuntimeData() {
        return this._runtimeData;
    }

    setRuntimeData(runtimeData: IRuntimeUnitDataType) {
        this._runtimeData = runtimeData;
    }

    getArrayFormulaCellData() {
        return this._arrayFormulaCellData;
    }

    setArrayFormulaCellData(unitData: IRuntimeUnitDataType) {
        this._arrayFormulaCellData = unitData;
    }

    getArrayFormulaRange() {
        return this._arrayFormulaRange;
    }

    setArrayFormulaRange(rangeData: IArrayFormulaRangeType) {
        this._arrayFormulaRange = rangeData;
    }

    getRuntimeArrayFormulaCellData() {
        return this._runtimeArrayFormulaCellData;
    }

    setRuntimeArrayFormulaCellData(unitData: IRuntimeUnitDataType) {
        this._runtimeArrayFormulaCellData = unitData;
    }

    getRuntimeArrayFormulaRange() {
        return this._runtimeArrayFormulaRange;
    }

    setRuntimeArrayFormulaRange(rangeData: IArrayFormulaRangeType) {
        this._runtimeArrayFormulaRange = rangeData;
    }

    getRuntimeFeatureCellData() {
        return this._runtimeFeatureCellData;
    }

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

    getActiveSheetRowCount() {
        return this.getCurrentActiveSheetData()?.rowCount || 0;
    }

    getActiveSheetColumnCount() {
        return this.getCurrentActiveSheetData()?.columnCount || 0;
    }

    getRowCount() {
        const rangeData = this.getRangeData();
        return rangeData.endRow - rangeData.startRow + 1;
    }

    getColumnCount() {
        const rangeData = this.getRangeData();
        return rangeData.endColumn - rangeData.startColumn + 1;
    }

    getRowData() {
        return this.getCurrentActiveSheetData()?.rowData || {};
    }

    getColumnData() {
        return this.getCurrentActiveSheetData()?.columnData || {};
    }

    isCell() {
        return false;
    }

    isColumn() {
        return false;
    }

    isRow() {
        return false;
    }

    isRange() {
        return false;
    }

    isTable() {
        return false;
    }

    isCurrentRowForRange(): boolean {
        return false;
    }

    isCurrentColumnForRange(): boolean {
        return false;
    }

    isMultiArea(): boolean {
        return false;
    }

    unionBy(referenceObject: BaseReferenceObject): NodeValueType {
        /** abstract */
        return ErrorValueObject.create(ErrorType.REF);
    }

    unionRange(rangeData1: IRange, rangeData2: IRange): IRange {
        /** abstract */
        return {
            startRow: -1,
            startColumn: -1,
            endRow: -1,
            endColumn: -1,
        };
    }

    getCellValueObject(cell: ICellData) {
        const value = getCellValue(cell);
        const cellValueType = Number(cell.t);
        if (cellValueType !== CellValueType.FORCE_STRING && ERROR_TYPE_SET.has(value as ErrorType)) {
            return ErrorValueObject.create(value as ErrorType);
        }

        if (cellValueType === CellValueType.NUMBER) {
            const pattern = this._getPatternByCell(cell);

            if (isTextFormat(pattern)) {
                return StringValueObject.create(value.toString()).withDateSystem(this._dateSystem);
            }

            return createNumberValueObjectByRawValue(value, pattern).withDateSystem(this._dateSystem);
        }
        if (cellValueType === CellValueType.STRING || cellValueType === CellValueType.FORCE_STRING) {
            // A1 is `"test"`, =A1 also needs to get `"test"`
            return StringValueObject.create(value.toString()).withDateSystem(this._dateSystem);
        }
        if (cellValueType === CellValueType.BOOLEAN) {
            return createBooleanValueObjectByRawValue(value);
        }

        return ValueObjectFactory.create(value, false, this._dateSystem);
    }

    private _getPatternByCell(cell: ICellData) {
        const styles = this._unitStylesData[this.getUnitId()];

        if (!styles) return '';

        const style = styles.getStyleByCell(cell);
        return style?.n?.pattern || '';
    }

    getCellByRow(row: number) {
        return this.getCellByPosition(row);
    }

    getCellByColumn(column: number) {
        return this.getCellByPosition(undefined, column);
    }

    getCurrentActiveSheetData() {
        return this._unitData[this.getUnitId()]?.[this.getSheetId()];
    }

    getCurrentStylesData() {
        return this._unitStylesData[this.getUnitId()];
    }

    getCurrentRuntimeSheetData() {
        return this._runtimeData?.[this.getUnitId()]?.[this.getSheetId()];
    }

    getCurrentActiveArrayFormulaCellData() {
        return this._arrayFormulaCellData?.[this.getUnitId()]?.[this.getSheetId()];
    }

    getCurrentRuntimeActiveArrayFormulaCellData() {
        return this._runtimeArrayFormulaCellData?.[this.getUnitId()]?.[this.getSheetId()];
    }

    getCurrentActiveArrayFormulaRange() {
        return this._arrayFormulaRange?.[this.getUnitId()]?.[this.getSheetId()];
    }

    getCurrentRuntimeActiveArrayFormulaRange() {
        return this._runtimeArrayFormulaRange?.[this.getUnitId()]?.[this.getSheetId()];
    }

    getCellData(row: number, column: number) {
        const activeSheetData = this.getCurrentActiveSheetData();

        const activeRuntimeData = this.getCurrentRuntimeSheetData();

        const activeArrayFormulaCellData = this.getCurrentActiveArrayFormulaCellData();

        const activeRuntimeArrayFormulaCellData = this.getCurrentRuntimeActiveArrayFormulaCellData();

        const activeArrayFormulaRangeMatrix = new ObjectMatrix<IRange>(this.getCurrentActiveArrayFormulaRange());

        const activeRuntimeArrayFormulaRangeMatrix = new ObjectMatrix<IRange>(this.getCurrentRuntimeActiveArrayFormulaRange());

        let cellData: Nullable<ICellData> = activeRuntimeData?.getValue(row, column) ||
            activeRuntimeArrayFormulaCellData?.getValue(row, column) ||
            this.getRuntimeFeatureCellValue(row, column);

        if (cellData) {
            return cellData;
        }

        cellData = activeArrayFormulaCellData?.getValue(row, column);

        if (cellData) {
            // Check if the cell is in the runtime array formula range. if so, do not return the original array formula cell data.
            let isInRuntimeArrayFormulaRange = false;

            activeArrayFormulaRangeMatrix.forValue((_row, _column, range) => {
                if (!(row >= range.startRow && row <= range.endRow && column >= range.startColumn && column <= range.endColumn)) {
                    return;
                }
                const runtimeAnchor = activeRuntimeData?.getValue(_row, _column);
                const anchorCalculated = runtimeAnchor && !isFormulaString(runtimeAnchor.f) && !isFormulaId(runtimeAnchor.si);
                if (activeRuntimeArrayFormulaRangeMatrix.getValue(_row, _column) || anchorCalculated) {
                    isInRuntimeArrayFormulaRange = true;
                    return false;
                }
            });

            if (!isInRuntimeArrayFormulaRange) {
                return cellData;
            }

            return null;
        }

        return activeSheetData?.cellData.getValue(row, column);
    }

    getRuntimeFeatureCellValue(row: number, column: number) {
        return getRuntimeFeatureCell(row, column, this.getSheetId(), this.getUnitId(), this._runtimeFeatureCellData);
    }

    isCellBlank(row: number, column: number): boolean {
        const cell = this.getCellData(row, column);
        if (!isNullCell(cell)) {
            return false;
        }

        const originalCell = this.getCurrentActiveSheetData()?.cellData.getValue(row, column);
        if (isFormulaString(originalCell?.f) || isFormulaId(originalCell?.si)) {
            return false;
        }

        if (!cell) {
            return true;
        }

        // Formula result matrices omit f/si. Only the result selected by getCellData is active.
        if (
            cell === this.getCurrentRuntimeActiveArrayFormulaCellData()?.getValue(row, column) ||
            cell === this.getCurrentActiveArrayFormulaCellData()?.getValue(row, column) ||
            cell === this.getRuntimeFeatureCellValue(row, column)
        ) {
            return false;
        }

        // Runtime sheet data also copies ordinary cells when an array formula is blocked.
        return originalCell != null || cell !== this.getCurrentRuntimeSheetData()?.getValue(row, column);
    }

    getCellByPosition(rowRaw?: number, columnRaw?: number) {
        const range = this.getRangePosition();
        const row = rowRaw ?? range.startRow;
        const column = columnRaw ?? range.startColumn;

        const cell = this.getCellData(row, column);

        if (!cell) {
            return NullValueObject.create();
        }

        return this.getCellValueObject(cell);
    }

    setCurrentRowAndColumn(row: number, column: number) {
        this._currentRow = row;
        this._currentColumn = column;
    }

    getCurrentRow() {
        return this._currentRow;
    }

    getCurrentColumn() {
        return this._currentColumn;
    }

    /**
     * Get the pattern of the cell
     * @param unitId
     * @param sheetId
     * @param row
     * @param column
     * @returns
     */
    getCellPattern(unitId: string, sheetId: string, row: number, column: number) {
        const currentStyles = this._unitStylesData[unitId];

        if (!currentStyles) {
            return '';
        }

        const currentCell = this._unitData[unitId]?.[sheetId]?.cellData?.getValue(row, column);

        if (!currentCell) {
            return '';
        }

        const style = currentStyles.getStyleByCell(currentCell);

        return style?.n?.pattern || '';
    }

    toArrayValueObject(useCache: boolean = true): ArrayValueObject {
        const { startRow, endRow, startColumn, endColumn } = this.getRangePosition();

        // A cached range can be reused only when its string and formatted-number interpretation matches.
        const key = `${this.getUnitId()}_${this.getSheetId()}_${startRow}_${endRow}_${startColumn}_${endColumn}_${this._dateSystem}`;

        const cache = this._checkIfWorksheetMiss() ? undefined : this._calculationCache;
        const array = cache && !cache.closed ? cache.references.get(key) : undefined;

        if (array && useCache) {
            return array;
        }

        const rowSize = endRow - startRow + 1;
        const columnSize = endColumn - startColumn + 1;

        if (rowSize < 0 || columnSize < 0) {
            return this._getBlankArrayValueObject();
        }

        const arrayValueList: BaseValueObject[][] = new Array(rowSize);
        this.iterator((valueObject: Nullable<BaseValueObject>, rowIndex: number, columnIndex: number) => {
            const row = rowIndex - startRow;
            const column = columnIndex - startColumn;
            if (!arrayValueList[row]) {
                arrayValueList[row] = new Array(columnSize);
            }

            if (valueObject == null) {
                valueObject = NullValueObject.create();
            }

            arrayValueList[row][column] = valueObject;
        });

        const arrayValueObjectData: IArrayValueObject = {
            calculateValueList: arrayValueList,
            rowCount: arrayValueList.length,
            columnCount: arrayValueList[0]?.length || 0,
            unitId: this.getUnitId(),
            sheetId: this.getSheetId(),
            row: startRow,
            column: startColumn,
            useInvertedIndexCache: true,
        };

        const arrayValueObject = ArrayValueObject.create(arrayValueObjectData, this._dateSystem).withCalculationCache(this._calculationCache);

        if (useCache && cache && !cache.closed) {
            cache.references.set(key, arrayValueObject);
        }

        return arrayValueObject;
    }

    toUnitRange() {
        return {
            range: this.getRangePosition(),
            sheetId: this.getSheetId(),
            unitId: this.getUnitId(),
        };
    }

    toUnitRanges(): IUnitRange[] {
        return [this.toUnitRange()];
    }

    private _checkIfWorksheetMiss() {
        if ((this._forcedSheetId == null || this._forcedSheetId.length === 0) && this._forcedSheetName.length > 0) {
            return true;
        }
        return false;
    }

    private _getBlankArrayValueObject() {
        const arrayValueObjectData: IArrayValueObject = {
            calculateValueList: [],
            rowCount: 0,
            columnCount: 0,
            unitId: this.getUnitId(),
            sheetId: this.getSheetId(),
            row: 0,
            column: 0,
        };

        return ArrayValueObject.create(arrayValueObjectData, this._dateSystem).withCalculationCache(this._calculationCache);
    }
}

function isTypedEmptyStringCell(cell: Nullable<ICellData>): boolean {
    return cell?.v === '' && (cell.t === CellValueType.STRING || cell.t === CellValueType.FORCE_STRING);
}

export class AsyncObject extends ObjectClassType {
    constructor(private _promise: Promise<BaseValueObject>) {
        super();
    }

    override isAsyncObject() {
        return true;
    }

    async getValue() {
        return this._promise;
    }
}

export class AsyncArrayObject extends ObjectClassType {
    constructor(private _promiseList: Array<Array<BaseValueObject | AsyncObject>>) {
        super();
    }

    override isAsyncArrayObject() {
        return true;
    }

    async getValue() {
        const variants: BaseValueObject[][] = [];

        for (let r = 0; r < this._promiseList.length; r++) {
            const promiseCells = this._promiseList[r];
            if (variants[r] == null) {
                variants[r] = [];
            }
            for (let c = 0; c < promiseCells.length; c++) {
                const promiseCell = promiseCells[c];
                if ((promiseCell as AsyncObject).isAsyncObject()) {
                    variants[r][c] = await (promiseCell as AsyncObject).getValue();
                } else {
                    variants[r][c] = promiseCell as BaseValueObject;
                }
            }
        }

        const arrayValueObjectData: IArrayValueObject = {
            calculateValueList: variants,
            rowCount: variants.length,
            columnCount: variants[0]?.length || 0,
            unitId: '',
            sheetId: '',
            row: 0,
            column: 0,
        };

        return ArrayValueObject.create(arrayValueObjectData);
    }
}