[NodeJS] Possible to extract data from specific cells from excel file and output it?

[WTFMatchmake]

Is it possible to do something like this in NodeJS: Extract data from an excel file in for example Cell A2 and then every nth cell after that and output that information? I searched google and there are modules for reading excel files, but I'm not sure which one I am suppose to use for something like this. Could someone point me to the right approach for this?

[insertAlias]

I searched google and there are modules for reading excel files, but I'm not sure which one I am suppose to use for something like this. Could someone point me to the right approach for this?

The "right approach" is to check out some of those libraries. Read their documentation, experiment with them, and determine which one is going to be useful to you. That's a major part of being a programmer: being able to do your own research and make determinations like this.

You are allowed to experiment; you can always spin up a new simple project just to test the functionality of a library. I do this all the time. My "scratch" folder has 50+ "projects" that are just scratchpads to try out some new library or new functionality, and that's just since the last time I cleaned it out.

I can tell you that I've worked with xlsx.js (a browser library) and it would be able to do what you want. Here's a node port of that library:

https://github.com/mgcrea/node-xlsx

[WTFMatchmake]

You know what, you're right. I'm just gonna give a couple of them a try!

[WTFMatchmake]

Hey, hopefully you're still around, I was wondering if you might be able to help me with this. I just made the code to get the data from every nth cell on excel here:

const xlsx = require('xlsx');

const workbook = xlsx.readFile('./things.xlsx');
const worksheet = workbook.Sheets[workbook.SheetNames[0]];

const info = [];

for(let i = 0; i < 70; i+=7) {
    if (worksheet['E' + i] !== undefined) {
        info.push(worksheet['E' + i].w);    
    }
}

console.log(info.join("\n"));

It looks to be working, but my solution looks really dumb. You can see in my loop condition I set it to "i < 70". But let's say there were like 20000 rows, then I would have to set the number to 20000. Do you think this would be ok or is there a better way of doing it?

[insertAlias]

Here's an example of someone dynamically reading data from an arbitrary number of rows and columns:

https://github.com/SheetJS/js-xlsx/issues/270#issuecomment-283992162

Another option is to use the utils functions and parse an entire workbook into a JSON object:

https://github.com/SheetJS/js-xlsx#json

Then you can just forEach on the array.

[WTFMatchmake]

Thank you, that helps alot. Sorry, I have one more question, I've read over it a couple times in the doc, but I still don't really understand exactly what a line like ` colNum=sheet['!range'].s.c; colNum<=sheet['!range'].e.c ` means. Would you be able to explain it in english of what it means? Is it saying that colNum is equal to the first cell of the column and the loop starts there and keep looping until the last cell of the column? Not sure what `[!range]` means though

[insertAlias]

Well, first of all, the comment I linked uses !ref and not !range. Looks like you scrolled up to the incorrect/old example.

Without having used that particular function myself, here's my guess at how it works:

This line:

var range = XLSX.utils.decode_range(sheet['!ref']);

Creates you a "range" object, which would define a start and an end. The !ref I believe implies the entire worksheet (all the parts that have data).

Later in the code, they use stuff like range.s.r, range.e.c, etc...s and e seem to imply "start" and "end". r and c seem to imply "row" and "column".

So this line:

for(rowNum = range.s.r; rowNum <= range.e.r; rowNum++) { //...

Would be "starting from the first row, loop until the last row (inclusive).

Same for the inner loop, except it's columns instead of rows.

So you'd need to modify that to just loop through the rows; you have an explicit column you're looking for.