using System;
using System.ComponentModel;
using System.IO;
using System.Windows;
using System.Windows.Data;
using System.Windows.Input;
using System.Windows.Controls;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Globalization;
using System.Windows.Media;
using C1.Silverlight.FlexGrid;

namespace C1.Silverlight.FlexGridExcel
{
    /// <summary>
    /// Class that extends the C1FlexGrid control to provide Excel-like functionality
    /// such as letter and number-indexed column and row headers, PDF export, and more.
    /// </summary>
    public class C1FlexGridExcel : C1FlexGrid
    {
        //---------------------------------------------------------------------------
        #region ** fields

        ExcelContextMenu _menu;             // context menu
        FlexUndoStack _undo;                // undo/redo stack
        PasteAction _pasteAction;           // for recording undoable paste actions
        HitTestInfo _htDown;                // for selecting columns
        bool _selecting;                    // state for selecting columns
        List<Column> _selColumns;           // selected columns
        C1FlexGridFilter _filter;           // Excel-style column filter
        ExcelCellFactory _xlCellFactory;    // custom cell factory
        CellStyle _hdrRowStyle;             // style for header rows
        CellStyle _grStyle;                 // style for group rows
        CellStyle _bgrStyle;                // style for bottom group rows
        ObservableCollection<ColumnSortDescription> _sortDescriptions; // custom (unbound) sort descriptions

        #endregion

        //---------------------------------------------------------------------------
        #region ** ctor

        /// <summary>
        /// Initializes a new instance of a <see cref="C1FlexGridExcel"/>.
        /// </summary>
        public C1FlexGridExcel()
        {
            // create context menu
            _menu = new ExcelContextMenu(this);

            // Excel doesn't allow dragging rows or columns
            AllowDragging = AllowDragging.None;

            // start with blue color scheme
            ColorSchemeManager.ApplyColorScheme(this, ColorScheme.Blue);

            // use custom cell factory to generate Excel-style row and column headers
            // (columns labeled A, B, C, etc, rows labeled 1, 2, 3, etc)
            _xlCellFactory = new ExcelCellFactory(this);
            CellFactory = _xlCellFactory;

            // Excel doesn't freeze groups
            AreRowGroupHeadersFrozen = false;

            // create styles for header and bottom group rows
            HeaderRowStyle = new CellStyle();
            GroupRowStyle = new CellStyle();
            GroupRowStyle.FontWeight = FontWeights.Bold;
            BottomGroupRowStyle = new CellStyle();
            BottomGroupRowStyle.FontWeight = FontWeights.Bold;
            BottomGroupRowStyle.Foreground = new SolidColorBrush(Colors.Gray);

            // enable easy subtotal formatting
            GroupHeaderConverter = new ExcelGroupHeaderConverter();

            // disable mouse sorting (Excel doesn't do that)
            AllowSorting = false;

            // show Excel-style marquee
            ShowMarquee = true;
            CursorBackground = null;
            GridLinesVisibility = GridLinesVisibility.All;

            // enable automatic copy/paste
            ClipboardCopyMode = ClipboardCopyMode.ExcludeHeader;
            ClipboardPasteMode = ClipboardCopyMode.ExcludeHeader;

            // tab moves the selection (not the focus)
            KeyActionTab = KeyAction.MoveAcross;

            // connect handlers to select columns
            PreviewMouseLeftButtonDown += mouseLeftButtonDown;
            MouseLeftButtonUp += mouseLeftButtonUp;
            MouseMove += mouseMove;
            SelectionChanged += selectionChanged;

            // connect handler to resize selected columns
            ResizedColumn += resizedColumn;

            // ensure header row is visible after collapsing groups
            LayoutUpdated += _layoutUpdated;

            // create undo/redo stack
            _undo = new FlexUndoStack(this);
            _undo.StateChanged += (s, e) =>
                {
                    SetValue(CanUndoProperty, _undo.CanUndo);
                    SetValue(CanRedoProperty, _undo.CanRedo);
                };

            // enable column filtering by default
            _filter = new C1FlexGridFilter(this);
            _filter.UseCollectionView = true;
            _filter.Editor = new ExcelFilterEditor();

            // set default header row style
            var hrs = HeaderRowStyle;
            hrs.FontWeight = FontWeights.Bold;
            hrs.Background = new SolidColorBrush(Colors.LightGray);

            // custom sort descriptions
            _sortDescriptions = new ObservableCollection<ColumnSortDescription>();
            _sortDescriptions.CollectionChanged += (s, e) =>
                {
                    ColumnHeaders.Invalidate();
                };
        }

        #endregion

        //---------------------------------------------------------------------------
        #region ** object model

