PHPSpreadsheet 一些常用方法汇总

显示行号 | 选择喜欢的代码风格

PhpSpreadsheet 为早期 PHPExcel 的重构，是 PHPOffice Excel 部分，用原生纯 PHP 写的库，提供了各种类，方便读取和写入各种电子表格文件格式，如生成原生的 Excel（可以 100% 完成非常复杂的原生功能，不限于公式、条件、统计图、各 Sheet 之间的计算等）和 LibreOffice Calc 等，汇总常用方法用于 MARK。

PHPSpreadSheet 安装

{
    "require": {
        "phpoffice/phpspreadsheet": "^1.28"
    },
    "config": {
        "platform": {
            "php": "8.0"
        }
    }
}

之后 composer 运行：

composer install

PHPSpreadSheet 设置千分位符及其他 Format

<?php
// 设置千分位符及小于 0 为红色
$sheet->getStyle('A1')->getNumberFormat()->setFormatCode("[Red][<0](#,##0);#,##0");
$sheet->getStyle('A1')->getNumberFormat()->setFormatCode("#,##0.00"); // 千分位符

// 设置百分比
$sheet->getStyle('A1')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_PERCENTAGE);
$sheet->getStyle('A1')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_PERCENTAGE_00); // 百分比为两位

PHPSpreadSheet 设置 RowHeight

<?php
// 设置第一行的行高为 30
$sheet->getRowDimension(1)->setRowHeight(30);

PHPSpreadSheet 设置字体名称

<?php
// 获取 A1 单元格的样式
$style = $sheet->getStyle('A1');

// 获取字体设置
$font = $style->getFont();

// 设置字体为等线字体
$font->setName('等线');

PHPSpreadSheet 设置 Excel 边框 Border 样式

// Borders
$borders = $sheet->getStyle('A1:E1')->getBorders();
$borders->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

PHPSpreadSheet 设置字体颜色

<?php
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setRGB('B8002E');

PHPSpreadSheet 设置 freezPane 及定格某一个 Cell

<?php
// 冻结窗格，锁定前两行
$sheet->freezePane('A3');

// 设置活动单元格为 A1
$sheet->setSelectedCell('A1');

PHPSpreadSheet 合并单元格

<?php
require 'vendor/autoload.php'; // 请确保已经包含了 PHPSpreadsheet 的自动加载文件

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// 创建一个新的 Spreadsheet 对象
$spreadsheet = new Spreadsheet();

// 获取当前活动的工作表
$sheet = $spreadsheet->getActiveSheet();

// 合并 A1 到 M1
$sheet->mergeCells('A1:M1');
// 设置合并后的单元格内容
$sheet->setCellValue('A1', '合并的内容');

// 合并 N1 到 T1
$sheet->mergeCells('N1:T1');
// 设置合并后的单元格内容
$sheet->setCellValue('N1', '合并的内容');

// 设置其他内容...

// 保存 Excel 文件
$writer = new Xlsx($spreadsheet);
$writer->save('example.xlsx');

PHPSpreadSheet 设置垂直对齐为居中

<?php
...

$sheet->setCellValue('A1', '设置内容');

// 获取 A1 单元格样式
$style = $sheet->getStyle('A1');

// 获取对齐设置
$alignment = $style->getAlignment();

// 设置垂直对齐为居中
$alignment->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

PHPSpreadSheet 循环列，设置上下单元格合并

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

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

// 假设有 20 列，你可以根据实际情况调整列数
$columnCount = 20;

// 设置每一列自动宽度并上下合并
for ($col = 'A'; $col <= 'T'; $col++) {
    // 设置自动宽度
    $sheet->getColumnDimension($col)->setAutoSize(true);

    // 合并当前列的上下两行
    $sheet->mergeCells($col . '1:' . $col . '2');
    
    // 设置合并后的单元格内容
    $sheet->setCellValue($col . '1', '合并的内容');
    
    // 获取当前列的样式
    $style = $sheet->getStyle($col . '1');
    
    // 获取对齐设置
    $alignment = $style->getAlignment();
    
    // 设置垂直对齐为居中
    $alignment->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
}

...

PHPSpreadSheet 设置字体粗体

<?php
// 在 A1 单元格中设置文本
$sheet->setCellValue('A1', '粗体文本');

// 获取 A1 单元格样式
$style = $sheet->getStyle('A1');

// 获取字体设置
$font = $style->getFont();

// 设置字体粗体
$font->setBold(true);

