Skip to Content
🎉 Univer 0.7.0 is released.Read more →
GuidesUniver SheetsFeaturesTable

Table

Facade APIHas Paid PlanUniver ServerUniver on Node.jsPreset
✅--✅UniverSheetsTablePreset

The table function converts ordinary data areas into structured tables, providing features such as automatic filtering, dynamic range adjustment, and alternating row colors. It simplifies data management and analysis, and greatly improves data processing efficiency.

This feature includes the following plugin packages:

Presets installation

import { createUniver, defaultTheme, LocaleType, merge } from '@univerjs/presets';
import { UniverSheetsCorePreset } from '@univerjs/presets/preset-sheets-core';
import UniverPresetSheetsCoreEnUS from '@univerjs/presets/preset-sheets-core/locales/en-US';
import { UniverSheetsTablePreset } from '@univerjs/presets/preset-sheets-table';
import UniverPresetSheetsTableEnUS from '@univerjs/presets/preset-sheets-table/locales/en-US';
 
import '@univerjs/presets/lib/styles/preset-sheets-core.css'
import '@univerjs/presets/lib/styles/preset-sheets-table.css'
 
const {univerAPI} = createUniver({ 
  locale: LocaleType.EN_US, 
  locales: { 
    [LocaleType.EN_US]: merge( 
      {}, 
      UniverPresetSheetsCoreEnUS, 
      UniverPresetSheetsTableEnUS 
    ), 
  }, 
  theme: defaultTheme, 
  presets: [ 
    UniverSheetsCorePreset(), 
    UniverSheetsTablePreset() 
  ]
});

Manual combined installation

npm install @univerjs/sheets-table @univerjs/sheets-table-ui
import { LocaleType, merge, Univer } from '@univerjs/core';
import { defaultTheme } from "@univerjs/design";
import { UniverSheetsTablePlugin } from '@univerjs/sheets-table';
import { UniverSheetsTableUIPlugin } from '@univerjs/sheets-table-ui';
import SheetsTableUIEnUS from '@univerjs/sheets-table-ui/locale/en-US';
 
import '@univerjs/sheets-table-ui/lib/index.css';
 
import '@univerjs/sheets-table/facade';
 
const univer = new Univer({ 
  theme: defaultTheme, 
  locale: LocaleType.EN_US, 
  locales: { 
    [LocaleType.EN_US]: merge( 
      SheetsTableUIEnUS 
    ), 
  },
});
 
univer.registerPlugin(UniverSheetsTablePlugin);
univer.registerPlugin(UniverSheetsTableUIPlugin);

Univer on Node.js Manual combination installation

npm install @univerjs/sheets-table
import { LocaleType, Univer } from '@univerjs/core';
import { defaultTheme } from "@univerjs/design";
import { UniverSheetsTablePlugin } from '@univerjs/sheets-table';
 
import '@univerjs/sheets-table/facade';
 
const univer = new Univer({
  theme: defaultTheme,
  locale: LocaleType.EN_US,
});
 
univer.registerPlugin(UniverSheetsTablePlugin);

Facade API 0.7.0+

To get full definition of facade api, please refer to FacadeAPI .

Insert Table

  • FWorkbook.addTable: Insert a table in the specified worksheet of the workbook
  • FWorksheet.addTable: Insert a table in the worksheet
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Insert a table in the range B2:F11
const fRange = fWorksheet.getRange('B2:F11');
const success = await fWorksheet.addTable(
  'name-1',
  fRange.getRange(),
  'id-1',
  {
    showHeader: true,
  }
);
 
if (success) {
  const tableInfo = fWorkbook.getTableInfo('id-1');
  console.log('debugger tableInfo', tableInfo);
}

Get Table

  • FWorkbook.getTableList: Get all table lists in the workbook
  • FWorksheet.getSubTableInfos: Get all table lists in the worksheet
const fWorkbook = univerAPI.getActiveWorkbook();
const tables = fWorkbook.getTableList();
console.log('debugger tables', tables);
  • FWorkbook.getTableInfo: Get table information by table ID
  • FWorkbook.getTableInfoByName: Get table information by table name
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Insert a table in the range B2:F11
const fRange = fWorksheet.getRange('B2:F11');
const success = await fWorksheet.addTable(
  'name-1',
  fRange.getRange(),
  'id-1',
  {
    showHeader: true,
  }
);
 
if (success) {
  const tableInfo = fWorkbook.getTableInfo('id-1');
  console.log('debugger tableInfo', tableInfo);
}
  • FWorksheet.getTableByCell: Get table information by cell position
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
const cellB2 = fWorksheet.getRange('B2');
const row = cellB2.getRow();
const column = cellB2.getColumn();
console.log('debugger tableInfo', fWorksheet.getTableByCell(row, column));
 
