CODEWITHSUNDEEP
javascriptnode.jsxlsxxlsx-js
Holly Do
Holly Do

Reputation: 31

XLSX JS get header of column based upon address of cell

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);

Upvotes: 1

Views: 3498

Answers (2)

Valentin Stamate
Valentin Stamate

Reputation: 1439

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]);
}

Upvotes: 0

t hj
t hj

Reputation: 93

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]])

Upvotes: 0

Related Questions