Search results matching tag 'Excel'

How To Add Excel Like Selection of DataCells In Xceed DataGridControl

Michel [Xceed] — Wed, 28 Sep 2011 00:45:00 GMT

Recently, we had a question here at technical support asking if we had "Excel Like Selection of DataCells".

Well... we don't! When SelectionUnit is set to Cell on DataGridControl it is possible to make your selection on a cell-by-cell basis and to select multiple cells at once by Shift Clicking but still, that is not what some clients expect, especially if they are used to an Excel-like selection. That is, clicking on one cell, holding down the mouse key and as you move the mouse around, the cells in the rectangle formed by the clicked cell and the current location of the mouse should dynamically become selected and the content should scroll as you move your mouse to the edges of the DataGridControl .

But of course, nothing is impossible! This behavior can be achieved but it requires a little extra work.

This post will show how to customize the DataGridControl in order to add this functionality. The demo application is provided below.

First thing we need to do is to create a custom DataGridControl, in this post I will name my class ExelSelectionDataGrid which inherits from DataGridControl.

We will keep a reference to the first clicked cell and the cell that lies under the mouse as the mouse moves. We will then dynamically calculate and add the ranges of cells that lie in the rectangle bounded by the first clicked cell and the cell beneath the mouse to the SelectedCellRanges collection of the datagrid instance.

For that, we would need to override the following:

OnPreviewMouseLeftButtonDown In order to get the keep track of the first clicked cell

OnPreviewMouseMove where we get the cell that is currently under the mouse, find the ranges between the first cell and the current cell and update the selected ranges, and finally scroll the DataGridControl if we are close to any of the edges if possible.

OnPreviewMouseLeftButtonUp only to set our selection flag to false.

Getting the cell under the mouse can easily be acheived by using the VisualTreeHelperClass. This is how I used it:

private DataCell getDataCellUnderMouse(MouseEventArgs e)

