Customizing WPF Grid

[Dhanasekaran Veluchamy]

Hi,
I am evaluating the Syncfusion WPF grid control. My requirement are,
#1. Grid columns should fill the parent control's Width and Column each should be resizable (as it is now).
#2. Currently If we click on the first cell in a row it selects the entire Row; we want like this, clicking anywhere in the grid should Highlight the entire Row. Highlighting color & Text Color should be configurable (Selection mode is Row level)
#3. Grid line color should be configurable
#4. Grid will display nearly 50k records, we would like to implement the VirtualGrid method(give a sample to do that) and we will be editing any values in the grid it will be just static cell.
#5. Vertical scroll bar should appear if the number of records exceeds the visible region.
#6. We would be displaying the summary Row as Frozen row in the bottom of the grid to display sum of the values being displayed in that column. (this row SHOULD NOT SCROLL if we scroll down the scroll bar)
#7. Export grid data to Excel file. What is the best way to implement this, considering if we impliment VirtualGrid.

Let me know if these are possible? Please find the attachment for clear understanding.

Thanks in advance,
Dhana



Grid_Layout_88692012.zip

[Administrator]

We curently are not shipping a databound control. If you want to do what you described now, you can use our GridControl in virtual mode. Additionally, to handle the summaries requirement, you could use our grouping engine and then just virtually bind the grouping engine to the GridControl. Below is a link to a sample that does this.

1) To handle this requirement you would need to catch a couple of event and adjust the column widths to fill your needs. The sample below has code to make the right most column expand to fill the client area if needed.

2) You set the ListBoxSelectionMode to get this behavior as shown in the sample. To control the hightlight color requires you set a brush, and to control the font requires you to handle a couple of event. The sample shows a pink hightligh with bold font for the selected row.

3)You can control the gridline color by setting border style information on the grid.Model.TableStyle object. The sample shows red borders.

4)The sample displays 50K rows in the grid with the summaries calculated in about 1.2 seconds after the data has been downloaded. You can time grid load times for fewer rows by setting the count field in the window constructor code and checking the load time diplayed in teh window title.

5) To get scrollbars, you should host the grid in a ScrollViewer object and set its scrollbarvisibilty properties to auto (as done in the sample xaml).

6) The sample shows a frozen footer row displaying the summary values.

7) One way to do this is to use our XlsIO product and just export the data directly using that product. You can see samples of using the WPF WlsIO library in the WPF sample browser at
...\Documents\Syncfusion\EssentialStudio\7.1.0.21\WPF\XlsIO.WPF\Samples\3.5\WindowsSamples\






GroupingSample_d3c99e0b.zip

[Dhanasekaran Veluchamy]

Thanks Clay Burch for your quick response.

I have few more questions here

#1. I don't want just fill the last column to fill the width, instead each column width should be in percentage so that if we resize the application the column widths should adjust according to new window size. Like we specify the WPF Grid ColumnDefinition Width, (i.e. Width="50*")
#2. Is it possible to have custom class for Grid Model, which is derived from the Syncfusion's GridModel class, so that i can implement some common logic that can be used in our application.

Thanks in advance,
Dhana

[Administrator]

Below is the sample back with a new class, ColumnWidthsSizingHelper added. The ColumnStyle class also has a PercentWidth property added. If you set PercentWidth on a column, then that column will always be sized so it occupies the percentage of the left over width after the columns which do not have a PercentWidth set have taken into account where they use the Width set in the ColumnWidths collection. The sample has percentages set for the Name and Col2 columns.

Regarding using a derived GridModel, I do not see an obvious way to do this. I will have to consult with the Grid architect to see if he wants to allow this, and if so, how it might be done. I expect we will have to tweak our code base to allow this. In the meantime, you might consider accumulating your customizations in a helper class, and then just use that to manage you customization. This will be doable if you can use events to manage the customizations and do not have to override any virtual methods. This is how the grouping helper and the sizing helper in the sample works.



