I need to show the list of countries in sheet1 of 2rd column in all the cells using combo box. The user can select the country in combo box. The list of countries is stored in our database. So i have a list of countries in Datatable, i need to import the in sheet2 and view the list in sheet1 for all cells in 2rd column. I need the coding for that. Please provide your suggestion to solve this problem.
Well, I think you may use List(DropDown) validation for your scenario. Simply 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 second worksheet. Now add drop down validation to the first worksheet column (e.g A) based on the values of the second worksheet range.
I have written a sample code for you here (I have extracted Country 's distinct values of Northwind Access Database).
Workbook workbook = new Workbook();
// Get the first worksheet.
Worksheet worksheet1 = workbook.Worksheets;
// 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.
//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.
//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.
area.StartRow = 0;
area.EndRow = 14;
area.StartColumn = 0;
area.EndColumn = 0;
// Add the validation area.
// Save the excel file.
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:
Thanks for your reply. I tried your code, but it show the error message at the time of bind the application. The error message are “The type or namespace name ‘ValidationCollection’ could not be found (are you missing a using directive or an assembly reference?)” . Please provide your suggestion for that. I am waiting for your reply.
If you are using some older version then you should use in the code: Validations instead ValidationCollection.
In the recent version, we have renamed some classes and also added a few namespaces to re-organize classes. See the document for your reference (check the “Notable changes for the users - sub topic at bottom”:
For complete reference, please see the Aspose.Cells API Reference.
Thanks for your reply. I have completed the task with your help. But I face one more problem, after I loaded the Sheet2 data's to Sheet1 Drop down list and I try to open the excel sheet, is showing default on "Evaluation Sheet" not Sheet1. How I can set the Cursor to sheet1 of Column 1.
Please provide your suggestion for that. I am awiating for your reply.
If you are not using license file and not setting the license in your code, an extra Evaluation worksheet will be always added to the generated file and will be active by default. You need to use a valid license and set the license file into your code before using other Aspose.Cells for .NET API, see the document for your reference:
When you use/set valid license, then you may activate your desired sheet for your need, see the topic:
Thanks for your reply. No Evaluation warning message after putting my license file. I need one more query, I need to block to delete the sheet2 by the user manually in excel because I have the data’s in Sheet2. So How can i protect to not delete the sheet2.
Please provide your suggest.
I think you may protect your worksheet so that the users cannot delete any row/column or cells formatting or values, please see the topics here:
Moreover, MS Excel does not directly provide to restrict deleting a worksheet in the workbook, however, you may use Workbook.Protect() method to protect the contents, structures, windows, objects etc. you may use:
Doing so, the user will not able to see the Delete command on right clicking on the worksheet tabs.