PHP使用PhpSpreadsheet读取Excel多个工作薄

发表于 2023年06月21日
PHP

PHP使用PhpSpreadsheet可以很方便读取Excel文件，包括多个工作薄的Excel.

安装依赖

composer require phpoffice/phpspreadsheet

1	composer require phpoffice/phpspreadsheet

使用

use PhpOffice\PhpSpreadsheet\IOFactory;

$path = ROOT_PATH . '/public/test.xlsx';
$reader = IOFactory::createReader('Xlsx'); // 创建读操作
$excel = $reader->load($path); // 载入excel表格

$sheets = $excel->getAllSheets();
$sheets_count = count($sheets);
echo "共 {$sheets_count} 个工作薄 \n";
foreach ($sheets as $sheet_idx=>$sheet){
    $sheet_idx1 = $sheet_idx + 1;
    $rows_count = $sheet->getHighestRow();
    echo "工作薄 {$sheet_idx1} 共 {$rows_count} 行 \n";
    for ($i = 2; $i <= $rows_count; $i++){
        $account = $sheet->getCell("G{$i}")->getValue();
        $num = $sheet->getCell("I{$i}")->getValue();
        $rate = $sheet->getCell("J{$i}")->getValue();
        echo "工作薄 {$sheet_idx1} {$account} {$num} {$rate} \n";
    }
}

use PhpOffice\PhpSpreadsheet\IOFactory;

$path = ROOT_PATH . '/public/test.xlsx';

$reader = IOFactory::createReader('Xlsx'); // 创建读操作

$excel = $reader->load($path); // 载入excel表格

$sheets = $excel->getAllSheets();

$sheets_count = count($sheets);

echo "共 {$sheets_count} 个工作薄 \n";

foreach ($sheets as $sheet_idx=>$sheet){

$sheet_idx1 = $sheet_idx + 1;

$rows_count = $sheet->getHighestRow();

echo "工作薄 {$sheet_idx1} 共 {$rows_count} 行 \n";

for ($i = 2; $i <= $rows_count; $i++){

$account = $sheet->getCell("G{$i}")->getValue();

$num = $sheet->getCell("I{$i}")->getValue();

$rate = $sheet->getCell("J{$i}")->getValue();

echo "工作薄 {$sheet_idx1} {$account} {$num} {$rate} \n";

}

这个示例是循环每个工作薄，获取所有行特定列的值。

其它用法

//设置活跃的工作薄，下标从0开始
$excel->setActiveSheetIndex(0);

//获取活跃的工作薄
$sheet = $spreadsheet->getActiveSheet();

//获取第2列第3行单元格的值
$value = $sheet->getCellByColumnAndRow(2, 3)->getValue();
//等价于
$value = $sheet->getCell("B3")->getValue();