Range & Selection & Cell

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Create a range of A1 cell:
const fRange = fWorksheet.getRange(0, 0);
console.log(fRange, fRange.getA1Notation());
 
// Creates a range of A1:B2:
const fRange2 = fWorksheet.getRange(0, 0, 2, 2);
console.log(fRange2, fRange2.getA1Notation());

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Get the A1:B2 range on named Sheet1
const fRange1 = fWorksheet.getRange('Sheet1!A1:B2');
console.log(fRange1, fRange1.getA1Notation());
 
// Get a single cell A1
const fRange2 = fWorksheet.getRange('A1');
console.log(fRange2, fRange2.getA1Notation());
 
// Get the A1:B2 range
const fRange3 = fWorksheet.getRange('A1:B2');
console.log(fRange3, fRange3.getA1Notation());
 
// Get the range of column A
const fRange4 = fWorksheet.getRange('A:A');
console.log(fRange4, fRange4.getA1Notation());
 
// Get the range of row 1
const fRange5 = fWorksheet.getRange('1:1');
console.log(fRange5, fRange5.getA1Notation());

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
 
console.log(fRange.getValue()); // The value of the top-left cell in the range
console.log(fRange.getRawValue()); // The raw value of the top-left cell in the range
console.log(fRange.getDisplayValue()); // The displayed value of the top-left cell in the range
console.log(fRange.getCellData()); // The cell data of the top-left cell in the range

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
 
console.log(fRange.getValues()); // The values of all cells in the range
console.log(fRange.getRawValues()); // The raw values of all cells in the range
console.log(fRange.getDisplayValues()); // The displayed values of all cells in the range
console.log(fRange.getCellDatas()); // The cell data of all cells in the range

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
 
console.log(fRange.getFormula()); // The formula of the top-left cell in the range
console.log(fRange.getFormulas()); // The formulas of all cells in the range

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
fRange.setValue('Hello, Univer');

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
fRange.setValue('=A1+B1');

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
fRange.setValue({
  v: 'Hello, Univer',
  custom: {
    key: 'value',
  },
});

fRange.setValueForCell('Hello, Univer');

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
fRange.setValues([
  ['A1', 'B1'],
  ['A2', 'B2'],
]);
 
fRange.setValues([
  [{ v: 'A1' }, { v: 'B1' }],
  [{ v: 'A2' }, { v: 'B2' }],
]);

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
fRange.setValues({
  0: {
    0: 'A1',
    1: 'B1',
  },
  1: {
    0: 'A2',
    1: 'B2',
  },
});

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorkSheet = fWorkbook.getActiveSheet();
const fRange = fWorkSheet.getRange('A1:D10');
 
// Clears content and formatting information of the range.
fRange.clear();

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
console.log(fRange.getCellStyleData()); // The style data of the top-left cell in the range
console.log(fRange.getCellStyle()); // The style of the top-left cell in the range
console.log(fRange.getCellStyles()); // The styles of all cells in the range

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
fRange.setValues([
  [1, 2],
  [3, 4]
]).setFontWeight('bold')
  .setFontLine('underline')
  .setFontFamily('Arial')
  .setFontSize(24)
  .setFontColor('red');

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
fRange
  .setFontWeight(null)
  .setFontLine(null)
  .setFontFamily(null)
  .setFontSize(null)
  .setFontColor(null);

// Assume the active sheet empty sheet.
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const values = [
  [1, 2, 3, 4],
  [2, 3, 4, 5],
  [3, 4, 5, 6],
  [4, 5, 6, 7],
  [5, 6, 7, 8],
];
 
// Set the range A1:D5 with some values, the range A1:D5 will be:
// 1 | 2 | 3 | 4
// 2 | 3 | 4 | 5
// 3 | 4 | 5 | 6
// 4 | 5 | 6 | 7
// 5 | 6 | 7 | 8
const fRange = fWorksheet.getRange('A1:D5');
fRange.setValues(values);
console.log(fWorksheet.getRange('A1:D5').getValues()); // [[1, 2, 3, 4], [2, 3, 4, 5], [3, 4, 5, 6], [4, 5, 6, 7], [5, 6, 7, 8]]
 
// Insert the empty cells into the range A1:B2 along the columns dimension, the range A1:D5 will be:
//   |   | 1 | 2
//   |   | 2 | 3
// 3 | 4 | 5 | 6
// 4 | 5 | 6 | 7
// 5 | 6 | 7 | 8
const fRange2 = fWorksheet.getRange('A1:B2');
fRange2.insertCells(univerAPI.Enum.Dimension.COLUMNS);
console.log(fWorksheet.getRange('A1:D5').getValues()); // [[null, null, 1, 2], [null, null, 2, 3], [3, 4, 5, 6], [4, 5, 6, 7], [5, 6, 7, 8]]
 
