using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace iDiTect.Excel.Demo
{
    public static class DataValidationHelper
    {
        public static void AddNumberValidation()
        {
            var workbook = new Workbook();
            var worksheet = workbook.Worksheets.Add("ValidationSheet");

            // Add a between value validation 
            var validation = worksheet.Cell("A1").SetDataValidation();
            validation.Decimal.Between(1, 10);
            // Show prompt message
            validation.ShowPromptMessage = true;
            validation.PromptTitle = "Enter decimal value";
            validation.PromptMessage = "Value should be between 1 and 10";
            // Show error message
            validation.ShowErrorMessage = true;
            validation.ErrorStyle = ErrorStyle.Stop;
            validation.ErrorTitle = "An invalid value was entered";
            validation.ErrorMessage = "Value must be between 1 and 10";


            //You can also add other type validation, like greater than...
            var validation2 = worksheet.Range("A2:A10").SetDataValidation();
            validation2.Integer.GreaterThan(10);
            validation2.ShowErrorMessage = true;
            validation2.ErrorStyle = ErrorStyle.Warning;
            validation2.ErrorTitle = "An invalid value was entered";
            validation2.ErrorMessage = "Value must be integer, and greater than 10";
            
            workbook.Save("NumberValidation.xlsx");
        }

        public static void AddTextLengthValidation()
        {
            var workbook = new Workbook();
            var worksheet = workbook.Worksheets.Add("ValidationSheet");

            var validation = worksheet.Cell(1, 1).SetDataValidation();
            validation.TextLength.GreaterThanOrEqual(5);

            // Show prompt message
            validation.ShowPromptMessage = true;
            validation.PromptMessage = "Input charactor length should be greater than 4";
            // Show error message
            validation.ShowErrorMessage = true;
            validation.ErrorStyle = ErrorStyle.Information;
            validation.ErrorTitle = "Value length is invalid";
            validation.ErrorMessage = "Value length must be greater or equal 5";

            workbook.Save("TextLengthValidation.xlsx");
        }

        public static void AddDateAndTimeValidation()
        {
            var workbook = new Workbook();
            var worksheet = workbook.Worksheets.Add("ValidationSheet");

            var validation = worksheet.Cell("A1").SetDataValidation();
            validation.Date.GreaterThan(new DateTime(2010, 1, 1));            
            // Show error message
            validation.ErrorTitle = "An invalid value was entered";
            validation.ErrorMessage = "Input date must be after 1/1/2010";

            var validation2 = worksheet.Cell("A2").SetDataValidation();
            validation2.Time.Between(new TimeSpan(9, 0, 0), new TimeSpan(17, 59, 59));
            // Show error message
            validation2.ErrorTitle = "An invalid value was entered";
            validation2.ErrorMessage = "Input time must be between in 9:00:00 - 17:59:59";

            workbook.Save("DateAndTimeValidation.xlsx");
        }

        public static void AddListValidation()
        {
            var workbook = new Workbook();
            var worksheet = workbook.Worksheets.Add("ValidationSheet");

            //First you need initialize a list of data
            worksheet.Cell("A1").Value = 1;
            worksheet.Cell("A2").Value = 2;
            worksheet.Cell("A3").Value = 3;
            worksheet.Cell("A4").Value = 4;
            worksheet.Cell("A5").Value = 5;

            var validation = worksheet.Cell("A6").SetDataValidation();
            //Add data in current worksheet
            validation.List("A1:A5");
            //Add data in the other worksheet of the same workbook
            //validation.List("Sheet2!A1:A5");

            // Show prompt message
            validation.ShowPromptMessage = true;
            validation.PromptTitle = "Enter pre-defined value";
            validation.PromptMessage = "Select or enter a value from the list";
            // Show error message
            validation.ShowErrorMessage = true;
            validation.ErrorTitle = "An invalid value was entered";
            validation.ErrorMessage = "Select a value from the list";
            validation.ErrorStyle = ErrorStyle.Stop;

            workbook.Save("ListValidation.xlsx");
        }

       
    }
}
