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

namespace iDiTect.Excel.Demo
{
    public static class ConditionalFormattingHelper
    {
        public static void AddConditionalFormatting()
        {
            var workbook = new Workbook();
            var worksheet = workbook.Worksheets.Add("ConditionalFormatting");

            // Create 9 columns of samples data
            for (int col = 1; col < 10; col++)
            {
                worksheet.Cell(1, col).Value = "Sample " + col;

                for (int row = 2; row < 12; row++)
                {
                    worksheet.Cell(row, col).Value = row - 1;
                }
            }

            var range = worksheet.Range("A2:A11");
            var rule1 = range.AddConditionalFormat().ColorScale();
            rule1.LowestValue(ExcelColor.Red);
            //You can set 2 or 3 color scale, if want to show 3 color scale, modify the "Midpoint"
            //rule1.Midpoint(ConditionalFormatContentType.Percent, 50, ExcelColor.Pear);
            rule1.HighestValue(ExcelColor.Purple);


            range = worksheet.Range(2, 2, 11, 2); //="B2:B11"
            //find top 3 items, depend on the bigger value
            var rule2 = range.AddConditionalFormat().IsTop(3);
            //or find top 30% items, depend on the bigger value
            //var rule2 = range.AddConditionalFormat().IsTop(30, TopBottomType.Percent);
            rule2.Font.FontColor = ExcelColor.Red;
            //similar
            //var rule2 = range.AddConditionalFormat().IsBottom(3);

            range = worksheet.Range("C2:C11");
            var rule3 = range.AddConditionalFormat().IsStartsWith("3");
            rule3.Fill.BackgroundColor = ExcelColor.Red;
            //similar
            //var rule3 = range.AddConditionalFormat().IsEndsWith("3");
            //var rule3 = range.AddConditionalFormat().IsContain("3");
            //var rule3 = range.AddConditionalFormat().IsNotContain("3");

            range = worksheet.Range("D2:D11");
            var rule4 = range.AddConditionalFormat().IsBetween(4, 6);
            rule4.Font.FontColor = ExcelColor.Red;
            //similar
            //var rule4 = range.AddConditionalFormat().IsNotBetween();

            range = worksheet.Range("E2:E11");
            var rule5 = range.AddConditionalFormat().IsGreaterThan(5);
            rule5.Font.FontColor = ExcelColor.Red;
            //similar
            //var rule5 = range.AddConditionalFormat().IsGreaterThanOrEqual(5);
            //var rule5 = range.AddConditionalFormat().IsLessThan(5);
            //var rule5 = range.AddConditionalFormat().IsLessThanOrEqual(5);
            //var rule5 = range.AddConditionalFormat().IsEqual(5);
            //var rule5 = range.AddConditionalFormat().IsNotEqual(5);

            range = worksheet.Range("F2:F11");
            var rule6 = range.AddConditionalFormat().IsBlank();
            rule6.Font.FontColor = ExcelColor.Red;
            //similar
            //var rule6 = range.AddConditionalFormat().IsNotBlank();
            //var rule6 = range.AddConditionalFormat().IsError();
            //var rule6 = range.AddConditionalFormat().IsNotError();
            //var rule6 = range.AddConditionalFormat().IsDuplicate();
            //var rule6 = range.AddConditionalFormat().IsUnique();

            range = worksheet.Range("G2:G11");
            var rule7 = range.AddConditionalFormat().IsTrue("IF(G2-G3<=0,1,0)");
            //or just simply set the rule to TRUE
            //var rule7 = range.AddConditionalFormat().IsTrue("TRUE");
            rule7.Font.FontColor = ExcelColor.Red;

            range = worksheet.Range("H2:H11");
            var rule8 = range.AddConditionalFormat().DataBar(ExcelColor.Red);
            //Set all data value to show bar with gradient
            rule8.LowestValue();
            rule8.HighestValue();
            //or set target data value to show bar with gradient
            //rule8.Minimum(ConditionalFormatContentType.Number, 3);
            //rule8.Maximum(ConditionalFormatContentType.Percent, 80);

            range = worksheet.Range("I2:I11");
            var rule9 = range.AddConditionalFormat().IconSet(IconSetStyle.ThreeArrows);
            rule9.AddValue(ConditionalFormatIconSetOperator.GreaterThanOrEqual, "3", ConditionalFormatContentType.Number);
            rule9.AddValue(ConditionalFormatIconSetOperator.GreaterThan, "5", ConditionalFormatContentType.Number);
            rule9.AddValue(ConditionalFormatIconSetOperator.GreaterThan, "7", ConditionalFormatContentType.Number);


            worksheet.Cell(1, 10).Value = "Sample 10";
            for (int row = 2; row < 12; row++)
            {
                worksheet.Cell(row, 10).Value = DateTime.Now.AddDays(-row + 1);
                worksheet.Cell(row,10).Style.NumberFormat.Format = "mm-dd-yy";
            }

            range = worksheet.Range("J2:J11");
            var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.Last7Days);
            rule10.Font.FontColor = ExcelColor.Red;
            //similar
            //var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.Today);
            //var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.Yesterday);
            //var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.Tomorrow);
            //var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.ThisWeek);
            //var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.LastWeek);
            //var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.NextWeek);
            //var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.ThisMonth);
            //var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.LastMonth);
            //var rule10 = range.AddConditionalFormat().DateIn(TimePeriod.NextMonth);

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



    }
}
