/// <summary>
/// Allow date values.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="DataValue">The data value. Any valid date formatted value is fine. It is suggested to just copy the value you have in Excel interface.</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowDate(SLDataValidationSingleOperandValues DataOperator, string DataValue, bool IgnoreBlank)
{
this.Type = DataValidationValues.Date;
this.Operator = this.TranslateOperatorValues(DataOperator);
DateTime dt;
if (DataValue.StartsWith("="))
{
this.Formula1 = DataValue.Substring(1);
}
else
{
if (DateTime.TryParse(DataValue, out dt))
{
this.Formula1 = SLTool.CalculateDaysFromEpoch(dt, this.Date1904).ToString(CultureInfo.InvariantCulture);
}
else
{
// 1 Jan 1900
this.Formula1 = "1";
}
}
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
internal DataValidationOperatorValues TranslateOperatorValues(SLDataValidationSingleOperandValues Operator)
{
DataValidationOperatorValues result = DataValidationOperatorValues.Between;
switch (Operator)
{
case SLDataValidationSingleOperandValues.Equal:
result = DataValidationOperatorValues.Equal;
break;
case SLDataValidationSingleOperandValues.NotEqual:
result = DataValidationOperatorValues.NotEqual;
break;
case SLDataValidationSingleOperandValues.GreaterThan:
result = DataValidationOperatorValues.GreaterThan;
break;
case SLDataValidationSingleOperandValues.LessThan:
result = DataValidationOperatorValues.LessThan;
break;
case SLDataValidationSingleOperandValues.GreaterThanOrEqual:
result = DataValidationOperatorValues.GreaterThanOrEqual;
break;
case SLDataValidationSingleOperandValues.LessThanOrEqual:
result = DataValidationOperatorValues.LessThanOrEqual;
break;
}
return(result);
}
/// <summary>
/// Allow decimal (floating point) values.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="DataValue">The data value.</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowDecimal(SLDataValidationSingleOperandValues DataOperator, decimal DataValue, bool IgnoreBlank)
{
this.Type = DataValidationValues.Decimal;
this.Operator = this.TranslateOperatorValues(DataOperator);
this.Formula1 = DataValue.ToString(CultureInfo.InvariantCulture);
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow decimal (floating point) values.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="DataValue">The data value.</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowDecimal(SLDataValidationSingleOperandValues DataOperator, string DataValue, bool IgnoreBlank)
{
this.Type = DataValidationValues.Decimal;
this.Operator = this.TranslateOperatorValues(DataOperator);
this.Formula1 = this.CleanDataSourceForFormula(DataValue);
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow date values.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="DataValue">The data value. Any valid date formatted value is fine. It is suggested to just copy the value you have in Excel interface.</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowDate(SLDataValidationSingleOperandValues DataOperator, DateTime DataValue, bool IgnoreBlank)
{
this.Type = DataValidationValues.Date;
this.Operator = this.TranslateOperatorValues(DataOperator);
this.Formula1 = SLTool.CalculateDaysFromEpoch(DataValue, this.Date1904).ToString(CultureInfo.InvariantCulture);
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow data according to text length.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="Length">The text length for comparison.</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowTextLength(SLDataValidationSingleOperandValues DataOperator, string Length, bool IgnoreBlank)
{
this.Type = DataValidationValues.TextLength;
this.Operator = this.TranslateOperatorValues(DataOperator);
this.Formula1 = this.CleanDataSourceForFormula(Length);
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow only whole numbers.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="DataValue">The data value.</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowWholeNumber(SLDataValidationSingleOperandValues DataOperator, long DataValue, bool IgnoreBlank)
{
Type = DataValidationValues.Whole;
Operator = TranslateOperatorValues(DataOperator);
Formula1 = DataValue.ToString(CultureInfo.InvariantCulture);
Formula2 = string.Empty;
AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow only whole numbers.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="DataValue">The data value.</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowWholeNumber(SLDataValidationSingleOperandValues DataOperator, string DataValue,
bool IgnoreBlank)
{
Type = DataValidationValues.Whole;
Operator = TranslateOperatorValues(DataOperator);
Formula1 = CleanDataSourceForFormula(DataValue);
Formula2 = string.Empty;
AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow data according to text length.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="Length">The text length for comparison.</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowTextLength(SLDataValidationSingleOperandValues DataOperator, int Length, bool IgnoreBlank)
{
if (Length < 0)
{
Length = 0;
}
this.Type = DataValidationValues.TextLength;
this.Operator = this.TranslateOperatorValues(DataOperator);
this.Formula1 = Length.ToString(CultureInfo.InvariantCulture);
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow time values.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="Time">
/// The time. Any valid time formatted value is fine. It is suggested to just copy the value you have in
/// Excel interface.
/// </param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowTime(SLDataValidationSingleOperandValues DataOperator, string Time, bool IgnoreBlank)
{
Type = DataValidationValues.Time;
Operator = TranslateOperatorValues(DataOperator);
double fTime = 0;
DateTime dt;
string sTime;
// we include the day, month and year for formatting because it seems that parsing based
// just on time (hour, minute, second, AM/PM designator) is too much for TryParseExact()...
string[] saFormats =
{
"dd/MM/yyyy H", "dd/MM/yyyy h t", "dd/MM/yyyy h tt", "dd/MM/yyyy H:m",
"dd/MM/yyyy h:m t", "dd/MM/yyyy h:m tt", "dd/MM/yyyy H:m:s", "dd/MM/yyyy h:m:s t", "dd/MM/yyyy h:m:s tt"
};
var sSampleDate = DateTime.Now.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);
// 1440 = 24 hours * 60 minutes
// 86400 = 24 hours * 60 minutes * 60 seconds
if (Time.StartsWith("="))
{
Formula1 = Time.Substring(1);
}
else
{
sTime = string.Format("{0} {1}", sSampleDate, Time);
if (DateTime.TryParseExact(sTime, saFormats, CultureInfo.InvariantCulture,
DateTimeStyles.AllowWhiteSpaces, out dt))
{
fTime = dt.Hour / 24.0 + dt.Minute / 1440.0 + dt.Second / 86400.0;
Formula1 = fTime.ToString(CultureInfo.InvariantCulture);
}
else
{
Formula1 = "0";
}
}
Formula2 = string.Empty;
AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow time values.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="Hour">The hour between 0 to 23 (both inclusive).</param>
/// <param name="Minute">The minute between 0 to 59 (both inclusive).</param>
/// <param name="Second">The second between 0 to 59 (both inclusive).</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowTime(SLDataValidationSingleOperandValues DataOperator, int Hour, int Minute, int Second,
bool IgnoreBlank)
{
if (Hour < 0)
{
Hour = 0;
}
if (Hour > 23)
{
Hour = 23;
}
if (Minute < 0)
{
Minute = 0;
}
if (Minute > 59)
{
Minute = 59;
}
if (Second < 0)
{
Second = 0;
}
if (Second > 59)
{
Second = 59;
}
Type = DataValidationValues.Time;
Operator = TranslateOperatorValues(DataOperator);
double fTime = 0;
// 1440 = 24 hours * 60 minutes
// 86400 = 24 hours * 60 minutes * 60 seconds
fTime = Hour / 24.0 + Minute / 1440.0 + Second / 86400.0;
Formula1 = fTime.ToString(CultureInfo.InvariantCulture);
Formula2 = string.Empty;
AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow date values.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="DataValue">The data value. Any valid date formatted value is fine. It is suggested to just copy the value you have in Excel interface.</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowDate(SLDataValidationSingleOperandValues DataOperator, DateTime DataValue, bool IgnoreBlank)
{
this.Type = DataValidationValues.Date;
this.Operator = this.TranslateOperatorValues(DataOperator);
this.Formula1 = SLTool.CalculateDaysFromEpoch(DataValue, this.Date1904).ToString(CultureInfo.InvariantCulture);
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow data according to text length.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="Length">The text length for comparison.</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowTextLength(SLDataValidationSingleOperandValues DataOperator, int Length, bool IgnoreBlank)
{
if (Length < 0) Length = 0;
this.Type = DataValidationValues.TextLength;
this.Operator = this.TranslateOperatorValues(DataOperator);
this.Formula1 = Length.ToString(CultureInfo.InvariantCulture);
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow data according to text length.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="Length">The text length for comparison.</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowTextLength(SLDataValidationSingleOperandValues DataOperator, string Length, bool IgnoreBlank)
{
this.Type = DataValidationValues.TextLength;
this.Operator = this.TranslateOperatorValues(DataOperator);
this.Formula1 = this.CleanDataSourceForFormula(Length);
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow time values.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="Hour">The hour between 0 to 23 (both inclusive).</param>
/// <param name="Minute">The minute between 0 to 59 (both inclusive).</param>
/// <param name="Second">The second between 0 to 59 (both inclusive).</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowTime(SLDataValidationSingleOperandValues DataOperator, int Hour, int Minute, int Second, bool IgnoreBlank)
{
if (Hour < 0) Hour = 0;
if (Hour > 23) Hour = 23;
if (Minute < 0) Minute = 0;
if (Minute > 59) Minute = 59;
if (Second < 0) Second = 0;
if (Second > 59) Second = 59;
this.Type = DataValidationValues.Time;
this.Operator = this.TranslateOperatorValues(DataOperator);
double fTime = 0;
// 1440 = 24 hours * 60 minutes
// 86400 = 24 hours * 60 minutes * 60 seconds
fTime = ((double)Hour / 24.0) + ((double)Minute / 1440.0) + ((double)Second / 86400.0);
this.Formula1 = fTime.ToString(CultureInfo.InvariantCulture);
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow time values.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="Time">The time. Any valid time formatted value is fine. It is suggested to just copy the value you have in Excel interface.</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowTime(SLDataValidationSingleOperandValues DataOperator, string Time, bool IgnoreBlank)
{
this.Type = DataValidationValues.Time;
this.Operator = this.TranslateOperatorValues(DataOperator);
double fTime = 0;
DateTime dt;
string sTime;
// we include the day, month and year for formatting because it seems that parsing based
// just on time (hour, minute, second, AM/PM designator) is too much for TryParseExact()...
string[] saFormats = new string[] { "dd/MM/yyyy H", "dd/MM/yyyy h t", "dd/MM/yyyy h tt", "dd/MM/yyyy H:m", "dd/MM/yyyy h:m t", "dd/MM/yyyy h:m tt", "dd/MM/yyyy H:m:s", "dd/MM/yyyy h:m:s t", "dd/MM/yyyy h:m:s tt" };
string sSampleDate = DateTime.Now.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture);
// 1440 = 24 hours * 60 minutes
// 86400 = 24 hours * 60 minutes * 60 seconds
if (Time.StartsWith("="))
{
this.Formula1 = Time.Substring(1);
}
else
{
sTime = string.Format("{0} {1}", sSampleDate, Time);
if (DateTime.TryParseExact(sTime, saFormats, CultureInfo.InvariantCulture, DateTimeStyles.AllowWhiteSpaces, out dt))
{
fTime = ((double)dt.Hour / 24.0) + ((double)dt.Minute / 1440.0) + ((double)dt.Second / 86400.0);
this.Formula1 = fTime.ToString(CultureInfo.InvariantCulture);
}
else
{
this.Formula1 = "0";
}
}
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow only whole numbers.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="DataValue">The data value.</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowWholeNumber(SLDataValidationSingleOperandValues DataOperator, long DataValue, bool IgnoreBlank)
{
this.Type = DataValidationValues.Whole;
this.Operator = this.TranslateOperatorValues(DataOperator);
this.Formula1 = DataValue.ToString(CultureInfo.InvariantCulture);
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow date values.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="DataValue">The data value. Any valid date formatted value is fine. It is suggested to just copy the value you have in Excel interface.</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowDate(SLDataValidationSingleOperandValues DataOperator, string DataValue, bool IgnoreBlank)
{
this.Type = DataValidationValues.Date;
this.Operator = this.TranslateOperatorValues(DataOperator);
DateTime dt;
if (DataValue.StartsWith("="))
{
this.Formula1 = DataValue.Substring(1);
}
else
{
if (DateTime.TryParse(DataValue, out dt))
{
this.Formula1 = SLTool.CalculateDaysFromEpoch(dt, this.Date1904).ToString(CultureInfo.InvariantCulture);
}
else
{
// 1 Jan 1900
this.Formula1 = "1";
}
}
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
/// <summary>
/// Allow only whole numbers.
/// </summary>
/// <param name="DataOperator">The type of operation.</param>
/// <param name="DataValue">The data value.</param>
/// <param name="IgnoreBlank">True if blanks are ignored. False otherwise.</param>
public void AllowWholeNumber(SLDataValidationSingleOperandValues DataOperator, string DataValue, bool IgnoreBlank)
{
this.Type = DataValidationValues.Whole;
this.Operator = this.TranslateOperatorValues(DataOperator);
this.Formula1 = this.CleanDataSourceForFormula(DataValue);
this.Formula2 = string.Empty;
this.AllowBlank = IgnoreBlank;
}
internal DataValidationOperatorValues TranslateOperatorValues(SLDataValidationSingleOperandValues Operator)
{
DataValidationOperatorValues result = DataValidationOperatorValues.Between;
switch (Operator)
{
case SLDataValidationSingleOperandValues.Equal:
result = DataValidationOperatorValues.Equal;
break;
case SLDataValidationSingleOperandValues.NotEqual:
result = DataValidationOperatorValues.NotEqual;
break;
case SLDataValidationSingleOperandValues.GreaterThan:
result = DataValidationOperatorValues.GreaterThan;
break;
case SLDataValidationSingleOperandValues.LessThan:
result = DataValidationOperatorValues.LessThan;
break;
case SLDataValidationSingleOperandValues.GreaterThanOrEqual:
result = DataValidationOperatorValues.GreaterThanOrEqual;
break;
case SLDataValidationSingleOperandValues.LessThanOrEqual:
result = DataValidationOperatorValues.LessThanOrEqual;
break;
}
return result;
}
