I am working on Aspose Cells…I need to Bind a Dropdown List to a Column in Excel.can u please suggest me
Hi Aravind,
You may import the data from your desired column(s) (e.g Country) from the DataTable of your source using Cells.ImportDataTable() method to fill a column in the worksheet. Now add dropdown validation to the column (e.g A) based on the values of the range.
Please use the sample code below (We have extracted Country's distinct values of Northwind Access Database).
Sample code:
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];
OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=e:\\test\\Northwind.mdb");
//Open the connection object.
con.Open();
//Create a command object and specify the SQL query.
OleDbCommand cmd = new OleDbCommand("Select distinct(Country) from Employees", con);
//Create a data adapter object.
OleDbDataAdapter da = new OleDbDataAdapter();
//Specify the command.
da.SelectCommand = cmd;
//Create a dataset object.
DataSet ds = new DataSet();
//Fill the dataset with the table records.
da.Fill(ds, "Employees");
//Create a datatable with respect to dataset table.
DataTable dt = ds.Tables["Employees"];
worksheet2.Cells.ImportDataTable(dt, false, "A1");
// Create a range in the second worksheet.
int rowindex = worksheet2.Cells.MaxDataRow;
string cellname = CellsHelper.CellIndexToName(rowindex, 0);
Range range = worksheet2.Cells.CreateRange("A1", cellname);
// Name the range.
range.Name = "MyRange";
// Get the validations collection.
ValidationCollection validations = worksheet1.Validations;
// Create a new validation to the validations list.
Validation validation = validations[validations.Add()];
// 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 = "Invalid Country Error";
// Set the error message.
validation.ErrorMessage = "Please select a country from the drop down";
// Specify the validation area.
CellArea area;
area.StartRow = 0;
area.EndRow = 14;
area.StartColumn = 0;
area.EndColumn = 0;
// Add the validation area.
validation.AreaList.Add(area);
// Save the excel file.
workbook.Save("e:\\test\\output.xls");
Alternatively, you can try to add combo box control instead of using List Data Validation. For reference how to add Combo Box control, see the topic:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/working-with-controls.html
If you face any difficulty, please feel free to contact us.
Thanks,