Reading Excel file using node.js

Question

Okay so i am using the FileUploader module to upload my file from angular to my REST API:

var uploader = $scope.uploader = new FileUploader({
    url: api.getUrl('uploadCompetence',null)
});

This is sent to the following POST function:

        router.route('/api/uploadCompetence')
        .post(function (req, res) {

        // This is where i want to read the file

            var competence = Competence.build(req.body.location);
            competence.add(function (success) {
                    res.json({message: 'quote created!'});
                },
                function (err) {
                    res.status(err).send(err);
                });
        })

Now my goal is to read the excel file and then add each row to my database.

However im not quite sure how i can read the file from Node.js i have debugged my server and couldnt find the file anywhere but the the api is being called from my Angular application

Can anyone push me in the right direction? :)

By excel, what do you mean? Semicolon seperated CSV files, or .xlsx files? — aludvigsen
– aludvigsen, Commented Mar 4, 2015 at 17:39
@aludvigsen xlsx but i am not entirely sure that it is actually sending the file where can i check that do you know? — Marc Rasmussen
– Marc Rasmussen, Commented Mar 4, 2015 at 18:30
I've used node-xlsx in one of my projects. Pretty easy to use. — cleong
– cleong, Commented Mar 4, 2015 at 18:41

Diogo Cardoso · Accepted Answer · 2015-11-05 15:50:29Z

119

There are a few different libraries doing parsing of Excel files (.xlsx). I will list two projects I find interesting and worth looking into.

Node-xlsx

Excel parser and builder. It's kind of a wrapper for a popular project JS-XLSX, which is a pure javascript implementation from the Office Open XML spec.

node-xlsx project page

Example for parsing file

var xlsx = require('node-xlsx');

var obj = xlsx.parse(__dirname + '/myFile.xlsx'); // parses a file

var obj = xlsx.parse(fs.readFileSync(__dirname + '/myFile.xlsx')); // parses a buffer

ExcelJS

Read, manipulate and write spreadsheet data and styles to XLSX and JSON. It's an active project. At the time of writing the latest commit was 9 hours ago. I haven't tested this myself, but the api looks extensive with a lot of possibilites.

exceljs project page

Code example:

// read from a file
var workbook = new Excel.Workbook();
workbook.xlsx.readFile(filename)
    .then(function() {
        // use workbook
    });

// pipe from stream
var workbook = new Excel.Workbook();
stream.pipe(workbook.xlsx.createInputStream());

edited Nov 5, 2015 at 15:50

Diogo Cardoso

22.4k26 gold badges104 silver badges139 bronze badges

answered Mar 4, 2015 at 19:17

aludvigsen

6,0013 gold badges29 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Liudmila Dobriakova Over a year ago

i'm getting this as an obj: Object = [{"name":"Sheet1","data":[[]]}] what could be wrong?

Felipe Augusto · Accepted Answer · 2017-11-05 20:22:25Z

91

You can also use this node module called js-xlsx

1) Install module
npm install xlsx

2) Import module + code snippet

var XLSX = require('xlsx')
var workbook = XLSX.readFile('Master.xlsx');
var sheet_name_list = workbook.SheetNames;
var xlData = XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list[0]]);
console.log(xlData);

edited Nov 5, 2017 at 20:22

Felipe Augusto

8,22413 gold badges43 silver badges75 bronze badges

answered Jul 26, 2017 at 8:27

Charitha Goonewardena

4,7542 gold badges39 silver badges40 bronze badges

9 Comments

kumbhani bhavesh Over a year ago

how will you append ?

sundagy Over a year ago

very poor styles support.

Gopal Over a year ago

@Charitha please help me to retrieve the header columns or null column values in the each array.

rachit_verma Over a year ago

I want to add this data to mongo DB. How do I do that ?

Ruben Over a year ago

Note: The npm package hasn't been updated since 2020

|

Connect2sky · Accepted Answer · 2020-01-15 12:52:53Z

12

You can use read-excel-file npm.

In that, you can specify JSON Schema to convert XLSX into JSON Format.

const readXlsxFile = require('read-excel-file/node');

const schema = {
    'Segment': {
        prop: 'Segment',
        type: String
    },
    'Country': {
        prop: 'Country',
        type: String
    },
    'Product': {
        prop: 'Product',
        type: String
    }
}

readXlsxFile('sample.xlsx', { schema }).then(({ rows, errors }) => {
    console.log(rows);
});

answered Jan 15, 2020 at 12:52

Connect2sky

3474 silver badges13 bronze badges

Comments

Codemaker2015 · Accepted Answer · 2018-06-29 07:08:19Z

9

install exceljs and use the following code,

var Excel = require('exceljs');

var wb = new Excel.Workbook();
var path = require('path');
var filePath = path.resolve(__dirname,'sample.xlsx');

