PHPExcel is a powerful library that provides comprehensive functionality for handling Excel files in PHP. This article covers reading Excel files and exporting data with template support.

Prerequisites

1. Download PHPExcel SDK from GitHub: https://github.com/PHPOffice/PHPExcel
2. Extract the SDK and integrate the class files into your project structure

Reading Excel Files

Method 1: Using Column-Based Reading

function readExcelFile($filePath) {
    require_once dirname(__FILE__) . '/Lib/Classes/PHPExcel/IOFactory.php';
    
    $excelReader = PHPExcel_IOFactory::load($filePath);
    
    $worksheet = $excelReader->getSheet(0);
    $totalRows = $worksheet->getHighestRow();
    $totalColumns = $worksheet->getHighestColumn();
    
    $columnLetters = array('A','B','C','D','E','F','G','H','I','J','K','L','M', 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
    
    $dataSet = array();
    for ($row = 2; $row <= $totalRows; $row++) {
        $rowData = array();
        for ($col = 0; $columnLetters[$col] != 'F'; $col++) {
            $cellValue = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
            $rowData[] = $cellValue;
        }
        $dataSet[] = $rowData;
    }
    
    return $dataSet;
}

Method 2: Using Iterator Pattern

function parseExcelToArray($filePath) {
    require_once dirname(__FILE__) . '/Lib/Classes/PHPExcel/IOFactory.php';
    
    $excelReader = PHPExcel_IOFactory::load($filePath);
    
    $worksheets = $excelReader->getWorksheetIterator();
    $resultData = array();
    
    foreach ($worksheets as $sheet) {
        $rows = $sheet->getRowIterator();
        
        foreach ($rows as $rowIndex => $rowItem) {
            $rowNumber = $rowItem->getRowIndex();
            
            if ($rowNumber < 2) {
                continue;
            }
            
            $cells = $rowItem->getCellIterator();
            $rowData = array();
            
            foreach ($cells as $cell) {
                $rowData[] = $cell->getValue();
            }
            
            $resultData[] = $rowData;
        }
    }
    
    return $resultData;
}

Both methods convert Excel data into PHP arrays to further processing.

Exporting Excel Files

Basic Export Function

function generateSpreadsheet($data, $fileName, $fieldMapping, $startingRow = 1, $isExcel2007 = false) {
    require_once APP_ROOT . '/Api/excel/PHPExcel.php';
    require_once APP_ROOT . '/Api/excel/PHPExcel/Writer/Excel2007.php';
    
    if (empty($fileName)) {
        $fileName = time();
    }
    
    if (!is_array($fieldMapping)) {
        return false;
    }
    
    $columnLabels = array('A','B','C','D','E','F','G','H','I','J','K','L','M', 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
    $spreadsheet = new PHPExcel();
    
    if ($isExcel2007) {
        $writer = new PHPExcel_Writer_Excel2007($spreadsheet);
        $fileName .= '.xlsx';
    } else {
        $writer = new PHPExcel_Writer_Excel5($spreadsheet);
        $fileName .= '.xls';
    }
    
    $activeSheet = $spreadsheet->getActiveSheet();
    
    foreach ($data as $row) {
        foreach ($fieldMapping as $colIndex => $fieldName) {
            $activeSheet->setCellValue($columnLabels[$colIndex] . $startingRow, $row[$fieldName]);
        }
        $startingRow++;
    }
    
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");
    header('Content-Disposition:attachment;filename=' . $fileName);
    header("Content-Transfer-Encoding:binary");
    $writer->save('php://output');
}

Template-Based Exportt

function exportWithTemplate($data, $outputName, $fieldMapping = array()) {
    require_once dirname(__FILE__) . '/Lib/Classes/PHPExcel/IOFactory.php';
    require_once dirname(__FILE__) . '/Lib/Classes/PHPExcel.php';
    require_once dirname(__FILE__) . '/Lib/Classes/PHPExcel/Writer/Excel2007.php';
    
    $columnLabels = array('A','B','C','D','E','F','G','H','I','J','K','L','M', 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
    
    $templatePath = dirname(__FILE__) . '/template.xls';
    $spreadsheet = PHPExcel_IOFactory::load($templatePath);
    
    $writer = new PHPExcel_Writer_Excel5($spreadsheet);
    $activeSheet = $spreadsheet->getActiveSheet();
    
    $activeSheet->setCellValue('A2', "Document Title: Sample Report");
    $activeSheet->setCellValue('C2', "Generated: " . date('Y-m-d H:i:s'));
    
    $rowPosition = 4;
    foreach ($data as $record) {
        foreach ($fieldMapping as $colIndex => $fieldName) {
            $activeSheet->setCellValue($columnLabels[$colIndex] . $rowPosition, $record[$fieldName]);
        }
        $rowPosition++;
    }
    
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");
    header('Content-Disposition:attachment;filename="' . $outputName . '.xls"');
    header("Content-Transfer-Encoding:binary");
    $writer->save('php://output');
}

To use template-based export, create an Excel template file with predefined formatting, headers, and styling. The export function then populates data into the designated cells while preserving the template's appearance.