php wordpress export-to-excel phpspreadsheet phpoffice-phpspreadsheet

Downloading generated excel only works locally | WordPress

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 PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;

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 \PhpOffice\PhpSpreadsheet\Spreadsheet();
  $spreadsheet->setActiveSheetIndex(0);
  $spreadsheet->getActiveSheet()->fromArray($array,NULL,'A1');
  $spreadsheet->getActiveSheet()->setTitle($title);

  ob_end_clean();
  $writer = \PhpOffice\PhpSpreadsheet\IOFactory::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!

Solution

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 PhpOffice\PhpSpreadsheet\Spreadsheet;
      use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
      use PhpOffice\PhpSpreadsheet\IOFactory;
      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>...