How to add a dropdownlist in excel using spreadsheetgear?

Question

I want to add drop down list in one of column of Excel sheet. How to add it using spreadsheetgear????

Answer 1

If you are trying to add a cell Data Validation dropdown list to a column, you can do this using the SpreadsheetGear.IValidation interface, available from the IRange.Validation property. I’ve provided some sample code below that demonstrates building two columns of data validation. Column B pulls its dropdown items from a range of cells on the same sheet; Column C pulls its dropdown items from a static list of values.

// Create workbook and a local variable to Cells
IWorkbook workbook = Factory.GetWorkbook();
IRange cells = workbook.ActiveWorksheet.Cells;
// Build up some data to use in our validation list
cells["A1:A5"].Value = "=ROUND(RAND()*100, 0)";
// Create cell validation on Column B using values from other cells
cells["B:B"].Validation.Add(SpreadsheetGear.ValidationType.List, ValidationAlertStyle.Information, ValidationOperator.Default, "=$A$1:$A$5", "");
// Create cell validation on Column C using a static list
cells["C:C"].Validation.Add(SpreadsheetGear.ValidationType.List, ValidationAlertStyle.Information, ValidationOperator.Default, "a,b,c", "");

Note: I work at SpreadsheetGear and provide technical assistance for customers and evaluators of our product. Feel free to contact us at [email protected] if you have additional questions.

Answer 2

You can use cell Validation. It works similarly to how you do it in Excel.

private void CreateList(SpreadsheetGear.IRange cell, string list)
{
  cell.Validation.Add(
        SpreadsheetGear.ValidationType.List,
        SpreadsheetGear.ValidationAlertStyle.Warning,
        SpreadsheetGear.ValidationOperator.Default,
        list, null);
}

In a windows forms program, you would call it something like this:

workbookView1.GetLock();
try
{
  SpreadsheetGear.IRange cell =workbookView1.ActiveWorksheet.Cells["A1"];
  CreateList(cell, "Alabama,Alaska,Arizona,Arkansas,California,Colorado,Connecticut,Delaware,Florida");
}
finally
{
  workbookView1.ReleaseLock();
}