Spreadsheet Class

The Spreadsheet class is the core container for workbook data in PhpSpreadsheet. It manages worksheets, document properties, styles, and named ranges.

Class Overview

Namespace: PhpOffice\PhpSpreadsheet Source: src/PhpSpreadsheet/Spreadsheet.php The Spreadsheet class represents an entire workbook and provides methods for managing worksheets, document metadata, styles, and calculations.

Constructor

`__construct()`

Creates a new PhpSpreadsheet with one worksheet.

use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = new Spreadsheet();

The constructor automatically:

Creates one default worksheet
Initializes document properties
Sets up document security
Creates the calculation engine
Initializes default styles

Worksheet Management

`getActiveSheet()`

Get the currently active worksheet.

Return

Worksheet

The active worksheet object

$activeSheet = $spreadsheet->getActiveSheet();

`createSheet(?int $sheetIndex = null)`

Create a new worksheet and add it to the workbook.

sheetIndex

int|null

Index position where sheet should be inserted (0-based). Use null to add at the end.

Return

Worksheet

The newly created worksheet

// Add sheet at the end
$newSheet = $spreadsheet->createSheet();

// Insert sheet at specific position
$secondSheet = $spreadsheet->createSheet(1);

`addSheet(Worksheet $worksheet, ?int $sheetIndex = null, bool $retitleIfNeeded = false)`

Add an existing worksheet to the workbook.

worksheet

Worksheet

The worksheet object to add

sheetIndex

int|null

Index position (0-based), or null to add at the end

retitleIfNeeded

bool

default:"false"

Automatically rename if a sheet with the same name exists

Return

Worksheet

The added worksheet

$worksheet = new Worksheet($spreadsheet, 'My Sheet');
$spreadsheet->addSheet($worksheet, null, true);

`getSheet(int $sheetIndex)`

Get a worksheet by its index position.

sheetIndex

int

Zero-based index of the worksheet

Return

Worksheet

The requested worksheet

$firstSheet = $spreadsheet->getSheet(0);
$secondSheet = $spreadsheet->getSheet(1);

`getSheetByName(string $worksheetName)`

Get a worksheet by its name.

worksheetName

string

Name of the worksheet to retrieve

Return

Worksheet|null

The worksheet, or null if not found

$sheet = $spreadsheet->getSheetByName('Sales Data');
if ($sheet !== null) {
    // Work with the sheet
}

`getSheetByNameOrThrow(string $worksheetName)`

Get a worksheet by name, throwing an exception if not found.

worksheetName

string

Name of the worksheet

Return

Worksheet

The worksheet

try {
    $sheet = $spreadsheet->getSheetByNameOrThrow('Sales Data');
} catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
    echo "Sheet not found: " . $e->getMessage();
}

`getAllSheets()`

Get all worksheets in the workbook.

Return

Worksheet[]

Array of all worksheets

$sheets = $spreadsheet->getAllSheets();
foreach ($sheets as $sheet) {
    echo $sheet->getTitle() . "\n";
}

`getSheetCount()`

Get the total number of worksheets.

Return

int

Number of worksheets in the workbook

$count = $spreadsheet->getSheetCount();
echo "This workbook has {$count} sheets";

`removeSheetByIndex(int $sheetIndex)`

Remove a worksheet by its index position.

sheetIndex

int

Zero-based index of the worksheet to remove

// Remove the second sheet
$spreadsheet->removeSheetByIndex(1);

`setActiveSheetIndex(int $worksheetIndex)`

Set the active sheet by index.

worksheetIndex

int

Zero-based index of the worksheet to activate

Return

Worksheet

The newly activated worksheet

$activeSheet = $spreadsheet->setActiveSheetIndex(0);

Document Properties

`getProperties()`

Get document properties (metadata).

Return

Properties

Document properties object

$properties = $spreadsheet->getProperties();
$properties->setCreator('John Doe')
    ->setTitle('Sales Report')
    ->setSubject('Q4 2024 Sales')
    ->setDescription('Quarterly sales analysis')
    ->setKeywords('sales report quarterly')
    ->setCategory('Reports');

`setProperties(Properties $documentProperties)`

Set document properties.

documentProperties

Properties

The properties object to set

use PhpOffice\PhpSpreadsheet\Document\Properties;

$properties = new Properties();
$properties->setCreator('Jane Smith');
$spreadsheet->setProperties($properties);

Named Ranges

`addNamedRange(NamedRange $namedRange)`

Add a named range to the workbook.

namedRange

NamedRange

The named range object to add

use PhpOffice\PhpSpreadsheet\NamedRange;

$namedRange = new NamedRange(
    'TaxRate',
    $spreadsheet->getActiveSheet(),
    'A1'
);
$spreadsheet->addNamedRange($namedRange);

`getNamedRange(string $namedRange, ?Worksheet $worksheet = null)`

Get a named range.

namedRange

string

Name of the range

worksheet

Worksheet|null

Scope worksheet (null for global scope)

Return

NamedRange|null

The named range, or null if not found

$range = $spreadsheet->getNamedRange('TaxRate');
if ($range !== null) {
    $value = $range->getRange();
}

`getNamedRanges()`

Get all named ranges.

Return

DefinedName[]

Array of all named ranges

$ranges = $spreadsheet->getNamedRanges();
foreach ($ranges as $range) {
    echo $range->getName() . ": " . $range->getValue() . "\n";
}

`removeNamedRange(string $namedRange, ?Worksheet $worksheet = null)`

Remove a named range.

namedRange

string

Name of the range to remove

worksheet

