Export to excel file, cell line break, ALT + ENTER

Question

I need to put line break in a cell using PHP to xls export, i have tried characters like \r or \n they put the text to the next cell, instead of just putting text in the same cell with line break.

Example:

hello world

I need Hello in one line and world on next like with in the SAME CELL.

no am not using any library, code works fine, i just need to put line break in a CELL — user3118004, May 05 '14 at 17:08
Well, check this! http://stackoverflow.com/questions/7765652/line-break-within-data-for-excel-2003... Seems chr(10) and chr(13) are what you're looking for. — briosheje, May 05 '14 at 17:09
@briosheje does not works, puts line breaks on ROW, instead of CELL — user3118004, May 05 '14 at 17:13
Recording a macro shows how excel does it... by adding a & Chr(10) & ... `ActiveCell.FormulaR1C1 = "Hello" & Chr(10) & "World"` — xQbert, May 05 '14 at 17:14
That's because you likely don't have the wrap text option turned on for that cell or column. Without it the special character char(10) does nothing. So... step 1) in an excel cell type `="Hello" & char(10) & "World"` notice how it doesn't wrap. Now turn wraping on right click, format cells, alignment tab, wrap checkbox. with it on it will wrap. So then from PHP you can write out that formula and you'll get wrapping.... Now how do programatically assign alignment so that it wraps? you're on your own there... — xQbert, May 05 '14 at 17:20
header( "Content-Type: application/xls" ); header( "Content-Disposition: attachment; filename=" . $fileName . ".xls" ); header( "Pragma: no-cache" ); header( "Expires: 0" ); header( "Lacation: excel.htm?id=yes" ); — user3118004, May 05 '14 at 17:22
http://stackoverflow.com/questions/11299663/phpexcel-and-text-wrapping perhaps... but I don't know if PHPExcel is a 3rd party tool or if it's part of PHP... Looks to be opensource... https://phpexcel.codeplex.com/ — xQbert, May 05 '14 at 17:23
@xQbert: PHPExcel is a third party tool BUT, to be honest, if you need to do any kind of Excel thing with PHP, It is just what you should look for and, as far as I can see, it's the most supported library concerning editing excel files with PHP. Also, perhaps let's wait if Mark Baker can help us. http://stackoverflow.com/users/324584/mark-baker — briosheje, May 05 '14 at 17:31
this time i managed to apply some other formatting logic, but i think i should use phpexcel or some other kind of app — user3118004, May 09 '14 at 14:14

score 1 · Answer 1 · edited May 23 '17 at 12:34

This is dirty code.. but working.. hope this help you out.

I refered to these

http://www.bennadel.com/blog/1095-maintaining-line-breaks-in-an-html-excel-file.htm

Export MySQL data to Excel in PHP

<?php
/*******EDIT LINES 3-8*******/
$DB_Server = "localhost"; //MySQL Server    
$DB_Username = "root"; //MySQL Username     
$DB_Password = "";             //MySQL Password     
$DB_DBName = "survey";         //MySQL Database Name  
$DB_TBLName = "results"; //MySQL Table Name   
$filename = "survey results";         //File Name
/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/    
//create MySQL connection   
$sql = "Select username, email, q1, q2, q3, createdat from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());   
//execute query 
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields

echo <<<EOF
    <!--- Store Excel-HTML output. --->
<cfsavecontent variable="strData">

    <table border=".5pt">
    <thead>
        <tr>
EOF;

for ($i = 0; $i < mysql_num_fields($result); $i++) {
    echo "<th>" . mysql_field_name($result,$i) . "</th>";
}

echo <<<EOF
    </thead>
    <tbody>

EOF;

//end of printing column names  
//start while loop to get data
    while($row = mysql_fetch_row($result))
    {
        echo "<tr valign=\"top\">";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
            echo "<td>";
            if(!isset($row[$j]))
                echo preg_replace("/\r\n|\n\r|\n|\r/", "<br style=\"mso-data-placement:same-cell;\" />", "NULL".$sep);
            elseif ($row[$j] != "")
                echo preg_replace("/\r\n|\n\r|\n|\r/", "<br style=\"mso-data-placement:same-cell;\" />", "$row[$j]".$sep);
            else
                echo preg_replace("/\r\n|\n\r|\n|\r/", "<br style=\"mso-data-placement:same-cell;\" />", "".$sep);
            echo "</td>";
        }
        echo "</tr>";
    }       
echo <<<EOF
    </tbody>
    </table>

</cfsavecontent>


<!--- Set header for file attachment. --->
<cfheader
    name="content-disposition"
    value="attachment; filename=data.xls"
    />

<!--- Stream the content. --->
<cfcontent
    type="application/excel"
    variable="#ToBinary( ToBase64( strData ) )#"
    />
EOF;
?>

Export to excel file, cell line break, ALT + ENTER

1 Answers1