Miss Dynamic Tables

[ovgp]

This is:

- [ ] a bug report
- [V] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Open the excel file (.xlsx) with dynamic tables and save the same file with dynamic tables

What is the current behavior?

The spreadsheet opens the excel file and saves the file without dynamic tables. Only plain text.

What are the steps to reproduce?

Use reader fuction to open the file Force to readonly Define a worksheet Use writer function to save the file

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

// add code that show the issue here...

$inputFileName = 'template-sample.xlsx';

$inputFileType = IOFactory::identify($inputFileName); $reader = IOFactory::createReader($inputFileType); $reader->setReadDataOnly(true); $spreadsheet = $reader->load($inputFileName);

$spreadsheet->setActiveSheetIndex(1);

$writer = IOFactory::createWriter($spreadsheet, "Xlsx"); $writer->save("template-test.xlsx");

Which versions of PhpSpreadsheet and PHP are affected?

1.18.0

[MarkBaker]

Correct... none of the Readers support dynamic tables like pivot tables. That is something we are currently investigating

[ovgp]

Correct... none of the Readers support dynamic tables like pivot tables. That is something we are currently investigating

Thanks in advance. I think it's a very important function because I need to integrate with some softwares, for example, Microsoft Dynamics NAV (from Rapid Start).

I need to use https://products.aspose.app/cells/editor to keep Dynamic Tables.

Are you working on that? It will take long time?

Thanks in advance.

[MarkBaker]

I just started work on the absolute basics of Pivot Tables for the Xlsx Reader/Writer two weeks ago... at the moment, I have some very simplistic code that just allows the pivot table to be read and written, without any form of editing or access to the data, just so that it won't get lost in a read/write; but that doesn't handle pivot tables that have any form of customisation (even something as basic as a custom style will trigger errors on the save) It's likely to take several months of work to get something robust enough to merge, and that depends how much time I get to work on it over the Summer.

[slombart]

Hello @MarkBaker,

With new sources from GitHub, i tried to open a xlsx with pivot table, change rows in sheet containing data but newly generated xlsx file continue to drop pivot table. I don't need to change anything in pivot table, the user will update this on opening.

Simple example with Reader/Writer :

$objPHPExcel = \PhpOffice\PhpSpreadsheet\IOFactory::load("test.xlsx", \PhpOffice\PhpSpreadsheet\Reader\IReader::LOAD_WITH_CHARTS | \PhpOffice\PhpSpreadsheet\Reader\IReader::LOAD_WITH_PIVOT_TABLES);
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($objPHPExcel, 'Xlsx');
$writer->save('newFile.xlsx', \PhpOffice\PhpSpreadsheet\Writer\IWriter::SAVE_WITH_PIVOT_TABLES);

Files are not the same, newFile.xlsx not including pivot table.

Seems the problem is in writer, in Xlsx.php file not mention of Pivot in file.

Thanks !

[dromaniz]

Hello! Have we any news on this?

[slombart]

Hello @dromaniz ,

No news, i'm using mix of https://github.com/svrnm/exceldatatables and macros to get job done...

[warrior0986]

Hello @dromaniz ,

No news, i'm using mix of https://github.com/svrnm/exceldatatables and macros to get job done...

Hello @slombart, can you provide an example of how implement this with exceldatatables and macros, I have been trying for 2 weeks but it does not work for me, the excel file is always corrupted when I try to open it.

Thanks so much

[slombart]

Hello @warrior0986,

First, i create my Excel file with sample pivot table (one sheet called "Pivot" containing pivot table and one "Data" containing base data). I add a macro "on open" to refresh pivot table :

Sub AdjustPivotDataRange()
'PURPOSE: Automatically readjust a Pivot Table's data source range
'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault

Dim Data_sht As Worksheet
Dim Pivot_sht As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String

'Set Variables Equal to Data Sheet and Pivot Sheet
  Set Data_sht = ThisWorkbook.Worksheets("Data")
  Set Pivot_sht = ThisWorkbook.Worksheets("Pivot")

'Enter in Pivot Table Name
  PivotName = "PivotTable1"

'Dynamically Retrieve Range Address of Data
  Set StartPoint = Data_sht.Range("A1")
  Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))

  NewRange = Data_sht.Name & "!" & _
    DataRange.Address(ReferenceStyle:=xlR1C1)

'Make sure every column in data set has a heading and is not blank (error prevention)
  If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then
    MsgBox "One of your data columns has a blank heading." & vbNewLine _
      & "Please fix and re-run!.", vbCritical, "Column Heading Missing!"
    Exit Sub
  End If

