How to create cell in excel file having multiple selection dropdown using OpenXml?

user_3096059 0
Viewed 657

As I am working on functionality to write an excel file using DocumentFormat.OpenXML NuGet package.

I can create the dropdown list on a specific cell in excel but my requirement is, that specific cell should be allowed the user to select multiple items from dropdown list.

Using the below code I am able to create dropdown on cell but that cell is not allowing the multiple selections.

            DataValidation dataValidation = new DataValidation
            {
                Type = DataValidationValues.List,
                AllowBlank = true,
                SequenceOfReferences = new ListValue<StringValue>() { InnerText = "B1" },
                Formula1 = new Formula1("'Cricket Team'!$A$1:$A$3")

            };

            DataValidations dataValidations = worksheet1.GetFirstChild<DataValidations>();
            if (dataValidations != null)
            {
                dataValidations.Count = dataValidations.Count + 1;
                dataValidations.Append(dataValidation);
            }
            else
            {
                DataValidations newdataValidations = new DataValidations();
                newdataValidations.Append(dataValidation);
                newdataValidations.Count = 1;
                worksheet1.Append(newdataValidations);
            }

Sample Output of this code is:

enter image description here

My requirement is, the user can select multiple items from dropdown list.

asp.net-core openxml excel c#
1 Answers

my requirement is, that specific cell should be allowed the user to select multiple items from dropdown list.

You can try to set and apply the filter to the range via AutoFilter, like below.

Worksheet sheet1 = new Worksheet();
sheet1.Append(sheetData);

// set the AutoFilter
// and set the range based on your requirement and data items
AutoFilter autoFilterForName = new AutoFilter(){ Reference = "B1:B" + 3 }; 

sheet1.Append(autoFilterForName);

Test Result

enter image description here

user_67516340
Related