logo标签列表

将WordPress导出为CSV文件

phpwordpresscsvheader
8
我需要将一个表格中的数据导出为csv文件。我已经成功获取了数据，但是浏览器没有生成CSV文件。
我的代码如下：这是头文件的问题。我只能得到以逗号分隔的值的输出，但无法获得CSV文件。
/* Converting data to CSV */

public function CSV_GENERATE($getTable)
{
    ob_clean();
    global $wpdb;
    $field='';
    $getField ='';

    if($getTable){
        $result = $wpdb->get_results("SELECT * FROM $getTable");
        $requestedTable = mysql_query("SELECT * FROM ".$getTable);
        // echo "hey";die;//var_dump($result);die;

        $fieldsCount = mysql_num_fields($requestedTable);

        for($i=0; $i<$fieldsCount; $i++){
            $field = mysql_fetch_field($requestedTable);
            $field = (object) $field;         
            $getField .= $field->name.',';
        }

        $sub = substr_replace($getField, '', -1);
        $fields = $sub; # GET FIELDS NAME
        $each_field = explode(',', $sub);
        $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){
            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->$each_field[$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"); 
        header("Content-type: application/x-msdownload");
        //header("Content-Disposition: attachment; filename=data.csv");

        return $fields; 
    }
5个回答
10

现在这个工作完美了，我们可以将其用作插件。我修改了这篇文章，感谢 sruthi sri。

希望这能帮助到某些人 :)

<?php

class CSVExport
{
/**
* Constructor
*/
public function __construct()
{
if(isset($_GET['download_report']))
{
$csv = $this->generate_csv();

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"report.csv\";" );
header("Content-Transfer-Encoding: binary");

echo $csv;
exit;
}

// Add extra menu items for admins
add_action('admin_menu', array($this, 'admin_menu'));

// Create end-points
add_filter('query_vars', array($this, 'query_vars'));
add_action('parse_request', array($this, 'parse_request'));
}

/**
* Add extra menu items for admins
*/
public function admin_menu()
{
add_menu_page('Download Report', 'Download Report', 'manage_options', 'download_report', array($this, 'download_report'));
}

/**
* Allow for custom query variables
*/
public function query_vars($query_vars)
{
$query_vars[] = 'download_report';
return $query_vars;
}

/**
* Parse the request
*/
public function parse_request(&$wp)
{
if(array_key_exists('download_report', $wp->query_vars))
{
$this->download_report();
exit;
}
}

/**
* Download report
*/
public function download_report()
{
echo '<div class="wrap">';
echo '<div id="icon-tools" class="icon32">
</div>';
echo '<h2>Download Report</h2>';
//$url = site_url();

echo '<p>Export the Subscribers';
}

/**
* Converting data to CSV
*/
public function generate_csv()
{
$csv_output = '';
$table = 'users';

$result = mysql_query("SHOW COLUMNS FROM ".$table."");

$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output = $csv_output . $row['Field'].",";
$i++;
}
}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j].",";
}
$csv_output .= "\n";
}

return $csv_output;
}
}

