CODEWITHSUNDEEP
node.jsexcelxlsx
Madura Pradeep
Madura Pradeep

Reputation: 2454

Node xlsx module get headers of the excel file

How to get the headers of the given Excel file in node xlsx (https://www.npmjs.com/package/xlsx) module?

Upvotes: 5

Views: 17527

Answers (3)

Ashish sah
Ashish sah

Reputation: 755

Have a look here: https://www.npmjs.com/package/xlsx if we go through the documentation. It says that we need to pass options By default, sheet_to_json scans the first row and uses the values as headers. With the header: 1 option, the function exports an array of arrays of values.

So the whole code goes like this:

  const data = e.target.result;
  const workbook = XLSX.read(data, { type: "array" });
  console.log(workbook);
  const firstSheetName = workbook.SheetNames[0];
  const worksheet = workbook.Sheets[firstSheetName];
  const options = { header: 1 };
  const sheetData2 = XLSX.utils.sheet_to_json(worksheet, options);
  const header = sheetData2.shift();
  console.log(header); //you should get your header right here

Where e.target comes from your input.

Upvotes: 3

Sharan V K
Sharan V K

Reputation: 181

Did the following in xlsx v0.16.9

const workbookHeaders = xlsx.readFile(filePath, { sheetRows: 1 });
const columnsArray = xlsx.utils.sheet_to_json(workbookHeaders.Sheets[sheetName], { header: 1 })[0];

Upvotes: 14

Madura Pradeep
Madura Pradeep

Reputation: 2454

As I could find, there's no exposed method to get headers of the Excel file from the module. So I copied few functions (With all respect to author. https://github.com/SheetJS/js-xlsx) from their source code and make that work with few changes.

function getHeaders(sheet){
    var header=0, offset = 1;
    var hdr=[];
    var o = {};
    if (sheet == null || sheet["!ref"] == null) return [];
    var range = o.range !== undefined ? o.range : sheet["!ref"];
    var r;
    if (o.header === 1) header = 1;
    else if (o.header === "A") header = 2;
    else if (Array.isArray(o.header)) header = 3;
    switch (typeof range) {
        case 'string':
            r = safe_decode_range(range);
            break;
        case 'number':
            r = safe_decode_range(sheet["!ref"]);
            r.s.r = range;
            break;
        default:
            r = range;
    }
    if (header > 0) offset = 0;
    var rr = XLSX.utils.encode_row(r.s.r);
    var cols = new Array(r.e.c - r.s.c + 1);
    for (var C = r.s.c; C <= r.e.c; ++C) {
        cols[C] = XLSX.utils.encode_col(C);
        var val = sheet[cols[C] + rr];
        switch (header) {
            case 1:
                hdr.push(C);
                break;
            case 2:
                hdr.push(cols[C]);
                break;
            case 3:
                hdr.push(o.header[C - r.s.c]);
                break;
            default:
                if (val === undefined) continue;
                hdr.push(XLSX.utils.format_cell(val));
        }
    }
    return hdr;
}



function safe_decode_range(range) {
    var o = {s:{c:0,r:0},e:{c:0,r:0}};
    var idx = 0, i = 0, cc = 0;
    var len = range.length;
    for(idx = 0; i < len; ++i) {
        if((cc=range.charCodeAt(i)-64) < 1 || cc > 26) break;
        idx = 26*idx + cc;
    }
    o.s.c = --idx;

    for(idx = 0; i < len; ++i) {
        if((cc=range.charCodeAt(i)-48) < 0 || cc > 9) break;
        idx = 10*idx + cc;
    }
    o.s.r = --idx;

    if(i === len || range.charCodeAt(++i) === 58) { o.e.c=o.s.c; o.e.r=o.s.r; return o; }

    for(idx = 0; i != len; ++i) {
        if((cc=range.charCodeAt(i)-64) < 1 || cc > 26) break;
        idx = 26*idx + cc;
    }
    o.e.c = --idx;

    for(idx = 0; i != len; ++i) {
        if((cc=range.charCodeAt(i)-48) < 0 || cc > 9) break;
        idx = 10*idx + cc;
    }
    o.e.r = --idx;
    return o;
}

Call getHeaders function by passing the Work Sheet will return the headers array of the excel sheet.

Upvotes: 8

Related Questions