PHP高效、轻量级表格数据处理库 OpenSpout ,很好用

OpenSpout 是一个高效、轻量级的 PHP 库，用于处理电子表格文件（如 Excel 和 CSV）。它支持读取和写入大型文件，且内存占用低。本文将详细介绍如何安装和使用 OpenSpout。

安装

OpenSpout 可以通过 Composer 安装。确保你已经安装了 Composer，然后在项目根目录下运行以下命令：

composer require openspout/openspout

安装完成后，Composer 会自动加载 OpenSpout。

基本使用

读取 Excel 文件

以下是一个读取 Excel 文件的示例：

require 'vendor/autoload.php';use OpenSpout\Reader\Common\Creator\ReaderEntityFactory;// 创建 Reader 对象
$reader = ReaderEntityFactory::createXLSXReader();// 打开文件
$reader->open('example.xlsx');foreach ($reader->getSheetIterator() as $sheet) {foreach ($sheet->getRowIterator() as $row) {// 处理每一行数据$cells = $row->getCells();print_r($cells);}
}// 关闭 Reader
$reader->close();

写入 Excel 文件

以下是一个写入 Excel 文件的示例：

require 'vendor/autoload.php';use OpenSpout\Writer\Common\Creator\WriterEntityFactory;
use OpenSpout\Common\Entity\Row;// 创建 Writer 对象
$writer = WriterEntityFactory::createXLSXWriter();// 打开文件
$writer->openToFile('output.xlsx');// 创建行数据
$rows = [WriterEntityFactory::createRowFromArray(['Name', 'Age', 'City']),WriterEntityFactory::createRowFromArray(['John Doe', 30, 'New York']),WriterEntityFactory::createRowFromArray(['Jane Doe', 25, 'Los Angeles']),
];// 写入行数据
foreach ($rows as $row) {$writer->addRow($row);
}// 关闭 Writer
$writer->close();

读取 CSV 文件

以下是一个读取 CSV 文件的示例：

require 'vendor/autoload.php';use OpenSpout\Reader\Common\Creator\ReaderEntityFactory;// 创建 Reader 对象
$reader = ReaderEntityFactory::createCSVReader();// 打开文件
$reader->open('example.csv');foreach ($reader->getSheetIterator() as $sheet) {foreach ($sheet->getRowIterator() as $row) {// 处理每一行数据$cells = $row->getCells();print_r($cells);}
}// 关闭 Reader
$reader->close();

写入 CSV 文件

以下是一个写入 CSV 文件的示例：

require 'vendor/autoload.php';use OpenSpout\Writer\Common\Creator\WriterEntityFactory;
use OpenSpout\Common\Entity\Row;// 创建 Writer 对象
$writer = WriterEntityFactory::createCSVWriter();// 打开文件
$writer->openToFile('output.csv');// 创建行数据
$rows = [WriterEntityFactory::createRowFromArray(['Name', 'Age', 'City']),WriterEntityFactory::createRowFromArray(['John Doe', 30, 'New York']),WriterEntityFactory::createRowFromArray(['Jane Doe', 25, 'Los Angeles']),
];// 写入行数据
foreach ($rows as $row) {$writer->addRow($row);
}// 关闭 Writer
$writer->close();

高级功能

处理大型文件

OpenSpout 通过流式处理支持大型文件，避免内存溢出。以下是一个处理大型文件的示例：

require 'vendor/autoload.php';use OpenSpout\Reader\Common\Creator\ReaderEntityFactory;// 创建 Reader 对象
$reader = ReaderEntityFactory::createXLSXReader();// 打开文件
$reader->open('large_file.xlsx');foreach ($reader->getSheetIterator() as $sheet) {foreach ($sheet->getRowIterator() as $row) {// 处理每一行数据$cells = $row->getCells();print_r($cells);}
}// 关闭 Reader
$reader->close();

自定义样式

OpenSpout 允许你自定义单元格样式。以下是一个自定义样式的示例：

require 'vendor/autoload.php';use OpenSpout\Writer\Common\Creator\WriterEntityFactory;
use OpenSpout\Common\Entity\Style\Style;
use OpenSpout\Common\Entity\Row;// 创建 Writer 对象
$writer = WriterEntityFactory::createXLSXWriter();// 打开文件
$writer->openToFile('styled_output.xlsx');// 创建样式
$style = (new Style())->setFontBold()->setFontSize(14)->setFontColor(Color::BLUE)->setBackgroundColor(Color::YELLOW);// 创建行数据
$rows = [WriterEntityFactory::createRowFromArray(['Name', 'Age', 'City'], $style),WriterEntityFactory::createRowFromArray(['John Doe', 30, 'New York']),WriterEntityFactory::createRowFromArray(['Jane Doe', 25, 'Los Angeles']),
];// 写入行数据
foreach ($rows as $row) {$writer->addRow($row);
}// 关闭 Writer
$writer->close();