Excel在字符串数据验证方面的局限性（XLSX文件操作注意事项）
This article was translated from English: Does it need improvement?
Translated
View the article in English
Microsoft Excel 对字符串列表的长度有一个已知的限制，即列表的总长度（包括逗号等分隔符）不能超过 255 个字符。
当您在源字段中手动输入字符串列表，而不是从 Excel 表格中的现有范围中选择数据时，就会出现此问题，如下所示。
Excel 限制问题
解决 Excel 限制问题
针对此限制的标准解决方法是在工作表的列或行中创建值列表，而不是在源字段中输入字符串列表。 如下所示。
Excel 局限性解决方案示例
之后，在数据验证中使用公式引用，例如 C1:C100，并指向该范围。
这种方法可以绕过限制，让你创建下拉列表而不受 255 个字符的限制。
IronXL 出现问题
IronXL使用方法 AddStringListRule 来应用字符串列表进行数据验证。 但是，由于上述 Excel 中的字符限制，保存方法仍然有效。 但是，需要注意的是，如果列表超过 255 个字符，则可能会导致严重问题：打开文件时 Excel 崩溃。 下面举个例子来说明这个问题。
using IronXL;
using IronXL.DataValidations;
// Set your license key here if you have one
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY";
var workBook = WorkBook.Create(ExcelFileFormat.XLSX);
var sheet = workBook.CreateWorkSheet("Validation Rule Example using");
// Apply rules from row 2 down to row 500
var validationRangeStart = "2";
var validationRangeFinish = "500";
// --- 1. Define All Column Headers ---
sheet["A1"].Value = "String List Test";
sheet["B1"].Value = "Formula List Test";
sheet["A1:B1"].Style.Font.Bold = true;
// 256
string[] arrayString = new string[]
{
"SKU-A01-R", "SKU-A02-R", "SKU-A03-R", "SKU-A04-R", "SKU-A05-R",
"SKU-B01-G", "SKU-B02-G", "SKU-B03-G", "SKU-B04-G", "SKU-B05-G",
"SKU-C01-B", "SKU-C02-B", "SKU-C03-B", "SKU-C04-B", "SKU-C05-B",
"SKU-D01-Y", "SKU-D02-Y", "SKU-D03-Y", "SKU-D04-Y", "SKU-D05-Y",
"END-001", "END-002", "END-003", "END-004", "END-005", "END-010", "END-001"
}; // total char with seperator 257 (230 char + 26 sperator [,] )
// no problem when write to a file
var skuValidation = sheet.DataValidations.AddStringListRule(sheet[$"A{validationRangeStart}:A{validationRangeFinish}"].RangeAddress, arrayString);
// set error box
skuValidation.ShowErrorBox = true;
skuValidation.ErrorBoxTitle = "Invalid SKU";
skuValidation.ErrorBoxText = "Please select a SKU from the list.";
skuValidation.ErrorAlert = ErrorAlert.Stop;
//attempt to save the file
var fileName = "CategoryProductCatalog.xlsx";
workBook.SaveAs(fileName);using IronXL;
using IronXL.DataValidations;
// Set your license key here if you have one
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY";
var workBook = WorkBook.Create(ExcelFileFormat.XLSX);
var sheet = workBook.CreateWorkSheet("Validation Rule Example using");
// Apply rules from row 2 down to row 500
var validationRangeStart = "2";
var validationRangeFinish = "500";
// --- 1. Define All Column Headers ---
sheet["A1"].Value = "String List Test";
sheet["B1"].Value = "Formula List Test";
sheet["A1:B1"].Style.Font.Bold = true;
// 256
string[] arrayString = new string[]
{
"SKU-A01-R", "SKU-A02-R", "SKU-A03-R", "SKU-A04-R", "SKU-A05-R",
"SKU-B01-G", "SKU-B02-G", "SKU-B03-G", "SKU-B04-G", "SKU-B05-G",
"SKU-C01-B", "SKU-C02-B", "SKU-C03-B", "SKU-C04-B", "SKU-C05-B",
"SKU-D01-Y", "SKU-D02-Y", "SKU-D03-Y", "SKU-D04-Y", "SKU-D05-Y",
"END-001", "END-002", "END-003", "END-004", "END-005", "END-010", "END-001"
}; // total char with seperator 257 (230 char + 26 sperator [,] )
// no problem when write to a file
var skuValidation = sheet.DataValidations.AddStringListRule(sheet[$"A{validationRangeStart}:A{validationRangeFinish}"].RangeAddress, arrayString);
// set error box
skuValidation.ShowErrorBox = true;
skuValidation.ErrorBoxTitle = "Invalid SKU";
skuValidation.ErrorBoxText = "Please select a SKU from the list.";
skuValidation.ErrorAlert = ErrorAlert.Stop;
//attempt to save the file
var fileName = "CategoryProductCatalog.xlsx";
workBook.SaveAs(fileName);Imports IronXL
Imports IronXL.DataValidations
' Set your license key here if you have one
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY"
Dim workBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim sheet = workBook.CreateWorkSheet("Validation Rule Example using")
' Apply rules from row 2 down to row 500
Dim validationRangeStart = "2"
Dim validationRangeFinish = "500"
' --- 1. Define All Column Headers ---
sheet("A1").Value = "String List Test"
sheet("B1").Value = "Formula List Test"
sheet("A1:B1").Style.Font.Bold = True
' 256
Dim arrayString() As String = { "SKU-A01-R", "SKU-A02-R", "SKU-A03-R", "SKU-A04-R", "SKU-A05-R", "SKU-B01-G", "SKU-B02-G", "SKU-B03-G", "SKU-B04-G", "SKU-B05-G", "SKU-C01-B", "SKU-C02-B", "SKU-C03-B", "SKU-C04-B", "SKU-C05-B", "SKU-D01-Y", "SKU-D02-Y", "SKU-D03-Y", "SKU-D04-Y", "SKU-D05-Y", "END-001", "END-002", "END-003", "END-004", "END-005", "END-010", "END-001" }
' no problem when write to a file
Dim skuValidation = sheet.DataValidations.AddStringListRule(sheet($"A{validationRangeStart}:A{validationRangeFinish}").RangeAddress, arrayString)
' set error box
skuValidation.ShowErrorBox = True
skuValidation.ErrorBoxTitle = "Invalid SKU"
skuValidation.ErrorBoxText = "Please select a SKU from the list."
skuValidation.ErrorAlert = ErrorAlert.Stop
'attempt to save the file
Dim fileName = "CategoryProductCatalog.xlsx"
workBook.SaveAs(fileName)
$vbLabelText
$csharpLabel
在代码中，我们尝试将名为 arrayString 的字符串列表作为字符串列表的源，以创建一个下拉列表。然后，我们尝试将其保存为 CategoryProductCatalog.xlsx。 但是，由于这些限制，当使用 Microsoft Excel 打开该文件时，会显示一条消息，指出该文件已损坏，无法打开。
IronXL 的解决方案
为了妥善解决 Excel 的这一限制， IronXL提供了 AddFormulaListRule 方法，该方法可通过 DataValidations 属性获得。 这种方法与使用 Excel 的解决方案类似。
我们首先将数值列表写入工作表上的一系列单元格中。 然后使用 AddFormulaListRule 将该范围引用为下拉列表的数据源。这有效地绕过了限制，方法与我们对 Excel 解决方案所做的相同，但这次是通过编程实现的。
使用 AddFormulaListRule 可以正确规避 Excel 的限制，同时还能确保文件在 Excel 中打开时保持可读性和稳定性，即使包含很长的值列表。
代码示例
以下是规避此问题的其他示例代码。 在同一个工作簿中，我们首先创建一个工作表，然后定义所有列标题。 我们输入上一个示例中的字符串列表，以及新的数据来演示该功能。 然后我们调用 AddFormulaListRule，并使用上面的验证范围。 最后我们将其保存为 validationRuleExample.xlsx。 为了确保工作簿没有损坏且可以正常使用，我们在 Excel 中打开它。 这一步骤至关重要，因为它证明了我们的代码已成功实现。
using IronXL;
using IronXL.DataValidations;
using System;
// Set your license key here if you have one
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY";
var workBook = WorkBook.Create(ExcelFileFormat.XLSX);
var sheet = workBook.CreateWorkSheet("Validation Rule Example using");
// Apply rules from row 2 down to row 500
var validationRangeStart = "2";
var validationRangeFinish = "500";
// --- 1. Define All Column Headers ---
sheet["A1"].Value = "String List Test";
sheet["B1"].Value = "Formula List Test";
sheet["A1:B1"].Style.Font.Bold = true;
string[] arrayString2 = new string[]
{
"SKU-A01-R", "SKU-A02-R", "SKU-A03-R", "SKU-A04-R", "SKU-A05-R",
"SKU-B01-G", "SKU-B02-G", "SKU-B03-G", "SKU-B04-G", "SKU-B05-G",
"SKU-C01-B", "SKU-C02-B", "SKU-C03-B", "SKU-C04-B", "SKU-C05-B",
"SKU-D01-Y", "SKU-D02-Y", "SKU-D03-Y", "SKU-D04-Y", "SKU-D05-Y",
"END-001", "END-002", "END-003", "END-004", "END-005", "END-010", "END-001",
"IRON01","IRON02","IRON03","IRON04","IRON05","IRON06","IRON07","IRON08","IRON09","IRON10",
"IRON11","IRON12","IRON13","IRON14","IRON15","IRON16","IRON17","IRON18","IRON19","IRON20",
"IRON21","IRON22","IRON23","IRON24","IRON25","IRON26","IRON27","IRON28","IRON29","IRON30",
"IRON31","IRON32","IRON33","IRON34","IRON35","IRON36","IRON37","IRON38","IRON39","IRON40",
"IRON41","IRON42","IRON43","IRON44","IRON45","IRON46","IRON47","IRON48","IRON49","IRON50",
"IRON51","IRON52","IRON53","IRON54","IRON55","IRON56","IRON57","IRON58","IRON59","IRON60",
"IRON61","IRON62","IRON63","IRON64","IRON65","IRON66","IRON67","IRON68","IRON69","IRON70",
"IRON71","IRON72","IRON73","IRON74","IRON75","IRON76","IRON77","IRON78","IRON79","IRON80",
"IRON81","IRON82","IRON83","IRON84","IRON85","IRON86","IRON87","IRON88","IRON89","IRON90",
"IRON91","IRON92","IRON93","IRON94","IRON95","IRON96","IRON97","IRON98","IRON99","IRON100"
}; // total char with seperator 257 (230 char + 26 sperator [,] )
//Write the contents of your array to column A of the data sheet.
for (int i = 0; i < arrayString2.Length; i++)
{
// Note: IronXL row and cell indices are 1-based.
sheet[$"A{i + 1}"].Value = arrayString2[i];
}
// Create the formula string that points to our data range.
// This will look like: 'ValidationData'!$A$1:$A$100
string formula = $"'{sheet.Name}'!$A$1:$A${arrayString2.Length}";
Console.WriteLine($"Generated formula for validation: {formula}");
var sku2Validation = sheet.DataValidations.AddFormulaListRule(sheet[$"B{validationRangeStart}:B{validationRangeFinish}"], formula);
sku2Validation.ShowErrorBox = true;
sku2Validation.ErrorBoxTitle = "Invalid SKU2";
sku2Validation.ErrorBoxText = "Please select a SKU from the list.";
sku2Validation.ErrorAlert = ErrorAlert.Stop;
// --- Final Touches ---
sheet.AutoSizeColumn(0, true);
sheet.AutoSizeColumn(1, true);
var fileName = "validationRuleExample.xlsx";
workBook.SaveAs(fileName);
Console.WriteLine($"Successfully created '{fileName}' with 2 validation rules example.");using IronXL;
using IronXL.DataValidations;
using System;
// Set your license key here if you have one
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY";
var workBook = WorkBook.Create(ExcelFileFormat.XLSX);
var sheet = workBook.CreateWorkSheet("Validation Rule Example using");
// Apply rules from row 2 down to row 500
var validationRangeStart = "2";
var validationRangeFinish = "500";
// --- 1. Define All Column Headers ---
sheet["A1"].Value = "String List Test";
sheet["B1"].Value = "Formula List Test";
sheet["A1:B1"].Style.Font.Bold = true;
string[] arrayString2 = new string[]
{
"SKU-A01-R", "SKU-A02-R", "SKU-A03-R", "SKU-A04-R", "SKU-A05-R",
"SKU-B01-G", "SKU-B02-G", "SKU-B03-G", "SKU-B04-G", "SKU-B05-G",
"SKU-C01-B", "SKU-C02-B", "SKU-C03-B", "SKU-C04-B", "SKU-C05-B",
"SKU-D01-Y", "SKU-D02-Y", "SKU-D03-Y", "SKU-D04-Y", "SKU-D05-Y",
"END-001", "END-002", "END-003", "END-004", "END-005", "END-010", "END-001",
"IRON01","IRON02","IRON03","IRON04","IRON05","IRON06","IRON07","IRON08","IRON09","IRON10",
"IRON11","IRON12","IRON13","IRON14","IRON15","IRON16","IRON17","IRON18","IRON19","IRON20",
"IRON21","IRON22","IRON23","IRON24","IRON25","IRON26","IRON27","IRON28","IRON29","IRON30",
"IRON31","IRON32","IRON33","IRON34","IRON35","IRON36","IRON37","IRON38","IRON39","IRON40",
"IRON41","IRON42","IRON43","IRON44","IRON45","IRON46","IRON47","IRON48","IRON49","IRON50",
"IRON51","IRON52","IRON53","IRON54","IRON55","IRON56","IRON57","IRON58","IRON59","IRON60",
"IRON61","IRON62","IRON63","IRON64","IRON65","IRON66","IRON67","IRON68","IRON69","IRON70",
"IRON71","IRON72","IRON73","IRON74","IRON75","IRON76","IRON77","IRON78","IRON79","IRON80",
"IRON81","IRON82","IRON83","IRON84","IRON85","IRON86","IRON87","IRON88","IRON89","IRON90",
"IRON91","IRON92","IRON93","IRON94","IRON95","IRON96","IRON97","IRON98","IRON99","IRON100"
}; // total char with seperator 257 (230 char + 26 sperator [,] )
//Write the contents of your array to column A of the data sheet.
for (int i = 0; i < arrayString2.Length; i++)
{
// Note: IronXL row and cell indices are 1-based.
sheet[$"A{i + 1}"].Value = arrayString2[i];
}
// Create the formula string that points to our data range.
// This will look like: 'ValidationData'!$A$1:$A$100
string formula = $"'{sheet.Name}'!$A$1:$A${arrayString2.Length}";
Console.WriteLine($"Generated formula for validation: {formula}");
var sku2Validation = sheet.DataValidations.AddFormulaListRule(sheet[$"B{validationRangeStart}:B{validationRangeFinish}"], formula);
sku2Validation.ShowErrorBox = true;
sku2Validation.ErrorBoxTitle = "Invalid SKU2";
sku2Validation.ErrorBoxText = "Please select a SKU from the list.";
sku2Validation.ErrorAlert = ErrorAlert.Stop;
// --- Final Touches ---
sheet.AutoSizeColumn(0, true);
sheet.AutoSizeColumn(1, true);
var fileName = "validationRuleExample.xlsx";
workBook.SaveAs(fileName);
Console.WriteLine($"Successfully created '{fileName}' with 2 validation rules example.");Imports IronXL
Imports IronXL.DataValidations
Imports System
' Set your license key here if you have one
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY"
Dim workBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim sheet = workBook.CreateWorkSheet("Validation Rule Example using")
' Apply rules from row 2 down to row 500
Dim validationRangeStart = "2"
Dim validationRangeFinish = "500"
' --- 1. Define All Column Headers ---
sheet("A1").Value = "String List Test"
sheet("B1").Value = "Formula List Test"
sheet("A1:B1").Style.Font.Bold = True
Dim arrayString2() As String = {"SKU-A01-R", "SKU-A02-R", "SKU-A03-R", "SKU-A04-R", "SKU-A05-R", "SKU-B01-G", "SKU-B02-G", "SKU-B03-G", "SKU-B04-G", "SKU-B05-G", "SKU-C01-B", "SKU-C02-B", "SKU-C03-B", "SKU-C04-B", "SKU-C05-B", "SKU-D01-Y", "SKU-D02-Y", "SKU-D03-Y", "SKU-D04-Y", "SKU-D05-Y", "END-001", "END-002", "END-003", "END-004", "END-005", "END-010", "END-001", "IRON01", "IRON02", "IRON03", "IRON04", "IRON05", "IRON06", "IRON07", "IRON08", "IRON09", "IRON10", "IRON11", "IRON12", "IRON13", "IRON14", "IRON15", "IRON16", "IRON17", "IRON18", "IRON19", "IRON20", "IRON21", "IRON22", "IRON23", "IRON24", "IRON25", "IRON26", "IRON27", "IRON28", "IRON29", "IRON30", "IRON31", "IRON32", "IRON33", "IRON34", "IRON35", "IRON36", "IRON37", "IRON38", "IRON39", "IRON40", "IRON41", "IRON42", "IRON43", "IRON44", "IRON45", "IRON46", "IRON47", "IRON48", "IRON49", "IRON50", "IRON51", "IRON52", "IRON53", "IRON54", "IRON55", "IRON56", "IRON57", "IRON58", "IRON59", "IRON60", "IRON61", "IRON62", "IRON63", "IRON64", "IRON65", "IRON66", "IRON67", "IRON68", "IRON69", "IRON70", "IRON71", "IRON72", "IRON73", "IRON74", "IRON75", "IRON76", "IRON77", "IRON78", "IRON79", "IRON80", "IRON81", "IRON82", "IRON83", "IRON84", "IRON85", "IRON86", "IRON87", "IRON88", "IRON89", "IRON90", "IRON91", "IRON92", "IRON93", "IRON94", "IRON95", "IRON96", "IRON97", "IRON98", "IRON99", "IRON100"}
'Write the contents of your array to column A of the data sheet.
For i As Integer = 0 To arrayString2.Length - 1
' Note: IronXL row and cell indices are 1-based.
sheet($"A{i + 1}").Value = arrayString2(i)
Next i
' Create the formula string that points to our data range.
' This will look like: 'ValidationData'!$A$1:$A$100
Dim formula As String = $"'{sheet.Name}'!$A$1:$A${arrayString2.Length}"
Console.WriteLine($"Generated formula for validation: {formula}")
Dim sku2Validation = sheet.DataValidations.AddFormulaListRule(sheet($"B{validationRangeStart}:B{validationRangeFinish}"), formula)
sku2Validation.ShowErrorBox = True
sku2Validation.ErrorBoxTitle = "Invalid SKU2"
sku2Validation.ErrorBoxText = "Please select a SKU from the list."
sku2Validation.ErrorAlert = ErrorAlert.Stop
' --- Final Touches ---
sheet.AutoSizeColumn(0, True)
sheet.AutoSizeColumn(1, True)
Dim fileName = "validationRuleExample.xlsx"
workBook.SaveAs(fileName)
Console.WriteLine($"Successfully created '{fileName}' with 2 validation rules example.")
$vbLabelText
$csharpLabel
输出 Excel 文件
如您所见，Excel 文件稳定，在使用 AddFormulaListRule 后不会发生任何崩溃。 要亲自体验，请点击此链接查看本文中使用的应用程序演示以及 Excel 文件。此演示将帮助您了解该流程的实际运行情况，并了解如何将其应用到您的工作中。
准备开始了吗？
Nuget 下载 1,950,735 | 版本: 2026.4 刚刚发布
还在滚动吗？
想快速获得证据？ PM > Install-Package IronXL.Excel
运行示例 观看您的数据变成电子表格。
免费开始
免费开始
预定免费现场演示
无需信用卡或创建账户
