Last month, I had a request from my client to create the export data as an Excel file in WordPress. The client has their own server and they wanted to focus on memory usage. Plus, the client uses PHP 5.5 which is pretty old. I found one PHP class that met the client’s requirements. The PHP class is PHP_XLSXWriter.
Let’s say, we create an archive download template and the download is a custom post type. On the archive template, we add the option for downloading the data as an Excel file. Once the users select the option, it will prompt for download to their computer. Below is a screenshot as I mention.
Download PHP_XLSXWriter class
PHP_XLSXWriter is designed to output an Excel-compatible spreadsheet in xlsx format. The class supports the features below.
- supports PHP 5.2.1+
- UTF-8 encoded input
- multiple worksheets
- supports currency/date/numeric cell formatting, simple formulas
- supports basic cell styling
- supports writing huge 100K+ row spreadsheets
Download the PHP_XLSXWriter class from GitHub.
Create New Download Plugin (a custom plugin)
In this tutorial, I will create the download functionality via the plugin instead of using the functions.php. The idea is that users can disable the plugin if they don’t need it so that the WP won’t load this plugin. It saves loading time.
Here is the Download plugin structure.
- plugins
- download-excel
- download-excel.php
- inc
- xlsxwriter.class.php
- download-excel
A download-excel.php is the main file of our plugin. An xlsxwriter.class.php is downloaded from Github. You can simply copy and paste the xlsxwriter.class code from GitHub into our xlsxwriter.class.php in our plugin.
Below is the code for the download-excel.php. Note that, I write the code as 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 our Download plugin is done.
A New Archive Download Template
We will create the custom post type called “download” and we will create the custom post type template for it.
Create a Download Custom Post Type
In order to create the download post type, you can use the Custom Post Type UI plugin which is a simple way and fast. Another way, you can create the custom post type using the custom code in the functions.php. It is your choice.
Once you create the download post type, you have to set the Has Archive as true. So that we can use the archive custom post-type template. The archive download template can access from the front end via https://yoursite.com/download.
Create an Archive Download Template
Next, create a new template name archive-download.php in the “wp-content/your-active-theme”. Then add the code below in the template file.
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.
Active the Download plugin
Finally, you can activate the download Excel plugin and visit https://yoursite.com/download to see the result.
Note that, the design template is based on your active theme. You won’t see the design as the same as the screenshot I gave you in this post.
Download as a PDF file
For downloading the PDF file, you can do the same steps above. Just change from the Excel class to the PDF library you like. I normally use the mPDF library.
That’s it for today. Hope my post gives you some ideas and is helpful for your work. If my post is useful and saves you time, please considering buy me a coffee. It gives me the energy to maintain and publish more posts.
