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.
TypeScript boardcollaborationdocdocxexceloffce365officepdfpptpptxpresentationsdksheetslidesspreadsheetuniverwordwordprocessorworkspacexlsx
/**
* 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, Nullable } from '@univerjs/core';
import { CellValueType } from '@univerjs/core';
import type { FormulaCalculationCache } from '../../services/formula-cache.service';
import type { BaseReferenceObject, FunctionVariantType } from '../reference-object/base-reference-object';
import type { BaseValueObject } from '../value-object/base-value-object';
import { ERROR_TYPE_SET, ErrorType } from '../../basics/error-type';
import { compareToken } from '../../basics/token';
import { CellReferenceObject } from '../reference-object/cell-reference-object';
import { ColumnReferenceObject } from '../reference-object/column-reference-object';
import { RowReferenceObject } from '../reference-object/row-reference-object';
import { ArrayValueObject } from '../value-object/array-value-object';
import { ErrorValueObject } from '../value-object/base-value-object';
import { BooleanValueObject, NumberValueObject } from '../value-object/primitive-object';
import { expandArrayValueObject } from './array-object';
import { isWildcard, normalizeTextForComparison } from './compare';
import { stripErrorMargin } from './math-kit';
import {
booleanObjectIntersection,
createCriteriaValueObject,
findCompareToken,
valueObjectCompare,
} from './object-compare';
export function convertTonNumber(valueObject: BaseValueObject) {
const currentValue = valueObject.getValue();
let result = 0;
if (currentValue) {
result = 1;
}
return NumberValueObject.create(result);
}
export function isSingleValueObject(valueObject: FunctionVariantType) {
if (valueObject.isArray() && (valueObject as ArrayValueObject).getRowCount() === 1 && (valueObject as ArrayValueObject).getColumnCount() === 1) {
return true;
}
if (valueObject.isReferenceObject()) {
if ((valueObject as BaseReferenceObject).isCell()) {
return true;
}
if ((valueObject as BaseReferenceObject).getRowCount() === 1 && (valueObject as BaseReferenceObject).getColumnCount() === 1) {
return true;
}
return false;
}
valueObject = valueObject as BaseValueObject;
if (valueObject.isString() || valueObject.isNumber() || valueObject.isBoolean() || valueObject.isError() || valueObject.isNull()) {
return true;
}
return false;
}
/**
* Covert BaseValueObject to cell value
* @param objectValue
* @returns
*/
export function objectValueToCellValue(objectValue: Nullable<BaseValueObject>): ICellData | undefined {
const pattern = objectValue?.getPattern();
let cellWithStyle: ICellData = {};
let cellWithCustomData: ICellData = {};
if (pattern) {
cellWithStyle = {
s: {
n: {
pattern,
},
},
};
}
if (objectValue?.getCustomData()) {
cellWithCustomData = {
custom: objectValue.getCustomData(),
};
}
if (objectValue == null) {
return {
v: null,
...cellWithStyle,
};
}
if (objectValue.isError()) {
return {
v: (objectValue as ErrorValueObject).getErrorType() as string,
t: CellValueType.STRING,
...cellWithStyle,
...cellWithCustomData,
};
}
if (objectValue.isValueObject()) {
const vo = objectValue as BaseValueObject;
const v = vo.getValue();
if (vo.isNumber()) {
return {
v,
t: CellValueType.NUMBER,
...cellWithStyle,
...cellWithCustomData,
};
}
if (vo.isBoolean()) {
return {
v: v ? 1 : 0,
t: CellValueType.BOOLEAN,
...cellWithStyle,
...cellWithCustomData,
};
}
// Error-looking text must remain distinct from actual errors when read back.
if (vo.isString()) {
return {
v,
t: ERROR_TYPE_SET.has(v as ErrorType) ? CellValueType.FORCE_STRING : CellValueType.STRING,
...cellWithStyle,
...cellWithCustomData,
};
}
if (vo.isNull()) {
return {
v: null,
...cellWithStyle,
...cellWithCustomData,
};
}
return {
v,
t: CellValueType.STRING,
...cellWithStyle,
...cellWithCustomData,
};
}
}
/**
* The size of the extended range is determined by the maximum width and height of the criteria range.
* @param variants
* @returns
*/
export function calculateMaxDimensions(variants: BaseValueObject[]) {
let maxRowLength = 0;
let maxColumnLength = 0;
variants.forEach((variant, i) => {
if (i % 2 === 1) {
if (variant.isArray()) {
const arrayValue = variant as ArrayValueObject;
maxRowLength = Math.max(maxRowLength, arrayValue.getRowCount());
maxColumnLength = Math.max(maxColumnLength, arrayValue.getColumnCount());
} else {
maxRowLength = Math.max(maxRowLength, 1);
maxColumnLength = Math.max(maxColumnLength, 1);
}
}
});
return { maxRowLength, maxColumnLength };
}
/**
* Parse the paired range and criteria in functions like COUNTIFS, SUMIFS, etc.
* @param variants - The range and criteria pairs
* @param targetRange - The target range for calculation (e.g., sumRange in SUMIFS)
* @returns An object containing parsed information
*/
// eslint-disable-next-line max-lines-per-function
export function parsePairedRangeAndCriteria(
variants: FunctionVariantType[],
targetRange?: FunctionVariantType
): {
isError: boolean;
errorObject: ErrorValueObject | null;
rangeIsDifferentSize: boolean;
criteriaMaxRowLength: number;
criteriaMaxColumnLength: number;
targetRange: ArrayValueObject | null;
variants: BaseValueObject[];
} {
/**
* The range and criteria must be in pairs.
* If not, Excel will prevent the operation. But we can't do it like that, so we just return a #VALUE! error.
*/
if (variants.length === 0 || variants.length % 2 !== 0) {
return {
isError: true,
errorObject: ErrorValueObject.create(ErrorType.VALUE),
rangeIsDifferentSize: false,
criteriaMaxRowLength: 0,
criteriaMaxColumnLength: 0,
targetRange: null,
variants: [],
};
}
let _targetRange: ArrayValueObject | null = null;
let targetRangeRowCount = -1;
let targetRangeColumnCount = -1;
if (targetRange) {
/**
* If the target range is provided, it must be a reference object.
* If not, Excel will prevent the operation. But we can't do it like that, so we just return a #VALUE! error.
*/
if (!targetRange.isReferenceObject()) {
return {
isError: true,
errorObject: ErrorValueObject.create(ErrorType.VALUE),
rangeIsDifferentSize: false,
criteriaMaxRowLength: 0,
criteriaMaxColumnLength: 0,
targetRange: null,
variants: [],
};
}
_targetRange = (targetRange as BaseReferenceObject).toArrayValueObject();
targetRangeRowCount = (_targetRange as ArrayValueObject).getRowCount();
targetRangeColumnCount = (_targetRange as ArrayValueObject).getColumnCount();
}
let criteriaMaxRowLength = 0;
let criteriaMaxColumnLength = 0;
let rangeIsDifferentSize = false;
const _variants: BaseValueObject[] = [];
for (let i = 0; i < variants.length; i++) {
if (i % 2 === 1) {
const range = variants[i - 1];
const criteria = variants[i];
/**
* The range must be a reference object.
* If not, Excel will prevent the operation. But we can't do it like that, so we just return a #VALUE! error.
*/
if (!range.isReferenceObject()) {
return {
isError: true,
errorObject: ErrorValueObject.create(ErrorType.VALUE),
rangeIsDifferentSize: false,
criteriaMaxRowLength: 0,
criteriaMaxColumnLength: 0,
targetRange: null,
variants: [],
};
}
const _range = (range as BaseReferenceObject).toArrayValueObject();
const rangeRowCount = _range.getRowCount();
const rangeColumnCount = _range.getColumnCount();
if (i === 1 && targetRangeRowCount === -1 && targetRangeColumnCount === -1) {
// If the target range is not specified, use the first range as the target range. e.g. COUNTIFS(A1:A3, ">1", B1:B3, "<5").
targetRangeRowCount = rangeRowCount;
targetRangeColumnCount = rangeColumnCount;
} else if (!rangeIsDifferentSize && (targetRangeRowCount !== rangeRowCount || targetRangeColumnCount !== rangeColumnCount)) {
// The size of each range must be the same as the target range
rangeIsDifferentSize = true;
}
let _criteria = criteria;
if (criteria.isReferenceObject()) {
_criteria = (criteria as BaseReferenceObject).toArrayValueObject();
}
criteriaMaxRowLength = Math.max(criteriaMaxRowLength, _criteria.isArray() ? (_criteria as ArrayValueObject).getRowCount() : 1);
criteriaMaxColumnLength = Math.max(criteriaMaxColumnLength, _criteria.isArray() ? (_criteria as ArrayValueObject).getColumnCount() : 1);
_variants.push(_range);
_variants.push(_criteria as BaseValueObject);
}
}
return {
isError: false,
errorObject: null,
rangeIsDifferentSize,
criteriaMaxRowLength,
criteriaMaxColumnLength,
targetRange: _targetRange,
variants: _variants,
};
}
export function baseValueObjectToArrayValueObject(valueObject: BaseValueObject): ArrayValueObject {
if (valueObject.isArray()) {
return valueObject as ArrayValueObject;
}
return ArrayValueObject.createByArray([[valueObject.getValue()]]);
}
function getEqualityValueKeys(value: BaseValueObject): Array<string | number> {
const keys: Array<string | number> = [];
if (value.isString()) {
keys.push(normalizeTextForComparison(String(value.getValue())));
} else if (!value.isNumber()) {
return keys;
}
const number = value.isNumber() ? value : createCriteriaValueObject(String(value.getValue()), value.getDateSystem());
if (number.isNumber() && Number.isFinite(Number(number.getValue()))) {
keys.push(stripErrorMargin(Number(number.getValue())));
}
return keys;
}
type EqualityIndex = Map<string | number, Array<[number, number]>>;
/**
* Equality indexes keyed by the calculation pass they were built for.
*
* Range arrays are cached per calculation pass and are not mutated while the pass is
* running, so one index can serve every formula that reads the same range. Keying the
* outer map by the pass cache makes the whole entry disappear together with the pass,
* which is also why no change tracking is needed.
*/
const passEqualityIndexes = new WeakMap<FormulaCalculationCache, Map<ArrayValueObject, EqualityIndex>>();
function getOrBuildEqualityIndex(
range: ArrayValueObject,
localIndexes: Map<ArrayValueObject, EqualityIndex>
): EqualityIndex {
const calculationCache = range.getCalculationCache();
if (calculationCache == null || calculationCache.closed) {
let index = localIndexes.get(range);
if (index === undefined) {
index = buildEqualityIndex(range);
localIndexes.set(range, index);
}
return index;
}
let indexes = passEqualityIndexes.get(calculationCache);
if (indexes === undefined) {
indexes = new Map();
passEqualityIndexes.set(calculationCache, indexes);
}
let index = indexes.get(range);
if (index === undefined) {
index = buildEqualityIndex(range);
indexes.set(range, index);
}
return index;
}
function buildEqualityIndex(range: ArrayValueObject): EqualityIndex {
const index: EqualityIndex = new Map();
range.iterator((value, row, column) => {
if (!value) {
return;
}
const position: [number, number] = [row, column];
for (const key of getEqualityValueKeys(value)) {
let matches = index.get(key);
if (!matches) {
matches = [];
index.set(key, matches);
}
matches.push(position);
}
});
return index;
}
function getIndexedPositions(
index: EqualityIndex,
keys: Array<string | number>,
range: ArrayValueObject
): Array<[number, number]> | undefined {
const buckets = keys.map((key) => index.get(key) ?? []);
const candidateCount = buckets.reduce((count, bucket) => count + bucket.length, 0);
// ponytail: conservatively bound candidate copies; refine overlapping buckets only if profiling warrants it.
if (candidateCount >= range.getRowCount() * range.getColumnCount() / 2) {
return undefined;
}
if (buckets.length === 1) {
return buckets[0];
}
return Array.from(new Set(buckets.flat())).sort((a, b) => a[0] - b[0] || a[1] - b[1]);
}
function pickRangePositions(range: ArrayValueObject, positions: Array<[number, number]>): ArrayValueObject {
return ArrayValueObject.create({
calculateValueList: positions.map(([row, column]) => [range.get(row, column)]),
rowCount: positions.length,
columnCount: 1,
unitId: '',
sheetId: '',
row: -1,
column: -1,
});
}
function getExactCriteriaKeys(criteria: Nullable<BaseValueObject>): Array<string | number> | undefined {
if (!criteria || (!criteria.isString() && !criteria.isNumber())) {
return undefined;
}
const [operator, value] = criteria.isString()
? findCompareToken(String(criteria.getValue()), criteria.getDateSystem())
: [compareToken.EQUALS, criteria];
if (operator !== compareToken.EQUALS || value.isDateFormat()) {
return undefined;
}
// Blanks and wildcard patterns retain the full comparison path.
if (value.isString() && (value.getValue() === '' || isWildcard(String(value.getValue())))) {
return undefined;
}
const keys = getEqualityValueKeys(value);
return keys.length > 0 ? keys : undefined;
}
/**
* Upper bound of the repeated-criteria cache. Repeated criteria are few in practice
* (blank cells, a handful of keys); the bound guards against a criteria array that is
* entirely made of distinct values.
*/
const MAX_REPEATED_CRITERIA_RESULTS = 4096;
/**
* Build a cache key for one tuple of criteria values.
*
* Every value kind gets its own tagged key instead of requiring an exact equality
* match, so repeated criteria — typically the trailing blank cells of a whole-column
* criteria reference — reuse a single evaluation instead of rescanning the range once
* per cell.
*/
function getCriteriaCacheKey(criteria: Array<Nullable<BaseValueObject>>): string {
let key = '';
for (const value of criteria) {
const segment = getCriteriaKeySegment(value);
// Length-prefix every segment: criteria text may contain the separator, and an
// ambiguous join would let two different tuples share one cache entry.
key += `${segment.length}:${segment}`;
}
return key;
}
function getCriteriaKeySegment(value: Nullable<BaseValueObject>): string {
if (value == null || value.isNull()) {
return 'n';
}
if (value.isBoolean()) {
return `b:${value.getValue()}`;
}
if (value.isNumber()) {
const pattern = value.getPattern();
// Keep the exact double. Every comparison is a function of it, but two values
// that `stripErrorMargin` folds together can still compare differently: date
// comparisons round the serial at 1e-8 before comparing, so 45000.00000000499
// and 45000.000000005006 land on different dates.
const serial = String(value.getValue());
// A date-formatted serial also compares differently from a plain number of the
// same value, so the pattern is part of the key.
return pattern ? `#:${pattern}:${serial}` : `#:${serial}`;
}
if (value.isError()) {
return `e:${value.getValue()}`;
}
// Text criteria may be parsed as dates, which depends on the date system.
return `s:${value.getDateSystem()}:${value.getValue()}`;
}
function getCriteriaPositions(
pairs: Array<{ range: ArrayValueObject; criteriaArray: ArrayValueObject }>,
row: number,
column: number,
localIndexes: Map<ArrayValueObject, EqualityIndex>
): Array<[number, number]> | undefined {
for (const { range, criteriaArray } of pairs) {
const keys = getExactCriteriaKeys(criteriaArray.get(row, column));
if (!keys) {
continue;
}
const positions = getIndexedPositions(getOrBuildEqualityIndex(range, localIndexes), keys, range);
// ponytail: stop at the first selective index; compare all criteria on its candidates.
if (positions !== undefined) {
return positions;
}
}
}
function getConditionalAggregateResult(
formulaName: string,
matchedCount: number,
picked?: ArrayValueObject
): BaseValueObject | undefined {
if (formulaName === 'COUNTIFS') {
return NumberValueObject.create(matchedCount);
}
switch (formulaName) {
case 'SUMIFS':
return picked!.sum();
case 'AVERAGEIFS':
return picked!.sum().divided(picked!.count());
case 'MAXIFS':
return picked!.getColumnCount() === 0 ? NumberValueObject.create(0) : picked!.max();
case 'MINIFS':
return picked!.getColumnCount() === 0 ? NumberValueObject.create(0) : picked!.min();
}
}
/**
* Get the paired range and criteria result for COUNTIFS, SUMIFS, etc.
*/
// eslint-disable-next-line max-lines-per-function
export function getPairedRangeAndCriteriaResult(
variants: BaseValueObject[],
params: {
formulaName: string;
maxRowLength: number;
maxColumnLength: number;
isNumberSensitive?: boolean;
targetRange?: ArrayValueObject;
}
) {
const { formulaName, maxRowLength, maxColumnLength, isNumberSensitive = false, targetRange } = params;
// Align all criteria values to the same dimension as the maximum criteria range.
const rangeAndCriteriaArrays: Array<{
range: ArrayValueObject;
criteriaArray: ArrayValueObject;
}> = [];
for (let i = 0; i < variants.length; i++) {
if (i % 2 === 1) {
continue;
}
const range = variants[i] as ArrayValueObject;
const criteria = variants[i + 1];
const criteriaArray = expandArrayValueObject(maxRowLength, maxColumnLength, criteria, ErrorValueObject.create(ErrorType.NA));
rangeAndCriteriaArrays.push({
range,
criteriaArray,
});
}
if (rangeAndCriteriaArrays.length === 0) {
return [];
}
const results: BaseValueObject[][] = [];
const arrayPairs = rangeAndCriteriaArrays.filter((_, index) => {
const criteria = variants[index * 2 + 1];
return criteria.isArray() &&
(criteria as ArrayValueObject).getRowCount() * (criteria as ArrayValueObject).getColumnCount() > 1;
});
const hasArrayResult = maxRowLength * maxColumnLength > 1;
const useEqualityIndex = isNumberSensitive && hasArrayResult && arrayPairs.length > 0;
// A single cell result is evaluated once, so caching repeated criteria only pays off for arrays.
const useRepeatedCriteria = hasArrayResult && arrayPairs.length > 0;
const repeatedResults = new Map<string, BaseValueObject>();
const localIndexes = new Map<ArrayValueObject, EqualityIndex>();
/**
* Iterate through all criteria values for each dimension, calculate the comparison result with the corresponding range, and then calculate the Boolean intersection of all comparison results as the final result for that dimension criteria value.
* Then calculate this dimension criteria value's result.
* This avoiding store the all dimension comparison result in memory and then calculating all dimension criteria value's result, which may cause memory overflow when the range is large and there are multiple dimension criteria.
* For example, `=COUNTIFS(Q$3:Q$10002,C$3:C$5002,R$3:R$10002,L6)`.
*/
const firstPair = rangeAndCriteriaArrays[0];
firstPair.criteriaArray.iterator((_, rowIndex, columnIndex) => {
let finalCompareResult: ArrayValueObject | undefined;
let positions: Array<[number, number]> | undefined;
const cacheKey = useRepeatedCriteria
? getCriteriaCacheKey(arrayPairs.map(({ criteriaArray }) => criteriaArray.get(rowIndex, columnIndex)))
: undefined;
const cached = cacheKey === undefined ? undefined : repeatedResults.get(cacheKey);
if (cached !== undefined) {
results[rowIndex] ??= [];
results[rowIndex][columnIndex] = cached;
return;
}
if (useEqualityIndex) {
positions = getCriteriaPositions(arrayPairs, rowIndex, columnIndex, localIndexes);
if (positions?.length === 0) {
results[rowIndex] ??= [];
results[rowIndex][columnIndex] = formulaName === 'AVERAGEIFS'
? ErrorValueObject.create(ErrorType.DIV_BY_ZERO)
: NumberValueObject.create(0);
return;
}
}
/**
* Indexed positions already are the match set, so a single pair can be aggregated
* without materializing the comparison mask or picking through it.
*/
if (positions !== undefined && rangeAndCriteriaArrays.length === 1) {
const picked = targetRange ? pickRangePositions(targetRange, positions) : undefined;
const result = getConditionalAggregateResult(formulaName, positions.length, picked);
results[rowIndex] ??= [];
results[rowIndex][columnIndex] = result as BaseValueObject;
if (cacheKey !== undefined && result && repeatedResults.size < MAX_REPEATED_CRITERIA_RESULTS) {
repeatedResults.set(cacheKey, result);
}
return;
}
rangeAndCriteriaArrays.forEach(({ range: sourceRange, criteriaArray }) => {
// Preserve paired row/column offsets and reuse the existing comparison semantics.
const range = positions === undefined ? sourceRange : pickRangePositions(sourceRange, positions);
const criteriaValueObject = criteriaArray.get(rowIndex, columnIndex);
if (!criteriaValueObject) {
return;
}
// range must be an ArrayValueObject, criteria must be a BaseValueObject
let compareResult = valueObjectCompare(range, criteriaValueObject) as ArrayValueObject;
// When comparing non-numbers and numbers, countifs does not take the result
if (isNumberSensitive) {
compareResult = filterSameValueObjectResult(compareResult, range, criteriaValueObject);
}
if (finalCompareResult === undefined) {
finalCompareResult = compareResult;
return;
}
finalCompareResult = booleanObjectIntersection(finalCompareResult, compareResult);
});
const aggregateRange = positions === undefined || !targetRange ? targetRange : pickRangePositions(targetRange, positions);
let matchedCount = 0;
if (formulaName === 'COUNTIFS') {
(finalCompareResult as ArrayValueObject).iterator((value) => {
if (value?.isBoolean() && value.getValue() === true) {
matchedCount++;
}
});
}
const picked = formulaName === 'COUNTIFS' || aggregateRange === undefined
? undefined
: aggregateRange.pick(finalCompareResult as ArrayValueObject);
const result = getConditionalAggregateResult(formulaName, matchedCount, picked);
if (!results[rowIndex]) {
results[rowIndex] = [];
}
results[rowIndex][columnIndex] = result as BaseValueObject;
if (cacheKey !== undefined && result && repeatedResults.size < MAX_REPEATED_CRITERIA_RESULTS) {
repeatedResults.set(cacheKey, result);
}
});
return results;
}
/**
* Two ArrayValueObject of the same type can be compared
*/
export function filterSameValueObjectResult(
array: ArrayValueObject,
range: ArrayValueObject,
criteria: BaseValueObject
): ArrayValueObject {
const [operator, criteriaObject] = criteria.isString()
? findCompareToken(`${criteria.getValue()}`, criteria.getDateSystem())
: [compareToken.EQUALS, criteria];
return array.mapValue((valueObject, r, c) => {
const rangeValueObject = range.get(r, c);
const isBlankStringCriteria = criteriaObject.isString() && criteriaObject.getValue() === '';
if (
isBlankStringCriteria &&
(
operator === compareToken.GREATER_THAN ||
operator === compareToken.GREATER_THAN_OR_EQUAL ||
operator === compareToken.LESS_THAN ||
operator === compareToken.LESS_THAN_OR_EQUAL
)
) {
return BooleanValueObject.create(false);
}
if (operator === compareToken.NOT_EQUAL && isBlankStringCriteria && rangeValueObject?.isString() && rangeValueObject.getValue() === '') {
return BooleanValueObject.create(true);
}
if (
criteriaObject.isString() &&
isWildcard(`${criteriaObject.getValue()}`) &&
rangeValueObject?.isString() &&
rangeValueObject.getValue() === ''
) {
return valueObject;
}
if (
criteriaObject.isString() &&
criteriaObject.getValue() !== '' &&
(operator === compareToken.LESS_THAN || operator === compareToken.LESS_THAN_OR_EQUAL) &&
rangeValueObject?.isString() &&
rangeValueObject.getValue() === ''
) {
return BooleanValueObject.create(true);
}
if (
criteriaObject.isString() &&
criteriaObject.getValue() !== '' &&
(operator === compareToken.LESS_THAN || operator === compareToken.LESS_THAN_OR_EQUAL) &&
(rangeValueObject == null || rangeValueObject.isNull())
) {
return BooleanValueObject.create(false);
}
if (
criteriaObject.isString() &&
rangeValueObject?.isString() &&
(operator === compareToken.LESS_THAN || operator === compareToken.LESS_THAN_OR_EQUAL) &&
isSameLowerBoundBucket(rangeValueObject.getValue() as string, criteriaObject.getValue() as string)
) {
return BooleanValueObject.create(false);
}
if (rangeValueObject?.isNumber() && criteriaObject.isString()) {
const criteriaNumber = createCriteriaValueObject(`${criteriaObject.getValue()}`, criteriaObject.getDateSystem());
if (criteriaNumber.isNumber()) {
return rangeValueObject.compare(criteriaNumber, operator);
}
}
if (operator !== compareToken.NOT_EQUAL && criteriaObject.isNumber() && criteriaObject.isDateFormat() && rangeValueObject?.isString()) {
const rangeNumber = rangeValueObject.convertToNumberObjectValue();
if (rangeNumber.isNumber()) {
return rangeNumber.compare(criteriaObject, operator);
}
}
if (rangeValueObject && isSameValueObjectType(rangeValueObject, criteriaObject)) {
return valueObject;
}
if (rangeValueObject?.isError() && criteriaObject.isError() && rangeValueObject.getValue() === criteriaObject.getValue()) {
return BooleanValueObject.create(true);
}
/**
* Equality criteria coerce numeric text; numeric inequality criteria
* retain the distinction between text cells and numeric cells.
* Other operators require both valueObjects to be of the same type.
* For example:
* | A1 | B1 |
* | '123' | 123 |
*
* =COUNTIF(A1:B1, '=123') will return 2
* =COUNTIF(A1:B1, '<>1') will return 2
* =COUNTIF(A1:B1, '>1') will return 1
* =COUNTIF(A1:B1, '<=123') will return 1
*/
if (operator === compareToken.EQUALS || operator === compareToken.NOT_EQUAL) {
if (operator === compareToken.EQUALS && criteriaObject.isNumber() && rangeValueObject?.isString()) {
const rangeNumber = createCriteriaValueObject(`${rangeValueObject.getValue()}`, rangeValueObject.getDateSystem());
if (rangeNumber.isNumber()) {
return rangeNumber.compare(criteriaObject, operator);
}
}
if (operator === compareToken.EQUALS) {
return BooleanValueObject.create(false);
}
if (operator === compareToken.NOT_EQUAL) {
return BooleanValueObject.create(true);
}
}
return BooleanValueObject.create(false);
});
}
function isSameLowerBoundBucket(rangeValue: string, criteriaValue: string): boolean {
const criteriaMatch = criteriaValue.match(/^(\d+)\|/);
const rangeMatch = rangeValue.match(/^(\d+)-/);
return criteriaMatch != null && rangeMatch != null && criteriaMatch[1] === rangeMatch[1];
}
/**
* Check if the two valueObjects are of the same type
* @param left
* @param right
* @returns
*/
export function isSameValueObjectType(left: BaseValueObject, right: BaseValueObject) {
if (left.isNumber() && right.isNumber()) {
return true;
}
if (left.isBoolean() && right.isBoolean()) {
return true;
}
// blank string is same as a blank cell
const isLeftBlank = left.isString() && left.getValue() === '';
const isRightBlank = right.isString() && right.getValue() === '';
if ((isLeftBlank || left.isNull()) && (isRightBlank || right.isNull())) {
return true;
}
if (left.isString() && !isLeftBlank && right.isString() && !isRightBlank) {
return true;
}
return false;
}
export enum ReferenceObjectType {
CELL,
COLUMN,
ROW,
}
export function getReferenceObjectFromCache(trimToken: string, type: ReferenceObjectType) {
let referenceObject: BaseReferenceObject;
switch (type) {
case ReferenceObjectType.COLUMN:
referenceObject = new ColumnReferenceObject(trimToken);
break;
case ReferenceObjectType.ROW:
referenceObject = new RowReferenceObject(trimToken);
break;
case ReferenceObjectType.CELL:
default:
referenceObject = new CellReferenceObject(trimToken);
break;
}
return referenceObject;
}
export function getRangeReferenceObjectFromCache(variant1: BaseReferenceObject, variant2: BaseReferenceObject) {
let referenceObject: FunctionVariantType;
if (variant1.isCell() && variant2.isCell()) {
referenceObject = variant1.unionBy(variant2) as BaseReferenceObject;
} else if (variant1.isRow() && variant2.isRow()) {
referenceObject = variant1.unionBy(variant2) as BaseReferenceObject;
} else if (variant1.isColumn() && variant2.isColumn()) {
referenceObject = variant1.unionBy(variant2) as BaseReferenceObject;
} else {
referenceObject = ErrorValueObject.create(ErrorType.NAME);
}
return referenceObject;
}