GroupingSample_3e0b0af4.zip

[Administrator]

Regarding using a derived GridModel, we have corrected an issue in our code base that allows code like this to work.

public Window1()
        {
            InitializeComponent();
            grid.Model = new MyGridModel();
           

// reset of your code.....
           //  grid.Model.RowCount = 40;
           // grid.Model.ColumnCount = 10;
        }

This correction should be included in next week's development build.

[Dhanasekaran Veluchamy]

#1. The Column Width helper class works fine if I resize the Column's width then it goes back to the original position (the resized column should maintain its width, if user wants expand the column).
#2. The ToolTip should show for the cell that has the truncated text (i.e. if the cell text is "nam...", it should show the tooltip on hovering mouse over that cell as "name0")
#3. Is there anyway that we can identify the last visible row of the grid. (gridControl1.ViewLayout.LastVisibleRow as in the Syncfusion Windows GridControl)

[Administrator]

1) If you only want the percent sizing applied once as the grid is initially displayed, then one way you can do this is to add a field, bool applSizing, to control when the ApplySizing is done.

        bool applySizing = true;

        public void EnableSizing()
        {
            applySizing = true;
        }

        public void ApplySizes()
        {
            if (this.viewer.ActualWidth == 0)
                return; //not sized yet
       
             if (!applySizing)
                return;

            applySizing = false;

            //continue with currentcode

Later, if you want the grid to redo the percent sizing for some reason, you can call sizingHelper.EnableSizing() followed by sizingHelper.ApplySizies().

2) There is no support for this yet. Currently, you would have to code something yourself to manage this.

3) Try these methods to see if they give you what you want:

    int index = grid.ScrollRows.LastBodyVisibleLineIndex;
    VisibleLineInfo info = grid.ScrollRows.LastBodyVisibleLine;

[Dhanasekaran Veluchamy]

Question #3, neither
int index = grid.ScrollRows.LastBodyVisibleLineIndex;
nor
VisibleLineInfo info = grid.ScrollRows.LastBodyVisibleLine;
gives the last visible row of the grid, instead it gives the last row. Kindly let me know is there any way that i can get the last visible row index (like gridControl1.ViewLayout.LastVisibleRow as in Syncfusion Windows GridControl)

[Dhanasekaran Veluchamy]

Also, i set the highlight color like this
grid.HighlightBrush = new SolidColorBrush( Color.FromArgb( 0xFF, 0xDF, 0xB3, 0x2A ) );

the highlighting color overshadows the text being displayed in the grid cell. But if we reduce the alpha quotient it the text will be visible but i do not want to change the highlight color

Is there any way to accomplish this?

[Administrator]

The grid.ScrollRows.LastBodyVisibleLineIndex property seems to work for me in the sample posted below. What are you doing differently?

The default way of handling the highlighting is to draw a panel over the selected cells. This does require using alpha blending to allow the text to bleed through.

If you do not want to do it this way, you can handle the PrepareRenderCell event and there, explicitly set the Background for any selected cell. The sample below shows such code.





WpfApplication10_9574929d.zip

[Dhanasekaran Veluchamy]

Thanks for your kind support. I have followed what have you done and it worked.

I have an issue in selection changed event. Initially this event is called two times for selection added. Is there any work around to control this to be raised one time?
For further selection changes it is being called three times one for selection removal & other two for selection add (for same record selection).

Kindly let me know how to get this done

[Administrator]

You can check the value of e.Reason in the vent handler code. I think MouseUp will only happen once for each contiguous mouse selection.

  void Model_SelectionChanged(object sender, GridSelectionChangedEventArgs e)
        {
            if (e.Reason == GridSelectionReason.MouseUp)
            {
                //do something once per contiguous selection
            }
        }

[Administrator]

Under the GridDataControl I can see the QuerySummaryCustomAggregate on the model, however, I cant see how to implement the customisation under GridControl

