Issue with ValidationType.LIST in Java

Aspose.Cells Product Family
1

Hi,

We are new to Aspose. We are using this for generating Excel with dynamic data in Java.

Would like to whether there is any limitation on the total size of characters in the ValidationType.LIST.

Because when we add 10 items to list whose total size is 211(characters) , the drop down is generated properly. If we add the 11th item , the drop down is not generated in the excel sheet.

Kindly let us know what is the problem.

Thanks

Pradeepti.

This message was posted using Page2Forum from Advance Topics - Aspose.Cells for .NET and Java
2

Hi Pradeepti,

Well, I don't find the problem you have mentioned. Following is my testing code which works fine and attached is the output file.

Sample code:

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

// Get the first worksheet.
Worksheet ExcelWorkSheet = workbook.getWorksheets().getSheet(0);

// Add a new worksheet and access it.
Worksheet worksheet2 = workbook.getWorksheets().addSheet();

// Create a range with name in the second worksheet.
NamedRange range = worksheet2.getCells().createNamedRange("MyRange",0,4,10,4);

// Fill different cells with data in the range.
range.getCell(0,0).setValue("Blue asdflasdjf");
range.getCell(1,0).setValue("Red ladjfla aldsfjldf");
range.getCell(2,0).setValue("Green alsdjflasdjf alsdfjl");
range.getCell(3,0).setValue("Yellow lajdflajsdf adslf");
range.getCell(4,0).setValue("Brown ajdflajdljlkads d");
range.getCell(5,0).setValue("LightGreen lajsdlfjdas flkj");
range.getCell(6,0).setValue("LightBlue lajdlfjd adkfj");
range.getCell(7,0).setValue("Pink lajdsljdfkadfj akdsf");
range.getCell(8,0).setValue("ReddishGreen lajdlkfjlk ajdflkjd");
range.getCell(9,0).setValue("Black lajdlkfj aldjflkadjf");
range.getCell(10,0).setValue("LightGrey ljadlfkj lajdflkajd");
//range.getCell(11,0).setValue("Maroon ljadfslkjjadskfjl adjf");

// Obtain the existing Validations collection.
Validations validations = ExcelWorkSheet.getValidations();

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

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

// 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.setAlertType(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 of cells.
CellArea area = new CellArea(0,0,4,0);

// Add the Validation area.
validation.addCellArea(area);

// Save the excel file.
workbook.save("e:\\files\\1validationtypelist.xls");

And by the way which version of Aspose.Cells for Java you are using? Could you try out latest version / fix i.e.., 1.9.5

If you still find the problem, could you create a test code (with template file if you have) and paste it here to reproduce the issue, we will check it soon.

Thank you.

3

Hi,

We still have the problem. The DropDown is shown without any values. The drop down is for the 3rd column for 300 rows.

Please check the below code.

Validations err = sheet1.getValidations();

Validation errValid = err.get(err.add());

errValid.setType(ValidationType.LIST);

String verr = "Arch. guidelines not followed,Cache not handled properly,Code not clear,Coding guidelines not followed,DB guidelines not followed,Hardcoding,Improper indentation,Incorrect assignment,Incorrect business logic,Incorrect code comments,Incorrect error handling,Incorrect javadoc comments,Incorrect validation,Incorrect/complicated programming logic,Initialization/closing resources not done,Memory not handled properly,Missing code comments,Missing error handling,Missing header information,Missing javadoc comments,Missing reference,Missing validation,Not as per Design Document,Parameter mismatch,Performance tuning,Query not tuned properly,Repetitive code,Return value not checked,Session not handled properly,UI guidelines not followed,Unused/extra code";

errValid.setFormula1(verr);

CellArea area2 = new CellArea(1,2,300,2);

errValid.getAreaList().add(area2);

Kindly help us with a solution.

Thanks

Pradeepti

4

Hi,

Well, there is certain limit of characters for the input source data range for List Validation in MS Excel(97-2003). How could you do this (perform your task manually) in MS Excel, I think you cannot.

If MS Excel can do a task fine where as Aspose.Cells for Java does not, we will try to figure out the issue then.

Thank you.

5

Hi,

We have tried the way you have suggessted.

It's working to some extent.

The Drop Down list's values are visible only when we set the option "Apply these changes to all other cells with the same settings." under Data->Validation->Settings.

Is there any way that we can set this option through code in Java.

Thanks

Pradeepti

6

Hi,

OK, we will look into it and get back to you soon.

Thank you.

7

Hi,

Thank you for considering Aspose.

We have fixed the bug that causes drop down list to become invisible, please try the attached latest fix.

Thank you and Best Regards,