public XSSFDataValidation(XSSFDataValidationConstraint constraint, CellRangeAddressList regions, CT_DataValidation ctDataValidation)
: base()
{
this.validationConstraint = constraint;
this.ctDdataValidation = ctDataValidation;
this.regions = regions;
}
public XSSFDataValidation(XSSFDataValidationConstraint constraint, CellRangeAddressList regions, CT_DataValidation ctDataValidation)
{
this.validationConstraint = constraint;
this.ctDdataValidation = ctDataValidation;
this.regions = regions;
this.ctDdataValidation.errorStyle = ST_DataValidationErrorStyle.stop;
this.ctDdataValidation.allowBlank = true;
}
public XSSFDataValidation(XSSFDataValidationConstraint constraint, CellRangeAddressList regions, CT_DataValidation ctDataValidation)
: base()
{
this.validationConstraint = constraint;
this.ctDdataValidation = ctDataValidation;
this.regions = regions;
}
public XSSFDataValidation(CellRangeAddressList regions, CT_DataValidation ctDataValidation)
: base()
{
this.validationConstraint = GetConstraint(ctDataValidation);
this.ctDdataValidation = ctDataValidation;
this.regions = regions;
this.ctDdataValidation.errorStyle = (ST_DataValidationErrorStyle.stop);
this.ctDdataValidation.allowBlank = (true);
}
public void NamedRangeReference()
{
// named range list
String namedRange = "MyNamedRange";
IDataValidationConstraint constraint = new XSSFDataValidationConstraint(listType, ignoredType, namedRange, null);
Assert.IsNull(constraint.ExplicitListValues);
Assert.AreEqual("MyNamedRange", constraint.Formula1);
}
public void RangeReference()
{
// (unnamed range) reference list
String reference = "A1:A5";
IDataValidationConstraint constraint = new XSSFDataValidationConstraint(listType, ignoredType, reference, null);
Assert.IsNull(constraint.ExplicitListValues);
Assert.AreEqual("A1:A5", constraint.Formula1);
}
public XSSFDataValidation(CellRangeAddressList regions, CT_DataValidation ctDataValidation)
: base()
{
this.validationConstraint = GetConstraint(ctDataValidation);
this.ctDdataValidation = ctDataValidation;
this.regions = regions;
this.ctDdataValidation.errorStyle = (ST_DataValidationErrorStyle.stop);
this.ctDdataValidation.allowBlank = (true);
}
public void ListLiteralsQuotesAreStripped_arrayConstructor()
{
// literal list, using array constructor
String literal = "\"one, two, three\"";
String[] expected = new String[] { "one", "two", "three" };
IDataValidationConstraint constraint = new XSSFDataValidationConstraint(expected);
CollectionAssert.AreEqual(expected, constraint.ExplicitListValues);
// Excel and DataValidationConstraint Parser ignore (strip) whitespace; quotes should still be intact
Assert.AreEqual(literal.Replace(" ", ""), constraint.Formula1);
}
private static XSSFDataValidationConstraint GetConstraint(CT_DataValidation ctDataValidation)
{
XSSFDataValidationConstraint constraint = null;
String formula1 = ctDataValidation.formula1;
String formula2 = ctDataValidation.formula2;
ST_DataValidationOperator operator1 = ctDataValidation.@operator;
ST_DataValidationType type = ctDataValidation.type;
int validationType = XSSFDataValidation.validationTypeReverseMappings[type];
int operatorType = XSSFDataValidation.operatorTypeReverseMappings[operator1];
constraint = new XSSFDataValidationConstraint(validationType, operatorType, formula1, formula2);
return(constraint);
}
public void ListLiteralsQuotesAreStripped_formulaConstructor()
{
// literal list, using formula constructor
String literal = "\"one, two, three\"";
String[] expected = new String[] { "one", "two", "three" };
IDataValidationConstraint constraint = new XSSFDataValidationConstraint(listType, ignoredType, literal, null);
CollectionAssert.AreEqual(expected, constraint.ExplicitListValues);
// Excel and DataValidationConstraint Parser ignore (strip) whitespace; quotes should still be intact
// FIXME: whitespace wasn't stripped
Assert.AreEqual(literal, constraint.Formula1);
}
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));
}
private XSSFDataValidationConstraint GetConstraint(CT_DataValidation ctDataValidation)
{
XSSFDataValidationConstraint constraint = null;
String formula1 = ctDataValidation.formula1;
String formula2 = ctDataValidation.formula2;
ST_DataValidationOperator operator1 = ctDataValidation.@operator;
ST_DataValidationType type = ctDataValidation.type;
int validationType = XSSFDataValidation.validationTypeReverseMappings[type];
int operatorType = XSSFDataValidation.operatorTypeReverseMappings[operator1];
constraint = new XSSFDataValidationConstraint(validationType, operatorType, formula1, formula2);
return constraint;
}
/* (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));
}
