/// <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; }