@bgroup/excel ·

This repository contains a library that facilitates the creation and reading of Excel files in .xlsx and .csv formats. The main class, Excel, provides an interface for creating custom Excel files and reading existing files for further analysis. The library takes care of creating spreadsheets, managing column headers, and applying validations to the data, which eases the generation and processing of Excel files.

Installation

To use this package in your project, follow these steps:

1. Clone this repository to your local system.

2. Install the dependencies by running the following command in the /library directory:

   npm install @bgroup/excel

Excel Class

The Excel class is the heart of this library. It provides properties and methods for the creation and reading of Excel files. Below are descriptions of its properties and methods:

Import:

import { Excel } from '@bgroup/excel/excel';

Use Case 1: Creating an Excel File in XLSX Format

This use case describes how to create an Excel file in XLSX format.

Usage

// Example code to create an Excel file in XLSX format
const excel = new Excel();
const params = {
    pathname: 'output/',
    filename: 'example.xlsx',
    type: 'xlsx',
    sheetData: [
        {
            sheetName: 'Sheet1',
            data: [
                { name: 'Alice', age: 28 },
                { name: 'Bob', age: 32 },
            ],
            columnsHeader: [
                { header: 'Name', key: 'name' },
                { header: 'Age', key: 'age' },
            ],
        },
    ],
};

excel.create(params).then((result) => {
    if (result.status) {
        console.log(
            `XLSX file created successfully at: ${result.data.pathFile}`
        );
        return;
    }
    console.error(`Error creating the XLSX file: ${result.error}`);
});

Response

The response of this use case includes details about the status and location of the created file, as well as error details in case of failure.

Use Case 2: Reading an Excel File in XLSX Format

This use case describes how to read an Excel file in XLSX format. Column or cell validation by range is optional with the validations property, in this case, it's described with column validation.

Usage

// Example code to read an Excel file in XLSX format
const excel = new Excel();
const readParams = {
    filePath: 'input/example.xlsx',
    type: 'xlsx',
};

excel.read(readParams).then((result) => {
    if (result.status) {
        console.log('Data read successfully:');
        console.log(result.data);
        return;
    }
    console.error(`Error reading the XLSX file: ${result.error}`);
});

Response

The response of this use case includes the data read from the spreadsheet and error details in case of failure.

Use Case 3: Reading an Excel File in XLSX Format with Specific Cell Validations by Range

This use case describes how to read an Excel file in XLSX format applying specific cell validations by range.

Usage

// Example code to read an Excel file in XLSX format
const excel = new Excel();
const readParams = {
    filePath: 'input/example.xlsx',
    type: 'xlsx',
    validations: {
        cells: [
            {
                sheet: 'Sheet1',
                items: [
                    {
                        startRow: 2,
                        endRow: 2,
                        startCol: 1,
                        endCol: 1,
                        type: 'string',
                        regex: '^[A-Za-z ]+$',
                    },
                    {
                        startRow: 2,
                        endRow: 2,
                        startCol: 3,
                        endCol: 3,
                        type: 'number',
                    },
                ],
            },
        ],
    },
};

