Remove quote marks from excel cells using node xlsx

Question

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 createExcelFile(testInfo, fileType, 
adaptationId) {
const [tomorrow, nextMonth] = await getDatesForFiles();

 const data = [
    {
        'storeNumber': 33228,
        'adaptationId': parseInt(adaptationId),
        'effectiveFrom': tomorrow.replaceAll('"', ''),
        'effectiveTo': nextMonth.replaceAll('"', '')
    }
];
// @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)
});

score 0 · Answer 1 · answered Jul 05 '23 at 14:09

0

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

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

answered Jul 05 '23 at 14:09

candre

593
3
8

I have and sadly this doesn't work. When I do a toString() on the buffer I get output like this: EffectiveTo where I think the t="str" determines the String, but still lost as to how to remove the double quotes – Steerpike Jul 05 '23 at 14:29

score -2 · Answer 2 · answered Jul 05 '23 at 10:52

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!

thank you, unfortunately it did not. I added some Playwright context to the question that may be relevant — Steerpike, Jul 05 '23 at 11:12
Welcome back to Stack Overflow. It looks like it's been a while since you've posted and may not be aware of the latest policies since your answer appears likely to have been entirely or partially written by AI (e.g., ChatGPT). As a heads-up, [posting of AI-generated content is not permitted on Stack Overflow](//meta.stackoverflow.com/q/421831). If you used an AI tool to assist with any answer, I would encourage you to delete it. Thanks! — NotTheDr01ds, Jul 05 '23 at 11:45
**Readers should review this answer carefully and critically, as AI-generated information often contains fundamental errors and misinformation.** If you observe quality issues and/or have reason to believe that this answer was generated by AI, please leave feedback accordingly. The moderation team can use your help to identify quality issues. — NotTheDr01ds, Jul 05 '23 at 11:45

Remove quote marks from excel cells using node xlsx

2 Answers2