nextjs parse XLSX on API route from incoming request

Question

I have been trying to reduce my NextJS bundle size by moving my XLSX parsing to an API route. It uses the npm xlsx (sheetjs) package, and extracts JSON from a selected XLSX.

What I am doing in the frontend is

let res;
let formData = new FormData();
formData.append("file", e.target.files[0]);

  try {
    res = await axios.post("/api/importExcel", formData);
  } catch (e) {
    createCriticalError(
      "Critical error during file reading from uploaded file!"
    );
  }

On the API route I am unable to to read the file using XLSX.read()

I believe NextJS uses body-parser on the incoming requests but I am unable to convert the incoming data to an array buffer or any readable format for XLSX.

Do you have any suggestions about how to approach this issue?

I tried multiple solutions, the most viable seemed this, but it still does not work

export default async function handler(req, res) {
  console.log(req.body);

  let arr;
  let file = req.body;
  let contentBuffer = await new Response(file).arrayBuffer();

  try {
    var data = new Uint8Array(contentBuffer);
    var workbook = XLSX.read(data, { type: "array" });
    var sheet = workbook.Sheets[workbook.SheetNames[0]];
    arr = XLSX.utils.sheet_to_json(sheet);
  } catch (e) {
    console.error("Error while reading the excel file");
    console.log({ ...e });
    res.status(500).json({ err: e });
  }

  res.status(200).json(arr);
}

score 1 · Answer 1 · answered Jul 12 '21 at 15:20

Since you're uploading a file, you should start by disabling the body parser to consume the body as a stream.

I would also recommend using a third-party library like formidable to handle and parse the form data. You'll then be able to read the file using XLSX.read() and convert it to JSON.

import XLSX from "xlsx";
import formidable from "formidable";

// Disable `bodyParser` to consume as stream
export const config = {
    api: {
        bodyParser: false
    }
};

export default async function handler(req, res) {
    const form = new formidable.IncomingForm();

    try {
        // Promisified `form.parse`
        const jsonData = await new Promise(function (resolve, reject) {
            form.parse(req, async (err, fields, files) => {
                if (err) {
                    reject(err);
                    return;
                }

                try {
                    const workbook = XLSX.readFile(files.file.path);
                    const sheet = workbook.Sheets[workbook.SheetNames[0]];
                    const jsonSheet = XLSX.utils.sheet_to_json(sheet);
                    resolve(jsonSheet);
                } catch (err) {
                    reject(err);
                }
            });
        });
        
        return res.status(200).json(jsonData);
    } catch (err) {
        console.error("Error while parsing the form", err);
        return res.status(500).json({ error: err });
    }
}

hi there! can you tell me why we need to promisify form.parse — Vignesh s, Jan 04 '23 at 11:43
@Vigneshs The API route has to wait for the form parsing to finish before returning. That would not happen if the `form.parse` is not promisified. — juliomalves, Jan 04 '23 at 12:50

nextjs parse XLSX on API route from incoming request

1 Answers1