public void Test53965()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
List <IDataValidation> lst = sheet.GetDataValidations(); //<-- works
Assert.AreEqual(0, lst.Count);
//create the cell that will have the validation applied
sheet.CreateRow(0).CreateCell(0);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("SUM($A$1:$A$1) <= 3500");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, addressList);
sheet.AddValidationData(validation);
// this line caused XmlValueOutOfRangeException , see Bugzilla 3965
lst = sheet.GetDataValidations();
Assert.AreEqual(1, lst.Count);
}
finally
{
wb.Close();
}
}
public void TestCreateMultipleRegionsValidation()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateExplicitListConstraint(new string[] { "A" });
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(0, 0, 0, 0);
cellRangeAddressList.AddCellRangeAddress(0, 1, 0, 1);
cellRangeAddressList.AddCellRangeAddress(0, 2, 0, 2);
XSSFDataValidation dataValidation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList) as XSSFDataValidation;
sheet.AddValidationData(dataValidation);
Assert.AreEqual(new CellRangeAddress(0, 0, 0, 0), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[0]);
Assert.AreEqual(new CellRangeAddress(0, 0, 1, 1), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[1]);
Assert.AreEqual(new CellRangeAddress(0, 0, 2, 2), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[2]);
Assert.AreEqual("A1 B1 C1", dataValidation.GetCTDataValidation().sqref);
}
finally
{
wb.Close();
}
}
private XSSFDataValidation CreateValidation(XSSFSheet sheet)
{
//create the cell that will have the validation applied
IRow row = sheet.CreateRow(0);
row.CreateCell(0);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("true");
XSSFDataValidation validation = (XSSFDataValidation)dataValidationHelper.CreateValidation(constraint, new CellRangeAddressList(0, 0, 0, 0));
return(validation);
}
public IDataValidationHelper GetDataValidationHelper()
{
return(_sh.GetDataValidationHelper());
}