从数据库里到处txt数据，然后再把txt文档转成Excel文档，结果被一个换行符终止了，

1. 错误出现的场景：需要在linux上运行一个PHP文件，

cd /data/tools/repository/apache2.4.3/htdocs/feedback/protected/commands

nohup /data/tools/repository/php-5.4.7/bin/php /data/tools/repository/apache2.4.3/htdocs/feedback/protected/cron.php exportcrash

<?php
class ExportCrashCommand extends CConsoleCommand {
    public function run($args) {
    ini_set("memory_limit","-1");
$datetime_last = "2014-04-14";
//$datetime = date("Y-m-d");
$datetime = "2014-04-26";
$filename = "Enduser_Feedback".$datetime_last."-".$datetime.".txt";
$local_dir = "/data/backup/".$filename;

$sql = "select feed_back_id,imei_code,com_ref,hwts,swts,file_date,file_time,phone_nb,crash_type,project_name from tbl_feed_back as t,tbl_project as p where t.project_id = p.project_id and file_date >= '$datetime_last' and file_date <= '$datetime' and type != 1 and type != 2 order by feed_back_id";
$models=FeedBack::model()->findAllBySql($sql);
       $generated_file_content="IDProjectIMEI CodeCU RefHW VersionSW VersionCrash TypeDateTimePhone number\n";
        foreach($models as $model)
{
$crash_type = mb_convert_encoding($model->crash_type,"gb2312","UTF-8");
$crash_type = str_replace("CHAR(10)","",$crash_type);
$generated_file_content.= $model->feed_back_id."".$model->project_name."".$model->imei_code."".$model->com_ref."".$model->hwts."".$model->swts."".$model->crash_type."".$model->file_date."".$model->file_time."".$model->phone_nb."".$model->problem_des."\n";
}
$fp = fopen($local_dir, 'w');
fwrite($fp,$generated_file_content);
        fclose($fp);
    }
}
?>

生成txt文件，

从加密钥的服务器上拷贝这个txt到另一个linux服务器上：

scp -P 18081 root@172.24.147.11:/data/backup/Enduser_Feedback2014-04-14-2014-04-26.txt ./

2. 从ftp服务器上拷贝这个文件到windows桌面，打开Excel，通过Excel打开这个txt文件，通过Tab键分隔，出现以下错误：

This message can appear due to one of the following:

• The file contains more than 1,048,576 rows or 16,384 columns. To fix this problem, open the source file in a text editor such as Microsoft Word. Save the source file as several smaller files that conform to this row and column limit, and then open the smaller files in Microsoft Excel. If the source data cannot be opened in a text editor, try importing the data into Microsoft Access, and then exporting subsets of the data from Access to Excel.
• The area that you are trying to paste the tab-delineated data into is too small. To fix this problem, select an area in the worksheet large enough to accommodate every delimited item.

Notes

• Excel cannot exceed the limit of 1,048,576 rows and 16,384 columns.
• By default, Excel places three worksheets in a workbook file. Each worksheet can contain 1,048,576 rows and 16,384 columns of data, and workbooks can contain more than three worksheets if your computer has enough memory to support the additional data. 

3. 发现是在转换终端的那一行有换行符，

txt文件出现换行的地方：

1449014 Unknow862761022315539TCL J726Tmt6582vBE3-0移动生活
旗舰店 2014-04-1415:32:00未知
1449015 Unknow862761022315539TCL J726Tmt6582vBE3-0移动生活
旗舰店 2014-04-1415:32:00未知

正确的文件：

1449016 Unknow862761022342319TCL J726Tmt6582vBE3-0QQ2014-04-1415:32:00SIM1:18218681264

Excel最后一行：

1449013	Unknow	8.63E+14	TCL J726T	mt6582	vBE3-0	末日战场	########	15:32:00	未知
1449014	Unknow	8.63E+14	TCL J726T	mt6582	vBE3-0	移动生活

4. 修改上述的php文件，

用了如下添加了如下这些行，

$crash_type = str_replace("CHAR(10)","",$crash_type);//过滤换行
$crash_type = str_replace("\r","",$crash_type);
$crash_type = str_replace("\n","",$crash_type);
$crash_type = str_replace("\t","",$crash_type);
$crash_type = str_replace("\r\n","",$crash_type);
$crash_type = preg_replace("/\s+/", " ", $crash_type);

htmlentities(string,quotestyle,character-set);
$crash_type = str_replace(PHP_EOL, '', $crash_type);
htmlentities($crash_type)

 $crash_type = str_replace("\n","",$crash_type);//过滤换行
 $crash_type = str_replace("\t","",$crash_type);//过滤换行
$crash_type = str_replace("\r\n","",$crash_type);//过滤换行
$crash_type=preg_replace("/\s+/", " ", $crash_type);//过滤多余回车

$crash_type = trim($crash_type);   
$crash_type = strip_tags($crash_type,"");   
$crash_type = ereg_replace("\t","",$crash_type);  
$crash_type = ereg_replace("\r\n","",$crash_type);  
$crash_type = ereg_replace("\r","",$crash_type);  
$crash_type = ereg_replace("\n","",$crash_type);  
$crash_type = ereg_replace(" "," ",$crash_type);  
$crash_type =  trim($crash_type);

结果还是不行，只好去发现数据库中的错误：

5. 结果发现数据库在存储时也是换行，那个字段我在复制粘贴时它会新建一行，crash type就有两行：移动生活，新插入一行是旗舰店。

看了这篇文章（http://www.chengxuyuans.com/MySQL/73723.html）

UPDATE tablename SET field = REPLACE(REPLACE(field, CHAR(10), ''), CHAR(13), '');
 char(10): 换行符
 char(13): 回车符 

用了如下数据库语句：

select * from tbl_feed_back where feed_back_id like '1449014%'; 

查询后又换行：
UPDATE tbl_feed_back SET crash_type = REPLACE(crash_type, CHAR(10), '');
影响23行。
UPDATE tbl_feed_back SET crash_type = REPLACE(crash_type, CHAR(13), '');
影响0行。

它那个符号是数据库里面的换行CHAR(10)

这样执行之后那个换行就没有了。

但是我在php里这样是过滤不掉的。$crash_type = str_replace("CHAR(10)","",$crash_type);\

6. 同事的解决办法：在代码可以通过过滤ascii码来过滤掉这个换行

$crash_type = str_replace(chr(10),"",$crash_type);

发现这样子也不行啊。

7. 这样也不行：

<?php
$msg = '移动生活
旗舰店';
echo 'test1' . $msg . '<br>';
$msg = nl2br($msg);
echo 'test2' . $msg . '<br>';
$msg = htmlentities($msg);
echo 'test3' . $msg . '<br>';;
echo 'test4' . strip_tags($msg);
?>

输出如下：

test1移动生活 旗舰店
test2移动生活
旗舰店
test3移动生活<br /> 旗舰店
test4移动生活<br /> 旗舰店

8. 大家有没有好的方法推荐一下。