Deal With Excel Sheets in Objects

4 min readMay 28, 2018

Hi All ! Hope you good there.

This Article is mainly for those who are dealing with Excel Sheets OR CSV Sheets and facing the difficulty and everytime write New code to get all the data from the Sheet then Save in Database.

I had also feel the same and having difficulty with extracting all the Stuff form Sheets and Play with it. Then I decide to make something which would help me and others to deal with data present in Sheet and Play with it.

So as you can see the above Picture and Heading of this article, you can understand what does it mean.

Yeah !! you were right 😃.

Xl-Converter, this is the thing which helps you to extract all the stuff from Sheet and set in the form of Objects or Array. Not Just this, it can also make Excel from objects, you can also make Queries in it to get your needed Data from Excel.

So Let’s Install it first -

npm install xlconverter --save

Conversions -

# Excel To Objects :

There are two methods to Extract Data from Excel. If there will be only one (particular) sheet present we will use xlToObjectsOfSheet, If you want to get all the sheets data then we will use xlToObjects.

Now we can Jump to Code -

xlToObjectsOfSheet -

let xlconverter = require('xlconverter');
let path="abv/cac/ac/ac.xlsx"; // path of sheet
let sheet= "sheet1"; // sheet data you want to get// xlToObjectsOfSheet, will provide objects of prticular sheet present in xl sheet;xlconverter.xlToObjectsOfSheet(path, sheet, function (err, data) {
          
          console.log(err);
          console.log(data);
});/**
* data -
* [{},{},{}]
* // object as rows
*/

xlToObjects -

let xlconverter = require('xlconverter');
let path="abv/cac/ac/ac.xlsx";// xlToObjects, will provide objects of all the sheets present in xl sheet;xlconverter.xlToObjects(path, function (err, data) {
        
         console.log(err);
         console.log(data);
});/**
* data -
* {
*   sheet1: [{},{},{}],
*   sheet2: [{},{},{}]
* }
* // object as rows
*/

# Object To Excel :

Want to make Excel from Data Dump ? Use this !!!

let xlconverter = require('xlconverter');let headers=["name","age"];
let rows=[{ name:"hardy",age:21 },{ name:"hardy",age:21 }];
let path="abc/cac/";
let fileName="myNewSheet"; //.xlsx is by default
let sheetName="sheet1";xlconverter.objectsToXl(headers, rows, path, fileName, sheetName,function (err, data) {
             console.log(err);
             console.log(data);
});/**
* if error -`sheetName` sheet not created
* data -
* `sheetName` sheet successfully created.
*/

Getters -

We can use Excel Sheet as a very small Database as we can do Extractions from that by putting some queries, The query can be made in form of Objects, Arrays Thing can be achieved like:

Any Perticular Row.
Any Perticular Column.
Get Number of Rows.
Get Number of Columns.
Get Data as Objects of Perticular Columns.

# Get Row :

Accept Query as Object Eg- {name:"Jhon",age:"21"}.
and type of query will be done.
Return Single Row which matches both Conditions First.

let rowQuery= {name:"Jhon",age:"21"};
let filePath="abc/cac/";
let sheetName="sheet1";xlconverter.getters.row(filePath, rowQuery, sheetName, function (err, data) {console.log(err);
      console.log(data);
});/**
* data-
*
* {name:"Jhon", age:"21", f_name:"D_Jhon"}
* // object as rows
*/

# Get Rows :

Accept Parameters as Object Eg- {name:"Jhon",age:"21"}.
and type of query will be done.
Return Multiple Rows which matches both Conditions.

let rowQuery= {name:"Jhon",age:"21"};
let filePath="abc/cac/";
let sheetName="sheet1";xlconverter.getters.rows(filePath, rowQuery, sheetName, function (err, data) {
         
         console.log(err);
         console.log(data);
});/**
* data-
*
* [{name:"Jhon", age:"21", f_name:"D_Jhon"},
* {name:"Jhon", age:"21", f_name:"D_Jhonee"}]
* // object as rows
*/

# Get Column :

Accept Parameters as String Eg- "name".
Return Array of Strings which is Present in that Column.

let colQuery= "name";
let filePath="abc/cac/";
let sheetName="sheet1";xlconverter.getters.coloumn(filePath, colQuery, sheetName, function (err, data) {
         
         console.log(err);
         console.log(data);
});/**
* data-
*
* ["Jhon","Jhon2","Jhon3"]
* // as column cells
*/

# Get Columns :

Accept Parameters as String in Array Eg- ["name","age"].
Return Object with Keys name and age and both of them have Array of Strings which is Present in respective Column.

let colQuery= ["name","age"];
let filePath="abc/cac/";
let sheetName="sheet1";xlconverter.getters.coloumns(filePath, colQuery, sheetName, function (err, data) {
         
         console.log(err);
         console.log(data);
});/**
* data-
*
* {
*  name:["Jhon","Jhon2","Jhon3"],
*  age:[21,33,13]
* }
* // as coloumn cells
*/

# Get Rows from Selective Columns :

Accept Parameters as String in Array Eg- ["name","age"].
Return all Rows with Selected Columns name and age as Objects in Array
Getting Selective Coloumns of Rows.

let colQuery= ["name","age"];
let filePath="abc/cac/";
let sheetName="sheet1";xlconverter.getters.selectiveColoumnsOfRows(filePath, colQuery, sheetName, function (err, data) {
        
         console.log(err);
         console.log(data);
});/**
* data-
*
* [
*   {name: "hardy", age: 12},
*   {name: "pery", age: 41},
*   {name: "bob", age: 42}
*  ]
* 
*/