EasyXLS™ library allows you to create drop down lists in Excel file. A drop down list is a data validation that permits entering only values from the list in the cell.
Source code sample
The below example shows how to export an Excel file with a drop down list with valid values from a named range.
// Create an instance of the class that exports Excel files, having two sheets
ExcelDocument workbook = new ExcelDocument(2);
// Set sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab");
workbook.easy_getSheetAt(1).setSheetName("Second tab");
...
// Create named range
xlsSecondTab.easy_addName("Range", "=Second tab!$A$1:$A$4");
// Add data validation as drop down list type
ExcelWorksheet xlsFirstTab = (ExcelWorksheet)workbook.easy_getSheetAt(0);
xlsFirstTab.easy_addDataValidator("A1:A10", DataValidator.VALIDATE_LIST,
DataValidator.OPERATOR_EQUAL_TO, "=Range", "");
// Create Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Excel drop down list.xlsx");
' Create an instance of the class that exports Excel files, having two sheetsDim workbook As New ExcelDocument(2)
' Set sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab")
workbook.easy_getSheetAt(1).setSheetName("Second tab")
...
' Create named range
xlsSecondTab.easy_addName("Range", "=Second tab!$A$1:$A$4")
' Add data validation as drop down list typeDim xlsFirstTab As ExcelWorksheet = workbook.easy_getSheetAt(0)
xlsFirstTab.easy_addDataValidator("A1:A10", DataValidator.VALIDATE_LIST, _
DataValidator.OPERATOR_EQUAL_TO, "=Range", "")
' Create Excel file
workbook.easy_WriteXLSXFile("C:\Samples\Excel drop down list.xlsx")
C++// Create an instance of the class that exports Excel files
EasyXLS::IExcelDocumentPtr workbook;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelDocument),
NULL,
CLSCTX_ALL,
__uuidof(EasyXLS::IExcelDocument),
(void**) &workbook) ;
// Create two sheets
workbook->easy_addWorksheet_2("First tab");
workbook->easy_addWorksheet_2("Second tab");
...
// Create named range
xlsSecondTab->easy_addName_2("Range", "=Second tab!$A$1:$A$4");
// Add data validation as drop down list type
EasyXLS::IExcelWorksheetPtr xlsFirstTab =
(EasyXLS::IExcelWorksheetPtr)workbook->easy_getSheetAt(0);
xlsFirstTab->easy_addDataValidator_3("A1:A10", DATAVALIDATOR_VALIDATE_LIST,
DATAVALIDATOR_OPERATOR_EQUAL_TO, "=Range", "");
// Create Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Excel drop down list.xlsx");
Click here to see Continuous Code ListingC++.NET// Create an instance of the class that exports Excel files, having two sheets
ExcelDocument ^workbook = gcnew ExcelDocument(2);
// Set sheet names
workbook->easy_getSheetAt(0)->setSheetName("First tab");
workbook->easy_getSheetAt(1)->setSheetName("Second tab");
...
// Create named range
xlsSecondTab->easy_addName("Range", "=Second tab!$A$1:$A$4");
// Add data validation as drop down list type
ExcelWorksheet ^xlsFirstTab =
safe_cast<ExcelWorksheet^>(workbook->easy_getSheetAt(0));
xlsFirstTab->easy_addDataValidator("A1:A10", DataValidator::VALIDATE_LIST,
DataValidator::OPERATOR_EQUAL_TO, "=Range", "");
// Create Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Excel drop down list.xlsx");
Click here to see Continuous Code Listing
// Create an instance of the class that exports Excel files, having two sheets
ExcelDocument workbook = new ExcelDocument(2);
// Set sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab");
workbook.easy_getSheetAt(1).setSheetName("Second tab");
...
// Create named range
xlsSecondTab.easy_addName("Range", "=Second tab!$A$1:$A$4");
// Add data validation as drop down list type
ExcelWorksheet xlsFirstTab = (ExcelWorksheet)workbook.easy_getSheetAt(0);
xlsFirstTab.easy_addDataValidator("A1:A10", DataValidator.VALIDATE_LIST,
DataValidator.OPERATOR_EQUAL_TO, "=Range", "");
// Create Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Excel drop down list.xlsx");
.NET:// Create an instance of the class that exports Excel files
$workbook = new COM("EasyXLS.ExcelDocument");
// Create two sheets
$workbook->easy_addWorksheet_2("First tab");
$workbook->easy_addWorksheet_2("Second tab");
...
// Create named range
$xlsSecondTab->easy_addName_2("Range", "=Second tab!\$A\$1:\$A\$4");
// Add data validation as drop down list type
$xlsFirstTab = $workbook->easy_getSheetAt(0);
$xlsFirstTab->easy_addDataValidator_3("A1:A10", $DATAVALIDATOR_VALIDATE_LIST,
$DATAVALIDATOR_OPERATOR_EQUAL_TO, "=Range", "");
// Create Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Excel drop down list.xlsx");
Click here to see Continuous Code ListingJava:// Create an instance of the class that exports Excel files
$workbook = new java("EasyXLS.ExcelDocument");
// Create two sheets
$workbook->easy_addWorksheet("First tab");
$workbook->easy_addWorksheet("Second tab");
...
// Create a named area range
$xlsSecondTab->easy_addName("Range", "=Second tab!\$A\$1:\$A\$4");
// Add data validation as drop down list type
$xlsFirstTab = $workbook->easy_getSheetAt(0);
$xlsFirstTab->easy_addDataValidator("A1:A10", $DATAVALIDATOR_VALIDATE_LIST,
$DATAVALIDATOR_OPERATOR_EQUAL_TO, "=Range", "");
// Export Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Excel drop down list.xlsx");
Click here to see Continuous Code Listing
' Create an instance of the class that exports Excel filesset workbook = Server.CreateObject("EasyXLS.ExcelDocument")
' Create two sheets
workbook.easy_addWorksheet_2("First tab")
workbook.easy_addWorksheet_2("Second tab")
...
' Create named range
xlsSecondTab.easy_addName_2 "Range", "=Second tab!$A$1:$A$4"' Add data validation as drop down list typeset xlsFirstTab = workbook.easy_getSheetAt(0)
xlsFirstTab.easy_addDataValidator_3 "A1:A10", DATAVALIDATOR_VALIDATE_LIST, _
DATAVALIDATOR_OPERATOR_EQUAL_TO, "=Range", ""' Create Excel file
workbook.easy_WriteXLSXFile ("C:\Samples\Excel drop down list.xlsx")
' Create an instance of the class that exports Excel filesSet workbook = CreateObject("EasyXLS.ExcelDocument")
' Create two sheets
workbook.easy_addWorksheet_2("First tab")
workbook.easy_addWorksheet_2("Second tab")
...
' Create named range
xlsSecondTab.easy_addName_2 "Range", "=Second tab!$A$1:$A$4"' Add data validation as drop down list typeSet xlsFirstTab = workbook.easy_getSheetAt(0)
xlsFirstTab.easy_addDataValidator_3 "A1:A10", _
DataValidator.DATAVALIDATOR_VALIDATE_LIST, _
DataValidator.DATAVALIDATOR_OPERATOR_EQUAL_TO, "=Range", ""' Create Excel file
workbook.easy_WriteXLSXFile ("C:\Samples\Excel drop down list.xlsx")
' Create an instance of the class that exports Excel filesSet workbook = CreateObject("EasyXLS.ExcelDocument")
' Create two sheets
workbook.easy_addWorksheet_2("First tab")
workbook.easy_addWorksheet_2("Second tab")
...
' Create named range
xlsSecondTab.easy_addName_2 "Range", "=Second tab!$A$1:$A$4"' Add data validation as drop down list typeset xlsFirstTab = workbook.easy_getSheetAt(0)
xlsFirstTab.easy_addDataValidator_3 "A1:A10", VALIDATE_LIST, _
OPERATOR_EQUAL_TO, "=Range", ""' Create Excel file
workbook.easy_WriteXLSXFile ("C:\Samples\Excel drop down list.xlsx")
<!-- Create an instance of the class that exports Excel files --><cfobject type="java"class="EasyXLS.ExcelDocument"name="workbook"action="CREATE"><!-- Create two sheets --><cfset ret = workbook.easy_addWorksheet("First tab")><cfset ret = workbook.easy_addWorksheet("Second tab")>
...
<!-- Create named range --><cfset xlsSecondTab.easy_addName("Range", "=Second tab!$A$1:$A$4")><!-- Add data validation as drop down list type --><cfset xlsFirstTab = workbook.easy_getSheetAt(0)><cfset xlsFirstTab.easy_addDataValidator("A1:A10", DataValidator.VALIDATE_LIST,
DataValidator.OPERATOR_EQUAL_TO, "=Range", "")><!-- Create Excel file --><cfset ret = workbook.easy_WriteXLSXFile("C:\Samples\Excel drop down list.xlsx")>
.NET:# Create an instance of the class that exports Excel files, having two sheets
workbook = ExcelDocument(2)
# Set the sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab")
workbook.easy_getSheetAt(1).setSheetName("Second tab")
...
# Create a named area range
xlsSecondTab.easy_addName("Range", "=Second tab!$A$1:$A$4")
# Add data validation as drop down list type
xlsFirstTab = workbook.easy_getSheetAt(0)
xlsFirstTab.easy_addDataValidator("A1:A10", DataValidator.VALIDATE_LIST,
DataValidator.OPERATOR_EQUAL_TO, "=Range", "")
# Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Excel drop down list.xlsx")
Click here to see Continuous Code ListingJava:# Create an instance of the class that exports Excel files, having two sheets
workbook = gateway.jvm.ExcelDocument(2)
# Set the sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab")
workbook.easy_getSheetAt(1).setSheetName("Second tab")
...
# Create a named area range
xlsSecondTab.easy_addName("Range", "=Second tab!$A$1:$A$4")
# Add data validation as drop down list type
xlsFirstTab = workbook.easy_getSheetAt(0)
xlsFirstTab.easy_addDataValidator("A1:A10", gateway.jvm.DataValidator.VALIDATE_LIST,
gateway.jvm.DataValidator.OPERATOR_EQUAL_TO, "=Range", "")
# Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Excel drop down list.xlsx")
Click here to see Continuous Code Listing
The screen shot provides an example of an Excel file with a drop down list.
