Core Initialization and Sheet Setup

To begin working with PHPExcel, you must instantiate the main class and define the active sheet properties.

// Initialize the PHPExcel object
$spreadsheet = new PHPExcel(); 

// Access and rename the active worksheet
$currentSheet = $spreadsheet->getActiveSheet();
$currentSheet->setTitle('Project_Summary');

Cell Manipulation and Styling

Configuring cell content involves setting values, formulas, and visual styles. For bulk formatting, applying arrays is often more efficient than chaining methods.

// Set basic cell values and formulas
$currentSheet->setCellValue('A1', 'Company Report');
$currentSheet->setCellValue('C10', '=SUM(A10:B10)');

// Font styling and merging
$currentSheet->getStyle('A1')->getFont()->setName('Arial')->setSize(14)->setBold(true);
$currentSheet->mergeCells('A1:G1');

// Alignment and wrapping
$alignmentConfig = [
    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
    'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
    'wrap'       => true
];
$currentSheet->getStyle('A1')->getAlignment()->applyFromArray($alignmentConfig);

// Defining borders via style arrays
$borderStyle = [
    'borders' => [
        'allborders' => [
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => ['argb' => 'FF000000'],
        ],
    ],
];
$currentSheet->getStyle('A5:G15')->applyFromArray($borderStyle);

Dimensions and Visibility

Adjusting column widths and row heights ensures the data remains legible.

// Set dimensions
$currentSheet->getDefaultRowDimension()->setRowHeight(18);
$currentSheet->getRowDimension('2')->setRowHeight(25);
$currentSheet->getColumnDimension('B')->setWidth(30);

// Auto-size a column based on content
$currentSheet->getColumnDimension('C')->setAutoSize(true);

// Hide specific columns
$currentSheet->getColumnDimension('D')->setVisible(false);

Advanced Data Types

PHPExcel handles specific formats like dates and hyperlinks through internal shared helpers.

// Numeric strings forced as numbers
$currentSheet->getCell('A2')->setValueExplicit('0012345', PHPExcel_Cell_DataType::TYPE_NUMERIC);

// Date handling using PHP timestamps
$unixTimestamp = strtotime('2023-10-25');
$currentSheet->setCellValue('B2', PHPExcel_Shared_Date::PHPToExcel($unixTimestamp));
$currentSheet->getStyle('B2')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);

// Hyperlinks
$currentSheet->setCellValue('E2', 'Visit Website');
$currentSheet->getCell('E2')->getHyperlink()->setUrl('https://example.com');

Reading Existing Excel Files

When importing data, use the IOFactory to identify the file type and load the content into an object.

$sourceFile = './data/records.xlsx';

if (!file_exists($sourceFile)) {
    die("File not found.");
}

// Load the spreadsheet
$fileReader = PHPExcel_IOFactory::createReaderForFile($sourceFile);
$excelObj = $fileReader->load($sourceFile);

$dataSheet = $excelObj->getSheet(0);
$maxRow = $dataSheet->getHighestRow();
$maxCol = $dataSheet->getHighestColumn();

// Iterate through rows and columns
for ($rowIndex = 1; $rowIndex <= $maxRow; $rowIndex++) {
    for ($colIndex = 'A'; $colIndex <= $maxCol; $colIndex++) {
        $cellValue = $dataSheet->getCell($colIndex . $rowIndex)->getValue();
        // Process $cellValue
    }
}

Pane Freezing and Sheet Protection

Freezing rows or columns helps keep headers visible during scrollling. Protecting the sheet prevents unauthorized modifications.

// Freeze the first row and first column
$currentSheet->freezePane('B2');

// Alternative: Freeze by coordinate
$currentSheet->freezePaneByColumnAndRow(0, 2); // Freezes first row

// Set password protection
$security = $currentSheet->getProtection();
$security->setPassword('SecurePass123');
$security->setSheet(true);
$security->setSort(true);

Managing Export Downloads

To trigger a download via POST without refreshing the current page, use a hidden form submission in JavaScript.

function executeExport(endpoint, payload) {
    const hiddenForm = document.createElement("form");
    hiddenForm.action = endpoint;
    hiddenForm.method = "POST";
    hiddenForm.style.display = "none";

    Object.keys(payload).forEach(key => {
        const input = document.createElement("textarea");
        input.name = key;
        input.value = payload[key];
        hiddenForm.appendChild(input);
    });

    document.body.appendChild(hiddenForm);
    hiddenForm.submit();
    document.body.removeChild(hiddenForm);
}

Common Troubleshooting

PHP 7+ Compatibility Errors

If you encounter a 'break' not in the 'loop' or 'switch' context error, navigate to PHPExcel/Calculation/Functions.php. Locate the break statement inside the affected conditional block (usually around line 580) and remove it, as PHP 7+ does not allow break outside of loops.

Corrupted File Downloads

If exported files are unreadable or prompt an "Invalid Format" error, it is often due to output buffer pollution (like BOM or PHP notices). Use ob_clean() before sending headers.

public function exportFile() {
    // ... logic to generate Excel ...
    
    ob_clean(); // Clear output buffer
    flush();
    
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="report.xlsx"');
    header('Cache-Control: max-age=0');

    $writer = PHPExcel_IOFactory::createWriter($spreadsheet, 'Excel2007');
    $writer->save('php://output');
    exit;
}

ZipArchive Missing

If you see Class 'ZipArchive' not found, ensure the PHP ZIP extension is installed. On Debian/Ubuntu systems, use: sudo apt-get install php7.x-zip (replace x with your version).

Calculation Exceptions

Errors stating Could not close zip file usual indicate permission issues on the temporary directory, the destination path, or that the file is currently locked by another application.