Exceljs data validation list cause error in Ms. Excel

Question

I use exceljs node module and add dropdown data validation

worksheet.getCell(cell).dataValidation = {
  type: 'list',
  allowBlank: true,
  formulae: ['"One,Two,Three,Four"'],
  showErrorMessage: true,
  errorStyle: 'error',
  errorTitle: 'Error',
  error: 'Value must be in the list'
};

The file successfully generated. But it can only be opened using Libre Office. Opening the file with Ms. Office will cause the following error:

Excel completed file level validation and repair.
Some parts of this workbook mau have been repaired or discarded.
Repaired Part: /xl/worksheets/sheet1.xml part.

How to solve this problem?

Christian Læirbag · Accepted Answer · 2020-01-03 20:13:57Z

2

Invert quotation marks in data validation formulae. It only supports and recognizes the other one.

worksheet.getCell(cell).dataValidation=
{
    type : "list",
    allowBlank : true,
    formulae : ["'One,Two,Three,Four'"],//<--------------------------------Right there
    showErrorMessage : true,
    errorStyle : "error",
    errorTitle : "Error",
    error : "Value must be in the list"
};

edited Jan 3, 2020 at 20:13

answered Oct 4, 2019 at 18:37

Christian Læirbag

3344 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Exceljs data validation list cause error in Ms. Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related