c# - 异常 :The total length of a DataValidation list cannot exceed 255 characters

Question

我正在尝试在 epplus 中动态创建公式字段。如果公式字段包含少于 255 个字符，则它正在正确创建。如果超过 255然后它抛出一个异常异常(exception):DataValidation 列表的总长度不能超过 255 个字符。

谁能帮我解决这个问题？或者请告诉我一些替代方案。

Answer 1

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

您最好的选择可能是填充单元格中的值，并将值的范围提供给公式。像这样:

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);
}