PHP Classes

PHP Excel Pivot Tables: Extends PHP Excel classes with pivot table support

Recommend this page to a friend!
     
  Info   View files Example   View files View files (17)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog (1)    
Last Updated Ratings Unique User Downloads Download Rankings
2023-05-29 (10 months ago) RSS 2.0 feedNot yet rated by the usersTotal: 76 This week: 1All time: 10,148 This week: 80Up
Version License PHP version Categories
php-pivottables-4-ex 1.0.0Custom (specified...5PHP 5, Files and Folders, Business
Description 

Author

Bill

This package extends PHP Excel classes with pivot table support.

It provides sub-classes for the classes of PHP Excel packages and new classes that can allow manipulating pivot tables in spreadsheets generated in Microsoft Excel format.

Currently, this package provides:

- A spreadsheet sub-class that can process and add pivot tables

- A Excel XLSX parser sub-class that can read and include in the spreadsheet pivot table information

- A Excel XLSX writer sub-class that export pivot table information

- A relationship sub-class that provides access to relationship functions that are private in the parent relationship class

- A workbook sub-class that provides access to relationship functions that are private in the parent workbook class

- Several classes to manipulate pivot tables, pivot table collections, groups of rows and columns in a pivot table, group collections, pivot table caching

Innovation Award
PHP Programming Innovation award winner
May 2023
Winner		 Excel is a popular spreadsheet tool that many people use in particular to keep track of financial information in tables of rows and cells.

Excel supports pivot tables. Pivot tables can be used to show aggregated information from rows and cells that provide more extensive information. Pivot tables can be used to show summaries of information to help people working with finances to make better decisions related with their businesses.

There is PHP package named PHP Excel that provides several classes to manipulate spreadsheets stored in Excel XLSX format.

This package can extend the PHP Excel package to provide more extensive support to pivot tables.

Manuel Lemos
Picture of Bill
Name: Bill <contact>
Classes: 2 packages by
Country: United Kingdom
Age: ???
All time rank: 4193186 in United Kingdom
Week rank: 83 Up5 in United Kingdom Up
Innovation award
Innovation award
Nominee: 2x

Winner: 1x

Example

<?php

use \lyquidity\xbrl_validate\PhpOffice\PhpSpreadsheet\Groups;
use \lyquidity\xbrl_validate\PhpOffice\PhpSpreadsheet\Group;
use \lyquidity\xbrl_validate\PhpOffice\PhpSpreadsheet\Spreadsheet;

require_once __DIR__ . '/data.php';
if ( file_exists( __DIR__ . '/../../../autoload.php' ) )
{
    require_once __DIR__ . '/../../../autoload.php';
}
// Required for the original implementation
elseif ( file_exists( __DIR__ . '/../vendor/autoload.php' ) )
{
    require_once __DIR__ . '/../vendor/autoload.php';
}
else
{
    throw new Exception("Unable to autoload classes");
}

// Only load the bootstrap files directly if they have not been loaded by Composer already
// Required for the original implementation
$files = get_included_files();
$bootstrap = realpath( __DIR__ . '/../phpspreadsheet/Spreadsheet.php' );
if ( ! in_array( $bootstrap, $files ) )
{
    require_once __DIR__ . '/../phpspreadsheet/Spreadsheet.php';
}

$outputFileName = __DIR__ . '/generated.xlsx';

$spreadsheet = new Spreadsheet();

$spreadsheet->getProperties()
    ->setCreator("XBRL Query Generator")
    ->setLastModifiedBy("XBRL Query Generator")
    ->setTitle("Microsoft 2018 QK")
    ->setSubject("Pivot table report")
    ->setDescription("This could be an explanation")
    ->setKeywords("xbrl microsoft 2018 10k")
    ->setCategory("Reports");

$data = load_data();

$networks = array(
    // All pivot tables are added to sheets to which the data is added starting at cell B2.

    // The first PT is added to a sheet called 'Worksheet'. It has two groups on the rows
    // (Account/Genre) that are filtered to three of the accounts. Because there is filtering
    // the sort type must be 'manual'. There are no groups added to the columns. Instead,
    // the columns are the values of three numeric columns.
    // Note that while the row groups object is created passing an explicit 'Group' instance
    // the value groups instance is created by passing an array of string names. This is a
    // simple ay to create groups if the default group values (no filtering and sort type
    // ascending) are acceptable.
    array( 'data' => $data,
            'args' => array(
                "Worksheet1",
                2 + count( $data ) + 1 + 3, 2,
                new Groups( array( new Group( 'Account', 'manual', array( 'Megan', 'Daniel', 'Hannah' ) ), 'Genre' ) ),
                new Groups(),
                new Groups( array( 'Total Size', 'Images', 'Average Ranking' ) )
            )
    ),

    // The second PT is added to a sheet called 'Worksheet2'. It has just one group on the rows
    // (Account) that is not filtered but the account names will be displayed in descending order.
    // This PT has two groups on the columns (Genre/Images). The values are from the 'Total Size'
    // column.
    array( 'data' => $data,
            'args' => array(
                "Worksheet2",
                2 + count( $data ) + 1 + 3, 2,
                new Groups( new Group( 'Account', 'descending' ) ),
                new Groups( array( 'Genre', 'Images' ) ),
                new Groups( array( 'Total Size' ) )
            )
    ),

    // The third PT is added to a sheet called 'Worksheet3'. It has two groups on the rows
    // (Account/Genre). There is one group on the columns (Images) and the values are from the
    // 'Total Size' column.
    array( 'data' => $data,
            'args' => array(
                "Worksheet3",
                2 + count( $data ) + 1 + 3, 2,
                new Groups( array( 'Account', 'Genre' ) ) ,
                new Groups( array( 'Images' ) ),
                new Groups( array( 'Total Size' ) )
            )
    )
);

foreach ( $networks as $index => $network )
{
    $range = $spreadsheet->addData( $data, $network['args'][0] );
    $spreadsheet->addNewPivotTable( $data, $range, ...$network['args'] );
}

$writer = PhpOffice\PhpSpreadsheet\IOFactory::createWriter( $spreadsheet, 'Xlsx' );
$writer->save($outputFileName);

$spreadsheet->disconnectWorksheets();
unset( $spreadsheet );


Details

PHP Pivot Tables for Excel - Lite

PHPOffice/PhpSpreadsheet is a great project to read and write Excel workbook but it does not support some Excel features such as Tables and Pivot Tables. This project extends PhpSpreadsheet by adding support for pivot tables but only in a limited way.

What is supported?

This project ensures that existing pivot tables are retained and allows pivot tables to be created to report on data in worksheets. The rows and columns can be defined based on columns in the worksheet and they can be filtered and sorted. Only Xlsx/Xlsm files are supported.

What is not supported?

The pivot table features not supported include: - External data sources - Styling - Hierarchies - Formulas - File types other than Xlsx

However, there is no reason why support for these features cannot be added and the project shows how additional features can be implemented.

Installing

Use composer with the command:

composer require lyquidity/php-pivottables-4-excel-lite:dev-master --prefer-dist

Getting started

The ./examples/example.php file includes illustrations of using the classes.

Assuming you have installed the library using composer then this PHP application will run the test:

<?php
require_once __DIR__ . '/vendor/autoload.php';
require __DIR__ . '/vendor/lyquidity/php-pivottables-4-excel-lite/examples/example.php';

The examples use the following simple data set:

|Account|Genre|Images|Average Ranking|Total Size| |:---|:---|---:|---:|---:| |Megan |Portraits |20 |4 |72000| |Hannah |Landscapes |31 |3.5|83000| |Vicky |Floral |25 |4.2|42000| |Ian |Portraits |40 |3.7|92000| |Michael |Landscapes |23 |3.8|72000| |Daniel |Landscapes |29 |4.4|85000|

Overridden Classes

To implement support for pivot tables it has been necessary to override 5 classes:

|Class|Reason| |:---|:---| |Spreadsheet|Extends the PhpSpreadsheet class to add functions that carry forward existing pivot tables and add new ones. Only addData and addNewPivotTable should be called from your code. The class also maintains a list of the cache definitions, record sets and pivot table definitions.| |XlsxReader|Registered by the replacement spreadsheet class to handle reading Xlsx documents so that existing pivot table resources can be recorded in a spreadsheet class instance. The whole XlsxReader class is replicated because it relies on private functions that cannot be called from descendant instances.| |XlsxWriter|Registered by the replacement spreadsheet class to handle writing Xlsx documents so that pivot table resources recorded in a spreadsheet class instance can be included in the generated package file. The whole XlsxWriter class is replicated because it relies on private functions that cannot be called from descendant instances.| |Rels|Add support for the relationships required for pivot table support. WriteRelationship and writeUnparsedRelationship are reimplemented because they are private in the parent Rels class and cannot be called from this claSS.| |Workbook|This class is replaced so the <PivotCaches> element can be written. The whole class is reimplemented because all the functions are private so it is not possible to replace just one.|

New classes

In addition eight new classes are added:

|Class|Reason| |:---|:---| |PivotCacheDefinition|Used to represent a cache definition file in the workbook document| |PivotCacheDefinitionCollection|Represents the list of existing and new cache definition files| |PivotCacheRecords|Represents one of the cache records files in the workbook document| |PivotCacheRecordsCollection|Represents the list of existing and new cache records files| |PivotTable|Used to represent a pivot table definition file in the workbook document| |PivotTableCollection|Represents the list of existing and new pivot table definition files| |Group|Represents a specfic column, row or value field from the data set and is used to define the use of the field in the pivot table. The class defines the field name, the sort order and any filter applied.| |Groups|Represents a collection of Group instances to build up the fields use for rows, columns and values|


  Files folder image Files  
File Role Description
Files folder imageexamples (3 files)
Files folder imagephpspreadsheet (3 files, 1 directory)
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files  /  examples  
File Role Description
  Accessible without login Plain text file data.php Example Example script
  Accessible without login Plain text file data.xml Data Auxiliary data
  Accessible without login Plain text file example.php Example Example script

  Files folder image Files  /  phpspreadsheet  
File Role Description
Files folder imageXlsx (8 files)
  Plain text file Spreadsheet.php Class Class source
  Plain text file XlsxReader.php Class Class source
  Plain text file XlsxWriter.php Class Class source

  Files folder image Files  /  phpspreadsheet  /  Xlsx  
File Role Description
  Plain text file PivotCacheDefinition.php Class Class source
  Plain text file PivotCacheDefinitionCollection.php Class Class source
  Plain text file PivotCacheRecords.php Class Class source
  Plain text file PivotCacheRecordsCollection.php Class Class source
  Plain text file PivotTable.php Class Class source
  Plain text file PivotTableCollection.php Class Class source
  Plain text file Rels.php Class Class source
  Plain text file Workbook.php Class Class source

Downloadphp-pivottables-4-ex-2023-05-29.zip 55KB
Downloadphp-pivottables-4-ex-2023-05-29.tar.gz 50KB
Install with ComposerInstall with Composer
 Version Control Unique User Downloads Download Rankings  
 100%
Total:76
This week:1
All time:10,148
This week:80Up

  Applications that use this package  
No pages of applications that use this class were specified.

Add link image If you know an application of this package, send a message to the author to add a link here.