Deal With Excel Sheets in Objects

xlconverter

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}
* ]
*
*/

N O T E :

callback function is required in all methods.

getters are using xlToObjectsOfSheet function that's why sheet name is required.

THAT’s IT.

References -

  1. NPM , https://www.npmjs.com/package/xlconverter
  2. GIT , https://github.com/hardy12994/xlconverter

Hope you Enjoy this Article. Please clap if you Like this.

Follow Me ❤️ for more Articles on JavaScript, Angular, React, Node.

--

--

--

Founder and C.E.O at XpRate.com | Want to help world to grow 🌱

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

setInterval(), setTimeout() and creating an accurate timer in JavaScript

Using Child Components in Angular Forms

Create A Subscription Service with Stripe in NodeJS & Express

Copy to Clipboard in React Native and React Native Web

Why You Should Learn JavaScript.

What I Learned at Work this Week: Let’s talk about hoisting

Unit Testing with Dependency Injection

Learn To Use Node.js With MySQL

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Hardeep Singh

Hardeep Singh

Founder and C.E.O at XpRate.com | Want to help world to grow 🌱

More from Medium

Never change arrays and objects directly in JavaScript.

Different methods to merge objects in JavaScript

Javascript Promises in plain English (I promise!)

Let , Var and Const in JS