Could you provide an update to this example so that it creates a summarydescriptor based on SummaryType.Custom ?

Thanks,
Heath

[Administrator]

QuerySummaryCustomAggregate is not supported in a GridControl, only in a GridDataControl.

For a GridControl, there are several ways to populate a footer row with some kind of summary calculation. If your summary can be expressed as an excel-like formula supported by the GridFormulaEngine available in a GridControl, then you can use code like this to set up a footer row that holds a summary cell. In this code, the footer cell in column 2 holds the sum of values in column 2 and footer cell in column 3 holds the average.

            grid.Model.RowHeights.FooterLineCount = 1;
            int footerRowIndex = grid.Model.RowCount - 1;
            int columnIndex2 = 2;
            int columnIndex3 = 3;

            grid.Model[footerRowIndex, columnIndex2].CellType = "FormulaCell";
            grid.Model[footerRowIndex, columnIndex2].Text = string.Format("=Sum({1}1:{1}{0})", footerRowIndex - 1, GridRangeInfo.GetAlphaLabel(columnIndex2)); //sum of all values in column B
            grid.Model[footerRowIndex, columnIndex3].CellType = "FormulaCell";
            grid.Model[footerRowIndex, columnIndex3].Text = string.Format("=Average({1}1:{1}{0})", footerRowIndex - 1, GridRangeInfo.GetAlphaLabel(columnIndex3)); //average of all values in column C

If you really want to have an event where you can dynamically provide a special custom summary value on demand, then you can do this using the grid.Model.QueryCellInfo event. You also have to use some kind of event to make sure new summaries are computed when values in the GridControl change. One event you can use for this is the grid.Mode.CommitCellInfo event.

Attached is a sample that shows how to use both these techniques to include a summary row in a GridControl. In this sample, the summary for the first column is populated with a custom summary using QueryCellInfo and CommitCellInfo. Summaries for columns 2 and 3 are done using excel-like formulas. If you use the excel-like formula approach, then the QueryCellInfo and CommitCellInfo events are not needed.



CS_a0ba77c5.zip

[Administrator]

Thanks for your sample.

The reasoning behind my initial question is I'm attempting to have three custom rows in the footer.

1. Total - this is the sum of all preceding values in the table.
2. Change - an arbitary numeric value keyed by a user, default 0.
3. Actual - this will be Total - Change

Is this something that the grid lends itself to be able to do?

Thanks,
Heath

[Administrator]

You can use either technique I mentioned above to do something like this. Here is the sample back using excel-like formulas as you described with 3 summary rows, the first being a total, the second being something the user types, and the third being the difference of the first two.



CS_54e728b6.zip

[Administrator]

Clay,

Thanks for your help, your samples are quite good.

My grid is almost working, however, I needed to add three rows to stop the last three from being overwritten:
this.dataGrid.Model.InsertRows(gridRowCount, 3);

Coupled with the binding of the grid based on a propertychanged event in my presenter (the datatable is generated at runtime), using, this.dataGrid.DataContext = my DataTable, I'm finding:
a) three new rows being added with the background color i've set (as expected)
b) occasionally some data being in them
c) any visible data disappearing when I double click on the cell
d) no formulas being visible at all.

Is this because I'm using a datacontext and need to subscribe to a different event to add the additional rows?

Thanks,
Heath

[Administrator]

Perhaps some code may be useful, here's the code currently plugged into the Model Initialized event to ensure that grid.Model.RowCount > the number of footer items I need.

