Line break removed after pasting data into excel C#

Question

0.00/5 (No votes)

See more:

Hello Everyone,

I have a WPF datagrid. Also I have a method for copying the grid values.

After copying the values from grid, the line break getting removed from the column content after pasting in to excel.

Regards,
Arunkumar Murugesan

What I have tried:

The below code I'm using for set the grid values as clipboard data.

<pre><pre>if (SelectedRows != null && SelectedRows.Count > 0)
            {
                string Values = "<TABLE><TBODY>";
                string Headers = "";
                if (string.IsNullOrEmpty(Headers))
                {
                    Values += "<TR>";

                    foreach (DataColumn column in DynamicBindingTable.Columns)
                    {
                        Values += "<TD>" + column.ColumnName + "</TD>";
                    }
                    Values += "</TR>";
                }
                for (int i = 0; i < CopiedRows.Count; i++)
                {
                    Values += "<TR>";
                    for (int j = 0; j < CopiedRows[i].ItemArray.Length; j++)
                    {
                        object dynamicValues = CopiedRows[i][j].ToString();
                        Type columnType = DynamicBindingTable.Columns[j].DataType;
                        if (dynamicValues != null)
                        {
                            Values += "<TD>" + dynamicValues.ToString() + "</TD>";
                        }
                    }
                    Values += "</TR>";
                }
                Values += "</TBODY></TABLE>";
                Clipboard.SetData(DataFormats.UnicodeText, (object)Values);
            }

The values are setting into clipboard data. But after pasting the values into excel the line breaks has removed in the content.

For Example, in Grid one column value as,

Dear Customer,
In case you require specific free time deal at destination to be listed. 

Failure to receive confirmation by comp

Free time is considered invalid.

We regret to inform you that we do not have available content.

But after pasting into excel it showing as below(single text)

Dear Customer,In case you require specific free time deal at destination to be listed. Failure to receive confirmation by comp.Free time is considered invalid

Could you please help me resolve this problem?

Posted 25-Apr-20 2:55am

Arunkumar Murugesan

Updated 25-Apr-20 5:10am

Add a Solution

Comments

Maciej Los 25-Apr-20 10:35am

What you do is NOT an export to Excel format, but html format. In that case, you have to you detect where a break line is, then to insert   in that place.

[no name] 25-Apr-20 10:51am

And most probably to add some style information like  

Maciej Los 25-Apr-20 11:05am

:thumbsup:

[no name] 25-Apr-20 11:21am

Thanks and sorry that I stolen now the answer from you :-)

Maciej Los 25-Apr-20 11:32am

:DCheers!

Arunkumar Murugesan 26-Apr-20 2:23am

Hey Maciej Los and 0x01AA,

Thank u so much. it works fine. Great...

Maciej Los 26-Apr-20 3:18am

Glad to hear. Please, next time use [Reply] widget to inform user about your reply (on the right side of nick/login).

[no name] 26-Apr-20 7:08am

Nice to hear that it works. The next thing will be escaping the text, means what to do in case " " is in the source text. Ok seems to bee an easy one we can do it with and similar in case you really have " " in the text. See also Edit2 in the updated Solution 1.

Arunkumar Murugesan 27-Apr-20 0:17am

Noted all your points Maciej Los and 0x01AA.

Thank you so much for your quick assitance.

Arunkumar Murugesan 27-Apr-20 7:36am

Hi Maciej and 0x01AA,

One more doubts in the HTML format,

One column value prepared as "<vslname><vslvoyage>" in the HTML format. After pasting into excel it should pasted as "<vslname><vslvoyage>" But it was pasted as empty.

How could we resolve this?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

User 11060979 · Accepted Answer · 2020-04-25T05:11:00

Like Maciej mentioned in the comment to the question you need to replace linenbreaks (linefeeds resp carriage linefeeds) with

<br>

But it is not only a simple   you have to add. To inform Excel to keep it in the same cell you need to replace linebreaks with

<br style='mso-data-placement: same-cell'>

For examlpe

<td>Jan<br style='mso-data-placement: same-cell'>uary</td>

I hope it helps.

[Edit1]
Finally a better option with 'embedded' style

<style>
  br {mso-data-placement:same-cell;}
</style>
<table>
  <tr>
    <th>Col 1</th>
    <th>Col 2</th>
  </tr>
  <tr>
    <td>Line1 <br>Line 2</td>
    <td>data for col 2</td>
  </tr>
</table>

[Edit2]
To allow to have and also something like in the source text you need to encode the source text with HtmlEncode. More info about HtmlEncode you will find e.g. here WebUtility.HtmlEncode Method (System.Net) | Microsoft Docs[^]

[Edit 3]
Yet another thing. In case there is a chance that a lot of data are in your grid you should think about using StringBuilder instead of concatenating strings.

I remember a test I made before about 10 years.
Concatenating 50'000 times a string of 10 characters in a loop took 25 Sec. while with StringBuilder it took '0 Seconds' (measured with Environement.TickCount).