XLSX-Import

Github: https://github.com/Siemienik/XToolset/.../xlsx-import

Importing data from xlsx as simple as possible and map into configured data model with fully TypeScript typing support.

Part of XToolset - collection of tools makes handling spreadsheet handy, easy with great developer experience.

Getting Started

1. Install the package

npm i xlsx-import --save

2. Declare types if using TypeScript

interface Book {
    Id: number;
    Title: string;
    Author: string;
}

interface Person {
    FirstName: string;
    SecondName: string;
    Age: number;

    EmployedIn: string;
    IsUnemployed: boolean;
    IsEmployed: boolean;
}

3. Write a config

    const config = {
        books: {
            type: 'list',
            worksheet: 'Favourites',
            columns: [
                {
                    index: 1, // column index (1,2,3....n); `1` for column `A`
                    key: 'Id', // output item's field
                    mapper: (v: string) => Number.parseInt(v) // nullable, for transformating values
                },
                {index: 2, key: 'Title'}, // column `B`
                {index: 5, key: 'Author'}, // column `E`
            ],
            rowOffset: 1, // offset header row
        },
        owner: {
            worksheet: 'About list owner',
            type: 'object',
            fields: [
                {row: 2, col: 1, key: 'FirstName'}, // `A2`
                {row: 2, col: 2, key: 'SecondName'}, // `B2`
                {row: 3, col: 1, key: 'Age', mapper: Number.parseInt}, // `A3`
            ]
        },
    };

4. Use mappers (optional)

Mapper is a function that transforms values. You can use built-in mappers or write your own.

import { upperCaseMapper, isEmpty, isFilled, isValue } from 'xlsx-import/lib/mappers';

// isValue: assert / check the value
const isMale = isValue(['male', 'm']);
const isFemale = isValue(['female', 'f']);

const config = {
    // ...
    owner: {
        worksheet: 'About list owner',
        type: 'object',
        fields:[
            {row: 2, col: 1, key: 'FirstName'},
            {row: 2, col: 2, key: 'SecondName', mapper: upperCaseMapper},
            {row: 3, col: 1, key: 'Age', mapper: Number.parseInt},
            // three fields based on one cell but with different mapper
            {row: 2, col: 3, key: 'EmployedIn'},
            {row: 2, col: 3, key: 'IsUnemployed', mapper: isEmpty},
            {row: 2, col: 3, key: 'IsEmployed', mapper: isFilled},
            // custom mappers defined above
            {row: 2, col: 3, key: 'isMale', mapper: isMale},
            {row: 2, col: 3, key: 'isFemale', mapper: isFemale},
        ]
    },
};

5. Import data

const factory = new ImporterFactory();

//...

// for filePath:
const importer = await factory.from(filePath);

// for buffer:
// const importer = await factory.fromBuffer(buffer);

const books = importer.getAllItems<Book[]>(config.books); // it returns `Book[]`
const book = importer.getFirstItem<Book>(config.books); // it returns first `Book` item
const author = importer.getFirstItem<Person>(config.owner); // it returns `Person`

Command Line Interface (CLI)

It is possible to use the command line interface read more about xlsx-import-cli.

Examples

Example integrations with xlsx-import are placed in Siemienik/XToolset-examples directory. Currently, available:

Frontend frameworks

• Vue sample - it is a web app created with Vue that displays parsed xlsx file.
• React sample - it is a web app created with React that displays parsed xlsx file.
• Angular sample - it is a web app created with Angular that displays parsed xlsx file.

Console / CLI

• NodeJS sample of importing an invoice - it is pure JS example which runs on nodejs.
• NodeJS + TS sample of importing an invoice - it is Typescript example that can be transpiled down to pure JS or run directly with ts-node.
• Command line of xlsx-import examples with prepared scripts based on cli version of xlsx-import.

Backend

• ExpressJS sample - it is a small service created with ExpressJS can parse xlsx files with concrete structure

The Configuration

Xlsx supports two modes of importing files: Vertical List and Single Object.

Example:

const cfg = {
    // Indicates which worksheet should be used for data source .
    // For CSV typically `sheet 1` works perfectly.
    // string, required.
    worksheet:'sheet 1',

    // Indicates importing strategy, described below
    type : 'object' // or 'list'

    // ... type required fields, read below
}

• worksheet

(string, required) Indicates which worksheet should be used for data source. For CSV typically sheet 1 works perfectly.

• types

Enum ImportType	Raw values	Description
Default: List, aliases: ListVertical, Vertical	list, list-vertical, vertical	Used to import list of objects from worksheet reading from top to down (row by row). Each field has to defined column index (A is 1, B is 2 ... etc.). Fallback mechanism use this option for incorrect type value (warn message will be printed).
Object, aliases: Single, Singleton	object, single, singletion	Used to import single object from worksheet. Each field has to has defined row&col index.

