點燈坊

失くすものさえない今が強くなるチャンスよ

Reading Excel on Web API

Sam Xiao's Avatar 2022-11-22

Although we can’t read Excel files on Web API directly, we can use the xlsx package to finish the task.

Version

Brave 1.45.127

xlsx

excel000

Excel file is read by Web API.

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <script
      lang="javascript"
      src="https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js"></script>
    <title>HTML Lab</title>
  </head>
  <body>
    <input
      id="uploadFile"
      type="file"
      onchange="onChange()"
      accept=".csv,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel" />
  </body>
  <script>
    let { read, utils } = XLSX

    let onChange = () => {
      let file = document.querySelector('#uploadFile').files[0]
      let fileReader = new FileReader()
      fileReader.readAsArrayBuffer(file)

      fileReader.onload = e => {
        let workbook = read(e.target.result)
        let sheetName = workbook.SheetNames[0]
        let sheet = workbook.Sheets[sheetName]
        let data = utils.sheet_to_json(sheet)[0]

        console.log(data)
      }
    }
  </script>
</html>

Line 6

<script
  lang="javascript"
  src="https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js"></script>
  • Load XLSX package by CDN

Line 12

<input
  id="uploadFile"
  type="file"
  onchange="onChange()"
  accept=".csv,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel" />
  • id：define id for DOM element
  • onchange：fire onChange() on change event
  • accpet：only Excel files accept

Line 19

let { read, utils } = XLSX
  • Extract read() and utils() from XLSX

Line 21

let onChange = () => {
  let file = document.querySelector('#uploadFile').files[0]
  let fileReader = new FileReader()
  fileReader.readAsArrayBuffer(file)
}
  • file：get selected file by id and querySelector()
  • fileReader：create FileReader instance
  • readAsArrayBuffer()：use readAsArrayBuffer() to fill fileReader from file Object

Line 26

fileReader.onload = e => {
  let workbook = read(e.target.result)
  let sheetName = workbook.SheetNames[0]
  let sheet = workbook.Sheets[sheetName]
  let data = utils.sheet_to_json(sheet)[0]

  console.log(data)
}
  • onload()：read Excel file when fileReader Object finishes loading
  • read()：read selected file from e.target.result, which is an ArrayBuffer
  • SheetName[]：get the name of the sheets
  • Sheets：use Sheets[] by the sheetName to get the sheet
  • utils.sheet_to_json()：convert sheet to JSON

Conclusion

  • Reading Excel on Web API is much the sam as Reading Excel on Apline. Only difference is that we have to use id and querySelector() to get selected files

# Web API