wb.xlsx.readFile(filePath).then(function(){

    var sh = wb.getWorksheet("Sheet1");

    sh.getRow(1).getCell(2).value = 32;
    wb.xlsx.writeFile("sample2.xlsx");
    console.log("Row-3 | Cell-2 - "+sh.getRow(3).getCell(2).value);

    console.log(sh.rowCount);
    //Get all the rows data [1st and 2nd column]
    for (i = 1; i <= sh.rowCount; i++) {
        console.log(sh.getRow(i).getCell(1).value);
        console.log(sh.getRow(i).getCell(2).value);
    }
});

answered Jun 29, 2018 at 7:08

Codemaker2015

16k9 gold badges118 silver badges93 bronze badges

1 Comment

sundagy Over a year ago

insert new rows in middle of table with merged cells is not supported.

Mani kiran · Accepted Answer · 2019-06-13 10:16:02Z

Useful link

https://ciphertrick.com/read-excel-files-convert-json-node-js/

 var express = require('express'); 
    var app = express(); 
    var bodyParser = require('body-parser');
    var multer = require('multer');
    var xlstojson = require("xls-to-json-lc");
    var xlsxtojson = require("xlsx-to-json-lc");
    app.use(bodyParser.json());
    var storage = multer.diskStorage({ //multers disk storage settings
        destination: function (req, file, cb) {
            cb(null, './uploads/')
        },
        filename: function (req, file, cb) {
            var datetimestamp = Date.now();
            cb(null, file.fieldname + '-' + datetimestamp + '.' + file.originalname.split('.')[file.originalname.split('.').length -1])
        }
    });
    var upload = multer({ //multer settings
                    storage: storage,
                    fileFilter : function(req, file, callback) { //file filter
                        if (['xls', 'xlsx'].indexOf(file.originalname.split('.')[file.originalname.split('.').length-1]) === -1) {
                            return callback(new Error('Wrong extension type'));
                        }
                        callback(null, true);
                    }
                }).single('file');
    /** API path that will upload the files */
    app.post('/upload', function(req, res) {
        var exceltojson;
        upload(req,res,function(err){
            if(err){
                 res.json({error_code:1,err_desc:err});
                 return;
            }
            /** Multer gives us file info in req.file object */
            if(!req.file){
                res.json({error_code:1,err_desc:"No file passed"});
                return;
            }
            /** Check the extension of the incoming file and 
             *  use the appropriate module
             */
            if(req.file.originalname.split('.')[req.file.originalname.split('.').length-1] === 'xlsx'){
                exceltojson = xlsxtojson;
            } else {
                exceltojson = xlstojson;
            }
            try {
                exceltojson({
                    input: req.file.path,
                    output: null, //since we don't need output.json
                    lowerCaseHeaders:true
                }, function(err,result){
                    if(err) {
                        return res.json({error_code:1,err_desc:err, data: null});
                    } 
                    res.json({error_code:0,err_desc:null, data: result});
                });
            } catch (e){
                res.json({error_code:1,err_desc:"Corupted excel file"});
            }
        })
    }); 
    app.get('/',function(req,res){
        res.sendFile(__dirname + "/index.html");
    });
    app.listen('3000', function(){
        console.log('running on 3000...');
    });

Shubham Verma · Accepted Answer · 2023-03-24 13:18:06Z

1

You can use the below method to read an excel file in NodeJs: Install and import xlsx.

const xlsx = require('xlsx');

Write a method to read file:

export const readExcelFile = async (path, filePath) => {
    try {
        const file = xlsx.readFile(`${path}/${filePath}`);
        let data = []
        const sheets = file.SheetNames
        for (let i = 0; i < sheets.length; i++) {
            const temp = xlsx.utils.sheet_to_json(
                file.Sheets[file.SheetNames[i]])
            temp.forEach((res) => {
                data.push(res)
            })
        }
        return data;
    }
    catch (err) {
        console.log(err);
    }
};

How to use:

const result = await readExcelFile(PATH_DOWNLOADED_FILE, sourceFileName);
console.log(`Result: ${JSON.stringify(result)}`);

answered Mar 24, 2023 at 13:18

Shubham Verma

10.2k8 gold badges70 silver badges87 bronze badges

1 Comment

Liudmila Dobriakova Over a year ago

using this code i can't access to the sheets data... it return me an array of one element Sheet1 with empty body. Could u help?

Pranjal Sharma · Accepted Answer · 2017-07-03 13:11:58Z

-2

Install 'spread_sheet' node module,it will both add and fetch row from local spreadsheet

answered Jul 3, 2017 at 13:11

Pranjal Sharma

331 silver badge3 bronze badges

1 Comment

yoges nsamy Over a year ago

an example would be useful in answering the question

Collectives™ on Stack Overflow

Reading Excel file using node.js

7 Answers 7

Node-xlsx

ExcelJS

1 Comment

9 Comments

Comments

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Node-xlsx

ExcelJS

1 Comment

9 Comments

Comments

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related