public void TestGetDataValidationsAny()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
List <IDataValidation> list = sheet.GetDataValidations();
Assert.AreEqual(0, list.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(ValidationType.ANY,
OperatorType.IGNORED, null, null);
CellRangeAddressList AddressList = new CellRangeAddressList(1, 2, 3, 4);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, AddressList);
validation.EmptyCellAllowed = (/*setter*/ true);
validation.CreateErrorBox("error-title", "error-text");
validation.CreatePromptBox("prompt-title", "prompt-text");
sheet.AddValidationData(validation);
list = sheet.GetDataValidations(); // <-- works
Assert.AreEqual(1, list.Count);
HSSFDataValidation dv = list[(0)] as HSSFDataValidation;
{
CellRangeAddressList regions = dv.Regions;
Assert.AreEqual(1, regions.CountRanges());
CellRangeAddress Address = regions.GetCellRangeAddress(0);
Assert.AreEqual(1, Address.FirstRow);
Assert.AreEqual(2, Address.LastRow);
Assert.AreEqual(3, Address.FirstColumn);
Assert.AreEqual(4, Address.LastColumn);
}
Assert.AreEqual(true, dv.EmptyCellAllowed);
Assert.AreEqual(false, dv.SuppressDropDownArrow);
Assert.AreEqual(true, dv.ShowErrorBox);
Assert.AreEqual("error-title", dv.ErrorBoxTitle);
Assert.AreEqual("error-text", dv.ErrorBoxText);
Assert.AreEqual(true, dv.ShowPromptBox);
Assert.AreEqual("prompt-title", dv.PromptBoxTitle);
Assert.AreEqual("prompt-text", dv.PromptBoxText);
IDataValidationConstraint c = dv.ValidationConstraint;
Assert.AreEqual(ValidationType.ANY, c.GetValidationType());
Assert.AreEqual(OperatorType.IGNORED, c.Operator);
}
public IDataValidation CreateValidation(IDataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList)
{
XSSFDataValidationConstraint constraint1 = (XSSFDataValidationConstraint)constraint;
CT_DataValidation ctDataValidation = new CT_DataValidation();
int validationType = constraint.GetValidationType();
switch (validationType)
{
case 0:
ctDataValidation.type = ST_DataValidationType.none;
break;
case 1:
ctDataValidation.type = ST_DataValidationType.whole;
break;
case 2:
ctDataValidation.type = ST_DataValidationType.@decimal;
break;
case 3:
ctDataValidation.type = ST_DataValidationType.list;
ctDataValidation.formula1 = constraint.Formula1;
break;
case 4:
ctDataValidation.type = ST_DataValidationType.date;
break;
case 5:
ctDataValidation.type = ST_DataValidationType.time;
break;
case 6:
ctDataValidation.type = ST_DataValidationType.textLength;
break;
case 7:
ctDataValidation.type = ST_DataValidationType.custom;
break;
default:
ctDataValidation.type = ST_DataValidationType.none;
break;
}
if (validationType != 0 && validationType != 3)
{
ctDataValidation.@operator = ST_DataValidationOperator.between;
if (XSSFDataValidation.operatorTypeMappings.ContainsKey(constraint.Operator))
{
ctDataValidation.@operator = XSSFDataValidation.operatorTypeMappings[constraint.Operator];
}
if (constraint.Formula1 != null)
{
ctDataValidation.formula1 = constraint.Formula1;
}
if (constraint.Formula2 != null)
{
ctDataValidation.formula2 = constraint.Formula2;
}
}
CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.CellRangeAddresses;
List <string> stringList = new List <string>();
for (int index = 0; index < cellRangeAddresses.Length; ++index)
{
CellRangeAddress cellRangeAddress = cellRangeAddresses[index];
stringList.Add(cellRangeAddress.FormatAsString());
}
ctDataValidation.sqref = stringList;
return((IDataValidation) new XSSFDataValidation(constraint1, cellRangeAddressList, ctDataValidation));
}
/* (non-Javadoc)
* @see NPOI.ss.usermodel.DataValidationHelper#CreateValidation(NPOI.ss.usermodel.DataValidationConstraint, NPOI.ss.util.CellRangeAddressList)
*/
public IDataValidation CreateValidation(IDataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList)
{
XSSFDataValidationConstraint dataValidationConstraint = (XSSFDataValidationConstraint)constraint;
CT_DataValidation newDataValidation = new CT_DataValidation();
int validationType = constraint.GetValidationType();
switch (validationType)
{
case ValidationType.LIST:
newDataValidation.type = (ST_DataValidationType.list);
newDataValidation.formula1 = (constraint.Formula1);
break;
case ValidationType.ANY:
newDataValidation.type = ST_DataValidationType.none;
break;
case ValidationType.TEXT_LENGTH:
newDataValidation.type = ST_DataValidationType.textLength;
break;
case ValidationType.DATE:
newDataValidation.type = ST_DataValidationType.date;
break;
case ValidationType.INTEGER:
newDataValidation.type = ST_DataValidationType.whole;
break;
case ValidationType.DECIMAL:
newDataValidation.type = ST_DataValidationType.@decimal;
break;
case ValidationType.TIME:
newDataValidation.type = ST_DataValidationType.time;
break;
case ValidationType.FORMULA:
newDataValidation.type = ST_DataValidationType.custom;
break;
default:
newDataValidation.type = ST_DataValidationType.none;
break;
}
if (validationType != ValidationType.ANY && validationType != ValidationType.LIST)
{
newDataValidation.@operator = ST_DataValidationOperator.between;
if (XSSFDataValidation.operatorTypeMappings.ContainsKey(constraint.Operator))
{
newDataValidation.@operator = XSSFDataValidation.operatorTypeMappings[constraint.Operator];
}
if (constraint.Formula1 != null)
{
newDataValidation.formula1 = (constraint.Formula1);
}
if (constraint.Formula2 != null)
{
newDataValidation.formula2 = (constraint.Formula2);
}
}
CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.CellRangeAddresses;
string sqref = string.Empty;
for (int i = 0; i < cellRangeAddresses.Length; i++)
{
CellRangeAddress cellRangeAddress = cellRangeAddresses[i];
if (sqref.Length == 0)
{
sqref = cellRangeAddress.FormatAsString();
}
else
{
sqref = " " + cellRangeAddress.FormatAsString();
}
}
newDataValidation.sqref = sqref;
newDataValidation.allowBlank = (true);
newDataValidation.errorStyle = ST_DataValidationErrorStyle.stop;
return(new XSSFDataValidation(dataValidationConstraint, cellRangeAddressList, newDataValidation));
}
/* (non-Javadoc)
* @see NPOI.ss.usermodel.DataValidationHelper#CreateValidation(NPOI.ss.usermodel.DataValidationConstraint, NPOI.ss.util.CellRangeAddressList)
*/
public IDataValidation CreateValidation(IDataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList)
{
XSSFDataValidationConstraint dataValidationConstraint = (XSSFDataValidationConstraint)constraint;
CT_DataValidation newDataValidation = new CT_DataValidation();
int validationType = constraint.GetValidationType();
switch (validationType)
{
case ValidationType.LIST:
newDataValidation.type = (ST_DataValidationType.list);
newDataValidation.formula1 = (constraint.Formula1);
break;
case ValidationType.ANY:
newDataValidation.type = ST_DataValidationType.none;
break;
case ValidationType.TEXT_LENGTH:
newDataValidation.type = ST_DataValidationType.textLength;
break;
case ValidationType.DATE:
newDataValidation.type = ST_DataValidationType.date;
break;
case ValidationType.INTEGER:
newDataValidation.type = ST_DataValidationType.whole;
break;
case ValidationType.DECIMAL:
newDataValidation.type = ST_DataValidationType.@decimal;
break;
case ValidationType.TIME:
newDataValidation.type = ST_DataValidationType.time;
break;
case ValidationType.FORMULA:
newDataValidation.type = ST_DataValidationType.custom;
break;
default:
newDataValidation.type = ST_DataValidationType.none;
break;
}
if (validationType != ValidationType.ANY && validationType != ValidationType.LIST)
{
newDataValidation.@operator = XSSFDataValidation.operatorTypeMappings[constraint.Operator];
if (constraint.Formula1 != null)
{
newDataValidation.formula1 = (constraint.Formula1);
}
if (constraint.Formula2 != null)
{
newDataValidation.formula2 = (constraint.Formula2);
}
}
CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.CellRangeAddresses;
List <String> sqref = new List <String>();
for (int i = 0; i < cellRangeAddresses.Length; i++)
{
CellRangeAddress cellRangeAddress = cellRangeAddresses[i];
sqref.Add(cellRangeAddress.FormatAsString());
}
newDataValidation.sqref = (sqref);
return(new XSSFDataValidation(dataValidationConstraint, cellRangeAddressList, newDataValidation));
}
/* (non-Javadoc)
* @see NPOI.ss.usermodel.DataValidationHelper#CreateValidation(NPOI.ss.usermodel.DataValidationConstraint, NPOI.ss.util.CellRangeAddressList)
*/
public IDataValidation CreateValidation(IDataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList)
{
XSSFDataValidationConstraint dataValidationConstraint = (XSSFDataValidationConstraint)constraint;
CT_DataValidation newDataValidation = new CT_DataValidation();
int validationType = constraint.GetValidationType();
switch (validationType)
{
case ValidationType.LIST:
newDataValidation.type = (ST_DataValidationType.list);
newDataValidation.formula1 = (constraint.Formula1);
break;
case ValidationType.ANY:
newDataValidation.type = ST_DataValidationType.none;
break;
case ValidationType.TEXT_LENGTH:
newDataValidation.type = ST_DataValidationType.textLength;
break;
case ValidationType.DATE:
newDataValidation.type = ST_DataValidationType.date;
break;
case ValidationType.INTEGER:
newDataValidation.type = ST_DataValidationType.whole;
break;
case ValidationType.DECIMAL:
newDataValidation.type = ST_DataValidationType.@decimal;
break;
case ValidationType.TIME:
newDataValidation.type = ST_DataValidationType.time;
break;
case ValidationType.FORMULA:
newDataValidation.type = ST_DataValidationType.custom;
break;
default:
newDataValidation.type = ST_DataValidationType.none;
break;
}
if (validationType != ValidationType.ANY && validationType != ValidationType.LIST)
{
newDataValidation.@operator = ST_DataValidationOperator.between;
if(XSSFDataValidation.operatorTypeMappings.ContainsKey(constraint.Operator))
newDataValidation.@operator = XSSFDataValidation.operatorTypeMappings[constraint.Operator];
if (constraint.Formula1 != null)
{
newDataValidation.formula1 = (constraint.Formula1);
}
if (constraint.Formula2 != null)
{
newDataValidation.formula2 = (constraint.Formula2);
}
}
CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.CellRangeAddresses;
List<String> sqref = new List<String>();
for (int i = 0; i < cellRangeAddresses.Length; i++)
{
CellRangeAddress cellRangeAddress = cellRangeAddresses[i];
sqref.Add(cellRangeAddress.FormatAsString());
}
newDataValidation.sqref = sqref;
return new XSSFDataValidation(dataValidationConstraint, cellRangeAddressList, newDataValidation);
}