excel-creator v2.2.1
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
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
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago