I would like to add the multi select option in excel using the aspose.cells

Aspose.Cells Product Family
1

Hi There,
I am exploring the multi select option like the drop down where user can select the multiple option and the selected options should get copied in the linked cell separated by some character. After exploring the aspose.cells I found the ListBox that can be used.
In the POC I found the following issues with it.

  1. Not getting the scroll bar, if items are increasing then we have to increase the height.
  2. Selected values are not getting pushed to linked cells.
  3. No option to apply the entire column or range of cells.
  4. we want the list come up while clicking on cell like the dropdown, unable to add it like that.

My code is as follows. Could you please help me to achive the above, if other better option is available in aspose.cells then please suggest the sam.

Thanks in Advance,
Uzair

           Cells MSCells = worksheet2.Cells;
            MSCells["DD2"].PutValue("Sales");
            MSCells["DD3"].PutValue("Finance");
            MSCells["DD4"].PutValue("MIS");
            MSCells["DD5"].PutValue("R&D");
            MSCells["DD6"].PutValue("Marketing");
            MSCells["DD7"].PutValue("HRA");

            // Add a new list box.
            Aspose.Cells.Drawing.ListBox listBox = sheet.Shapes.AddListBox(20, 0, 1, 0, 50, 100);

            // Set the placement type.
            listBox.Placement = PlacementType.FreeFloating;

            // Set the linked cell.
            listBox.LinkedCell = "B21";

            // Set the input range.
            listBox.InputRange = "Dropdowns!DD2:DD7";

            // Set the selection tyle.
            listBox.SelectionType = SelectionType.Multi;
            listBox.TextHorizontalOverflow = TextOverflowType.Overflow;
            listBox.TextVerticalOverflow = TextOverflowType.Overflow;

            // Set the list box with 3-D shading.
            listBox.Shadow = true;


            int count = listBox.ItemCount;

            // Assert.AreEqual(count, 4);

            listBox.PageChange = count;

            listBox.Height = count * 15;
2

@uzairpune,
Thank you for your query.

Could you please provide us a sample Excel file (as reference) created by MS Excel which contains all the required features which you want to create using Aspose.Cells. We will use this template Excel file as reference and provide assistance to perform the same tasks using Aspose.Cells.

3

@ahsaniqbalsidiqui,
Thanks for your reply.

I think excel does not provide the multi select, but by doing some VBA macro code we can achieve it.

As we arealready using the aspose.cells do you have any option for multiple selection. OR any other way we can achieve the multiple value selection.

We want our customer should select the master values from the list or some kind of UI where they can select multiple values.

Thanks
Uzair

4

For example,

in an excel cell, when user click, a drop down should appear with the Sales and Finance.
Then user should be able to select either Sales or Sales,Finance both. After selection the sales or sales,finance should come in the selected cell.

5

@uzairpune,
Aspose.Cells mimics the behaviour of MS Excel and if some task cannot be performed using it, Aspose.Cells may also not support it. I am afraid that until you do not share some expected Excel file which is created using MS Excel, we cannot provide you further assistance. Please take your time and share an Excel file which performs your required tasks.

Regarding VBA macros Aspose.Cells provides option to add/modify macros but you have to write your own logic in it to achieve the required functionality. Please have a look at the following document section for information about VBA:

Workbook VBA Project

6

@ahsaniqbalsidiqui,

I have created the .xlsm file with the help of following link. unfortunately I can not upload the file as the .xlsm extension is not allowed to upload here. if you still want it share your email I will send it to you.

Considering the security, we can not use the VBA code in our case. Hence I want to get it some how without the VBA code.

Then I saw the ListBox and listboxactivexcontrol in aspose.cells.
I am able to list down the items in these controls but the selected values are not coming in the linked cell.

Also it is visible all time.

Do we have any such control in aspose.cells that might not be in excel.

7

@uzairpune,
You may zip the template file and upload it here for our testing. If file is large, you may upload it to some public file sharing server and share the download link (along with password if required for unzip) here. Please note that your template file is must required which you want to create using Aspose.Cells.

I am afraid that there is no such control available in Aspose.Cells which is not there in MS Excel.

8

MultiselectUsingVBACode1.zip (15.3 KB)

@ahsaniqbalsidiqui,
Please check the last column of the attached file. it is done through the VBA code. As I mentioned earlier we can not use the VBA code in our case.

is it possible using aspose.cells in excel file without VBA code?

Thanks
uzair

9

@uzairpune,
Could you please try below code snippet and let us know your feedback.

Workbook workbook = new Workbook();
Worksheet worksheet1 = workbook.Worksheets[0];
Range range = worksheet1.Cells.CreateRange("H1", "H4");
range.Name = "MyRange";
range[0, 0].PutValue("USA");
range[1, 0].PutValue("UK");
range[2, 0].PutValue("France");
range[3, 0].PutValue("China");
ValidationCollection validations = worksheet1.Validations;
Validation validation = validations[validations.Add()];
validation.Type = ValidationType.List;
validation.Operator = OperatorType.None;
validation.InCellDropDown = true;
validation.Formula1 = "=MyRange";
validation.ShowError = true;
validation.AlertStyle = Aspose.Cells.ValidationAlertType.Stop;
validation.ErrorTitle = "Error";
validation.ErrorMessage = "Please select a valid country from the list";
Aspose.Cells.CellArea area;
area.StartRow = 0;
area.EndRow = 3;
area.StartColumn = 0;
area.EndColumn = 0;
validation.AddArea(area);
workbook.Save("D:\Dropdown.xls");
10

@ahsaniqbalsidiqui,

Thanks for your reply.
I want the multi select list, the code you have sent is for the single selection, which I have implemented.
The file I have sent you we can do the multiple selections like the highlighted in below image.

image.png (3.9 KB)

Let us know if we can achieve it by tweaking the code you have provided OR If any other classes available in aspose for the same.

Thanks again for your follow up and helping us.

11

@uzairpune,
As it is only possible through VBA in MS Excel, same is the limitation with the Aspose.Cells library. Please note that Aspose.Cells mimics the behaviour of MS Excel and if a feature is not available in MS Excel, it is not possible to perform the same using Aspose.Cells as well. I am afraid that no such control is available in Aspose.Cells which can be used to achieve this functionality.

Please feel free to write us back if you have any other query in this regard.