XAF 如何从Excel复制多个单元格内容到GridView

2012年04月11日 ⁄ 综合 ⁄ 共 10998字    ⁄ 字号 小 中 大 ⁄ 评论关闭

how to paste some excel content to xtragrid?

.相關資料

http://community.devexpress.com/forums/t/36684.aspx

http://community.devexpress.com/forums/t/58611.aspx

 .調用方法：

using System;

using DevExpress.ExpressApp;

using DevExpress.ExpressApp.Actions;

using DevExpress.Persistent.Base;

using DevExpress.ExpressApp.Win.Editors;

using DevExpress.XtraGrid.Views.Grid;

namespace XafExpandExtendTest.Module

{

    public partial class CopyPasteListViewController : ViewController

    {

        public CopyPasteListViewController()

        {

            InitializeComponent();

            RegisterActions(components);

            TargetViewType = ViewType.ListView;

        }

        private void simpleAction1_Execute(object sender, SimpleActionExecuteEventArgs e)

        {

            ListView lv = View as ListView;

            GridView gv = (lv.Editor as GridListEditor).GridView;

            XtraGridHelper.GridViewClipboardPaste(gv);

        }

    }

}

.實現代碼：

using System;

using System.Collections.Generic;

using System.Windows.Forms;

using DevExpress.XtraGrid.Views.Grid;

using DevExpress.XtraEditors.Repository;

using DevExpress.XtraEditors.Controls;

namespace XafExpandExtendTest.Module

{

    public class XtraGridHelper

    {

        private static string ClipboardText

        {

            get

            {

                string value = string.Empty;

                IDataObject iData = Clipboard.GetDataObject();

                if (iData != null)

                {

                    if (iData.GetDataPresent(DataFormats.Text))

                    {

                        value = iData.GetData(DataFormats.Text) as string;

                    }

                }

                return value;

            }

            set

            {

                Clipboard.SetDataObject(value);

            }

        }

        //private static string[] ClipboardTextLines

        //{

        //    get

        //    {

        //        return XtraGridHelper.ClipboardText.Split(' ');

        //    }

        //}

        public static void PasteTable(GridView gridView)

        {

            gridView.ClearSorting();

            int originalFocusedVisibleColIndex = gridView.FocusedColumn.VisibleIndex;

            string[] clipboardTextLines = XtraGridHelper.ClipboardTextLines;

            // paste data from clipboard into row cells

            foreach (string line in clipboardTextLines)

            {

                if (line != string.Empty)

                {

                    string[] lineFragments = line.Split('\t');

                    foreach (string lineFragment in lineFragments)

                    {

                        // 'paste' in new value

                        gridView.ShowEditor();

                        if (gridView.ActiveEditor != null)

                        {

                            gridView.ActiveEditor.Text = lineFragment.Trim();

                            gridView.CloseEditor();

                        }

                        // move to next visible column if next visible column exists

                        if (gridView.FocusedColumn.VisibleIndex < gridView.VisibleColumns.Count - )

                        {

                            gridView.FocusedColumn = gridView.VisibleColumns[gridView.FocusedColumn.VisibleIndex + ];

                        }

                        else

                        {

                            break; //stop 'pasting' in focused row

                        }

                    }

                    // move to next row

                    if (gridView.FocusedRowHandle < gridView.RowCount - )

                    {

                        gridView.MoveNext();

                        gridView.FocusedColumn = gridView.VisibleColumns[originalFocusedVisibleColIndex];

                    }

                    else

                    {

                        break; //stop 'pasting' in the grid

                    }

                }

            }

        }

       /*You might consider pointing to kb articles that have the same paste functionality;

        * the article refrenced by Brendon adds rows when a paste action is invoked.

        * The solution presented in this thread pastes values into existing cells, relative to the focused cell.

        * And man do I hate when I see questions posted with no answers, like the one that started this thread.

        * Here is an update to my solution above, it only accomidates the column and edit types in my grid...

       */

        public static void GridViewClipboardPaste(GridView gridView)

