使用js-xlsx设置xlsx文件的样式

Question

我到处寻找解决这个问题的方法。如何在javascript中编写新的xlsx文件时向单元格添加样式？当我的意思是风格，我想添加背景颜色到第一行(A1)，也希望所有的工作表是RTL而不是LTR我在文档中搜索，但无法成功实现这个选项，我将非常感谢如果有人能拿出一个解决这个问题的解决方案

下面是我的脚本：

​

    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');
    }

​

非常感谢!

Answer 1

这是节点版本XLSX库[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;
}

在此代码的基础上，我可以像这样更改对齐方式，并将输出文件对齐方式更改为右对齐：

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