Worksheet Class

Worksheet::SHEETSTATE_VISIBLE    // Sheet is visible
Worksheet::SHEETSTATE_HIDDEN     // Sheet is hidden but can be unhidden
Worksheet::SHEETSTATE_VERYHIDDEN // Sheet is hidden and cannot be unhidden via UI

Worksheet::BREAK_NONE   // No page break
Worksheet::BREAK_ROW    // Row page break
Worksheet::BREAK_COLUMN // Column page break

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

$spreadsheet = new Spreadsheet();
$worksheet = new Worksheet($spreadsheet, 'Sales Data');

// Different ways to get a cell
$cell = $worksheet->getCell('A1');
$cell = $worksheet->getCell([1, 1]); // [column, row]

// Set different types of values
$worksheet->setCellValue('A1', 'Product Name');
$worksheet->setCellValue('B1', 1234.56);
$worksheet->setCellValue('C1', new DateTime());
$worksheet->setCellValue('D1', true);

use PhpOffice\PhpSpreadsheet\Cell\DataType;

// Set as text even if it looks like a number
$worksheet->setCellValueExplicit('A1', '00123', DataType::TYPE_STRING);

// Set a formula
$worksheet->setCellValueExplicit('B1', '=SUM(A1:A10)', DataType::TYPE_FORMULA);

if ($worksheet->cellExists('A1')) {
    $value = $worksheet->getCell('A1')->getValue();
}

$title = $worksheet->getTitle();

$worksheet->setTitle('Q4 Sales');

$state = $worksheet->getSheetState();

use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

$worksheet->setSheetState(Worksheet::SHEETSTATE_HIDDEN);

// Get highest row in entire sheet
$maxRow = $worksheet->getHighestRow();

// Get highest row in column A
$maxRowInA = $worksheet->getHighestRow('A');

// Get highest column in entire sheet
$maxCol = $worksheet->getHighestColumn(); // e.g., 'D'

// Get highest column in row 1
$maxColInRow1 = $worksheet->getHighestColumn(1);

$lastDataRow = $worksheet->getHighestDataRow();

$lastDataCol = $worksheet->getHighestDataColumn();

$dimension = $worksheet->calculateWorksheetDimension();
echo "Worksheet range: $dimension"; // e.g., "A1:D10"

// Set row height
$worksheet->getRowDimension(1)->setRowHeight(30);

// Hide a row
$worksheet->getRowDimension(2)->setVisible(false);

// Set column width
$worksheet->getColumnDimension('A')->setWidth(20);

// Auto-size a column
$worksheet->getColumnDimension('B')->setAutoSize(true);

// Hide a column
$worksheet->getColumnDimension('C')->setVisible(false);

// Set width of first column (A)
$worksheet->getColumnDimensionByColumn(1)->setWidth(15);

// Style a single cell
$worksheet->getStyle('A1')
    ->getFont()->setBold(true);

// Style a range
$worksheet->getStyle('A1:D1')
    ->getFont()->setBold(true)
    ->getAlignment()->setHorizontal('center');

$style = $worksheet->getStyle('A1');
$style->getFont()->setBold(true);

// Apply same style to other cells
$worksheet->duplicateStyle($style, 'B1:Z1');

use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;

$pageSetup = $worksheet->getPageSetup();
$pageSetup->setOrientation(PageSetup::ORIENTATION_LANDSCAPE)
    ->setPaperSize(PageSetup::PAPERSIZE_A4)
    ->setFitToPage(true)
    ->setFitToWidth(1)
    ->setFitToHeight(0);

$margins = $worksheet->getPageMargins();
$margins->setTop(1)
    ->setRight(0.75)
    ->setBottom(1)
    ->setLeft(0.75);

$headerFooter = $worksheet->getHeaderFooter();
$headerFooter->setOddHeader('&C&HPage &P of &N');
$headerFooter->setOddFooter('&L&B' . $worksheet->getTitle() . '&RGenerated: &D');

$protection = $worksheet->getProtection();
$protection->setSheet(true)
    ->setPassword('secret')
    ->setFormatCells(false)
    ->setInsertRows(false);

use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;

// Create and add chart
$chart = new Chart(/* ... */);
$worksheet->addChart($chart);

$chartCount = $worksheet->getChartCount();

$chart = $worksheet->getChartByName('Sales Chart');
if ($chart !== false) {
    // Work with chart
}

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();

// Set title
$worksheet->setTitle('Sales Report');

// Add headers
$worksheet->setCellValue('A1', 'Product');
$worksheet->setCellValue('B1', 'Quantity');
$worksheet->setCellValue('C1', 'Price');
$worksheet->setCellValue('D1', 'Total');

// Style headers
$worksheet->getStyle('A1:D1')
    ->getFont()->setBold(true)
    ->getAlignment()
    ->setHorizontal(Alignment::HORIZONTAL_CENTER);

// Add data
$worksheet->setCellValue('A2', 'Product A');
$worksheet->setCellValue('B2', 10);
$worksheet->setCellValue('C2', 25.50);
$worksheet->setCellValue('D2', '=B2*C2');

// Set column widths
$worksheet->getColumnDimension('A')->setWidth(20);
$worksheet->getColumnDimension('B')->setAutoSize(true);
$worksheet->getColumnDimension('C')->setAutoSize(true);
$worksheet->getColumnDimension('D')->setAutoSize(true);

// Set row height
$worksheet->getRowDimension(1)->setRowHeight(30);

// Page setup
$worksheet->getPageSetup()
    ->setOrientation(PageSetup::ORIENTATION_LANDSCAPE)
    ->setPaperSize(PageSetup::PAPERSIZE_A4);

// Get worksheet dimensions
$highestRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
echo "Data range: A1:{$highestColumn}{$highestRow}";