        /// <summary>
        /// Gets or sets the value stored in a grid cell using Excel range notation.
        /// </summary>
        /// <param name="cellReference">Cell reference using Excel notation (e.g. "A1", "AB32").</param>
        /// <returns>The value stored in the cell.</returns>
        public object this[string cellReference]
        {
            get
            {
                var rng = GetCellRange(cellReference);
                return this[rng.Row, rng.Column];
            }
            set
            {
                var rng = GetCellRange(cellReference);
                this[rng.Row, rng.Column] = value;
            }
        }
        /// <summary>
        /// Gets the <see cref="ContextMenu"/> associated with this <see cref="C1FlexGridExcel"/>.
        /// </summary>
        /// <remarks>
        /// Callers may customize the context menu by modifying the content of the Items property.
        /// </remarks>
        public ExcelContextMenu ContextMenu
        {
            get { return _menu; }
        }
        /// <summary>
        /// Gets the <see cref="UndoStack"/> associated with this <see cref="C1FlexGridExcel"/>.
        /// </summary>
        /// <remarks>
        /// <see cref="IUndoableAction"/> elements added to the UndoStack can be undone/re-done later.
        /// </remarks>
        public FlexUndoStack UndoStack
        {
            get { return _undo; }
        }
        /// <summary>
        /// Saves the grid to a Xlsx file.
        /// </summary>
        /// <param name="stream">Stream where the file is to be saved.</param>
        public void SaveXlsx(Stream stream)
        {
            GridExport.SaveXlsx(this, stream);
        }
        /// <summary>
        /// Saves the grid to a Csv file.
        /// </summary>
        /// <param name="stream">Stream where the file is to be saved.</param>
        public void SaveCsv(Stream stream)
        {
            GridExport.SaveCsv(this, stream);
        }
        /// <summary>
        /// Saves the grid to a PDF stream.
        /// </summary>
        /// <param name="stream">Stream where the file is to be saved.</param>
        public void SavePdf(Stream stream)
        {
            GridExport.SavePdf(this, stream);
        }
        /// <summary>
        /// Saves the grid to a PDF stream.
        /// </summary>
        /// <param name="stream">Stream where the file is to be saved.</param>
        /// <param name="options"><see cref="PdfExportOptions"/> class with parameters 
        /// that customize the PDF output.</param>
        public void SavePdf(Stream stream, PdfExportOptions options)
        {
            GridExport.SavePdf(this, stream, options);
        }
        /// <summary>
        /// Gets or sets the color scheme used to render the grid.
        /// </summary>
        public ColorScheme ColorScheme 
        {
            get { return (ColorScheme)GetValue(ColorSchemeProperty); }
            set { SetValue(ColorSchemeProperty, value); }
        }
        /// <summary>
        /// Identifies the <see cref="ColorScheme"/> dependency property.
        /// </summary>
        public static readonly DependencyProperty ColorSchemeProperty =
            DependencyProperty.Register(
                "ColorScheme",
                typeof(ColorScheme),
                typeof(C1FlexGridExcel),
                new PropertyMetadata(ColorScheme.Blue, C1FlexGridExcel.OnColorSchemeChanged));
        // apply the new color scheme to the grid
        static void OnColorSchemeChanged(DependencyObject d, DependencyPropertyChangedEventArgs e)
        {
            var grid = (C1FlexGridExcel)d;
            ColorSchemeManager.ApplyColorScheme(grid, grid.ColorScheme);
        }

#if false // this property was moved to the base C1FlexGrid control
        /// <summary>
        /// Gets or sets a value that determines whether the grid should display
        /// the outline button bar.
        /// </summary>
        [DefaultValue(true)]
        public bool ShowOutlineBar
        {
            get { return (bool)GetValue(ShowOutlineBarProperty); }
            set { SetValue(ShowOutlineBarProperty, value); }
        }
        /// <summary>
        /// Identifies the <see cref="ShowOutlineBarProperty"/> dependency property.
        /// </summary>
        public static readonly DependencyProperty ShowOutlineBarProperty =
            DependencyProperty.Register(
                "ShowOutlineBar",
                typeof(bool),
                typeof(C1FlexGridExcel),
                new PropertyMetadata(true, C1FlexGridExcel.OnShowOutlineBarChanged));
        // invalidate the grid when the ShowOutlineBar property changes
        static void OnShowOutlineBarChanged(DependencyObject d, DependencyPropertyChangedEventArgs e)
        {
            var grid = (C1FlexGridExcel)d;
            grid.Invalidate();
        }
#endif

