xlsx package Write excel file to a custom path

Question

Using the xlsx package, I'm attempting to write an array of objects to Excel. Instead of the current directory, I want to write the file to a specific path.

 const fileName ='ouptputs/test.xlsx'
const workSheet = XLSX.utils.json_to_sheet(data);
const wb = XLSX.utils.book_new();
                
 XLSX.utils.book_append_sheet(wb, workSheet,fileName);
 const s = XLSX.writeFile(wb, fileName,{
     type:"file"
 });

But executing this I m getting the error

Sheet name cannot contain : \ / ? *

So how do I specify the path instead of generating the file to the current dir?

Answer 1

The issue is that when you send a string to the writeFile method as fileName, it attempts to locate the file with the name you gave in the current directory but it contains the character "/" that is not allowed in file names and is therefore creating the error.

So the solution is to create the path by using the "path" library, See below code for example

const path = require('path');

// You may need to use relative path in join function depending upon the working file location
const filePath = path.join(__dirname, 'ouptputs/test.xlsx'); // __dirname -> returns the current path of the working file
const workSheet = XLSX.utils.json_to_sheet(data);
const wb = XLSX.utils.book_new();
                
XLSX.utils.book_append_sheet(wb, workSheet, 'Sheet1'); // Sheet1 is the name of sheet that is created inside workbook
const s = XLSX.writeFile(wb, filePath, {
        bookType: 'xlsx',
        type: 'file'
});

I hope this helps you.