WPF DataGrid to csv, exporting only the visible values in the grid

Question

I'm fairly new to WPF and was wondering whether a possibility exists to simply export a WPF DataGrid to a csv file. I've tried using reflections to get the values needed, though that works to some extent I was wondering whether it's possible with an attached property to get the displayed values, these do not necessarily correspond to the values of the item source. The below attached property works as long as I use a static string, or a static resource of a string etc. If I try to use the columns bindings I just get a the default string.empty

    public static readonly DependencyProperty ExportStringProperty =
        DependencyProperty.RegisterAttached("ExportString", //name of    attached property
        typeof(string), //type of attached property
        typeof(ExportBehaviour), //type of this owner class
        new PropertyMetadata(string.Empty)); //the default value of the attached property

    public static string GetExportString(DataGridColumn column)
    {
        return (string)column.GetValue(ExportStringProperty);
    }

    public static void SetExportString(DataGridColumn column, string value)
    {
        column.SetValue(ExportStringProperty, value);
    }

Is there a similar way to get the binding value from xaml in a way like:

    <DataGridTextColumn Header="Name" Binding="{Binding (datagridexcel:Product.Name)}" datagridexcel:ExportBehaviour.ExportString="{Binding (datagridexcel:Product.Name)}"/>

as said, the above earlier works for a static typed string and not for the binding. It has to be said that working with the item source in this case should be avoided and the only thing I'm interested in is the datagrid and the values shown there.

Answer 1

I made this simple app to demonstrate a way of getting CSV from DataGrid. You have the DataGrid:

<DataGrid x:Name="MyDataGrid" Grid.Row="0" ItemsSource="{Binding Rows}" />

In this example it's bound to the following property in the viewmodel:

private IEnumerable<RowViewModel> _rows;
public IEnumerable<RowViewModel> Rows
{
    get { return _rows; }
    set
    {
        _rows = value;
        OnPropertyChanged("Rows");
    }
}

Rows are set to the following sample data:

Rows = new List<RowViewModel>
{
    new RowViewModel { FirstName = "John", LastName = "Doe", DateOfBirth = new DateTime(1988, 12, 19) },
    new RowViewModel { FirstName = "Lara", LastName = "Croft", DateOfBirth = new DateTime(1975, 5, 3) },
    new RowViewModel { FirstName = "Sam", LastName = "Fisher", DateOfBirth = new DateTime(1967, 2, 9) }
};

Under the DataGrid I have a Button:

<Button Grid.Row="1" Content="Copy values as CSV" Command="{Binding CopyAsCsvCommand}" CommandParameter="{Binding ElementName=MyDataGrid}" />

It's bound to a Command in the viewmodel and the CommandParameter is the whole DataGrid.

CopyAsCsvCommand = new DelegateCommand<DataGrid>(CopyAsCsvHandler);

The Command's handler method where the actual copying happens:

private void CopyAsCsvHandler(DataGrid dg)
{
    dg.SelectAllCells();
    dg.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
    ApplicationCommands.Copy.Execute(null, dg);
    dg.UnselectAllCells();
    LivePreviewText = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);
}

This is equivalent to selecting all the cells with CTRL+A and pressing CTRL+C.

Example

Now that you have the CSV content you can just save it down to a file with CSV extension. I hope this helps and this is what you were looking for.

Answer 2

This worked for me. Datagrid Export to CSV (WPF)

   private void button_Click(object sender, RoutedEventArgs e)
    {       
        dataGrid1.SelectAllCells();
        dataGrid1.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
        ApplicationCommands.Copy.Execute(null, dataGrid1);
        dataGrid1.UnselectAllCells();
        String result = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);
        File.AppendAllText("D:\\test.csv", result, UnicodeEncoding.UTF8);

    }

Answer 3

I was able to solve this using Reflection.

Assuming we have a DataGrid with the following conditions:

• Columns have display names that may differ from the property names contained within the collection of objects bound to the DataGrid.
• Columns can be rearranged.
• Sometimes, not always, columns can be hidden.
• Any cell can contain any symbol, including a new line and double quote.

This solution creates a comma separated string of the DataGrid exactly how it's displayed on the screen without overwriting whatever is in the clipboard.

const string delimiter = ",";
var sb = new StringBuilder();

var visibleColumns = OutputGrid.Columns.Where(c => c.Visibility == Visibility.Visible);

var columnHeaders = columnHeaders.Select(c => c.Header);

sb.AppendLine(String.Join(delimiter, columns.Select(c => String.Format("\"{0}\"", c != null ? c.ToString().Replace("\"", "'") : String.Empty))));

var rows = OutputGrid.Items.Cast<TypeThatsBoundToTheDataGrid>()
                           .Select(item => item.GetType().GetProperties()                                         
                                                         .Where(p => visibleColumns.Select(c => c.SortMemberPath)
                                                                                   .Contains(p.Name))
                                                         .OrderBy(p => OutputGrid.Columns                         
                                                                                 .Select(c => c.SortMemberPath)
                                                                                 .ToList()
                                                                                 .IndexOf(p.Name))
                                                         .Select(p => p.GetValue(item)));

foreach (var row in rows)
{
    sb.AppendLine(String.Join(delimiter, row.Select(val => String.Format("\"{0}\"", val != null ? val.ToString().Replace("\"", "'") : String.Empty))));
}

string csvData = sb.ToString();

// Save this string however you want.

Answer 4

this is correction for solution done from jb_ice_yyc

Code

public static void ExportToCSV(DataGrid dataGrid)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "CSV Files (*.csv)|*.csv";

            if (openFileDialog.ShowDialog() == true)
            {
                string filePath = openFileDialog.FileName;

                try
                {
                    const string delimiter = ",";
                    var sb = new StringBuilder();

                    var visibleColumns = dataGrid.Columns.Where(c => c.Visibility == Visibility.Visible);

                    var columnHeaders = visibleColumns.Select(c => c.Header);

                    sb.AppendLine(String.Join(delimiter, columnHeaders.Select(c => String.Format("\"{0}\"", c != null ? c.ToString().Replace("\"", "'") : String.Empty))));

                    var rows = dataGrid.Items.Cast<cStudente>()
                                               .Select(item => item.GetType().GetProperties()
                                                                             .Where(p => visibleColumns.Select(c => c.SortMemberPath)
                                                                                                       .Contains(p.Name))
                                                                             .OrderBy(p => dataGrid.Columns
                                                                                                     .Select(c => c.SortMemberPath)
                                                                                                     .ToList()
                                                                                                     .IndexOf(p.Name))
                                                                             .Select(p => p.GetValue(item)));

                    foreach (var row in rows)
                    {
                        sb.AppendLine(String.Join(delimiter, row.Select(val => String.Format("\"{0}\"", val != null ? val.ToString().Replace("\"", "'") : String.Empty))));
                    }

                    string csvData = sb.ToString();
                    using (StreamWriter sw = new StreamWriter(filePath))
                    {
                        sw.WriteLine(csvData);
                    }
                    MessageBox.Show("Data exported to CSV successfully.", "Export Complete", MessageBoxButton.OK, MessageBoxImage.Information);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("An error occurred while exporting the data: " + ex.Message, "Export Error", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }



        }

this is aprooved