CODEWITHSUNDEEP
c#exceloffice-interopcom-interopexcel-interop
Vlad Schnakovszki
Vlad Schnakovszki

Reputation: 8601

Generating Excel file with Pre-poulated Dropdown Values with C#'s Microsoft.Office.Interop.Excel

I want to generate an Excel file from my C# application, which users will be able to fill in later on. I'm using Microsoft.Office.Interop.Excel.

I'm launching Excel and populating the header cells using the below code:

var excelApp = new Excel.Application {Visible = true};
var workbook = excelApp.Workbooks.Add(Missing.Value);
var workSheet = (Excel.Worksheet) workbook.Worksheets.Item[1];

headers.Select((s, i) => new Tuple<string, int>(s, i)).ToList().ForEach
    (
     h => { workSheet.Cells[1, h.Item2 + 1] = h.Item1; }
    );

How can I specify that the first column's cells should have a drop-down containing pre-populated values?

I've tried loads of things available online, such as the below found here, without luck:

var dropDownRange = workSheet.Range["A2"].Resize[64000];
dropDownRange.Value = Values;
dropDownRange = dropDownRange.Offset[0, 1];
dropDownRange.Validation.Delete();
dropDownRange.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertInformation, Type.Missing, "=DropDownList");

Upvotes: 1

Views: 1357

Answers (1)

Ehz
Ehz

Reputation: 2067

The formula parameter expects a comma separated string of values when applying a validation limiting to a list. Here is an example that creates a dropdown with three values:

var items = new List<string>() { "Item 1", "Item 2", "Item 3" };
var formattedItems = string.Join(", ", items.ToArray());

var dropDownRange = workSheet.Range["A2"].EntireColumn;
dropDownRange.Validation.Delete();
dropDownRange.Validation.Add(Excel.XlDVType.xlValidateList,
    Excel.XlDVAlertStyle.xlValidAlertInformation,
    Excel.XlFormatConditionOperator.xlBetween,
    formattedItems,
    Type.Missing);

Also if you need to default the cells to a value you can do this:

dropDownRange.Value = "Item 2";

or even better:

dropDownRange.Value = items[1];

Upvotes: 4

Related Questions