我有一些用php编写的代码,用于将数据从mysql导出到xls,但我的输出php有问题。
这是代码
<?php
// koneksi database
$db = "k8665xxxx";
mysql_connect("localhost","k866xxx","kixxx");
mysql_select_db("$db");
$date = date('d-m-Y');
$filename = "Data-inquiry-downloaded-$date.xls";
// nilai awal counter untuk jumlah data yang sukses dan yang gagal diimport
$sukses = 0;
$gagal = 0;
$table = "promo";
//jumlah kolom
$jkolom=0;
//generate kolom
$q= mysql_query("select * from $table where last_update between '$_POST[DariTanggal]' AND '$_POST[SampaiTanggal]'");
$r=mysql_fetch_assoc($q);
foreach ($r as $head=>$nilai) {
$judul = str_replace("Promo","Inquiry","$head");
$header .=$judul."'t"; //print header table
$jkolom++;
}
$header .= "'n";
//generate baris
$result= mysql_query("select * from $table where last_update between '$_POST[DariTanggal]' AND '$_POST[SampaiTanggal]'");
while ($row=mysql_fetch_array($result)) {
for ($x=0; $x<$jkolom; $x++) {
$content .=$row[$x]."'t";
}
$content .= $row[$x]."'n";
}
$output .= $header.$content;
header('Content-type:application/ms-excel');
header('Content-Disposition: attachment; filename='.$filename);
echo $output;
?>
这是输出。。。
622 Joni bekasi 2193850955 4/11/2012 spooring yani TELP Bekasi deal
623 rengko 81383691374 cek distributor komo 4/11/2012 33x12.5x15 ban rosi TELP DKI Jakarta
624 Joko Prayitno 4/11/2012 175/65/14 ban rosi EMAIL jokfis2001@yahoo.com DKI Jakarta
625 diana Jl.landas pacu timur blok D No.5kemayoran 818772885 4/11/2012 B1603BFF Honda 2009 jazz Rs ban 185/55/R16 ban yani YM diana.william@ymail.com Jakarta Pusat deal
626 Indra Jln.Terusan I Gusti NgurahRay Pondok Kopi
8129496491 4/11/2012 B8723KF Suzuki 2004 baleno paket dasi paket dasi rosi TELP ind_dra@yahoo.com Jakarta Timur deal
627 ari depok 2128639500 mau konfirmasi lagi 4/11/2012 195/60/15 ban rosi TELP Depok
让我了解详细信息,看看626号。数据就像这个
626 Indra Jln.Terusan I Gusti NgurahRay Pondok Kopi
8129496491 4/11/2012 B8723KF Suzuki 2004 baleno paket dasi paket dasi rosi TELP ind_dra@yahoo.com Jakarta Timur deal
但是,它一定是…
626 Indra Jln.Terusan I Gusti NgurahRay Pondok Kopi 8129496491 4/11/2012 B8723KF Suzuki 2004 baleno paket dasi paket dasi rosi TELP ind_dra@yahoo.com Jakarta Timur deal
我的很多数据都变成了这样。。有人知道我的问题吗?
与其尝试构建分隔的数据字符串,不如使用内置函数fputcsv()
例如(节略)
header('Content-Type: text/csv' );
header('Content-Disposition: attachment;filename='.$filename);
$fp = fopen('php://output', 'w');
while ($row = mysql_fetch_row($result)) {
fputcsv($fp, $row);
}
fclose($fp);
我想您的数据库中的文本中有一个换行符,在像$content .=$row[$x]."'t";
这样的2行中,使用str_replace()
删除'n
,或者使用建议的csv函数中内置的php:s(如Phil)。
如果你想构建更复杂的xls,而不仅仅是csv中的数据,你可能想阅读关于"ExcelXML2003"的文件
$content .= str_replace("'n", " ", $row[$x]) . "'t";
更新
或者只是修剪掉所有不必要的空白
$content .= trim($row[$x]) . "'t";