Get excel column headers using SheetJS

by

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.

2 thoughts on “Get excel column headers using SheetJS”

  1. 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>
    Reply

  2. SheetJS: Pass the worksheet and the function returns an array of column names:

    get_header_row(sheet) {
    let headers = [];
    let range = XLSX.utils.decode_range(sheet['!ref']);
    let 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) {
      let cell = sheet[XLSX.utils.encode_cell({c:C, r:R})] /* find the cell in the first row */

      let hdr = "";
        if(cell && cell.t) hdr = XLSX.utils.format_cell(cell);

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

    Reference: Solution adopted from Github Issue

    More Info: If you want to know more about the general structure, then please look at the official link

    Reply

Leave a Comment