Free Support Forum - aspose.com

Populate dropdown from datatable c# in Excel

Hi Team,

I am trying to populate dropdown in Excel cell from datatable c#. Datatable has one column.
Do you have some example for this task?

Please help.

Thank you,
pa

@rudolfkalik,

Thanks for your query.

You may create drop down list / Combobox control using Aspose.Cells APIs, for reference kindly check:
Adding combobox to a worksheet

For setting the contents of the dropdown from a database/datasource, you can import data from database to some Worksheet cells and set this data as a data source range for the Combobox control, kindly check how to import data to worksheet from different data sources:
Import Data into Worksheet

OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\\test\\Northwind.mdb");
con.Open();
OleDbCommand cmd = new OleDbCommand("Select CustomerID from Customers", con);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "Customers");

Workbook wb = new Workbook();
//Add a new sheet.
Worksheet sheet2 = wb.Worksheets[wb.Worksheets.Add()];
sheet2.Name = "Customers";
//Import the datatable
sheet2.Cells.ImportDataTable(ds.Tables["Customers"], false, 0, 0);
//Get the first default worksheet.
Worksheet sheet = wb.Worksheets[0];
//Get the worksheet cells collection.
Cells cells = sheet.Cells;
//Input a value.
cells["B3"].PutValue("Customers:");
//Set it bold.
Aspose.Cells.Style stl = wb.CreateStyle();
stl.Font.IsBold = true;
cells["B3"].SetStyle(stl);

//Add a new combo box.
ComboBox comboBox = sheet.Shapes.AddComboBox(2, 0, 2, 0, 22, 100);
//Set the linked cell;
//comboBox.LinkedCell = "A1";
int maxrow = sheet2.Cells.MaxDataRow;
string maxcellname = CellsHelper.CellIndexToName(maxrow, 0);
//Set the input range.
comboBox.InputRange = "Customers!A1:" + maxcellname;
//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();
//To hide Customers sheet (Sheet2);
//wb.Worksheets[1].IsVisible = false;
//Saves the file.
wb.Save(@"d:\test\tst_dataobtained.xls");

Hi,
I did your example. I can see combobox with populated data.
How can I read selected value from this box when I upload this file?
Selected value not showing in the cell (it is showing in combobox).

Please help.
RK

@rudolfkalik,

You may please use formula using INDEX() to display value from a combo box. Please try the following sample code and share your feedback.

// Instantiating a "Products" DataTable object
DataTable dataTable = new DataTable("Customers");

// Adding columns to the DataTable object
dataTable.Columns.Add("Product Name", typeof(string));

for (int i = 0; i < 10; i++)
{
    // Creating an empty row in the DataTable object
    DataRow dr = dataTable.NewRow();

    // Adding data to the row
    dr[0] = string.Format("Customer {0}", i + 1);

    // Adding filled row to the DataTable object
    dataTable.Rows.Add(dr);
}

Workbook wb = new Workbook();
//Add a new sheet.
Worksheet sheet2 = wb.Worksheets[wb.Worksheets.Add()];
sheet2.Name = "Customers";
//Import the datatable
sheet2.Cells.ImportDataTable(dataTable, false, 0, 0);
//Get the first default worksheet.
Worksheet sheet = wb.Worksheets[0];
//Get the worksheet cells collection.
Cells cells = sheet.Cells;
//Input a value.
cells["B3"].PutValue("Customers:");
//Set it bold.
Aspose.Cells.Style stl = wb.CreateStyle();
stl.Font.IsBold = true;
cells["B3"].SetStyle(stl);

//Add a new combo box.
ComboBox comboBox = sheet.Shapes.AddComboBox(2, 0, 2, 0, 22, 100);
//Set the linked cell;
comboBox.LinkedCell = "B4";
int maxrow = sheet2.Cells.MaxDataRow;
string maxcellname = CellsHelper.CellIndexToName(maxrow, 0);
//Set the input range.
comboBox.InputRange = "Customers!A1:" + maxcellname;
//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();

sheet.Cells["B6"].Formula = @"=INDEX(Customers!$A$1:$A$10,Sheet1!B4)";
//To hide Customers sheet (Sheet2);
//wb.Worksheets[1].IsVisible = false;
//Saves the file.
wb.Save(@"tst_dataobtained.xlsx");

Hi,

Do you have sample without creating combobox, but do this with Data Validation?

Thank you,
RK

@rudolfkalik,

You may please try following sample code. If it does not fulfill your requirements, please create a required file using Excel and share with us. We will assist you to create similar file using Aspose.Cells.

// 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.
Aspose.Cells.Range range = worksheet2.Cells.CreateRange("E1", "E4");

// 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.xlsx");

Thank you for your reply.
Please see attached test file.
Please show how program this list in cell C4.
Data in the Sheet2

In the Sheet2 can be more than 300 rows.

Thank you,
RKTest Data Validation List .zip (6.4 KB)

@rudolfkalik,

Thanks for the template file.

I think you should find some time to see and understand the code segments shared by Ahsan Iqbal. Anyways, I have written the following sample code to accomplish your task. I have used your template file as an input file and added List data validation type to C4 cell in the first worksheet based on the underlying data in the second sheet.
e.g
Sample code:

// Create a workbook object.
            Workbook workbook = new Workbook("e:\\test2\\Test Data Validation List .xlsx");

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

            // Get the second sheet.
            Worksheet worksheet2 = workbook.Worksheets[1];

            // Create a range in the first worksheet.
            Aspose.Cells.Range range = worksheet2.Cells.CreateRange("A1", "A5");

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

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

            // Create Cell Area 
            CellArea ca = new CellArea();
            ca.StartRow = 3;
            ca.EndRow = 3;
            ca.StartColumn = 2;
            ca.EndColumn = 2;

            // 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 using named range.
            //validation.Formula1 = "=MyRange";

            //Or
            // Alternatively, you may set the formula1 directly.
            validation.Formula1 = "=Sheet2!A1:A5";


            // 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 name from the list";

            // Save the Excel file.
            workbook.Save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.

It is works for me.

Thank you very much.
RK

@rudolfkalik,

Good to know that your issue is sorted out by the suggested code segment. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.