Perform a list data validation

Aspose.Cells Product Family
1

Hi aspose comunity,

When i perform a list data validation, i get a correct drop down list with a .xls file, but when i save the same file with extension .xlsx i get values of the list in the same line(One line of values separated by ";" see attachements) . but what is surprising is when i select the list data and i go to the tab data->Data Validation) of the Excel file i find that the source list is good and after i click on save button without any change, i come back to the drop down list data and i find that the list become correct.

If you have meet the same problem an you have the solution, thank you to forward it.

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and use the latest version:
Aspose.Cells
for .NET v7.2.1.9 It should fix your problem.

If the problem still occurs, we will need your sample runnable but simpler project replicating this problem and the source xls/xlsx files you are using.

3

I’m sorry I didn’t say with what i’m working, i use aspose cells for java aspose-cells-7.2.1.8

4

Hi,

Thanks for your information.

It is good to know that you are using the latest version.

Please provide us your sample project replicating the problem, it will help us look into your issue.

Please also provide all the source xls/xlsx files which you are using in your code.

Thanks for your understanding.

5

But i dont use any shape in my source code, this is the part of my code of data validations :

ValidationCollection validations = worksheet.getValidations();

int index = validations.add();

Validation validation = validations.get(index);

// Set the validation type.

validation.setType(ValidationType.LIST);

// Set the in cell drop down.

validation.setIgnoreBlank(true);

validation.setInCellDropDown(true);

validation.setShowError(true);

validation.setErrorTitle("Error");

validation.setErrorMessage("You must select one of the value available in the drop-down list box");

validation.setShowInput(true);

validation.setInputMessage("");

// Specify the validation area of cells.

CellArea area = new CellArea();

area.StartRow = 2;

area.EndRow = 500;

area.StartColumn = i;

area.EndColumn = i;

// Set the formula1.

if (strValidation.length() < 255)

validation.setFormula1(strValidation);

else validation.setFormula1(strInternalValidation);

// Add the Validation area.

validation.addArea(area);

Note : strValidation and strInternalValidation are just strings that have the folowing format value1;value2;value3;value4

Thanks for help

6

Hi,

I have tested your code with the latest version:
Aspose.Cells
for Java v7.2.1.8
after making some modifications to make it runnable.

I was able to replicate this issue. I have attached the output xls and xlsx files and screenshot for your reference.

We will look into your issue and fix the bug and update you asap.

Please see the code below and the screenshot.

Java


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.getWorksheets().get(0);


ValidationCollection validations = worksheet.getValidations();


int index = validations.add();


Validation validation = validations.get(index);


// Set the validation type.


validation.setType(ValidationType.LIST);


// Set the in cell drop down.


validation.setIgnoreBlank(true);


validation.setInCellDropDown(true);


validation.setShowError(true);


validation.setErrorTitle(“Error”);


validation.setErrorMessage(“You must select one of the value available in the drop-down list box”);


validation.setShowInput(true);


validation.setInputMessage(“”);


// Specify the validation area of cells.


CellArea area = new CellArea();


area.StartRow = 2;


area.EndRow = 500;


area.StartColumn = 0;


area.EndColumn = 1;


String strValidation=“1;2;3;4”;

String strInternalValidation=“5;6;7;8”;


// Set the formula1.


if (strValidation.length() < 255)


validation.setFormula1(strValidation);


else validation.setFormula1(strInternalValidation);


// Add the Validation area.


validation.addArea(area);



workbook.save(“F:\Shak-Data-RW\Downloads\Output.xlsx”);


Screenshot:

7

the code is correct,

You can see the expected output file (See attachement) .

Click on cells A3/A4/A5/etc...

8

Hi,

Thanks for your help.

We have logged your issue in our database with the id: CELLSJAVA-40211

Once, there is some update, we will let you know asap.

9

Hi,


Please use “,” instead of “;” while separating the list values. You need to change the lines of code to:
e.g.
String strValidation=“value1,value2,value3,value4”;
String strInternalValidation=“value5,value6,value7,value8”;

I have tested the code (Shared by Shakeel Faiz) using the above lines and it works fine as per your desired output.

Thank you.
10

Yes amjad,

ok try to save the file with .xls extention(case of "," is the separator) you will found the same problem with ";" on xlsx file extension, this is in one hand,

in the other hand the finally user can choose any separator type(Control Panel/Regions and Languages/Additional setting/list separator), So you can not to force him to choose a given type of seperators.

Anyway, we are waiting for the resolution of that bug.

Thank you for the interest.

11

Hi,


Well, it works fine with “,” using both XLS and XLSX save file formats. Aspose.Cells works with US English Settings for MS Excel by default. If I open your desired Output file in MS Excel 2007, you may see that the separator used was “,” (see the attached screen shot). What is your regional and language settings on your end.

As Shakeel Faiz already logged a ticket for investigation, so we will further look into it. Your additional information for env. and other settings would help us too.

Thank you.
12

Thank you so much

13

Hi,

1 - We can only support ‘,’ as separator when user set formula for list.

2 - We did find the issue that list validation in aspose created xls file does not work when change system list separator to others than ‘,’. We will provide a fix for this issue soon.

14

The issues you have found earlier (filed as CELLSJAVA-40211) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.