Home » BLOG » WordPress » Export the data as Excel file in WordPress

Export the data as Excel file in WordPress

category:  WordPress
4 min read

Last month, I had a request from my client to create the export data as the excel file in WordPress. The client has their own server and they wanted to focus on memory usage. Plus, the client uses the PHP 5.5 which is pretty old. I found one PHP class that met the client’s requirement. The PHP class is PHP_XLSXWriter.

Let’s say, we create an archive download template in which the download is a custom post type. On the template, we add the option to download the data as an excel file. Once the users select the option, it will prompt for download to your computer. Below is a screenshot as I mention.

wordpress download 1
an archive download template

Here are the steps we will do

Download the PHP_XLSXWriter class

Follow this link to download the class from Github.

Create a new plugin

In this tutorial, I will create the download functionality in the plugin instead of functions.php. Here is the plugin structure.

  • plugins
    • download-excel
      • download-excel.php
      • inc
        • xlsxwriter.class.php

A download-excel.php is the main file of our plugin. An xlsxwriter.class.php is downloaded from the link above. Paste the code below into the download-excel.php. Note that, I write the code as the class-based.

<?php

/**
 * Plugin Name: Download data as excel
 * Description: Download data as excel
 * Version: 1.0
 * Author: Apple Rinquest
 * Author URI: https://applerinquest.com
 */

class DOWNLOAD_EXCEL
{
  function __construct()
  {
    // I use the download feature on the frontend so I use the init action hook.
    // If you use the download feature on the backend, you will use the admin_init action hook.
    add_action('init', array($this, 'print_excel'));
  }


  function print_excel()
  {

    // # check the URL in order to perform the downloading
    if (!isset($_GET['excel']) || !isset($_GET['download_excel'])) {
      return false;
    }


    // # check the XLSXWriter class is already loaded or not. If it is not loaded yet, we will load it.
    if (!class_exists('XLSXWriter')) {
      include_once('inc/xlsxwriter.class.php');
    }

    // # set the destination file
    $fileLocation = 'output.xlsx';

    // # prepare the data set
    $data = array(
      array('year', 'month', 'amount'),
      array('2003', '1', '220'),
      array('2003', '2', '153.5'),
    );

    // # call the class and generate the excel file from the $data
    $writer = new XLSXWriter();
    $writer->writeSheet($data);
    $writer->writeToFile($fileLocation);


    // # prompt download popup
    header('Content-Description: File Transfer');
    header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    header("Content-Disposition: attachment; filename=" . basename($fileLocation));
    header("Content-Transfer-Encoding: binary");
    header("Expires: 0");
    header("Pragma: public");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header('Content-Length: ' . filesize($fileLocation));

    ob_clean();
    flush();

    readfile($fileLocation);
    unlink($fileLocation);
    exit;
  }
}

// # initialize the class
new DOWNLOAD_EXCEL();

Now the plugin is done.

A new archive download template

We will use the custom post type template. The custom post type is download.

In order to create the download post type, you can use the Custom Post Type UI plugin. Once you create the download post type, you have to set the Has Archive as true. We set the Has Archive because we want our URL to be https://yoursite.com/download.

Next, create the new template names archive-download.php. Then add the code below in the template. We will use the dropdown component from Bootstrap 4. The dropdown component can add the link to each option.

<?php
get_header();
?>

<!-- Normally, you don't need to add the style and scripts in the template like this example. You will add those in the header (get_header()). I add the style and scripts here for quick implement. -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" integrity="sha384-ApNbgh9B+Y1QKtv3Rn7W3mgPxhU9K/ScQsAP7hUibX39j7fakFPskvXusvfa0b4Q" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>


<div class="container">
  <div class="row">
    <div class="col-sm-12">
      <h1>Download</h1>
    </div>
  </div>
</div>


<?php
// # build the URL and query string
$get_vars_excel = $_GET;
$get_vars_excel['excel'] = true;
$get_vars_excel['download_excel'] = true;
$excel_url = site_url() . '?' . http_build_query($get_vars_excel);
?>
<div class="container">
  <div class="row">
    <div class="col-sm-12">
      <!- download options ->
        <div class="dropdown">
          <button class="btn btn-success dropdown-toggle" type="button" id="dropdownMenuButton" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
            Download
          </button>
          <div class="dropdown-menu" aria-labelledby="dropdownMenuButton">
            <a class="dropdown-item" href="<?php echo $pdf_url ?>">Download as PDF</a>
            <a class="dropdown-item" href="<?php echo $excel_url ?>">Download as Excel</a>
          </div>
        </div>
    </div>
  </div>
</div>

<div class="container">
  <div class="row">
    <div class="col-sm-8">
      <?php
      if (have_posts()) : while (have_posts()) : the_post();
          the_title();
          echo '<div class="entry-content">';
          the_content();
          echo '</div>';
        endwhile;
      endif;
      ?>
    </div>
  </div>
</div>

<?php
get_footer();

Now the archive download template is complete.

Finally, you can activate the download excel plugin and visit the https://yoursite.com/download to see the result. Note that, the design template based on your active theme. You won’t see the design as same as the screenshot I gave you.

For downloading the PDF file, you can do the same steps. Just change from Excel class to PDF library you like. I normally use the mPDF library.

That’s it for today. Hope my post gives you some idea and be helpful for your work.