Cell Validation fails while dynamically binding the dropdown list to a cell using Aspose C#

Question

I have created an extension method to bind 2 different dropdownlist to specific cells using aspose. First dropdown is working fine but while binding the second dropdown, it throws error as "Excel was able to open the file by repairing or removing the unreadable content". Inside BindNOP() method you can see there is list of string,of size =25 , I tried commenting the list by reducing the size till 7 string values, then it started binding. Can anyone guide what is the mistake that is done in the code which is causing excel file to break. Thank you in advance.

    public static void BindRemarks(this Worksheet worksheet)
    {
        List<string> secList = new List<string>();
        secList.Add("Certificate cancelled due to revision request");
        secList.Add("Certificate modified due to revision request");
        secList.Add("New Certificate issued due to revision request");
        var flatsecList = string.Join(",", secList.ToArray());
        var validations = worksheet.Validations;
        Validation validation = validations[validations.Add()];
        validation.Type = Aspose.Cells.ValidationType.List;
        validation.Operator = OperatorType.Between;
        validation.InCellDropDown = true;
        validation.Formula1 = flatsecList;
        validation.ShowError = true;
        validation.AlertStyle = ValidationAlertType.Stop;
        validation.ErrorTitle = "Invalid Remark Error";
        validation.ErrorMessage = "Please select Remark from the drop down";

        //Specify the validation area O1
        CellArea areaRemarks;
        areaRemarks.StartRow = 1; areaRemarks.EndRow = 10;
        areaRemarks.StartColumn = 14; areaRemarks.EndColumn = 14;

        //Add the validation area
        validation.AddArea(areaRemarks);
        worksheet.Cells.SetColumnWidth(14, 40);
    }

    public static void BindNOP(this Worksheet worksheet)
    {
        List<string> nopList = new List<string>();
        nopList.Add("Interest on securities");
        nopList.Add("Dividends");
        nopList.Add("Interest other than Interest on securities");
        nopList.Add("Payments to contractors");
        nopList.Add("Insurance Commission");
        nopList.Add("Commission, etc., on the sale of lottery tickets");
        nopList.Add("Commission / Brokerage");
        nopList.Add("Rent - Plant / Machinery / equipment");
        //nopList.Add("Rent - Land and Building / furniture / fittings");
        //nopList.Add("Rent - Land and Building / furniture / fittings");
        //nopList.Add("Fee for technical services");
        //nopList.Add("Fees for professional services and others");
        //nopList.Add("Income in respect of units");
        //nopList.Add("Payment of compensation on acquisition of certain immovable property");
        //nopList.Add("Income referred to in Clause (a) of section 10(23FC) from units of a business trust");
        //nopList.Add("Income in respect of units of investment fund");
        //nopList.Add("Income in respect of investment in securitization trust");
        //nopList.Add("Payment of certain sums by certain individuals or Hindu undivided family");
        //nopList.Add("Payment of certain sums by e-commerce operator to e-commerce participant");
        //nopList.Add("Long-term capital gains referred in section 115E or sub-clause (iii) of clause (c) of sub-section (1) of section 112");
        //nopList.Add("Long-term capital gains referred to in section 112A");
        //nopList.Add("Short Term Capital Gain");
        //nopList.Add("Interest Payment");
        //nopList.Add("Royalty");
        //nopList.Add("Other Income");
        var flatnopList = string.Join(",", nopList.ToArray());
        var validations = worksheet.Validations;
        Validation validation = validations[validations.Add()];
        validation.Type = Aspose.Cells.ValidationType.List;
        validation.Operator = OperatorType.Between;
        validation.InCellDropDown = true;
        validation.Formula1 = flatnopList;
        validation.ShowError = true;
        validation.AlertStyle = ValidationAlertType.Stop;
        validation.ErrorTitle = "Invalid NOP Error";
        validation.ErrorMessage = "Please select NOP from the drop down";

        //Specify the validation area H1
        CellArea areaNOP;
        areaNOP.StartRow = 1; areaNOP.EndRow = 20;
        areaNOP.StartColumn = 7; areaNOP.EndColumn = 7;

        //Add the validation area
        validation.AddArea(areaNOP);
        worksheet.Cells.SetColumnWidth(7, 40);
    }

Answer 1

This is MS Excel's limitation regarding List data validation feature and nothing to do with Aspose.Cells APIs. Please note, there are limits on number of items to be shown in a data validation drop down list:

1. You can show upto 32,767 items if the list items come from worksheet cells.
2. When you paste the items directly into the data validation's Source in the validation's dialog box, the limit is 256 characters only including the separators (e.g., "," for your case).

For your case, 2) is true. That's why when you try to add 8th (zero-based) List item, it corrupts the output Excel file and no List data validation is added.

You should insert/paste your long data list into worksheet cells first and then try setting that Source range for your List validation, it will work fine. See the document on list data validation for your reference.

You may also post your queries in the dedicated forums.

PS. I am working as Support developer/ Evangelist at Aspose.