excel.read(readParams).then(result => {
    if (result.status) {
        console.log('Data read successfully

:');
        console.log(result.data);
        return;
    }
    console.error(`Error reading the XLSX file: ${result.error}`);
});

Response

The response of this use case includes the data read from the spreadsheet and error details in case of failure.

Use Case 4: Reading an Excel File in XLSX Format by Specific Sheet

This use case describes how to read an Excel file in XLSX format by a specific sheet.

Usage

// Example code to read an Excel file in XLSX format
const excel = new Excel();
const readParams = {
    filePath: 'input/example.xlsx',
    type: 'xlsx',
    sheet: 'Sheet1',
};

excel.read(readParams).then((result) => {
    if (result.status) {
        console.log('Data read successfully:');
        console.log(result.data);
        return;
    }
    console.error(`Error reading the XLSX file: ${result.error}`);
});

Response

The response of this use case includes the data read from the spreadsheet and error details in case of failure.

Use Case 5: Creating an Excel File in CSV Format

This use case describes how to create an Excel file in CSV format. Currently, CSV files cannot be created with more than one sheet.

Usage

// Example code to create an Excel file in CSV format
const excel = new Excel();
const params = {
    pathname: 'output/',
    filename: 'example.csv',
    type: 'csv',
    sheetData: [
        {
            sheetName: 'Sheet1',
            data: [
                { name: 'Alice', age: 28 },
                { name: 'Bob', age: 32 },
            ],
            columnsHeader: [
                { header: 'Name', key: 'name' },
                { header: 'Age', key: 'age' },
            ],
        },
    ],
};

excel.create(params).then((result) => {
    if (result.status) {
        console.log(
            `CSV file created successfully at: ${result.data.pathFile}`
        );
        return;
    }
    console.error(`Error creating the CSV file: ${result.error}`);
});

Response

The response of this use case is similar to that of Use Case 1.

Use Case 6: Reading an Excel File in CSV Format

This use case describes how to read an Excel file in CSV format, column validation is not currently available in this case.

Usage

// Example code to read an Excel file in CSV format
const excel = new Excel();
const readParams = {
    filePath: 'input/example.csv',
    type: 'csv',
};

excel.read(readParams).then((result) => {
    if (result.status) {
        console.log('Data read successfully:');
        console.log(result.data);
        return;
    }
    console.error(`Error reading the CSV file: ${result.error}`);
});

Response

The response of this use case is similar to that of Use Case 2.

Use Case 7: Invalid Parameters

This use case shows how to handle invalid parameters when creating or reading Excel files.

Usage

// Example code to handle invalid parameters
const excel = new Excel();

// Example 1: Invalid creation parameters
const invalidCreateParams = { pathname: null, filename: null, sheetData: null };

excel.create(invalidCreateParams).then((result) => {
    if (result.status) {
        console.log('File created successfully.');
        return;
    }
    console.error(`Error creating the file: ${result.error}`);
});

// Example 2: Invalid reading parameters
const invalidReadParams = { filePath: null, type: null };

excel.read(invalidReadParams).then((result) => {
    if (result.status) {
        console.log('Data read successfully.');
        return;
    }
    console.error(`Error reading the file: ${result.error}`);
});

Response

The response of this use case varies depending on the nature of the invalid parameters.

Excel Properties

| Property | Description | | ------------- | ------------------------------------------------------------------------------------------------------------------------------------- | | columnsHeader | An array of objects that defines the headers for the columns in the spreadsheets. These headers are used when creating an Excel file. | | workbook | An object from the exceljs library that represents the Excel workbook and is used to load existing Excel files. |

Excel Methods

| Method | Description | | ------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | create | This method is used to create a custom Excel file. It takes a params parameter object specifying the location, file name, sheet data, and more. Returns a promise resolving in an object with information about the status and file location, if successful. | | read | This method is used to read an existing Excel file, either in .xlsx or .csv format. It takes a params parameter object specifying the file location and validations to apply. |

Response Structure

create Response

When the create method is called, the response has the following structure:

| Property | Description | | -------- | ----------------------------------------------------------------------------------------------------------- | | status | Indicates whether the operation was successful (true) or failed (false). | | data | An object containing information about the created Excel file, including the path, file name, and location. | | error | Details of the error, in case the operation fails. Can be a text string or an array of errors. |

read Response

When the read method is called, the response has the following structure:

| Property | Description | | -------- | ---------------------------------------------------------------------------------------------- | | status | Indicates whether the operation was successful (true) or failed (false). | | data | An object that contains the data read from the spreadsheet, organized by sheet. | | error | Details of the error, in case the operation fails. Can be a text string or an array of errors. |

These response structures allow you to verify the status of the operation and access data or error details as appropriate.

Properties and Explanation of create Parameters

The create method accepts a params object that is used to customize the creation of an Excel file. The properties of params are detailed below:

create params Properties

| Property | Description | | --------- | ------------------------------------------------------------------------------------------------------------------------------------------- | | pathname | The location or path where the Excel file will be saved. Must be a text string. | | options | Additional options for creating the Excel file. It's an object used for specific file configurations. | | filename | The name of the Excel file to be created. Must be a text string. | | sheetData | An array of objects defining the data of the worksheets to be included in the Excel file. Each object in this array represents a worksheet. | | type | The type of Excel file to be created, which can be "csv" or "xlsx". Must be a text string. |

sheetData Properties in create

| Property | Description | | ------------- | --------------------------------------------------------------------------------------------------------------------------------------- | | sheetName | The name of the worksheet. Must be a text string. | | data | An array of objects representing the data to be included in the worksheet. Each object contains information about the rows and columns. | | columnsHeader | An array of objects that define the headers of the columns in the worksheet. These headers are used when creating an Excel file. |

Properties and Explanation of read Parameters

The read method is used to read an existing Excel file and apply validations as necessary. The properties of params are detailed below:

read params Properties

| Property | Description | | ----------- | ----------------------------------------------------------------------------------------------- | | filePath | The location of the Excel file to be read. Must be a text string. | | validations | An object containing validations for cells, including data type validations and regex patterns. | | type | The type of Excel file to be read, which can be "csv" or "xlsx". Must be a text string. |

validations Properties in read

The validations object is used to specify the validations that will be applied to the cells of the Excel file. The properties of validations are divided into separate tables below:

columns Properties

| Property | Description | | --------- | ----------------------------------------------------------------------------------------------------------- | | sheetName | The name of the worksheet to which the validations will be applied. Must be a text string. | | key | The key of the column to which the validations will be applied. Must be a text string. | | type | The expected data type in the column (optional). Can be "string", "number", "boolean", or "date". | | regex | A regular expression pattern to validate the values of the column (optional). Must be a regular expression. |

range Properties

| Property | Description | | --------- | --------------------------------------------------------------------------------------------------------- | | sheetName | The name of the worksheet to which the validations will be applied. Must be a text string. | | startRow | The start row number for the validation. Must be a number. | | endRow | The end row number for the validation. Must be a number. | | startCol | The start column number for the validation. Must be a number. | | endCol | The end column number for the validation. Must be a number. | | type | The expected data type in the range of cells (optional). Can be "string", "number", "boolean", or "date". | | regex |

A regular expression pattern to validate the values in the range of cells (optional). Must be a regular expression. |

These properties allow you to specify the validations to apply to the cells of the Excel file during reading.

Contributions

Contributions, issues, and feature requests are welcome. Feel free to check the issues page or open new ones.

License

This project is under the MIT license. See the LICENSE file for more information.