public void DecimalFormula_FormulaValueIsSetFromXmlNodeInConstructor()
{
// Arrange
LoadXmlTestData("A1", "decimal", "1.3");
// Act
var validation = new ExcelDataValidationDecimal(_sheet, ExcelDataValidation.NewId(), "A1", ExcelDataValidationType.Decimal, _dataValidationNode, _namespaceManager);
Assert.AreEqual(1.3D, validation.Formula.Value);
}
public void DataValidations_ShouldSetOperatorFromExistingXml()
{
// Arrange
LoadXmlTestData("A1", "whole", "greaterThanOrEqual", "1");
// Act
var validation = new ExcelDataValidationInt(_sheet, ExcelDataValidation.NewId(), "A1", ExcelDataValidationType.Whole, _dataValidationNode, _namespaceManager);
// Assert
Assert.AreEqual(ExcelDataValidationOperator.greaterThanOrEqual, validation.Operator);
}
public void ListFormula_FormulasExcelFormulaIsSetFromXmlNodeInConstructor()
{
// Arrange
LoadXmlTestData("A1", "list", "A1");
// Act
var validation = new ExcelDataValidationList(_sheet, ExcelDataValidation.NewId(), "A1", ExcelDataValidationType.List, _dataValidationNode, _namespaceManager);
// Assert
Assert.AreEqual("A1", validation.Formula.ExcelFormula);
}
public void DataValidations_ShouldSetShowInputMessageFromExistingXml()
{
// Arrange
LoadXmlTestData("A1", "whole", "1", false, true);
// Act
var validation = new ExcelDataValidationInt(_sheet, ExcelDataValidation.NewId(), "A1", ExcelDataValidationType.Whole, _dataValidationNode, _namespaceManager);
// Assert
Assert.IsTrue(validation.ShowInputMessage ?? false);
}
public void DataValidations_ShouldSetErrorTitleFromExistingXml()
{
// Arrange
LoadXmlTestData("A1", "whole", "1", "Prompt", "PromptTitle", "Error", "ErrorTitle");
// Act
var validation = new ExcelDataValidationInt(_sheet, ExcelDataValidation.NewId(), "A1", ExcelDataValidationType.Whole, _dataValidationNode, _namespaceManager);
// Assert
Assert.AreEqual("ErrorTitle", validation.ErrorTitle);
}
public void UidShouldBeSetOnValidations()
{
// Arrange
LoadXmlTestData("A1", "decimal", "1.3");
var id = ExcelDataValidation.NewId();
// Act
var validation = new ExcelDataValidationDecimal(_sheet, id, "A1", ExcelDataValidationType.Decimal, _dataValidationNode, _namespaceManager);
// Assert
Assert.AreEqual(id, validation.Uid);
}
public void CustomFormula_FormulasFormulaIsSetFromXmlNodeInConstructor()
{
// Arrange
LoadXmlTestData("A1", "decimal", "A1");
// Act
var validation = new ExcelDataValidationCustom(_sheet, ExcelDataValidation.NewId(), "A1", ExcelDataValidationType.Custom, _dataValidationNode, _namespaceManager);
// Assert
Assert.AreEqual("A1", validation.Formula.ExcelFormula);
}
public void ListFormula_FormulaValueIsSetFromXmlNodeInConstructorOrderIsCorrect()
{
// Arrange
LoadXmlTestData("A1", "list", "\"1,2\"");
// Act
var validation = new ExcelDataValidationList(_sheet, ExcelDataValidation.NewId(), "A1", ExcelDataValidationType.List, _dataValidationNode, _namespaceManager);
// Assert
CollectionAssert.AreEquivalent(new List <string> {
"1", "2"
}, (ICollection)validation.Formula.Values);
}
public void TimeFormula_ValueIsSetFromConstructorValidateSecond()
{
// Arrange
var time = new ExcelTime(0.812M);
LoadXmlTestData("A1", "time", "0.812");
// Act
var formula = new ExcelDataValidationTime(_sheet, ExcelDataValidation.NewId(), "A1", ExcelDataValidationType.Time, _dataValidationNode, _namespaceManager);
// Assert
Assert.AreEqual(time.Second.Value, formula.Formula.Value.Second.Value);
}
public void DateTimeFormula_FormulasFormulaIsSetFromXmlNodeInConstructor()
{
// Arrange
var date = DateTime.Parse("2011-01-08");
LoadXmlTestData("A1", "decimal", "A1");
// Act
var validation = new ExcelDataValidationDateTime(_sheet, ExcelDataValidation.NewId(), "A1", ExcelDataValidationType.Decimal, _dataValidationNode, _namespaceManager);
// Assert
Assert.AreEqual("A1", validation.Formula.ExcelFormula);
}
public void DateTimeFormula_FormulaValueIsSetFromXmlNodeInConstructor()
{
// Arrange
var date = DateTime.Parse("2011-01-08");
var dateAsString = date.ToOADate().ToString(_cultureInfo);
LoadXmlTestData("A1", "decimal", dateAsString);
// Act
var validation = new ExcelDataValidationDateTime(_sheet, ExcelDataValidation.NewId(), "A1", ExcelDataValidationType.Decimal, _dataValidationNode, _namespaceManager);
// Assert
Assert.AreEqual(date, validation.Formula.Value);
}
public static void BindDataValidationForExcel(Database db, ExcelWorksheet worksheet, ExcelPackage package, int columnIndex, string enumSheetName, string enumType, int minValue, int maxValue, string columnName, ExcelDataValidation dataValidationType)
{
try
{
switch (dataValidationType)
{
case ExcelDataValidation.TextLength:
#region Text Length Data Validation
var textcolumn = worksheet.DataValidations.AddTextLengthValidation(ExcelRange.GetAddress(2, columnIndex, ExcelPackage.MaxRows, columnIndex));
textcolumn.ShowErrorMessage = true;
textcolumn.ErrorStyle = ExcelDataValidationWarningStyle.stop;
textcolumn.ErrorTitle = "Error";
textcolumn.Error = string.Format(columnName + " must be less than or equal to {0} characters.", maxValue);
textcolumn.Formula.Value = minValue;
textcolumn.Formula2.Value = maxValue;
textcolumn.AllowBlank = false;
textcolumn.ShowInputMessage = true;
//textcolumn.Prompt = "Please enter " + columnName;
break;
#endregion
case ExcelDataValidation.Integer:
#region Integer Data Validation
var integercolumn = worksheet.DataValidations.AddIntegerValidation(ExcelRange.GetAddress(2, columnIndex, ExcelPackage.MaxRows, columnIndex));
integercolumn.ErrorStyle = ExcelDataValidationWarningStyle.stop;
integercolumn.PromptTitle = "Enter a integer value here";
integercolumn.Prompt = "Value should be between 0 and 9";
integercolumn.ShowInputMessage = true;
integercolumn.ErrorTitle = "Invalid data error";
integercolumn.Error = string.Format(columnName + " must be between {0} and {1}.", 1, 9);
integercolumn.ShowErrorMessage = true;
integercolumn.Operator = ExcelDataValidationOperator.between;
integercolumn.Formula.Value = 0;
integercolumn.Formula2.Value = maxValue;
break;
#endregion
case ExcelDataValidation.DecimalValidationWithMaxLength:
#region Decimal Data Validation
var decimalcolumn2 = worksheet.DataValidations.AddDecimalValidation(ExcelRange.GetAddress(2, columnIndex, ExcelPackage.MaxRows, columnIndex));
decimalcolumn2.ShowErrorMessage = true;
decimalcolumn2.ErrorStyle = ExcelDataValidationWarningStyle.stop;
decimalcolumn2.ErrorTitle = "Invalid data error";
decimalcolumn2.Error = "Value must be numeric and less than or equal to " + maxValue + " digits";
decimalcolumn2.Formula.Value = minValue;
//decimalcolumn2.Formula.Value = 0D;
string maxChar = string.Empty;
for (int i = 0; i < maxValue; i++)
{
maxChar += "9";
}
decimalcolumn2.Operator = ExcelDataValidationOperator.between;
decimalcolumn2.Formula2.Value = Convert.ToDouble(maxChar);
break;
#endregion
default:
break;
}
}
catch
{
throw;
}
}
