Styling xlsx file using js-xlsx

Question

I've looked everywhere to find a solution for this. How can I add styles to cells when writing a new xlsx file in javascript? When I mean styles I would like to add background color to the first line(A1) and also want all the sheet to be RTL and not LTR I've searched in the docs but couldn't succeed implementing this option I would really appreciate if anyone could come out with a solution for this problem

here is my script:

    function getCsvFile(value) {
        const currentDate = getCurrentDate()
        let obj = []
        let currentCompany = ''
        value.split('\n').forEach(row => {
            let i = 0;
            if (!row.startsWith('[') && !row.startsWith(']')) {
                if (!hasNumber(row.split(' ')[0])) {
                    currentCompany = row
                    const isExists = obj.find(company => company.name === currentCompany)
                    if (isExists) {
                    } else {
                        obj.push({ name: currentCompany, products: [] })
                    }
                } else {
                    obj = obj.map(company => company.name === currentCompany ? { ...company, products: [...company.products, row] } : company)
                }
            }
        });

        const maxRows = Math.max.apply(Math, obj.map(function (o) { return o.products.length; }))
        const headers = obj.map(({ name }) => name)
        let rows = []
        for (let i = 0; i < maxRows; i++) {
            let row = []
            obj.forEach(element => {
                row.push(element.products[i] || '')
            });
            rows.push(row)
        }

        var wb = XLSX.utils.book_new();
        wb.Props = {
            Title: "SheetJS Tutorial",
            Subject: "Test",
            Author: "Red Stapler",
            CreatedDate: new Date(2017, 12, 19)
        };

        wb.SheetNames.push(currentDate);

        var ws_data = [
            headers,
            ...rows
        ];
        var ws = XLSX.utils.aoa_to_sheet(ws_data);
        ws['A1'].s = {
            fill: {
                patternType: "none", // none / solid
                fgColor: { rgb: "FF000000" },
                bgColor: { rgb: "FFFFFFFF" }
            },
            font: {
                name: 'Times New Roman',
                sz: 16,
                color: { rgb: "#FF000000" },
                bold: true,
                italic: false,
                underline: false
            },
            border: {
                top: { style: "thin", color: { auto: 1 } },
                right: { style: "thin", color: { auto: 1 } },
                bottom: { style: "thin", color: { auto: 1 } },
                left: { style: "thin", color: { auto: 1 } }
            }
        };
        var wscols = headers.map(column => {
            return { wch: column.length + 20 }
        });

        ws['!cols'] = wscols;
        wb.Sheets[currentDate] = ws;
        var wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'binary', });
        function s2ab(s) {

            var buf = new ArrayBuffer(s.length);
            var view = new Uint8Array(buf);
            for (var i = 0; i < s.length; i++) view[i] = s.charCodeAt(i) & 0xFF;
            return buf;

        }
        saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), 'test.xlsx');
    }

Thanks a lot!

Answer 1

this is structure of Workbook in node version XLSX library [0.16.0][1] :

index.d.ts:

export interface WorkBook {
    /**
     * A dictionary of the worksheets in the workbook.
     * Use SheetNames to reference these.
     */
    Sheets: { [sheet: string]: WorkSheet };

    /** Ordered list of the sheet names in the workbook */
    SheetNames: string[];

    /** Standard workbook Properties */
    Props?: FullProperties;

    /** Custom workbook Properties */
    Custprops?: object;

    Workbook?: WBProps;

    vbaraw?: any;
}

export interface WBProps {
    /** Sheet Properties */
    Sheets?: SheetProps[];

    /** Defined Names */
    Names?: DefinedName[];

    /** Workbook Views */
    Views?: WBView[];

    /** Other Workbook Properties */
    WBProps?: WorkbookProperties;
}

/** Workbook View */
export interface WBView {
    /** Right-to-left mode */
    RTL?: boolean;
}

base of this code i could change alignment like this and output file alignment changed to right:

if (wb.Workbook) {
   wb.Workbook.Views[0]['RTL'] = true;
} else {
    wb.Workbook = {};
      wb.Workbook['Views'] = [{RTL: true}];
}