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

XAF 如何从Excel复制多个单元格内容到GridView
2012年04月11日 ⁄ 综合 ⁄ 共 10998字    ⁄ 字号 小 中 大 ⁄ 评论关闭

how to paste some excel content to xtragrid?

1.相關資料

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

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

 2.調用方法：

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);
        }
    }
}

 

 

3.實現代碼：

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('	');
                    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 - 1)
                        {
                            gridView.FocusedColumn = gridView.VisibleColumns[gridView.FocusedColumn.VisibleIndex + 1];
                        }
                        else
                        {
                            break; //stop 'pasting' in focused row
                        }
                    }
                    // move to next row
                    if (gridView.FocusedRowHandle < gridView.RowCount - 1)
                    {
                        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('	');
                    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 - 1)
                        {
                            gridView.FocusedColumn = gridView.VisibleColumns[gridView.FocusedColumn.VisibleIndex + 1];
                        }
                        else
                        {
                            break; //stop 'pasting' in focused row
                        }
                    }
                    // move to next row
                    if (gridView.FocusedRowHandle < gridView.RowCount - 1)
                    {
                        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, "
") .Split('
');  
                    // 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(0, 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('	'));
                // Make sure we don't overshoot the columns
                if(rowValues.Count > numberOfColumnsThatCanBePastedTo)
                    rowValues = new List<string>(rowValues.GetRange(0, numberOfColumnsThatCanBePastedTo));
                pasteList.Add(rowValues);
            }
            var currentRow = gridView.FocusedRowHandle;
            for (int i = 0; i < pasteList.Count; i++)
            {
                var pasteRow = currentRow + i;
                for (int j = 0; j < pasteList[i].Count; j++)
                {
                    var pasteCol = currentCol.VisibleIndex + j;
                    gridView.SetRowCellValue(pasteRow, gridView.VisibleColumns[pasteCol], pasteList[i][j]);
                }
            }
        }
    }
}