Question 201095 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Remove quote marks from excel cells using node xlsx

I’m using node-xlsx to try and write a simple data structure to a new Excel file. When it gets written, I see no quotation marks around the cell data yet when I upload the file into a browser using automation, it gets rejected because quote marks have been added to all the string values. I’m not clear why this is happening

The code:

export async function createAdaptationExcelFile(testInfo, fileType, 
adaptationId) {
const [tomorrow, nextMonth] = await getDatesForAdaptationFiles();

const data = [
    ['installer number', 'Adaptation ID', 'effectiveFrom', 
    'EffectiveTo'],
    [33228, adaptationId, tomorrow, nextMonth],
];
// @ts-ignore
const buffer = xlsx.build([{name: "Sheet One", data: data}], { 
cellDates: false });

//write the buffer to a file in a temp folder
const tempFileName = uuid.v4() + '.xlsx';
const tempFilePath = path.join(process.cwd(), 'src' ,'test-data', 
'temp-files', tempFileName);
    fs.writeFileSync(tempFilePath, buffer);

    return [tempFileName, buffer];
}

All values except for the number 33228 are affected so looks like this is a lead – how can I prevent this behaviour? Using replace() with a regex seems to have no effect.

I am using Playwright’s fileChooser function to do the upload and I wonder if the error lies here?

await fileChooser.setFiles({
   name:fileName,
   mimeType:'application/vnd.ms-excel',
   buffer: Buffer.from(buffer)
});
Tags:

2

Answers


  1. 0

    The issue you are experiencing is likely related to the default behavior of the node-xlsx library when writing data to Excel files. By default, node-xlsx adds quotation marks around cell values that contain non-numeric data to ensure proper formatting and interpretation of the data when opening the file in Excel.

    To prevent the addition of quotation marks around string values, you can specify the cellDates option as false when calling the xlsx.build() function. This option instructs node-xlsx to treat all values as strings, preventing the automatic formatting of dates and other non-numeric values.

    Here’s an updated version of your code with the cellDates option set to false:

    export async function createNewExcelFile(testInfo, fileType, adaptationId) {
  const [tomorrow, nextMonth] = await getDatesForFile();

  const data = [
    ['installer number', 'Adaptation ID', 'effectiveFrom', 'EffectiveTo'],
    [33228, adaptationId, tomorrow, nextMonth],
  ];

  // @ts-ignore
  const buffer = xlsx.build([{ name: "Sheet One", data: data }], { cellDates: false });

  //write the buffer to a file in a temp folder
  const tempFilePath = path.join(process.cwd(), 'src/test-data/temp-files', uuid.v4() + '.xlsx');
  fs.writeFileSync(tempFilePath, buffer);

  return tempFilePath;
}

    By setting cellDates to false, the values will be treated as strings, and quotation marks will not be added around them. This should help prevent the issue you were experiencing when uploading the file.

    Give this approach a try and see if it resolves the problem. If you still encounter issues or have further questions, please let me know!

    Login or Signup to reply.
  2. 0

    Have you tried using mimeType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' – which is the .xlsx one.

    application/vnd.ms-excel is .xls, source

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search