How to add dropdown in excel sheet programmatically?

Question

I am using the ExcelPackage dll to creating the excel from the .net application.

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

Also I want to do some formatting changes to the cell values.

Answer 1

Try spreadsheetgear ....

Answer 2

    protected void AddDropDownToExcel(string path)
        {
            //path gives the location where u have created excel file
            string fileName = path.Replace("\\", "\\\\");
// F is the column name where you want to place the dropdown
            string RowCount = "F" + gridrowcount;
            // Open Excel and get first worksheet.      
            var workbook = application.Workbooks.Open(fileName);
            var worksheet = workbook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;     
            // Set range for dropdownlist       
            var rangeNewStatus = worksheet.get_Range("F2", RowCount);
            rangeNewStatus.ColumnWidth = 20;
            rangeNewStatus.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop,
            Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween, "dropdownlistitem1, dropdownlistitem2");
            // Save.
            workbook.Save();
            workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);
            application.Quit();
        }