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.

10.7 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 {
    CellValue,
    DataValidationOperator,
    ICellData,
    IDataValidationRule,
    IRange,
    ISheetDataValidationRule,
    IStyleData,
    Nullable,
    Workbook,
} from '@univerjs/core';
import type { IFormulaResult, IFormulaValidResult, IValidatorCellInfo } from '@univerjs/data-validation';
import type { LocaleKey } from '../locale/types';
import {
    DataValidationRenderMode,
    DataValidationType,
    isFormulaString,
    IUniverInstanceService,
    numfmt,
    Rectangle,
    Tools,
    UniverInstanceType,
    WrapStrategy,
} from '@univerjs/core';
import {
    deserializeRangeWithSheet,
    isReferenceString,
    LexerTreeBuilder,
    sequenceNodeType,
} from '@univerjs/engine-formula';
import { deserializeListOptions } from '@univerjs/sheets';
import { DataValidationFormulaService } from '../services/dv-formula.service';
import { DataValidationListCacheService } from '../services/dv-list-cache.service';
import { getFormulaResult, isLegalFormulaResult } from '../utils/formula';
import { getCellValueOrigin } from '../utils/get-cell-data-origin';
import { BaseSheetValidator } from './base-sheet-validator';

// Keep getRuleFormulaResultSet for backward compatibility (used in isValidType)
export function getRuleFormulaResultSet(result: Nullable<Nullable<ICellData>[][]>): string[] {
    if (!result) {
        return [];
    }

    const resultSet = new Set<string>();
    for (let i = 0, rowLen = result.length; i < rowLen; i++) {
        const row = result[i];
        if (!row) continue;
        for (let j = 0, colLen = row.length; j < colLen; j++) {
            const cell = row[j];
            const value = getCellValueOrigin(cell);
            if (value !== null && value !== undefined) {
                if (typeof value !== 'string' && typeof cell?.s === 'object' && cell.s?.n?.pattern) {
                    resultSet.add(numfmt.format(cell.s.n.pattern, value, { throws: false }));
                    continue;
                }

                const valueStr = typeof value === 'string' ? value : String(value);
                if (isLegalFormulaResult(valueStr)) {
                    resultSet.add(valueStr);
                }
            }
        }
    }

    return [...resultSet];
}

const supportedFormula = [
    'if',
    'indirect',
    'choose',
    'offset',
];

// 1. must have REFERENCE or DEFINED_NAME node.
// 2. only support some formula
export function isValidListFormula(formula: string, lexer: LexerTreeBuilder) {
    if (!isFormulaString(formula)) {
        return true;
    }

    const isRefString = isReferenceString(formula.slice(1));
    if (isRefString) {
        return true;
    }

    const nodes = lexer.sequenceNodesBuilder(formula);

    return (nodes) && nodes.some((node) => typeof node === 'object' && node.nodeType === sequenceNodeType.FUNCTION && supportedFormula.indexOf(node.token.toLowerCase()) > -1);
}

function isRuleIntersects(rule: IDataValidationRule, sheetName: string) {
    const { formula1 = '', ranges } = rule;
    const isRefString = isReferenceString(formula1.slice(1));

    if (isRefString) {
        const refRange = deserializeRangeWithSheet(formula1.slice(1));
        if ((!refRange.sheetName || refRange.sheetName === sheetName) && ranges.some((range: IRange) => Rectangle.intersects(range, refRange.range))) {
            return true;
        }
    }

    return false;
}

export class ListValidator extends BaseSheetValidator {
    protected formulaService = this.injector.get(DataValidationFormulaService);
    private _lexer = this.injector.get(LexerTreeBuilder);
    private _univerInstanceService = this.injector.get(IUniverInstanceService);
    private _listCacheService = this.injector.get(DataValidationListCacheService);

    order = 50;
    override readonly offsetFormulaByRange = false;

    id: string = DataValidationType.LIST;
    title: string = 'sheets-data-validation.list.title';
    operators: DataValidationOperator[] = [];
    scopes: string | string[] = ['sheet'];

    override skipDefaultFontRender = (rule: ISheetDataValidationRule) => {
        return rule.renderMode !== DataValidationRenderMode.TEXT;
    };

    override validatorFormula(rule: IDataValidationRule, unitId: string, subUnitId: string): IFormulaValidResult {
        const success = !Tools.isBlank(rule.formula1);
        const valid = isValidListFormula(rule.formula1 ?? '', this._lexer);
        const sheetName = this._univerInstanceService.getUnit<Workbook>(unitId, UniverInstanceType.UNIVER_SHEET)?.getSheetBySheetId(subUnitId)?.getName();
        const isIntersects = isRuleIntersects(rule, sheetName ?? '');

        return {
            success: Boolean(success && valid && !isIntersects),
            formula1: success
                ? valid
                    ? !isIntersects ?
                        undefined :
                        this.localeService.t<LocaleKey>('sheets-data-validation.validFail.listIntersects') :
                    this.localeService.t<LocaleKey>('sheets-data-validation.validFail.listInvalid')
                : this.localeService.t<LocaleKey>('sheets-data-validation.validFail.list'),
        };
    }