        /// <summary>
        /// Undoes the last action (valid only when <see cref="CanUndo"/> returns true).
        /// </summary>
        public void Undo()
        {
            _undo.Undo();
        }
        /// <summary>
        /// Gets a value that determines whether the undo stack contains any actions.
        /// </summary>
        public bool CanUndo
        {
            get { return (bool)GetValue(CanUndoProperty); }
        }
        /// <summary>
        /// Identifies the <see cref="CanUndo"/> dependency property.
        /// </summary>
        public static readonly DependencyProperty CanUndoProperty =
            DependencyProperty.Register(
                "CanUndo",
                typeof(bool),
                typeof(C1FlexGridExcel),
                new PropertyMetadata(false));
        /// <summary>
        /// Repeats the last action (valid only when <see cref="CanRedo"/> returns true).
        /// </summary>
        public void Redo()
        {
            _undo.Redo();
        }
        /// <summary>
        /// Gets a value that determines whether the redo stack contains any actions.
        /// </summary>
        public bool CanRedo
        {
            get { return (bool)GetValue(CanRedoProperty); }
        }
        /// <summary>
        /// Identifies the <see cref="CanUndo"/> dependency property.
        /// </summary>
        public static readonly DependencyProperty CanRedoProperty =
            DependencyProperty.Register(
                "CanRedo",
                typeof(bool),
                typeof(C1FlexGridExcel),
                new PropertyMetadata(false));
        /// <summary>
        /// Gets or sets a value that determines whether the grid should display
        /// an extra bottom group row below the data when the main group row is located 
        /// above the data.
        /// </summary>
        public bool ShowBottomGroupRows
        {
            get { return (bool)GetValue(ShowBottomGroupRowsProperty); }
            set { SetValue(ShowBottomGroupRowsProperty, value); }
        }
        /// <summary>
        /// Identifies the <see cref="ShowBottomGroupRowsProperty"/> dependency property.
        /// </summary>
        public static readonly DependencyProperty ShowBottomGroupRowsProperty =
            DependencyProperty.Register(
                "ShowBottomGroupRows",
                typeof(bool),
                typeof(C1FlexGridExcel),
                new PropertyMetadata(false, C1FlexGridExcel.OnShowBottomGroupRowsChanged));
        // re-create the rows when the ShowBottomGroupRows property changes
        static void OnShowBottomGroupRowsChanged(DependencyObject d, DependencyPropertyChangedEventArgs e)
        {
            var grid = (C1FlexGridExcel)d;
            if (grid.CollectionView != null)
            {
                grid.CollectionView.Refresh();
            }
        }
        /// <summary>
        /// Gets or sets the caption shown on the first cell of the group rows.
        /// </summary>
        /// <remarks>
        /// <para>The string used to build the caption may contain placeholders that will be
        /// replaced with the group information as follows:</para>
        /// <list>
        /// <item>{0}: Name of the property being grouped on (e.g. "Product").</item>
        /// <item>{1}: Value of the property for this group (e.g. "Computer").</item>
        /// <item>{2}: Number of items in this group.</item>
        /// <item>{3}: Plural indicator ("s" if the item count is greater than one, "" otherwise).</item>
        /// </list>
        /// </remarks>
        /// <example>
        /// The code below creates group captions that are formatted like the default
        /// group rows in a regular <see cref="C1FlexGrid"/> control:
        /// <code>
        /// // create captions like "Product: Computer (12 items)"
        /// _flex.GroupRowCaption = "{0}: {1} ({2} items)";
        /// </code>
        /// </example>
        public string GroupRowCaption
        {
            get { return (string)GetValue(GroupRowCaptionProperty); }
            set { SetValue(GroupRowCaptionProperty, value); }
        }
        /// <summary>
        /// Identifies the <see cref="GroupRowCaptionProperty"/> dependency property.
        /// </summary>
        public static readonly DependencyProperty GroupRowCaptionProperty =
            DependencyProperty.Register(
                "GroupRowCaption",
                typeof(string),
                typeof(C1FlexGridExcel),
                new PropertyMetadata(null));
        /// <summary>
        /// Gets or sets the caption shown on the first cell of the bottom group rows.
        /// </summary>
        /// <remarks>
        /// <para>Extra group rows are shown below the data when the <see cref="ShowBottomGroupRows"/> 
        /// property is set to true.</para>
        /// <para>By default, they show the same content as the regular group rows, defined by the
        /// grid's <see cref="C1FlexGrid.GroupHeaderConverter"/>. You can customize the content of 
        /// the bottom group rows by setting this property.</para>
        /// <para>The string used to build the caption may contain placeholders that will be
        /// replaced with the group information as follows:</para>
        /// <list>
        /// <item>{0}: Name of the property being grouped on (e.g. "Product").</item>
        /// <item>{1}: Value of the property for this group (e.g. "Computer").</item>
        /// <item>{2}: Number of items in this group.</item>
        /// <item>{3}: Plural indicator ("s" if the item count is greater than one, "" otherwise).</item>
        /// </list>
        /// </remarks>
        /// <example>
        /// The code below creates subtotal captions that are formatted like the default
        /// group rows in a regular <see cref="C1FlexGrid"/> control:
        /// <code>
        /// // create captions like "Product: Computer (12 items)"
        /// _flex.BottomGroupRowCaption = "{0}: {1} ({2} items)";
        /// </code>
        /// </example>
        public string BottomGroupRowCaption
        {
            get { return (string)GetValue(BottomGroupRowCaptionProperty); }
            set { SetValue(BottomGroupRowCaptionProperty, value); }
        }
        /// <summary>
        /// Identifies the <see cref="BottomGroupRowCaptionProperty"/> dependency property.
        /// </summary>
        public static readonly DependencyProperty BottomGroupRowCaptionProperty =
            DependencyProperty.Register(
                "BottomGroupRowCaption",
                typeof(string),
                typeof(C1FlexGridExcel),
                new PropertyMetadata(null));

