Table

GitHubEdit on GitHub
Preset Info
@univerjs/preset-sheets-table
Server Required
No

The Table feature allows users to create and manage tables in spreadsheets for better organization and presentation of data. It supports various table styles and operations, helping users quickly process and analyze information.

Preset Mode

Installation

npm install @univerjs/preset-sheets-table

Usage

import { UniverSheetsCorePreset } from '@univerjs/preset-sheets-core'
import UniverPresetSheetsCoreEnUS from '@univerjs/preset-sheets-core/locales/en-US'
import { UniverSheetsTablePreset } from '@univerjs/preset-sheets-table'
import UniverPresetSheetsTableEnUS from '@univerjs/preset-sheets-table/locales/en-US'
import { createUniver, LocaleType, merge } from '@univerjs/presets'

import '@univerjs/preset-sheets-core/lib/index.css'
import '@univerjs/preset-sheets-table/lib/index.css'

const { univerAPI } = createUniver({
  locale: LocaleType.En_US,
  locales: {
    [LocaleType.En_US]: merge(
      {},
      UniverPresetSheetsCoreEnUS,
      UniverPresetSheetsTableEnUS, 
    ),
  },
  presets: [
    UniverSheetsCorePreset(),
    UniverSheetsTablePreset(), 
  ],
})

Plugin Mode

Installation

npm install @univerjs/sheets-table @univerjs/sheets-table-ui

Usage

import { LocaleType, merge, Univer } from '@univerjs/core'
import { UniverSheetsSortPlugin } from '@univerjs/sheets-table'
import { UniverSheetsSortUIPlugin } from '@univerjs/sheets-table-ui'
import SheetsSortUIEnUS 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({
  locale: LocaleType.En_US,
  locales: {
    [LocaleType.En_US]: merge(
      {},
      SheetsTableUIEnUS, 
    ),
  },
})

univer.registerPlugin(UniverSheetsTablePlugin) 
univer.registerPlugin(UniverSheetsTableUIPlugin) 

Facade API

Complete Facade API type definitions can be found in the 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')
}

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()
  • 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')
}
  • 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()
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,
  },
)
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')
}
  • 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)
}

Modify/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')

if (tableInfo) {
  // Remove the table by table 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 after 3 seconds
  setTimeout(async () => {
    const newRange = fWorksheet.getRange('B2:F21')
    await fWorksheet.setTableRange('id-1', newRange.getRange())

    const tableInfo = fWorkbook.getTableInfo('id-1')
  }, 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')
  }, 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')
}