Exceljs Formatter

excel-formatter

excel-formatter writes json object to excel file.

Usage

    var headers = ["name", "description", "heading"];
    var sample_json = [
      {
        "name": "Saurabh Pandey",
        "addr": [
          { "city": "560103", "area": "Bellundur", "pincode": "Bangalore", "state": "Karnataka" }
        ],
        "phone_no": [
          { "phone": "8090951024", "country_code": "+91" }
        ]
      },
      {
        "name": "Saurabh Pandey",
        "addr": [
          { "city": "560103", "area": "Bellundur", "pincode": "Bangalore", "state": "Karnataka" }
        ],
        "phone_no": [
          { "phone": "8009994420", "country_code": "+91" }
        ],
      }
    ];
    
    var _headerStyle = {
      fill: {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFFFFF00' },
        bgColor: { argb: 'FF0000FF' }
      },
      font: { name: 'Calibri', family: 4, size: 12, bold: true },
      border: { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } }
    };
  
    var _cell_style = {
      alignment: { vertical: 'middle', horizontal: 'center', wrapText: true },
      border: { top: { style: 'thin' }, left: { style: 'thin' }, bottom: { style: 'thin' }, right: { style: 'thin' } }
    };
    var config = {
      headers: headers, //(optional)
      headerStyle: _headerStyle, //(optional)
      subHeaderStyle: _headerStyle, //(optional)
      cellStyle: _cell_style, //(optional)
      maxCellLen: 30 //(optional)

    }
    console.log(excelGen.export_json(
        sample_json, "test", config
      ) 
    );

Styles

Fonts

// for the wannabe graphic designers out there
{ 
    font:{
        name: 'Comic Sans MS',
        family: 4,
        size: 16,
        underline: true,
        bold: true
    }
}
// Cells that share similar fonts may reference the same font object after
// the workbook is read from file or stream

| Font Property | Description | Example Value(s) | | ------------- | ----------------- | ---------------- | | name | Font name. | 'Arial', 'Calibri', etc. | | family | Font family for fallback. An integer value. | 1 - Serif, 2 - Sans Serif, 3 - Mono, Others - unknown | | scheme | Font scheme. | 'minor', 'major', 'none' | | charset | Font charset. An integer value. | 1, 2, etc. | | size | Font size. An integer value. | 9, 10, 12, 16, etc. | | color | Colour description, an object containing an ARGB value. | { argb: 'FFFF0000'} | | bold | Font weight | true, false | | italic | Font slope | true, false | | underline | Font underline style | true, false, 'none', 'single', 'double', 'singleAccounting', 'doubleAccounting' | | strike | Font strikethrough | true, false | | outline | Font outline | true, false | | vertAlign | Vertical align | 'superscript', 'subscript'

Alignment

// set cell alignment to top-left, middle-center, bottom-right
{ alignment: { vertical: 'top', horizontal: 'left' } };

**Valid Alignment Property Values**

| horizontal       | vertical    | wrapText | shrinkToFit | indent  | readingOrder | textRotation |
| ---------------- | ----------- | -------- | ----------- | ------- | ------------ | ------------ |
| left             | top         | true     | true        | integer | rtl          | 0 to 90      |
| center           | middle      | false    | false       |         | ltr          | -1 to -90    |
| right            | bottom      |          |             |         |              | vertical     |
| fill             | distributed |          |             |         |              |              |
| justify          | justify     |          |             |         |              |              |
| centerContinuous |             |          |             |         |              |              |
| distributed      |             |          |             |         |              |              |

Borders

// set single thin border around A1
{ 
    border : {
        top: {style:'thin'},
        left: {style:'thin'},
        bottom: {style:'thin'},
        right: {style:'thin'}
    } 
}

Valid Border Styles

• thin
• dotted
• dashDot
• hair
• dashDotDot
• slantDashDot
• mediumDashed
• mediumDashDotDot
• mediumDashDot
• medium
• double
• thick

Fills

// fill A1 with red darkVertical stripes
{ 
    fill: {
        type: 'pattern',
        pattern:'darkVertical',
        fgColor:{argb:'FFFF0000'
    }
}

Pattern Fills

| Property | Required | Description | | -------- | -------- | ----------- | | type | Y | Value: 'pattern'Specifies this fill uses patterns | | pattern | Y | Specifies type of pattern (see Valid Pattern Types below) | | fgColor | N | Specifies the pattern foreground color. Default is black. | | bgColor | N | Specifies the pattern background color. Default is white. |

Valid Pattern Types

• none
• solid
• darkGray
• mediumGray
• lightGray
• gray125
• gray0625
• darkHorizontal
• darkVertical
• darkDown
• darkUp
• darkGrid
• darkTrellis
• lightHorizontal
• lightVertical
• lightDown
• lightUp
• lightGrid
• lightTrellis

Gradient Fills

| Property | Required | Description | | -------- | -------- | ----------- | | type | Y | Value: 'gradient'Specifies this fill uses gradients | | gradient | Y | Specifies gradient type. One of ['angle', 'path'] | | degree | angle | For 'angle' gradient, specifies the direction of the gradient. 0 is from the left to the right. Values from 1 - 359 rotates the direction clockwise | | center | path | For 'path' gradient. Specifies the relative coordinates for the start of the path. 'left' and 'top' values range from 0 to 1 | | stops | Y | Specifies the gradient colour sequence. Is an array of objects containing position and color starting with position 0 and ending with position 1. Intermediary positions may be used to specify other colours on the path. |