CODEWITHSUNDEEP
c#excelexport-to-excelepplus
Mohammad Zargarani
Mohammad Zargarani

Reputation: 1422

Epplus number of drop down items limitation in excel file

Consider this code:

var dropDown = sheet.DataValidations.AddListValidation(cells[2, colIndex, maxCol, colIndex].Address);
                foreach (var bb in brokerBranchs)
               {
                    dropDown.Formula.Values.Add(bb.Title);
                }

With 29 of dropDown items everything is ok and created excel file works fine but as the number of items exceeds 29, opening created file shows following error: enter image description here Opening corrupted result file discards all drop down items associated with all columns. What is possible solution to this problem? Any help will be appreciated.

Upvotes: 6

Views: 5796

Answers (1)

Mohammad Zargarani
Mohammad Zargarani

Reputation: 1422

you must add new sheet to add drop down item insert to this sheet

 ExcelWorksheet ddList = excelPackage.Workbook.Worksheets.Add("DropDownList");

now add data to ddList in first column

 var brokerBranchs = accountingUnitOfWork.BrokerServiceAccessor.GetBrokerBranchByBrokerId(firmId).OrderBy(x => x.Title).ToList();
            var val = sheet.DataValidations.AddListValidation(cells[2, colIndex, maxCol, colIndex].Address);
            for (int index = 1; index <= brokerBranchs.Count; index++)
            {
                ddList.Cells[index, 1].Value = brokerBranchs[index - 1].Title;
            }

now create address for use in formula

var address = ddList.Cells[1, 1, brokerBranchs.Count(), 1].Address.ToString();
var arr = address.Split(':');
var char1 = arr[0][0];
var num1 = arr[0].Trim(char1);
var char2 = arr[1][0];
var num2 = arr[1].Trim(char2);

now use address in formula 

val.Formula.ExcelFormula = string.Format("=DropDownList!${0}${1}:${2}${3}", char1,          num1, char2, num2);
val.ShowErrorMessage = true;
val.Error = "Select from List of Values ...";

Upvotes: 6

Related Questions