How do I set styles programmatically for a row/column index?

[Mashiane]

Hi there

How do I set styles programmatically for a row/column index?

Thanks

[MartinDawson]

Check out the rows class, it has a setStyle function on it.

you can access it via spreadsheet.data.rows.setStyle.

See this example too https://portal.sheetjs.com/xspread/

[harshith-venkatesh]

@MartinDawson , could you please help us when we are dealing with this function

const out = [];
wb.SheetNames.forEach(function (name) {
  const o = { name: name, rows: {} };
  const ws = wb.Sheets[name];
  const aoa = XLSX.utils.sheet_to_json(ws, { raw: false, header: 1 });
  aoa.forEach(function (r: any, i) {
    const cells = {};
    r.forEach(function (c, j) {
      cells[j] = { text: c };
    });
    o.rows[i] = { cells: cells };
  });
  out.push(o);
});
this.dataLoading = false;
return out;

}```

In above case, how can we set style programmatically?

[harshith-venkatesh]

r.forEach(function (c, j) { cells[j] = { text: c }; }); Do we need to specify setStyle here?

[MartinDawson]

@harshith-venkatesh You only need that function if you are importing or exporting a sheet from sheetjs to x-spreadsheet

So it seems like you are converting from sheetjs sheet to x-spreadsheet, i.e uploading a sheet to your website and want to preserve the styles? If that's correct then you need to see this docs:

https://github.com/SheetJS/sheetjs#cell-object

The key: s is what holds the style for the style object from your sheet that you are uploading. the style/theme of the cell (if applicable)

x-spreadsheet has it's styles like this:

I don't really know the exact code though because I am not using sheetjs to upload sheets to a website like your code is doing.

[otary]

this.sheet.data.rows.setStyle(0, {
                font: {
                    italic: true,
                    size: 50,
                    name: "YaHei",
                    bold: true
                }
            });

I've set it like this, but it doesn't work！

[saberjsd]

Hi @Mashiane , I use this set styles for a exact cell on Spreadsheet instance:

  /**
   * set styles for a cell
   * @param ri
   * @param ci
   * @param property
   * @param value
   * @param sheetIndex
   */
  setCellStyle(
    ri: any,
    ci: any,
    property: string,
    value: any,
    sheetIndex: number = 0
  ) {
    // `this` is `Spreadsheet` instance.
    const dataProxy = this.datas[sheetIndex];
    const { styles, rows } = dataProxy;
    const cell = rows.getCellOrNew(ri, ci);
    let cstyle: any = {};
    if (cell.style !== undefined) {
      cstyle = cloneDeep(styles[cell.style]);
    }
    if (property === 'format') {
      cstyle.format = value;
      cell.style = dataProxy.addStyle(cstyle);
    } else if (
      property === 'font-bold' ||
      property === 'font-italic' ||
      property === 'font-name' ||
      property === 'font-size'
    ) {
      const nfont: any = {};
      nfont[property.split('-')[1]] = value;
      cstyle.font = Object.assign(cstyle.font || {}, nfont);
      cell.style = dataProxy.addStyle(cstyle);
    } else if (
      property === 'strike' ||
      property === 'textwrap' ||
      property === 'underline' ||
      property === 'align' ||
      property === 'valign' ||
      property === 'color' ||
      property === 'bgcolor'
    ) {
      cstyle[property] = value;
      cell.style = dataProxy.addStyle(cstyle);
    } else {
      cell[property] = value;
    }
  }

[mohamedds-12]

@saberjsd Thanks so much... you are a life saver!

[mohamedds-12]

This works like a charm : grid.cell(ri, ci).style = 0; 0 is the index of the styles array when loading data:

loadData({
  styles: [
            {
              bgcolor: '#f4f5f8',
              textwrap: true,
              color: '#900b09',
              border: {
                top: ['thin', '#0366d6'],
                bottom: ['thin', '#0366d6'],
                right: ['thin', '#0366d6'],
                left: ['thin', '#0366d6'],
              },
            },
          ],
{
        name: 'sheet0',
        freeze: 'B3',
        styles: [
          {
            bgcolor: '#f4f5f8',
            textwrap: true,
            color: '#900b09',
            border: {
              top: ['thin', '#0366d6'],
              bottom: ['thin', '#0366d6'],
              right: ['thin', '#0366d6'],
              left: ['thin', '#0366d6'],
            },
          },
        ],
        merges: [
          'C3:D4',
        ],
        rows: {
          1: {
            cells: {
              0: {text: 'testingtesttestetst'},
              2: {text: 'testing'},
            },
          },
          2: {
            cells: {
              0: {text: 'render', style: 0},
              1: {text: 'Hello'},
              2: {text: 'haha', merge: [1, 1]},
            }
          },
          8: {
            cells: {
              8: {text: 'border test', style: 0},
            }
          }
        },
      },{
        name: 'sheet1',
        freeze: 'B3',
        styles: [
          {
            bgcolor: '#f4f5f8',
            textwrap: true,
            color: '#900b09',
            border: {
              top: ['thin', '#0366d6'],
              bottom: ['thin', '#0366d6'],
              right: ['thin', '#0366d6'],
              left: ['thin', '#0366d6'],
            },
          },
        ],
        merges: [
          'C3:D4',
        ],
        rows: {
          1: {
            cells: {
              0: {text: 'testingtesttestetst'},
              2: {text: 'testing'},
            },
          },
          2: {
            cells: {
              0: {text: 'render', style: 0},
              1: {text: 'Hello'},
              2: {text: 'haha', merge: [1, 1]},
            }
          },
          8: {
            cells: {
              8: {text: 'border test', style: 0},
            }
          }
        },
      }];`