Get excel column headers using SheetJS

Question

I am currently creating a web application that allows a user to upload an excel file into a database but before the user uploads the file I would like to allow them to check the headers of the excel file if it matches the preset on the database. The code below allows me to display everything on the excel file:

$('#inputfile').change(function(e){
                var reader = new FileReader();
                reader.readAsArrayBuffer(e.target.files[0]);
                reader.onload = function(e) {
                        var data = new Uint8Array(reader.result);
                        var wb = XLSX.read(data,{type:'array'});
                        var htmlstr = XLSX.write(wb,{sheet:"Sheet1", type:'binary',bookType:'html'});
                        $('#printHere')[0].innerHTML += htmlstr;
                }
});

I would like to only store the excel file's header in an array and display it. I'm new to Javascript so any help would be much appreciated.

score 8 · Answer 1 · answered Oct 01 '18 at 15:20

You can do something like:

  const header = []
  const columnCount = XLSX.utils.decode_range(ws['!ref']).e.c + 1
  for (let i = 0; i < columnCount; ++i) {
    header[i] = ws[`${XLSX.utils.encode_col(i)}1`].v
  }

Here is the whole example:

function extractHeader(ws) {
  const header = []
  const columnCount = XLSX.utils.decode_range(ws['!ref']).e.c + 1
  for (let i = 0; i < columnCount; ++i) {
    header[i] = ws[`${XLSX.utils.encode_col(i)}1`].v
  }
  return header
}

function handleFile() {
  const input = document.getElementById("file")
  const file = input.files[0]
  
  if (file.type !== 'application/vnd.ms-excel') {
    renderError()
  }
  
  const reader = new FileReader()
  const rABS = !!reader.readAsBinaryString
  reader.onload = e => {
    /* Parse data */
    const bstr = e.target.result

    const wb = XLSX.read(bstr, { type: rABS ? 'binary' : 'array' })
    /* Get first worksheet */
    const wsname = wb.SheetNames[0]
    const ws = wb.Sheets[wsname]

    const header = extractHeader(ws)
    renderTable(header)
  }
  
  if (rABS) reader.readAsBinaryString(file)
    else reader.readAsArrayBuffer(file)
}

function renderTable(header) {
  const table = document.createElement('table')
  const tr = document.createElement('tr')
  for (let i in header) {
    const td = document.createElement('td')
    const txt = document.createTextNode(header[i])
    td.appendChild(txt)
    tr.appendChild(td)
  }
  
  table.appendChild(tr)
  
  document.getElementById('result').appendChild(table)
}

function renderError() {
  const errorMsg = 'Unexpected file type'
  const error = document.createElement('p')
  error.setAttribute('class', 'error')
  const txt = document.createTextNode(errorMsg)
  error.appendChild(txt)
  document.getElementById('result').appendChild(error)
  throw new Error(errorMsg)
}

#result table tr td {
  border: 2px solid grey;
}

#result .error {
  color: red;
}

<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>

<input type="file" onchange="handleFile()" id='file' accept=".csv"/>
<div id="result"><div>

Uncaught TypeError: Cannot read property 'split' of undefined at Object.mt [as decode_range] (xlsx.full.min.js:13) — RahulMohan82, Aug 07 '20 at 11:57
I am getting this error on some xlsx files at this line XLSX.utils.decode_range(ws['!ref']).e.c + 1 — RahulMohan82, Aug 07 '20 at 11:58

score 0 · Answer 2 · answered Mar 27 '23 at 16:07

    const headers = {};
    const range = xlsx.utils.decode_range(worksheet['!ref']);
    let C;
    const R = range.s.r;
    /* start in the first row */
    for (C = range.s.c; C <= range.e.c; ++C) {
        /* walk every column in the range */
        const cell = worksheet[xlsx.utils.encode_cell({ c: C, r: R })];
        /* find the cell in the first row */
        let hdr = "UNKNOWN " + C; // <-- replace with your desired default
        if (cell && cell.t) hdr = xlsx.utils.format_cell(cell);
        headers[C] = hdr;
    }
    console.log(headers);

Get excel column headers using SheetJS

2 Answers2