List Data Dropdown does not show selected value

Aspose.Cells Product Family
1

I have problems with your example ‘ListDataValidation’

The drop down shows the values Blue, Red, Green, Yellow but if I select one of them they are not displayed in the cell.

I attach the excel that was created here:.export3.zip (1.2 KB)

And an other question:
Once the selected entry would be displayed in the cell, how can I prevent that the user does edit it?

2

@phe,
I have noticed the issue with the java code and we will update it soon. Could you please test it with below code and let us know your feedback.

// Create a workbook object.
Workbook workbook = new Workbook();

// Get the first worksheet.
Worksheet worksheet1 = workbook.getWorksheets().get(0);

// Add a new worksheet and access it.
int i = workbook.getWorksheets().add();
Worksheet worksheet2 = workbook.getWorksheets().get(i);

// Create a range in the second worksheet.
Range range = worksheet2.getCells().createRange("E1", "E4");

// Name the range.
range.setName("MyRange");

// Fill different cells with data in the range.
range.get(0, 0).putValue("Blue");
range.get(1, 0).putValue("Red");
range.get(2, 0).putValue("Green");
range.get(3, 0).putValue("Yellow");

// Get the validations collection.
ValidationCollection validations = worksheet1.getValidations();

// Create Cell Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;

// Create a validation object adding to the collection list.
int index = validations.add(ca);
Validation validation = validations.get(index);

// Set the validation type.
validation.setType(ValidationType.LIST);

// Set the operator.
validation.setOperator(OperatorType.NONE);

// Set the in cell drop down.
validation.setInCellDropDown(true);

// Set the formula1.
validation.setFormula1("=MyRange");


// Enable it to show error.
validation.setShowError(true);

// Set the alert type severity level.
validation.setAlertStyle(ValidationAlertType.STOP);

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

// Set the error message.
validation.setErrorMessage("Please select a color from the list");

// Specify the validation area.
CellArea area = new CellArea();
area.StartRow = 0;
area.EndRow = 4;
area.StartColumn = 0;
area.EndColumn = 0;

// Add the validation area.
validation.addArea(area);

// Save the Excel file.
workbook.save(dataDir + "output.out.xls");

For preventing a user from editing the selected value, could you please provide a sample file generated by MS Excel. We will share the sample code to generate the same using Aspose.Cells.

3

Yes, this code works as expected.
Thank you very much.

4

@phe,

Good to know that the suggested code segment works for your needs. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.