        /// <summary>
        /// Gets or sets the <see cref="CellStyle"/> used to render the header row.
        /// </summary>
        public CellStyle HeaderRowStyle
        {
            get { return _hdrRowStyle; }
            set
            {
                if (_hdrRowStyle != value)
                {
                    if (_hdrRowStyle != null)
                        _hdrRowStyle.PropertyChanged -= _style_PropertyChanged;

                    _hdrRowStyle = value;

                    if (_hdrRowStyle != null)
                        _hdrRowStyle.PropertyChanged += _style_PropertyChanged;

                    Invalidate();
                }
            }
        }
        /// <summary>
        /// Gets or sets the <see cref="CellStyle"/> used to render group rows.
        /// </summary>
        public CellStyle GroupRowStyle
        {
            get { return _grStyle; }
            set
            {
                if (_grStyle != value)
                {
                    if (_grStyle != null)
                        _grStyle.PropertyChanged -= _style_PropertyChanged;

                    _grStyle = value;

                    if (_grStyle != null)
                        _grStyle.PropertyChanged += _style_PropertyChanged;

                    Invalidate();
                }
            }
        }
        /// <summary>
        /// Gets or sets the <see cref="CellStyle"/> used to render the extra group rows.
        /// </summary>
        public CellStyle BottomGroupRowStyle
        {
            get { return _bgrStyle; }
            set
            {
                if (_bgrStyle != value)
                {
                    if (_bgrStyle != null)
                        _bgrStyle.PropertyChanged -= _style_PropertyChanged;

                    _bgrStyle = value;

                    if (_bgrStyle != null)
                        _bgrStyle.PropertyChanged += _style_PropertyChanged;

                    Invalidate();
                }
            }
        }

        // invalidate the grid when the header row style changes
        void _style_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            Invalidate();
        }

        /// <summary>
        /// Gets or sets whether the grid should provide column filters.
        /// </summary>
        public bool EnableColumnFilters
        {
            get { return (bool)GetValue(EnableColumnFiltersProperty); }
            set { SetValue(EnableColumnFiltersProperty, value); }
        }
        /// <summary>
        /// Identifies the <see cref="EnableColumnFiltersProperty"/> dependency property.
        /// </summary>
        public static readonly DependencyProperty EnableColumnFiltersProperty =
            DependencyProperty.Register(
                "EnableColumnFilters",
                typeof(bool),
                typeof(C1FlexGridExcel),
                new PropertyMetadata(true, C1FlexGridExcel.OnEnableColumnFiltersChanged));
        // enable/disable column filtering
        static void OnEnableColumnFiltersChanged(DependencyObject d, DependencyPropertyChangedEventArgs e)
        {
            var grid = (C1FlexGridExcel)d;
            if ((bool)e.NewValue)
            {
                grid._filter.Owner = grid;
            }
            else
            {
                grid._filter.Owner = null;
            }
        }
        /// <summary>
        /// Show the filter editor for the currently selected column.
        /// </summary>
        public void ShowFilterEditor()
        {
            var index = Selection.Column;
            if (index > -1)
            {
                _filter.ShowFilterEditor(Columns[index]);
            }
        }
        /// <summary>
        /// Prints the grid directly to the browser.
        /// </summary>
        /// <param name="documentName">Name of the document to print.</param>
        /// <param name="pageSize">Page size, in pixels (used to paginate the output).</param>
        /// <param name="preview">True if the browser should show a preview before printing.</param>
        /// <remarks>
        /// This method saves the grid to a paged HTML document, then uses javascript to send the
        /// document directly to the browser for printing. This method is faster and more memory
        /// efficient than the standard <see cref="C1FlexGrid.Print"/> method, which uses the 
        /// Silverlight printing mechanism to render more accurate output.
        /// </remarks>
        public void PrintToBrowser(string documentName, Size pageSize, bool preview)
        {
            // hide extra header rows while printing
            var headerRow = Rows[0] as HeaderRow;
            if (headerRow != null)
            {
                headerRow.Visible = false;
            }

            // print
            var htmlPrinter = new FlexHtmlPrinter(this);
            htmlPrinter.Print("Preview", pageSize, preview);

            // restore extra header rows
            if (headerRow != null)
            {
                headerRow.Visible = true;
            }
        }
        /// <summary>
        /// Gets a collection of <see cref="ColumnSortDescription"/> objects that describe
        /// how unbound columns are sorted.
        /// </summary>
        public IList<ColumnSortDescription> ColumnSortDescriptions
        {
            get { return _sortDescriptions; }
        }

        #endregion

        //---------------------------------------------------------------------------
        #region ** overrides

