Display actual value instead of selected index in list box aspose cells

[saurabh.arora]

Hi,

I was trying to insert list box into an excel sheet. When i select the value in the list , it shows me the value of selected index instead of value. I want the selected value to be displayed.

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

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

//Get the worksheet cells collection.
Cells cells = sheet.getCells();

//Input a value.
cells.get(“B3”).setValue(“Choose Dept:”);

Style style = cells.get(“B3”).getStyle();
style.getFont().setBold(true);
//Set it bold.
cells.get(“B3”).setStyle(style);

//Input some values that denote the input range for the combo box.
cells.get(“A2”).setValue(“Sales”);
cells.get(“A3”).setValue(“Finance”);
cells.get(“A4”).setValue(“MIS”);
cells.get(“A5”).setValue(“R&D”);
cells.get(“A6”).setValue(“Marketing”);
cells.get(“A7”).setValue(“HRA”);

//Add a new list box.
com.aspose.cells.ListBox listBox = (com.aspose.cells.ListBox)sheet.getShapes().addShape(MsoDrawingType.LIST_BOX,3, 3, 1,0, 100, 122);

//Set the linked cell;
listBox.setLinkedCell(“A1”);

//Set the input range.
listBox.setInputRange("=A2:A7");

//Set the Placement Type, the way the list box is attached to the cells.
listBox.setPlacement(PlacementType.FREE_FLOATING);

//Set the list box with 3-D shading.
listBox.setShadow(true);

//Set the selection type.
listBox.setSelectionType(SelectionType.SINGLE);

//AutoFit Columns
sheet.autoFitColumns();

//Saves the file.
workbook.save(“E:\Excels\tstlistbox.xls”);

Also , is there any style to distinguish between multi select list and single select list. Like , when i open the sheet , i cannot tell whether the list is single select or multi select.

[amjad.sahi]

@saurabh.arora,

Thanks for the sample code and details.

When i select the value in the list , it shows me the value of selected index instead of value. I want the selected value to be displayed.

Well, this is how MS Excel behaves for linked cells. You may try to use some Vba codes or macros to display the selected item instead of value.

Using Aspose.Cells, you may get all the list box items at runtime, see the following sample code segment for your reference:
e.g
Sample code:

		//Get the source input range for the ListBox.
		Range range = cells.createRange(listBox.getInputRange());
        //Iterate through the range cells and get its values.			
		Iterator rangeIterator = range.iterator();
		while(rangeIterator.hasNext())
		{
		Cell cell = (Cell)rangeIterator.next();
		System.out.println(cell.getValue());
		}

Also , is there any style to distinguish between multi select list and single select list. Like , when i open the sheet , i cannot tell whether the list is single select or multi select

Well, there is no style difference visually, you got to check its selection type, see the sample line of code for your reference:
e.g
Sample code:

		//Get the selection type constant.
		System.out.println(listBox.getSelectionType());

Hope, this helps a bit.

Thank you.

[saurabh.arora]

Thanks for the clarification.

“Well, this is now MS Excel behaves for linked cells. You may try to use some Vba codes or macros to display the selected item instead of value.” – Is there any plan to bring this functionality any time soon in Aspose.

“Well, there is no style difference visually, you got to check its selection type” – Can you suggest something how to make single select and multi select visually different for the user in the sheet.

Thanks

[amjad.sahi]

@saurabh.arora,

Is there any plan to bring this functionality any time soon in Aspose.

I do not think there is any such feature in MS Excel to display list box item instead of index for a list box control’s linked cell. If you know how to achieve it in MS Excel, kindly give us details and sample file, we will check it on how to do it via Aspose.Cells APIs.

Can you suggest something how to make single select and multi select visually different for the user in the sheet.

Well, you may make your own rule for differentiation. For example, for a multi-select list box, you may apply 3d shading style. For single select, you should not apply this style for difference. See the sample line of code:
e.g
Sample code:

//Set the list box with 3-D shading.
listBox.setShadow(true);

Thank you.

[saurabh.arora]

Thanks for the answer.

Can I show activex controls. I could not see proper documentation for this. Is this supported?

[amjad.sahi]

@saurabh.arora,

Yes, I think ActiveXControls can suite your needs as the linked cell shows selected item instead of index. See the following sample code on how to add ComboBox ActiveX control (you may easily add other controls like ListBox ActiveX Control by changing its control type):
e.g
Sample code:

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

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

		//Get the worksheet cells collection.
		Cells cells = sheet.getCells();

		//Input a value.
		cells.get("B3").setValue("Choose Dept:");

		Style style = cells.get("B3").getStyle();
		style.getFont().setBold(true);
		//Set it bold.
		cells.get("B3").setStyle(style);

		//Input some values that denote the input range for the combo box.
		cells.get("A2").setValue("Sales");
		cells.get("A3").setValue("Finance");
		cells.get("A4").setValue("MIS");
		cells.get("A5").setValue("R&D");
		cells.get("A6").setValue("Marketing");
		cells.get("A7").setValue("HRA");

		//Add and access combo box, change its fill range and value property
        Shape shape = sheet.getShapes().addActiveXControl(ControlType.COMBO_BOX, 5, 0, 5, 0, 100, 20);
        com.aspose.cells.ComboBoxActiveXControl combo = (com.aspose.cells.ComboBoxActiveXControl)shape.getActiveXControl();
        combo.setListFillRange("A3:A7");
        combo.setValue("Apple");
        combo.setLinkedCell("A1");
        
        //Save the output Excel file
        workbook.save("f:\\files\\out1.xlsx");