// Insert a table in the range B2:F11
const fRange = fWorksheet.getRange('B2:F11');
const success = await fWorksheet.addTable(
  'name-1',
  fRange.getRange(),
  'id-1',
  {
    showHeader: true,
  }
);
console.log('debugger tableInfo2', fWorksheet.getTableByCell(row, column));

Set Filter

  • FWorkbook.setTableFilter: Set the filter for the table in the workbook
  • FWorksheet.setTableFilter: Set the filter for the table in the worksheet
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Insert a table in the range B2:F11
const fRange = fWorksheet.getRange('B2:F11');
const success = await fWorksheet.addTable(
  'name-1',
  fRange.getRange(),
  'id-1',
  {
    showHeader: true,
  }
);
 
if (success) {
  // Set the filter for the second column
  await fWorksheet.setTableFilter('id-1', 1, {
    filterType: univerAPI.Enum.TableColumnFilterTypeEnum.condition,
    filterInfo: {
      conditionType: univerAPI.Enum.TableConditionTypeEnum.Number,
      compareType: univerAPI.Enum.TableNumberCompareTypeEnum.GreaterThan,
      expectedValue: 10,
    },
  });
 
  const tableInfo = fWorkbook.getTableInfo('id-1');
  console.log('debugger tableInfo', tableInfo);
}
  • FWorksheet.resetFilter: Reset the filter for the table in the worksheet
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Insert a table in the range B2:F11
const fRange = fWorksheet.getRange('B2:F11');
const success = await fWorksheet.addTable(
  'name-1',
  fRange.getRange(),
  'id-1',
  {
    showHeader: true,
  }
);
 
if (success) {
  // Set the filter for the second column
  await fWorksheet.setTableFilter('id-1', 1, {
    filterType: univerAPI.Enum.TableColumnFilterTypeEnum.condition,
    filterInfo: {
      conditionType: univerAPI.Enum.TableConditionTypeEnum.Number,
      compareType: univerAPI.Enum.TableNumberCompareTypeEnum.GreaterThan,
      expectedValue: 10,
    },
  });
 
  // Reset the filter for the second column after 3 seconds
  setTimeout(async () => {
    await fWorksheet.resetFilter('id-1', 1);
  }, 3000);
}

Update/Delete Table

  • FWorkbook.removeTable: Remove the table in the workbook
  • FWorksheet.removeTable: Remove the table in the worksheet
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const tableInfo = fWorkbook.getTableInfo('id-1');
console.log('debugger tableInfo', tableInfo);
 
if (tableInfo) {
  // Remove the table with the specified id
  await fWorksheet.removeTable('id-1');
}
  • FWorksheet.setTableRange: Update the table range in the worksheet
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Insert a table in the range B2:F11
const fRange = fWorksheet.getRange('B2:F11');
const success = await fWorksheet.addTable(
  'name-1',
  fRange.getRange(),
  'id-1',
  {
    showHeader: true,
  }
);
 
if (success) {
  // Update the table range to B2:F21 after 3 seconds
  setTimeout(async () => {
    const newRange = fWorksheet.getRange('B2:F21');
    await fWorksheet.setTableRange('id-1', newRange.getRange());
 
    const tableInfo = fWorkbook.getTableInfo('id-1');
    console.log('debugger tableInfo', tableInfo);
  }, 3000);
}
  • FWorksheet.setTableName: Update the table name in the worksheet
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Insert a table in the range B2:F11
const fRange = fWorksheet.getRange('B2:F11');
const success = await fWorksheet.addTable(
  'name-1',
  fRange.getRange(),
  'id-1',
  {
    showHeader: true,
  }
);
 
if (success) {
  // Update the table name after 3 seconds
  setTimeout(async () => {
    await fWorksheet.setTableName('id-1', 'new-name');
 
    const tableInfo = fWorkbook.getTableInfo('id-1');
    console.log('debugger tableInfo', tableInfo);
  }, 3000);
}

Add table theme

  • FWorksheet.addTableTheme: Add the theme for the table in the worksheet
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
 
// Insert a table in the range B2:F11
const fRange = fWorksheet.getRange('B2:F11');
const success = await fWorksheet.addTable(
  'name-1',
  fRange.getRange(),
  'id-1',
  {
    showHeader: true,
  }
);
 
if (success) {
  await fWorksheet.addTableTheme('id-1', {
    name: 'table-custom-1',
    headerRowStyle: {
      bg: {
        rgb: '#145f82',
      },
    },
    firstRowStyle: {
      bg: {
        rgb: '#c0e4f5',
      },
    },
  });
 
  const tableInfo = fWorkbook.getTableInfo('id-1');
  console.log('debugger tableInfo', tableInfo);
}
Was this page helpful?
Last updated on
WatermarkAdvanced FormulaPro