PHP项目PHPOffice Excel处理

wen PHP项目 2026-07-24 140

本文目录导读：

安装
基础配置
创建Excel文件
读取Excel文件
高级样式设置
处理大文件
实用功能示例
性能优化建议
常见错误处理

我来详细介绍PHPOffice/PhpSpreadsheet（PHPExcel的升级版）在PHP项目中的Excel处理。

安装

composer require phpoffice/phpspreadsheet

基础配置

自动加载

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;

创建Excel文件

基本示例

// 创建Spreadsheet对象
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 设置单元格值
$sheet->setCellValue('A1', 'Hello');
$sheet->setCellValue('B1', 'World');
$sheet->setCellValue('C1', 'Welcome');
// 保存文件
$writer = new Xlsx($spreadsheet);
$writer->save('hello_world.xlsx');

完整示例：导出数据

function exportExcel($data, $headers, $filename = 'export.xlsx') {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    // 设置标题行
    $col = 'A';
    foreach ($headers as $header) {
        $sheet->setCellValue($col . '1', $header);
        $col++;
    }
    // 填充数据
    $row = 2;
    foreach ($data as $item) {
        $col = 'A';
        foreach ($item as $value) {
            $sheet->setCellValue($col . $row, $value);
            $col++;
        }
        $row++;
    }
    // 设置样式
    $styleArray = [
        'font' => [
            'bold' => true,
            'size' => 12,
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
    ];
    $sheet->getStyle('A1:' . $col . '1')->applyFromArray($styleArray);
    // 设置列宽
    foreach (range('A', $col) as $columnID) {
        $sheet->getColumnDimension($columnID)->setAutoSize(true);
    }
    // 输出文件
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
}

读取Excel文件

基本读取

function readExcel($filePath) {
    $spreadsheet = IOFactory::load($filePath);
    $sheet = $spreadsheet->getActiveSheet();
    $data = [];
    foreach ($sheet->getRowIterator() as $row) {
        $rowData = [];
        foreach ($row->getCellIterator() as $cell) {
            $rowData[] = $cell->getValue();
        }
        $data[] = $rowData;
    }
    return $data;
}

高级读取：处理空行和特殊格式

function readExcelAdvanced($filePath) {
    $spreadsheet = IOFactory::load($filePath);
    $worksheet = $spreadsheet->getActiveSheet();
    $data = [];
    $highestRow = $worksheet->getHighestRow();
    $highestColumn = $worksheet->getHighestColumn();
    $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
    for ($row = 1; $row <= $highestRow; ++$row) {
        $rowData = [];
        for ($col = 1; $col <= $highestColumnIndex; ++$col) {
            $cellValue = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
            // 处理日期格式
            if (\PhpOffice\PhpSpreadsheet\Shared\Date::isDateTime($cellValue)) {
                $cellValue = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($cellValue);
                $cellValue = $cellValue->format('Y-m-d H:i:s');
            }
            $rowData[] = $cellValue;
        }
        // 跳过空行
        if (!empty(array_filter($rowData))) {
            $data[] = $rowData;
        }
    }
    return $data;
}

高级样式设置

单元格样式

function setCellStyle($sheet) {
    // 字体样式
    $sheet->getStyle('A1:D1')->getFont()
        ->setBold(true)
        ->setSize(14)
        ->setColor(new \PhpOffice\PhpSpreadsheet\Style\Color('FF0000'));
    // 背景颜色
    $sheet->getStyle('A1:D1')->getFill()
        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
        ->getStartColor()->setARGB('FFFF00');
    // 边框
    $sheet->getStyle('A1:D10')->getBorders()
        ->getAllBorders()
        ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
    // 对齐
    $sheet->getStyle('A1:D10')->getAlignment()
        ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
        ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
    // 数字格式
    $sheet->getStyle('B2:B10')->getNumberFormat()
        ->setFormatCode('#,##0.00');
    // 合并单元格
    $sheet->mergeCells('A1:D1');
}

处理大文件

使用Chunk读取

function readLargeExcel($inputFileName) {
    $inputFileType = IOFactory::identify($inputFileName);
    $reader = IOFactory::createReader($inputFileType);
    // 设置只读取数据
    $reader->setReadDataOnly(true);
    // 分块读取
    $chunkSize = 1000;
    $chunkFilter = new ChunkReadFilter();
    $reader->setReadFilter($chunkFilter);
    $data = [];
    for ($startRow = 1; $startRow <= 10000; $startRow += $chunkSize) {
        $chunkFilter->setRows($startRow, $chunkSize);
        $spreadsheet = $reader->load($inputFileName);
        // 处理数据
        $worksheet = $spreadsheet->getActiveSheet();
        foreach ($worksheet->getRowIterator() as $row) {
            $rowData = [];
            foreach ($row->getCellIterator() as $cell) {
                $rowData[] = $cell->getValue();
            }
            $data[] = $rowData;
        }
    }
    return $data;
}

实用功能示例

导出包含图片的Excel

function exportWithImage($data, $imagePath) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    // 添加数据
    $sheet->setCellValue('A1', '商品名称');
    $sheet->setCellValue('B1', '价格');
    $sheet->setCellValue('C1', '图片');
    $row = 2;
    foreach ($data as $item) {
        $sheet->setCellValue('A' . $row, $item['name']);
        $sheet->setCellValue('B' . $row, $item['price']);
        // 添加图片
        $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
        $drawing->setName('Image');
        $drawing->setDescription('Product Image');
        $drawing->setPath($imagePath);
        $drawing->setHeight(100);
        $drawing->setCoordinates('C' . $row);
        $drawing->setWorksheet($sheet);
        $row++;
    }
    return $spreadsheet;
}

多工作表操作

function createMultiSheet($dataSets) {
    $spreadsheet = new Spreadsheet();
    foreach ($dataSets as $index => $dataset) {
        if ($index == 0) {
            $sheet = $spreadsheet->getActiveSheet();
        } else {
            $sheet = $spreadsheet->createSheet();
        }
        $sheet->setTitle($dataset['title']);
        // 设置数据
        $row = 1;
        foreach ($dataset['headers'] as $col => $header) {
            $colLetter = chr(65 + $col);
            $sheet->setCellValue($colLetter . $row, $header);
        }
        $row = 2;
        foreach ($dataset['data'] as $item) {
            $col = 0;
            foreach ($item as $value) {
                $colLetter = chr(65 + $col);
                $sheet->setCellValue($colLetter . $row, $value);
                $col++;
            }
            $row++;
        }
    }
    return $spreadsheet;
}

性能优化建议

使用缓存：大数据量时启用缓存

$settings = new Settings();
$cache = new CacheInterface();
$settings->setCache($cache);

使用内存优化：大文件读取时使用

$reader->setReadDataOnly(true);
$reader->setIgnoreRowsWithNoCells(true);

批量写入：
```
$sheet->fromArray($data, null, 'A2');
```

常见错误处理

try {
    $spreadsheet = IOFactory::load($filePath);
    // 处理逻辑
} catch (\PhpOffice\PhpSpreadsheet\Reader\Exception $e) {
    // 读取错误
    echo '文件格式不支持: ' . $e->getMessage();
} catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
    // Spreadsheet操作错误
    echo '处理错误: ' . $e->getMessage();
} catch (Exception $e) {
    // 其他错误
    echo '未知错误: ' . $e->getMessage();
}

这些是PHPOffice/PhpSpreadsheet的主要用法，根据你的具体需求，可以选择相应的功能来实现Excel的导入导出处理。

本文地址： https://www.bmjup.com/post/28989.html