How to apply cell formats and number formats globally in spreadsheet?

1 min read

This Knowledge Base explains how to apply the cell formatting and number formatting globally in a JavaScript Spreadsheet. Use the queryCellInfo client-side event to achieve this requirement. Apply cell formats and number formats using the setCell method. Row height or column width can also be applied globally using the setRow and setColumn method respectively.

[HTML]

  <div id="spreadsheet"></div>

[TS]

//Initialize the spreadsheet component.
let spreadsheet: Spreadsheet = new Spreadsheet({
  sheets: [
    {
      name: 'Car Sales Report',
      ranges: [{ dataSource: (dataSource as any).defaultData }],
    },
  ],
  created: (): void => {
    spreadsheet.cellFormat(
      { fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' },
      'A1:F1'
    );
  },
  queryCellInfo: (args): void => {
    let sheet = spreadsheet.getActiveSheet();
    setCell(
      args.rowIndex,
      args.colIndex,
      sheet,
      {
        format: '0.00E+00',
        style: {
          borderBottom: '1px solid #000000',
          borderLeft: '1px solid #000000',
          borderRight: '1px solid #000000',
          borderTop: '1px solid #000000',
          fontSize: '14pt',
          fontWeight: 'bold',
          textAlign: 'right',
        },
      },
      true
    );
    if (
      !sheet.columns[args.colIndex] ||
      sheet.columns[args.colIndex].height != 120
    ) {
      //Condition to skip already applied columns.
      setColumn(sheet, args.colIndex, { width: 120, customWidth: true });
    }
    if (!sheet.rows[args.rowIndex] || sheet.rows[args.rowIndex].height != 30) {
      //Condition to skip already applied rows.
      setRow(sheet, args.rowIndex, { height: 30, customHeight: true });
    }
  },
});
 
//Render the initialized spreadsheet component.
spreadsheet.appendTo('#spreadsheet');

Sample Link: https://stackblitz.com/edit/lbgncs

Screenshot:

JavaScript Spreadsheet