Type: ListVertical

For type values: list, list-vertical, vertical

ListVertical iterates each row after offset and read data by using configured columns indexes.

Example:

const cfg = {
    worksheet:'sheet 1',
    type : 'list',

    // How many rows should omit, default 0
    rowOffset: 1,

    // Configure columns
    columns: [
        {
            // Column index (1,2,3....n); `1` for column `A`
            index: 1,

            // Indicade where in imported object data should be placed
            key: 'id',

            // A function which allow us to map a result field.
            // The xlsx-importer has build-in mappers, @see #Mappers
            mapper: (v: string) => Number.parseInt(v)
        },
        /* more columns ... */
    ],
}

Type: SingleObject

For type values: object, single, singletion

SingleObject do not iterate through the worksheet. It picks data from specific targets configured in the field: fields. It always produces exactly one object.

Example:

const cfg = {
    worksheet:'sheet 1',
    type : 'object',

    // configure fields
    fields: [
        {
            // Specify column index. Indexing starts from 1. That means, `1` is `A`, `2` is `B`, etc...
            // Indicade row index. Row indexing starts from 1, index 0 doesn't exist.
            // This example target into `A2`.
            col: 2, row: 2,

            // Indicade where in imported object data should be placed
            key: 'secondName',

            // A function which allow us to map a result field.
            // The xlsx-importer has build-in mappers, @see #Mappers
            // Below implemented mapper, which makes upper first letter.
            mapper: (v: string) => v.replace(/^[a-z]/, (match) => match.toUpperCase() )
        },
        /* more fields ... */
    ],
}

Mappers

Exported Name	Description
upperCaseMapper	Transforms string to upper case
lowerCaseMapper	Transforms string to lower case
jsonMapper	Transforms a JSON string to a JSON object or to null if parsing was not possible
integerMapper	Transforms string into integer
booleanMapper	Transforms string into boolean
numberMapper	Transforms string into number
dateMapper	Transforms date string into Date object
isValue	Examines if value is included in accepted values provided
isEmpty	Examines if input is empty
isFilled	Examines if input is not empty
splitMapper	Transforms string into array of items

splitMapper

Configurable and immutable splitMapper with possibility to use specific itemMapper<TReturnType>(mapper) or separator(string).

• .separator(';'): SplitMapper - set separator
• .itemMapper(itemMapper): SplitMapper - set mapper for items,

Setting separator or item mapper do not change origin mapper but create new one. As an item mapper may use also another splitMapper like below:

// Building a mapper
const sentenceSplitter = splitMapper.separator('. ');
const wordSplitter = splitMapper.separator(' ');
const wordsInSentencesMapper = sentenceSplitter.itemMapper<string[]>(wordSplitter);

// Standalone usage:
const input = 'Lorem ipsum dolor sit amet. consectetur adipiscing elit. Nullam placerat massa nec efficir. ';

const result = wordsInSentencesMapper(input);
// [
//     ['Lorem', 'ipsum', 'dolor', 'sit', 'amet'],
//     ['consectetur', 'adipiscing', 'elit'],
//     ['Nullam', 'placerat', 'massa', 'nec', 'efficir'],
//     ['']
// ]


// In a config:
// {row: 3, col: 1, key: 'words', mapper: wordsInSentencesMapper},

jsonMapper

JSON Mapper transforming JSON string to a JSON object.

// Building a mapper 
const mapper: JsonMapper = jsonMapper;

// Usage sample:
const jsonString: string = '{"name":"John Doe"}';
const result: object = mapper(jsonString); // { name: 'John Doe'}

// JsonMapper returns default value (null) in case it could not parse the input
const invalidJsonString: string = 'invalid JSON';
const result: null = mapper(jsonString); // null

The default value is customizable:

const mapper: JsonMapper = jsonMapper.default('My default value');
const invalidJsonString: string = 'invalid JSON';
const result: string = mapper(invalidJsonString); // 'My default value'

Support

If any help needed, just feel free to create an issue. We will be really thankful for added links into stackoverflow topics if exists.

We are ready to provide paid support, in order that please contact me: [email protected] or [email protected].

✅ Browser Support

Browser supporting has been proved for ✅ Chrome and ✅ Firefox in Angular & Vue & React Examples and theirs tests. Feel welcome to run examples by yourselves and check it.

✅ NodeJS Support

10	11	12	13	14	15	16	17	18	19	20	21
✅	✅	✅	✅	✅	✅	✅	✅	✅	✅	✅	✅