Also, see the document on how to update ActiveX controls in the sheet for your reference:

Update ActiveX ComboBox Control

Hope, this helps a bit.

Thank you.

[saurabh.arora]

Thanks. Actually I got that sorted but ran into another problem. The list is not getting resized when i change the column width. Can you please help me with that.

Shape shape = dataSheet.getShapes().addActiveXControl(ControlType.LIST_BOX, 4, 0, columnToInsertList, 0, dataCells.getColumnWidthPixel(columnToInsertList), dataCells.getRowHeightPixel(4) * 5);

shape.setPlacement(PlacementType.MOVE_AND_SIZE);
(Not working , was working with normal ListBox thing)

Also , still not clear with the style of list box.

ListBoxActiveXControl activeXControl = (ListBoxActiveXControl) shape.getActiveXControl();

activeXControl.setListStyle(1);

(what is the all available option that this method can take)

Your help is really appreciated.

[amjad.sahi]

@saurabh.arora,

Shape shape = dataSheet.getShapes().addActiveXControl(ControlType.LIST_BOX, 4, 0, columnToInsertList, 0, dataCells.getColumnWidthPixel(columnToInsertList), dataCells.getRowHeightPixel(4) * 5);
shape.setPlacement(PlacementType.MOVE_AND_SIZE);

(Not working , was working with normal ListBox thing)

Well, Placement attribute only works for Form controls/shapes. It won’t work for ActiveXControl as the property is not available under ListBoxActiveXControl or ComboBoxActiveXControl.

Also , still not clear with the style of list box.
ListBoxActiveXControl activeXControl = (ListBoxActiveXControl) shape.getActiveXControl();
activeXControl.setListStyle(1);
(what is the all available option that this method can take)

Well, there are two styles for the control:

• Plain - Displays a list in which the background of an item is highlighted when it is selected - 0
• Option - Displays a list in which an option button or a checkbox (if you set the control as multi-select list) next to each entry displays the selection state of that item - 1

For your needs to differentiate if the list box control is multi-select, you may make use of list style as “Option” with selection type as “MULTI”. See the following code snippet for your reference:
e.g
Sample code:

..............
Shape shape = sheet.getShapes().addActiveXControl(ControlType.LIST_BOX, 5, 0, 5, 0, 100, 200);
        com.aspose.cells.ListBoxActiveXControl list = (com.aspose.cells.ListBoxActiveXControl)shape.getActiveXControl();
        list.setListFillRange("A3:A7");
        list.setValue("Apple");
        list.setLinkedCell("A1");
        list.setListStyle(ControlListStyle.OPTION);
        list.setSelectionType(SelectionType.MULTI);
............. 

Hope, this helps a bit.

Thank you.

[saurabh.arora]

Hi,

I was able to solve all my problems but ran into one issue. When i am setting the name of my list or shape , it does not pick up the value I am giving it. It picks the name as “ListBox$Number” where Number is the number of list. I want it to pick the name of column number where i am inserting the list. For example “ListBox$InsertionColumnNumber”.

activeXControl.setValue(“ListBox” + columnToInsert);

shape.setName(“ListBox” + columnToInsert);

Tried these api’s but does not work.

Thanks

[amjad.sahi]

@saurabh.arora,

Thanks for the details.

After an initial test, I am not able to set or change the name of ListBox ActiveX Control, it looks like the feature is not available. I tried Shape.setName but it does not work.
e.g
Sample code:

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

String columnNumber = "1"; 

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

//Get the worksheet cells collection. 
Cells cells = sheet.getCells(); 

//Input a value. 
cells.get("B3").setValue("Choose Dept:"); 

Style style = cells.get("B3").getStyle(); 
style.getFont().setBold(true); 
//Set it bold. 
cells.get("B3").setStyle(style); 

//Input some values that denote the input range for the combo box. 
cells.get("A2").setValue("Sales"); 
cells.get("A3").setValue("Finance"); 
cells.get("A4").setValue("MIS"); 
cells.get("A5").setValue("R&D"); 
cells.get("A6").setValue("Marketing"); 
cells.get("A7").setValue("HRA"); 

//Access combo box, change its fill range and value property 
        Shape shape = sheet.getShapes().addActiveXControl(ControlType.LIST_BOX, 5, 0, 5, 0, 100, 200); 
        shape.setName("MyList" + columnNumber); 
        com.aspose.cells.ListBoxActiveXControl list = (com.aspose.cells.ListBoxActiveXControl)shape.getActiveXControl(); 
        list.setListFillRange("A3:A7"); 
        list.setValue("Apple"); 
        list.setLinkedCell("A1"); 
        list.setListStyle(ControlListStyle.OPTION); 
        list.setSelectionType(SelectionType.MULTI); 
         
        //Save the output Excel file 
        workbook.save("f:\\files\\out1.xlsx"); 

I have logged a ticket with an id “CELLSJAVA-42368” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

Thank you.

[shakeel.faiz]

@saurabh.arora

Thanks for using Aspose APIs.

This is to inform you that we have fixed your issue CELLSJAVA-42368 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

[shakeel.faiz]

@saurabh.arora

Please download and try the following fix and let us know your feedback.

• Aspose.Cells for Java (Latest Version)

[amjad.sahi]

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

---

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.