WordPress导出数据库自定义表的数据为CSV文件

导语：有一位客户要求开发一个活动报名的页面，要求前台可以报名，后台可以清空和导出数据。叶子根据网上的资料，自己改了一些代码，修改了表名，屏蔽了导入功能。

说明与演示

该代码请放置在functions.php中，叶子只使用了导出功能，而且是导出的固定一个表wp_leafevent，如果你有多个表需要导出，请自行修改语句。导入功能屏蔽了，没有验证代码的正确性，如果需要导入功能，请自行恢复和验证。

具体的代码

function application_admin_pages() {
add_menu_page(__('活动信息'), __('活动信息'), 'manage_options', basename(__FILE__), 'application_landing_function');
}
add_action('admin_menu', 'application_admin_pages');
function application_admin_submenu() {
add_submenu_page( 'functions.php', 'import_export', '导出', 'manage_options', 'backup-page', 'application_import_export_page');
}
add_action('admin_menu', 'application_admin_submenu');

/* application_landing_function */
function application_landing_function() { ?>
<div class="wrap">
<h1>活动报名信息内容</h1>
<table>
<?php

global $wpdb;

$querystr = "SELECT id,post_id,author,author_name,name,phone,email,cre_date FROM wp_leafevent order by cre_date desc LIMIT 50";


$results = $wpdb->get_results($wpdb->prepare($querystr,$post_id),ARRAY_A);

echo '<tr>';

echo '<td>序号</td>'.'<td>活动页面编号</td>'.'<td>提交用户</td>'.'<td>提交用户</td>'.'<td>参加人</td>'.'<td>电话</td>'.'<td>电邮</td>'.'<td>创建时间</td>';

echo '</tr>';

foreach ($results as $r){

echo '<tr>';

echo '<td>'.$r['id'].'</td>'.'<td>'.$r['post_id'].'</td>'.'<td>'.$r['author'].'</td>'.'<td>'.$r['author_name'].'</td>'.'<td>'.$r['name'].'</td>'.'<td>'.$r['phone'].'</td>'.'<td>'.$r['email'].'</td>'.'<td>'.$r['cre_date'].'</td>';

echo '</tr>';
} ?>
</table>
</div>
<?php }

/* application_import_export_page */
function application_import_export_page() {?>
<div class="wrap">
<h1>活动信息导出</h1>
<!--h2>Import CSV</h2>
<form method="post" action="" method="post" enctype="multipart/form-data">
<fieldset>
<legend><strong>Choose a CSV (.csv) file to upload, then click Import .</strong></legend>
<input type="file" name="file" size="50" maxlength="100000" /><br/>
</fieldset>
<p class="submit">
<input type="submit" name="import" class="button button-primary button-import" value="Import" />
</p>
</form-->
<!-- End Import from HTML-->

<?php echo export_UI(); ?><!-- Export CSV HTML-->
<p></p>
<?php import_form_action();?><!-- Import form action -->
</div>
<script type="text/javascript" charset="utf-8">
$(document).on('click','input.button-import', function(){
if (!confirm("Are You Sure")) {
return false;
}
})
</script>
<?php }

/* Import Form Action */
function import_form_action() {
define('UPLOAD_PATH', dirname(__FILE__).'/upload/');
global $wpdb;
if ( count($_POST) > 0 && isset($_POST['import']) ) {

$up_info = $_FILES['file'];
if($up_info['error']>0) {
echo "Error: " . $up_info['error']. "<br />";
} else {
$file = fopen($up_info["tmp_name"],"r");
$count = 0;
$table_name = array();

$wpdb->query ('TRUNCATE TABLE wp_leafevent') ;

while(!feof($file) && $data = fgetcsv($file)) {
$column = count($data);
$result = array();

if($count==0 && !empty($data)) {
for($i=0;$i<$column;$i++){
array_push($table_name,$data[$i]);
}
} elseif ($count>0 && !empty($data)) {

for($i=0;$i<$column;$i++) {
array_push($result,$data[$i]);
}

$temp_result = array();
$i=0;
foreach($table_name as $table_name_key => $table_name_val) {
$temp_result[$table_name_val] = $result[$i];
$i++;
}

$res = $wpdb->insert( "_cwcustom_rebate_devices", $temp_result );
echo $res ? $count.' Succeed<br/>' : $count.' False<br/>';
}

$count++;
}
fclose($file);
}
}
}

