excel-creator

NPM Package for creating Excel with subtitles from nested objects.

Prerequisites

This project requires NodeJS (version 8 or later) and NPM. Node and NPM are really easy to install. To make sure you have them available on your machine, try running the following command.

$ npm -v && node -v
6.4.1
v8.16.0

First make sure you are able to run python3 (Mac/Linux) or python (Windows) from the terminal. If you are not then you might need to add it to the PATH. If you want to use a version of python not in the PATH you should specify pythonPath.

DockerFile requirements

Install git for the @types/xlsx-populate dependency

RUN apk add --no-cache git

Download python and pip and udpate pip

RUN apk add --no-cache python3 py3-pip &&
pip3 install --no-cache-dir --upgrade pip

Download required libraries

RUN python3 -m pip install --no-cache-dir json_excel_converter &&
python3 -m pip install --no-cache-dir xlsxwriter

Table of contents

• Project Name
  • Prerequisites
  • DockerFile requirements
  • Table of contents
  • Getting Started
  • Installation
  • Usage
    • createExcel
      • variables
      • merges
  • Example
  • Credits
  • Authors
  • License

Getting Started

These instructions will get you a copy of the project up and running on your local machine for development and testing purposes. See deployment for notes on how to deploy the project on a live system.

Installation

BEFORE YOU INSTALL: please read the prerequisites

To install and set up the library, run:

$ npm install -S excel-creator

Or if you prefer using Yarn:

$ yarn add --dev excel-creator

Usage

createExcel

createExcel(workSheets: UserData[], stream = true, path?: string, pythonPath?: string): Promise<NodeJS.ReadableStream | string>

export interface ExcelFileStruct {
  fileName?: string;
  workSheets: UserData[];
}

type VerticalAlignment = 'top' | 'middle' | 'bottom';
type HorizontalAlignment = 'left' | 'center' | 'right';

export interface IHorizontalExcelOptions {
  verticalAlignment?: VerticalAlignment;
  horizontalAlignment?: HorizontalAlignment;
  boldHeaders?: boolean;
  headersBottomBorder?: boolean;
}

export interface IVerticalExcelOptions {
  verticalAlignment?: VerticalAlignment;
  horizontalAlignment?: HorizontalAlignment;
  boldHeaders?: boolean;
  headersLeftBorder?: boolean;
  headersRightBorder?: boolean;
}

export interface IOptions { vertical?: IVerticalExcelOptions; horizontal?: IHorizontalExcelOptions }

export interface UserData {
  workSheetName: string;
  translation?: { [key: string]: string };
  data?: Record<string, any>;
  options: IOptions;
}

Variables

• path

| Type | | --- | | string |

The path to save the file

• worksheets

| Type | | --- | | UserData |

The array containing the information aboute the sheets

Worksheets properties

workSheetName

| Type | Default value | | --- | --- | | string| 'sheet' |

The name of the sheet

data

| Type | Default value | | --- | --- | | Record<string, any> | 'center' |

The actual sheets text. Keys for headers and values for the values. If you want just one value the syntax is:

{ key: 'value' }

If you want just many values the syntax is:

{ key: ['value1','value2'] }

translation

| Type | Default value | | --- | --- | | translation: { beni: 'beni2', old: 'veryOld' }/undefined | undefined |

Translate any key or value to another text.

options

Contains the next properties:

vertical?: {

verticalAlignment

| Type | Default value | | --- | --- | | 'top'/ 'middle' / 'bottom' | 'top' |

The vertical alignment of the text

horizontalAlignment

| Type | Default value | | --- | --- | | 'left'/ 'center' / 'right' | 'center' |

The horizontal alignment of the text

boldHeaders

| Type | Default value | | --- | --- | | boolean | false |

Define if the headers are bold

headersBottomBorder

| Type | Default value | | --- | --- | | boolean | false |

Define if there is a line under the headers }

horizonal: {

verticalAlignment

| Type | Default value | | --- | --- | | 'top'/ 'middle' / 'bottom' | 'top' |

The vertical alignment of the text

horizontalAlignment

| Type | Default value | | --- | --- | | 'left'/ 'center' / 'right' | 'center' |

The horizontal alignment of the text

boldHeaders

| Type | Default value | | --- | --- | | boolean | false |

Define if the headers are bold

headersLeftBorder

| Type | Default value | | --- | --- | | boolean | false |

Define if there is a line left of the merged cells

headersLeftBorder

| Type | Default value | | --- | --- | | boolean | false |

Define if there is a line Right of the merged cells }

• stream

| Type | Default value | | --- | --- | | boolean / true |

Set as true if you want to get a stream, or false if you want to save the file in the path.

• pythonPath

| Type | Default value | | --- | --- | | string / null | null |

If you want to use a version of python not in the PATH you should specify pythonPath.

Merges

Horizontal merge:

If you want subtitle for specific key the syntax is:

{ mainTitleName: { subTitleName: 'value'} }

You can add how many nested subtitles that you want. For better understanding look at the example.

Vertical merge:

In order to achieve vertical merges you have to specify the key word #merge{number}# where the number is the number of cells you want to merge. The next object propery will be the only thing present in the merged cell. For example, if you have an array like so:

    {title:['#merge3#', 'a', 'f' ]}

The generated excel file will be as follows:

Example:

import { createExcel } from  'excel-creator';

const main = async () => {
  await createExcel(
    [
      {
        data: {
          major: ['#merge3#', 'a', '#merge3#', 'b', '#merge3#', 'c'],
          mm: { 22: [1, '2', '3'], 23: ['1', '2', '3'], 24: ['1', '2', '4'] },
          unit: ['8153', '8200', 'total'],
        },
        workSheetName: 'greatShit',
        options: {
          horizontal: {
            verticalAlignment: 'top',
            horizontalAlignment: 'center',
            boldHeaders: true,
            headersBottomBorder: true,
          },
          vertical: {
            verticalAlignment: 'top',
            horizontalAlignment: 'center',
            boldHeaders: true,
            headersRightBorder: true,
          },
        },
      },
    ],
    false,
    './index.xlsx',
  );
};

main();

result:

Credits

Binyamin Muller, Itay Tovim

Authors

• Binyamin Muller - Initial work
• Itay Tovim - Initial work

License

MIT License © Andrea SonnY