MNB PHPExcelDeveloper Guide
v2.0.5
Documentation versions v2.0.5Current v2.0.4Maintenance v2.0.3Maintenance v2.0.0Initial v2 Compare versions →
Writing workbooks

Charts and Pivot Tables

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

Updated

LevelAdvancedReading time15 minPackagemnb/mnb-phpexcel
addChart()addPivotTable()preservePivotTablesFrom()

What you will learn

  • Choose charts for visualization and pivots for aggregation.
  • Create a standard native pivot from a source range.
  • Know when to preserve a trusted template instead.

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.