Creating Drop Downs in Excel

Aspose.Cells Product Family
1

Hi Team,

I want to generate an excel sheet which will have 2 drop downs but the values for both drop downs would be dynamic.

also there will be other columns in the excel which too are dynamic, there count will vary.

Please help me how to do this in aspose

Thanks.

2

@siddhanntarora1992,

There are different ways to create combo box control. You may consider following sample code where column F is targeted as combo box source data. You may append/delete data in F column and combo box shall pick data dynamically from this column. For this purpose Range is created based upon maximum rows in the column F. Using this example you may create your desired output file.

If this sample does not fulfill your requirement, please prepare your required output workbook using Excel and share with us along with the sample code (if any). We will try to provide you assistance to create similar output using Aspose.Cells.

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

// Get the first worksheet.
Worksheet sheet = workbook.Worksheets[0];

// Get the worksheet cells collection.
Cells cells = sheet.Cells;

// Input a value.
cells["G3"].PutValue("Student:");

// Set it bold.
cells["G3"].GetStyle().Font.IsBold = true;

// Input some values that denote the input range
// For the combo box.
cells["F2"].PutValue("Std001");
cells["F3"].PutValue("Std002");
cells["F4"].PutValue("Std003");
cells["F5"].PutValue("Std004");
cells["F6"].PutValue("Std005");
cells["F7"].PutValue("Std006");
cells["F8"].PutValue("Std007");

// Add a new combo box.
Aspose.Cells.Drawing.ComboBox comboBox = sheet.Shapes.AddComboBox(2, 0, 7, 0, 22, 100);
// ExEnd:1
            
// Set the linked cell;
comboBox.LinkedCell = "F1";
Aspose.Cells.Range range = cells.CreateRange("F2", CellsHelper.CellIndexToName(cells.GetLastDataRow(5), 5));
range.Name = "Students";
// Set the input range.
//comboBox.InputRange = "F2:F7";
comboBox.InputRange = "=Students";


// Set no. of list lines displayed in the combo
// Box's list portion.
comboBox.DropDownLines = 5;

// Set the combo box with 3-D shading.
comboBox.Shadow = true;

// AutoFit Columns
sheet.AutoFitColumns();

// Saves the file.
workbook.Save(@"book1.out.xls");