        /// <summary>
        /// Overridden to add/update the header row at the top of the grid
        /// </summary>
        protected override void OnLoadedRows(EventArgs e)
        {
            var view = CollectionView;
            if (view != null)
            {
                // create header row 
                var hdrRow = new HeaderRow();
                Rows.Insert(0, hdrRow);
            }

            // fire event
            base.OnLoadedRows(e);
        }
        /// <summary>
        /// Overridden to create all rows for items in a FlatList.
        /// </summary>
        protected override void CreateBoundRows()
        {
            var flatList = GetFlatList();
            if (flatList == null)
            {
                // not a flat list, allow default handling
                base.CreateBoundRows();
            }
            else
            {
                // create rows for flat lists (in this case, all rows are group rows)
                foreach (var item in CollectionView)
                {
                    var groupInfo = flatList.GetGroupInformation(item);
                    var gr = new FlatListRow(item, groupInfo.Level);
                    Rows.Add(gr);
                }
            }
        }
        /// <summary>
        /// Overridden to skip group row creation if the data source is a FlatList and to
        /// to insert 'subtotal' rows below the groups.
        /// </summary>
        protected override void CreateGroupRows()
        {
            // remove extra group rows, if any
            using (Rows.DeferNotifications())
            {
                // remove old subtotal rows
                for (int r = 0; r < this.Rows.Count; r++)
                {
                    if (Rows[r] is BottomGroupRow)
                    {
                        Rows.RemoveAt(r);
                        r--;
                    }
                }
            }

            // create group rows as usual if the data source is not a flat list
            var flatList = GetFlatList();
            if (flatList == null)
            {
                base.CreateGroupRows();
            }

            // check whether the data source has groups
            bool hasGroups = 
                (flatList != null && flatList.GetMaxLevels() > 0) ||
                (CollectionView != null && CollectionView.GroupDescriptions.Count > 0);

            // create 'subtotal' rows below the groups
            if (hasGroups &&
                GroupRowPosition == FlexGrid.GroupRowPosition.AboveData &&
                ShowBottomGroupRows)
            {
                using (Rows.DeferNotifications())
                {
                    // insert new extra subtotal rows
                    for (int r = 0; r < this.Rows.Count; r++)
                    {
                        var gr = Rows[r] as GroupRow;
                        if (gr != null)
                        {
                            // special handling for FlatList items with no children
                            if (flatList != null && !flatList.GetGroupInformation(gr.DataItem).HasChildren)
                            {
                                continue;
                            }

                            // add extra subtotal row
                            var subtotalRow = new BottomGroupRow(gr);
                            var rg = subtotalRow.GetCellRange();
                            Rows.Insert(rg.BottomRow + 1, subtotalRow);
                        }
                    }
                }
            }
        }
        ///// <summary>
        ///// Overridden to show/hide the subtotals on a group row above the data when there
        ///// are extra group rows below the data.
        ///// </summary>
        ///// <param name="e"><see cref="CellRangeEventArgs"/> that contains the event data.</param>
        //protected override void OnGroupCollapsedChanged(CellRangeEventArgs e)
        //{
        //    // update group row visibility
        //    if (ShowBottomGroupRows)
        //    {
        //        Invalidate();
        //    }

