CODEWITHSUNDEEP
c#asp.netexcelepplus
galiolio
galiolio

Reputation: 275

Create a dropdownList of checkboxes with EPPLUS Core in Asp Core 1.0

I managed to add a dropdown populated with values in an other sheet, but it allows me to only select one value.

I have a case I need to select more than one value using DataValidation in EPPLUS.

How could I add for exemple a list of checkboxes instead of a simple dropdown ?

I tried

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

But I only can select one checkbox, I need to select multiple to have multiple values in an array for example.

So is there any way to make this dropdown of checkboxes to allow me to choose multiple values ?

I've found a tutorial to create it directly in Excel : Tuto

Is it possible to do the same thing with EPPLUS Core?

Thanks

Upvotes: 1

Views: 1183

Answers (1)

Yahya Hussein
Yahya Hussein

Reputation: 9121

You can achieve this 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();
        }

Upvotes: 1

Related Questions