Nukem's Possible Manifestation

    elxml

    0.3.2 • Public • Published

    elxml

    A minimalistic Excel OOXML writer.

    NPM Version License

    The main purpose is to create simple Excel files via JavaScript. The current implementation supports

    1. Multiple sheets
    2. Creation of rows and cells
    3. Column width definition
    4. PatternFills for cells
    5. Borders for cells
    6. Number formats for cells
    7. Fonts for cells
    8. Merge cells
    9. AutoFilter

    elxml allows to use string tables to save memory.

    Uses xmlbuilder-js, archiver and underscore

    Usage

    Create a workbook:

    var excel = require("elxml.js");
    var wb = excel.createWorkbook();

    Create a sheet within the workbook:

    var sheet = wb.addSheet("mySheet");

    Add a row with a single cell and set a value for the cell

    var row = sheet.addRow(1);
    var cell = row.addCell("A","d");  // the value is a date in ISO standard notation
    cell.setValue("2014-02-02");

    Last step is to save the workbook:

    wb.save("test.01.xlsx", function(err) {
        if (!err) {
            console.log("File saved!");
        } else {
            console.log(err);
        }
    });

    Advanced usage

    Number formats

    The above example creates an Excel file with a date cell but without a proper number format.

    To apply the number format to the cell we have to create a cell style. Cell styles are based on a default cell style which defines the number format, fill, borders and the font for those cells which don't use any cell style. In elxml the default is: Calibri with a size of 11, no borders, no fill, no number format.

    Lets set a date number format to see a better formatted date in Excel:

    // create a date format
    var dateFrmt = wb.addNumberFormat("dd/mm/yy;@");
     
    // get the predefined default style
    var defStyle = wb.getStyle("Standard");
     
    // derive a new style from the default style
    var dateStyle = wb.addStyle(defStyle, {numFrmt: dateFrmt});
     
    // apply the style
    cell.setStyle(dateStyle);

    Another Example

    // create a date format
    var dateFrmt = wb.addNumberFormat("DD/MM/YYYY\\ HH:MM:SS");
     
    // create a default style
    var defStyle = wb.createStyle("Standard");
    // derive a new style from the default style
    var dateStyle = wb.addStyle(defStyle, {numFrmt: dateFrmt});
     
    // format value to MSDATE format
    var value = moment( "2014-06-17 08:55:49" ).toOADate();
     
    // set cell type
    var cell = row.addCell( "A" , excel.CELL_TYPE_MSDATE );
     
    // apply the style
    cell.setStyle(dateStyle);
     
    // write data to cell
    cell.setValue( value );

    To see which number formats are available take a look at the OOXML spec.

    Strings

    By default strings are saved as inline strings.

    // add a cell with a string value
    var cell1 = row.addCell("D");      // excel.CELL_TYPE_STRING is default
    cell1.setValue("Hello World!");
     
    var cell2 = row.addCell("D",excel.CELL_TYPE_STRING);    // set type
    cell2.setValue("Hello World!");

    In case you have tables with many similar string values you can enable the "shared string" function. Note that this must be done for every cell as inline strings are the default.

    // add a cell with a string value
    var cell = row.addCell("D",excel.CELL_TYPE_STRING_TAB);
    cell.setValue("Hello World!");

    This will save memory while saving and speed up loading of the created Excel file.

    Fills

    You can define pattern fills. Possible options for a pattern fill are: fgColor, bgColor and type. Lets create a red solid fill:

    // create a color (RGBA)
    var red = wb.color(255,0,0,0);
    // create a pattern fill
    var redFill = wb.addPatternFill({fgColor:red, type:excel.PATTERN_TYPE_SOLID});
    // create a cell style with the red fill
    var redFillStyle = wb.addStyle(defStyle, {fill: redFill});
    // apply the style
    cell.setStyle(dateStyle);

    Borders

    You can define the borders for a cell. First you have to create a border representation object which is used to define the border style.

    // create a color (RGBA)
    var black = wb.color(0,0,0,0);
    // create a thick border presentation with a black color
    var thickBorderPr = wb.createBorderPr(excel.BORDER_STYLE_THICK, black);
    // create a border type, bottom line is set to thickBorderPr
    var border = wb.addBorder({bottom:thickBorderPr});
    // create a cell style with the border
    var borderStyle = wb.addStyle(defStyle, {border: border});
    // apply the style
    cell.setStyle(borderStyle);

    Fonts

    You can create new fonts which are derived from the default font. You can set the size and whether the font is bold or not (default).

    // create a bold font, the font is derived from the default font
    var boldFont = wb.addFont({bold: true});
    // create a cell style with the bold font
    var boldFontStyle = wb.addStyle(defStyle, {font: boldFont});
    // apply the style
    cell.setStyle(boldFontStyle);

    Column width

    Define the width for one or more columns:

    // set the width of the first column to 30
    sheet.setColumn(1,1,30);
    // set the width of columns 2 - 5 to 50
    sheet.setColumn(2,5,50);

    Merge cells

    // merge cells horizontal
    sheet.mergeCell("A2:D2");
    // merge cells vertical
    sheet.mergeCell("A3:A6");

    AutoFilter

    // use a range which contains data! 
    sheet.setAutoFilter("A1:D1");

    Change history

    0.1.3 - add callback to Workbook.save method
    0.1.4 - fixed issues #8, #9
    0.1.5 - add support for italics, text rotation and text wrapping
    0.1.6 - a 'Standard' style is created by default, access it via the getStyle function
    0.2.0 - add Workbook.saveToStream to save the resulting ZIP directly to a file stream
    0.2.1 - fixed issue with wrong style IDs
    0.3.0 - add basic support for AutoFilter, fixed error in string table
    0.3.1 - add Row.setStyleForAllCells convenience method
    0.3.2 - added font underline property

    Install

    npm i elxml

    DownloadsWeekly Downloads

    28

    Version

    0.3.2

    License

    MIT

    Last publish

    Collaborators

    • dunkelrot