2

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Vinoth
  • 972
  • 1
  • 16
  • 47

2 Answers2

2

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

Nicolas Després
  • 1,279
  • 1
  • 12
  • 12
  • in the dropdown b1,b2.b3,...b10 for example(a,b,c,d,e,f,g,h,i,j,k,l)values i want. I dont want sum of b1 to b10. The dropdown should show only the cell c1 to c 10 – Vinoth May 22 '17 at 13:12
  • This is exactly what it does. Here is a full example: `// Require library var xl = require('excel4node'); // Create a new instance of a Workbook class var wb = new xl.Workbook(); // Add Worksheets to the workbook var ws = wb.addWorksheet('Sheet 1'); ws.cell(1,2).string('a'); ws.cell(2,2).string('b'); ws.addDataValidation({ type: 'list', allowBlank: 1, sqref: 'C1:C10', formulas: [ '=$B$1:$B$10' ] }); wb.write('Excel.xlsx');` – Nicolas Després May 22 '17 at 13:58
  • @ nicolas i want to edit only Column A to Column H all other column should protected and i want to declare the excel sheet name how to achieve this – Vinoth Jun 02 '17 at 11:25
  • @ nicolas i am struck to pass the $scope varible and that values should comes into the dropdown list . first i will explain my scenario i have bulk of data in my $scope variable i have passed that all values into excel dropdown but here it reflect only [object Object] can you tell me how to do this – Vinoth Jun 05 '17 at 10:00
  • Is it a new question? I believe I answered the original question. If this is a new question please open it in Stack Overflow following the guidelines on how to ask a question... – Nicolas Després Jun 05 '17 at 12:05
  • Hi nicolas I am not able to set dropdown to 2 or more column can you tell me how to set the dropdown to two or more column – Vinoth Jun 12 '17 at 08:24
  • To merge cell you can use the following API: ws.cell(startRow, startColumn, [[endRow, endColumn], isMerged]); – Nicolas Després Jun 27 '17 at 12:40
  • Hi @NicolasDesprés , I want to make a cell readonly. can you please help me to find this solution? – Praveen Gehlot Feb 06 '20 at 11:53
0

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

Abinash
  • 466
  • 7
  • 15