How can I save rearranged column positions when reading out a DataGrid?

Question

I have a DataGrid implemented in a WPF-window inside a VSTO-AddIn, which is supposed to preview data to the user before it is pasted to an existing Excel-sheet. The CanUserSortColumns-Property is set to true, so the user is able to rearrange the columns before they are read out and pasted to the sheet. However, when I read out the DataRows from the DataGrid, as shown below, the columns are back in their original position.

 object[,] dataArray = new object[allRows, allCols];

        for (int r = 0; r < allRows; r++)
        {
            DataRow row = ClientGrid.Rows[r];
            for (int c = 0; c < allCols; c++)
            {
                dataArray[r, c] = row[c];
            }
        }

Here is my question: Is there any way to fix this the quick way or at least to track the changes of column display indices in order to rearrange the column order in the code with every change of the columns display order?

I've already tried working something out with the DisplayIndex-property but I did not quite get the hang of the numbers it spits out.

I had the same problem a while ago using DataGrid, DataSource and DataBinding. I gave up DataGrid and used a ListView instead. But if you need to edit the cells then the WPF `ItemsControl` would be a far better solution. — Bizhan, Oct 04 '17 at 10:00

score 0 · Answer 1 · answered Oct 04 '17 at 10:06

You could order the columns by the DisplayIndex property and use reflection to export the values using reflection, e.g.:

public static void ExportUsingRefection(this DataGrid grid)
{
    if (grid.ItemsSource == null || grid.Items.Count.Equals(0))
        throw new InvalidOperationException("You cannot export any data from an empty DataGrid.");

    IEnumerable<DataGridColumn> columns = grid.Columns.OrderBy(c => c.DisplayIndex);
    ICollectionView collectionView = CollectionViewSource.GetDefaultView(grid.ItemsSource);
    foreach (object o in collectionView)
    {
        if (o.Equals(CollectionView.NewItemPlaceholder))
            continue;

        foreach (DataGridColumn column in columns)
        {
            if (column is DataGridBoundColumn)
            {
                string propertyValue = string.Empty;

                /* Get the property name from the column's binding */
                BindingBase bb = (column as DataGridBoundColumn).Binding;
                if (bb != null)
                {
                    Binding binding = bb as Binding;
                    if (binding != null)
                    {
                        string boundProperty = binding.Path.Path;

                        /* Get the property value using reflection */
                        PropertyInfo pi = o.GetType().GetProperty(boundProperty);
                        if (pi != null)
                        {
                            object value = pi.GetValue(o);
                            if (value != null)
                                propertyValue = value.ToString();
                        }
                    }
                }
                //...
            }
        }
    }
}

Please refer to the following blog post for more information.

How to export data from a DataGrid in WPF: https://blog.magnusmontin.net/2013/09/29/export-data-from-a-datagrid/

score 0 · Accepted Answer · answered Oct 12 '17 at 17:04

I ended up doing the following:

Reading out the DisplayIndex-Property and the initial position of each column and ordering the tuples by the DisplayIndex-Property afterwards

var columnIndices = new List<Tuple<int,int>>();
var colCounter = 0;

foreach (var col in myDataGrid.Columns)
{
    columnIndices.Add(Tuple.Create(col.DisplayIndex, colCounter));
    colCounter += 1;
}

var sortedColumns = columnIndices.OrderBy(x => x.Item1).ToList();

Creating an empty DataTable and adding the original DataGrid's columns to it. Then I set the new DataTable's DataRow's cells equal to the cells of the DataRows of the original DataGrid.

var myDataTable = new DataTable();

foreach (var index1 in sortedColumns)
{
    var columnName = myDataGrid.Columns[index1.Item2].ColumnName;
    myDataTable.Columns.Add(columnName, myDataGrid.Columns[index1.Item2].DataType);
}

foreach (DataRow dr in myDataGrid.Rows)
{
    var itemArray = new object[allCols];
    var indexer = 0;

    foreach (var index2 in sortedColumns)
    {
        itemArray[indexer] = dr[index2.Item2];
        indexer += 1;
    }

    myDataTable.Rows.Add(itemArray);
}

How can I save rearranged column positions when reading out a DataGrid?

2 Answers2