PhpSpreadsheet

PhpSpreadsheet 是一个用于读写 Excel 和其他电子表格格式的 PHP 库，支持包括 .xlsx、.xls、.csv 等文件格式。它非常强大，适用于处理电子表格数据的各种操作，如创建、编辑、读取、格式化和导出表格。

https://github.com/PHPOffice/PhpSpreadsheet

基础用法

安装 PhpSpreadsheet

composer require phpoffice/phpspreadsheet

创建新 Excel 文件

require ‘vendor/autoload.php’;

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

$spreadsheet = new Spreadsheet(); // 创建新的电子表格
$sheet = $spreadsheet->getActiveSheet(); // 获取活动的工作表
$sheet->setCellValue(‘A1’, ‘Hello World!’); // 设置单元格内容

$writer = new Xlsx($spreadsheet); // 创建 Excel 文件的写入器
$writer->save(‘hello_world.xlsx’); // 保存文件到本地

读取 Excel 文件

require ‘vendor/autoload.php’;

use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = ‘hello_world.xlsx’;
$spreadsheet = IOFactory::load($inputFileName); // 加载 Excel 文件
$sheet = $spreadsheet->getActiveSheet();

// 读取某个单元格的值
echo $sheet->getCell(‘A1’)->getValue();

写入数据到 Excel 文件

require ‘vendor/autoload.php’;

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

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

// 设置表格数据
$sheet->setCellValue(‘A1’, ‘Name’);
$sheet->setCellValue(‘B1’, ‘Age’);
$sheet->setCellValue(‘A2’, ‘John Doe’);
$sheet->setCellValue(‘B2′, ’28’);

$writer = new Xlsx($spreadsheet);
$writer->save(‘data.xlsx’);

读取多行和多列的数据

require ‘vendor/autoload.php’;

use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = ‘data.xlsx’;
$spreadsheet = IOFactory::load($inputFileName);
$sheet = $spreadsheet->getActiveSheet();

// 循环读取数据
foreach ($sheet->getRowIterator() as $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false); // 遍历所有单元格

foreach ($cellIterator as $cell) {
echo $cell->getValue() . “\t”;
}
echo “\n”;
}

设置单元格样式

require ‘vendor/autoload.php’;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Border;

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

$sheet->setCellValue(‘A1’, ‘Bold Red Text’);

// 设置字体样式
$sheet->getStyle(‘A1’)->getFont()->setBold(true);
$sheet->getStyle(‘A1’)->getFont()->getColor()->setARGB(Color::COLOR_RED);

// 设置边框
$sheet->getStyle(‘A1’)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);

$writer = new Xlsx($spreadsheet);
$writer->save(‘styled.xlsx’);

导出为 CSV

require ‘vendor/autoload.php’;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Csv;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue(‘A1’, ‘Name’);
$sheet->setCellValue(‘B1’, ‘Age’);
$sheet->setCellValue(‘A2’, ‘John’);
$sheet->setCellValue(‘B2′, ’30’);

// 导出为 CSV 文件
$writer = new Csv($spreadsheet);
$writer->save(‘data.csv’);