Get the header column from excel

[kalai7890]

Hi ,
I am reading excel file through js-xlsx. my requirement is to read the the first headings row alone,
now i am doing this by
var columns = XLSX.utils.decode_range(workSheet['!ref']).e.c + 1;
for (z in workSheet) {
if(z[0] === '!') continue;
if(rowIndex == 0){
excelColumns[columnIndex] = (JSON.stringify(workSheet[z].v)).replace (/"/g,"");
}
else break;
columnIndex++;
if(columns == columnIndex){
rowIndex++;
}.
but this has some issues while reading the excel if the number of columns in the heading is less than number of columns in the remaining data row for this logic.

Is there any way to do this with js-xlsx library defined function to read the heading of excel.Any help will be much appreciated.

heading here i refer to the first line of the document.

[SheetJSDev]

@kalai7890 If you just want to pull the header row, the easiest way is to just walk the cells in the first row:

function get_header_row(sheet) {
    var headers = [];
    var range = XLSX.utils.decode_range(sheet['!ref']);
    var C, R = range.s.r; /* start in the first row */
    /* walk every column in the range */
    for(C = range.s.c; C <= range.e.c; ++C) {
        var cell = sheet[XLSX.utils.encode_cell({c:C, r:R})] /* find the cell in the first row */

        var hdr = "UNKNOWN " + C; // <-- replace with your desired default 
        if(cell && cell.t) hdr = XLSX.utils.format_cell(cell);

        headers.push(hdr);
    }
    return headers;
}

For simple access patterns, it may be easier to work with an array of arrays. See #212 (comment) for an example of working with the sheet_to_json utility function

[kalai7890]

what is range.s.c and range.e.c ? what is the difference?

[SheetJSDev]

It's discussed a bit in the README: the decoded range is an object where range.s is the upper-left corner of the range (so range.s.c is the first column and range.s.r is the first row) and range.e is the lower-right corner of the range (so range.e.c is the last column and range.e.r is the last row) The indices are 0-based, consistent with JS but different from Excel (the first element of a JS array is index 0, but the first row is number 1 in Excel)

For example, for the range reference A1:C5: the upper-left cell is A1, represented as the object {c:0, r:0}; the lower-right cell is C5, represented as the object {c:2, r:4}; so the range object is {s:{c:0, r:0},e:{c:2, r:4}}

The mnemonic here is: s for "start of range", e for "end of range", r for "row", c for "column"

[kalai7890]

you are brilliant!!! thank you so much

[sofathitesh]

Hello sir,
I need your help. I want to fetch the number of row and columns. How I do this using this library. Please help me for this. if you can explain this work with simple example its more useful for me. thank you.

[sofathitesh]

I am using Javascript this is my simple code.
var url = "myFile.xlsx";
var oReq = new XMLHttpRequest();
oReq.open("GET", url, true);
oReq.responseType = "arraybuffer";

oReq.onload = function(e) {
var arraybuffer = oReq.response;
var data = new Uint8Array(arraybuffer);
var arr = new Array();
for(var i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);
var bstr = arr.join("");

/* Call XLSX */
var workbook = XLSX.read(bstr, {type:"binary"});
var t = workbook.SheetNames;

//HOW I COUNT NUMBER OF ROWS AND COLUMN'S WHICH ARE NOT EMPTY
}

oReq.send();

[dominickp]

@SheetJSDev Thank function is a life saver. Thank you!

[ramu505]

hello, I want to read particular
rows in the xlsx file. how to read with this library

[thekingspaghettie]

@SheetJSDev when I use the function. It returns me "!ref" undefined

[victorj2307]

Hi everyone,
I was using this function to get Header Row (provided by @SheetJSDev):

function get_header_row(sheet) {
    var headers = [];
    var range = XLSX.utils.decode_range(sheet['!ref']);
    var C, R = range.s.r; /* start in the first row */
    /* walk every column in the range */
    for(C = range.s.c; C <= range.e.c; ++C) {
        var cell = sheet[XLSX.utils.encode_cell({c:C, r:R})] /* find the cell in the first row */

        var hdr = "UNKNOWN " + C; // <-- replace with your desired default 
        if(cell && cell.t) hdr = XLSX.utils.format_cell(cell);

        headers.push(hdr);
    }
    return headers;
}

But today I upgraded XLSX to version 0.12.3, and now there is an error saying "Property 'format_cell' does not exist on type 'XLSX$Utils"

Can you help me, please?

Is it there a new function to replace 'format_cell'?

[SheetJSDev]

@victorj2307 It's available in the JS module but currently isn't in the TS definition. If you would like to submit a PR, the type definitions are in /types/index.d.ts and the simplest fix would be

     decode_range(range: string): Range;
 
+    /** Format cell */
+    format_cell(cell: CellObject, v?: any, opts?: any): string;
+
     /* --- General Utilities --- */

[victorj2307]

Hi @SheetJSDev, thanks for your help.
I haven't create a PR in the past in any Github project, so I'm not familiar with how to do it.

I used a casting to any to avoid TS check. I'd appreciate if you make the change and it will be available in a future version.

This is what I did:

if (cell && cell.t) hdr = (<any>XLSX.utils).format_cell(cell);

Thanks in advance.

[valentin-stamate]

@kalai7890 I found an easier solution using regex.

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

[ashishsah1000]

This thing worked for me:

const workbook = XLSX.read(data, { type: "array" });
const firstSheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[firstSheetName];
const options = { header: 1 };
const sheetData = XLSX.utils.sheet_to_json(worksheet, options);

const header = sheetData.shift();
console.log(header);