Limitación de Excel con respecto a la validación de datos para cadenas
Microsoft Excel tiene una limitación conocida respecto a la longitud de las listas de cadenas, que establece que la longitud total de la lista, incluidos los separadores como las comas, no puede superar los 255 caracteres.
Este problema ocurre cuando introduces manualmente la lista de cadenas en el campo Fuente, en lugar de seleccionar datos de un rango existente en la hoja de Excel, como se muestra a continuación.
Problema de limitación de Excel
Solución para la limitación de Excel
La solución estándar para esta limitación es crear una lista de valores en una columna o fila dentro de la hoja de trabajo, en lugar de ingresar la lista de cadenas en el campo Fuente. Como se muestra a continuación.
Ejemplo de solución de limitación de Excel
Después, utiliza una referencia de fórmula, por ejemplo, C1:C100, en la validación de datos y apunta a ese rango.
Este enfoque elude la limitación, permitiéndote crear una lista desplegable sin estar restringido por el límite de 255 caracteres.
Problema en IronXL
IronXL utiliza el método AddStringListRule para aplicar una lista de cadenas para la validación de datos. Sin embargo, debido a la misma limitación de caracteres en Excel mencionada, el método de guardado seguiría funcionando. No obstante, es esencial ser consciente de que si la lista supera los 255 caracteres, podría generar un problema grave: que Excel se bloquee al abrir el archivo. Aquí tienes un ejemplo que ilustra el 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)Dentro del código, estamos intentando pasar una lista de cadenas llamada arrayString como la fuente de una lista de cadenas para crear una lista desplegable. Luego intentamos guardarla como CategoryProductCatalog.xlsx. Sin embargo, debido a estas limitaciones, al abrir el archivo con Microsoft Excel, muestra un mensaje diciendo que el archivo está dañado y no se puede abrir.
Solución para IronXL
Para abordar adecuadamente esta limitación de Excel, IronXL proporciona el método AddFormulaListRule, que está disponible desde la propiedad DataValidations. Este método es similar a la solución para trabajar con Excel.
Primero escribimos la lista de valores en un rango de celdas en la hoja de trabajo. Luego usamos AddFormulaListRule para referenciar ese rango como la fuente de la lista desplegable. Este método elude efectivamente la limitación mediante el mismo método que utilizamos para la solución de Excel, pero en este caso, es programático.
Usar AddFormulaListRule elude adecuadamente la limitación de Excel, pero también asegura que el archivo permanezca tanto legible como estable cuando se abre en Excel, incluso con listas largas de valores.
Ejemplo de código
A continuación se encuentra el código de ejemplo adicional que elude este problema. Usando el mismo libro de trabajo, primero creamos una hoja de trabajo y luego definimos todos los encabezados de columna. Ingresamos la lista de cadenas del ejemplo anterior, así como nuevos datos para ilustrar la funcionalidad. Luego llamamos a AddFormulaListRule con el rango de validación mencionado anteriormente. Finalmente, lo guardamos como validationRuleExample.xlsx. Para asegurarnos de que nuestro libro de trabajo no esté dañado y se pueda usar normalmente, lo abrimos en Excel. Este paso es crucial ya que demuestra la implementación exitosa de nuestro 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.")Archivos de Excel de salida
Como puedes ver, el archivo de Excel es estable y no causa bloqueos después de usar AddFormulaListRule. Para probarlo por ti mismo, consulta este enlace para una demostración de la aplicación utilizada en este artículo, junto con el archivo de Excel. Esta demostración te permitirá ver el proceso en acción y comprender cómo aplicarlo en tu trabajo.
Comience gratis
Comience gratis
Reserve demostración en vivo gratis
No se requiere tarjeta de crédito ni creación de cuenta
