Cell Data Structure

Cell Position

The cell data in Univer Sheets is stored in the cellData field of IWorksheetData object, which contains all the cell information such as cell value, style, type, etc.

The structure of cellData is as follows

interface IWorksheetData {
  cellData: {
    // first row
    0: {
      // first column
      0: { v: 'A1' }
      // second column
      1: { v: 'B1' }
    }
    // second row
    1: {
      // first column
      0: { v: 'A2' }
      // second column
      1: { v: 'B2' }
    }
  }
}

Cell Data

The complete cell information is as follows.

Property	Description
v	Cell original value
s	Cell style id or style object
t	Cell type
p	Rich text, also a 📝 Univer Docs
f	Formula
si	Formula id
custom	Custom field

Check out the type information of each field of ICellData.

Cell Original Value

cellData.v stores the original value of the cell, which can be a string or a number. For cells with formulas, v stores the calculation result of the formula.

Here we show two different cell values.

interface IWorksheetData {
  cellData: {
    0: {
      0: { v: 'A1' }
      1: { v: 1 }
    }
  }
}

Cell Style

cellData.s stores the style id or style object of the cell.

If s is a string, it represents a style id. Univer Sheets supports reference optimization for styles. The repeated style objects are stored in the styles field of IWorkbookData, which is a Map structure, each key is the style id, and the value is the style object.

Please avoid using color values with transparency (such as rgba), otherwise the background will show the content of the underlying cell when the editing state is activated.

interface IWorkbookData {
  styles: {
    random_style_id_1: {
      fs: 12
      bg: {
        rgb: '#ff0000'
      }
    }
  }
}

Then store the id in the cell style to achieve the purpose of style reuse.

interface IWorksheetData {
  cellData: {
    0: {
      0: {
        v: 'A1'
        s: 'random_style_id_1'
      }
      1: {
        v: 'B1'
        s: 'random_style_id_1'
      }
    }
  }
}

If s is an object, it represents a complete cell style object IStyleData.

The complete style field is as follows.

Property	Description
ff	Font
fs	Font size
it	Italic
bl	Bold
ul	Underline
st	Strikethrough
ol	Overline
bg	Background color
bd	Border
cl	Font color
va	Superscript or subscript
tr	Text rotation
ht	Horizontal alignment
vt	Vertical alignment
tb	Truncate overflow
pd	Padding
n	Number format

You can check out the type information of each field of IStyleData.

Font

ff is a string that represents the font name.

interface IStyleData {
  ff: 'Arial' // Font name is Arial
}

Italic

it is a boolean number, 0 means not italic, 1 means italic.

interface IStyleData {
  it: 1 // Italic
}

Bold

bl is a boolean number, 0 means not bold, 1 means bold.

interface IStyleData {
  bl: 1 // Bold
}

Underline

ul is an object, represents underline style.

interface IStyleData {
  ul: {
    s: 1 // Whether to show underline
    c: 0 // Whether the color follows the font color. When `c` is 1 (TRUE), cl does not work. The default value is 1
    cl: { // Underline color
      rgb: '#ff0000'
    }
    t: 0 // Underline type
  }
}

Strikethrough

st is an object, represents strikethrough style.

interface IStyleData {
  st: {
    s: 1 // Whether to show strikethrough
    c: 0 // Whether the color follows the font color. When `c` is 1 (TRUE), cl does not work. The default value is 1
    cl: { // Strikethrough color
      rgb: '#ff0000'
    }
    t: 0 // Strikethrough type
  }
}

Overline

ol is an object, represents overline style.

interface IStyleData {
  ol: {
    s: 1 // Whether to show overline
    c: 0 // Whether the color follows the font color. When `c` is 1 (TRUE), cl does not work. The default value is 1
    cl: { // Overline color
      rgb: '#ff0000'
    }
    t: 0 // Overline type
  }
}

Background Color

bg is an object, represents background color.

interface IStyleData {
  bg: {
    rgb: '#ff0000' // Background color is red
  }
}

Border

bd is an object, represents border style.

interface IStyleData {
  bd: {
    // Top border
    t: {
      s: 0 // Border style
      cl: { // Border color
        rgb: '#ff0000'
      }
    }
    // Bottom border
    b: {
      s: 0 // Border style
      cl: { // Border color
        rgb: '#ff0000'
      }
    }
    // Left border
    l: {
      s: 0 // Border style
      cl: { // Border color
        rgb: '#ff0000'
      }
    }
    // Right border
    r: {
      s: 0 // Border style
      cl: { // Border color
        rgb: '#ff0000'
      }
    }
  }
}

