PHP generates Excel and controls line breaks in Excel cells

The company uses Mantis to manage bugs. Mantis has a function to export bugs to Excel.

But the current mantis version, when exporting to excel, "summary, description, instructions on steps to reproduce the problem, additional information, comments", etc. In this information,

the carriage returns and line feeds used when submitting bugs are all gone. Because there is no format, it is very inconvenient to read in excel.

In retrospect, this is the problem that has been improved.

I am using php to do it. When generating excel, how to write a line break character in an excel cell to control the format of the cell.

I tested it and found that rn is invalid when generating excel and will be filtered.

But if When writing
, the effect is not to change the line in the cell, but to change a new cell.

Google it, and in order to achieve the purpose of line breaking in the cell, you need to insert the following string

&#39;<br style="mso-data-placement:same-cell;" />&#39;

In addition, during my investigation, I discovered another interesting problem.

As mentioned earlier, when using php to generate excel, rn does not have any line wrapping function. To control line wrapping, you need With the help of html tags.

However, if rn is mixed in the middle of

,

looks like the following, then the rn at this time is equivalent to a
, and it will be made In excel, create a new cell.

Write

<br/>..........rn<br/>..........rn<br/>.......... ..rn<br/>

This kind of behavior is obviously not what we want,

So let’s replace them all like the following.

$p_new_lines   = array("\r\n", "\n", "\r","\r\n", "<pre class="brush:php;toolbar:false">","

","
","
","
"); $p_change_line_in_excel_cell = &#39;<br style="mso-data-placement:same-cell;" />&#39;; $p_result = str_replace( $p_new_lines,$p_change_line_in_excel_cell,$p_input);