Limitação de Planilha Excel para Validação de Dados de Strings em C
O Microsoft Excel possui uma limitação conhecida para o comprimento de listas de strings ao criar planilha Excel, que estipula que o comprimento total da lista, incluindo separadores como vírgulas, não pode ultrapassar 255 caracteres.
Esse problema ocorre quando você insere manualmente a lista de strings no campo Origem, em vez de selecionar dados de um intervalo existente na planilha do Excel, conforme mostrado abaixo.
Problema de limitação do Excel
Solução para a limitação do Excel no Excel
A solução padrão para essa limitação é criar uma lista de valores em uma coluna ou linha da planilha, em vez de inserir a lista de strings no Campo de Origem. Conforme mostrado abaixo.
Exemplo de solução para limitação do Excel
Em seguida, utilize uma referência de fórmula, por exemplo, C1:C100, na validação de dados e aponte para esse intervalo.
Essa abordagem contorna a limitação, permitindo que você crie uma lista suspensa sem estar restrito ao limite de 255 caracteres.
Problema no IronXL
IronXL usa o método AddStringListRule para aplicar uma lista de strings para validação de dados. No entanto, devido à mesma limitação de caracteres do Excel mencionada anteriormente, o método de salvamento ainda funcionaria. No entanto, é fundamental estar ciente de que, se a lista exceder 255 caracteres, isso poderá causar um problema sério: o Excel poderá travar ao abrir o arquivo. Aqui está um exemplo que ilustra o problema.
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)No código, estamos tentando passar uma lista de strings chamada arrayString como fonte de uma lista de strings para criar uma lista suspensa. Em seguida, tentamos salvá-la como CategoryProductCatalog.xlsx. No entanto, devido a essas limitações, ao abrir o arquivo com o Microsoft Excel, é exibida uma mensagem informando que o arquivo está corrompido e não pode ser aberto.
Solução para IronXL
Para lidar adequadamente com essa limitação do Excel, o IronXL fornece o método AddFormulaListRule, que está disponível na propriedade DataValidations. Este método é semelhante à solução utilizada para trabalhar com o Excel.
Primeiro, escrevemos a lista de valores em um intervalo de células na planilha. Em seguida, use AddFormulaListRule para referenciar esse intervalo como a origem da lista suspensa. Isso contorna a limitação usando os mesmos métodos que aplicamos à solução do Excel, mas, neste caso, de forma programática.
O uso de AddFormulaListRule contorna corretamente a limitação do Excel e garante que o arquivo permaneça legível e estável quando aberto no Excel, mesmo com longas listas de valores.
Exemplo de código
Segue abaixo um exemplo de código adicional que contorna esse problema. Usando a mesma pasta de trabalho, primeiro criamos uma planilha e depois definimos todos os cabeçalhos de coluna. Inserimos a lista de strings do exemplo anterior, bem como novos dados para ilustrar a funcionalidade. Em seguida, chamamos AddFormulaListRule com o intervalo de validação acima. Finalmente, salvamos como validationRuleExample.xlsx. Para garantir que nossa planilha não esteja corrompida e possa ser usada normalmente, nós a abrimos no Excel. Esta etapa é crucial, pois demonstra a implementação bem-sucedida do nosso código.
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.")Arquivos Excel de saída
Como você pode ver, o arquivo Excel está estável e não causa nenhuma falha após o uso de AddFormulaListRule. Para experimentar você mesmo, acesse este link para ver uma demonstração do aplicativo usado neste artigo, juntamente com o arquivo Excel. Essa demonstração permitirá que você veja o processo em ação e entenda como aplicá-lo no seu trabalho.
Ainda está rolando a tela?
Quer provas rápidas? PM > Install-Package IronXL.Excel
executar um exemplo Veja seus dados se transformarem em uma planilha.
Comece GRATUITAMENTE
Comece GRATUITAMENTE
Agende uma demonstração ao vivo gratuita
Não é necessário cartão de crédito nem criação de conta.
