How to get all dropdown's (listBox) values from a cell

I have a pre-populated excel file. Some cells there are dropdowns with values. I want using aspose for Java get all values from these dropdowns.
Seems ListBox - is what I need to work with, but I only found examples how to create listBox (http://www.aspose.com/api/java/cells/com.aspose.cells/classes/ListBox), but not how to initialize existing one.

I tried to assignt cell to listBox as following:

ListBox listBox = getCellsCollection().get(rowNum - offset, getColumnIndex(uniqueColumnName);

But got an error:

> Type mismatch: cannot convert from Cell to ListBox


Initializing ListBox worked this way:

Worksheet sheet = book.getWorksheets().get(0);
ListBox listBox = (ListBox) sheet.getShapes().addShape(MsoDrawingType.LIST_BOX,7, 0, 1,0, 1, 1);
listBox.setPlacement(PlacementType.FREE_FLOATING);
listBox.setLinkedCell("D8");
But non of the getAlternativeText(), getSelectedCells(), getCharacters() give me what I need (all return null).
P.S. I dont really understand the values 7, 0, 1, 0, 1, 1 in adding a shape, but that should mean 'D8' cell in my understanding.

When I try to get values from a cell - I only get displayed one and not the full list (or I don't know how to get it right).

How using aspose for Java I can get all values from existing dropdown in excel file?

Thanks in advance.

Hi,

Thanks for your posting and considering Aspose.Cells.

Please provide us your sample excel file having your dropdown or list containing values for our reference. You can create it manually with Microsoft Excel and attach it here. We will use it for our investigation and provide you a sample code to achieve your requirements programmatically using Aspose.Cells APIs. Thanks for your cooperation and have a good day.

Hello,

Thank you for helping.


I can’t share the real file I use, but the attached example should work. The idea: I have a dropdown (listBox) in cell ‘A2’ with values ‘Male’, ‘Female’, ‘Unknown’. Working with cell I can only get the displayed value (one was selected). But I need all of them. Also might be a tricky that source cells are hiden and I don’t know where they are (that’s made to hide them from the end user).

So in the end I need ideally a list [‘Male’, ‘Female’, ‘Unknown’], or a string 'Male, Female, Unknown’

Please let me know if you need any other details.
Thanks in advance.

Hi,

Thanks for your nice description and clarifying the issue with simple excel file and using Aspose.Cells.

Please see the following sample code that achieves your requirements. I have also shown the console output of this code for your reference

I have tested the code with the latest version: Aspose.Cells for Java (Latest Version).

Java
String filePath = “F:\Shak-Data-RW\Downloads\Example.xlsx”;

Workbook workbook = new Workbook(filePath);

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

Cell cell = worksheet.getCells().get(“A2”);

Validation v = cell.getValidation();

Object obj = v.getValue1();

if(obj!=null && obj instanceof Object[])
{
Object[] o = (Object[]) obj;

for (int i = 0; i < o.length; i++)
{
System.out.println(o[i]);
}
}

Console Output:
Unknown
Female
Male

1 Like

Hello,

Thank you so much, that definitely should solve my problem.

The only wierd thing - when I copied your code and imported missing classes I got an error 'The method getValidation() is undefined for the type Cell ' on the line:

Validation v = cell.getValidation();

I've attached the screen with all available getters for Cell i can see.

Sorry for stupid questions, but I don't know why getValidation isn't available for me.

Thanks in advance.

Hi,

Thanks for your feedback and using Aspose.Cells.

I am afraid, this method became available in v8.2.1 and later. Please see the following documentation relating to Public API Changes for your reference.

However, in older versions you can access all your Validations using the Worksheet.getValidations() collection.

I’ve updated version in dependency - everything works.
Thank you one more time!

Hi,


Good to know that your issue is sorted out by upgrading to latest version(s) of the product. 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.

Thank you.

How can we do this in C#? I am trying to do the same, but can’t get .validation


Hi,

Thanks for your posting and considering Aspose.Cells.

Please download and use the latest version: Aspose.Cells for .NET (Latest Version) it should fix your issue.

Here is the equivalent sample code in C# as shown in this post. Please also download the Example.xlsx file used inside the code. I have also shown the console output of the code for your reference.

C#

String filePath = “Example.xlsx”;

Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];

Cell cell = worksheet.Cells[“A2”];

Validation v = cell.GetValidation();

Object obj = v.Value1;

if (obj != null && obj is Object[])

{

Object[] o = (Object[])obj;

for (int i = 0; i < o.Length; i++)

{

Console.WriteLine(o[i]);

}

}

Console Ouput

Male

Female

Unknown

Thank you! It works, this lib is great!

@romazzz,

You are welcome.