Trim values while exporting WPF DataGrid to CSV

Question

I am exporting WPF DataGrid values to CSV with following code:

    private void Button_Click_2(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.WriteAllText(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + @"\" + "AllCustomersCSV.txt", result, UnicodeEncoding.UTF8);
    }

However result has a lot of spaces after values. Looks like WPF DataGrid behavior. I have tried to add:

String result = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue.Trim());

but it does not seem to work. What am I doing wrong?

P.S. I guess it should be done with loop or List as it is not able to trim each one separately in my current setup?

---

EDIT 1:

Here is an example of txt file output:

---

EDIT 2:

result output example:

\tENERGIDIRETORAT                    \t                                   \tBOX 5091 MAJORSTUA                 \tN-0301   

I guess I need something like:

result = Regex.Replace(result, " *\t *", ",");

However this is not functioning for all rows, especially with empty cells in some rows.

So it should remove all whitespaces (tabs) before \t but leave \t's to be replaced by , (commas).

---

EDIT 3:

I haven't got CSV export working as there were some commas probably missing, because after importing it to Excel some columns did not match. I went for trying xls file format. This seems to be working fine (UTF8 Encoding was wrong BTW):

    private void Button_Click(object sender, RoutedEventArgs e)
    {
        DataGrid2.SelectAllCells();
        DataGrid2.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;
        ApplicationCommands.Copy.Execute(null, DataGrid2);
        String resultat = (string)Clipboard.GetData(DataFormats.CommaSeparatedValue);
        String result = (string)Clipboard.GetData(DataFormats.Text);
        DataGrid2.UnselectAllCells();
        StreamWriter file1 = new StreamWriter(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + @"\" + "SameCustomersCSV.xls",
    false, Encoding.GetEncoding("ISO-8859-1"));
        file1.WriteLine(result.Replace(',', ' '));
        file1.Close();

        MessageBox.Show(" Exporting data to Excel file to your Desktop is ready!");
    }

I don't really understand what is the problem with CSV...

Answer 1

You could remove the tabs by iterating through the copied characters and write all of them except \t to the file:

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.Text);
    using (StreamWriter sw = new StreamWriter(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + @"\" + "AllCustomersCSV.txt",
        false, Encoding.UTF8))
        foreach (char c in result)
            if (c != '\t')
                sw.Write(c);
            else
                sw.Write(",");
}