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 { Dependency, IWorkbookData, Workbook, Worksheet } from '@univerjs/core';
import {
cellToRange,
CellValueType,
DateSystem,
ICommandService,
IConfigService,
ILogService,
Inject,
Injector,
InterceptorEffectEnum,
IUniverInstanceService,
LocaleType,
LogLevel,
ObjectMatrix,
Plugin,
ThemeService,
Univer,
UniverInstanceType,
} from '@univerjs/core';
import {
InterceptCellContentPriority,
INTERCEPTOR_POINT,
INumfmtService,
NumfmtService,
SetNumfmtMutation,
SetRangeValuesMutation,
SheetInterceptorService,
} from '@univerjs/sheets';
import { afterEach, beforeEach, describe, expect, it, vi } from 'vitest';
import { SHEETS_NUMFMT_PLUGIN_CONFIG_KEY } from '../../config/config';
import { SheetsNumfmtCellContentController } from '../numfmt-cell-content.controller';
function createWorkbookData(
id = 'test',
locale = LocaleType.ZH_CN,
dateSystem = DateSystem.Date1900
): IWorkbookData {
return {
id,
appVersion: '3.0.0-alpha',
locale,
dateSystem,
name: '',
sheetOrder: ['sheet1', 'sheet2'],
styles: {
textStyle: {
n: {
pattern: '@',
},
},
},
sheets: {
sheet1: {
id: 'sheet1',
name: 'Sheet1',
rowCount: 20,
columnCount: 20,
cellData: {
0: {
0: { v: true, t: CellValueType.BOOLEAN },
1: { v: '001', t: CellValueType.FORCE_STRING },
2: { v: 'text', t: CellValueType.STRING },
},
1: {
0: { v: '123', t: CellValueType.STRING, s: 'textStyle' },
1: { v: '1234.5', t: CellValueType.STRING },
2: { v: -12.3, t: CellValueType.NUMBER },
3: { v: '20%', t: CellValueType.STRING, s: 'textStyle' },
4: { v: 0, t: CellValueType.NUMBER },
5: { v: 0.009999999999999787, t: CellValueType.NUMBER, s: 'textStyle' },
6: { v: 12.345, t: CellValueType.NUMBER, s: { n: null } },
},
},
},
sheet2: {
id: 'sheet2',
name: 'Sheet2',
rowCount: 20,
columnCount: 20,
cellData: {
1: {
1: { v: '9.9', t: CellValueType.STRING },
},
},
},
},
};
}
function createControllerTestBed(workbookData?: IWorkbookData) {
const univer = new Univer();
const injector = univer.__getInjector();
class TestPlugin extends Plugin {
static override pluginName = 'test-plugin';
static override type = UniverInstanceType.UNIVER_SHEET;
constructor(
_config: undefined,
@Inject(Injector) override readonly _injector: Injector
) {
super();
}
override onStarting(): void {
const dependencies: Dependency[] = [
[SheetInterceptorService],
[INumfmtService, { useClass: NumfmtService }],
[SheetsNumfmtCellContentController],
];
dependencies.forEach((dependency) => this._injector.add(dependency));
}
}
univer.registerPlugin(TestPlugin);
const sheet = univer.createUnit<IWorkbookData, Workbook>(UniverInstanceType.UNIVER_SHEET, workbookData ?? createWorkbookData());
const get = injector.get.bind(injector);
get(IUniverInstanceService).focusUnit('test');
get(ILogService).setLogLevel(LogLevel.SILENT);
return {
univer,
get,
sheet,
};
}
function getInterceptedCell(worksheet: Worksheet, workbook: Workbook, row: number, col: number, get: Injector['get']) {
const interceptorService = get(SheetInterceptorService);
const rawData = worksheet.getCellRaw(row, col);
return interceptorService.fetchThroughInterceptors(INTERCEPTOR_POINT.CELL_CONTENT, InterceptorEffectEnum.Style | InterceptorEffectEnum.Value)(
rawData,
{
unitId: workbook.getUnitId(),
subUnitId: worksheet.getSheetId(),
row,
col,
worksheet,
workbook,
rawData,
}
);
}
describe('SheetsNumfmtCellContentController', () => {
let univer: Univer;
let get: Injector['get'];
let workbook: Workbook;
let commandService: ICommandService;
let numfmtService: INumfmtService;
beforeEach(() => {
const testBed = createControllerTestBed();
univer = testBed.univer;
get = testBed.get;
workbook = testBed.sheet;
commandService = get(ICommandService);
numfmtService = get(INumfmtService);
commandService.registerCommand(SetNumfmtMutation);
commandService.registerCommand(SetRangeValuesMutation);
});
afterEach(() => {
univer.dispose();
});
it('formats runtime table number formats without persisting cell styles and respects direct formats', () => {
get(SheetsNumfmtCellContentController);
get(SheetInterceptorService).intercept(INTERCEPTOR_POINT.CELL_CONTENT, {
priority: InterceptCellContentPriority.TABLE_STYLE,
effect: InterceptorEffectEnum.Style | InterceptorEffectEnum.Value,
handler: (cell, location, next) => {
return next({ ...cell, themeStyle: { n: { pattern: '0.000' } } });
},
});
const worksheet = workbook.getSheetBySheetId('sheet1')!;
expect(getInterceptedCell(worksheet, workbook, 1, 1, get)?.v).toBe('1234.500');
expect(get(SheetInterceptorService).fetchThroughInterceptors(INTERCEPTOR_POINT.CELL_CONTENT, InterceptorEffectEnum.Value)(
worksheet.getCellRaw(1, 1),
{ rawData: worksheet.getCellRaw(1, 1), workbook, worksheet, unitId: 'test', subUnitId: 'sheet1', row: 1, col: 1 }
)?.v).toBe('1234.500');
expect(worksheet.getCellRaw(1, 1)).toEqual({ v: '1234.5', t: CellValueType.STRING });
expect(getInterceptedCell(worksheet, workbook, 1, 6, get)?.v).toBe(12.345);
expect(worksheet.getCellRaw(1, 6)?.s).toEqual({ n: null });
numfmtService.setValues('test', 'sheet1', [{ pattern: '0.0', ranges: [cellToRange(1, 1)] }]);
expect(getInterceptedCell(worksheet, workbook, 1, 1, get)?.v).toBe('1234.5');
expect(getInterceptedCell(worksheet, workbook, 1, 0, get)?.v).toBe('123');
});
it('keeps legacy number-format lookup when no table dxf is present', () => {
get(SheetsNumfmtCellContentController);
const worksheet = workbook.getSheetBySheetId('sheet1')!;
expect(getInterceptedCell(worksheet, workbook, 1, 1, get)?.v).toBe('1234.5');
numfmtService.setValues('test', 'sheet1', [{ pattern: '0.00', ranges: [cellToRange(1, 1), cellToRange(1, 6)] }]);
expect(getInterceptedCell(worksheet, workbook, 1, 1, get)?.v).toBe('1234.50');
expect(getInterceptedCell(worksheet, workbook, 1, 6, get)?.v).toBe('12.35');
expect(workbook.getStyles().getStyleByCell(worksheet.getCellRaw(1, 6))?.n?.pattern).toBe('0.00');
});
it('does not scan changed cells when the number-format render cache is empty', async () => {
get(SheetsNumfmtCellContentController);
const forValue = vi.spyOn(ObjectMatrix.prototype, 'forValue');
try {
await commandService.executeCommand(SetRangeValuesMutation.id, {
unitId: 'test',
subUnitId: 'sheet1',
cellValue: { 1: { 1: { v: 42, t: CellValueType.NUMBER } } },
}, { onlyLocal: true, fromFormula: true });
expect(workbook.getSheetBySheetId('sheet1')?.getCellRaw(1, 1)?.v).toBe(42);
// The worksheet mutation still scans its payload; empty-cache invalidation adds no scan.
expect(forValue).toHaveBeenCalledTimes(1);
} finally {
forValue.mockRestore();
}
});
it('still invalidates populated render-cache entries during ordinary value edits', async () => {
get(SheetsNumfmtCellContentController);
const worksheet = workbook.getSheetBySheetId('sheet1')!;
numfmtService.setValues('test', 'sheet1', [{ pattern: '0.00', ranges: [cellToRange(1, 1)] }]);
expect(getInterceptedCell(worksheet, workbook, 1, 1, get)).toMatchObject({ v: '1234.50' });
const forValue = vi.spyOn(ObjectMatrix.prototype, 'forValue');
try {
await commandService.executeCommand(SetRangeValuesMutation.id, {
unitId: 'test',
subUnitId: 'sheet1',
cellValue: { 1: { 1: { v: 42, t: CellValueType.NUMBER } } },
});
expect(forValue).toHaveBeenCalledTimes(2);
expect(getInterceptedCell(worksheet, workbook, 1, 1, get)).toMatchObject({ v: '42.00' });
} finally {
forValue.mockRestore();
}
});
it('skips unsupported cells and handles text format markers', () => {
get(SheetsNumfmtCellContentController);
const worksheet = workbook.getSheetBySheetId('sheet1')!;
expect(getInterceptedCell(worksheet, workbook, 0, 0, get)).toEqual({ v: true, t: CellValueType.BOOLEAN });
expect(getInterceptedCell(worksheet, workbook, 0, 1, get)).toEqual({ v: '001', t: CellValueType.FORCE_STRING });
expect(getInterceptedCell(worksheet, workbook, 0, 2, get)).toEqual({ v: 'text', t: CellValueType.STRING });
expect(getInterceptedCell(worksheet, workbook, 1, 0, get)).toEqual({
v: '123',
t: CellValueType.STRING,
s: 'textStyle',
markers: {
tl: {
size: 6,
color: '#409f11',
},
},
});
expect(getInterceptedCell(worksheet, workbook, 1, 3, get)).toEqual({
v: '20%',
t: CellValueType.STRING,
s: 'textStyle',
markers: {
tl: {
size: 6,
color: '#409f11',
},
},
});
expect(getInterceptedCell(worksheet, workbook, 1, 5, get)).toEqual({
v: 0.01,
t: CellValueType.NUMBER,
s: 'textStyle',
markers: {
tl: {
size: 6,
color: '#409f11',
},
},
});
get(IConfigService).setConfig(SHEETS_NUMFMT_PLUGIN_CONFIG_KEY, { disableTextFormatMark: true });
expect(getInterceptedCell(worksheet, workbook, 1, 0, get)).toEqual({
v: '123',
t: CellValueType.STRING,
s: 'textStyle',
});
expect(getInterceptedCell(worksheet, workbook, 1, 3, get)).toEqual({
v: '20%',
t: CellValueType.STRING,
s: 'textStyle',
});
expect(getInterceptedCell(worksheet, workbook, 1, 5, get)).toEqual({
v: 0.01,
t: CellValueType.NUMBER,
s: 'textStyle',
});
});
it('formats numeric values and clears render cache on numfmt or value mutations', async () => {
get(SheetsNumfmtCellContentController);
const worksheet = workbook.getSheetBySheetId('sheet1')!;
numfmtService.setValues('test', 'sheet1', [
{ pattern: '0.00', ranges: [cellToRange(1, 1)] },
{ pattern: '[Red]0.00', ranges: [cellToRange(1, 2)] },
]);
const firstFormatted = getInterceptedCell(worksheet, workbook, 1, 1, get);
expect(firstFormatted).toMatchObject({ v: '1234.50', t: CellValueType.NUMBER });
expect(worksheet.getCellRaw(1, 1)).toMatchObject({ v: '1234.5', t: CellValueType.STRING });
const negativeFormatted = getInterceptedCell(worksheet, workbook, 1, 2, get);
expect(negativeFormatted).toMatchObject({
v: '-12.30',
t: CellValueType.NUMBER,
interceptorStyle: {
cl: {
rgb: expect.any(String),
},
},
});
await commandService.executeCommand(SetNumfmtMutation.id, {
unitId: 'test',
subUnitId: 'sheet1',
refMap: {
1: {
pattern: '0.0',
},
},
values: {
1: {
ranges: [cellToRange(1, 1)],
},
},
});
expect(getInterceptedCell(worksheet, workbook, 1, 1, get)).toMatchObject({ v: '1234.5' });
await commandService.executeCommand(SetRangeValuesMutation.id, {
unitId: 'test',
subUnitId: 'sheet1',
cellValue: {
1: {
1: {
v: '42',
t: CellValueType.STRING,
},
},
},
});
expect(getInterceptedCell(worksheet, workbook, 1, 1, get)).toMatchObject({ v: '42.0' });
});
it('updates section colors after value and format edits while preserving the raw font color', async () => {
get(SheetsNumfmtCellContentController);
const worksheet = workbook.getSheetBySheetId('sheet1')!;
const themeService = get(ThemeService);
const originalStyle = { cl: { rgb: '#663399' } };
numfmtService.setValues('test', 'sheet1', [{
pattern: '[Red]0.00;[Green]\\-0.00;[Blue]0.00',
ranges: [cellToRange(1, 1)],
}]);
for (const { value, color } of [
{ value: 12.34, color: 'red' },
{ value: -12.34, color: 'green' },
{ value: 0, color: 'blue' },
]) {
await commandService.executeCommand(SetRangeValuesMutation.id, {
unitId: 'test',
subUnitId: 'sheet1',
cellValue: { 1: { 1: { v: value, t: CellValueType.NUMBER, s: originalStyle } } },
});
expect(getInterceptedCell(worksheet, workbook, 1, 1, get)).toMatchObject({
v: value.toFixed(2),
interceptorStyle: { cl: { rgb: themeService.getColorFromTheme(`${color}.500`) } },
});
expect(worksheet.getCellRaw(1, 1)).toMatchObject({ v: value });
expect(workbook.getStyles().get(worksheet.getCellRaw(1, 1)?.s)).toMatchObject(originalStyle);
}
await commandService.executeCommand(SetNumfmtMutation.id, {
unitId: 'test',
subUnitId: 'sheet1',
refMap: { 1: { pattern: '0.00' } },
values: { 1: { ranges: [cellToRange(1, 1)] } },
});
const cell = getInterceptedCell(worksheet, workbook, 1, 1, get);
expect(cell).toMatchObject({ v: '0.00' });
expect(cell?.interceptorStyle).toBeUndefined();
expect(workbook.getStyles().get(cell?.s)).toMatchObject(originalStyle);
});
it('formats each workbook with its own locale and date system', () => {
get(SheetsNumfmtCellContentController);
const date1900Sheet = workbook.getSheetBySheetId('sheet1')!;
const date1904Workbook = univer.createUnit<IWorkbookData, Workbook>(
UniverInstanceType.UNIVER_SHEET,
createWorkbookData('date1904', LocaleType.FR_FR, DateSystem.Date1904)
);
const date1904Sheet = date1904Workbook.getSheetBySheetId('sheet1')!;
numfmtService.setValues('test', 'sheet1', [{ pattern: 'yyyy-mm-dd', ranges: [cellToRange(1, 4)] }]);
numfmtService.setValues('date1904', 'sheet1', [{ pattern: 'yyyy-mm-dd', ranges: [cellToRange(1, 4)] }]);
expect(getInterceptedCell(date1900Sheet, workbook, 1, 4, get)).toMatchObject({ v: '1900-01-00' });
expect(getInterceptedCell(date1904Sheet, date1904Workbook, 1, 4, get)).toMatchObject({ v: '1904-01-01' });
});
it('preserves a legitimate empty number-format result across renders', () => {
get(SheetsNumfmtCellContentController);
const worksheet = workbook.getSheetBySheetId('sheet1')!;
numfmtService.setValues('test', 'sheet1', [{ pattern: ';;;', ranges: [cellToRange(1, 1)] }]);
const formattedCell = getInterceptedCell(worksheet, workbook, 1, 1, get);
expect(formattedCell).toMatchObject({
v: '',
t: CellValueType.NUMBER,
coverable: false,
});
expect(worksheet.getCellRaw(1, 1)).toMatchObject({ v: '1234.5', t: CellValueType.STRING });
expect(getInterceptedCell(worksheet, workbook, 1, 1, get)).toEqual(formattedCell);
});
it('formats sheets independently and applies locale overrides after switching sheets', () => {
const controller = get(SheetsNumfmtCellContentController);
const sheet1 = workbook.getSheetBySheetId('sheet1')!;
const sheet2 = workbook.getSheetBySheetId('sheet2')!;
numfmtService.setValues('test', 'sheet1', [{ pattern: '0.00', ranges: [cellToRange(1, 1)] }]);
numfmtService.setValues('test', 'sheet2', [{ pattern: '0.0', ranges: [cellToRange(1, 1)] }]);
expect(getInterceptedCell(sheet1, workbook, 1, 1, get)).toMatchObject({ v: '1234.50', t: CellValueType.NUMBER });
workbook.setActiveSheet(sheet2);
expect(getInterceptedCell(sheet2, workbook, 1, 1, get)).toMatchObject({ v: '9.9' });
workbook.setActiveSheet(sheet1);
expect(getInterceptedCell(sheet1, workbook, 1, 1, get)).toMatchObject({ v: '1234.50' });
controller.setNumfmtLocal('fr');
expect(getInterceptedCell(sheet1, workbook, 1, 1, get)).toMatchObject({ v: '1234,50' });
expect(getInterceptedCell(sheet2, workbook, 1, 1, get)).toMatchObject({ v: '9,9' });
});
});