制作网站时,经常会遇到检索数据列表的情况。通常用户希望下载这些列表数据并存储到客户端。当然下载这些数据的时候是需要固定的格式,以便于用Excel等软件阅览。说的简单一些就是CSV/Excel数据导出 。
注意事项:
输出文档的基本格式为:列1,列2,列3,……,列n/n
格式化数据的同时要对特殊的字符进行过滤。譬如“,”如果不转换为全角“,”会导致数据格式的混乱。
实现方法:
举例:检索数据库表,将结果保存为字符串,进行格式和特殊字符的过滤后,导出到客户端的CSV文件。
OrderSearchEdit_saveCSV.php
<?
php
include ( $_SERVER [ ' DOCUMENT_ROOT ' ] . " /ftcart/OrderInfoManager.class.php " );
include ( $_SERVER [ ' DOCUMENT_ROOT ' ] . " /ftcart/CommonUtil.php " );
include ( $_SERVER [ ' DOCUMENT_ROOT ' ] . " /ftcart/CommonConst.php " );
include ( $_SERVER [ ' DOCUMENT_ROOT ' ] . " /ftcart/CommonErrorMsg.php " );
include ( $_SERVER [ ' DOCUMENT_ROOT ' ] . " /webadmin.php " );
$searchcase = new OrderInfoManager();
$nowTime = microtime_float();
$searchcase = unserialize ( $_SESSION [ " ORDER_SEARCH_CASE " ]);
$listcsv = $searchcase -> doCSV(); // 调用doCSV()方法
$filename = $nowTime ;
// $filename = str_replace(":","",$nowTime);
//$filename = trim($filename);
//echo $filename;
header ( " Content-Disposition: attachment; filename= " . $filename . " .csv " );
header ( ' Content-Type:APPLICATION/OCTET-STREAM ' );
echo " 注文No.,注文日,氏名,住所,支払い方法,合計金額,発送 " ;
echo $listcsv ;
?>
include ( $_SERVER [ ' DOCUMENT_ROOT ' ] . " /ftcart/OrderInfoManager.class.php " );
include ( $_SERVER [ ' DOCUMENT_ROOT ' ] . " /ftcart/CommonUtil.php " );
include ( $_SERVER [ ' DOCUMENT_ROOT ' ] . " /ftcart/CommonConst.php " );
include ( $_SERVER [ ' DOCUMENT_ROOT ' ] . " /ftcart/CommonErrorMsg.php " );
include ( $_SERVER [ ' DOCUMENT_ROOT ' ] . " /webadmin.php " );
$searchcase = new OrderInfoManager();
$nowTime = microtime_float();
$searchcase = unserialize ( $_SESSION [ " ORDER_SEARCH_CASE " ]);
$listcsv = $searchcase -> doCSV(); // 调用doCSV()方法
$filename = $nowTime ;
// $filename = str_replace(":","",$nowTime);
//$filename = trim($filename);
//echo $filename;
header ( " Content-Disposition: attachment; filename= " . $filename . " .csv " );
header ( ' Content-Type:APPLICATION/OCTET-STREAM ' );
echo " 注文No.,注文日,氏名,住所,支払い方法,合計金額,発送 " ;
echo $listcsv ;
?>
doCSV()方法:
function
doCSV(){
global $db ;
$nowdate = getNowDateYYYYMMDD();
$SQL = " ##### " ;
$rs = $db -> query( $SQL );
$result ;
$payment_method ;
$delivery_status ;
if (( ! DB :: isError( $rs ))) {
while ( $row = $rs -> fetchRow()){
$row = Common_record( $row );
if ( $row [ ' payment_method_id ' ] == 0 ){ $payment_method = " 銀行振り込み " ;}
else if ( $row [ ' payment_method_id ' ] == 1 ){ $payment_method = " 郵便局 " ;}
else if ( $row [ ' payment_method_id ' ] == 2 ){ $payment_method = " 代引き " ;}
else { $payment_method = "" ;}
if ( $row [ ' actual_send_date ' ] > $nowdate ){ $delivery_status = " 未発送 " ;}
else if ( $row [ ' actual_send_date ' ] < $nowdate ){ $delivery_status = " 発送済 " ;}
// 格式化并过滤特殊字符
$result .= $row [ ' order_id ' ] . " , " .
$row [ ' create_date ' ] . " , " .
$row [ ' order_first_name ' ] . " " .
$row [ ' order_last_name ' ] . " , " .
str_replace ( " , " , " , " , $row [ ' order_address ' ]) . " , " .
$payment_method . " , " .
$row [ ' amount ' ] . " , " .
$delivery_status . " " ;
}
return $result ;
}
}
global $db ;
$nowdate = getNowDateYYYYMMDD();
$SQL = " ##### " ;
$rs = $db -> query( $SQL );
$result ;
$payment_method ;
$delivery_status ;
if (( ! DB :: isError( $rs ))) {
while ( $row = $rs -> fetchRow()){
$row = Common_record( $row );
if ( $row [ ' payment_method_id ' ] == 0 ){ $payment_method = " 銀行振り込み " ;}
else if ( $row [ ' payment_method_id ' ] == 1 ){ $payment_method = " 郵便局 " ;}
else if ( $row [ ' payment_method_id ' ] == 2 ){ $payment_method = " 代引き " ;}
else { $payment_method = "" ;}
if ( $row [ ' actual_send_date ' ] > $nowdate ){ $delivery_status = " 未発送 " ;}
else if ( $row [ ' actual_send_date ' ] < $nowdate ){ $delivery_status = " 発送済 " ;}
// 格式化并过滤特殊字符
$result .= $row [ ' order_id ' ] . " , " .
$row [ ' create_date ' ] . " , " .
$row [ ' order_first_name ' ] . " " .
$row [ ' order_last_name ' ] . " , " .
str_replace ( " , " , " , " , $row [ ' order_address ' ]) . " , " .
$payment_method . " , " .
$row [ ' amount ' ] . " , " .
$delivery_status . " " ;
}
return $result ;
}
}
这样就ok啦,在你的导出数据html页面做一个<input type="button" οnclick="do_CSV()">然后在JS里面写一个提交form的do_CSV(),并将form action到OrderSearchEdit_saveCSV.php就完成了。应该注意的是由于OrderSearchEdit_saveCSV.php中声明的是一个Content-Type:APPLICATION/OCTET-STREAM所以当前页面不会被刷新。这点可能大家都知道,不过我是刚学到的,所以记录一下。