Hi, i would like to create a combox box in the excel sheet.
I check the following method: Class ComboBox | Aspose.Cells for .NET API Reference
it appears I would need to create a range for combox box to reference.
Is it any way where I do not need to create a range to set value into combo box?
@catalparue,
Could you please provide more details about your requirement? How do you want to populate the values in the combo box. Create a desired Excel file containing such combo box using MS Excel only for our reference. We will analyze your template file and provide steps to acheive the same using Aspose.Cells.
hi, following is my sample code from interop. I would like ‘“Item1,Item2,Item3”’ to save in the combox box directly. (I think in Aspose Cell I need to put “Item1,Item2,Item3” into other cell and Aspose combox refer to this )?
Excel.Application App = null;
Excel.Workbook Book = null;
Excel.Worksheet Sheet = null;
object Missing = System.Reflection.Missing.Value;
try
{
App = new Excel.Application();
Book = App.Workbooks.Add();
Sheet = (Excel.Worksheet) Book.Worksheets[1];
Excel.Range Range = Sheet.get_Range("B2", "B2");
Range.Validation.Add(Excel.XlDVType.xlValidateList
, Excel.XlDVAlertStyle.xlValidAlertStop
, Excel.XlFormatConditionOperator.xlBetween
, "Item1,Item2,Item3"
, Type.Missing);
Range.Validation.InCellDropdown = true;
Range.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(255, 217, 217, 0));
App.Visible = true;
}
finally
{
Base.ReleaseObject(Sheet);
Base.ReleaseObject(Book);
Base.ReleaseObject(App);
}
In MS Excel, if you use form control (combobox), it may not be possible to set constant values (range) instead of cells range. However, it is possible to use List data validation (as per your interop. code) to specify your flat list/constant values instead of cells range. See the sample code for your reference that works find as I tested:
e.g.
Sample code:
Workbook workbook = new Workbook();
Worksheet WorkSheet = workbook.Worksheets[0];
//Extend width for first and second column a bit.
WorkSheet.Cells.SetColumnWidthPixel(0, 120);
WorkSheet.Cells.SetColumnWidthPixel(1,89);
WorkSheet.Cells["A2"].PutValue("Choose items");
var validations = WorkSheet.Validations;
//Define list to store data.
var list = new List<string>();
list.Add("Item1");
list.Add("Item2");
list.Add("Item3");
list.Add("Item4");
list.Add("Item5");
//Specify the separator for the data list.
var flatList = string.Join(",", list.ToArray());
//Define area B2:B2
CellArea area;
area.StartRow = 1;
area.EndRow = 1;
area.StartColumn = 1;
area.EndColumn = 1;
var validation = validations[validations.Add(area)];
validation.Type = ValidationType.List;
validation.Operator = OperatorType.Between;
validation.InCellDropDown = true;
validation.Formula1 = flatList;
validation.ShowError = true;
validation.AlertStyle = ValidationAlertType.Stop;
// Set the error title
validation.ErrorTitle = "Test";
//Set the error message
validation.ErrorMessage = "Test message";
workbook.Save("e:\\test2\\out1.xlsx");
Hope, this helps a bit.
thanks! it looks good!
@catalparue,
Please note:
First run the sample code to generate “out1.xlsx” file. Now open it into MS Excel manually. Move the cursor to B2 cell via arrow keys or click on B2 cell and the comboxbox list data validation would be active and dropdown arrow would be shown in the worksheet.
Let us know if you still find any issue.
thanks! it work perfectly!
One more query. How to set default value into combobox? eg: Item2.
@catalparue,
You may just set cell value as the desired item where this combo box is displayed before saving the file as shown in the following sample code:
...
...
...
//Set desired value before saving the file
WorkSheet.Cells["B2"].PutValue("Item2");
workbook.Save("out1.xlsx");
hi, thanks for your suggestion.
Above example is using CellArea area;
for example, if my CellArea area is from B2:Z2…
How can i set all the default for all those range? Can I use the same object CellArea or need to use new object like Range?
Yes, you may create a range based on the cell area (“B2:Z2”) and then set value to that range in one go. See the updated code segment for your reference:
e.g.
Sample code:
Workbook workbook = new Workbook();
Worksheet WorkSheet = workbook.Worksheets[0];
//Extend width for first and second column a bit.
WorkSheet.Cells.SetColumnWidthPixel(0, 120);
WorkSheet.Cells.SetColumnWidthPixel(1,89);
WorkSheet.Cells["A2"].PutValue("Choose items");
var validations = WorkSheet.Validations;
//Define list to store data.
var list = new List<string>();
list.Add("Item1");
list.Add("Item2");
list.Add("Item3");
list.Add("Item4");
list.Add("Item5");
//Specify the separator for the data list.
var flatList = string.Join(",", list.ToArray());
//Define area B2:Z2
CellArea area;
area.StartRow = 1;
area.EndRow = 1;
area.StartColumn = 1;
area.EndColumn = 25;
var validation = validations[validations.Add(area)];
validation.Type = ValidationType.List;
validation.Operator = OperatorType.Between;
validation.InCellDropDown = true;
validation.Formula1 = flatList;
validation.ShowError = true;
validation.AlertStyle = ValidationAlertType.Stop;
// Set the error title
validation.ErrorTitle = "Test";
//Set the error message
validation.ErrorMessage = "Test message";
Range range = WorkSheet.Cells.CreateRange(area.StartRow, area.StartColumn, area.EndRow - area.StartRow + 1, area.EndColumn - area.StartColumn + 1);
range.Value = "Item2";
workbook.Save("e:\\test2\\out1.xlsx");
Hope, this helps a bit.
prefect ! thanks! Sahi.