        //    // fire event as usual
        //    base.OnGroupCollapsedChanged(e);
        //}
        /// <summary>
        /// Overridden to handle keyboard shortcuts.
        /// </summary>
        /// <param name="e"><see cref="KeyEventArgs"/> that contains the event data.</param>
        protected override void OnPreviewKeyDown(KeyEventArgs e)
        {
            // default processing
            base.OnPreviewKeyDown(e);
            if (!e.Handled && e.OriginalSource == this)
            {
                // additional key handling
                var ctl = (Keyboard.Modifiers & ModifierKeys.Control) != 0;
                var shft = (Keyboard.Modifiers & ModifierKeys.Shift) != 0;

                // honor undo/redo shortcuts (CTRL-Z, CTRL-Y)
                if (ctl)
                {
                    switch (e.Key)
                    {
                        case Key.Z:
                            e.Handled = true;
                            this.Undo();
                            break;
                        case Key.Y:
                            e.Handled = true;
                            this.Redo();
                            break;
                    }
                }

                // delete selection content on Delete key
                if (e.Key == Key.Delete && !shft)
                {
                    e.Handled = true;
                    ClearSelection();
                }

                // delete current cell content and start editing on BackSpace key
                // (like Excel)
                if (e.Key == Key.Back)
                {
                    var sel = this.Selection;
                    if (sel.IsValid)
                    {
                        // clear cursor cell and start editing
                        ClearRange(new CellRange(sel.Row, sel.Column));
                        StartEditing(true);
                    }
                }
            }
        }
        /// <summary>
        /// Clears all nullable cells in the selection.
        /// </summary>
        public void ClearSelection()
        {
            ClearRange(Selection);
        }
        /// <summary>
        /// Clears all nullable/editable cells in a given range.
        /// </summary>
        public void ClearRange(CellRange range)
        {
            if (!IsReadOnly)
            {
                // create undoable paste action to represent this clear operation
                _pasteAction = new PasteAction(this);

                // clear selected cells
                var invalidate = false;
                foreach (var cell in range.Cells)
                {
                    if (AllowEditing(cell.Row, cell.Column))
                    {
                        this[cell.Row, cell.Column] = null;
                        invalidate |= this.Columns[cell.Column].GroupAggregate != Aggregate.None;
                    }
                }

                // if the range that was cleared contains aggregates, 
                // invalidate grid to force an update
                if (invalidate)
                {
                    Invalidate();
                }

                // close the paste action and add to the undo stack
                _pasteAction.FinishPasting();
                UndoStack.AddAction(_pasteAction);
                _pasteAction = null;
            }
        }
        /// <summary>
        /// Overridden to enable undoing paste actions.
        /// </summary>
        public override void Paste()
        {
            // paste into active editor, if any
            var tb = GetActiveTextBox();
            if (tb != null)
            {
                // note: setting SelectedText doesn't work with binding
                // looks like a bug in the TextBox control.
                var clip = System.Windows.Clipboard.GetText();
                var txt = tb.Text;
                var ss = tb.SelectionStart;
                var sl = tb.SelectionLength;
                tb.Text = txt.Substring(0, ss) + clip + txt.Substring(ss + sl);
                tb.Select(ss, clip.Length);
                tb.Focus();
                return;
            }

            // create undoable paste action to represent this paste operation
            _pasteAction = new PasteAction(this);

            // perform the paste operation (recording is on)
            base.Paste();

            // close the paste action and add to the undo stack
            _pasteAction.FinishPasting();
            UndoStack.AddAction(_pasteAction);
            _pasteAction = null;
        }
        /// <summary>
        /// Overridden to copy editor content if the editor is active.
        /// </summary>
        public override void Copy()
        {
            // copy from active editor, if any
            var tb = GetActiveTextBox();
            if (tb != null && tb.SelectionLength > 0)
            {
                System.Windows.Clipboard.SetText(tb.SelectedText);
                tb.Focus();
                return;
            }

            // allow default behavior
            base.Copy();
        }
        /// <summary>
        /// Overridden to record individual cell assignments during a paste operation.
        /// </summary>
        public override object this[int row, Column col]
        {
            get { return base[row, col]; }
            set
            {
                // prevent pasting if the cell is read-only
                if (!AllowEditing(row, col.Index))
                {
                    return;
                }

                // if recording the paste operation, add this assignment to the list
                if (_pasteAction != null)
                {
                    _pasteAction.PasteCell(row, col.Index, base[row, col], value);
                }

                try
                {
                    // delegate to base class
                    base[row, col] = value;

                    // commit changes immediately after each edit (to update subtotals)
                    CommitChanges(row);
                }
                catch
                {
                    // cannot paste this value (wrong type, etc)
                }
            }
        }
        /// <summary>
        /// Overridden to commit changes after every update (so subtotals are updated immediately).
        /// </summary>
        /// <param name="e"></param>
        protected override void OnCellEditEnded(CellEditEventArgs e)
        {
            base.OnCellEditEnded(e);

            // commit changes immediately after each edit (to update subtotals)
            CommitChanges(e.Row);
        }
        /// <summary>
        /// Overridden to aggregate only items that have no children.
        /// Items that do have children obtain the aggregates from the children.
        /// </summary>
        public override bool IncludeInAggregate(Row row, Column column)
        {
            // special handling for flat lists
            var flatList = GetFlatList();
            if (flatList != null && row.DataItem != null)
            {
                // exclude items that have children
                var item = row.DataItem;
                var group = flatList.GetGroupInformation(item);
                return group.HasChildren == false;
            }

            // exclude HeaderRow and BottomGroupRow rows (they have no real data)
            if (row is HeaderRow || row is BottomGroupRow)
            {
                return false;
            }

            // allow default handling (exclude GroupRows)
            return base.IncludeInAggregate(row, column);
        }
        /// <summary>
        /// Overridden to change the default save options.
        /// We don't need to save row and column headers since they don't contain any
        /// data (or any content, actually; the header content is all created dynamically
        /// by the cell factory).
        /// </summary>
        new public void Save(Stream s, FileFormat fmt)
        {
            base.Save(s, fmt, SaveOptions.Formatted);
        }
        ///// <summary>
        ///// Overridden to check for read-only cell templates before entering edit mode.
        ///// </summary>
        ///// <param name="e"></param>
        //override protected void OnBeginningEdit(CellEditEventArgs e)
        //{
        //    if (!AllowEditing(e.Row, e.Column))
        //    {
        //        e.Cancel = true;
        //    }
        //    base.OnBeginningEdit(e);
        //}

        #endregion

        //---------------------------------------------------------------------------
        #region ** event handlers

        void _layoutUpdated(object sender, EventArgs e)
        {
            // ensure header row is visible after collapsing groups
            // (necessary when showing totals below the data)
            if (Rows.Count > 0)
            {
                var hdrRow = Rows[0] as HeaderRow;
                if (hdrRow != null && !hdrRow.IsVisible)
                {
                    Rows.RemoveAt(0);
                    Rows.Insert(0, new HeaderRow());
                }
            }

            // ensure bottom group rows are visible when their parent group row is visible
            // REVIEW: this is necessary only when the user collapses/expands the tree using
            // the group outline buttons on top of the grid; shouldn't be needed at all!
            foreach (var row in Rows)
            {
                var bgr = row as BottomGroupRow;
                if (bgr != null)
                {
                    bgr.Visible = bgr.GroupRow.IsVisible && !bgr.GroupRow.IsCollapsed;
                    if (bgr.IsVisible != bgr.Visible)
                    {
                        CreateGroupRows();
                        break;
                    }
                }
            }

            // show dropdowns only on cursor cell
            UpdateDropDownVisibility();
        }

