js-xlsx-gen

Simple declarative layer built on top of protobi/js-xlsx which is a fork of SheetJS/js-xlsx that adds styles.

Installation

npm install js-xlsx-gen

Usage

var xlsGen = require('js-xlsx-gen')({
    defaultDateFormat: 'yyyy-mm-dd' // optional override of default date format of mm/dd/yyyy
});
 
// define some common styles
// see: https://github.com/protobi/js-xlsx/blob/master/tests/test-style.js for examples of styling AND https://github.com/SheetJS/ssf/blob/master/ssf.js for examples of numFmt
var commonStyles = {
    header: {
        alignment: {horizontal: 'center'},
        font: {bold: true}
    },
    positive: {
        numFmt: '$#,##0.00;$(#,##0.00)',
        font: {color: {rgb: '00ffff'}},
        fill: {fgColor: {rgb: 'ffffff'}}
    }
};
// next define the spreadsheet with each key as the sheet name
var spreadsheet = {
    sheetA: {
        header: {
            styles: {
                row: commonStyles.header,
                columns: {
                    1: {font: {italic: true}}
                }
            },
            columns: [
                {v :'ColA', s: commonStyles.header},
                {v :'ColB', s: commonStyles.header}
            ]
        },
        data: {
            rows: [
                [1, 2],
                [2, 3]
            ]
        }
    },
    sheetB: {
        header: {
            columns: [
                'Column A',
                'Column B',
                'Column C',
                'Column D'
            ]
        },
        data: {
            styles: {
                columns: {
                    2: {numFmt: '$#,##0.00;$(#,##0.00)'},
                    3: {font: {color: {rgb: '00ff00'}}}
                }
            },
            rows: [
                [
                    new Date(),
                    true,
                    {v: new Date(), s: {numFmt: 'mm-dd-yyyy'}},
                    20
                ],
                [
                    {v: -0.02, s: {numFmt: '$#,##0.00;$(#,##0.00)', font: {color: {rgb: 'ffffff'}}, fill: {fgColor: {rgb: 'ff0000'}}}}, // inline styling
                    {v: 3, s: commonStyles.positive}, // inline styling to global styling
                    1.23,
                    40
                ]
            ]
        }
    }
};
// build workbook with OPTIONAL default cell styling
var workbook = xlsGen.generate(spreadsheet, {
    header: {font: { name: "Verdana", sz: 18, color: {rgb: "FF8800"}}, fill: {fgColor: {rgb: "aa00ff"}}},
    data: {font: { name: "Verdana", sz: 11, color: {rgb: "FF0000"}}, fill: {fgColor: {rgb: "ffaa00"}}}
});
// write out XLSX
xlsGen.writeFile(workbook, 'workbook.xlsx');

API

• generate
• write
• writeFile

---

generate ( spreadsheet, defaultStyle )

Generate a workbook from a spreadsheet definition with default cell styles.

Arguments

• spreadsheet - Spreadsheet definition
• defaultStyle - OPTIONAL styling for headers and data

Example

var workbook = xlsGen.generate(spreadsheet, {
    header: {font: { name: "Verdana", sz: 18, color: {rgb: "FF8800"}}, fill: {fgColor: {rgb: "aa00ff"}}},
    data: {font: { name: "Verdana", sz: 11, color: {rgb: "FF0000"}}, fill: {fgColor: {rgb: "ffaa00"}}}
});

---

write ( workbook, wopts )

Write workbook to Binary string. For more details see js-xlsx write()

---

writeFile ( workbook, filePath )

Write workbook to Binary string. For more details see js-xlsx writeFile()

Spreadsheet Definition

The Spreadsheet Definition contains multiple sheets:

var spreadsheet = {
    sheetA: {
    },
    sheetB: {		
    }
};

Each sheet has an optional header section and a data section:

var spreadsheet = {
    sheetA: {
        header: {
        },
        data: {
        }
    }
};

The optional header section has an optional style section and columns section:

var spreadsheet = {
    sheetA: {
        header: {
            styles: {
            },
            columns: [
            ]
        }
    }
};

The data section has an optional style section and data section:

var spreadsheet = {
    sheetA: {
        data: {
            styles: {
            },
            rows: [
            ]
        }
    }
};

The styles section under the header and data sections contains an optional row section and an optional columns section:

var spreadsheet = {
    sheetA: {
        header: {
            styles: {
                row: {
                    alignment: {horizontal: 'center'},
                    font: {bold: true}
                },
                columns {
                    1: {font: {italic: true}}
                }
            },
            columns: [
            ]
        },
        data: {
            styles: {
                row: {
                    alignment: {horizontal: 'right'},
                    font: {bold: true, italic: true, sz: 14}
                },
                columns {
                    1: {
                        numFmt: '$#,##0.00;$(#,##0.00)',
                        font: {color: {rgb: '00ffff'}},
                        fill: {fgColor: {rgb: 'ffffff'}}
                    }
                }
            },
            rows: [
            ]
        }
    }
};

Unfortunately, these styles aren't documented well, but examples can be found in the style test code of protobi/js-xlsx.

The columns array of the header section contains cell data:

var spreadsheet = {
    sheetA: {
        header: {
            columns: [
                {v :'ColA', s: commonStyles.header},
                'ColB'
            ]
        },
        data: {
        }
    }
};

Notice that the columns array contains either objects that conform to Cell Objects in protobi/js-xlsx OR raw data, i.e. number, date, string, boolean.

The rows array of the data section contains row data:

var spreadsheet = {
    sheetA: {
        data: {
            rows: [
                [
                    new Date(),
                    true,
                    {v: new Date(), s: {numFmt: 'mm-dd-yyyy'}},
                    20
                ],
                [
                    {v: -0.02, s: {numFmt: '$#,##0.00;$(#,##0.00)', font: {color: {rgb: 'ffffff'}}, fill: {fgColor: {rgb: 'ff0000'}}}},
                    {v: 3, s: commonStyles.positive},
                    1.23,
                    40
                ]
            ]
        }
    }
};

Notice that the rows array contains an array for each row which contains either objects that conform to Cell Objects in protobi/js-xlsx OR raw data, i.e. number, date, string, boolean.