        {

            gridView.ClearSorting();

            int originalFocusedVisibleColIndex = gridView.FocusedColumn.VisibleIndex;

            string[] clipboardTextLines = XtraGridHelper.ClipboardTextLines;

            gridView.BeginUpdate(); //lock grid for update

            // paste data from clipboard into row cells

            foreach (string line in clipboardTextLines)

            {

                if (line != string.Empty)

                {

                    string[] lineFragments = line.Split('\t');

                    foreach (string lineFragment in lineFragments)

                    {

                        string clipboardString = lineFragment.Trim();

                        // 'paste' in new value

                        try

                        {

                            RepositoryItem edit = gridView.FocusedColumn.ColumnEdit;

                            if (edit != null)

                            {

                                switch (edit.EditorTypeName)

                                {

                                    case "ImageComboBoxEdit":

                                        RepositoryItemImageComboBox imageComboBoxEdit = (RepositoryItemImageComboBox)edit;

                                        foreach (ImageComboBoxItem item in imageComboBoxEdit.Items)

                                        {

                                            if (item.Description.Equals(clipboardString))

                                            {

                                                gridView.SetRowCellValue(gridView.FocusedRowHandle, gridView.FocusedColumn, item.Value);

                                                break;

                                            }

                                        }

                                        break;

                                    case "CheckEdit":

                                        bool checkValue;

                                        if (Boolean.TryParse(clipboardString, out checkValue))

                                        {

                                            gridView.SetRowCellValue(gridView.FocusedRowHandle, gridView.FocusedColumn, checkValue);

                                        }

                                        break;

                                }

                            }

                            else

                            {

                                object newValue = null;

                                switch (gridView.FocusedColumn.ColumnType.Name)

                                {

                                    case "String":

                                        newValue = clipboardString;

                                        break;

                                    case "Boolean":

                                        newValue = Boolean.Parse(clipboardString);

                                        break;

                                    case "Int32":

                                        newValue = Int32.Parse(clipboardString);

                                        break;

                                }

                                gridView.SetRowCellValue(gridView.FocusedRowHandle, gridView.FocusedColumn, newValue);

                            }

                        }

                        catch (Exception ex)

                        {

                            //do nothing

                        }

                        // move to next visible column if next visible column exists

                        if (gridView.FocusedColumn.VisibleIndex < gridView.VisibleColumns.Count - )

                        {

                            gridView.FocusedColumn = gridView.VisibleColumns[gridView.FocusedColumn.VisibleIndex + ];

                        }

                        else

                        {

                            break; //stop 'pasting' in focused row

                        }

                    }

                    // move to next row

                    if (gridView.FocusedRowHandle < gridView.RowCount - )

                    {

                        gridView.MoveNext();

                        gridView.FocusedColumn = gridView.VisibleColumns[originalFocusedVisibleColIndex];

                    }

                    else

                    {

                        break; //stop 'pasting' in the grid

                    }

                }

            }

            gridView.EndUpdate(); //lock grid for update

        }

        ////////////////////////////////////////////////////////////////////

        private static string[] ClipboardTextLines

        {

            get

            {

                var clipboardText = ClipboardText;

                if (clipboardText.Contains(Environment.NewLine))

                {

                    return clipboardText.Trim().Replace(Environment.NewLine, "\n") .Split('\n');

                    // Replace CRLF with just CR then Split.

                }

                return clipboardText.Split(' ');

            }

        }

        // And this is what I did to speed it up:

        public static void PasteTable2(GridView gridView)

        {

            gridView.ClearSorting();

            var clipboardTextLines = new List<string>(ClipboardTextLines);

            // If we have too many rows trim the list.

            if (gridView.RowCount < clipboardTextLines.Count)

                clipboardTextLines = new List<string>(clipboardTextLines.GetRange(, gridView.RowCount));

            var currentCol = gridView.FocusedColumn;

            var numberOfColumnsThatCanBePastedTo = gridView.VisibleColumns.Count - currentCol.VisibleIndex;

            var pasteList = new List<List<string>>();

            foreach (var line in clipboardTextLines)

            {

                var rowValues = new List<string>(line.Split('\t'));

                // Make sure we don't overshoot the columns

                if(rowValues.Count > numberOfColumnsThatCanBePastedTo)

                    rowValues = new List<string>(rowValues.GetRange(, numberOfColumnsThatCanBePastedTo));

                pasteList.Add(rowValues);

            }

            var currentRow = gridView.FocusedRowHandle;

            for (int i = ; i < pasteList.Count; i++)

            {

                var pasteRow = currentRow + i;

                for (int j = ; j < pasteList[i].Count; j++)

                {

                    var pasteCol = currentCol.VisibleIndex + j;

                    gridView.SetRowCellValue(pasteRow, gridView.VisibleColumns[pasteCol], pasteList[i][j]);

                }

            }

        }

    }

}