        // handle clicks on column headers to select them
        void mouseLeftButtonDown(object sender, System.Windows.Input.MouseButtonEventArgs e)
        {
            // check that the user is over a column header but not in the resizing area
            _htDown = HitTest(e);
            if (_htDown.CellType == CellType.ColumnHeader)
            {
                const int RESIZE_THRESHOLD = 3;
                if (Math.Abs(_htDown.Point.X - _htDown.Rect.X) <= RESIZE_THRESHOLD ||
                    Math.Abs(_htDown.Point.X - _htDown.Rect.Right) <= RESIZE_THRESHOLD)
                {
                    _htDown = null;
                }
            }

            // select the column
            if (_htDown != null && _htDown.CellType == CellType.ColumnHeader)
            {
                // check that the user released the mouse on the same column
                var ht = HitTest(e);
                if (ht.CellType == CellType.ColumnHeader && _htDown.Column == ht.Column)
                {
                    // get column that was clicked
                    var col = Columns[ht.Column];

                    // reset selection if control key is not pressed
                    if ((Keyboard.Modifiers & ModifierKeys.Control) == 0)
                    {
                        foreach (var c in Columns)
                        {
                            c.Selected = c == col;
                        }
                    }

                    // save selected columns to handle mouse move
                    _selColumns = Columns.Selected;

                    // select column that was clicked
                    _selecting = true;
                    Select(0, ht.Column, Rows.Count - 1, ht.Column, false);
                    col.Selected = true;
                    _selecting = false;
                }
            }
        }
        void mouseLeftButtonUp(object sender, System.Windows.Input.MouseButtonEventArgs e)
        {
            // reset mouse state
            _htDown = null;
        }

        // select columns as the user drags the mouse
        void mouseMove(object sender, MouseEventArgs e)
        {
            if (_htDown != null && _htDown.CellType == CellType.ColumnHeader)
            {
                var ht = HitTest(e);
                if (ht.CellType == CellType.ColumnHeader)
                {
                    // get selection range
                    var c1 = Math.Min(_htDown.Column, ht.Column);
                    var c2 = Math.Max(_htDown.Column, ht.Column);

                    // select columns in range or in the original selection
                    for (int c = 0; c < Columns.Count; c++)
                    {
                        var col = Columns[c];
                        col.Selected =
                            (c >= c1 && c <= c2) ||
                            (_selColumns != null && _selColumns.Contains(col));
                    }
                }
            }
        }

        // reset column selection when the regular selection changes
        void selectionChanged(object sender, CellRangeEventArgs e)
        {
            if (!_selecting)
            {
                foreach (var c in Columns)
                {
                    c.Selected = false;
                }
            }
            UpdateDropDownVisibility();
        }

        // resize all selected columns at once
        void resizedColumn(object sender, CellRangeEventArgs e)
        {
            var col = Columns[e.Column];
            if (col.Selected)
            {
                foreach (var c in Columns.Selected)
                {
                    c.Width = col.Width;
                }
            }
        }

        #endregion

        //---------------------------------------------------------------------------
        #region ** implementation

        // show dropdown only for cursor cell
        void UpdateDropDownVisibility()
        {
            if (Rows.Count > 0 && Columns.Count > 0)
            {
                var visibleRange = ViewRange;
                if (Columns.Frozen > 0) visibleRange.Column = 0;
                if (Rows.Frozen > 0) visibleRange.Row = 0;
                foreach (var cell in visibleRange.Cells)
                {
                    var rng = MergeManager != null
                        ? MergeManager.GetMergedRange(this, CellType.Cell, cell)
                        : cell;
                    var bdr = Cells.GetCellElement(rng) as Border;
                    if (bdr != null)
                    {
                        var g = bdr.Child as Grid;
                        if (g != null && g.Children.Count > 1)
                        {
                            var bdrDropDown = g.Children[1] as Border;
                            if (bdrDropDown != null)
                            {
                                var poly = bdrDropDown.Child as System.Windows.Shapes.Polygon;
                                if (poly != null)
                                {
                                    poly.Visibility = this.GetSelectedState(rng) == SelectedState.Cursor
                                        ? Visibility.Visible
                                        : Visibility.Collapsed;
                                }
                            }
                        }
                    }
                }
            }
        }

        // gets the IFlatList being used as the grid's items source
        internal IFlatList GetFlatList()
        {
            return GroupRowPosition == GroupRowPosition.AboveData
                ? ItemsSource as IFlatList
                : null;
        }

        // gets the active TextBox editor
        TextBox GetActiveTextBox()
        {
            var bdr = ActiveEditor as Border;
            return bdr != null ? bdr.Child as TextBox : null;
        }

        // translates an excel cell reference (e.g. "A1" into a CellRange)
        CellRange GetCellRange(string xlRef)
        {
            // parse reference
            var rx = new System.Text.RegularExpressions.Regex("([A-Z]+)([1-9]+)");
            var m = rx.Match(xlRef.ToUpper());
            if (!m.Success)
            {
                throw new Exception("Invalid cell reference.");
            }

            // parse row
            var row = int.Parse(m.Groups[2].Value, CultureInfo.InvariantCulture);

            // parse column
            var col = 0;
            var colRef = m.Groups[1].Value;
            for (int i = 0; i < colRef.Length; i++)
            {
                col = 26 * col + (colRef[i] - 'A' + 1);
            }

            // return cell range
            return new CellRange(row - 1, col - 1);
        }

        // commit any pending changes back to the data source
        void CommitChanges(int row)
        {
            // commit edits
            var edt = CollectionView as IEditableCollectionView;
            if (edt != null)
            {
                if (!edt.IsEditingItem)
                {
                    var item = this.Rows[row].DataItem;
                    if (item != null)
                    {
                        edt.EditItem(item);
                    }
                }
                edt.CommitEdit();
            }
        }

