Adding data validation Error

[amit-mn]

When I add data validation to worksheet then it is not generating a correct file and it shows repair option before open the excel file.

Below is the error message: Repaired Part: /xl/worksheets/sheet1.xml part with XML error. Load error. Line 1, column 1112. Removed Feature: Worksheet properties from /xl/worksheets/sheet1.xml part

Below is the adding data validation code: ws.addDataValidation({ type: 'list', sqref: 'A2:'+'A20', formulas: [ '=sheet2!$A$1:$A$3' ] }); Please provide solution for this. I found that this is coming due to formulas key in addDatavalidation.

[natergj]

@amit-mn I am unable to reproduce this error. I am using the following. If you continue to see the issue, could you let me know which version of excel4node you have installed, which version of MS Excel you see the issue in and also provide a full gist for code that can reproduce the issue.

https://gist.github.com/natergj/585a6c7624678dc93abd9d6eba2b1b32

Since you are referencing the acceptable list values from another sheet, be certain that you are naming the sheet exactly as you have referenced.

[Kadasiddha]

Hi @natergj / @nateratseas ,

I am facing similar issue. When we try to open file in Microsoft Excel its give prompt to repair the file. After repair the dropdown doesn't have the values.

let wb = new xl.Workbook();
let ws = wb.addWorksheet('Data');
let ws1 = wb.addWorksheet('VALIDATION_FUNCTIONS');
       
ws.cell(1, 1).string('validation');
ws1.cell(1, 1).string('function_name');

for (let i = 0; i < validationRec.length; i++) {
           let row = i + 2
           ws1.cell(row, 1).string(validationRec[i].function_name);
       }
ws.addDataValidation({
           type: 'list', allowBlank: false, sqref: 'A2:A100', formulas:[`=VALIDATION_FUNCTIONS!$A$2:$A$${validationRec.length+1}` ]
       });

Where validationRec has following JSON ARRAY (Added using sheet as function_name will keep on increasing in future )

[
  { function_name: 'APLHABETS_WITH_SPACE', id: 1 },
  { function_name: 'ALPHA_WITH_SPACE_AND_SPECIAL_CHAR', id: 2 },
  { function_name: 'APLHABETS_WITHOUT_SPACE', id: 3 },
  { function_name: 'ALPHA_NUMERIC_WITH_SPACE_AND_UNDERSCORE', id: 4 },
  { function_name: 'ALPHA_NUMERIC_WITHOUT_SPACE', id: 5 },
  { function_name: 'NUMERIC', id: 6 },
  { function_name: 'DATE_YYYYMMDD', id: 7 },
  { function_name: 'TIME_HHMMSS', id: 8 },
  { function_name: 'DATE_YYMMDD', id: 9 },
  { function_name: 'TIME_HHMM', id: 10 },
  { function_name: 'DOB_GREATER_THAN_OR_EQUAL_18', id: 11 },
  { function_name: 'FUTURE_DATE', id: 12 },
  { function_name: 'PAST_DATE', id: 13 },
  { function_name: 'DATE_MMDDYYYY', id: 14 }
]

Please let me know what I am doing wrong here.

Version I am using in package.json "excel4node": "^1.7.2"

[ArtStepanyuk]

Having the same issue, everything works fine in Libre