# @juraj336/node-red-buffer-xlsx

> A simple node to convert a JSON to an excel buffer

Latest version **1.0.1** (published 2022-05-08) · MIT license · 0 weekly downloads

## Install

```sh
npm install @juraj336/node-red-buffer-xlsx
pnpm add @juraj336/node-red-buffer-xlsx
yarn add @juraj336/node-red-buffer-xlsx
bun add @juraj336/node-red-buffer-xlsx
```

## Health

**Score 15/100 (F)** — status: abandoned.

Positive: no vulnerabilities.

Warnings: low downloads; no types; no esm support.

Negative: abandoned; low maintenance score.

## Facts

| | |
|---|---|
| Version | 1.0.1 |
| Published | 2022-05-08 |
| First published | 2022-05-08 |
| Weekly downloads | 0 |
| License | MIT |
| TypeScript types | none |
| Module format | CommonJS |
| Dependencies | 1 |
| Unpacked size | 22.9 KB |
| Known vulnerabilities | 0 |
| Install scripts | no |
| GitHub stars | 1 |
| Author | Juraj van Rietbergen |
| Maintainers | juraj336 |
| Keywords | node-red, JSON to buffered xlsx, xlsx |

## Links

- npm: https://www.npmjs.com/package/@juraj336/node-red-buffer-xlsx
- Repository: https://github.com/JurajvRietbergen/node-red-buffer-xlsx
- Homepage: https://github.com/JurajvRietbergen/node-red-buffer-xlsx#readme
- Issues: https://github.com/JurajvRietbergen/node-red-buffer-xlsx/issues
- npm.io page: https://npm.io/package/@juraj336/node-red-buffer-xlsx

## Dependencies (1)

