Create DropDownList in Microsoft Office Excel using C#
While working with .Net windows applications and Microsoft Office you might require to add validations programmatically for Microsoft Excel. Today we discuss about how to create drop down list in Microsoft Office Excel using C# programmatically.
Open Visual Studio => Create New Project => Select Windows Application from available templates and name it as CSharpExcelDDL.
Now add reference for Microsoft.office.Interop.Excel .Net dll. Add some datatable to create the drop down list in Excel. First create one Excel Workbook by using Application & Workbooks classes with two worksheets; one worksheet is for MasteData of Excel sheet and another worksheet to enter data as shown below.
private void button1_Click(object sender, EventArgs e)
{
DataTable dt1 = new DataTable();
dt1.Columns.Add("Name");
dt1.Rows.Add("A");
dt1.Rows.Add("B");
dt1.Rows.Add("C");
dt1.Rows.Add("D");
dt1.Rows.Add("E");
Excel.Application oXL = new Excel.Application();
oXL.SheetsInNewWorkbook = 2;
Excel.Workbook oWB = oXL.Workbooks.Add();
Excel.Worksheet oMasterSheet = oWB.Worksheets["sheet2"];
oMasterSheet.Name = "MasterData";
Excel.Worksheet oSheet = oWB.Worksheets["sheet1"];
oSheet.Name = "UserData";
SetExcelMasterData(dt1, oMasterSheet, "Name", "Name", 1, oSheet, "A", "A");
string sFileName = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "ExcelFile.xlsx";
oWB.SaveAs(sFileName);
oWB.Close(Type.Missing);
Application.DoEvents();
Process showXL = Process.Start(sFileName);
Application.DoEvents();
}
private void SetExcelMasterData(DataTable dt, Excel.Worksheet oMasterSheet, string sMasterHeader, string sField, int iCol, Excel.Worksheet oSheet, string sCell, string sMasterCell)
{
oMasterSheet.Cells.Item[1, iCol] = sMasterHeader;
for (int i = 0; i < dt.Rows.Count; i++)
{
oMasterSheet.Cells.Item[i + 2, iCol] = dt.Rows[i][sField];
}
oSheet.Range[sCell + "2"].EntireColumn.Validation.Add(Excel.XlDVType.xlValidateList, (AlertStyle: Excel.XlDVAlertStyle.xlValidAlertStop), Operator: Excel.XlFormatConditionOperator.xlBetween, Formula1: "=" + oMasterSheet.Name + "!$" + sMasterCell + "$2:$" + sMasterCell + "$" + (dt.Rows.Count + 1).ToString());
oSheet.Range[sCell + "2"].EntireColumn.Validation.InCellDropdown = true;
oSheet.Range[sCell + "2"].EntireColumn.Validation.ErrorTitle = "Error in Validation";
oSheet.Range[sCell + "2"].EntireColumn.Validation.ErrorMessage = "Please select value from list";
}
As shown above we are adding validations for sheet1 UserData A cell, that means in UserData A cell you have to select data from drop down list where drop down list has data from Sheet2 MasterData.
Now execute the application; Click “Open Excel” button on windows form, it will open Microsoft Office Excel File. The output Excel File contains two WorkSheets one is for MasterData and another is to enter data.
The MasterData worksheet is as shown below.
The UserData is as shown below where user will enter data by selecting from drop down list.