        // check whether a cell is Read-Only (many possibilities)
        bool AllowEditing(int row, int col)
        {
            // check grid for read-only status
            if (row < 0 || col < 0 || IsReadOnly)
            {
                return false;
            }

            // check row, column for read-only status
            var c = Columns[col];
            var r = Rows[row];
            if (c.IsReadOnly || r.IsReadOnly)
            {
                return false;
            }

            // check binding
            var b = c.Binding;
            if (b != null && (b.Mode == BindingMode.OneWay || b.Mode == BindingMode.OneTime))
            {
                return false;
            }

            // check binding in cell editing template 
            // NOTE: this is not 100% safe, it only works if the editor is a TextBox
            // (will always allow editing CheckBoxes, ComboBoxes, NumericTextBoxes, DatePickers, etc)
            if (c.CellEditingTemplate != null)
            {
                var editor = c.CellEditingTemplate.LoadContent();
                foreach (TextBox tb in GetChildrenOfType<TextBox>(editor))
                {
                    tb.DataContext = r.DataItem;
                    if (tb.IsReadOnly || !tb.IsEnabled)
                    {
                        return false;
                    }
                    var bx = tb.GetBindingExpression(TextBox.TextProperty);
                    if (bx != null && bx.ParentBinding.Mode != BindingMode.TwoWay)
                    {
                        return false;
                    }
                }
                // could check for other control types here...
            }

            // all seems OK
            return true;
        }

        // get all the children of a specific type
        static IEnumerable<T> GetChildrenOfType<T>(DependencyObject e) where T : DependencyObject
        {
            if (e != null)
            {
                for (int i = 0; i < VisualTreeHelper.GetChildrenCount(e); i++)
                {
                    DependencyObject child = VisualTreeHelper.GetChild(e, i);
                    if (child != null && child is T)
                    {
                        yield return (T)child;
                    }
                    foreach (T childOfChild in GetChildrenOfType<T>(child))
                    {
                        yield return childOfChild;
                    }
                }
            }
        }

        #endregion

        //---------------------------------------------------------------------------
        #region ** group header converter

        // custom converter used to honor the GroupRowCaption property
        class ExcelGroupHeaderConverter : IValueConverter
        {
            public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
            {
                // get grid
                var r = parameter as Row;
                var xlg = r.Grid as C1FlexGridExcel;

                // get group row, caption
                var gr = parameter as GroupRow;
                var caption = xlg.GroupRowCaption;

                // handle bottom group rows
                if (gr == null)
                {
                    var bgr = parameter as BottomGroupRow;
                    if (bgr != null)
                    {
                        gr = bgr.GroupRow;
                        caption = xlg.BottomGroupRowCaption;
                    }
                }

                // done
                return xlg.FormatGroupCaption(gr, caption);
            }
            public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
            {
                throw new NotImplementedException();
            }
        }
        string FormatGroupCaption(GroupRow gr, string fmt)
        {
            // format regular items
            if (gr != null && gr.Group != null)
            {
                var view = CollectionView;
                if (view != null && view.GroupDescriptions.Count > gr.Level)
                {
                    // regular groups
                    if (string.IsNullOrEmpty(fmt))
                    {
                        fmt = "{0}: {1} ({2} item{3})";
                    }
                    var desc = view.GroupDescriptions[gr.Level] as PropertyGroupDescription;
                    var prop = desc.PropertyName;
                    var gval = gr.Group.Name;
                    var count = gr.Group.ItemCount;
                    var plural = count > 1 ? "s" : string.Empty;
                    return string.Format(fmt, prop, gval, count, plural);
                }
            }

            // format flat lists
            var flatList = GetFlatList();
            if (flatList != null && gr.DataItem != null)
            {
                if (string.IsNullOrEmpty(fmt))
                {
                    fmt = "{1}";
                }
                int col = GetOutlineColumn();
                var prop = Columns[col].BoundPropertyName;
                var gval = gr[col];
                var rng = gr.GetCellRange();
                rng.Column = rng.Column2 = col;
                var count = GetAggregate(Aggregate.Count, rng);
                var plural = count > 1 ? "s" : string.Empty;
                return string.Format(fmt, prop, gval, count, plural);
            }

            // ??
            return null;
        }
        int GetOutlineColumn()
        {
            int col = 0;
            for (int c = 0; c < Columns.Count; c++)
            {
                if (Columns[c].IsVisible)
                {
                    col = c;
                    break;
                }
            }
            return col;
        }

        #endregion
    }

    /// <summary>
    /// Class used to hold a column sort description.
    /// Used by the cell factory to add custom sort icons to arbitrary columns.
    /// For details, see the <see cref="C1FlexGridExcel.ColumnSortDescriptions"/> property.
    /// </summary>
    public class ColumnSortDescription
    {
        public ColumnSortDescription(Column column, ListSortDirection direction)
        {
            Column = column;
            Direction = direction;
        }
        public Column Column { get; private set; }
        public ListSortDirection Direction { get; private set; }
    }

}
