Search code examples
phpexcelphpspreadsheet

PHPSpreadsheet - Multiple tables with "format as table" in excel worksheet

I try to have multiple formatted tables in one worksheet. The template looks like following example: Template

The tables are styled with table format templates.

If i run the code: 

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = 'template/Age.xlsx';
$inputFileType = 'Xlsx';
if (!file_exists($inputFileName)) {
    echo('File ' . $inputFileNameShort . ' does not exist');
}
$reader = IOFactory::createReader($inputFileType);
$spreadsheet = $reader->load($inputFileName);

$writer = new Xlsx($spreadsheet);
$writer->save(Age.xlsx);
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);

The formation is not overtaken to the new Age.xlsx file.

If I try to style the tables by hand, I run in an issue with the AutoFilter. It seams to be that only one filter range can be set. I tried following code:

$ageSheet =$spreadsheet->getSheet(0);
$ageSheet->setAutoFilter('A3:B10');
$ageSheet->setAutoFilter('D3:E9');
$ageSheet->setAutoFilter('A17:B24');
$ageSheet->setAutoFilter('D17:E23');

Only the last range will be set.

My questions are:

  1. Is it possible to have more then one table in a worksheet using PHPSpreadsheet?
  2. How can I realize this kind of output shown above?

Version

  • Excel MS Excel 2013
  • PHPSpreadsheet [1.2.1] - 2018-04-10
Solution

  • In MS excel can be set only one real filter. To have more then one on a Worksheet, you have to use format templates. Format templates uses pivot tables to realize the multifilter behavior.

    PHPspreadsheet uses the table filtering and overrides the filtering every time by use of the setAutoFilter method. That means onlyone per worksheet is posible.

    There is at the Moment no support of pivot tables in PHPspreadsheet.

    At the moment it is not possible to have more the one filtered table in one worksheet.