- Demo
- C# source
- VB.Net source
The sample demonstrates how to provide a dropdownlist for valid data
using System;
using Spire.Xls;
namespace DropDownList
{
class Program
{
private static void CreateSampleData(Worksheet sheet)
{
sheet.Range["D8"].Text = "Country";
//set the datasource of the E8
sheet.Range["X1"].Text = "Lucy";
sheet.Range["X2"].Text = "Bolivia";
sheet.Range["X3"].Text = "Brazil";
sheet.Range["X4"].Text = "Canada";
sheet.Range["X5"].Text = "Chile";
sheet.Range["X6"].Text = "Colombia";
sheet.Range["X7"].Text = "Cuba";
sheet.Range["X8"].Text = "Ecuador";
sheet.Range["X9"].Text = " Salvado";
sheet.Range["X10"].Text = "Guyana";
sheet.Range["X11"].Text = "Jamaicatates";
sheet.Range["X12"].Text = "Mexico";
sheet.Range["X13"].Text = "Nicaragua";
sheet.Range["X14"].Text = "Paraguay";
sheet.Range["X15"].Text = "Peru";
sheet.Range["X16"].Text = "United";
sheet.Range["X17"].Text = "Uruguay";
sheet.Range["X18"].Text = "Venezuel";
CellRange rangeName = sheet.Range["E8"];
rangeName.DataValidation.AllowType = CellDataType.Formula;
rangeName.DataValidation.DataRange = sheet.Range["X1:X18"];
rangeName.DataValidation.IgnoreBlank = true;
rangeName.Activate();
//hide column X
sheet.HideColumn(sheet.Range["X1"].Column);
sheet.Range["D10"].Text = "Capital";
//set the datasource of the E9
sheet.Range["Y1"].Text = "Buenos Aires";
sheet.Range["Y2"].Text = "La Paz";
sheet.Range["Y3"].Text = "Brasilia";
sheet.Range["Y4"].Text = "Ottawa";
sheet.Range["Y5"].Text = "Santiago";
sheet.Range["Y6"].Text = "Bagota";
sheet.Range["Y7"].Text = "Havana";
sheet.Range["Y8"].Text = "Quito";
sheet.Range["Y9"].Text = "San Salvador";
sheet.Range["Y10"].Text = "Georgetown";
sheet.Range["Y11"].Text = "Kingston";
sheet.Range["Y12"].Text = "Mexico City";
sheet.Range["Y13"].Text = "Managua";
sheet.Range["Y14"].Text = "Asuncion";
sheet.Range["Y15"].Text = "Lima";
sheet.Range["Y16"].Text = "Washington";
sheet.Range["Y17"].Text = "Montevideo";
sheet.Range["Y18"].Text = "Caracas";
//Binding the datasource
CellRange rangeCapital = sheet.Range["E10"];
rangeCapital.DataValidation.AllowType = CellDataType.Formula;
rangeCapital.DataValidation.DataRange = sheet.Range["Y1:Y18"];
rangeCapital.DataValidation.IsSuppressDropDownArrow = false;
rangeCapital.Activate();
//hide coulumn Y
sheet.HideColumn(sheet.Range["Y1"].Column);
sheet.Range["D12"].Text = "Continent";
//set the datasource of the F9
CellRange rangeContinent = sheet.Range["E12"];
rangeContinent.DataValidation.AllowType = CellDataType.Formula;
rangeContinent.DataValidation.Values = new String[] { "South America", "North America" };
rangeContinent.DataValidation.IsSuppressDropDownArrow = false;
rangeContinent.Activate();
//Set the column's width
sheet.Range["E8"].Text = "Please select the country";
sheet.Range["E10"].Text = "Please select the capital";
sheet.Range["E12"].Text = "Please select the con tinent";
sheet.AutoFitColumn(4);
sheet.AutoFitColumn(5);
//Style
sheet.Range["D8:D12"].Style.Font.IsBold = true;
sheet.Range["D8"].Style.KnownColor = ExcelColors.Color36;
sheet.Range["D10"].Style.KnownColor = ExcelColors.Color36;
sheet.Range["D12"].Style.KnownColor = ExcelColors.Color36;
sheet.Range["E8"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["E10"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["E12"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.SetRowHeight(8, 16);
sheet.SetRowHeight(10, 16);
sheet.SetRowHeight(12, 16);
}
static void Main(string[] args)
{
//Build the workbook
Workbook workbook = new Workbook();
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.GridLinesVisible = false;
//Write sample data
CreateSampleData(sheet);
workbook.SaveToFile("test.xls");
System.Diagnostics.Process.Start(workbook.FileName);
}
}
}
Imports Spire.Xls
Module Module1
Private Sub CreateSampleData(ByVal sheet As Worksheet)
sheet.Range("D8").Text = "Country"
'Set the datasource of D9
sheet.Range("X1").Text = "Lucy"
sheet.Range("X2").Text = "Bolivia"
sheet.Range("X3").Text = "Brazil"
sheet.Range("X4").Text = "Canada"
sheet.Range("X5").Text = "Chile"
sheet.Range("X6").Text = "Colombia"
sheet.Range("X7").Text = "Cuba"
sheet.Range("X8").Text = "Ecuador"
sheet.Range("X9").Text = " Salvado"
sheet.Range("X10").Text = "Guyana"
sheet.Range("X11").Text = "Jamaicatates"
sheet.Range("X12").Text = "Mexico"
sheet.Range("X13").Text = "Nicaragua"
sheet.Range("X14").Text = "Paraguay"
sheet.Range("X15").Text = "Peru"
sheet.Range("X16").Text = "United"
sheet.Range("X17").Text = "Uruguay"
sheet.Range("X18").Text = "Venezue"
'Binding the datasource of D9
Dim rangeCountry As CellRange = sheet.Range("E8")
rangeCountry.DataValidation.AllowType = CellDataType.Formula
rangeCountry.DataValidation.DataRange = sheet.Range("X1:X18")
rangeCountry.DataValidation.IsSuppressDropDownArrow = False
rangeCountry.DataValidation.IgnoreBlank = True
rangeCountry.Activate()
sheet.HideColumn(sheet.Range("X1").Column)
sheet.Range("D10").Text = "Capital"
'Set the datasource of E9
sheet.Range("Y1").Text = "Buenos Aires"
sheet.Range("Y2").Text = "La Paz"
sheet.Range("Y3").Text = "Brasilia"
sheet.Range("Y4").Text = "Ottawa"
sheet.Range("Y5").Text = "Santiago"
sheet.Range("Y6").Text = "Bagota"
sheet.Range("Y7").Text = "Havana"
sheet.Range("Y8").Text = "Quito"
sheet.Range("Y9").Text = "San Salvador"
sheet.Range("Y10").Text = "Georgetown"
sheet.Range("Y11").Text = "Kingston"
sheet.Range("Y12").Text = "Mexico City"
sheet.Range("Y13").Text = "Managua"
sheet.Range("Y14").Text = "Asuncion"
sheet.Range("Y15").Text = "Lima"
sheet.Range("Y16").Text = "Washington"
sheet.Range("Y17").Text = "Montevideo"
sheet.Range("Y18").Text = "Caracas"
'Binding the datasource of E9
Dim rangeCapital As CellRange = sheet.Range("E10")
rangeCapital.DataValidation.AllowType = CellDataType.Formula
rangeCapital.DataValidation.DataRange = sheet.Range("Y1:Y18")
rangeCapital.DataValidation.IsSuppressDropDownArrow = False
rangeCapital.DataValidation.IgnoreBlank = True
rangeCapital.Activate()
sheet.HideColumn(sheet.Range("Y1").Column)
sheet.Range("D12").Text = "Continent"
'Set the datasource of F9
Dim rangeContinent As CellRange = sheet.Range("E12")
rangeContinent.DataValidation.AllowType = CellDataType.Formula
rangeContinent.DataValidation.Values = New String() {"South America", "North America"}
rangeContinent.DataValidation.IsSuppressDropDownArrow = False
rangeContinent.Activate()
'Set the width of the column
sheet.Range("E8").Text = "Please select the country "
sheet.Range("E10").Text = "Please select the capital "
sheet.Range("E12").Text = "Please select the continent "
sheet.AutoFitColumn(4)
sheet.AutoFitColumn(5)
'Style
sheet.Range("D8:D12").Style.Font.IsBold = True
sheet.Range("D8").Style.KnownColor = ExcelColors.Color36
sheet.Range("D10").Style.KnownColor = ExcelColors.Color36
sheet.Range("D12").Style.KnownColor = ExcelColors.Color36
sheet.Range("E8").Style.KnownColor = ExcelColors.LightGreen1
sheet.Range("E10").Style.KnownColor = ExcelColors.LightGreen1
sheet.Range("E12").Style.KnownColor = ExcelColors.LightGreen1
sheet.SetRowHeight(8, 16)
sheet.SetRowHeight(10, 16)
sheet.SetRowHeight(12, 16)
End Sub
Sub Main()
Dim workbook As Workbook = New Workbook()
workbook.CreateEmptySheet(1)
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.GridLinesVisible = False
'Writes sample data
CreateSampleData(sheet)
workbook.SaveToFile("test.xls")
System.Diagnostics.Process.Start(workbook.FileName)
End Sub
End Module
