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