Font Color

cl is an object, represents font color.

interface IStyleData {
  cl: {
    rgb: '#ff0000' // Font color is red
  }
}

Superscript or Subscript

va is a enum number, 1 means normal, 2 means subscript, 3 means superscript.

interface IStyleData {
  va: 2 // Subscript
}

Text Rotation

tr is an object, represents text rotation.

interface IStyleData {
  tr: {
    a: 0 // Text rotation angle
    v: 0 // Whether it is vertical. 1 means vertical, 0 means horizontal. The default value is 0. When v is 1, a is invalid
  }
}

Horizontal Alignment

ht is an enum number, 1 means left alignment, 2 means center, 3 means right alignment.

interface IStyleData {
  ht: 1 // Left alignment
}

Vertical Alignment

vt is an enum number, 1 means top alignment, 2 means center, 3 means bottom alignment.

interface IStyleData {
  vt: 1 // Top alignment
}

Truncate Overflow

tb is an enum number, 1 means overflow, 2 means truncate, 3 means wrap.

interface IStyleData {
  tb: 1 // Overflow
}

Padding

pd is an object, represents padding.

interface IStyleData {
  pd: {
    t: 0 // Top padding
    b: 0 // Bottom paddingAdd commentMore actions
    l: 0 // Left padding
    r: 0 // Right padding
  }
}

Number Format

n is an object, The pattern field indicates the number format. The number format is a string, please refer to here。

n is null or pattern is null, Indicates the general format.

import type { DEFAULT_TEXT_FORMAT_EXCEL } from '@univerjs/engine-numfmt'

interface IStyleData {
  n: {
    pattern: DEFAULT_TEXT_FORMAT_EXCEL // text format
  }
}

I don't want the cell to be converted by default when entering a number starting with 0 or a number that can be converted to a date format

Cell Type

cellData.t is an enumeration CellValueType, represents the type of the cell. 1 means string, 2 means number, 3 means boolean, 4 means force text. Univer will automatically recognize it if it is not set.

If the cell is a boolean type, the value of cellData.v is stored as 0 or 1, 0 means false, 1 means true.

interface IWorksheetData {
  cellData: {
    0: {
      0: {
        v: 'A1'
        t: 1 // String
      }
      1: {
        v: 1
        t: 2 // Number
      }
      2: {
        v: 1 // TRUE
        t: 3 // Boolean
      }
      3: {
        v: '012.0'
        t: 4 // Force text
      }
    }
  }
}

Rich Text

cellData.p is an object that represents rich text and is also a Univer Doc. Learn more about IDocumentData.

When both cell p and v are set, only the contents of p will be displayed.

cellData.p.body.dataStream is the content of rich text.

Formula

cellData.f is a string that represents a formula.

interface IWorksheetData {
  cellData: {
    0: {
      0: {
        f: '=SUM(A1:B1)' // function SUM
      }
    }
  }
}

Formula ID

cellData.si is a string that represents the formula ID. Univer Sheets supports reference optimization for formulas. After the current formula is associated with si in the cell, other cells can refer to the current formula through si. Note that the position of the cell with si must be in the lower right corner of the reference cell, otherwise an error will occur when exporting to XLSX.

The actual formula can be obtained through the Facade API range.getFormulas(). The rule is to get the formula f corresponding to si, and then calculate the actual formula based on the offset from the current cell position to the reference position.

interface IWorksheetData {
  cellData: {
    0: {
      0: {
        f: '=SUM(A1:B1)' // Sum formula
        si: 'random_formula_id_1' // ID of the current formula
      }
      1: {
        si: 'random_formula_id_1' // Take the formula corresponding to this ID during calculation
      }
    }
  }
}

Custom Field

cellData.custom is an object that represents a custom field. You can put any data that complies with the JSON format into it to customize the storage of some additional information.

Updating custom data will overwrite the original custom data. If you need to keep the original custom data when updating data, please obtain the custom data in advance and merge it into new data before updating.

The custom field is only suitable for some special scenarios, and the data stored in this field may be overwritten or deleted by any user operation. Please use it with caution.

interface IWorksheetData {
  cellData: {
    0: {
      0: {
        custom: {
          key: 'value'
        }
      }
    }
  }
}