// Set the range A1:D5 values again, the range A1:D5 will be:
// 1 | 2 | 3 | 4
// 2 | 3 | 4 | 5
// 3 | 4 | 5 | 6
// 4 | 5 | 6 | 7
// 5 | 6 | 7 | 8
fRange.setValues(values);
 
// Insert the empty cells into the range A1:B2 along the rows dimension, the range A1:D5 will be:
//   |   | 3 | 4
//   |   | 4 | 5
// 1 | 2 | 5 | 6
// 2 | 3 | 6 | 7
// 3 | 4 | 7 | 8
const fRange3 = fWorksheet.getRange('A1:B2');
fRange3.insertCells(univerAPI.Enum.Dimension.ROWS);
console.log(fWorksheet.getRange('A1:D5').getValues()); // [[null, null, 3, 4], [null, null, 4, 5], [1, 2, 5, 6], [2, 3, 6, 7], [3, 4, 7, 8]]

// Assume the active sheet empty sheet.
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const values = [
  [1, 2, 3, 4],
  [2, 3, 4, 5],
  [3, 4, 5, 6],
  [4, 5, 6, 7],
  [5, 6, 7, 8],
];
 
// Set the range A1:D5 with some values, the range A1:D5 will be:
// 1 | 2 | 3 | 4
// 2 | 3 | 4 | 5
// 3 | 4 | 5 | 6
// 4 | 5 | 6 | 7
// 5 | 6 | 7 | 8
const fRange = fWorksheet.getRange('A1:D5');
fRange.setValues(values);
console.log(fWorksheet.getRange('A1:D5').getValues()); // [[1, 2, 3, 4], [2, 3, 4, 5], [3, 4, 5, 6], [4, 5, 6, 7], [5, 6, 7, 8]]
 
// Delete the range A1:B2 along the columns dimension, the range A1:D5 will be:
// 3 | 4 |   |
// 4 | 5 |   |
// 3 | 4 | 5 | 6
// 4 | 5 | 6 | 7
// 5 | 6 | 7 | 8
const fRange2 = fWorksheet.getRange('A1:B2');
fRange2.deleteCells(univerAPI.Enum.Dimension.COLUMNS);
console.log(fWorksheet.getRange('A1:D5').getValues()); // [[3, 4, null, null], [4, 5, null, null], [3, 4, 5, 6], [4, 5, 6, 7], [5, 6, 7, 8]]
 
// Set the range A1:D5 values again, the range A1:D5 will be:
// 1 | 2 | 3 | 4
// 2 | 3 | 4 | 5
// 3 | 4 | 5 | 6
// 4 | 5 | 6 | 7
// 5 | 6 | 7 | 8
fRange.setValues(values);
 
// Delete the range A1:B2 along the rows dimension, the range A1:D5 will be:
// 3 | 4 | 3 | 4
// 4 | 5 | 4 | 5
// 5 | 6 | 5 | 6
//   |   | 6 | 7
//   |   | 7 | 8
const fRange3 = fWorksheet.getRange('A1:B2');
fRange3.deleteCells(univerAPI.Enum.Dimension.ROWS);
console.log(fWorksheet.getRange('A1:D5').getValues()); // [[3, 4, 3, 4], [4, 5, 4, 5], [5, 6, 5, 6], [null, null, 6, 7], [null, null, 7, 8]]

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Merge B1:B2
const fRange = fWorksheet.getRange('B1:B2');
fRange.merge();
console.log(fRange.isMerged()); // true
 
// A1:B2 is merged
const fRange2 = fWorksheet.getRange('A1:B2');
console.log(fRange2.isMerged()); // false
console.log(fRange2.isPartOfMerge()); // true
 
// Break apart the merged range
fRange2.breakApart();
 
// merge horizontally: fRange2.mergeAcross();
// merge vertically: fRange2.mergeVertically();
// Get all merged ranges in the worksheet: fWorksheet.getMergedRanges();

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Highlight the range C3:E5 with default style
const fRange = fWorksheet.getRange('C3:E5');
fRange.highlight();
 
// Highlight the range C7:E9 with custom style and primary cell D8
const fRange2 = fWorksheet.getRange('C7:E9');
const primaryCell = fWorksheet.getRange('D8').getRange();
const disposable = fRange2.highlight(
  {
    stroke: 'red',
    fill: 'yellow'
  },
  {
    ...primaryCell,
    actualRow: primaryCell.startRow,
    actualColumn: primaryCell.startColumn
  }
);
 
// Remove the range C7:E9 highlight after 5 seconds
setTimeout(() => {
  disposable.dispose();
}, 5000);

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// A1:A3 has following values:
//  A      |
//  1,2,3  |
//  4,,5,6 |
const fRange = fWorksheet.getRange('A1:A3');
fRange.setValues([
  ['A'],
  ['1,2,3'],
  ['4,,5,6']
]);
 
