Narcoleptic Programmers' Medicine

    @tormozz48/xlsx-template
    TypeScript icon, indicating that this package has built-in type declarations

    1.0.4 • Public • Published

    xlsx-template

    Fill workbook by given data into places marked by special placeholders.

    Build Status codecov

    Install

    Install package via npm:

    npm install @tormozz48/xlsx-template
    

    Usage Example

    Assume there ./template-simple.xlsx XLSX file in your working directory with given content:

    # A B C D E
    1 str(data.strVal) number(data.numberVal 0.00) date(data.dateVal dd/mm/yyyy) link(data.linkVal) {str(data.strVal)}
    2 number(data.numberVal) date(data.dateVal)
    const {XLSXPopulateTemplate} = require('xlsx-template');
    const xlsxPopulateTemplate = new XLSXPopulateTemplate();
     
    await xlsxPopulateTemplate.loadTemplate('./template-simple.xlsx');
    xlsxPopulateTemplate.applyData({
        data: {
            strVal: 'Some String value',
            numberVal: 3.14159,
            dateVal: new Date(2020, 0, 6),
            linkVal: {text: 'some link', ref: 'http://github.com'}
        }
    });
    await xlsxPopulateTemplate.toFile('./output.xlsx');

    Code above will create new file ./output.xlsx with replacing all placeholders from ./template-simple.xlsx on provided data. It also will set valid format to cells according to used placeholders.

    # A B C D E
    1 Some String value 3,14 06/01/2020 some link str(data.strVal)
    2 3,14159 06-01-2020

    Apply values given by arrays.

    It is possible to fill range of rows by values given as array. For example array of objects such as:

    [
        {
            strVal: 'Github',
            numberVal: 134.1222,
            dateVal: new Date(2020, 1, 1),
            linkVal: {text: 'github', ref: 'https://github.com'}
        },
        {
            strVal: 'Facebook',
            numberVal: 4352.232,
            dateVal: new Date(2020, 2, 23),
            linkVal: {text: 'facebook', ref: 'https://facebook.com'}
        },
        {
            strVal: 'Google',
            numberVal: 733.2122321,
            dateVal: new Date(2020, 3, 7),
            linkVal: {text: 'google', ref: 'https://google.com'}
        }
    ]

    applied to following template:

    # A B C D
    1 str(data[i].strVal) number(data[i].numberVal 0.00) date(data[i].dateVal dd/mm/yyyy) link(data[i].linkVal)

    will fill first three rows by corresponded values from data array:

    # A B C D
    1 Github 134,12 01/02/2020 github
    2 Facebook 4352,23 23/03/2020 facebook
    3 Google 733,21 07/04/2020 google

    Placeholders

    str()

    Paste given value to a cell marked with placeholder and use default cell string formatter.

    # A
    1 str(data.foo)
    xlsxPopulateTemplate.applyData({data: {foo: 'Hello World'}});
    # A
    1 Hello World

    number()

    Paste given value to a cell and use number formatter. Optionally use second argument of number placeholder function to specify number format which should be applied to cell value.

    # A
    1 number(data.foo, 0.00)
    xlsxPopulateTemplate.applyData({data: {foo: 3.14159}});
    # A
    1 3.14

    date()

    Paste given value to a cell and use date formatter. Optionally use second argument of date placeholder function to specify date format which should be applied to cell value. Default date format is dd-mm-yyyy.

    # A
    1 date(data.foo dd/mm/yyyy)
    xlsxPopulateTemplate.applyData({data: {foo: new Date(2020, 0, 9)}});
    # A
    1 09/01/2020

    link()

    Create link value in cell with placeholder. Needs to receive data item as object with fields: text and ref where text - is link text representation and ref - is link reference url.

    # A
    1 link(data.foo)
    xlsxPopulateTemplate.applyData({data: {foo: {text: 'Github', ref: 'https://github.com'}}});
    # A
    1 Github

    {} - raw formatter

    A special formatter which allows to simply expand inner placeholder without appliyng it. It is useful when there you need to fill template with some part of needed data at first stage and then fill rest of data at second stage.

    # A
    1 {str(data.foo)}
    xlsxPopulateTemplate.applyData({});

    After first call it simply expand inner placeholder str(data.foo) so it will be ready to use on next call.

    # A
    1 str(data.foo)
    xlsxPopulateTemplate.applyData({data: {foo: 'Hello World'}});
    # A
    1 Hello World

    array item placeholders

    To fill multiple rows below by corresponded array items you should use [i] to mark data node that should be applied as array of items.

    # A
    1 str(data[i].name)
    xlsxPopulateTemplate.applyData({data: [
        {name: 'Github'},
        {name: 'Facebook'},
        {name: 'Google'}
    ]);
    # A
    1 Github
    2 Facebook
    3 Google

    API

    .loadTemplate(templatePath)

    Async function to load XLSX workbook from buffer of file or event create it from scratch. Loads XLSX workbook from file:

    const xlsxPopulateTemplate = new XLSXPopulateTemplate();
    await xlsxPopulateTemplate.loadTemplate('./template-simple.xlsx');

    Loads XLSX workbook from buffer:

    const buffer = await fs.readFile('./template-simple.xlsx');
    const xlsxPopulateTemplate = new XLSXPopulateTemplate();
    await xlsxPopulateTemplate.loadTemplate(buffer);

    Creates XLSX workbook from scratch (empty workbook)

    const xlsxPopulateTemplate = new XLSXPopulateTemplate();
    await xlsxPopulateTemplate.loadTemplate();

    .applyData(data)

    Fills matched template placeholders with given data.

    const xlsxPopulateTemplate = new XLSXPopulateTemplate();
    await xlsxPopulateTemplate.loadTemplate();
    xlsxPopulateTemplate.applyData({foo: 'bar'})

    .toBuffer()

    Async function which serializes current workbook into buffer.

    const xlsxPopulateTemplate = new XLSXPopulateTemplate();
    await xlsxPopulateTemplate.loadTemplate();
    const buffer = await xlsxPopulateTemplate.toBuffer();

    .toFile(filePath)

    Async function which saves XLSX workbook into file with given filePath on local filesystem.

    const xlsxPopulateTemplate = new XLSXPopulateTemplate();
    await xlsxPopulateTemplate.loadTemplate();
    await xlsxPopulateTemplate.toFile('./my-awesome.xlsx');

    .workbook

    Getter which simply returns XLSX workbook instance of xlsx-populate2 package. It is usefull for direct manipulation with workbook or it inner parts like cells, rows and columns.

    const xlsxPopulateTemplate = new XLSXPopulateTemplate();
    await xlsxPopulateTemplate.loadTemplate();
     
    const workbook = xlsxPopulateTemplate.workbook;
    workbook.sheet('Sheet1').cell('A1').value('Foo');

    Development

    Some useful commands for development:

    • npm run build - compiles typescript code into javascript code.
    • npm run clean - cleans js dist folder with compiled javascript code.
    • npm run format - performs code formatting via prettier tool.
    • npm run lint - runs tslint syntax checker.
    • npm run test - runs mocha tests.
    • npm run test -watch - runs mocha tests in "watch" mode. Launch tests on every code change.
    • npm run test -cov - runs mocha tests with coverage calculation.

    Install

    npm i @tormozz48/xlsx-template

    DownloadsWeekly Downloads

    6

    Version

    1.0.4

    License

    MIT

    Unpacked Size

    134 kB

    Total Files

    7

    Last publish

    Collaborators

    • tormozz48