Skip to content
PHP 8.1+ Open source Developer documentation
Tools Packagist
Docs v2.0.6
Documentation versions v2.0.6Current v2.0.5Maintenance v2.0.4Maintenance v2.0.3Maintenance v2.0.0Initial v2 Compare versions →
MNB PHPExcelDeveloper Guide
AI

Writing workbooks

Charts and Pivot Tables

Create native charts and pivot summaries from workbook ranges without manually editing OOXML.

Level
Advanced
Reading time
15 min
Package
mnb/mnb-phpexcel
Updated
addChart()addPivotTable()preservePivotTablesFrom()

Create a chart from worksheet ranges#

PHP
$workbook->addChart(
    type: 'column',
    title: 'Sales by Month',
    series: [[
        'name' => 'Sales',
        'categories' => 'Summary!$A$2:$A$13',
        'values' => 'Summary!$B$2:$B$13',
    ]],
    options: [
        'sheet' => 'Summary',
        'anchor' => 'D2',
        'width' => 720,
        'height' => 380,
    ]
);

Create a pivot table from source data#

PHP
$workbook->addPivotTable(
    name: 'SalesPivot',
    sourceSheet: 'Data',
    sourceRange: 'A1:F5000',
    destinationCell: 'A1',
    options: [
        'sheet' => 'Summary',
        'rows' => ['Region'],
        'columns' => ['Month'],
        'filters' => ['Status'],
        'values' => [[
            'field' => 'Amount',
            'function' => 'sum',
            'name' => 'Total Sales',
        ]],
        'layout' => 'tabular',
        'repeat_item_labels' => true,
    ]
);

Preserve complex template objects when needed#

Native pivots cover standard and advanced range-based summaries. Preserve a trusted template for OLAP/data-model pivots, slicers, custom layouts, and workbook objects that depend on Excel-specific authoring.

PHP
$workbook->preservePivotTablesFrom(
    'trusted-dashboard-template.xlsx',
    sourceSheet: 'Data',
    sourceRange: 'A1:F5000'
);

Design charts for readers, not APIs#

  • Use clear titles and units.
  • Avoid too many series or categories.
  • Keep source ranges stable and include headers.
  • Validate the generated XLSX and test it in Excel or LibreOffice.