    override getExtraStyle(rule: IDataValidationRule, value: Nullable<CellValue>, { style: defaultStyle }: { style: IStyleData }): Nullable<IStyleData> {
        const tb = (defaultStyle.tb !== WrapStrategy.OVERFLOW ? defaultStyle.tb : WrapStrategy.CLIP) ?? WrapStrategy.WRAP;
        if (rule.type === DataValidationType.LIST && (rule.renderMode === DataValidationRenderMode.ARROW || rule.renderMode === DataValidationRenderMode.TEXT)) {
            const colorMap = this.getListWithColorMap(rule);
            const valueStr = `${value ?? ''}`;
            const color = colorMap[valueStr];
            if (color) {
                return {
                    bg: {
                        rgb: color,
                    },
                    tb,
                };
            }
        }

        return {
            tb,
        };
    }

    parseCellValue(cellValue: CellValue) {
        const cellString = cellValue.toString();
        return deserializeListOptions(cellString);
    }

    override async parseFormula(rule: IDataValidationRule, unitId: string, subUnitId: string): Promise<IFormulaResult<number | undefined>> {
        const results = await this.formulaService.getRuleFormulaResult(unitId, subUnitId, rule.uid);
        const formulaResult1 = getFormulaResult(results?.[0]?.result?.[0][0]);
        const isFormulaValid = isLegalFormulaResult(String(formulaResult1));

        return {
            formula1: undefined,
            formula2: undefined,
            isFormulaValid,
        };
    }

    override async isValidType(cellInfo: IValidatorCellInfo<Nullable<CellValue>>, formula: IFormulaResult<string[] | undefined>, rule: IDataValidationRule): Promise<boolean> {
        const { value, unitId, subUnitId } = cellInfo;
        const { formula1 = '' } = rule;
        const formula1Result = isFormulaString(formula1)
            ? this._listCacheService.getOrCompute(
                unitId,
                subUnitId,
                rule
            ).list
            : deserializeListOptions(formula1);
        const selected = rule.type === DataValidationType.LIST
            ? (value === undefined || value === null || value === '' ? [] : [`${value}`])
            : this.parseCellValue(value!);
        return selected.every((i) => formula1Result.includes(i));
    }

    override generateRuleName() {
        return this.localeService.t<LocaleKey>('sheets-data-validation.list.name');
    }

    override generateRuleErrorMessage(): string {
        return this.localeService.t<LocaleKey>('sheets-data-validation.list.error');
    }

    private _getUnitAndSubUnit(currentUnitId?: string, currentSubUnitId?: string): { unitId: string; subUnitId: string } | null {
        const workbook = (currentUnitId ? this._univerInstanceService.getUnit<Workbook>(currentUnitId, UniverInstanceType.UNIVER_SHEET) : undefined)
            ?? this._univerInstanceService.getCurrentUnitOfType<Workbook>(UniverInstanceType.UNIVER_SHEET);
        if (!workbook) return null;

        const worksheet = (currentSubUnitId ? workbook.getSheetBySheetId(currentSubUnitId) : undefined) ?? workbook.getActiveSheet();
        if (!worksheet) return null;

        return {
            unitId: workbook.getUnitId(),
            subUnitId: worksheet.getSheetId(),
        };
    }

    getList(rule: IDataValidationRule, currentUnitId?: string, currentSubUnitId?: string): string[] {
        const location = this._getUnitAndSubUnit(currentUnitId, currentSubUnitId);
        if (!location) return [];

        const { unitId, subUnitId } = location;
        return this._listCacheService.getOrCompute(
            unitId,
            subUnitId,
            rule
        ).list;
    }

    async getListAsync(rule: IDataValidationRule, currentUnitId?: string, currentSubUnitId?: string) {
        const { formula1 = '' } = rule;
        const location = this._getUnitAndSubUnit(currentUnitId, currentSubUnitId);
        if (!location) return [];

        const { unitId, subUnitId } = location;
        const results = await this.formulaService.getRuleFormulaResult(unitId, subUnitId, rule.uid);
        return isFormulaString(formula1) ? getRuleFormulaResultSet(results?.[0]?.result?.[0][0]) : deserializeListOptions(formula1);
    }

    getListWithColor(rule: IDataValidationRule, currentUnitId?: string, currentSubUnitId?: string): Array<{ label: string; color: string }> {
        const location = this._getUnitAndSubUnit(currentUnitId, currentSubUnitId);
        if (!location) return [];

        const { unitId, subUnitId } = location;
        return this._listCacheService.getOrCompute(
            unitId,
            subUnitId,
            rule
        ).listWithColor;
    }

    getListWithColorMap(rule: IDataValidationRule, currentUnitId?: string, currentSubUnitId?: string): Record<string, string> {
        const location = this._getUnitAndSubUnit(currentUnitId, currentSubUnitId);
        if (!location) return {};

        const { unitId, subUnitId } = location;
        return this._listCacheService.getOrCompute(
            unitId,
            subUnitId,
            rule
        ).colorMap;
    }
}