例外：DataValidation列表的总长度不能超过255个字符 [英] Exception:The total length of a DataValidation list cannot exceed 255 characters

问题描述

Am试图在epplus中动态创建公式字段。如果公式字段包含的字符少于255个，则说明它创建正确。如果超过255
，则抛出异常，因为
异常：DataValidation列表的总长度不能超过255个字符。

Am trying to create a formula field dynamically in epplus. If formula field contains less than 255 character then it is creating properly. If it exceeds 255 then it is throwing a exception as Exception:The total length of a DataValidation list cannot exceed 255 characters.

有人可以帮助我解决此问题吗？或请告诉我一些替代方法。

Can any one please help me to solve this issue? or please tell me some alternatives.

推荐答案

问题是您正在使用公式该单元格的容器，用于存储所有可用的列表选项-基本上是CSV列表。在Excel中，硬限制为255个字符。您可以通过在创建新的验证列表时进入excel并在源框中手动输入用逗号分隔的值来看到这一点。

The problem is you are using the Formulacontainer of that cell to store all of the available list options - basically a CSV list. That has a hard limit of 255 characters in Excel. You can see this by going into excel and manually entering values separated by commas right in the "Source" box when creating a new Validation List.

您最好的选择是在单元格中填充值，然后将值的范围提供给公式。像这样：

Your best option may be to populate the values in cells and give the range of the values to the Formula instead. Like this:

using (var pack = new ExcelPackage(existingFile))
{

    var ws = pack.Workbook.Worksheets.Add("Content");

    //var val = ws.DataValidations.AddListValidation("A1"); 
    //val.Formula.Values.Add("Here we have to add long text");
    //val.Formula.Values.Add("All list values combined have to have more then 255 chars");
    //val.Formula.Values.Add("more text 1 more text more text more text"); 
    //val.Formula.Values.Add("more text 2 more text more text more text"); 

    ws.Cells["B1"].Value = "Here we have to add long text";
    ws.Cells["B2"].Value = "All list values combined have to have more then 255 chars";
    ws.Cells["B3"].Value = "more text 1 more text more text more text";
    ws.Cells["B4"].Value = "more text 2 more text more text more text";
    ws.Cells["B5"].Value = "more text 2 more text more text more textmore text 2 more text more text more textmore text 2 more text more text more textmore text 2 more text more text more textmore text 2 more text more text more textmore text 2 more text more text more textmore text 2 more text more text more textmore";

    var val = ws.DataValidations.AddListValidation("A1");
    val.Formula.ExcelFormula = "B1:B5";

    pack.SaveAs(existingFile);
}

这篇关于例外：DataValidation列表的总长度不能超过255个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！