{

HitTestResult result = VisualTreeHelper.HitTest(this, e.MouseDevice.GetPosition(this));

DependencyObject obj = result.VisualHit;

var ctype = typeof(DataCell);

if (obj != null)

{

var otype = obj.GetType();

while (obj != null && (obj as DataCell) == null)

{

obj = VisualTreeHelper.GetParent(obj);

}

return obj as DataCell;

}

Manually scrolling the datagrid would require access to the scroll viewer, this can be reached through the following:

ScrollViewer sv = (ScrollViewer)this.Template.FindName("PART_ScrollViewer", this);

So this is the entire class:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Xceed.Wpf.DataGrid;

using System.Windows;

using System.Windows.Controls;

using System.Windows.Input;

using System.Diagnostics;

using System.Windows.Media;

using Xceed.Wpf.DataGrid.Views;

namespace ExcelLikeSelectionDataGrid

{

class ExelSelectionDataGrid : DataGridControl

{

public ExelSelectionDataGrid()

{

this.Loaded += new RoutedEventHandler(CustomDataGridControl_Loaded);

}

void CustomDataGridControl_Loaded(object sender, RoutedEventArgs e)

{

sv = (ScrollViewer)this.Template.FindName("PART_ScrollViewer", this);

}

ScrollViewer sv;

int firstItemIndex;

int secondItemIndex;

int firstColumnIndex;

int secondColumnIndex;

protected override void OnPreviewMouseMove(MouseEventArgs e)

{

base.OnPreviewMouseMove(e);

if (isSelecting)

{

DataCell cellUnderMouse = getDataCellUnderMouse(e);

if (cellUnderMouse != null)

{

int columnIndex = this.Columns[cellUnderMouse.FieldName].VisiblePosition;

int itemIndex = this.Items.IndexOf(cellUnderMouse.DataContext);

if ((columnIndex != secondColumnIndex || itemIndex != secondItemIndex) && columnIndex >= 0 && itemIndex >= 0)

{

secondColumnIndex = columnIndex;

secondItemIndex = itemIndex;

UpdateSelection();

}

Point mousePosition = Mouse.GetPosition(this);

if (mousePosition.Y > (this.ActualHeight - 10))

{

double calculatedVerticalOffset = sv.VerticalOffset + 3 * (this.View as TableflowView).ContainerHeight;

sv.ScrollToVerticalOffset(calculatedVerticalOffset);

}

if (mousePosition.X > (this.ActualWidth - 10))

{

double calculatedHorizontalOffset = sv.HorizontalOffset + 3 * 50.0;

sv.ScrollToHorizontalOffset(calculatedHorizontalOffset);

}

if (mousePosition.Y < (10))

{

double calculatedVerticalOffset = sv.VerticalOffset - 3 * (this.View as TableflowView).ContainerHeight;

sv.ScrollToVerticalOffset(calculatedVerticalOffset);

}

if (mousePosition.X < 10)

{

double calculatedHorizontalOffset = sv.HorizontalOffset - 3 * 50.0;

sv.ScrollToHorizontalOffset(calculatedHorizontalOffset);

}

protected override void OnPreviewMouseLeftButtonDown(MouseButtonEventArgs e)

{

base.OnPreviewMouseRightButtonDown(e);

DataCell clickedCell = getDataCellUnderMouse(e);

if (clickedCell != null)

{

isSelecting = true;

firstColumnIndex = this.Columns[clickedCell.FieldName].VisiblePosition;

firstItemIndex = this.Items.IndexOf(clickedCell.DataContext);

}

protected override void OnPreviewMouseLeftButtonUp(MouseButtonEventArgs e)

{

isSelecting = false;

}

private void UpdateSelection()

{

if (!(Keyboard.Modifiers == ModifierKeys.Shift))

this.SelectedCellRanges.Clear();

this.SelectedCellRanges.Add(new SelectionCellRange(firstItemIndex, firstColumnIndex, secondItemIndex, secondColumnIndex));

}

bool isSelecting = false;

private DataCell getDataCellUnderMouse(MouseEventArgs e)

{

HitTestResult result = VisualTreeHelper.HitTest(this, e.MouseDevice.GetPosition(this));

DependencyObject obj = result.VisualHit;

var ctype = typeof(DataCell);

if (obj != null)

{

var otype = obj.GetType();

while (obj != null && (obj as DataCell) == null)

{

obj = VisualTreeHelper.GetParent(obj);

}

return obj as DataCell;

}

I will not go through the code details here. It should be straightforward to understand once you look at it. You can download a sample application here.

Please, add your comments and suggestions below. Let me know if you find a better way to acheive this or if you find any bugs in my code ;)

Re: Roadmap 2009

Pratik — Thu, 27 Aug 2009 12:36:32 GMT

hi can u add one More Feature to RoadMap 2009

==>First Level Formatting to Exported Excel (Auto Fit Columns,Header Cells Background Color and Cell Background Color )(You can Refer teleric WPF Grid Export Functionality for the same)

specially when we export grouped Grid Data

like if my grid is having 3 columns ID, Name , Status, and when I have Grouped with Status Currently it is Giving like below

Status:<Status1>(1 Item )

ID1 Name1 Status1

Status:<Status2>(2 Item )

ID2 Name2 Status2

ID3 Name3 Status2

But as per my View The Ideal Format of Above Should Be some thing like belove

(+ -)Status:<Status1>(1 Item )

ID1 Name1 Status1

(+ -)Status:<Status2>(2 Item )

ID2 Name2 Status2

ID3 Name3 Status2

means Indented and nothing in Column of Grouping, ID Starts from second Column , (If two Grouping is there then ID starts with 3rd Column)

and If Possible "Row Outline" of excel in place of (+ -)

Export to excel in .xls format not in XML

Supriya — Mon, 03 Aug 2009 11:15:28 GMT

Hi,

I am working on Xceed Grid for .Net 3.7. I want to export the data to excel, not in XML but in xls format.

I followed some previous posts about this topic and saw a comment, "This feature is on our request list but currently not available in the grid. " at http://xceed.com/CS/forums/thread/9286.aspx.

I just want to confirm whether this is available in any latest service packs or not. And is there any other way to achieve this?

Adding CellContentTemplate (or similar) to DataGridItemProperty in DetailDescriptions / Excel export

Neo — Thu, 30 Jul 2009 16:56:22 GMT

OK, I resolved the matter of hiding columns in the DetailDescription by simply adding the Browsable(false) attribute to the properties I don't wish to show.

However, I still need to apply a CellContentTemplate to my DataGridItemProperty objects in my DetailDescription. To clarify my question, I have the following DataGridControl defined with a PropertyDetailDescription. The MyProperty Column in the main grid uses a custom CellContentTemplate (in order to format the decimal value in the cell and right-align it). How do I assign the same custom CellContentTemplate to the MyDetailProperty column in the PropertyDetailDescription?

<igDock:ContentPane>
<igDock:ContentPane.Resources>
    <xcdg:DataGridCollectionViewSource x:Key="mySource"
                                       Source="{Binding RelativeSource={RelativeSource AncestorType={x:Type local:MyClass}}, Path=MyData}">

      <xcdg:DataGridCollectionViewSource.DetailDescriptions>
        <xcdg:PropertyDetailDescription RelationName="MyDetailData">
          <xcdg:PropertyDetailDescription.ItemProperties>
            <xcdg:DataGridItemProperty Name="MyDetailProperty" DataType="{x:Type sys:Decimal}" />
          </xcdg:PropertyDetailDescription.ItemProperties>
        </xcdg:PropertyDetailDescription>
      </xcdg:DataGridCollectionViewSource.DetailDescriptions>
    </xcdg:DataGridCollectionViewSource>
</igDock:ContentPane.Resources>

<xcdg:DataGridControl ItemsSource="{Binding Source={StaticResource mySource}}"
                        AutoCreateDetailConfigurations="True">
    <xcdg:DataGridControl.Columns>
      <xcdg:Column FieldName="MyProperty" CellContentTemplate="{StaticResource myCellContentTemplate}" />
    </xcdg:DataGridControl.Columns>
</xcdg:DataGridControl>
</igDock:ContentPane>

Also, I still need to know how to export the contents of the detail rows to Excel. The ExportToExcel() function of DataGridControl only exports the master rows.

Thanks,
Jason

Re: New Excel Export Funtionality for Xceed WPF 3.1 Grid

Jenny [Xceed] — Mon, 26 Jan 2009 16:18:46 GMT

Version 3.1 exposes an ExportToExcel property as well as an Exporter class, which can both be used to export the content of the grid to Excel. You can refer to the Exporting topic in the documentation for more information and examples.

Re: Roadmap for Q4 2008 and beyond

Ryan — Thu, 18 Dec 2008 00:14:14 GMT

I need the "Easier support for unbound columns" right now. I'm surprised that this feature isn't in more WPF grids, as I have used it SO much in WinForms DataGridView and infragistics UltraGrid for WinForms. I guess I'll be creating bogus properties on my data objects for now... Hopefully there will be a good example on how to do this when it is realeased.

I'm glad to see the Export to Excell is coming soon.

It would also be nice if the Xceed Grid for WPF did exporting to PDF!

Re: Move a row in a WPF DataGrid

Odi [Xceed] — Wed, 17 Dec 2008 02:50:30 GMT

This isn't a feature supported by the datagrid at the moment, but it is something that we are considering as part of a group of "Excel-like" features. How is your datagrid to be wired to the datasource, and what would you like the datagrid to do to the datasource if a row is dragged to a new position?

By any chance, are you asking for this feature so some rows can be "pinned" to be always visible?

Spreadsheet / Excel-like capabilities discussion

Odi [Xceed] — Fri, 07 Nov 2008 20:18:28 GMT

With development of version 3.1 of Xceed DataGrid for WPF well on schedule with the features and timeframe outlined in the roadmap, some of the team has already begun working on the next batch of features suggested by customers in the roadmap thread, by email and at PDC 2008.

There's a class of features being requested that we consider to be spreadsheet-type features. These aren't features typically associated with datagrids, which are specifically designed for displaying and editing rows of data each with the same fields ("tabular data"), and to do so intuitively and efficiently. These features seem to be more suitable for Excel-like spreadsheet controls where cells can contain anything in any location, but aren't able to provide an excellent datagrid-like user experience for dealing with tabular data.

That said, it seems that datagrid users, while still dealing with tabular data and not needing a true free-form Excel-like control, still request these spreadsheet-type features. In order to fulfil your requests for these features, we need to better understand exactly what your needs are regarding these features, why you need them (your concrete usage scenario), how you would like to see them implemented, and how important each one is. We'll then be in a better position to see if we can accomodate the needs and properly build in these features into Xceed DataGrid for WPF.

Here's the list of some common requests of this type, to begin the discussion, please provide your feedback on the ones that are important to you, and tell us if we are missing any.

- Select multiple individual cells, or square regions or cells (for copying? editing? pasting?)
- Paste multiple cells into the grid
- Merge cells horizontally
- Merge cells vertically
- Automatically merge cells vertically when their content is the same
- Drag fill / autofill (like Excel, drag lower right of a cell to copy its contents to other cells)
- Drag rows to move them up or down
- Let end-user freeze rows
- Splitter to split grid into two views
- More?

Thanks.
Odi

Re: Export Functions

Jenny [Xceed] — Thu, 21 Aug 2008 12:54:01 GMT

Yes, however I do not have an estimate of when. In the meantime, you could take a look at this example (http://xceed.com/CS/files/folders/jenny/entry11762.aspx), which demonstrates how to implement cut and paste to and from Excel.

Re: Drag and Drop Examples for version 3.0

Jenny [Xceed] — Mon, 18 Aug 2008 20:14:51 GMT

I have updated the Excel Copy/Paste example to use version 3.0.