PHPSpreadsheet 一些常用方法汇总

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 插入新行、删除行、以及 PHPSpreadSheet 删除列

---

<?php

// 以下代码在第 7 行之前插入 2 个新行：
$spreadsheet->getActiveSheet()->insertNewRowBefore(7, 2);

// 将从第 7 行开始删除 2 行（即第 7 行和第 8 行）。
$spreadsheet->getActiveSheet()->removeRow(7, 2);

// 删除C列
$spreadsheet->getActiveSheet()->removeColumn('C', 2);

请注意，这是一个相当繁琐的过程，特别是对于大型工作表，尤其是当您从工作表开头附近插入/删除行/列时。

---

如果您需要插入/删除多个连续的行/列，请尽可能始终使用第二个参数，而不是多次调用来插入/删除单个行/列。

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 命令扩展阅读：

---

• PHPOffice/PhpSpreadsheet: A pure PHP library for reading and writing spreadsheet files
• openspout/openspout: Read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way
• PHPSpreadsheet 一些常用方法汇总
• MySQL 命令行数据导出到 Excel

CommandNotFound ⚡️ 坑否 - 其他频道扩展阅读：

---

• m4 命令
• PHP 计算两个 GPS 之间的距离
• rev 命令
• bye 命令
• qpdf 命令

---

---

发表评论