Configure Cell Data

Cell Position

The cell data in Univer Sheet is stored in the cellData field of IWorksheetData, cellData is a two-dimensional Map structure, with the first and second indexes representing the row number and column number respectively, and each cell is an ICellData object, which contains all the cell information such as cell value, style, type, etc.

The structure of cellData is as follows

// 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 Doc
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.

// 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 Sheet 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.

// 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.

// 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
td	Text direction
ht	Horizontal alignment
vt	Vertical alignment
tb	Truncate overflow
pd	Padding

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

Font

ff is a string that represents the font name.

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

Italic

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

// IStyleData
{
  it: 1 // Italic
}

Bold

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

// IStyleData
{
  bl: 1 // Bold
}

Underline

ul is an object, represents underline style.

// 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.

// 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.

// 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.

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

Border

bd is an object, represents border style.

// 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.

// 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.

// IStyleData
{
  va: 2 // Subscript
}

Text Rotation

tr is an object, represents text rotation.

// 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
  }
}

Text Direction

td is an enum number, 1 means left to right, 2 means right to left.

// IStyleData
{
  td: 1 // Left to right
}

Horizontal Alignment

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

// IStyleData
{
  ht: 1 // Left alignment
}

Vertical Alignment

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

// IStyleData
{
  vt: 1 // Top alignment
}

Truncate Overflow

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

// IStyleData
{
  tb: 1 // Overflow
}

Padding

pd is an object, represents padding.

// IStyleData
{
  pd: {
    t: 0, // Top padding
    b: 0, // Bottom padding
    l: 0, // Left padding
    r: 0  // Right padding
  }
}

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.

// IWorksheetData
{
  cellData: {
    0: {
      0: {
        v: 'A1',
        t: 1 // String
      },
      1: {
        v: 1,
        t: 2 // Number
      },
      1: {
        v: 1, // TRUE
        t: 2 // Boolean
      },
    },
  }
}

Rich Text

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

Formula

cellData.f is a string that represents a formula.

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

Formula ID

cellData.si is a string that represents the formula ID. Univer Sheet 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.

// 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.

// IWorksheetData
{
  cellData: {
    0: {
      0: {
        custom: {
          key: 'value'
        }
      },
    },
  }
}

Internationalization Using UMD