1. Home
  2. Forum
  3. WPF
  4. Merge Row cells based on values present in source.

Merge Row cells based on values present in source.

Hello Team,

Hope everyone is safe,

I need help merging data from two tables into single data grid.

I've two lists as follow : 

1) Invoice Details

InvoiceId Customer Name SubTotal Discount Total amount
1 A 100 0 100
2 B 200 0 200


2) Invoice Master Details

InvoiceId ItemName Qty Amount
1 TestA 1 25
1 TestB 1 25
1 TestC 2 50
2 TestA 1 25
2 TestB 1 25
2 TestC 2 50


Now I am joining this two tables and getting following results using inner join and I am attaching this query result as item source for sfDataGrid so i will be getting following output.

InvoiceId Customer Name ItemName Qty Amount SubTotal Discount Total amount
1 A TestA 1 25 100 0 100
1 A TestB 1 25 100 0 100
1 A TestC 2 50 100 0 100
2 B TestA 2 25 200 0 200
2 B TestB 2 25 200 0 200
2 B TestC 2 50 200 0 200


What I want to achieve is as follow : 

Invoice Id CustomerName ItemName Qty Amount SubTotal Discount Total Amount
1 A TestA 1 25 100 0 100
TestB 1 25
TestC 2 50
2 B TestA 2 25 200 0 200
TestB 2 25
TestC 2 50


I want to configure sfDatagrid such a way that it show data in above format and it must merge redundant values based on master invoice id.


I went by documentation of  Merge cells (https://help.syncfusion.com/wpf/datagrid/merge-cells#merging-cells-based-on-the-content ) but I've doubt on few things.

1) How to achieve above results based on the data i've provided above.

2) If InvoiceId 1 and 2 both are having same SubTotal and Total Amount then i dont want them to merge into single cells across invoice id 1 and 2 because both are different, how can i prevent it from happening. I want data grid to merge cells by following group by invoice id.

3) I've enabled FilterRow for this data grid will grouping work with filter row ?

4) I've enabled ExportToExcel as well for same control, if cells are merged will it reflect same way in excel as well ?

Please response on above as soon as possible.


Thank you 

Dhairya joshi.


2 Replies
VS Vijayarasan Sivanandham Syncfusion Team September 1, 2021 05:15 PM UTC

Hi Dhairya Joshi,

Thank you for contacting Syncfusion Support.

Currently, we are analyzing your requirement of “Merge Row cells based on values present in source.” We will validate and update you the details on or before September 03, 2021.

We appreciate your patience until then.

Regards,
Vijayarasan S


VS Vijayarasan Sivanandham Syncfusion Team September 3, 2021 12:41 PM UTC

Hi Dhairya Joshi,

Thank you for your patience.

Please find answer for your queries below 
Queries 
Solutions 
 
How to achieve above results based on the data i've provided above. 
 
You can merge the redundant data in adjacent cells in a row or columns using QueryCoveredRange event. For more information related to Merging cells based on the content, please refer the user guide documentation, 
 
 
 
If InvoiceId 1 and 2 both are having same SubTotal and Total Amount then i dont want them to merge into single cells across invoice id 1 and 2 because both are different, how can i prevent it from happening. I want data grid to merge cells by following group by invoice id. 
 
Your requirement can be achieved by customize the GetRange method and QueryCoveredRange event in SfDataGrid. Please refer the below code snippet, 
 
void dataGrid_QueryCoveredRange(object sender, GridQueryCoveredRangeEventArgs e) 
{ 
            //here skip the mering for particular columns in SfDataGrid 
            if (e.GridColumn.MappingName == "ItemName" || e.GridColumn.MappingName == "Quantity" || e.GridColumn.MappingName == "Amount") 
                return; 
 
            var range = GetRange(e.GridColumn, e.RowColumnIndex.RowIndex, e.RowColumnIndex.ColumnIndex, e.Record); 
            
            // You can know that the range is already exist in Covered Cells by IsInRange method. 
 
            if (!dataGrid.CoveredCells.IsInRange(range)) 
           { 
                e.Range = range; 
                e.Handled = true; 
            } 
 
            //If the calculated range is already exist in CoveredCells, you can get the range using SfDataGrid.GetConflictRange (CoveredCellInfo coveredCellInfo) extension method. 
} 
 