// Instantiate a singleton of this plugin
$csvExport = new CSVExport();
谢谢你的课程，但是当我使用它时，我的 CSV 格式不正确。我的意思是我没有列。 - Adrien G
对不起，我不明白你缺少什么，但是它对我来说完美地工作了。我在这篇帖子中粘贴的是相同的代码。 - Developer
微软的Excel太烂了...为了按列格式化文档，我不得不用分号代替逗号... - Adrien G
3
我是一个后来者，但对你们的代码进行了一点小的“改进”，并希望分享。如果将代码粘贴到主插件.php文件中，则无需经过3个步骤。只需根据需要在脚本底部更改值即可。我喜欢保持整洁，并为你们添加了大量注释。
对于初学者可能需要这样做，并为每个人添加灵活性：
  1. First add global variable define('MY_PLUGIN_DIR', plugin_dir_path(__FILE__));
  2. After that add require_once(PARTS_MY_PLUGIN_DIR . '/databasestuff/table_to_csv.php')
  3. Under your_plugin_directory/databasestuff/table_to_csv.php save the following class and change the last few lines as required.

  4. Make adjustments to the last few lines

    class export_table_to_csv{

  private $db;
  private $table_name;
  private $separator;


  function __construct($table_n, $sep, $filename){

    global $wpdb;                                               //We gonna work with database aren't we?
    $this->db = $wpdb;                                          //Can't use global on it's own within a class so lets assign it to local object.
    $this->table_name = $table_n;                               
    $this->separator = $sep;

    $generatedDate = date('d-m-Y His');                         //Date will be part of file name. I dont like to see ...(23).csv downloaded

    $csvFile = $this->generate_csv();                           //Getting the text generated to download
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Cache-Control: private", false);                    //Forces the browser to download
    header("Content-Type: application/octet-stream");
    header("Content-Disposition: attachment; filename=\"" . $filename . " " . $generatedDate . ".csv\";" );
    header("Content-Transfer-Encoding: binary");

    echo $csvFile;                                              //Whatever is echoed here will be in the csv file
    exit;

  }


  function generate_csv(){

    $csv_output = '';                                           //Assigning the variable to store all future CSV file's data
    $table = $this->db->prefix . $this->table_name;             //For flexibility of the plugin and convenience, lets get the prefix

    $result = $this->db->get_results("SHOW COLUMNS FROM " . $table . "");   //Displays all COLUMN NAMES under 'Field' column in records returned

    if (count($result) > 0) {

        foreach($result as $row) {
            $csv_output = $csv_output . $row->Field . $this->separator;
        }
        $csv_output = substr($csv_output, 0, -1);               //Removing the last separator, because thats how CSVs work

    }
    $csv_output .= "\n";

    $values = $this->db->get_results("SELECT * FROM " . $table . "");       //This here

    foreach ($values as $rowr) {
        $fields = array_values((array) $rowr);                  //Getting rid of the keys and using numeric array to get values
        $csv_output .= implode($this->separator, $fields);      //Generating string with field separator
        $csv_output .= "\n";    //Yeah...
    }

    return $csv_output; //Back to constructor

  }
}

// Also include nonce check here - https://codex.wordpress.org/WordPress_Nonces
if(isset($_POST['processed_values']) && $_POST['processed_values'] == 'download_csv'){  //When we must do this
  $exportCSV = new export_table_to_csv('table_name',';','report');              //Make your changes on these lines
}

请注意：

  1. 表前缀将添加到表名中。
  2. 此脚本使用核心WordPress函数，这意味着您只需更改最后3行即可使其正常工作。
这种方法会在浏览器上输出数据。 - Khadreal
@Khadreal，这些头部信息会让浏览器将其下载为文件。如果你已经尝试过，那么需要调整头部信息以指定内容类型。 - Dmitriy Kravchuk
2

由于@Developer在管理员中无法正常显示或下载CSV文件，因此对其进行了一些小调整。 但现在它可以正常工作了 :)

<?php

/**
 * CSV Exporter bootstrap file
 *
 * This file is read by WordPress to generate the plugin information in the plugin
 * admin area. This file also includes all of the dependencies used by the plugin,
 * registers the activation and deactivation functions, and defines a function
 * that starts the plugin.
 *
 * @since             1.0.0
 * @package           CSV Export
 *
 * @wordpress-plugin
 * Plugin Name:       CSV Export
 * Plugin URI:        http://example.com/plugin-name-uri/
 * Description:       exports csvs derrr
 * Version:           1.0.0
 * Author:            Your Name or Your Company
 * Author URI:        http://example.com/
 * License:           GPL-2.0+
 * License URI:       http://www.gnu.org/licenses/gpl-2.0.txt
 * Text Domain:       csv-export
 * Domain Path:       /languages
 */
class CSVExport {

  /**
   * Constructor
   */
  public function __construct() {
    if (isset($_GET['report'])) {

      $csv = $this->generate_csv();
      header("Pragma: public");
      header("Expires: 0");
      header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
      header("Cache-Control: private", false);
      header("Content-Type: application/octet-stream");
      header("Content-Disposition: attachment; filename=\"report.csv\";");
      header("Content-Transfer-Encoding: binary");

      echo $csv;
      exit;
    }

// Add extra menu items for admins
    add_action('admin_menu', array($this, 'admin_menu'));

// Create end-points
    add_filter('query_vars', array($this, 'query_vars'));
    add_action('parse_request', array($this, 'parse_request'));
  }

