Skip to main content

NodeJS excel file parser & builder

 Node XLSX

Excel file parser/builder that relies on js-xlsx.


Usage

Installation

npm install node-xlsx --save


Examples

Parsing a xlsx from file/buffer, outputs an array of worksheets

import xlsx from 'node-xlsx';

// Or var xlsx = require('node-xlsx').default;


// Parse a buffer

const workSheetsFromBuffer = xlsx.parse(fs.readFileSync(`${__dirname}/myFile.xlsx`));

// Parse a file

const workSheetsFromFile = xlsx.parse(`${__dirname}/myFile.xlsx`);

Building a xlsx

import xlsx from 'node-xlsx';

// Or var xlsx = require('node-xlsx').default;


const data = [[1, 2, 3], [true, false, null, 'sheetjs'], ['foo', 'bar', new Date('2014-02-19T14:30Z'), '0.3'], ['baz', null, 'qux']];

var buffer = xlsx.build([{name: "mySheetName", data: data}]); // Returns a buffer

Custom column width

import xlsx from 'node-xlsx';

// Or var xlsx = require('node-xlsx').default;


const data = [[1, 2, 3], [true, false, null, 'sheetjs'], ['foo', 'bar', new Date('2014-02-19T14:30Z'), '0.3'], ['baz', null, 'qux']]

const options = {'!cols': [{ wch: 6 }, { wch: 7 }, { wch: 10 }, { wch: 20 } ]};


var buffer = xlsx.build([{name: "mySheetName", data: data}], options); // Returns a buffer

Spanning multiple rows A1:A4 in every sheets

import xlsx from 'node-xlsx';

// Or var xlsx = require('node-xlsx').default;


const data = [[1, 2, 3], [true, false, null, 'sheetjs'], ['foo', 'bar', new Date('2014-02-19T14:30Z'), '0.3'], ['baz', null, 'qux']];

const range = {s: {c: 0, r:0 }, e: {c:0, r:3}}; // A1:A4

const options = {'!merges': [ range ]};


var buffer = xlsx.build([{name: "mySheetName", data: data}], options); // Returns a buffer

Spanning multiple rows A1:A4 in second sheet only

import xlsx from 'node-xlsx';

// Or var xlsx = require('node-xlsx').default;


const dataSheet1 = [[1, 2, 3], [true, false, null, 'sheetjs'], ['foo', 'bar', new Date('2014-02-19T14:30Z'), '0.3'], ['baz', null, 'qux']];

const dataSheet2 = [[4, 5, 6], [7, 8, 9, 10], [11, 12, 13, 14], ['baz', null, 'qux']];

const range = {s: {c: 0, r:0 }, e: {c:0, r:3}}; // A1:A4

const sheetOptions = {'!merges': [ range ]};


var buffer = xlsx.build([{name: "myFirstSheet", data: dataSheet1}, {name: "mySecondSheet", data: dataSheet2, options: sheetOptions}]); // Returns a buffer

Beware that if you try to merge several times the same cell, your xlsx file will be seen as corrupted.


Using Primitive Object Notation Data values can also be specified in a non-abstracted representation.

Examples:


const rowAverage = [[{t:'n', z:10, f:'=AVERAGE(2:2)'}], [1,2,3];

var buffer = xlsx.build([{name: "Average Formula", data: rowAverage}]);

Refer to xlsx documentation for valid structure and values:


Cell Object: https://sheetjs.gitbooks.io/docs/#cell-object

Data Types: https://sheetjs.gitbooks.io/docs/#data-types

Format: https://sheetjs.gitbooks.io/docs/#number-formats

Troubleshooting

This library requires at lease nodeJS v4. For legacy versions, you can use this workaround before using the lib.


npm i --save object-assign

Object.prototype.assign = require('object-assign');

Contributing

Please submit all pull requests the against master branch. If your unit test contains javascript patches or features, you should include relevant unit tests. Thanks!


Available scripts

Script Description

start Alias of test:watch

test Run mocha unit tests

test:watch Run and watch mocha unit tests

lint Run eslint static tests

compile Compile the library

compile:watch Compile and watch the library

Authors


Comments

Post a Comment

Popular posts from this blog

How to use Ngx-Charts in Angular ?

Charts helps us to visualize large amount of data in an easy to understand and interactive way. This helps businesses to grow more by taking important decisions from the data. For example, e-commerce can have charts or reports for product sales, with various categories like product type, year, etc. In angular, we have various charting libraries to create charts.  Ngx-charts  is one of them. Check out the list of  best angular chart libraries .  In this article, we will see data visualization with ngx-charts and how to use ngx-charts in angular application ? We will see, How to install ngx-charts in angular ? Create a vertical bar chart Create a pie chart, advanced pie chart and pie chart grid Introduction ngx-charts  is an open-source and declarative charting framework for angular2+. It is maintained by  Swimlane . It is using Angular to render and animate the SVG elements with all of its binding and speed goodness and uses d3 for the excellent math functio...
Post a Comment
Read more

JavaScript new features in ES2019(ES10)

The 2019 edition of the ECMAScript specification has many new features. Among them, I will summarize the ones that seem most useful to me. First, you can run these examples in  node.js ≥12 . To Install Node.js 12 on Ubuntu-Debian-Mint you can do the following: $sudo apt update $sudo apt -y upgrade $sudo apt update $sudo apt -y install curl dirmngr apt-transport-https lsb-release ca-certificates $curl -sL https://deb.nodesource.com/setup_12.x | sudo -E bash - $sudo apt -y install nodejs Or, in  Chrome Version ≥72,  you can try those features in the developer console(Alt +j). Array.prototype.flat && Array.prototype. flatMap The  flat()  method creates a new array with all sub-array elements concatenated into it recursively up to the specified depth. let array1 = ['a','b', [1, 2, 3]]; let array2 = array1.flat(); //['a', 'b', 1, 2, 3] We should also note that the method excludes gaps or empty elements in the array: let array1 ...
Post a Comment
Read more

Understand Angular’s forRoot and forChild

  forRoot   /   forChild   is a pattern for singleton services that most of us know from routing. Routing is actually the main use case for it and as it is not commonly used outside of it, I wouldn’t be surprised if most Angular developers haven’t given it a second thought. However, as the official Angular documentation puts it: “Understanding how  forRoot()  works to make sure a service is a singleton will inform your development at a deeper level.” So let’s go. Providers & Injectors Angular comes with a dependency injection (DI) mechanism. When a component depends on a service, you don’t manually create an instance of the service. You  inject  the service and the dependency injection system takes care of providing an instance. import { Component, OnInit } from '@angular/core'; import { TestService } from 'src/app/services/test.service'; @Component({ selector: 'app-test', templateUrl: './test.component.html', styleUrls: ['./test.compon...
Post a Comment
Read more