Hi,
I want to copy the selected cells of my ultrawingrid to excel (using the ctrl+c / ctrl+v classic way).
Basically here are my grid's properties:
ultragrid.DisplayLayout.Override.RowSizing = RowSizing.AutoFixed currentBand.Layout.Override.CellClickAction = Infragistics.Win.UltraWinGrid.CellClickAction.CellSelect currentBand.Layout.Override.AllowMultiCellOperations = AllowMultiCellOperation.CopyWithHeaders currentBand.Layout.Override.HeaderClickAction = HeaderClickAction.Select ultragrid.DisplayLayout.Override.RowSelectors = Infragistics.Win.DefaultableBoolean.True ultragrid.DisplayLayout.Override.RowSelectorHeaderStyle = Infragistics.Win.UltraWinGrid.RowSelectorHeaderStyle.SeparateElement
ultragrid.DisplayLayout.Override.RowSizing = RowSizing.AutoFixed
currentBand.Layout.Override.CellClickAction = Infragistics.Win.UltraWinGrid.CellClickAction.CellSelect
currentBand.Layout.Override.AllowMultiCellOperations = AllowMultiCellOperation.CopyWithHeaders
currentBand.Layout.Override.HeaderClickAction = HeaderClickAction.Select
ultragrid.DisplayLayout.Override.RowSelectors = Infragistics.Win.DefaultableBoolean.True
ultragrid.DisplayLayout.Override.RowSelectorHeaderStyle = Infragistics.Win.UltraWinGrid.RowSelectorHeaderStyle.SeparateElement
All works fine so far when I select a range of cells, or when I select some rows with the row selectors.
I mean, the whole selected data can be copied/paste to excel, with ctrl+c/ ctrl+v.
Now, when I select a set of columns, clicking on the headers, then all the cells of these columns are selected (which is OK) BUT when I ctrl+c/ctrl+v to excel, only the single active cell is copied in the workbook.
I believe it's bug (known ? corrected ? I'm working with v9.1.20091.2094) but may be I have to set a mysterious property somewhere.
A clever solution. So you are basically handling the copy yourself before the grid can do it. Makes sense.
You probably don't have to turn off AllowMultiCellOperation, but instead just set e.Handled to true so that the grid knows you already handled the keystroke and that it should not handle it.
I'm not thrilled about hiding and then showing columns like you are doing. It might cause a flicker and it has a small potential to affect the grid's layout in some way. You could avoid a flicker using BeginUpdate/EndUpdate around your code and I cannot think of any layout problems at the moment, but it's something to watch out for.
Hi Mike
I found a way to avoid looping through the rows. It's a bit tricky but now I only loop through the columns, what is much, much faster.
The idea is to hide the unselected columns, then to copy all rows and finally to re-show the unselected columns.
Here's the code:
Dim columnCopyInProgress As Boolean = False Dim formerAllowMultiCellOperations As AllowMultiCellOperation = AllowMultiCellOperation.Default Private Sub UltraGrid5_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles UltraGrid5.KeyDown 'crtl+c' event If e.KeyCode = Keys.C And e.Control = True Then Dim ultragrid As UltraGrid = CType(sender, UltraGrid) Dim currentBand = ultragrid.DisplayLayout.Bands(0) If ultragrid.Selected.Columns.Count > 0 Then 'stop refresh ultragrid.BeginUpdate() 'get the list of selected columns Dim selectedColumnHeaders As New List(Of ColumnHeader) Dim selectedColumns As New List(Of UltraGridColumn) For Each columnHeader As ColumnHeader In ultragrid.Selected.Columns selectedColumnHeaders.Add(columnHeader) selectedColumns.Add(columnHeader.Column) Next 'will contain the list of columns displayed and not selected Dim formerDisplayedColumns As New List(Of UltraGridColumn) 'hide the unselected columns For Each column As UltraGridColumn In currentBand.Columns If column.Hidden = False And Not selectedColumns.Contains(column) Then formerDisplayedColumns.Add(column) column.Hidden = True End If Next 'select all cells of remaining columns ultragrid.Selected.Rows.Clear() ultragrid.Selected.Rows.AddRange(ultragrid.Rows.GetFilteredInNonGroupByRows) 'Copy the cell to the clipboard Me.UltraGrid5.PerformAction(UltraGridAction.Copy) 'reset display ultragrid.Selected.Rows.Clear() For Each column As UltraGridColumn In formerDisplayedColumns 'currentBand.Columns 'formerDisplayedColumns column.Hidden = False Next ultragrid.Selected.Columns.AddRange(selectedColumnHeaders.ToArray) ultragrid.EndUpdate() 'prevent copy operation to be automatically performed columnCopyInProgress = True formerAllowMultiCellOperations = ultragrid.DisplayLayout.Override.AllowMultiCellOperations ultragrid.DisplayLayout.Override.AllowMultiCellOperations = AllowMultiCellOperation.None End If End If End Sub Private Sub UltraGrid5_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles UltraGrid5.KeyUp If columnCopyInProgress Then 'Re-allow copy operations columnCopyInProgress = False Dim ultragrid As UltraGrid = CType(sender, UltraGrid) ultragrid.DisplayLayout.Override.AllowMultiCellOperations = formerAllowMultiCellOperations End If End Sub
Dim columnCopyInProgress As Boolean = False
Dim formerAllowMultiCellOperations As AllowMultiCellOperation = AllowMultiCellOperation.Default
Private Sub UltraGrid5_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles UltraGrid5.KeyDown
'crtl+c' event
If e.KeyCode = Keys.C And e.Control = True Then
Dim ultragrid As UltraGrid = CType(sender, UltraGrid)
Dim currentBand = ultragrid.DisplayLayout.Bands(0)
If ultragrid.Selected.Columns.Count > 0 Then
'stop refresh
ultragrid.BeginUpdate()
'get the list of selected columns
Dim selectedColumnHeaders As New List(Of ColumnHeader)
Dim selectedColumns As New List(Of UltraGridColumn)
For Each columnHeader As ColumnHeader In ultragrid.Selected.Columns
selectedColumnHeaders.Add(columnHeader)
selectedColumns.Add(columnHeader.Column)
Next
'will contain the list of columns displayed and not selected
Dim formerDisplayedColumns As New List(Of UltraGridColumn)
'hide the unselected columns
For Each column As UltraGridColumn In currentBand.Columns
If column.Hidden = False And Not selectedColumns.Contains(column) Then
formerDisplayedColumns.Add(column)
column.Hidden = True
End If
'select all cells of remaining columns
ultragrid.Selected.Rows.Clear()
ultragrid.Selected.Rows.AddRange(ultragrid.Rows.GetFilteredInNonGroupByRows)
'Copy the cell to the clipboard
Me.UltraGrid5.PerformAction(UltraGridAction.Copy)
'reset display
For Each column As UltraGridColumn In formerDisplayedColumns 'currentBand.Columns 'formerDisplayedColumns
column.Hidden = False
ultragrid.Selected.Columns.AddRange(selectedColumnHeaders.ToArray)
ultragrid.EndUpdate()
'prevent copy operation to be automatically performed
columnCopyInProgress = True
formerAllowMultiCellOperations = ultragrid.DisplayLayout.Override.AllowMultiCellOperations
ultragrid.DisplayLayout.Override.AllowMultiCellOperations = AllowMultiCellOperation.None
End Sub
Private Sub UltraGrid5_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles UltraGrid5.KeyUp
If columnCopyInProgress Then
'Re-allow copy operations
columnCopyInProgress = False
ultragrid.DisplayLayout.Override.AllowMultiCellOperations = formerAllowMultiCellOperations
The only difficulty is that I had to temporarily set the AllowMultiCellOperations property to None to prevent the grid to automatically manage the event. In this case, I reset the property on KeyUp event.
Do you see any problems with this solution ? May be you can even use it to implement it in a safer way in a next version of the ultragrid. :)
How many rows do you have?
There's no way to avoid looping through the grid rows - which is probably why selecting a column does not select all of the cells in the first place. :)
But Petar's code is not very efficient. It's not necessary to loop through every column, since you have a Selected.Columns collection which contains only the columns you need. And calling Contains on the collection every time will end up looping through the columns again, so that's very inefficient.Also, setting the Selected property on each cell individually is a bad idea - it's much better to add them all at once using AddRange.
Here's how I would do it:
private void ultraGrid1_BeforeSelectChange(object sender, BeforeSelectChangeEventArgs e) { UltraGrid grid = (UltraGrid)sender; if (e.NewSelections.Columns.Count > 0) { List<UltraGridCell> selectedCells = new List<UltraGridCell>(); UltraGridRow[] rows = grid.Rows.GetFilteredInNonGroupByRows(); foreach (UltraGridRow row in rows) { foreach (Infragistics.Win.UltraWinGrid.ColumnHeader columnHeader in e.NewSelections.Columns) { selectedCells.Add(row.Cells[columnHeader.Column]); } } if (selectedCells.Count > 0) { e.Cancel = true; grid.Selected.Cells.Clear(); grid.Selected.Cells.AddRange(selectedCells.ToArray()); } } }
I tested this with 10,000 rows and there was a small delay after clicking the mouse on the column header, but I don't see any way to make it any faster than that. And 10,000 rows is probably more than any human user could reasonably deal with, anyway.
I've just test the proposed solution.
Ok it works but the double loop (for each row, for each cell in row) takes a very long time (a non-acceptable time actually).
So, do you know the most efficient way to get all the cells of columns ?
Thanks for link.
So, the point is that the Selected.Columns property does not contain any cells, while Selected.Cells and Selected.Rows properties reference the cells.