How to download the data as csv file in WordPress - deal with the PHP old version | Apple Rinquest

Yesterday, I had a request to implement the download data as an excel file from WordPress. The client’s business sells wholesale and retail products. They want their partners to be able to download the product detail as PDF and Excel files. The PDF file is already implemented using the Mpdf library (I will write the mpdf tutorial sometime in the future). Today I will share with you how to implement the download as an excel file.

Table of Contents

Deal with PHP old version

At first, I planned to use the phpspreadsheet library which I used it for other clients but this client’s server has the old PHP version. This library doesn’t work and gives the error because the complex function in the library doesn’t work with the PHP old version ( the phpspreadsheet library requires at least PHP 5.6 ). If your server runs with PHP 7.x.x or 5.6.x, you don’t need to use the method that I am gonna talking about here. I recommend using the phpspreadsheet library. I will find time to write the phpspreadsheet library tutorial sometime soon.

The solution

I will use only MIME Types setting for the HTML header and print out the data. The file extension will be a CSV file which it has the comma-delimited. The file can open in MS Excel, Google Sheet online, WPS office and OpenOffice without any issues. However, you can not set the format cell or format row as same as using phpspreadsheet library.

Why don’t I just use MIME types for the xlsx file?

If I use the MIME types for the xlsx file, when I open the file in MSExcel old version(2015 and older version), I see the untrusted warning message which is annoying and it is not professional to let this warning message be seen by the business partners. The untrusted warning message occurs only in MSExcel’s old version only.

How to download the data as CSV file – step by step

Create a function calls print_csv. You can create in the functions.php or in your own plugin. In my case, I add to my own plugin.

function print_csv() {
   ...
}

Fetch the data from the database. If you store the data in the postmeta table, you will use the WordPress API. If you store the data in your own custom table, you will use $wpdb object. In my case, I use both.

function print_csv() {
   
   // # fetch data from the database
   // create your own query here...
}

Add the MIME types at the header to tell the browser what content type you are working on

function print_csv() {
   
    // # fetch data from the database
    // create your own query here...
   
    // # add MIME types at the header
    header('Content-Type: text/csv; charset=UTF-8;');
    header('Content-Disposition: attachment; filename="'. "example-" . time() .'.csv"');  
}

Now print out your data using echo(). In the code below, I add sample data in $header, $content and $footer. You must replace the data from your database.

/** 
* This code may give your the unexpected result.
*/
function print_csv() {
   
    // # fetch data from the database
    // create your own query here...
    $header = 'AppleRinquest Shop,Thailand';
    $content = 'Date:July2,2019';
    $content .= 'Product title:,Ring001';
    $content .= 'Price per piece:,150,000 bath';
    $footer = 'Copyright  © AppleRinquest Shop limited.';
   
    // # add MIME types at the header
    header('Content-Type: text/csv; charset=UTF-8;');
    header('Content-Disposition: attachment; filename="'. "example-" . time() .'.csv"');  
    
    // # print out your data
    echo $header;
    echo "\n";  // add new line
    echo "\n";  // add new line
    echo $content;
    echo "\n";  // add new line
    echo "\n";  // add new line    
    echo $footer;
}

For the code above, the download file may include an unexpected result. The full correct version is below and I explain how to fix it.

/**
* The full correct version without any issues.
* It works in MSExcel(2013 and newer), WPS office, Google Sheet online and Openoffice.
* Last test on 2 July 2019
*/
function print_csv() {
   
    // # fetch data from the database
    // create your own query here...
    $header = 'AppleRinquest Shop,Thailand';   // the data will show in 2 cells
    $content = '"' . 'Date:July2,2019' . '"';  // the data will show in 1 cell. we use double quote around the text in order to print out the comma without split the cell.
    $content .= 'Product title:,Ring001';
    $content .= 'Price per piece:,' . '"' . '150,000 bath' . '"';  // the data will show in 2 cells.
    $footer = html_entity_decode( 'Copyright  © AppleRinquest Shop limited.' , ENT_QUOTES, 'UTF-8');  // add html_entity_decode() to decode the HTML entity from the text if any.
   
    // # add MIME types at the header
    header('Content-Type: text/csv; charset=UTF-8;');  // tell the browser that this is the CSV file and encode UTF8.
    header('Content-Disposition: attachment; filename="'. "example-" . time() .'.csv"');    // tell the browser to let the viewers can download the file with the default filename as provided.
    
    // # to protect the MSExcel(2013 and older version) replaces the accent marks to the question mark(?)
    // I add these 3 byte UTF8 here before print out the first line to CSV file.
    echo chr(0xEF);
    echo chr(0xBB);
    echo chr(0xBF);
    
    // # print out your data
    echo $header;
    echo "\n";  // add new line
    echo "\n";  // add new line
    echo $content;
    echo "\n";  // add new line
    echo "\n";  // add new line    
    echo $footer;
    exit; // add the exit or die() after the last print out content to the CSV file.
          // otherwise, you may see all the current HTML code will print out to the CSV file too.
}

And that’s it. When you call the print_csv function, it will let you download the CSV file. You can open this file in MSExcel without any issue.