using System;
using System.Diagnostics;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;

namespace C1ExcelEvaluator
{
    public partial class MainPage : UserControl
    {
        C1.Silverlight.Excel.C1XLBook _book = new C1.Silverlight.Excel.C1XLBook();

        public MainPage()
        {
            InitializeComponent();

            // load testbook
            WriteLine("---------------------------------------------------");
            WriteLine("Loading 'testbook.xlsx'");
            _book.Load(GetResourceStream("testbook.xlsx"));
            WriteLine("Loaded.");
            WriteLine();

            // show original values
            ShowValues("Original Values");
            WriteLine();

            // change parameters
            var sheet = _book.Sheets[0];
            sheet[2, 0].Value = 2.0;
            sheet[3, 0].Value = 4.0;
            sheet[4, 0].Value = 6.0;

            // show new values
            ShowValues("Changed Values");
            WriteLine();

            // load regions_sample
            WriteLine("---------------------------------------------------");
            WriteLine("Loading 'regions_sample.xlsx'");
            _book.Load(GetResourceStream("regions_sample.xlsx"));
            WriteLine("Loaded.");
            WriteLine();

            // show current values (500 and 1000)
            sheet = _book.Sheets[0];
            WriteLine("Evaluating formulas with named ranges:");
            var cell = sheet[0, 1];
            WriteLine("\tcell B1: Formula = '{0}' Value='{1}' Evaluate='{2}",
                cell.Formula,
                cell.Value,
                cell.Evaluate(_book));
            cell = sheet[0, 2];
            WriteLine("\tcell C1: Formula = '{0}' Value='{1}' Evaluate='{2}",
                cell.Formula,
                cell.Value,
                cell.Evaluate(_book));
            cell = sheet[11, 2];
            WriteLine("\tcell C12: Formula = '{0}' Value='{1}' Evaluate='{2}'",
                cell.Formula,
                cell.Value,
                cell.Evaluate(_book));
            WriteLine();

            // change values
            WriteLine("Changing value of A2 to 400");
            sheet[1, 0].Value = 400;
            WriteLine();

            // show new values (700 and 1400)
            WriteLine("Evaluating formulas after the change:");
            cell = sheet[0, 1];
            WriteLine("\tcell B1: Formula = '{0}' Value='{1}' Evaluate='{2}'",
                cell.Formula,
                cell.Value,
                cell.Evaluate(_book));
            cell = sheet[0, 2];
            WriteLine("\tcell C1: Formula = '{0}' Value='{1}' Evaluate='{2}'",
                cell.Formula,
                cell.Value,
                cell.Evaluate(_book));
            cell = sheet[11, 2];
            WriteLine("\tcell C12: Formula = '{0}' Value='{1}' Evaluate='{2}'",
                cell.Formula,
                cell.Value,
                cell.Evaluate(_book));
        }
        void ShowValues(string title)
        {
            WriteLine(title);

            // show first cell: formula, original C1Excel value, Evaluate
            var sheet = _book.Sheets[0];
            var cell = sheet[0, 0];
            WriteLine("\tcell A1: Formula = '{0}' Value='{1}' Evaluate='{2}", 
                cell.Formula,
                cell.Value,
                cell.Evaluate(_book));

            // show second cell: formula, original C1Excel value, Evaluate
            cell = sheet[0, 2];
            WriteLine("\tcell A3: Formula = '{0}' Value='{1}' Evaluate='{2}",
                cell.Formula, 
                cell.Value,
                cell.Evaluate(_book));

            // show formula parameters
            for (int row = 2; row <= 4; row++)
            {
                cell = sheet[row, 0];
                WriteLine("\tcell[{0},0]: Value='{1}' Evaluate='{2}'", 
                    row, 
                    cell.Value,
                    cell.Evaluate(_book));
            }
        }
        void WriteLine(string fmt, params object[] items)
        {
            _txtTrace.Text += string.Format(fmt, items) + "\r\n";
        }
        void WriteLine()
        {
            WriteLine(string.Empty);
        }
        System.IO.Stream GetResourceStream(string resName)
        {
            var asm = this.GetType().Assembly;
            foreach (var rn in asm.GetManifestResourceNames())
            {
                if (rn.EndsWith(resName, StringComparison.OrdinalIgnoreCase))
                {
                    return asm.GetManifestResourceStream(rn);
                }
            }
            return null;
        }
    }
}
