How to Generate Excel File With Autofilters in PHP?

Question

Okay, here's my dilemma.

I've been working on a Wordpress plugin for Medical Marcom to automatically update his List of US Twitter Doctors. Basically, it provides the ability to create a form where users can request to be added to the list, requests can be confirmed in the admin panel, when added they're available in the Excel file and the initial data is filled out, and finally, certain fields are automatically updated throughout the week.

Here's the problem.

My code is generating an Excel file with PHP using PHPExcel. However, I need to have a simple autofilter applied to the sheet at startup (honestly, I don't know what the big deal is... anyone can easily apply an autofilter in Excel, but he wants it available from the start). So, I tried applying the code I found:

$excel->getActiveSheet()->setAutoFilter('A1:J' . $row);

$excel is my PHPExcel instance. $row is the last row being outputted from the database. The file is generated immediately when the url is clicked and PHP's headers are set to translate the output as an Excel file, like so:

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=" . $file);

But when I open the file, no autofilters are set... I tried posting a question over at PHPExcel's website, but I didn't get any replies, so I decided to ask here.

Does anyone know what I may be doing wrong? For now he's going with the original file (updated a bit, though) until this issue is resolved.

Orthopsychiatry

Answer 1

In case anyone comes across this question. The feature has been implemented both for XLSX and XLS.

You just need to specify the range of your header row for it to work:

$excel->getActiveSheet()->setAutoFilter('A1:J1');

Recognize

Answer 2

PHPSpreadsheet have a much nicer implementation if you want your whole sheet auto-filtered:

    $sheet->setAutoFilter(
        $sheet->calculateWorksheetDimension()
    );

Orchidectomy

Answer 3

I understand from this link that it has not been implemented yet.

It seems like it is still a work item (with a low priority sorry).

[EDIT] seems like it may work with Excel 2007 (see this work item). Which version of Excel do your client use?

Olivette

I'll have to ask him about it. I've seen that it HAS been implemented, and it shows it right in the documentation, but it didn't work for me. Another person who posted a work item apparently had got it working, it just didn't have the advanced features. I was starting to think, though, that I may have been trying to output the wrong type of Excel file. I'll go and ask my client if using Excel 2007 is fine. The current file is 2003, though, unfortunately... – Orthopsychiatry