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");
            openPicker.FileTypeFilter.Add(".txt");
            openPicker.FileTypeFilter.Add(".csv");

            StorageFile file = await openPicker.PickSingleFileAsync();
            if (file != null)
            {
                var filetype = file.FileType.ToLower();
                if (filetype == ".xlsx")
                {
                    await LoadExcel(file);
                }
                else if (filetype == ".txt")
                {
                    await LoadTxt(file, true);
                }
                else if (filetype == ".csv")
                {
                    await LoadCsv(file, ",", true);
                }

            }
        }

        private async System.Threading.Tasks.Task LoadCsv(StorageFile file, string separator = null, bool hasHeaders = false)
        {
            Stream stream = await file.OpenStreamForReadAsync();
            C1XLBook book = new C1XLBook();
            book.ListSeparator = separator;
            await book.LoadAsync(stream, C1.Xaml.Excel.FileFormat.Csv, false);
            // load first sheet
            if (book.Sheets.Count > 0)
            {
                var sheet = book.Sheets[0];
                this.c1FlexGrid1.Columns.Clear();
                this.c1FlexGrid1.Rows.Clear();
                int rowsCount = hasHeaders ? sheet.Rows.Count - 1 : sheet.Rows.Count;
                int columnsCount = sheet.Columns.Count;
                if (sheet.Rows.Count > 0)
                {
                    for (int c = 0; c < columnsCount; c++)
                    {
                        var col = new Column();
                        if (hasHeaders)
                        {
                            var cell = sheet[0, c];
                            if (cell != null && cell.Value != null)
                            {
                                col.ColumnName = cell.Value.ToString();
                            }
                        }
                        this.c1FlexGrid1.Columns.Add(col);
                    }
                }
                // add cells
                for (int r = 0; r < rowsCount; r++)
                {
                    var row = new Row();
                    this.c1FlexGrid1.Rows.Add(row);
                    for (int c = 0; c < columnsCount; c++)
                    {
                        var cell = sheet[r +(hasHeaders?1:0), c];
                        if (cell != null)
                        {
                            if (cell.Value != null)
                            {
                                // save value
                                this.c1FlexGrid1[r, c] = cell.Value;
                            }

                        }
                    }
                }

            }
        }

        private async System.Threading.Tasks.Task LoadTxt(StorageFile file, bool hasHeaders = false)
        {
            //Separator must be '\t'
            Stream stream = await file.OpenStreamForReadAsync();
            string txt = string.Empty;
            using (StreamReader reader = new StreamReader(stream))
            {
                txt = reader.ReadToEnd();
            }

            if (txt.Length > 0)
            {

                c1FlexGrid1.Rows.Clear();
                c1FlexGrid1.Columns.Clear();
                string newText = txt.Replace("\r\n", "\r");

                // remove trailing break if any (Excel always adds one)
                if (newText[newText.Length - 1] == '\r')
                {
                    newText = newText.Substring(0, newText.Length - 1);
                }

                var lines = newText.Split('\r', '\n');
                int rowCount = hasHeaders ? lines.Length - 1 : lines.Length;
                if (rowCount < 0)
                {
                    return;
                }
                int colCount = lines[0].Split('\t').Length;

                // add rows and columns
                for (int r = 0; r < rowCount; r++)
                {
                    c1FlexGrid1.Rows.Add(new Row());
                }
                for (int c = 0; c < colCount; c++)
                {
                    var column = new Column();
                    if (hasHeaders)
                    {
                        column.ColumnName = lines[0].Split('\t')[c].ToString();
                    }
                    c1FlexGrid1.Columns.Add(column);
                }

                if (hasHeaders && lines.Length > 1)
                {
                    txt = txt.Remove(0, txt.IndexOf(lines[1]));
                }
                CellRange rang = new CellRange(0, 0, rowCount - 1, colCount - 1);
                c1FlexGrid1.SetClipString(txt, rang, ClipboardCopyMode.None);

            }
        }

        private async System.Threading.Tasks.Task LoadExcel(StorageFile file)
        {
            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;
        }
    }
}
