line break within excel cell?

[thefortrees]

Hi all,

 

I am writing to an XML file using PHP then importing the file into Excel as a spreadsheet.

 

Every thing is working fine EXCEPT... I need linebreaks within a string (ie - one cell) and can not figure out/find how to do this. \n and the like do not work properly. Any suggestions?

[harristweed]

I think (not sure ) that a tab produces a line break in excel:

 

Try '" \t "

[cmgmyr]

no... \t inserts a new tab (cell to the right) and \n drops to a new row (record)

[thefortrees]

Is there any way to define the line break in hexadecimal? I think it is 0x0D 0x0A

[cmgmyr]

try \r i think thats a "return"

[Psycho]

How are you creating the Excel file? If you are creating it as a CSV file and just naming it as xls, then that won't work.

 

Here is a function I use and can insert a line break using \n:

 

<?

// Functions for export to excel.
function xlsBOF() { 
 echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0); 
 return; 
} 

function xlsEOF() { 
 echo pack("ss", 0x0A, 0x00); 
 return; 
} 

function xlsWriteNumber($Row, $Col, $Value) { 
 echo pack("sssss", 0x203, 14, $Row, $Col, 0x0); 
 echo pack("d", $Value); 
 return; 
} 

function xlsWriteLabel($Row, $Col, $Value ) { 
 $L = strlen($Value); 
 echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L); 
 echo $Value; 
 return; 
}

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=orderlist.xls "); 
header("Content-Transfer-Encoding: binary ");

xlsBOF();

$text = "This is a \n label with \n line breaks";
$number = "12345";

xlsWriteLabel(0,0,$text);
xlsWriteNumber(0,1,$number);

xlsEOF();
exit();
?>

[thefortrees]

I write to an XML file, then I import the XML file with Excel