XLSX JS get header of column based upon address of cell

Question

I'm using xlsx-js with nodejs, and I'm trying to read through an xlsx sheet with a calendar format. Where a 1 denotes that an activity had occurred during that day. I can loop through each row to see if it's undefined or not, but how do I loop up to detect that cell's header?

Here's an example of the calendar format sheet that I'm trying to read

I've tried converting the sheet to json, but the json isn't helpful because it could not detect the header cell of each day that was marked.

Sheet to json

Code:

       let fileName = `example.xlsx`;

        try {
          let workbook = XLSX.readFile(`./excel/${fileName}`, {cellDates:true});
        } catch (err) {
          logger.log('error', err);
        }
        let first_sheet_name = workbook.SheetNames[0];
        /* Get worksheet */
        var worksheet = workbook.Sheets[first_sheet_name];
        var jsonWorksheet = XLSX.utils.sheet_to_json(worksheet);
        console.log(jsonWorksheet);

It would be helpful if you add code that you've tried.

Mateus Wolkmer
– Mateus Wolkmer

2019-07-29 20:06:02 +00:00
Commented Jul 29, 2019 at 20:06 — Mateus Wolkmer
– Mateus Wolkmer, Commented Jul 29, 2019 at 20:06

t hj · Accepted Answer · 2019-07-30 07:59:16Z

0

What I do to make it to json is :

var file = "D:/excel.xlsx";
const workbook = XLSX.readFile(file, { cellDates:true, cellNF:false});
const sheet_name_list = workbook.SheetNames;
var va=  XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list[0]])

answered Jul 30, 2019 at 7:59

t hj

934 bronze badges

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

Comments

Valentin Stamate · Accepted Answer · 2022-06-03 18:12:35Z

0

You can get the header values using regex and sheet_to_formulae:

function getSheetHeaders(sheet: WorkSheet) {
    const headerRegex = new RegExp('^([A-Za-z]+)1=\'(.*)$');

    const cells = XLSX.utils.sheet_to_formulae(sheet);
    return cells.filter(item => headerRegex.test(item)).map(item => item.split("='")[1]);
}

answered Jun 3, 2022 at 18:12

Valentin Stamate

1,4491 gold badge15 silver badges25 bronze badges

Collectives™ on Stack Overflow

XLSX JS get header of column based upon address of cell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related