PHPSpreadSheet 设置水平、垂直居中

<?php
// 在 A1 单元格中设置文本
$sheet->setCellValue('A1', '粗体文本');

// 获取 A1 单元格样式
$style = $sheet->getStyle('A1');

// 获取对齐设置
$alignment = $style->getAlignment();

// 设置水平对齐为居中
$alignment->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
// 设置垂直对齐为居中
$alignment->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

PHPSpreadSheet 设置 Zoom

<?php
// 设置缩放比例为 80%
$sheet->getSheetView()->setZoomScale(80);

PHPSpreadSheet 设置条件格式及 comment

<?php

$min = 1;
$max = 5;
$end = 'Z';

$cond    = new Conditional();
$col_max = Coordinate::columnIndexFromString($end);

switch ($action) {
    case 'NOT_BETWEEN':
        $cond->setConditionType(Conditional::CONDITION_CELLIS)
            ->setOperatorType(Conditional::OPERATOR_NOTBETWEEN)
            ->addCondition($min)->addCondition($max)
            ->getStyle()->getFill()->setFillType(Fill::FILL_SOLID)->getEndColor()->setARGB('eb402b');

        $cond->getStyle()->getFont()->setColor(new \PhpOffice\PhpSpreadsheet\Style\Color(Color::COLOR_BLACK));

        for ($i = 7; $i <= $col_max; $i++) {
            $sheet->getComment(Coordinate::stringFromColumnIndex($i) . $i_start)->getText()->createTextRun("不介于 " . $min . " 和 " . $max . " 之间");
        }

        break;
    case 'GREATER_THAN':
        $cond->setConditionType(Conditional::CONDITION_CELLIS)
            ->setOperatorType(Conditional::OPERATOR_GREATERTHAN)
            ->addCondition($min)
            ->getStyle()->getFill()->setFillType(Fill::FILL_SOLID)->getEndColor()->setARGB('eb402b'); // ffb0fa

        $cond->getStyle()->getFont()->setColor(new \PhpOffice\PhpSpreadsheet\Style\Color(Color::COLOR_BLACK));

        for ($i = 7; $i <= $col_max; $i++) {
            $sheet->getComment(Coordinate::stringFromColumnIndex($i) . $i_start)->getText()->createTextRun("大于 " . $min);
        }
        break;

    default:
        break;
}

$conditionalStyles   = $sheet->getStyle('...')->getConditionalStyles();
$conditionalStyles[] = $cond;
$sheet->getStyle('...')->setConditionalStyles($conditionalStyles);

PHPSpreadSheet 其他操作 Mark

<?php

// 条件格式：Y/N 提示
$cond_y = new Conditional();
$cond_y->setConditionType(Conditional::CONDITION_CELLIS)
    ->setOperatorType(Conditional::OPERATOR_EQUAL)
    ->addCondition('"Y"')
    ->getStyle()->getFont()->getColor()->setARGB(Color::COLOR_GREEN);

$cond_n = new Conditional();
$cond_n->setConditionType(Conditional::CONDITION_CELLIS)
    ->setOperatorType(Conditional::OPERATOR_EQUAL)
    ->addCondition('"N"')
    ->getStyle()->getFont()->getColor()->setARGB(Color::COLOR_RED);

$cond_y->getStyle()->getFont()->setBold(true);
$cond_n->getStyle()->getFont()->setBold(true);


// 多个样式复合设置        
$STYLE_GREEN_USER = array( // 设置样式 - 绿色
    'font' => array(
        'color' => array('rgb' => '0000d4')
    ),
    'fill' => array(
        'fillType'   => Fill::FILL_SOLID,
        'startColor' => array('rgb' => 'ccffcc'),
    ),
);

// 调用 $STYLE_GREEN_USER
$sheet->getStyle("A13:B21")->applyFromArray($STYLE_GREEN_USER);

// 直接设置居右
$sheet->getStyle("A3:B49")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

PHPSpreadSheet 额外注意的点

在程序化制作生成大型 Excel 文件的时候，比较耗费内存，通常设置 ini_set('memory_limit', '1G');，同时可以按官方将数据缓冲到 Redis、Memcache 等地方： Memory saving，如果不需要过于复杂的渲染，也可以参考 openspout/openspout，目前一直在迭代更新。

PHPOffice / PHPSpreadsheet 命令扩展阅读：

发表评论

资源

PHP 值得关注的几个资源大全

PHP 框架

PHP 组件

PHPSpreadsheet 一些常用方法汇总