To read an xlsx file located in the public folder in a vuejs project

I am trying to read a quiz template, contained in an xlsx file, to afterwards make the quiz the template contains.

Storing the xlsx as a json in a database in not an option.

I have tried using the xlsx library to read directly the file from within the public file, but it didn't work as the path to the file wasn't found, and by looking at the documentation I have found browsers generally don't allow reading files this way, as it is deemed a security risk.

Is there a way to read xlsx files contained directly in the public directory?

Here is what I tried :

handleFile() {
    const workbook = XLSX.readFile('/xlsx/quiztemplate.xlsx')
    const firstSheetName = workbook.SheetNames[0]
    const worksheet = workbook.Sheets[firstSheetName]
    const excelData = XLSX.utils.sheet_to_json(worksheet)
    console.log(excelData)
  }

and here is the error :

Uncaught Error: Cannot access file /quiz/quiztemplate.xlsx

I checked the path a thousand times, so I don't think the path is the problem. The documentation says browsers don't allow this function, but I'm not sure that's really the issue as the function isn't outright rejected, it still attempts to read the file.

Solution

XLSX.readFile() is designed to be used in server-side (Node.js) environments.

Try to use this:

npm install xlsx

Since you cannot use readFile() directly in the browser, you need to fetch the file as a blob and then process it.

<template>
  <div>
    <button @click="handleFile">Load Quiz</button>
  </div>
</template>

<script>
import * as XLSX from 'xlsx';

export default {
  methods: {
    async handleFile() {
      try {
        const response = await fetch('/xlsx/quiztemplate.xlsx');
        const data = await response.arrayBuffer();
        const workbook = XLSX.read(data, { type: 'array' });
        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];
        const excelData = XLSX.utils.sheet_to_json(worksheet);
        console.log(excelData);
      } catch (error) {
        console.error("Error reading the Excel file:", error);
      }
    }
  }
}
</script>

Just to explain:

you can use the fetch API to retrieve the file from the public directory. so the path to the file is relative to the root of your public directory in your Vue.js project structure.

so u convert the fetched data to an ArrayBuffer. but why? Because the Excel file is a binary file, and this is how you can handle binary data in JavaScript on the client-side.

Use XLSX.read() from the xlsx library to parse the binary data. The type parameter is set to 'array' to indicate that the input data is an array buffer.

and after extract the necessary sheet and convert it to JSON format as you already intended.