How can I do a line break in cell of excel using PHP?

Question

Hi I have thw following code:

header('Content-type: application/vnd.ms-excel');
header("Content-Disposition: attachment; filename=archivo.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo "
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="expires" content="0">
</head>
<body>
<table width="100%" border="1" align="center" cellpadding="3" cellspacing="0">
  <tr>
    <td>Line 1<br>Line 2</td>
  </tr> 
</table>
</body>
</html>";

I have tried:

<td>Line 1\r\nLine 2</td>
<td>Line 1".chr(10) . chr(13)."Line 2</td>

But nothing seems to work, I need to do a line break in a cell, any suggestions are welcomed. I'm using excel 2010.

I already look at this question: line break within data for Excel 2003 and it didnt work.

It doesnt show error, when I apply <br> it creates a new row, when I use \r\n, \n, or chr(10) . chr(13), it creates a space.

Thanks!!

Try `\n` instead of `\r\n`. Windows uses just `\n` for a new line function. — Fluffeh, May 30 '14 at 13:41
This is why I get so frustrated with developers trying to pretend that HTML markup is an Excel file, and then fretting that it doesn't work — Mark Baker, May 30 '14 at 13:58

score 11 · Accepted Answer · answered May 30 '14 at 14:12

11

After searching a lot found this site: http://www.bennadel.com/blog/1095-maintaining-line-breaks-in-an-html-excel-file.htm

And this tag solves the issue <br style="mso-data-placement:same-cell;" />

Thanks for all your comments!!

answered May 30 '14 at 14:12

joseagaleanoc

575
2
7
20

I was trying to export a wp list table to .csv. Couldn't for the life in me make the line breaks inside an cell work. I found your answer and tried to export to .xls instead: it's working like a charm! Thanks for the help. – Pablo Aug 29 '16 at 19:31

score 1 · Answer 2 · answered Mar 31 '15 at 05:42

1

You should add double quotes to include line break

'<td>Line 1' . "\r\n" . 'Line 2</td>'

answered Mar 31 '15 at 05:42

BobGao

770
8
18

score 0 · Answer 3 · answered May 30 '14 at 13:46

0

You say charset="utf-8" but chr returns ASCII!

What about this? ;oP

utf8_encode(chr(10).chr(13))

answered May 30 '14 at 13:46

robin

159
5

Quid the EOF correction patch on your brackets (i've edited your code)? – robin May 30 '14 at 13:56
That wouldn't help - UTF-8 encoding of 7-bit character is a no-op, and both CR and LF are 7-bit characters. – toolforger Mar 29 '21 at 16:43

score -1 · Answer 4 · answered May 30 '14 at 13:43

-1

Have a shot with PHP_EOL it should give you the newline character of the operating system you're working on.

Example:

$output = 'Line 1' . PHP_EOL .
          'Line 2' . PHP_EOL .
          'Line 3';

answered May 30 '14 at 13:43

l0gic

121
5

Ok, this was fun.. Try using this break tag: `
` – l0gic May 30 '14 at 14:06

How can I do a line break in cell of excel using PHP?

4 Answers4