using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx;
using Telerik.Windows.Documents.Spreadsheet.Formatting.FormatStrings;
using Telerik.Windows.Documents.Spreadsheet.Model;

namespace ReadExcelMap
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            XlsxFormatProvider formatProvider = new XlsxFormatProvider();
            Workbook workbook = formatProvider.Import(File.ReadAllBytes(@"C:\temp\Template.xlsx"));

            var worksheet = workbook.Sheets[0] as Worksheet;
            var table = new DataTable();

            IEnumerable<ConditionalFormattingRange> formattingRanges = worksheet.Cells[1, 10].GetConditionalFormattings();

            var range = formattingRanges.FirstOrDefault();
            var formating = range.Formattings.FirstOrDefault();
            var rule = formating.Rule as ColorScaleRule;

            var value = worksheet.Cells[1, 10].GetValue().Value as NumberCellValue;

            if (value != null)
            {
                var result = formating.Resolve(worksheet.Cells[1, 10]);

                if (result == 0)
                {
                    Console.WriteLine(rule.ValueContext.MinimumColor.LocalValue);
                }
                else
                {
                    Console.WriteLine(rule.ValueContext.MaximumColor.LocalValue);
                }
            }


            //CellSelection selection = worksheet.Cells[2, 30];
            //ICellValue value = selection.GetValue().Value;
            //CellValueFormat format = selection.GetFormat().Value;
            //CellValueFormatResult formatResult = format.GetFormatResult(value);
            //string result = formatResult.InfosText;

            //var fill = selection.GetFill().Value as PatternFill;
            //ThemableColor backgroundColor = fill.BackgroundColor;

            //for (int i = 0; i < worksheet.UsedCellRange.ColumnCount; i++)
            //{
            //    CellSelection selection = worksheet.Cells[0, i];
            //    var columnName = selection.GetValue().Value.RawValue.ToString();

            //    table.Columns.Add(columnName);
            //}



            //for (int i = 1; i < worksheet.UsedCellRange.RowCount; i++)
            //{
            //    var values = new object[worksheet.UsedCellRange.ColumnCount];

            //    for (int j = 0; j < worksheet.UsedCellRange.ColumnCount; j++)
            //    {
            //        CellSelection selection = worksheet.Cells[i, j];

            //        ICellValue value = selection.GetValue().Value;
            //        CellValueFormat format = selection.GetFormat().Value;
            //        CellValueFormatResult formatResult = format.GetFormatResult(value);
            //        string result = formatResult.InfosText;

            //         IFill f = selection.GetFill().Value;
            //         ThemableColor t = selection.GetForeColor().Value;

            //        values[j] = result;
            //    }
            //    table.Rows.Add(values);

            //}

            //radGridView1.DataSource = table;
        }
    }
}
