Ograniczenie Excel dotyczące walidacji danych dla ciągów

Zaktualizowano:19 sierpnia, 2025

Translated

View the article in English

Microsoft Excel ma znane ograniczenie dotyczące długości list ciągów, które mówi, że całkowita długość listy, w tym separatory takie jak przecinki, nie może przekraczać 255 znaków.

Ten problem występuje, gdy ręcznie wprowadzisz listę ciągów w polu Źródło, zamiast wybierać dane z istniejącego zakresu w arkuszu Excel, jak pokazano poniżej.

Problem z ograniczeniem Excel

Rozwiązanie dla ograniczenia w Excel

Standardowym obejściem tego ograniczenia jest utworzenie listy wartości w kolumnie lub wierszu w arkuszu, zamiast wpisywania listy ciągów w polu Źródło. Jak pokazano poniżej.

Przykład rozwiązania ograniczenia Excel

Następnie użyj odwołania do formuły, na przykład C1:C100, w walidacji danych i wskaż na ten zakres.

To podejście omija ograniczenia, umożliwiając utworzenie listy rozwijanej bez ograniczenia do 255 znaków.

Problem w IronXL

IronXL używa metody AddStringListRule do zastosowania listy ciągów do walidacji danych. Jednakże, z powodu tego samego ograniczenia znaków w Excel, o którym wspomniano powyżej, metoda zapisu nadal będzie działać. Jednakże, ważne jest, aby być świadomym, że jeśli lista przekroczy 255 znaków, może to prowadzić do poważnego problemu: zawieszenia się Excel przy otwieraniu pliku. Oto przykład ilustrujący problem.

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() = 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 separator 257 (230 char + 26 separator [,] )

' 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

W kodzie próbujemy przekazać listę ciągów o nazwie arrayString jako źródło listy ciągów do stworzenia listy rozwijanej. Następnie próbujemy zapisać to jako CategoryProductCatalog.xlsx. Jednak, z powodu tych ograniczeń, podczas otwierania pliku w Microsoft Excel, wyświetla się wiadomość, że plik jest uszkodzony i nie można go otworzyć.

tekst alternatywny

Rozwiązanie dla IronXL

Aby właściwie zająć się tym ograniczeniem Excel, IronXL zapewnia metodę AddFormulaListRule, która jest dostępna z właściwości DataValidations. Ta metoda jest podobna do rozwiązania dla pracy z Excel.

Najpierw zapisujemy listę wartości w zakresie komórek na arkuszu. Następnie używamy AddFormulaListRule, aby odnieść się do tego zakresu jako źródła listy rozwijanej. Efektywnie omijając ograniczenia tymi samymi metodami, które zastosowaliśmy w rozwiązaniu dla Excel, ale w tym przypadku jest to programistyczne.

Użycie AddFormulaListRule właściwie omija ograniczenia Excel, ale także zapewnia, że plik pozostaje zarówno czytelny, jak i stabilny podczas otwierania w Excel, nawet z długimi listami wartości.

Przykład kodu

Poniżej znajduje się dodatkowy kod przykładowy, który omija ten problem. Używając tego samego skoroszytu, najpierw tworzymy arkusz roboczy, a następnie definiujemy wszystkie nagłówki kolumn. Wprowadzamy listę ciągów z poprzedniego przykładu, a także nowe dane, aby zilustrować funkcjonalność. Następnie wywołujemy AddFormulaListRule z zakresem walidacji z powyższego przykładu. Na koniec zapisujemy to jako validationRuleExample.xlsx. Aby zapewnić, że nasz skoroszyt nie jest uszkodzony i można go normalnie używać, otwieramy go w Excel. Ten krok jest kluczowy, ponieważ pokazuje udaną implementację naszego kodu.

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() = 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 separator 257 (230 char + 26 separator [,] )

'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

' 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

Wyjściowe pliki Excel

Jak widać, plik Excel jest stabilny i nie powoduje żadnych awarii po użyciu AddFormulaListRule. Aby przetestować to samodzielnie, zapoznaj się z tym linkiem do dema aplikacji użytej w tym artykule, wraz z plikiem Excel. To demo pozwoli ci zobaczyć proces w akcji i zrozumieć, jak zastosować go w swojej pracy.

Curtis Chau

Czat z zespołem inżynierów teraz

Autor tekstów technicznych

Curtis Chau posiada tytuł licencjata z informatyki (Uniwersytet Carleton) i specjalizuje się w front-endowym rozwoju, z ekspertką w Node.js, TypeScript, JavaScript i React. Pasjonuje się tworzeniem intuicyjnych i estetycznie przyjemnych interfejsów użytkownika, Curtis cieszy się pracą z nowoczesnymi frameworkami i tworzeniem dobrze zorganizowanych, atrakcyjnych wizualnie podrę...

Czytaj więcej

Gotowy, aby rozpocząć?

Nuget Pliki do pobrania 1,950,735 | Wersja: 2026.4 just released