// After calling splitTextToColumns(true), the range will be:
//  A |   |
//  1 | 2 | 3
//  4 | 5 | 6
fRange.splitTextToColumns(true);
 
// After calling splitTextToColumns(false), the range will be:
//  A |   |   |
//  1 | 2 | 3 |
//  4 |   | 5 | 6
fRange.splitTextToColumns(false);

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// A1:A3 has following values:
//     A   |
//  1;;2;3 |
//  1;,2;3 |
const fRange = fWorksheet.getRange('A1:A3');
fRange.setValues([
['A'],
['1;;2;3'],
['1;,2;3']
]);
 
// After calling splitTextToColumns(false, univerAPI.Enum.SplitDelimiterType.Semicolon|univerAPI.Enum.SplitDelimiterType.Comma), the range will be:
//  A |   |   |
//  1 |   | 2 | 3
//  1 |   | 2 | 3
fRange.splitTextToColumns(false, univerAPI.Enum.SplitDelimiterType.Semicolon|univerAPI.Enum.SplitDelimiterType.Comma);
 
// After calling splitTextToColumns(true, univerAPI.Enum.SplitDelimiterType.Semicolon|univerAPI.Enum.SplitDelimiterType.Comma), the range will be:
//  A |   |
//  1 | 2 | 3
//  1 | 2 | 3
fRange.splitTextToColumns(true, univerAPI.Enum.SplitDelimiterType.Semicolon|univerAPI.Enum.SplitDelimiterType.Comma);

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// A1:A3 has following values:
//     A   |
//  1#2#3  |
//  4##5#6 |
const fRange = fWorksheet.getRange('A1:A3');
fRange.setValues([
  ['A'],
  ['1#2#3'],
  ['4##5#6']
]);
 
// After calling splitTextToColumns(false, univerAPI.Enum.SplitDelimiterType.Custom, '#'), the range will be:
//  A |   |   |
//  1 | 2 | 3 |
//  4 |   | 5 | 6
fRange.splitTextToColumns(false, univerAPI.Enum.SplitDelimiterType.Custom, '#');
 
// After calling splitTextToColumns(true, univerAPI.Enum.SplitDelimiterType.Custom, '#'), the range will be:
//  A |   |
//  1 | 2 | 3
//  4 | 5 | 6
fRange.splitTextToColumns(true, univerAPI.Enum.SplitDelimiterType.Custom, '#');

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
console.log(fRange.getCellRect()); // width、heigh、left、right、top、bottom、x、y

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const fRange = fWorksheet.getRange('A1:B2');
console.log(fRange.getCell());

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Activate the new selection of A1:B2
const fRange = fWorksheet.getRange('A1:B2');
fRange.activate();
 
// Get the current active selection
const fSelection = fWorksheet.getSelection();
console.log(fSelection);
const activeRange = fSelection.getActiveRange();
console.log(activeRange.getA1Notation()); // A1:B2

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Set the selection to A1:B2 by FRange
const fRange = fWorksheet.getRange('A1:B2');
fRange.activate();
 
// Set the selection to C1:D2 by FWorksheet
fWorksheet.setActiveSelection(fWorksheet.getRange('C1:D2'));

const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Activate the new selection of A10:B11
const fRange = fWorksheet.getRange('A10:B11');
fRange.activate();
 
// Get the current active cell
let fSelection = fWorksheet.getSelection();
const { actualRow, actualColumn } = fSelection.getCurrentCell();
console.log(fWorksheet.getRange(actualRow, actualColumn).getA1Notation()); // A10
 
// Update the new active cell to B11
fSelection = fSelection.updatePrimaryCell(fWorksheet.getRange('B11'));
const { actualRow: newRow, actualColumn: newColumn } = fSelection.getCurrentCell();
console.log(fWorksheet.getRange(newRow, newColumn).getA1Notation()); // B11

const fWorkbook = univerAPI.getActiveWorkbook();
 
// Disable selection
fWorkbook.disableSelection();
 
// Enable selection
fWorkbook.enableSelection();
 
// Hide selection
fWorkbook.transparentSelection();
 
// Show selection
fWorkbook.showSelection();

univerAPI.addEvent(univerAPI.Event.SelectionChanged, (params)=> {
  const { worksheet, workbook, selections } = params;
  console.log(params);
});

{
  v: 'Hello, Univer',
  s: 'styleId',
  t: CellValueType.STRING
}

univerAPI.addEvent(univerAPI.Event.EventName, (params) => {
  // Event handling logic
});

univerAPI.addEvent(univerAPI.Event.CellPointerMove, (params) => {
  // Get event parameters
  const { worksheet, workbook, row, column } = params;
  console.log('Current cell position:', worksheet.getRange(row, column).getA1Notation());
})

