EasyXLS

How to create pivot table in PHP

<?php

/*===========================================================
| Tutorial 25
|
| This tutorial shows how to create an Excel file in PHP and
| to create a pivot table in a worksheet.
* ===========================================================*/
Click here to see the Excel file

include("DataType.inc");
include("PivotTable.inc");

header("Content-Type: text/html");

echo "Tutorial 25<br>";
echo "----------<br>";

// Create an instance of the class that exports Excel files
$workbook = new COM("EasyXLS.ExcelDocument");

// Create two worksheets
$workbook->easy_addWorksheet_2("First tab");
$workbook->easy_addWorksheet_2("Second tab");

// Get the table of data for the first worksheet
$xlsFirstTable =  $workbook->easy_getSheetAt(0)->easy_getExcelTable();

// Add data in cells for report header
$xlsFirstTable->easy_getCell(0,0)->setValue("Sale agent");
$xlsFirstTable->easy_getCell(0,0)->setDataType($DATATYPE_STRING);
$xlsFirstTable->easy_getCell(0,1)->setValue("Sale country");
$xlsFirstTable->easy_getCell(0,1)->setDataType($DATATYPE_STRING);
$xlsFirstTable->easy_getCell(0,2)->setValue("Month");
$xlsFirstTable->easy_getCell(0,2)->setDataType($DATATYPE_STRING);
$xlsFirstTable->easy_getCell(0,3)->setValue("Year");
$xlsFirstTable->easy_getCell(0,3)->setDataType($DATATYPE_STRING);
$xlsFirstTable->easy_getCell(0,4)->setValue("Sale amount");
$xlsFirstTable->easy_getCell(0,4)->setDataType($DATATYPE_STRING);

$xlsFirstTable->easy_getRowAt(0)->setBold(true);

// Add data in cells for report values - the source for pivot table
$xlsFirstTable->easy_getCell(1,0)->setValue("John Down");
$xlsFirstTable->easy_getCell(1,1)->setValue("USA");
$xlsFirstTable->easy_getCell(1,2)->setValue("June");
$xlsFirstTable->easy_getCell(1,3)->setValue("2010");
$xlsFirstTable->easy_getCell(1,4)->setValue("550");

$xlsFirstTable->easy_getCell(2,0)->setValue("Scott Valey");
$xlsFirstTable->easy_getCell(2,1)->setValue("United Kingdom");
$xlsFirstTable->easy_getCell(2,2)->setValue("June");
$xlsFirstTable->easy_getCell(2,3)->setValue("2010");
$xlsFirstTable->easy_getCell(2,4)->setValue("2300");

$xlsFirstTable->easy_getCell(3,0)->setValue("John Down");
$xlsFirstTable->easy_getCell(3,1)->setValue("USA");
$xlsFirstTable->easy_getCell(3,2)->setValue("July");
$xlsFirstTable->easy_getCell(3,3)->setValue("2010");
$xlsFirstTable->easy_getCell(3,4)->setValue("3100");

$xlsFirstTable->easy_getCell(4,0)->setValue("John Down");
$xlsFirstTable->easy_getCell(4,1)->setValue("USA");
$xlsFirstTable->easy_getCell(4,2)->setValue("June");
$xlsFirstTable->easy_getCell(4,3)->setValue("2011");
$xlsFirstTable->easy_getCell(4,4)->setValue("1050");

$xlsFirstTable->easy_getCell(5,0)->setValue("John Down");
$xlsFirstTable->easy_getCell(5,1)->setValue("USA");
$xlsFirstTable->easy_getCell(5,2)->setValue("July");
$xlsFirstTable->easy_getCell(5,3)->setValue("2011");
$xlsFirstTable->easy_getCell(5,4)->setValue("2400");

$xlsFirstTable->easy_getCell(6,0)->setValue("Steve Marlowe");
$xlsFirstTable->easy_getCell(6,1)->setValue("France");
$xlsFirstTable->easy_getCell(6,2)->setValue("June");
$xlsFirstTable->easy_getCell(6,3)->setValue("2011");
$xlsFirstTable->easy_getCell(6,4)->setValue("1200");

$xlsFirstTable->easy_getCell(7,0)->setValue("Scott Valey");
$xlsFirstTable->easy_getCell(7,1)->setValue("United Kingdom");
$xlsFirstTable->easy_getCell(7,2)->setValue("June");
$xlsFirstTable->easy_getCell(7,3)->setValue("2011");
$xlsFirstTable->easy_getCell(7,4)->setValue("700");

$xlsFirstTable->easy_getCell(8,0)->setValue("Scott Valey");
$xlsFirstTable->easy_getCell(8,1)->setValue("United Kingdom");
$xlsFirstTable->easy_getCell(8,2)->setValue("July");
$xlsFirstTable->easy_getCell(8,3)->setValue("2011");
$xlsFirstTable->easy_getCell(8,4)->setValue("360");

// Create pivot table
$xlsPivotTable = new COM("EasyXLS.PivotTables.ExcelPivotTable");
$xlsPivotTable->setName("Sales");
$xlsPivotTable->setSourceRange("First tab!\$A\$1:\$E\$9",$workbook);
$xlsPivotTable->setLocation_2("A3:G15");
$xlsPivotTable->addFieldToRowLabels("Sale agent");
$xlsPivotTable->addFieldToColumnLabels("Year");
$xlsPivotTable->addFieldToValues("Sale amount","Sale amount per year",$PIVOTTABLE_SUBTOTAL_SUM);
$xlsPivotTable->addFieldToReportFilter("Sale country");
$xlsPivotTable->setOutlineForm();
$xlsPivotTable->setStyle($PIVOTTABLE_PIVOT_STYLE_DARK_11);

// Add the pivot table to the second sheet
$xlsWorksheet = $workbook->easy_getSheet("Second tab");
$xlsWorksheet->easy_addPivotTable($xlsPivotTable);

// Export Excel file
echo "Writing file: C:\Samples\Tutorial25.xlsx<br>";
$workbook->easy_WriteXLSXFile("C:\Samples\Tutorial25.xlsx");

// Confirm export of Excel file
if ($workbook->easy_getError() == "")
    echo "File successfully created.";
else
    echo "Error encountered: " . $workbook->easy_getError();

// Dispose memory
$workbook->Dispose();
$workbook = null;
$xlsPivotTable = null;

?>

Overloaded methods
For methods with same name but different parameters, only the first method overload retains the original name. Subsequent overloads are uniquely renamed by appending to the method name '_2', '_3', etc (method, method_2, method_3), an integer that corresponds to the order of declaration that can be found in EasyXLS.h, a file that comes with EasyXLS installation.

Linux and PHP
This code sample works just the way it is for PHP on Windows, but PHP under Linux requires also PHP/Java Bridge and a similar code sample in Java.

EasyXLS Excel libraries:

.NET
Excel Library for PHP
full COM+ version to import, export or convert Excel files
Excel Writer for PHP
COM+ version to create and export Excel files
Download EasyXLS™ Excel Library for PHP

File formats:

MS Excel 97 - 2003
MS Excel 2007 - 2010
MS Excel 2013
MS Excel 2016
MS Excel 2019
XLSX XLSM XLSB XLS
XML HTML CSV TXT