/* Export */
define('CONST_CSV_SLUG', 'admin.php?page=backup-page&action=');
function export_UI() {
?>
<div id="wrapper">
<h2>导出 CSV</h2>
<div id="page">
<table cellspacing="0" class="wp-list-table widefat">
<thead>
<tr>
<th>序号</th>
<th>导出表到 CSV</th>
<th>清空表</th>
</tr>
</thead>
<tbody>
<?php
$array_table_name = array('1' => 'wp_leafevent');
foreach ($array_table_name as $serial_number => $table_name){?>
<tr>
<td><?php echo $serial_number;?></td>
<td><a class="button button-large" href="<?php echo CONST_CSV_SLUG.$table_name.'&eventtype=export';?>">导出 &nbsp; <strong> <?php echo $table_name;?></strong></a></td>
<td><a class="button button-large" href="<?php echo CONST_CSV_SLUG.$table_name.'&eventtype=truc';?>">清空 &nbsp; <strong> <?php echo $table_name;?></strong></a></td>
</tr>
<?php }
?>
</tbody>
</table>
</div>
</div>

<?php
}
/*Prompt to open/save exported data as .CSV file*/
function application_export(){

$getTable = isset($_REQUEST['action']) ? $_REQUEST['action'] : '';
$gettype = isset($_REQUEST['eventtype']) ? $_REQUEST['eventtype'] : '';
if ($getTable){echo csv_generate($getTable,$gettype);exit;}


}
add_action('init', 'application_export');
/* Convert table data into CSV format */
function csv_generate($getTable,$gettype){
ob_clean();
global $wpdb;
$field='';
$getField ='';

if($getTable){

if ($gettype=='export') {

$result = $wpdb->get_results("SELECT * FROM wp_leafevent",ARRAY_N);
$fieldsCount =8;
$csv_file_name = $getTable.'_'.date('Ymd_His').'.csv'; # CSV FILE NAME WILL BE table_name_yyyymmdd_hhmmss.csv

# GET FIELDS VALUES WITH LAST COMMA EXCLUDED
foreach($result as $row){
if(is_array($row)){
for($j = 0; $j < $fieldsCount; $j++){
if($j == 0) $fields .= "\n"; # FORCE NEW LINE IF LOOP COMPLETE
$value = str_replace(array("\n", "\n\r", "\r\n", "\r"), "\t", $row[$j]); # REPLACE NEW LINE WITH TAB
$value = str_getcsv ( $value , ",", "\"" , "\\"); # SEQUENCING DATA IN CSV FORMAT, REQUIRED PHP >= 5.3.0
$fields .= $value[0].','; # SEPARATING FIELDS WITH COMMA
}
$fields = substr_replace($fields, '', -1); # REMOVE EXTRA SPACE AT STRING END
}
}
header("Content-type: text/x-csv"); # DECLARING FILE TYPE
header("Content-Transfer-Encoding: binary");
header("Content-Disposition: attachment; filename=".$csv_file_name); # EXPORT GENERATED CSV FILE
header("Pragma: no-cache");
header("Expires: 0");

return $fields;
}

if ($gettype=='truc') {

$querystr = "truncate table wp_leafevent";


$results = $wpdb->query($querystr);

header("Location: /wp-admin/admin.php?page=functions.php");
return $results;

}
}
}
/* End Export*/

结束

你学会了么？如果需要讨论，请加入我们的QQ群。