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 { IDisposable, IMutationInfo, IRange, Workbook } from '@univerjs/core';
import type { EffectRefRangeParams, IInsertColCommandParams, IInsertRowCommandParams } from '@univerjs/sheets';
import { AbsoluteRefType, Disposable, DisposableCollection, getIntersectRange, Inject, Injector, isFormulaString, IUniverInstanceService, moveRangeByOffset, RANGE_TYPE, Rectangle, UniverInstanceType } from '@univerjs/core';
import { deserializeRangeWithSheetWithCache, ErrorType, generateStringWithSequence, LexerTreeBuilder, sequenceNodeType, serializeRange, serializeRangeWithSheet, serializeRangeWithSpreadsheet } from '@univerjs/engine-formula';
import { EffectRefRangId, getSeparateEffectedRangesOnCommand, handleCommonDefaultRangeChangeWithEffectRefCommands, handleDefaultRangeChangeWithEffectRefCommands, RefRangeService } from '@univerjs/sheets';
export type FormulaChangeMap = Record<string, Record<string, Record<string, string>>>;
export type FormulaChangeCallback = (formulaString: string) => {
redos: IMutationInfo[];
undos: IMutationInfo[];
};
export type RangeFormulaChangeCallback = (infos: { formulas: string[]; ranges: IRange[] }[]) => {
redos: IMutationInfo[];
undos: IMutationInfo[];
};
interface IRangeFormulaItem {
formulas: { newFormula: string; orginFormula: string }[];
ranges: IRange[];
key: string;
}
/**
* An inserted row or column that lands inside a registered range stays part of that range, the
* same way {@link handleCommonDefaultRangeChangeWithEffectRefCommands} expands a range when the
* insertion happens inside it. `registerRangeFormula` splits a range into the segments that are
* affected by a command, and an inserted block is not part of any segment, so it has to be
* collected separately, otherwise the inserted cells would silently drop out of the range.
*/
function getInsertedBlocks(command: EffectRefRangeParams, ranges: IRange[]): IRange[] {
const params = command.params as IInsertRowCommandParams | IInsertColCommandParams | undefined;
if (!params?.range) {
return [];
}
switch (command.id) {
case EffectRefRangId.InsertRowCommandId: {
const { startRow, endRow } = params.range;
return ranges
.filter((range) => startRow > range.startRow && startRow <= range.endRow)
.map((range) => ({
startRow,
endRow,
startColumn: range.startColumn,
endColumn: range.endColumn,
}));
}
case EffectRefRangId.InsertColCommandId: {
const { startColumn, endColumn } = params.range;
return ranges
.filter((range) => startColumn > range.startColumn && startColumn <= range.endColumn)
.map((range) => ({
startRow: range.startRow,
endRow: range.endRow,
startColumn,
endColumn,
}));
}
default:
return [];
}
}
function getFormulaKeyOffset(lexerTreeBuilder: LexerTreeBuilder, formulaString: string, refOffsetX: number, refOffsetY: number) {
const sequenceNodes = lexerTreeBuilder.sequenceNodesBuilder(formulaString);
if (sequenceNodes == null) {
return formulaString;
}
const newSequenceNodes: Array<{ unitId: string; sheetName: string; range: IRange }> = [];
for (let i = 0, len = sequenceNodes.length; i < len; i++) {
const node = sequenceNodes[i];
if (typeof node === 'string' || node.nodeType !== sequenceNodeType.REFERENCE) {
continue;
}
const { token } = node;
const sequenceGrid = deserializeRangeWithSheetWithCache(token);
const { sheetName, unitId: sequenceUnitId } = sequenceGrid;
let newRange: IRange = sequenceGrid.range;
if (newRange.startAbsoluteRefType === AbsoluteRefType.ALL && newRange.endAbsoluteRefType === AbsoluteRefType.ALL) {
continue;
} else {
newRange = moveRangeByOffset(newRange, refOffsetX, refOffsetY);
}
newSequenceNodes.push({
unitId: sequenceUnitId,
sheetName,
range: newRange,
});
}
return newSequenceNodes.map((item) => `${item.unitId}!${item.sheetName}!${item.range.startRow}!${item.range.endRow}!${item.range.startColumn}!${item.range.endColumn}`).join('|');
}
export class FormulaRefRangeService extends Disposable {
constructor(
@Inject(RefRangeService) private readonly _refRangeService: RefRangeService,
@Inject(LexerTreeBuilder) private readonly _lexerTreeBuilder: LexerTreeBuilder,
@IUniverInstanceService private readonly _univerInstanceService: IUniverInstanceService,
@Inject(Injector) private readonly _injector: Injector
) {
super();
}
transformFormulaByEffectCommand(unitId: string, subUnitId: string, formula: string, params: EffectRefRangeParams) {
const sequenceNodes = this._lexerTreeBuilder.sequenceNodesBuilder(formula);
const currentUnit = this._univerInstanceService.getCurrentUnitOfType<Workbook>(UniverInstanceType.UNIVER_SHEET)!;
const currentSheet = currentUnit.getActiveSheet();
const currentUnitId = currentUnit.getUnitId();
const currentSheetId = currentSheet.getSheetId();
const transformSequenceNodes = sequenceNodes?.map((node) => {
if (typeof node === 'object' && node.nodeType === sequenceNodeType.REFERENCE) {
const gridRangeName = deserializeRangeWithSheetWithCache(node.token);
const { range, unitId: rangeUnitId, sheetName: rangeSheetName } = gridRangeName;
const workbook = this._univerInstanceService.getUnit<Workbook>(rangeUnitId || unitId);
const worksheet = rangeSheetName ? workbook?.getSheetBySheetName(rangeSheetName) : workbook?.getSheetBySheetId(subUnitId);
if (!worksheet) {
throw new Error('Sheet not found');
}
const realUnitId = workbook!.getUnitId();
const realSheetId = worksheet.getSheetId();
if ((realUnitId !== currentUnitId) || (realSheetId !== currentSheetId)) {
return node;
}
const newRange = handleDefaultRangeChangeWithEffectRefCommands(range, params);
let newToken = '';
if (newRange) {
const offsetX = newRange.startColumn - range.startColumn;
const offsetY = newRange.startRow - range.startRow;
const finalRange = moveRangeByOffset(range, offsetX, offsetY);
if (rangeUnitId && rangeSheetName) {
newToken = serializeRangeWithSpreadsheet(rangeUnitId, rangeSheetName, finalRange);
} else if (rangeSheetName) {
newToken = serializeRangeWithSheet(rangeSheetName, finalRange);
} else {
newToken = serializeRange(finalRange);
}
} else {
newToken = ErrorType.REF;
}
return {
...node,
token: newToken,
};
} else {
return node;
};
});
return transformSequenceNodes ? `=${generateStringWithSequence(transformSequenceNodes)}` : '';
}
registerFormula(unitId: string, subUnitId: string, formula: string, callback: FormulaChangeCallback): IDisposable {
const rangeMap = new Map<string, { unitId: string; subUnitId: string; range: IRange; sheetName: string }>();
const sequenceNodes = this._lexerTreeBuilder.sequenceNodesBuilder(formula);
const disposableCollection = new DisposableCollection();
const handleChange = (params: EffectRefRangeParams) => {
const currentUnit = this._univerInstanceService.getCurrentUnitOfType<Workbook>(UniverInstanceType.UNIVER_SHEET)!;
const currentSheet = currentUnit.getActiveSheet();
const currentUnitId = currentUnit.getUnitId();
const currentSheetId = currentSheet.getSheetId();
const transformSequenceNodes = sequenceNodes?.map((node) => {
if (typeof node === 'object' && node.nodeType === sequenceNodeType.REFERENCE) {
const rangeInfo = rangeMap.get(node.token)!;
if ((rangeInfo.unitId !== currentUnitId) || (rangeInfo.subUnitId !== currentSheetId)) {
return node;
}
const newRange = handleDefaultRangeChangeWithEffectRefCommands(rangeInfo.range, params);
let newToken = '';
if (newRange) {
const offsetX = newRange.startColumn - rangeInfo.range.startColumn;
const offsetY = newRange.startRow - rangeInfo.range.startRow;
const finalRange = moveRangeByOffset(rangeInfo.range, offsetX, offsetY);
if (rangeInfo.unitId && rangeInfo.sheetName) {
newToken = serializeRangeWithSpreadsheet(rangeInfo.unitId, rangeInfo.sheetName, finalRange);
} else if (rangeInfo.sheetName) {
newToken = serializeRangeWithSheet(rangeInfo.sheetName, finalRange);
} else {
newToken = serializeRange(finalRange);
}
} else {
newToken = ErrorType.REF;
}
return {
...node,
token: newToken,
};
} else {
return node;
};
});
const newFormulaString = transformSequenceNodes && generateStringWithSequence(transformSequenceNodes);
return callback(`=${newFormulaString}`);
};
sequenceNodes?.forEach((node) => {
if (typeof node === 'object' && node.nodeType === sequenceNodeType.REFERENCE) {
const gridRangeName = deserializeRangeWithSheetWithCache(node.token);
const { range, unitId: rangeUnitId, sheetName: rangeSheetName } = gridRangeName;
const workbook = this._univerInstanceService.getUnit<Workbook>(rangeUnitId || unitId);
const worksheet = rangeSheetName ? workbook?.getSheetBySheetName(rangeSheetName) : workbook?.getSheetBySheetId(subUnitId);
if (!worksheet) {
return;
}
const realUnitId = workbook!.getUnitId();
const realSheetId = worksheet.getSheetId();
const item = {
unitId: realUnitId,
subUnitId: realSheetId,
range,
sheetName: rangeSheetName,
};
rangeMap.set(node.token, item);
disposableCollection.add(this._refRangeService.registerRefRange(range, handleChange, realUnitId, realSheetId));
}
});
return disposableCollection;
}
private _getFormulaDependcy(unitId: string, subUnitId: string, formula: string | undefined, ranges: IRange[]) {
const nodes = isFormulaString(formula) ? this._lexerTreeBuilder.sequenceNodesBuilder(formula!) : null;
const dependencyRanges: { unitId: string; subUnitId: string; ranges: IRange[] }[] = [];
nodes?.forEach((node) => {
if (typeof node === 'object' && node.nodeType === sequenceNodeType.REFERENCE) {
const gridRangeName = deserializeRangeWithSheetWithCache(node.token);
const { range, unitId: rangeUnitId, sheetName: rangeSheetName } = gridRangeName;
// ignore all absolute reference
if (range.startAbsoluteRefType === AbsoluteRefType.ALL && range.endAbsoluteRefType === AbsoluteRefType.ALL) {
return;
}
const workbook = this._univerInstanceService.getUnit<Workbook>(rangeUnitId || unitId);
const worksheet = rangeSheetName ? workbook?.getSheetBySheetName(rangeSheetName) : workbook?.getSheetBySheetId(subUnitId);
if (!worksheet) {
return;
}
const realUnitId = workbook!.getUnitId();
const realSheetId = worksheet.getSheetId();
const orginStartRow = ranges[0].startRow;
const orginStartColumn = ranges[0].startColumn;
const currentStartRow = range.startRow;
const currentStartColumn = range.startColumn;
const offsetRanges = ranges.map((range) => ({
startRow: range.startRow - orginStartRow + currentStartRow,
endRow: range.endRow - orginStartRow + currentStartRow,
startColumn: range.startColumn - orginStartColumn + currentStartColumn,
endColumn: range.endColumn - orginStartColumn + currentStartColumn,
}));
dependencyRanges.push({
unitId: realUnitId,
subUnitId: realSheetId,
ranges: offsetRanges,
});
}
});
return dependencyRanges;
}
// eslint-disable-next-line max-lines-per-function
registerRangeFormula(unitId: string, subUnitId: string, oldRanges: IRange[], formulas: string[], callback: RangeFormulaChangeCallback): IDisposable {
const disposableCollection = new DisposableCollection();
const formulaDeps = formulas.map((formula) => this._getFormulaDependcy(unitId, subUnitId, formula, oldRanges));
// eslint-disable-next-line max-lines-per-function
const handleRangeChange = (commandInfo: EffectRefRangeParams) => {
const wholeRangeResult = this._transformWholeRangeFormulas(unitId, subUnitId, oldRanges, formulas, commandInfo);
if (wholeRangeResult) {
return callback(wholeRangeResult);
}
const effectedRanges = getSeparateEffectedRangesOnCommand(this._injector, commandInfo);
if (!effectedRanges) {
return {
undos: [],
redos: [],
};
}
const originStartRow = oldRanges[0].startRow;
const originStartColumn = oldRanges[0].startColumn;
const deps = [{ unitId, subUnitId, ranges: oldRanges }, ...formulaDeps.flat()];
const matchedEffectedRanges: IRange[][] = [];
// 1. calculate effected ranges
for (const { unitId: depUnitId, subUnitId: depSubUnitId, ranges } of deps) {
if (depUnitId === effectedRanges.unitId && depSubUnitId === effectedRanges.subUnitId) {
const intersectedRanges: IRange[] = [];
const currentStartRow = ranges[0].startRow;
const currentStartColumn = ranges[0].startColumn;
const offsetRow = currentStartRow - originStartRow;
const offsetColumn = currentStartColumn - originStartColumn;
for (const range of effectedRanges.ranges) {
const intersectedRange: IRange[] = [];
for (const r of ranges) {
const intersect = getIntersectRange(range, r);
if (intersect) {
intersectedRange.push(intersect);
}
}
if (intersectedRange.length > 0) {
intersectedRanges.push(...intersectedRange);
}
}
if (intersectedRanges.length > 0) {
matchedEffectedRanges.push(
intersectedRanges.map((range) => ({
startRow: range.startRow - offsetRow,
endRow: range.endRow - offsetRow,
startColumn: range.startColumn - offsetColumn,
endColumn: range.endColumn - offsetColumn,
}))
);
}
}
}
if (matchedEffectedRanges.length > 0) {
// 2. split effected ranges to enusre there is no overlap
const ranges = Rectangle.splitIntoGrid([...matchedEffectedRanges.flat()]);
const noEffectRanges = Rectangle.subtractMulti(oldRanges, ranges);
noEffectRanges.sort((a, b) => a.startRow - b.startRow || a.startColumn - b.startColumn);
// 3. calculate every effected range to get new range and new formula
const keyMap = new Map<string, IRangeFormulaItem[]>();
for (let i = 0; i < ranges.length; i++) {
const range = ranges[i];
const currentRow = range.startRow;
const currentColumn = range.startColumn;
const offsetRow = currentRow - originStartRow;
const offsetColumn = currentColumn - originStartColumn;
const transformedRange = handleCommonDefaultRangeChangeWithEffectRefCommands(range, commandInfo).sort((a, b) => a.startRow - b.startRow || a.startColumn - b.startColumn);
if (!transformedRange.length) {
continue;
}
const transformedRow = transformedRange[0].startRow;
const transformedColumn = transformedRange[0].startColumn;
const transformedOffsetRow = transformedRow - originStartRow;
const transformedOffsetColumn = transformedColumn - originStartColumn;
const transformedFormulas = [];
for (let j = 0; j < formulas.length; j++) {
const formula = formulas[j];
const isFormulaFormulaString = isFormulaString(formula);
// 3.1 move formula ref offset to get formula string for this range before this command
const formulaString = isFormulaFormulaString ? this._lexerTreeBuilder.moveFormulaRefOffset(formula!, offsetColumn, offsetRow) : formula!;
// 3.2 transform formula by effect command
const newFormula = isFormulaFormulaString ? this.transformFormulaByEffectCommand(unitId, subUnitId, formulaString, commandInfo) : formulaString;
// 3.3 get formula key offset for this range after this command
const orginFormula = getFormulaKeyOffset(this._lexerTreeBuilder, newFormula, -transformedOffsetColumn, -transformedOffsetRow);
transformedFormulas.push({
newFormula,
orginFormula,
});
}
const item = {
formulas: transformedFormulas,
ranges: transformedRange,
key: transformedFormulas.map((item) => item.orginFormula).join('_'),
};
if (keyMap.has(item.key)) {
keyMap.get(item.key)!.push(item);
} else {
keyMap.set(item.key, [item]);
}
}
// 4. handle no effected ranges
const originKey = formulas.map((item) => getFormulaKeyOffset(this._lexerTreeBuilder, item, 0, 0)).join('_');
if (noEffectRanges.length > 0) {
const currentRow = noEffectRanges[0].startRow;
const currentColumn = noEffectRanges[0].startColumn;
const noEffectFormulas = [];
for (let i = 0; i < formulas.length; i++) {
const formula = formulas[i];
noEffectFormulas.push({
newFormula: isFormulaString(formula) ? this._lexerTreeBuilder.moveFormulaRefOffset(formula, currentColumn - originStartColumn, currentRow - originStartRow) : formula,
orginFormula: formula,
});
}
const item = {
formulas: noEffectFormulas,
ranges: noEffectRanges,
key: originKey,
};
if (keyMap.has(item.key)) {
keyMap.get(item.key)!.push(item);
} else {
keyMap.set(item.key, [item]);
}
}
// 4.1 the inserted row/column block itself is not part of any segment but stays
// covered by the range it was inserted into
this._appendInsertedBlockItems(keyMap, commandInfo, oldRanges, formulas, oldRanges[0], originKey);
// 5. merge effected ranges and get new formula,
// if origin formula was same, means these ranges can use same formula
const res = [];
const keys = Array.from(keyMap.keys());
for (let i = keys.length - 1; i >= 0; i--) {
const key = keys[i];
const ranges = keyMap.get(key)!.sort((a, b) => a.ranges[0].startRow - b.ranges[0].startRow || a.ranges[0].startColumn - b.ranges[0].startColumn);
const formulas = [];
for (let j = 0; j < ranges[0].formulas.length; j++) {
formulas.push(ranges[0].formulas[j].newFormula);
}
const newRanges = Rectangle.mergeRanges(ranges.map((item) => item.ranges).flat());
newRanges.sort((a, b) => a.startRow - b.startRow || a.startColumn - b.startColumn);
res.push({
formulas,
ranges: newRanges,
});
}
return callback(res);
}
return {
undos: [],
redos: [],
};
};
oldRanges.forEach((range) => {
const disposable = this._refRangeService.registerRefRange(range, handleRangeChange, unitId, subUnitId);
disposableCollection.add(disposable);
});
[...formulaDeps.flat()].forEach(({ unitId, subUnitId, ranges }) => {
ranges.forEach((range) => {
const disposable = this._refRangeService.registerRefRange(range, handleRangeChange, unitId, subUnitId);
disposableCollection.add(disposable);
});
});
return disposableCollection;
}
/**
* A whole column/row/sheet range keeps its anchor, so it is adjusted as a single range:
* segmenting it would both split the rule and drop the range type.
*
* @returns the single rule range to apply, or `undefined` when the regular segmentation applies
*/
private _transformWholeRangeFormulas(
unitId: string,
subUnitId: string,
oldRanges: IRange[],
formulas: string[],
commandInfo: EffectRefRangeParams
): { formulas: string[]; ranges: IRange[] }[] | undefined {
const ruleRangeType = oldRanges[0]?.rangeType ?? RANGE_TYPE.NORMAL;
if (ruleRangeType === RANGE_TYPE.NORMAL || !oldRanges.every((range) => range.rangeType === ruleRangeType)) {
return undefined;
}
const ranges = handleCommonDefaultRangeChangeWithEffectRefCommands(oldRanges[0], commandInfo);
if (!ranges.length) {
return [];
}
return [{
formulas: formulas.map((formula) => (isFormulaString(formula) ? this.transformFormulaByEffectCommand(unitId, subUnitId, formula, commandInfo) : formula)),
ranges,
}];
}
private _appendInsertedBlockItems(
keyMap: Map<string, IRangeFormulaItem[]>,
commandInfo: EffectRefRangeParams,
oldRanges: IRange[],
formulas: string[],
origin: IRange,
originKey: string
) {
for (const block of getInsertedBlocks(commandInfo, oldRanges)) {
const offsetRow = block.startRow - origin.startRow;
const offsetColumn = block.startColumn - origin.startColumn;
const blockFormulas = formulas.map((formula) => ({
newFormula: isFormulaString(formula) ? this._lexerTreeBuilder.moveFormulaRefOffset(formula, offsetColumn, offsetRow) : formula,
orginFormula: formula,
}));
const item: IRangeFormulaItem = {
formulas: blockFormulas,
ranges: [block],
key: originKey,
};
const items = keyMap.get(item.key);
if (items) {
items.push(item);
} else {
keyMap.set(item.key, [item]);
}
}
}
}