this.dataGrid.Model.Initialized += delegate(object sender, EventArgs e)
{
this.dataGrid.Model.RowHeights.FooterLineCount = 3;
this.dataGrid.Model.FooterRows = 3;

int gridRowCount = this.dataGrid.Model.RowCount;
this.dataGrid.Model.InsertRows(gridRowCount, 3);
gridRowCount = this.dataGrid.Model.RowCount;
int footerIndex = gridRowCount - 3;

// TOTAL row
this.dataGrid.Model[footerIndex, 1].Text = "TOTAL";
this.dataGrid.Model[footerIndex, 1].HorizontalAlignment = System.Windows.HorizontalAlignment.Right;
for (int i = 0; i < this.ThisPresenter.Items.Columns.Count; i++)
{
DataColumn column = this.ThisPresenter.Items.Columns[i];
if (column.DataType == typeof(decimal))
{
this.dataGrid.Model[footerIndex, i + 1].Background = Brushes.LightSkyBlue;
this.dataGrid.Model[footerIndex, i + 1].CellType = "FormulaCell";
this.dataGrid.Model[footerIndex, i + 1].Text = string.Format("=Sum({1}1:{1}{0})", footerIndex - 1, GridRangeInfo.GetAlphaLabel(i));
}
}


// Target Change
footerIndex++;
this.dataGrid.Model[footerIndex, 1].Text = "Target Change";
this.dataGrid.Model[footerIndex, 1].HorizontalAlignment = System.Windows.HorizontalAlignment.Right;
for (int i = 0; i < this.ThisPresenter.Items.Columns.Count; i++)
{
DataColumn column = this.ThisPresenter.Items.Columns[i];
if (column.DataType == typeof(decimal))
{
this.dataGrid.Model[footerIndex, i + 1].Background = Brushes.LightSlateGray;
this.dataGrid.Model[footerIndex, i + 1].Text = "0";
}
}

// Actual Change
footerIndex++;
this.dataGrid.Model[footerIndex, 1].Text = "Actual Change";
this.dataGrid.Model[footerIndex, 1].HorizontalAlignment = System.Windows.HorizontalAlignment.Right;
for (int i = 0; i < this.ThisPresenter.Items.Columns.Count; i++)
{
DataColumn column = this.ThisPresenter.Items.Columns[i];
if (column.DataType == typeof(decimal))
{

this.dataGrid.Model[footerIndex, i + 1].Background = Brushes.LightSeaGreen;
this.dataGrid.Model[footerIndex, i + 1].CellType = "FormulaCell";
this.dataGrid.Model[footerIndex, i + 1].Text = string.Format("={0}{1} - {0}{2}", GridRangeInfo.GetAlphaLabel(i), footerIndex - 2, footerIndex - 1);
}
}
};

[Administrator]

From your original question, I got the impression that you were trying to use a GridControl, and not a GridDataControl. You will not be able to easily use the GridFormulaEngine (excel-like formula support) in the bound GridDataControl. The reason for this is that the formula engine needs to have access to an underlying GridData object that holds the style information on a cell by cell basis. The bound GridDataControl does not have such as object.

So, the suggestion of using excel-like formulas will not work in a GridDataControl. Another sticky point is that it seems you as using a DataTable datasource, and this is not IQueryable which is required to use the built-in summary support within the GridDataControl.

But you should be able to use QueryCellInfo and CommitCellInfo to add three unbound footer rows and manage thing directly. It does require handling several events to catch the changes in the different kinds of cells, (the bound cells, and the unbound footers). Here is sample.







DataGridSummaries_3c053cd5.zip

[Administrator]

Clay,

Thanks for your help. I have migrated over to the GridControl and have it all up and working. This grid is working very nice for us with freezable columns and rows.

Is it possible to manually set a binding for the cellvalue/text in cell?

What I am trying to do, is populate the row of zeroes that we created with the contents of another observable object and have two way binding.

Thanks,
Heath

[Administrator]

There is no way to directly use WPF binding to the cellvalues in various cells in a GridControl.

But, you can use the QueryCellInfo and CommitCellInfo to bind cells to anything inlcuding arbitrary observable collections. If you want to support two binding where changes to your collection made outside the grid automatically trigger changes in the grid too, then your collection will have to expose some event that can be used by the GridControl to trigger the appropriate refresh calls so it can refresh itself automatically. Here is the earlier GridControl sample modified to do something like this.



CS_3549ba8b.zip