Search code examples
javahtmlexcelcopy-pasteline-breaks

How to handle line breaks when copy/pasting from HTML to MS Excel

My Java program creates an HTML file which main part consists of a table. One of those table columns contains text values that happen to have line breaks (<br/> in the HTML table).

The output HTML needs to be copy/pasted into an MS Excel file later on by the user. Everything went smoothly until I needed to transform line breaks into Excel ones.

Whenever there's an occurence, the first line of the said column is well put into the fitting Excel column, but the second line creates a new line starting at Column 1.

enter image description here

enter image description here

This questions has been asked several times but no answer has been given when there is no ASP involved. Needless to say, the <br style="mso-data-placement:same-cell;"> does NOT work.

Thanks for any answer, I'm desperate to get it fixed.

Solution

  • So my personal experience with excel and HTML is a battled one as well. Excel likes to break everything that you put in it that was perfectly formatted for Excel.

    My best answer for you is to create an "Excel" formatted version of the html for people that want to copy and paste it. (You could do a link/button to switch the formatting from simple html to Excel HTML)

    By Excel HTML, I mean to format it to use CHAR(13) / CHAR(10) instead of < BR \>.

    I am on a MAC so CHAR(13) works for me. See the attached jsfiddle for my solution.

    The one caveat that it has is that you have to enable word wrap on the field.

    http://jsfiddle.net/o0nmcnex/

    <table style="width:100%">
  <tr>
    <td>Jill</td>
    <td>Smith</td> 
      <td>=50 & CHAR(13) & 50</td>
  </tr>
  <tr>
    <td>Eve</td>
    <td>Jackson</td> 
    <td>94</td>
  </tr>
</table>