Worksheet|null

Scope worksheet (null for global scope)

$spreadsheet->removeNamedRange('TaxRate');

Styling

`getDefaultStyle()`

Get the default style for the workbook.

Return

Style

The default style object

$defaultStyle = $spreadsheet->getDefaultStyle();
$defaultStyle->getFont()->setName('Arial')
    ->setSize(10);

`addCellXf(Style $style)`

Add a cell style format (XF) to the workbook.

style

Style

The style to add

use PhpOffice\PhpSpreadsheet\Style\Style;

$style = new Style();
$style->getFont()->setBold(true);
$spreadsheet->addCellXf($style);

Calculation

`getCalculationEngine()`

Get the calculation engine.

Return

Calculation

The calculation engine instance

$calculation = $spreadsheet->getCalculationEngine();
$calculation->setCalculationCacheEnabled(true);

Complete Example

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\NamedRange;

// Create a new spreadsheet
$spreadsheet = new Spreadsheet();

// Set document properties
$spreadsheet->getProperties()
    ->setCreator('John Doe')
    ->setTitle('Sales Report')
    ->setSubject('Q4 2024')
    ->setDescription('Quarterly sales analysis');

// Get the active sheet and set its title
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Sales');

// Add data
$sheet->setCellValue('A1', 'Product');
$sheet->setCellValue('B1', 'Revenue');
$sheet->setCellValue('A2', 'Product A');
$sheet->setCellValue('B2', 1000);

// Create a second sheet
$summarySheet = $spreadsheet->createSheet();
$summarySheet->setTitle('Summary');

// Add a named range
$namedRange = new NamedRange(
    'TotalRevenue',
    $sheet,
    'B2:B10'
);
$spreadsheet->addNamedRange($namedRange);

// Set active sheet back to first sheet
$spreadsheet->setActiveSheetIndex(0);

// Get sheet count
echo "Total sheets: " . $spreadsheet->getSheetCount();

Worksheet - Individual worksheet within a spreadsheet
Cell - Individual cell within a worksheet
IOFactory - Loading and saving spreadsheets
Properties - Document metadata and properties
Style - Cell formatting and styles
NamedRange - Named cell ranges

Core Classes

Readers

Writers

Calculation

Styling

Charts & Graphics

Class Overview

Constructor

`__construct()`

Worksheet Management

`getActiveSheet()`

`createSheet(?int $sheetIndex = null)`

`addSheet(Worksheet $worksheet, ?int $sheetIndex = null, bool $retitleIfNeeded = false)`

`getSheet(int $sheetIndex)`

`getSheetByName(string $worksheetName)`

`getSheetByNameOrThrow(string $worksheetName)`

`getAllSheets()`

`getSheetCount()`

`removeSheetByIndex(int $sheetIndex)`

`setActiveSheetIndex(int $worksheetIndex)`

Document Properties

`getProperties()`

`setProperties(Properties $documentProperties)`

Named Ranges

`addNamedRange(NamedRange $namedRange)`

`getNamedRange(string $namedRange, ?Worksheet $worksheet = null)`

`getNamedRanges()`

`removeNamedRange(string $namedRange, ?Worksheet $worksheet = null)`

Styling

`getDefaultStyle()`

`addCellXf(Style $style)`

Calculation

`getCalculationEngine()`

Complete Example

Build docs developers (and LLMs) love

Core Classes

Readers

Writers

Calculation

Styling

Charts & Graphics

Documentation Index

​Class Overview

​Constructor

​__construct()

​Worksheet Management

​getActiveSheet()

​createSheet(?int $sheetIndex = null)

​addSheet(Worksheet $worksheet, ?int $sheetIndex = null, bool $retitleIfNeeded = false)

​getSheet(int $sheetIndex)

​getSheetByName(string $worksheetName)

​getSheetByNameOrThrow(string $worksheetName)

​getAllSheets()

​getSheetCount()

​removeSheetByIndex(int $sheetIndex)

​setActiveSheetIndex(int $worksheetIndex)

​Document Properties

​getProperties()

​setProperties(Properties $documentProperties)

​Named Ranges

​addNamedRange(NamedRange $namedRange)

​getNamedRange(string $namedRange, ?Worksheet $worksheet = null)

​getNamedRanges()

​removeNamedRange(string $namedRange, ?Worksheet $worksheet = null)

​Styling

​getDefaultStyle()

​addCellXf(Style $style)

​Calculation

​getCalculationEngine()

​Complete Example

​Related Classes

Build docs developers (and LLMs) love

Class Overview

Constructor

`__construct()`

Worksheet Management

`getActiveSheet()`

`createSheet(?int $sheetIndex = null)`

`addSheet(Worksheet $worksheet, ?int $sheetIndex = null, bool $retitleIfNeeded = false)`

`getSheet(int $sheetIndex)`

`getSheetByName(string $worksheetName)`

`getSheetByNameOrThrow(string $worksheetName)`

`getAllSheets()`

`getSheetCount()`

`removeSheetByIndex(int $sheetIndex)`

`setActiveSheetIndex(int $worksheetIndex)`

Document Properties

`getProperties()`

`setProperties(Properties $documentProperties)`

Named Ranges

`addNamedRange(NamedRange $namedRange)`

`getNamedRange(string $namedRange, ?Worksheet $worksheet = null)`

`getNamedRanges()`

`removeNamedRange(string $namedRange, ?Worksheet $worksheet = null)`

Styling

`getDefaultStyle()`

`addCellXf(Style $style)`

Calculation

`getCalculationEngine()`

Complete Example

Related Classes