How to validate an excel file imported to load in UI5 application

In this blog, we will check how we can validate a file, which we used to import in UI5 application just as we discussed in the previous blog.

It might happen, user might try uploading a wrong excel file by mistake. This scenario might be very unlikely and the user should be given a pop up saying the imported file is wrong!

To handle such scenarios, one way is to validate the imported file by comparing the headers of the imported file with the desired template.

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

First, try importing the desired template and debug the code to get the headers in the header variable as an array. Now you can store it as a string by doing header = header.toString();

Now, use this string value to compare it the next time to validate it to other template you import.

Hope this blog was easy to understand, if you still have some issue do comment down or write it to sapui5tutors@The whole idea is to compare the headers of the importing file to the desired file template.

• Tweet
• Share
• Share
• Share
• Share

About Admin MC3

This is dummy text. It is not meant to be read. Accordingly, it is difficult to figure out when to end it. But then, this is dummy text. It is not meant to be read. Period.

Related Post