How to Make a Dropdown List in Excel using Node.js. For more details: https://kb.aspose.com/cells/nodejs/how-to-make-a-dropdown-list-in-excel-using-nodejs/

How to Make a Dropdown List in Excel using Node.js.

var aspose = aspose || {};
aspose.cells = require("aspose.cells");

//Set the license
new aspose.cells.License().setLicense("License.lic");

// Create a workbook
var wb = new aspose.cells.Workbook();

// Access the target sheet
var ws1 = wb.getWorksheets().get(0);

// Create another sheet 
var i = wb.getWorksheets().add();

// It will be used for reference data therefore get access to it
var  ws2 = wb.getWorksheets().get(i);

// Create a reference range
var  refRange = ws2.getCells().createRange("E1", "E4");

// Set the name
refRange.setName("ReferenceRange");

// Fill the reference data
refRange.get(0, 0).putValue("Circle");
refRange.get(1, 0).putValue("Rectangle");
refRange.get(2, 0).putValue("Line");
refRange.get(3, 0).putValue("Polygon");

// Get a reference to the validations
var validations = ws1.getValidations();

// Create a cell Area
var area = new aspose.cells.CellArea();
area.StartRow = 0;
area.EndRow = 4;
area.StartColumn = 0;
area.EndColumn = 0;

// Create a new validation using the defined area
var newValidation = validations.get(validations.add(area));

// Set validation type
newValidation.setType (aspose.cells.ValidationType.LIST);

// Set the operator type
newValidation.setOperator(aspose.cells.OperatorType.NONE);

// Set flag to set in-cell drop-down
newValidation.setInCellDropDown(true);

// Set the formula by using the reference data range name
newValidation.setFormula1("=ReferenceRange");

// Enable the show error flag
newValidation.setShowError(true);

// Set the validation alert type
newValidation.setAlertStyle(aspose.cells.ValidationAlertType.STOP);

// Set the error title
newValidation.setErrorTitle("Error Title");

// Set the error message
newValidation.setErrorMessage("Please select data from the list");

// Save the output file
wb.save("output.xls");
console.log("Validation added successfully");