Hi Harish,
Thank you for your patience and understanding.
As far as I have understood your scenario (by looking at you previously provided sample spreadsheet), you wish to bind the database fields with the ComboBox in your spreadsheet. This cannot be accomplished directly because Aspose.Cells API does not provide any means to retrieve data from a Database and bind it to any control or place it in worksheet cells. Although, Aspose.Cells APIs provide an efficient means to import the data to spreadsheet cells if you have such data in a DataTable, ArrayList, DataGrid, DataReader and so on. Please check the detailed article on how to import the data to worksheets.
The workaround I devised for you can be achieved as follow,
- Connect to Database and retrieve the required data into one of the data holders as stated in the above shared article. You have to write your own custom routines to connect with your Database and retrieve required data because as discussed above this feature is beyond the scope of Aspose.Cells API.
- Once you have the data in any of the mentioned data holders, you can import it in worksheet cells by calling appropriate import method provided by Cells Class.
- Create a ComboBox in any worksheet and set the ComboBox.InputRange property so that it points to the imported data.
- Optionally, if you wish to hide the worksheet having the imported data, you can set the Worksheet.IsVisible property to false.
For better elaboration, I have dynamically created a DataTable and imported it to worksheet cells. Then I have created a ComboBox in another worksheet (other than the one where data resides) and sets its InputRange property. Please check the below provided code snippet and attached resultant spreadsheet for your reference.
C#
DataTable table = new DataTable();
table.Columns.Add(“Field”, typeof(string));
table.Rows.Add(“Finance”);
table.Rows.Add(“Auto”);
table.Rows.Add(“Games”);
table.Rows.Add(“Groups”);
table.Rows.Add(“HotJobs”);
table.Rows.Add(“Maps”);
table.Rows.Add(“Mobile”);
table.Rows.Add(“Movies”);
table.Rows.Add(“Music”);
table.Rows.Add(“Shopping”);
table.Rows.Add(“Sports”);
table.Rows.Add(“Tech”);
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
//Import Data to worksheet
sheet.Cells.ImportDataTable(table, false, 0, 0, true);
//Add another worksheet to hold the ComboBox
sheet = book.Worksheets.Add(“Sheet2”);
//Add a ComboBox at a specified location in worksheet
ComboBox comboBox = sheet.Shapes.AddComboBox(0, 0, 4, 0, 25, 90);
//Specify the range of cells that will be treated as ComboBox Values
comboBox.InputRange = “Sheet1!A2:A12”;
//Specify the SelectedIndex for the default selected value
comboBox.SelectedIndex = 0;
//[Optional] hide the worksheet holding the actual data
book.Worksheets[0].IsVisible = false;
//Save workbook
book.Save(myDir + “CustomComboBox.xlsx”);
I hope the above provided code snippet covers your main scenario with ComboBox. Please feel free to write back in case you need our further assistance.