private CoveredCellInfo GetRange(GridColumn column, int rowIndex, int columnIndex, object rowData) 
        { 
            var range = new CoveredCellInfo(columnIndex, columnIndex, rowIndex, rowIndex); 
            object data = reflector.GetFormattedValue(rowData, column.MappingName);             
 
            GridColumn leftColumn = null; 
            GridColumn rightColumn = null; 
 
            // total rows count. 
            int recordsCount = this.dataGrid.GroupColumnDescriptions.Count != 0 ? 
            (this.dataGrid.View.TopLevelGroup.DisplayElements.Count + this.dataGrid.TableSummaryRows.Count + this.dataGrid.UnBoundRows.Count + (this.dataGrid.AddNewRowPosition == AddNewRowPosition.Top ? +1 : 0)) : 
            (this.dataGrid.View.Records.Count + this.dataGrid.TableSummaryRows.Count + this.dataGrid.UnBoundRows.Count + (this.dataGrid.AddNewRowPosition == AddNewRowPosition.Top ? +1 : 0)); 
 
           // Merge Horizontally 
 
            // compare right column                
 
            for (int i = dataGrid.Columns.IndexOf(column); i < this.dataGrid.Columns.Count - 1; i++) 
            { 
                var compareData = reflector.GetFormattedValue(rowData, dataGrid.Columns[i + 1].MappingName); 
 
                if (compareData == null) 
                    break; 
 
                if (!compareData.Equals(data)) 
                    break; 
                rightColumn = dataGrid.Columns[i + 1]; 
            } 
 
            // compare left column. 
 
            for (int i = dataGrid.Columns.IndexOf(column); i > 0; i--) 
            { 
                var compareData = reflector.GetFormattedValue(rowData, dataGrid.Columns[i - 1].MappingName); 
 
                if (compareData == null) 
                    break; 
 
                if (!compareData.Equals(data)) 
                    break; 
                leftColumn = dataGrid.Columns[i - 1]; 
            } 
 
            if (leftColumn != null || rightColumn != null) 
            { 
                // set left index 
 
                if (leftColumn != null) 
                { 
                    var leftColumnIndex = this.dataGrid.ResolveToScrollColumnIndex(this.dataGrid.Columns.IndexOf(leftColumn)); 
                    range = new CoveredCellInfo(leftColumnIndex, range.Right, range.Top, range.Bottom); 
                } 
 
                // set right index 
 
                if (rightColumn != null) 
                { 
                    var rightColumnIndex = this.dataGrid.ResolveToScrollColumnIndex(this.dataGrid.Columns.IndexOf(rightColumn)); 
                    range = new CoveredCellInfo(range.Left, rightColumnIndex, range.Top, range.Bottom); 
                } 
                return range; 
            } 
 
            // Merge Vertically from the row index. 
 
            int previousRowIndex = -1; 
            int nextRowIndex = -1; 
 
            // Get previous row data.                 
            var startIndex = dataGrid.ResolveStartIndexBasedOnPosition(); 
 
            for (int i = rowIndex - 1; i >= startIndex; i--) 
            { 
                var previousData = this.dataGrid.GetRecordEntryAtRowIndex(i); 
 
                if (previousData == null || !previousData.IsRecords) 
                    break; 
                var compareData = reflector.GetFormattedValue((previousData as RecordEntry).Data, column.MappingName); 
 
                if (compareData == null) 
                    break; 
                 
                //here skip the merging when different InVoiceID contains record 
                var previousDataInvoiceID = reflector.GetFormattedValue((previousData as RecordEntry).Data, "InvoiceId"); 
 
                object rowDataInvoiceID = reflector.GetFormattedValue(rowData, "InvoiceId"); 
 
                if (!compareData.Equals(data) || !previousDataInvoiceID.Equals(rowDataInvoiceID)) 
                    break; 
                previousRowIndex = i; 
            } 
 
            // get next row data. 
 
            for (int i = rowIndex + 1; i < recordsCount + 1; i++) 
            { 
                var nextData = this.dataGrid.GetRecordEntryAtRowIndex(i); 
 
                if (nextData == null || !nextData.IsRecords) 
                    break; 
                var compareData = reflector.GetFormattedValue((nextData as RecordEntry).Data, column.MappingName); 
 
                if (compareData == null) 
                    break; 
 
                //here skip the merging when different InVoiceID contains record 
                var nextDataInvoiceID = reflector.GetFormattedValue((nextData as RecordEntry).Data, "InvoiceId"); 
                object rowDataInvoiceID = reflector.GetFormattedValue(rowData, "InvoiceId"); 
 
                if (!compareData.Equals(data) || !nextDataInvoiceID.Equals(rowDataInvoiceID)) 
                    break; 
                nextRowIndex = i; 
            } 
 
            if (previousRowIndex != -1 || nextRowIndex != -1) 
            { 
 
                if (previousRowIndex != -1) 
                    range = new CoveredCellInfo(range.Left, range.Right, previousRowIndex, range.Bottom); 
 
                if (nextRowIndex != -1) 
                    range = new CoveredCellInfo(range.Left, range.Right, range.Top, nextRowIndex); 
                return range; 
            } 
            return null; 
        } 
 
    } 
 
 
 
I've enabled FilterRow for this data grid will grouping work with filter row ? 
 
 
 
 
Yes. Grouping work with filter row enabled in SfDataGrid. 
 
I've enabled ExportToExcel as well for same control, if cells are merged will it reflect same way in excel as well ? 
 
You can export the merged cells to excel by setting the ExcelExportingOptions.ExportMergedCells property. For more information related to Export merged cells to Excel, please refer the user guide documentation, 
 
 
Please let us know if you have any concerns in this. 
Regards,
Vijayarasan S 


Loader.
Up arrow icon