Downloading generated excel only works locally

I have a word press page that shows a report as an html table, on button click, that report should be downloaded as xlsx file.

The problem is that while download feature works perfectly on my local machine, on the server I get gibberish like this echoed on the screen, instead of file downloading:

PKǂ�RG�D�Z�[Content_Types].xml��N …

Here is the relevant code:

page-export.php

<?php

require_once 'vendor/autoload.php';
global $wpdb;

use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;
use PhpOfficePhpSpreadsheetIOFactory;

if ( !post_password_required() ):

  // fetching data
  $query = "SELECT *,
                 (SELECT COUNT(*) FROM library_votes
                  WHERE libraries.id = library_votes.library_id) AS votes
          FROM libraries ORDER BY votes DESC";
  $libraries = $wpdb->get_results ( $query );

  // download on click
  if(isset($_GET['action']) && $_GET['action'] == 'download') {
    $header = ['Col1', 'Col2', 'ColN];
    $libArray = json_decode(json_encode($libraries), true);
    array_unshift($libArray, $header);
    array_columns_delete($libArray, ['ID']);

    // BUILD EXCEL FILE!
    outputXlsx($libArray, 'excel_filename', 'sheetname');
    exit();
  }
?>

// download btn
<a href="<?php echo esc_url(get_permalink())?>?action=download" class="btn btn-secondary btn-lg" tabindex="-1" role="button" aria-disabled="true">Download</a>

// loop & html for table
...

<?php 

else:
  echo get_the_password_form();
endif;
?>

JavaScript
​x
 
<?php​require_once 'vendor/autoload.php';global $wpdb;​use PhpOfficePhpSpreadsheetSpreadsheet;use PhpOfficePhpSpreadsheetWriterXlsx;use PhpOfficePhpSpreadsheetIOFactory;​if ( !post_password_required() ):​  // fetching data  $query = "SELECT *,                 (SELECT COUNT(*) FROM library_votes                  WHERE libraries.id = library_votes.library_id) AS votes          FROM libraries ORDER BY votes DESC";  $libraries = $wpdb->get_results ( $query );​  // download on click  if(isset($_GET['action']) && $_GET['action'] == 'download') {    $header = ['Col1', 'Col2', 'ColN];    $libArray = json_decode(json_encode($libraries), true);    array_unshift($libArray, $header);    array_columns_delete($libArray, ['ID']);​    // BUILD EXCEL FILE!    outputXlsx($libArray, 'excel_filename', 'sheetname');    exit();  }?>​// download btn<a href="<?php echo esc_url(get_permalink())?>?action=download" class="btn btn-secondary btn-lg" tabindex="-1" role="button" aria-disabled="true">Download</a>​// loop & html for table...​<?php ​else:  echo get_the_password_form();endif;?>​

functions.php

function outputXlsx($array, $filename = 'zones',$title = 'Sheet', $debug = false) {

  if (!$debug) {
      header('Content-Type: application/vnd.ms-excel');
      header('Content-Disposition: attachment;filename="'. $filename .'.xlsx"');
      header('Cache-Control: max-age=0');
  }

  $spreadsheet = new PhpOfficePhpSpreadsheetSpreadsheet();
  $spreadsheet->setActiveSheetIndex(0);
  $spreadsheet->getActiveSheet()->fromArray($array,NULL,'A1');
  $spreadsheet->getActiveSheet()->setTitle($title);

  ob_end_clean();
  $writer = PhpOfficePhpSpreadsheetIOFactory::createWriter($spreadsheet, 'Xlsx');
  $writer->save('php://output');   
}

JavaScript
 
function outputXlsx($array, $filename = 'zones',$title = 'Sheet', $debug = false) {​  if (!$debug) {      header('Content-Type: application/vnd.ms-excel');      header('Content-Disposition: attachment;filename="'. $filename .'.xlsx"');      header('Cache-Control: max-age=0');  }​  $spreadsheet = new PhpOfficePhpSpreadsheetSpreadsheet();  $spreadsheet->setActiveSheetIndex(0);  $spreadsheet->getActiveSheet()->fromArray($array,NULL,'A1');  $spreadsheet->getActiveSheet()->setTitle($title);​  ob_end_clean();  $writer = PhpOfficePhpSpreadsheetIOFactory::createWriter($spreadsheet, 'Xlsx');  $writer->save('php://output');   }​

I’ve tried adding additional headers I’ve found on similar questions, toggling ob_end_clean(), exit function after outputXlsx… etc. but It still only works locally.

Thanks in advance!

Answer

I finally fixed it!

Enabling debug mode in wordpress gave me the clues to solution with these two warnings:

Warning: Cannot modify header information – headers already sent
ob_end_clean(): failed to delete buffer. No buffer to delete

It turns out that all I had to do was to place the code for excel generation at the top of the page before html and add ob_start().

page-export.php:

//line 1 of the file
<?php
      ob_start(); // create buffer fo ob_end_clean()
      require_once 'vendor/autoload.php';
      global $wpdb;

      use PhpOfficePhpSpreadsheetSpreadsheet;
      use PhpOfficePhpSpreadsheetWriterXlsx;
      use PhpOfficePhpSpreadsheetIOFactory;
      if ( !post_password_required() ):
      $query = "SELECT *,
                       (SELECT COUNT(*) FROM library_votes
                        WHERE libraries.id = library_votes.library_id) AS votes
                FROM libraries ORDER BY votes DESC";
      $libraries = $wpdb->get_results ( $query );

      if(isset($_GET['action']) && $_GET['action'] == 'download') {
          $header = ['Col1', 'Col2', 'ColN'];
          $libArray = json_decode(json_encode($libraries), true);
          array_unshift($libArray, $header);
          array_columns_delete($libArray, ['ID']);
          outputXlsx($libArray, 'filename', 'sheetname');
          exit();
      }
?>

// html after
<html>...

JavaScript
 
//line 1 of the file<?php      ob_start(); // create buffer fo ob_end_clean()      require_once 'vendor/autoload.php';      global $wpdb;​      use PhpOfficePhpSpreadsheetSpreadsheet;      use PhpOfficePhpSpreadsheetWriterXlsx;      use PhpOfficePhpSpreadsheetIOFactory;      if ( !post_password_required() ):      $query = "SELECT *,                       (SELECT COUNT(*) FROM library_votes                        WHERE libraries.id = library_votes.library_id) AS votes                FROM libraries ORDER BY votes DESC";      $libraries = $wpdb->get_results ( $query );​      if(isset($_GET['action']) && $_GET['action'] == 'download') {          $header = ['Col1', 'Col2', 'ColN'];          $libArray = json_decode(json_encode($libraries), true);          array_unshift($libArray, $header);          array_columns_delete($libArray, ['ID']);          outputXlsx($libArray, 'filename', 'sheetname');          exit();      }?>​// html after<html>...​

Downloading generated excel only works locally | WordPress

Advertisement

Answer