  /**
   * Add extra menu items for admins
   */
  public function admin_menu() {
    add_menu_page('Download Report', 'Download Report', 'manage_options', 'download_report', array($this, 'download_report'));
  }

  /**
   * Allow for custom query variables
   */
  public function query_vars($query_vars) {
    $query_vars[] = 'download_report';
    return $query_vars;
  }

  /**
   * Parse the request
   */
  public function parse_request(&$wp) {
    if (array_key_exists('download_report', $wp->query_vars)) {
      $this->download_report();
      exit;
    }
  }

  /**
   * Download report
   */
  public function download_report() {
    echo '<div class="wrap">';
    echo '<div id="icon-tools" class="icon32">
</div>';
    echo '<h2>Download Report</h2>';
    echo '<p><a href="?page=download_report&report=users">Export the Subscribers</a></p>';
  }

  /**
   * Converting data to CSV
   */
  public function generate_csv() {
    $csv_output = '';
    $table = 'wp_users';

    $result = mysql_query("SHOW COLUMNS FROM " . $table . "");

    $i = 0;
    if (mysql_num_rows($result) > 0) {
      while ($row = mysql_fetch_assoc($result)) {
        $csv_output = $csv_output . $row['Field'] . ",";
        $i++;
      }
    }
    $csv_output .= "\n";

    $values = mysql_query("SELECT * FROM " . $table . "");
    while ($rowr = mysql_fetch_row($values)) {
      for ($j = 0; $j < $i; $j++) {
        $csv_output .= $rowr[$j] . ",";
      }
      $csv_output .= "\n";
    }

    return $csv_output;
  }

}

// Instantiate a singleton of this plugin
$csvExport = new CSVExport();

只需创建一个名为csv_export.php的文件，将其放置在plugins/csv_export/目录下即可完成！

非常有用，但是如何防止未登录的用户下载CSV？他们无法访问管理员页面，但可以访问URL以触发下载。 - hawbsl
请在文档中查看 is_admin() 和 current_user_can() 函数。 - Oliver M Grech
1

我不确定，但可能有几个原因。

你的括号不匹配 - 你缺少一个闭合的 }。

除非你在调用程序中执行此操作，否则你实际上没有将生成的内容发送到任何地方。也许你的意思是 echo $fields; 而不是 return $fields;？

你正在调用 ob_clean() - 你是否开启了输出缓冲？也许你的意思是 ob_end_clean() - 丢弃缓冲区并关闭缓冲？

我正在创建一个用于导出的 CSV；它只使用以下标题就可以工作：

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="' . $csv_file_name . '"');
header('Pragma: no-cache');
header('Expires: 0');

关于与您的调用不同之处：
  1. 您发送了两个 Content-Type 标头。
  2. 我的文件名周围有引号。
  3. 我没有指定 Content-Transfer-Encoding。

我不知道这些差异是否与您的问题有关，我只是列出它们以防它们有所帮助。

1
感谢您的回复和时间。这是头文件的问题，它们没有被首先加载。我尝试了一些东西，现在作为插件使用一个单独的文件。 - Developer
听起来像是我在答案中提到的输出缓冲吗？ - Hobo
0

我遇到了同样的问题。我发现问题不在于头文件代码，而是你点击导出时链接上的问题。你需要添加一个名为“noheader”的参数：

    <a href="admin.php?page=export&export=posts&noheader=1">Export</a>

我用与此问题提到的相同的头代码替换了我的代码，并且它也可以正常工作。 我的导出过程代码如下：

if( isset( $_GET['export'] ) ) {

    $csv = generate_csv();

    $filename = 'results.csv';
    $now = gmdate('D, d M Y H:i:s') . ' GMT';

    header( 'Content-Type: application/octet-stream' ); // tells browser to download

    header( 'Content-Disposition: attachment; filename="' . $filename .'"' );
    header( 'Pragma: no-cache' ); // no cache
    header( 'Expires: ' . $now ); // expire date

    echo $csv;
    exit;
}

最后两个标头告诉浏览器不要缓存导出的内容。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接