univerAPI.addEvent(univerAPI.Event.CellPointerDown, (params) => {
  const { worksheet, workbook, row, column } = params;
  console.log('Mouse down cell:', worksheet.getRange(row, column).getA1Notation());
})

univerAPI.addEvent(univerAPI.Event.CellPointerUp, (params) => {
  const { worksheet, workbook, row, column } = params;
  console.log('Mouse up cell:', worksheet.getRange(row, column).getA1Notation());
})

univerAPI.addEvent(univerAPI.Event.CellHover, (params) => {
  const { worksheet, workbook, row, column } = params;
  console.log('Hovered cell:', worksheet.getRange(row, column).getA1Notation());
})

univerAPI.addEvent(univerAPI.Event.DragOver, (params) => {
  const { worksheet, workbook, row, column } = params;
  console.log('Drag over cell:', worksheet.getRange(row, column).getA1Notation());
})

univerAPI.addEvent(univerAPI.Event.Drop, (params) => {
  const { worksheet, workbook, row, column } = params;
  console.log('Drop target cell:', worksheet.getRange(row, column).getA1Notation());
})

univerAPI.addEvent(univerAPI.Event.CellClicked, (params) => {
  const { worksheet, workbook, row, column } = params;
  console.log('Clicked cell:', worksheet.getRange(row, column).getA1Notation());
})

// Fixed position rendering
univerAPI.getSheetHooks().onCellRender([{
  drawWith: (ctx, info, skeleton, spreadsheets) => {
    const { row, col } = info;
    // Render a checkmark at position (1,1)
    if (row === 1 && col === 1) {
      const { primaryWithCoord } = info;
      const { startX, startY } = primaryWithCoord;
      ctx.fillText('✅', startX, startY + 10);
    }
  }
}]);
 
// Refresh canvas to apply rendering
univerAPI.getActiveWorkbook().getActiveSheet().refreshCanvas();

// Set marker
univerAPI.getActiveWorkbook().getActiveSheet().getRange('B2').setValue({ custom: { key: 'needCheck' } });
 
// Render based on marker
univerAPI.getSheetHooks().onCellRender([{
  drawWith: (ctx, info, skeleton, spreadsheets) => {
    const { row, col, data } = info;
    // Render checkmark at marked position
    if (data?.custom?.key === 'needCheck') {
      const { primaryWithCoord } = info;
      const { startX, startY } = primaryWithCoord;
      ctx.fillText('✅', startX, startY + 10);
    }
  }
}]);
 
// Refresh canvas to apply rendering
univerAPI.getActiveWorkbook().getActiveSheet().refreshCanvas();

const fWorkbook = univerAPI.getActiveWorkbook();
fWorkbook.startEditing();

// Pass true to commit changes, false to cancel editing
// This is an async function, requires await
const fWorkbook = univerAPI.getActiveWorkbook();
await fWorkbook.endEditingAsync(true);

univerAPI.addEvent(univerAPI.Event.BeforeSheetEditStart, (params) => {
  const { worksheet, workbook, row, column, eventType, keycode, isZenEditor } = params;
  console.log('Before cell edit:', params);
 
  // If you want to prevent entering the editing state
  params.cancel = true;
})

const disposable = univerAPI.addEvent(univerAPI.Event.SheetEditStarted, (params) => {
  const { worksheet, workbook, row, column, eventType, keycode, isZenEditor } = params;
  console.log(params);
});
 
// Remove the event listener, use `disposable.dispose()`

const disposable = univerAPI.addEvent(univerAPI.Event.BeforeSheetEditEnd, (params) => {
  const { worksheet, workbook, row, column, value, eventType, keycode, isZenEditor, isConfirm } = params;
  console.log(params);
 
  // Cancel the cell edit end operation
  params.cancel = true;
});
 
// Remove the event listener, use `disposable.dispose()`

univerAPI.addEvent(univerAPI.Event.SheetEditEnded, (params) => {
  const { worksheet, workbook, row, column, eventType, keycode, isZenEditor, isConfirm } = params;
  console.log('After cell edit:', params);
})

univerAPI.addEvent(univerAPI.Event.BeforeClipboardChange, (params) => {
  const {text, html} = params;
  console.log('Clipboard content:', text, html);
  // If you want to cancel the clipboard change
  // params.cancel = true;
});

univerAPI.addEvent(univerAPI.Event.BeforeClipboardPaste, (params) => {
  const {text, html} = params;
  console.log('Content to paste:', text, html);
  // If you want to cancel the paste operation
  // params.cancel = true;
});

univerAPI.addEvent(univerAPI.Event.ClipboardChanged, (params) => {
  const {text, html} = params;
  console.log('New clipboard content:', text, html);
});

univerAPI.addEvent(univerAPI.Event.ClipboardPasted, (params) => {
  const {text, html} = params;
  console.log('Pasted content:', text, html);
});