导出到Excel文件,单元格换行,ALT + ENTER
问题的出现原因:代码中使用了正则表达式替换函数preg_replace,将换行符替换成了Excel中的单元格换行符(<br style="mso-data-placement:same-cell;" />),但是由于代码中使用了mysql函数,而mysql函数在PHP 7.0版本中已被弃用,导致代码无法正常执行。
解决方法:将代码中的mysql函数替换为mysqli函数,同时修改连接数据库的方式,以适应PHP 7.0版本及以上的要求。具体修改代码如下:
<?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 $mysqli = new mysqli($DB_Server, $DB_Username, $DB_Password, $DB_DBName); if ($mysqli->connect_errno) { die("Couldn't connect to MySQL: " . $mysqli->connect_error); } //execute query $sql = "SELECT username, email, q1, q2, q3, createdat FROM $DB_TBLName"; $result = $mysqli->query($sql); if (!$result) { die("Couldn't execute query: " . $mysqli->error); } $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"); //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; while ($field = $result->fetch_field()) { echo "<th>" . $field->name . "</th>"; } echo <<<EOF </thead> <tbody> EOF; //end of printing column names //start while loop to get data while ($row = $result->fetch_row()) { echo "<tr valign=\"top\">"; foreach ($row as $cell) { echo "<td>"; if (!isset($cell)) { echo preg_replace("/\r\n|\n\r|\n|\r/", "<br style=\"mso-data-placement:same-cell;\" />", "NULL" . $sep); } elseif ($cell != "") { echo preg_replace("/\r\n|\n\r|\n|\r/", "<br style=\"mso-data-placement:same-cell;\" />", $cell . $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; ?>
以上是根据给出的代码整理出的原因和解决方法。