Setting cell style via `ws["A1"].s` object (after worksheet creation) is not supported

[jarki7777]

I'm trying to create a styled xls file:

let wb = XLSX.utils.book_new();
let ws_data = [['hello' , 'world']];
let ws = XLSX.utils.aoa_to_sheet(ws_data);
ws["A1"].s = {
    font: {
        name: "Calibri",
        sz: 24,
        bold: true,
        color: { rgb: "FFFFAA00" },
    },
};
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
XLSX.writeFile(wb, "file.xls");

When i open the file it looks like this:

[juanoude]

Found a solution?

[juanoude]

I solved mine here, now it is working almost perfectly. Seems that the styling property 'vertAlign: true' was breaking the whole document style. Try to put the size property as a string also.

[gitbrent]

The vertAlign prop is supposed to be either "superscript" or "subscript". The core xlsx library was not handling the use of a boolean correctly.

I've updated the docs to reflect the correct values.

[gitbrent]

RESPONSE @jarki7777

• setting styles via worksheet property - after ws has been created - is not supported
• you need to set style props before you call the aoa_to_sheet method.

// STEP 1: Create a new Workbook
const wb = XLSX.utils.book_new();

// STEP 2: Create data rows
let row1 = [
    { v: 'Courier: 24', t: 's', s: { font: { name: 'Courier', sz: 24 } } }
]

// STEP 3: Create Worksheet, add data, set cols widths
const ws = XLSX.utils.aoa_to_sheet([row1]);
ws['!cols'] = [{ width: 30 }, { width: 20 }, { width: 20 }]
XLSX.utils.book_append_sheet(wb, ws, 'browser-demo');

// STEP 4: Write Excel file to browser
XLSX.writeFile(wb, "xlsx-js-style-demo.xlsx");

HELP WANTED The multiple projects that were merged into this library only support this method of creation. The xlsx code is not very clear and uses variables like o for options (really?!) so I've failed to see how to add this functionality off-hand.

If anyone can identify how to support this feature or provide a pull request, that would be super helpful.

[kvprasad505]

RESPONSE @jarki7777

• setting styles via worksheet property - after ws has been created - is not supported
• you need to set style props before you call the aoa_to_sheet method.

// STEP 1: Create a new Workbook
const wb = XLSX.utils.book_new();

// STEP 2: Create data rows
let row1 = [
    { v: 'Courier: 24', t: 's', s: { font: { name: 'Courier', sz: 24 } } }
]

// STEP 3: Create Worksheet, add data, set cols widths
const ws = XLSX.utils.aoa_to_sheet([row1]);
ws['!cols'] = [{ width: 30 }, { width: 20 }, { width: 20 }]
XLSX.utils.book_append_sheet(wb, ws, 'browser-demo');

// STEP 4: Write Excel file to browser
XLSX.writeFile(wb, "xlsx-js-style-demo.xlsx");

HELP WANTED The multiple projects that were merged into this library only support this method of creation. The xlsx code is not very clear and uses variables like o for options (really?!) so I've failed to see how to add this functionality off-hand.

If anyone can identify how to support this feature or provide a pull request, that would be super helpful.

tried giving style before creating sheet, still it is not reflecting

[is-sam]

So no one has a solution to set styles ? which makes the package rather unusefull ..

[cerilloderek]

I initially installed SheetJS and later needed to use styles, so I looked for this project. In order to get it working, I had to remove all dependencies on xlsx and make them use xlsx-js-style instead. I have a monorepo and the hoisted xlsx seemed to be somehow deleting the xlsx-js-style folder in node_modules.

Once I updated all imports of xlsx to be

import XLSX from 'xlsx-js-style';

styles were working.