Hi there,
How to create a drop down with items is 1,2,3,4,5
and suppose i have already have data 3, then the dropdown in the cell should reflect this 3
regards
You may either create List data validation or create ListBox/combobox control using Aspose.Cells APIs. You can either create list data validation or use Aspose.Cells APIs to create a ListBox/ComboBox control. Refer to the examples in the relevant documents for guidance, and then create the desired dropdowns according to your needs.
If you encounter any issues while completing the task, please manually create your desired dropdown in MS Excel, save the file, zip it, and provide the Excel file for reference. We will evaluate it and assist you in achieving the same result using Aspose.Cells APIs.
Thank u Sir,
It works, but i am still wondering:
- what are the functions of these areas and what are the differences between them? I change the value, but nothing change.
// Create Cell Area
CellArea ca = new CellArea();
ca.StartRow = 1; //change
ca.EndRow = 3; // change
ca.StartColumn = 0;
ca.EndColumn = 0;
// Specify the validation area.
CellArea area;
area.StartRow = 0;
area.EndRow = 4;
area.StartColumn = 0;
area.EndColumn = 0;
- why the drop down appears only in the A2? How to set to other cell?
- how to programmatically populate the data without using range?
- Suppose i have a value “Green” how to set this data to reflect in the dropdown, so not the “yellow” one is shown
regards
@ibox
A validation can add multiple validation areas. When we create the validation, we specify a validation area, which can be added multiple times later. Of course, you can also change the data and reset the corresponding properties after adding it. Validation will use new data. Please check the attachment.
validation.zip (15.3 KB)
In the file “out_net1. xlsx”, “Sheet1” worksheet has added two validation areas, namely “A1:A5” and “C1:C5”. In the file out_2et2.xlsx, we have added a new validation data “pink” and added a area of “E1:E5”. So in the “Sheet1” worksheet, there are three validation areas, they are “A1:A5”, “C1:C5” and “E1:E5”.
The sample code as follows:
// Create a workbook object.
Workbook workbook = new Workbook();
// Get the first worksheet.
Worksheet worksheet1 = workbook.Worksheets[0];
// Add a new worksheet and access it.
int i = workbook.Worksheets.Add();
Worksheet worksheet2 = workbook.Worksheets[i];
Cells cells = worksheet2.Cells;
// Fill different cells with data in the range.
cells[0, 0].PutValue("Blue");
cells[1, 0].PutValue("Red");
cells[2, 0].PutValue("Green");
cells[3, 0].PutValue("Yellow");
// Get the validations collection.
ValidationCollection validations = worksheet1.Validations;
// Create Cell Area(A1:A5) for validation
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 4;
ca.StartColumn = 0;
ca.EndColumn = 0;
// Create a new validation to the validations list.
Validation validation = validations[validations.Add(ca)];
// Set the validation type.
validation.Type = Aspose.Cells.ValidationType.List;
// Set the operator.
validation.Operator = OperatorType.None;
// Set the in cell drop down.
validation.InCellDropDown = true;
StringBuilder builder = new StringBuilder();
builder.Append("=");
builder.Append(worksheet2.Name);
builder.Append("!$A$1:$A$4");
// Set the formula1.
validation.Formula1 = builder.ToString();
// Enable it to show error.
validation.ShowError = true;
// Set the alert type severity level.
validation.AlertStyle = ValidationAlertType.Stop;
// Set the error title.
validation.ErrorTitle = "Error";
// Set the error message.
validation.ErrorMessage = "Please select a color from the list";
// Specify the validation area(C1:C5) for adding other validation areas
CellArea area;
area.StartRow = 0;
area.EndRow = 4;
area.StartColumn = 2;
area.EndColumn = 2;
// Add the validation area.
validation.AddArea(area);
// Save the Excel file.
workbook.Save(filePath + "out_net1.xlsx");
//add new data for validation
cells[4, 0].PutValue("pink");
builder = new StringBuilder();
builder.Append("=");
builder.Append(worksheet2.Name);
builder.Append("!$A$1:$A$5");
validation.Formula1 = builder.ToString();
// Specify the validation area(C1:C5) for adding other validation areas
CellArea area2;
area2.StartRow = 0;
area2.EndRow = 4;
area2.StartColumn = 4;
area2.EndColumn = 4;
// Add the validation area.
validation.AddArea(area2);
workbook.Save(filePath + "out_net2.xlsx");
Hope helps a bit.
Thank u, Sir
It is very helpful. I am still wondering this syntax:
CellArea area2;
why not CellArea area2 = new CellArea()
like the first one:
CellArea ca = new CellArea();
and also for this value:
//add new data for validation
cells[4, 0].PutValue(“pink”);
builder = new StringBuilder();
builder.Append(“=”);
builder.Append(worksheet2.Name);
builder.Append(“!$A$1:$A$5”);
validation.Formula1 = builder.ToString();
how if i do not want to use range, but put value “blue”, “pink”, etc in the stringBuilder, is it possible?
regards
I guess you need to use constant values (like “blue”, “pink”, etc.) for the source (formula) for List data validation, instead of using cells (range) values, is not it? If so, kindly see the following sample code segment for your reference.
e.g.
Sample code:
string[] colors = new string[3];
colors[0] = "blue";
colors[1] = "pink";
colors[2] = "red";
....
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 4;
ca.StartColumn = 0;
ca.EndColumn = 0;
var flatList = string.Join(",", colors.ToArray());
// Create a new validation to the validations list.
Validation validation = validations[validations.Add(ca)];
validation.Type = Aspose.Cells.ValidationType.List;
// Set the in cell drop down.
validation.InCellDropDown = true;
validation.Formula1 = flatList;
........@ibox
1,The CellArea is a struct, so it could be "CellArea area2; " in C# project, but it’s better that use CellArea area2 = new CellArea().
2,
Please try validation.Formula1 = “blue, pink, red”;
Thank u, Sir
regards
Thank u, Sir
it works
But when i choose one item, it is written in the right side. I use numeric 1,2,3,4,5 for dropdown items.
How to make it written in the left side?
dropdownstring.png (3.2 KB)
regards
@ibox
This is the expected behavior. Aspose.Cells follows the specifications and rules of MS Excel. If you still have any questions, Please manually set the desired results in MS Excel. If you can complete this operation, please provide us with a sample file, and we will check it soon.
@ibox
Numberic value will defaultly align right in MS Excel.
If you want to align the numberic value left, please try the following solutions:
1, Format the cell as Text
style.Number = 49;
// style.Custom = “@”;
cell.SetStyle(style)
2, Align left
style.HorizontalAlignment = TextAlignmentType.Left;
Thank You, Sir
regards
You are welcome. If you have any further queries or comments, please feel free to write back to us.