How Do You Create a Drop List Of Values during run-time?

Aspose.Cells Product Family
1

My template excel sheet has a named range of cells used as the values for a drop down list on another worksheet. When I copy my template to my new file, the drop-downs are gone. So, how can these be created during runtime? There does not seem to be a way to assign a named cell range as the source for a validation drop list…

Mike

2

Hi Mike,

Do you use Excel.Copy method to copy the template and the drop-downs are gone? I fixed this bug. Please download hotfix at

The following is the sample code to create validation drop list:

Excel excel = new Excel();
excel.Worksheets.Add();
Worksheet sheet = excel.Worksheets[1];
Range range = sheet.Cells.CreateRange(2, 5, 4, 1);
range.Name = “MyRange”;

Validations validations = excel.Worksheets[0].Validations;
Aspose.Excel.Validation validation = validations[validations.Add()];
validation.Type = Aspose.Excel.ValidationType.List;
validation.Formula1 = “=MyRange”;

CellArea area;
area.StartRow = 0;
area.EndRow = 1;
area.StartColumn = 0;
area.EndColumn = 1;

validation.AreaList.Add(area);

3

Laurence, Thanks for the speedy reply. I will give that a try. I used an object copy from Excel Template to New Excel File in VB. Here is the code. When I tried to use the Copy method on the worksheet level it copied blank worksheets. With this method it seems to work for everything except the drop list being linked to the cells they need to be used in.

Dim SS As New Aspose.Excel.Excel
Dim SSNew As New Aspose.Excel.Excel

Try
   SS.Open(path & “\template.xls”)
   SSNew = SS
Catch ex As Exception
End Try

















4

@mstandal,
Aspose.Cells has replaced Aspose.Excel which is discarded and no more under active development now. This new product has all the features of its predecessor as well as support for the latest features in different versions of MS Excel.

Using Aspose.Cells, drop-down list can be created at runtime as shown in the following sample code.

// 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];

// Create a range in the second worksheet.
Range range = worksheet2.Cells.CreateRange("E1", "E10");

// Name the range.
range.Name = "MyRange";

// Fill different cells with data in the range.
range[0, 0].PutValue("Blue");
range[1, 0].PutValue("Red");
range[2, 0].PutValue("Green");
range[3, 0].PutValue("Yellow");

// Get the validations collection.
ValidationCollection validations = worksheet1.Validations;

// Create Cell Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
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;

// Set the formula1.
validation.Formula1 = "=MyRange";

// 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.
CellArea area;
area.StartRow = 0;
area.EndRow = 4;
area.StartColumn = 0;
area.EndColumn = 0;

// Add the validation area.
validation.AddArea(area);

// Save the Excel file.
workbook.Save("output.out.xls");

Here is a link to more information about data validation:
Data Validation

Here is a link to the free trial version of this new product:
Aspose.Cells for .NET(Latest version)

Here is a runnable complete solution that contains a lot of examples to test different features of Aspose.Cells.