'Change Pivot Table Data Source Range Address
  Pivot_sht.PivotTables(PivotName).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)

'Ensure Pivot Table is Refreshed
  Pivot_sht.PivotTables(PivotName).RefreshTable

End Sub

Then in my PHP i fill the worksheet Data with exceldatatables :

$dataTable = new Svrnm\ExcelDataTables\ExcelDataTable();
$baseFile = 'baseFile.xlsm';
$data = array();

foreach ( ... ) {

    $lineInfo = array(
        "Col1" => $data1,
        "Col2" => $data2,
        "Col3" => $data3,
        "Col4" => $data4,
        ...,
    );
    array_push($data, $lineInfo);
}

$fileContent = $dataTable->showHeaders()->addRows($data)->fillXLSX($baseFile);
file_put_contents('final.xlsm', $fileContent);

Then when we open final.xlsm it builds pivot table.

Regards

[warrior0986]

Thanks @slombart , It works!!!

[MarkBaker]

c.f. PR #2671 merged for release in version 1.23.0

[raost]

Hello! Have we any news on this? When can a solution be expected?

[LikenessWeb]

Looking for some feedback on this issue as well. I’m on 1.28. The reader does not even have the option for LOAD_WITH_PIVOT_TABLES. The workaround for adding a macro is not acceptable, most of my users, hospitals, don’t allow office documents with macros because of the security risks. Do we know if the root cause of this is in the reader? Writer? Or both? Looking for suggestions. I have even interrogated ChatGPT for answers on this and nothing.

[MarkBaker]

Looking for some feedback on this issue as well. I’m on 1.28.

Here's some feedback. Tables are now supported, and have been supported since 1.26.0; while 1.27.0 introduced support for Structured References in formulae to work with Tables. Pivot Tables aren't yet supported.

The reader does not even have the option for LOAD_WITH_PIVOT_TABLES.

Nor will there be an option until I add support for Pivot Tables.

Do we know if the root cause of this is in the reader? Writer? Or both?

The root cause for this is that I don't have enough hours in the day to work more on adding this feature.

I have even interrogated ChatGPT for answers on this and nothing.

Unless ChatGPT can read my mind and my work schedule, then I wouldn't expect it to provide any answers

[LikenessWeb]

Thanks for the quick reply, I was under the impression this was all supported. So my bad. I’m not super familiar with this project, code wise, but I wouldn’t mind contributing to it, if you pointed me to some of the areas in the project I should be looking at. Believe me, I understand the hours in a day issue. I think even if we approach this from a read only perspective, perhaps a feature that says read in a sheet as a blob and write it back out as a blob, without any support to interact with the pivot table, would probably solve most everyone’s issue. The use case is we are using a template excel file that we just want to add data to.

Hope this makes sense…again happy to help if you point me in the right direction.

[MarkBaker]

Thanks for the quick reply, I was under the impression this was all supported.

Nope! I've attempted to handle reading pivot tables a couple of times now, and even had some success there; but the problems arise with writing (even if I don't allow modification of the pivot definition from PHP).

I’m not super familiar with this project, code wise, but I wouldn’t mind contributing to it, if you pointed me to some of the areas in the project I should be looking at. Believe me, I understand the hours in a day issue.

This year, I've even cut down my professional working life, giving me an extra day each week to work on PhpSpreadsheet. Pivot Tables are one of the three big enhancements that I want to work on this year - the others being full support for Excel's Array Formulae (almost complete in its own branch here, but includes bc breaks, so it's labelled as 2.0-dev), and support for non-scalar Cell Values (the dynamic data under Data/Data Types on the Excel ribbon menu).

I think even if we approach this from a read only perspective, perhaps a feature that says read in a sheet as a blob and write it back out as a blob, without any support to interact with the pivot table, would probably solve most everyone’s issue. The use case is we are using a template excel file that we just want to add data to.

Unfortunately, nothing is as simple as that. An Excel spreadsheet (xlsx) is a series of xml files. And a pivot table isn't simply stored in a single file, but the data is spread across several files, and interwoven with other elements of data (e.g. styles). We don't simply read the pivot table data from a single place, but have to link it internally to all the related information; and when it's written, we have to write all of those related elements of data as well. If we don't have everything linked when we write the file, then that file will error when it is opened in MS Excel; which is an even worse situation than simply discarding the pivot definition.

Providing support for basic Tables is already a big step; and Pivot Tables build on that. It will happen, almost certainly this year; but there aren't any shortcuts that can speed up that work.