using C1.Xaml.Excel;
using C1.Xaml.FlexGrid;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices.WindowsRuntime;
using Windows.Foundation;
using Windows.Foundation.Collections;
using Windows.Storage;
using Windows.Storage.Pickers;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Controls;
using Windows.UI.Xaml.Controls.Primitives;
using Windows.UI.Xaml.Data;
using Windows.UI.Xaml.Input;
using Windows.UI.Xaml.Media;
using Windows.UI.Xaml.Navigation;

// The Blank Page item template is documented at http://go.microsoft.com/fwlink/?LinkId=234238

namespace LoadExcel
{
    /// <summary>
    /// An empty page that can be used on its own or navigated to within a Frame.
    /// </summary>
    public sealed partial class MainPage : Page
    {
        public MainPage()
        {
            this.InitializeComponent();
        }

        private async void btnLoadExcel_Click(object sender, RoutedEventArgs e)
        {
            FileOpenPicker openPicker = new FileOpenPicker();
            openPicker.FileTypeFilter.Add(".xlsx");

            StorageFile file = await openPicker.PickSingleFileAsync();
            if (file != null)
            {
                var book = new C1.Xaml.Excel.C1XLBook();
                await book.LoadAsync(file, C1.Xaml.Excel.FileFormat.OpenXml);

                // load first sheet
                if (book.Sheets.Count > 0)
                {
                    LoadFlexGrid(book.Sheets[0], c1FlexGrid1);
                }
                else
                {
                    // no sheets
                }
            }
        }

        /// <summary>
        /// Loads the content of an XLSheet into a C1FlexGrid.
        /// </summary>
        public static void LoadFlexGrid(XLSheet sheet, C1FlexGrid flex)
        {
            // set default parameters
            flex.FontFamily = new FontFamily(sheet.Book.DefaultFont.FontName);
            //flex.IsReadOnly = sheet.Locked;
            flex.GridLinesVisibility = sheet.ShowGridLines
                ? GridLinesVisibility.All
                : GridLinesVisibility.None;
            flex.GridLinesBrush = new SolidColorBrush(sheet.GridColor);
            //flex.HeadersVisibility = sheet.ShowHeaders
            //    ? HeadersVisibility.All
            //    : HeadersVisibility.None;
            flex.HeadersVisibility = HeadersVisibility.All;

            // add columns
            flex.Columns.Clear();
            for (int c = 0; c < sheet.Columns.Count; c++)
            {
                var cell = sheet[0, c];
                if(cell.Value != null)
                { 
                    // create column
                    var col = new Column();
                    //col.ColumnName = col.GetHashCode().ToString("x0");
                    //col.Header = "ds " + (flex.Columns.Count + 1).ToString();
                    col.Visible = sheet.Columns[c].Visible;

                    // and add to the grid
                    flex.Columns.Add(col);
                }
            }

            // add rows
            flex.Rows.Clear();
            foreach (XLRow r in sheet.Rows)
            {
                var row = new Row();
                row.Visible = r.Visible;
                flex.Rows.Add(row);
            }

            // add cells
            for (int r = 0; r < flex.Rows.Count; r++)
            {
                for (int c = 0; c < flex.Columns.Count; c++)
                {
                    var cell = sheet[r, c];
                    if (cell != null)
                    {
                        if (cell.Value != null)
                        {
                            flex[r, c] = cell.Value;
                        }
                    }
                }
            }

            // freeze rows/columns
            //flex.Rows.Frozen = sheet.Rows.Frozen;
            //flex.Columns.Frozen = sheet.Columns.Frozen;
        }
    }
}
