0

Good Afternoon,

For a long time I've used a PHP enabled template to export channel data to a Microsoft Excel spreadsheet and it's worked very well. Recently, I've discovered that since everyone has now upgraded their Excel and the default extension is now XLSX instead of XLS, an alert is displayed when opening the file. If you click proceed anyway you can still open the spreadsheet but if possible I'd like to now export to XLSX instead of XLS as to avoid this alert message. Please find an example of the alert I'm talking about below:

EE Excel Export Alert

In my export template I have 'Allow PHP' set to 'Yes' and 'PHP Parsing Stage' set to 'Output'. The template contains a basic HTML table that is setup to output the channel data I want in my spreadsheet and below is the PHP I have at the top of this template that generates the Excel file:

<?php
    header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=My-Channel-Data Spreadsheet.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
?>

Does anyone know of a way to adjust the code above so that the spreadsheet is exported as a modern Excel file using the XLSX extension? Any tips or suggestions to achieve this would be greatly appreciated, Thanks!

1

You should try to export data into XLSX format with PHP_XLSXWriter. You can try below code which is example of PHP_XLSXWriter.

<?php  
    include_once('xlsxwriter.class.php'); /*you can get xlsxwriter.class.php from given GitHub link*/
    $filename = "example.xlsx";
    header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($filename).'"');
    header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    header('Content-Transfer-Encoding: binary');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');   

    $header = array(
      'ID'=>'integer',
      'Subject'=>'string',
      'Content'=>'string',
    );

    $writer = new XLSXWriter();
    $writer->writeSheetHeader('Sheet1', $header);

    $array = array(
        '0' => array('1','Test Subject','Test Content'),
    );

    $writer->writeSheetRow('Sheet1', $array);
    $writer->writeSheet($array,'Sheet1', $header);
    $writer->writeToStdOut();
    exit(0);
?>
1
  • Thank you for your suggestion! I came across this earlier today while working on this and attempted to use the exact same example you have above from the GitHub Repo for XLSXWriter but the example spreadsheet that downloads is unable to be opened and Excel reports "The file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file." I verified that the file downloaded is indeed named 'example.xlsx' Any ideas on how to resolve that? – Andrew Currie Jan 19 '18 at 18:05
0

I just got the corrupted file error resolved and this is now working. The problem with the example above and also the one from the GitHub repo is that the Content-Type header needs to be using single quotes instead of double quotes.

So change this line:

    header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

To the following:

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

Once you make that adjustment the downloaded spreadsheet opens just fine, Thanks!

1
  • I've now ran into another problem and that is all my EE channel fields are exporting as the tags themselves rather than the data they contain. My original Excel export used an HTML table that allowed EE tags the XLSX Writer is using a PHP array and I'm not sure how to use EE tags inside an array yet. If anyone is familiar with a way to do that, please advise, Thanks! – Andrew Currie Jan 19 '18 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.