How to create the cell dropdown list programmatically using excel4node.js in node.js?

Question

I have used the excel4node js to create & download the excel file. I want to create the cell dropdown list in excel sheet dynamically. i.e. I have the 10 values from B1 to B10 cell, then i need to make cell C1 to C10 as dropdown list and the dropdown should contain values from b1 to b10. so the user allows to select the value for c1 to c10 cells using the dropdown list.

How to do this using excel4node js?

Answer 1

You can simply use ws.addDataValidation:

ws.addDataValidation({
    type: 'list',
    allowBlank: 1,
    sqref: 'C1:C10',
    formulas: [
        '=$B$1:$B$10'
    ]
});

You can also find all other option documented in https://www.npmjs.com/package/excel4node

Answer 2

If you want to specify list by yourself instead of referring a cell range, you can do like this,

ws.addDataValidation({
  type: 'list',
  allowBlank: true,
  prompt: 'Choose from dropdown',
  errorTitle: 'Invalid Option',
  error: 'Select Option from Dropdown',
  showDropDown: true,
  sqref: 'A1:A100',
  formulas: ['A,B,C,D,E,F'],
});

This will add the dropdown with options A,B,C,D,E,F