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();
- 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.
The active worksheet object
$activeSheet = $spreadsheet->getActiveSheet();
createSheet(?int $sheetIndex = null)
Create a new worksheet and add it to the workbook.
Index position where sheet should be inserted (0-based). Use null to add at the end.
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.
The worksheet object to add
Index position (0-based), or null to add at the end
Automatically rename if a sheet with the same name exists
$worksheet = new Worksheet($spreadsheet, 'My Sheet');
$spreadsheet->addSheet($worksheet, null, true);
getSheet(int $sheetIndex)
Get a worksheet by its index position.
Zero-based index of the worksheet
$firstSheet = $spreadsheet->getSheet(0);
$secondSheet = $spreadsheet->getSheet(1);
getSheetByName(string $worksheetName)
Get a worksheet by its name.
Name of the worksheet to retrieve
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.
try {
$sheet = $spreadsheet->getSheetByNameOrThrow('Sales Data');
} catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
echo "Sheet not found: " . $e->getMessage();
}
getAllSheets()
Get all worksheets in the workbook.
$sheets = $spreadsheet->getAllSheets();
foreach ($sheets as $sheet) {
echo $sheet->getTitle() . "\n";
}
getSheetCount()
Get the total number of worksheets.
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.
Zero-based index of the worksheet to remove
// Remove the second sheet
$spreadsheet->removeSheetByIndex(1);
setActiveSheetIndex(int $worksheetIndex)
Set the active sheet by index.
Zero-based index of the worksheet to activate
The newly activated worksheet
$activeSheet = $spreadsheet->setActiveSheetIndex(0);
Document Properties
getProperties()
Get document properties (metadata).
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.
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.
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.
Scope worksheet (null for global scope)
The named range, or null if not found
$range = $spreadsheet->getNamedRange('TaxRate');
if ($range !== null) {
$value = $range->getRange();
}
getNamedRanges()
Get all named ranges.
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.
Name of the range to remove
Scope worksheet (null for global scope)
$spreadsheet->removeNamedRange('TaxRate');
Styling
getDefaultStyle()
Get the default style for the workbook.
$defaultStyle = $spreadsheet->getDefaultStyle();
$defaultStyle->getFont()->setName('Arial')
->setSize(10);
addCellXf(Style $style)
Add a cell style format (XF) to the workbook.
use PhpOffice\PhpSpreadsheet\Style\Style;
$style = new Style();
$style->getFont()->setBold(true);
$spreadsheet->addCellXf($style);
Calculation
getCalculationEngine()
Get the calculation engine.
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 propertiesStyle - Cell formatting and stylesNamedRange - Named cell ranges
