how to make New lines in a cell using phpexcel

Question

i have problem with php excel,

i want to make new line in one cell but i can't, i have tried using \n or <br /> but itsn't work. this my code:

$objPHPExcel->getActiveSheet()->setCellValue('H5', 'Hello\nWorld'); // i need this show in two line
$objPHPExcel->getActiveSheet()->getStyle('H5')->getAlignment()->setWrapText(true);

fyi: my format excel is xls not xlsx. many thanks :)

@Mark Baker, sorry i just mistype. actually this is for cell H5. i just update my question, many thanks :) — bungdito, May 11 '11 at 06:47
OK, second question (seeing as you changed it when you edited): 'Hello\nWorld' or "Hello\nWorld"? Standard PHP quoting for strings applies: the double quotes make a difference to the \n — Mark Baker, May 11 '11 at 08:07

score 132 · Accepted Answer · answered May 11 '11 at 08:23

132

$objPHPExcel->getActiveSheet()->setCellValue('H5', "Hello\nWorld");
$objPHPExcel->getActiveSheet()->getStyle('H5')->getAlignment()->setWrapText(true);

Works for me...

You should always use double quotes when you add escape sequences in a PHP string.

answered May 11 '11 at 08:23

wimvds

12,790
2
41
42

6

It is useful to note that if you are doing this to a cell you have created by merging other cells you should specify all of the cells by their original mapping. Eg if you create B2 by merging B2-E6 then the argument to getStyle is 'B2:E6' – jerrygarciuh Nov 23 '13 at 03:45
this is working in your example but i want output like Hello \n World DEMO(new line after Hello).but here two new line created so how i solve this? – himansu Jul 31 '15 at 07:18
yes, content must be wrapped with double-quotes. Thank you. – Bhavin Thummar Dec 05 '19 at 07:30

score 32 · Answer 2 · edited Nov 16 '12 at 15:09

32

you should use 'r' to break into new line into excel with php

and use double quotes when you add escape sequences in a PHP string.

  $objPHPExcel->getActiveSheet()->setCellValue('H5', "Hello\r World");
  $objPHPExcel->getActiveSheet()->getStyle('H5')->getAlignment()->setWrapText(true);

edited Nov 16 '12 at 15:09

Agriesean

155
1
16

answered May 11 '11 at 08:43

Ravin

371
1
3
5

7

+1 for \r This should be the accepted answer. Save me the day with \r Thanks. – Adrian P. Dec 02 '13 at 16:00
4

You might want to try PHP_EOL as the new line separator is os-specific. – Marcel Burkhard Apr 13 '15 at 07:20
3

pay attention to double quotes – Tebe Jul 01 '15 at 10:09
@Ravin what about if I have data from MySQL? How will I put a new line same for $row['abc']. Can you help Thanks – Mazhar Iqbal Rana Apr 04 '23 at 03:19

score 11 · Answer 3 · edited Aug 17 '16 at 13:17

11

Improved answer based on Ravin and others

$objPHPExcel
  ->getActiveSheet()
  ->setCellValue('H5', "Hello".PHP_EOL." World");

$objPHPExcel
  ->getActiveSheet()
  ->getStyle('H5')
  ->getAlignment()
  ->setWrapText(true);

edited Aug 17 '16 at 13:17

fehrlich

2,497
2
26
44

answered Jan 22 '16 at 03:14

Muhammad Amjad

306
3
9

5

`PHP_EOL` is useless in this case, because it will give an EOL char based on the OS where the php script is run (so likely linux/unix server), but the file will likely be opened on Windows, where default EOL will be different. – mkilmanas Mar 02 '18 at 14:08
@Muhammad what about if I have data from MySQL? Like how will I put a new line same for $row['abc']. Can you help Thanks – Mazhar Iqbal Rana Apr 04 '23 at 03:19

score 10 · Answer 4 · answered Feb 12 '19 at 14:32

10

We can set for the default style, so don't need to specify for each cell range:
$objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(true);

answered Feb 12 '19 at 14:32

Andron

6,413
4
43
56

score -7 · Answer 5 · edited Dec 19 '17 at 06:26

-7

To achieve next line but same cell forxcel export, this is the simplest solution.

<tr>
    <td style="wrap-text: true">
        Test
        <br />
        Test2
    </td>
</tr>

edited Dec 19 '17 at 06:26

Pang

9,564
146
81
122

answered Dec 19 '17 at 05:28

Ambrish Dharane

11
1

Test
Test2 – Ambrish Dharane Dec 19 '17 at 05:30

how to make New lines in a cell using phpexcel

5 Answers5

Linked