Create a dropdownlist of checkboxes using EPPlus

4.2k views Asked by At

I am using EPPlus (http://epplus.codeplex.com/) to create Excel files in my ASP.NET application. I was wondering if anyone knows if it's possible to create a dropdownlist with checkboxes in one cell. I've checked the documentation, but found nothing so far. I've also tried Googling it but haven't found anything in the right direction yet.

This question on their forum is actually a pretty good demonstration of what I'm looking for, but it hasn't received any answers: http://epplus.codeplex.com/discussions/585879

Does anyone happen to have any ideas and can point me in the right direction?

2

There are 2 answers

0
Yahya Hussein On

For others reference, here is how I could enable multiple values select:

By inserting VBA Code:

see how to do it in here , using EPPlus you can insert VBA code using something like:

package.Workbook.CreateVBAProject();
worksheet.CodeModule.Code = code;

This following code did the trick:

using (var package = new ExcelPackage(new System.IO.FileInfo("D:\\b.xlsm")))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("11");
            var validationCell = worksheet.DataValidations.AddListValidation("A1");
            validationCell.Formula.Values.Add("a");
            validationCell.Formula.Values.Add("b");
            validationCell.Formula.Values.Add("c");

            string code = "Private Sub Worksheet_Change(ByVal Target As Range)\n" +
"Dim Oldvalue As String\n" +
"Dim Newvalue As String\n" +
"Application.EnableEvents = True\n" +
"On Error GoTo Exitsub\n" +
"If Target.Address = \"$A$1\" Then\n" +
 " If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then\n" +
  "  GoTo Exitsub\n" +
  "Else: If Target.Value = \"\" Then GoTo Exitsub Else\n" +
   " Application.EnableEvents = False\n" +
    "Newvalue = Target.Value\n" +
    "Application.Undo\n" +
    "Oldvalue = Target.Value\n" +
    "  If Oldvalue = \"\" Then\n" +
     "   Target.Value = Newvalue\n" +
      "Else\n" +
       " If InStr(1, Oldvalue, Newvalue) = 0 Then\n" +
        "    Target.Value = Oldvalue & \", \" & Newvalue\n" +
      "Else: \n" +
       "         Target.Value = Oldvalue\n" +
      "End If\n" +
    "End If\n" +
  "End If\n" +
 "End If\n" +
"Application.EnableEvents = True\n" +
"Exitsub: \n" +
 "  Application.EnableEvents = True\n" +
"End Sub";

            package.Workbook.CreateVBAProject();
            worksheet.CodeModule.Code = code;
            package.Save();
        }
0
ssabbattini On

You can use:

var validationCell = sheet.DataValidations.AddListValidation("A1");
validationCell.Formula.Values.Add("a");
validationCell.Formula.Values.Add("b");
validationCell.Formula.Values.Add("c");
...

but you can choose only one single value. I think that multiple values are not supported by excel.