Configure Cell Data

Cell Position

The cell data in Univer Sheets 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.

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

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

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
}

Font Size

fs is a number, the unit is pt.

// IStyleData
{
  fs: 12 // Font size is 12 pt
}

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

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.

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

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