PHPExcel庫讀寫Excel完整指南：生成下載、服務器保存、數據讀取三大場景

環境準備

引入 PHPExcel 類庫（需 Classes/PHPExcel.php 等核心文件）。

場景一：數組導出Excel並直接下載

function arrayToExcel($data) {
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->setActiveSheetIndex(0)
                 ->setTitle('firstsheet');
    $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')->setSize(10);

    $i = 2; // 從第2行開始寫數據
    foreach ($data as $line) {
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $line['From']);
        $objPHPExcel->getActiveSheet()->getCell('A'.$i)->setDataType('n');
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $line['To']);
        $i++;
    }

    // 直接輸出下載
    header("Content-Type: application/vnd.ms-excel");
    header('Content-Disposition: attachment; filename="export.xls"');
    $writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $writer->save('php://output');
}

場景二：讀取Excel文件轉數組

function excelToArray($file) {
    $reader = PHPExcel_IOFactory::createReader('Excel5');
    $reader->setReadDataOnly(true);
    $obj = $reader->load($file);
    $sheet = $obj->getActiveSheet();
    $highestRow = $sheet->getHighestRow();
    $highestCol = $sheet->getHighestColumn();
    $colIndex = PHPExcel_Cell::columnIndexFromString($highestCol);

    $excelData = array();
    for ($row = 2; $row <= $highestRow; $row++) {
        for ($col = 0; $col <= $colIndex; $col++) {
            $excelData[$row][] = $sheet->getCellByColumnAndRow($col, $row)->getValue();
        }
    }
    return $excelData;
}

場景三：精簡讀取示例

$reader = PHPExcel_IOFactory::createReader('Excel5');
$obj = $reader->load("31excel5.xls");
$sheet = $obj->getSheet(0);
for ($row = 1; $row <= $sheet->getHighestRow(); $row++) {
    for ($col = 'A'; $col <= $sheet->getHighestColumn(); $col++) {
        echo $col.$row . ": " . $sheet->getCell($col.$row)->getValue() . "<br/>";
    }
}