- [better-xlsx](https://npm.io/package/better-xlsx.md) ^0.7.6

## Alternatives

- [@mapbox/jsonlint-lines-primitives](https://npm.io/package/@mapbox/jsonlint-lines-primitives.md) — 5.3M weekly downloads
- [reftools](https://npm.io/package/reftools.md) — 3.5M weekly downloads
- [@hey-api/openapi-ts](https://npm.io/package/@hey-api/openapi-ts.md) — 3.5M weekly downloads
- [@mapbox/geojson-rewind](https://npm.io/package/@mapbox/geojson-rewind.md) — 2.4M weekly downloads
- [turbo-stream](https://npm.io/package/turbo-stream.md) — 1.7M weekly downloads

## Recent versions

- 1.0.1 (latest) — 2022-05-08

## README

# node-red-buffer-xlsx
A simple node-red node for converting JSON to a buffered xlsx. This package uses the node package better-xlsx.

## Usage
To ensure the node works correctly the JSON has to be formatted in a certain way. All styling options are optional.

### Sheet Formatting
The JSON can consist of multiple sheets, these are the properties each sheet can have:
- sheet_name
- sheet_styling
- header_styling
- columns_styling
- rows

  **sheet_name** (required):
  This is the name the sheet shall receive at the bottom.

  **sheet_styling** (optional):
  Styling for the entire sheet, see **Styling Formats**

  **header_styling** (optional):
  Styling for the header row, see **Styling Formats**

  **columns_styling** (optional):
  columns_styling should be an array of object with the following info:
  - index
  - column_styling

    *index*: The column you want to apply the styling to starting at 0

    *column_styling*: See **Styling Formats**

  **rows** (required):
  See **Row Formatting**.
  
 **Sheet Example**:
 ```
 [
  {
    "sheet_name": "sheet1",
    "sheet_styling": {
      "hAlign": "center",
      "fSize": "11"
    },
    "header_styling": {
      "fBold": true
    },
    "columns_styling": [
      {
        "index": 0,
        "column_styling": {
          "fItalic": true
        }
      }
    ],
    "rows": [
      {
        // See # Row Formatting
      }
    ]
  }
]
```

### Row Formatting
The JSON can consist of multiple rows, these are the properties each row can have:
- row_styling
- cells

  **row_styling** (optional):
  Styling for the entire row, see **Styling Formats**

  **cells** (required):
  See **Cell Formatting**.

**Row Example**:
```
"rows": [
  {
    "row_styling": {
      "hAlign": "left"
     },
    "cells": [
      {
        // See # Cells Formatting
      }
    ]
  }
]
```

### Cell Formatting
The JSON can consist of multiple cells, these are the properties each cell can have:
- cell_value
- cell_styling

  **cell_value** (optional):
  Value that will be displayed in the cell

  **cell_styling** (optional):
  Styling for single cell, see **Styling Formats**.

**Cell Example**
```
"cells": [
  {
    "cell_value": "Hello there!",
    "cell_styling": {
      "fColor": "ffa2917d"
    }
  }
]
```

### Styling Formats
Each of the properties ending with styling can include the following optional properties:
- pattern_type
- fgColor
- bgColor
- hAlign
- vAlign
- indent
- shrinkToFit
- textRotation
- wrapText
- fSize
- fName
- fFamily
- fCharset
- fColor
- fBold
- fItalic
- fUnderline
- cell_format
- cell_formula

  **pattern_type**:
  Specifies if cell background should be filled or not with the following options:
  - solid
  - none

  **fgColor**:
  Decides the foreground colour of the cell, all colours should be in HEX RBGA format without the #.

  **bgColor**:
  Deicdes the background colour of the cell, all colours should be in HEX RGBA format without the #.

  **hAlign**:
  Horizontal Alignment of cell values with the following options:
  - general
  - center
  - left
  - right

  **vAlign**:
  Vertical Alignment of cell values with the following options:
  - general
  - top
  - bottom
  - center

  **indent**:
  Decides indent of cell, should be a numeric value.

  **shrinkToFit**:
  Decides whether the cell value should shrink to fit the cell, should be a boolean.

  **textRotation**:
  Decides the rotation of the cell value, should be a numeric value between 0 and 255.

  **wrapText**:
  Decides whether the cell value should wrap to fit the cell, should be a boolean.

  **fSize**:
  Decides the cell value's font size, should be a numeric value.

  **fName**:
  Decides the cell value's font family, should be a font family.
  
  **fFamily**:
  - TBD

  **fCharset**:
  Decides the cell value's charset, should be a charset.

  **fColor**:
  Decides the cell value's colour, all colours should be in HEX RGBA format without the #.

  **fBold**:
  Decides whether the cell's value is Bold or not, should be a boolean.

  **fItalic**:
  Decides whether the cell's value is Italic or not, should be a boolean.

  **fUnderline**:
  Decides whether the cell's value is underlined or not, should be a boolean.

  **cell_format**:
  Decides number formatting for the cell with following options as example:
  ```
  "0" // integer index to built in formats
  "0.00%" // string matching a built-in format
  "0.0%" // string specifying a custom format
  "0.00%;\\(0.00%\\);\\-;@" // string specifying a custom format, escaping special characters
  "m/dd/yy" // string a date format using Excel's format notation
  ```

  **cell_formula**:
  Decides the cell's formula, see excel formulas for more.
  
  **Borders**:
  Decides the cell's borders formatting using the following parameters:
  - all
  - top
  - right
  - bottom
  - left

  Each of them can have the following:
  
  - style 
  - bColor

   **style**:
   Style can consist of the following types of border styles:
   ```
   thin
   medium
   thick
   dotted
   hair
   dashed
   mediumDashed
   dashDot
   mediumDashDot
   dashDotDot
   mediumDashDotDot
   slantDashDot
   ```
   
   **bColor**:
   Decides the colour of the borders, all colours should be in HEX RGBA format without the #.

**Styling Priorities**:
Styling is done in the following order:
1. Cell Styling
2. Header Styling
3. Column Styling
4. Row Styling
5. Sheet Styling

Unless Style Merging is selected the top priority will always be the only style chosen. If Style Merging is selected however the node will attempt to merge styles while still following the priority.

**Full Styling Example**:
```
{
  "pattern_type": "solid",
  "fgColor": "ffa2917d",
  "bgColor": "43ff64d9",
  "hAlign": "center",
  "vAlign": "top",
  "indent": "3",
  "shrinkToFit": false,
  "textRotation": "155",
  "wrapText": true,
  "fSize": "11",
  "fName": "Calibri",
  "fFamily": "Calibri",
  "fCharset": "UTF-8",
  "fColor": "9b0f64d9",
  "fBold": true,
  "fItalic": false,
  "fUnderline": false,
  "cell_format": "m/dd/yy",
  "cell_formula": "A1 - C2",
  "borders": {
    "top": {
      "style": "medium",
      "bColor": "009b9bd9"
    },
    "right": {
      "style": "thin",
      "bColor": "9b0f64d9"
    },
    "bottom": {
      "style": "thick",
      "bColor": "9b0f64d9"
    },
    "left": {
      "style": "dashDotDot",
      "bColor": "009b9bd9"
    }
  }
}
```

## Full Examples
Here some examples of full JSON

### Simple JSON
```
[
  {
    "sheet_name": "SimpleSheet",
    "sheet_styling": {
      "pattern_type": "solid",
      "fgColor": "ffffffff",
      "bgColor": "FE0000",
      "hAlign": "left",
      "vAlign": "left",
      "borders": {
       "all": {
        "style": "thick",
        "bColor": "009b9bd9"
       }
      }
    },
    "header_styling": {
      "pattern_type": "solid",
      "fgColor": "ffffffff",
      "bgColor": "ffe4e2de",
      "hAlign": "center",
      "vAlign": "center",
      "fBold": true
    },
    "columns_styling": [
      {
        "index": 0,
        "column_styling": {
          "hAlign": "right",
          "vAlign": "center",
          "fBold": true
        }
      },
      {
        "index": 4,
        "column_styling": {
          "hAlign": "right",
          "vAlign": "top",
          "fItalic": true
        }
      }
    ],
    "rows": [
      {
        "row_styling": {
          "pattern_type": "solid",
          "fgColor": "ffffffff",
          "bgColor": "ffe4e2de",
          "hAlign": "center",
          "vAlign": "center"
        },
        "cells": [
          {
            "cell_value": "ID",
            "cell_formula": "",
            "cell_format": "",
            "cell_styling": {
              "fBold": false
            }
          },
          {
            "cell_value": "Name",
            "cell_styling": {
              "fBold": false
            }
          },
          {
            "cell_value": "Company",
            "cell_styling": {
              "fBold": false
            }
          },
          {
            "cell_value": "Location",
            "cell_formula": "",
            "cell_format": "",
            "cell_styling": {
              "fBold": false
            }
          },
          {
            "cell_value": "Price"
          },
          {
            "cell_value": "Amount"
          }
        ]
      },
      {
        "row_styling": {
          "pattern_type": "solid",
          "fgColor": "ffffffff",
          "bgColor": "ffe4e2de",
          "hAlign": "center",
          "vAlign": "center"
        },
        "cells": [
          {
            "cell_value": "238126"
          },
          {
            "cell_value": "James. B."
          },
          {
            "cell_value": "Makers.BV",
            "cell_styling": {
              "pattern_type": "solid",
              "bgColor": "430f6480",
              "fColor": "FF000000"
            }
          },
          {
            "cell_styling": {
              "fBold": true,
              "fgColor": "43ff6480"
            }
          },
          {
            "cell_value": "152,00",
            "cell_format": "$0,00"
          },
          {
            "cell_value": "16,00",
            "cell_formula": "A2 - E2"
          }
        ]
      }
    ]
  }
]
```
### Complex JSON
- To be made

# TODO:
- [ ] Testing
- [ ] Complex JSON
- [ ] Ability for more complex features
- [X] Border Support

---
_Source: https://npm.io/package/@juraj336/node-red-buffer-xlsx · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._