Pivot Table

[emseecz]

Hi PHPOffice team,

First of all, thank you for your hard work on PhpSpreadsheet — it's an invaluable tool for many of us.

I’d like to ask about the current status of pivot table support. It seems that pivot tables are still not functional, and the last related issue I could find was opened back in 2021 without recent updates.

Is pivot table support currently being worked on, or is it planned for a future release? If there’s any way to help move it forward (e.g., testing or documentation), I’d be happy to contribute.

Thank you again for your time and dedication.

Best regards, emseecz

[oleibman]

It is a known deficiency that we don't support them. It looks too daunting for me. @MarkBaker did some great work to get tables supported in the first place (I would also have considered that too daunting). I know he started to look into pivot tables, but I don't think he made enough progress to continue the effort.

So, let me ask a question. Would it be of value to you if we added the ability for the Xlsx Reader to read the pivot table files, and for Xlsx Writer to include those in its output? No creation nor manipulation of pivot tables within PhpSpreadsheet, just passing them through, possibly with some restrictions on what actions you can take with the non-pivot-table part of the spreadsheet?

[emseecz]

Thank you very much for your reply and for the clarification.

Yes, I believe it would absolutely be valuable to have the ability to read and write pivot table definitions — even without the ability to create or manipulate them directly in PhpSpreadsheet. The use case of loading a template file with a pre-defined pivot table, updating the underlying data, and then writing it back while preserving the pivot table would be extremely helpful in many real-world scenarios.

If this kind of pass-through support is feasible (even with some limitations), it would already cover a lot of needs and workflows.

Thanks again for your work and consideration!

Kind regards

[Buyur]

Not sure if this has progressed but @emseecz has hit the nail on the head for me - any way to pass through pivot tables etc would be amazing - thank you everything everyone has done so far - not sure how I can contribute code-wise but happy to test, log and feedback if any work on this or other features ...

[oleibman]

Sorry for the absence of progress. Excel adds so many extra files to a spreadsheet in order to support pivot tables that it is difficult to figure out what exactly it is trying to do with all of them.

[Buyur]

Please dont apologise as what you do is really appreciated and makes such a difference... honestly I just wish I was a better coder to be able to help.

There is a sorta workaround which I have used in the past and just used today for another report... use the .xlsm as the template and embed a macro on the open workbook event. The in the Developer menu use record macro to record you creating a pivot table from a named range in another sheet in the workbook. Then save as the template and use phpspreadsheet to populate the source data and name the range. When you open the Workbook, the macro fires and the pivot is created. Its not an all-in-one but it does work! Everything you do can be recorded in a macro and then copied into the open VBA.

Thank you again for one of the most game-changing code libraries - makes so much possible :-)