PhpSpreadsheet读取excel

PhpSpreadsheet

安装

	composer require phpoffice/phpspreadsheet

支持的格式	是否支持读	是否支持写
Open Document Format/OASIS (.ods)	✓	✓
Office Open XML (.xlsx) Excel 2007 and above	✓	✓
BIFF 8 (.xls) Excel 97 and above	✓	✓
BIFF 5 (.xls) Excel 95	✓
SpreadsheetML (.xml) Excel 2003	✓
Gnumeric	✓
HTML	✓	✓
SYLK	✓
CSV	✓	✓
PDF (依赖安装 TCPDF, Dompdf 或 mPDF 库)		✓

源文档

compress.xlsx 文件

php读取

<?php

require "vendor/autoload.php";

// $inputFileName = 'source.xlsx';
$inputFileName = 'compress.xlsx';
$sheetname = 'Sheet1';

/**  1.检测文件类型  **/
$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName);
/**  2.根据类型创建合适的读取器对象  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

// 3. 设置读取器选项
// $reader->setReadDataOnly(true);
$reader->setLoadSheetsOnly($sheetname);

class MyReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
    public function readCell($column, $row, $worksheetName = '')
    {
        //  选定区域
        if ($row >= 1 && $row <= 7) {
            if (in_array($column, range('A','B'))) {
                return true;
            }
        }
        return false;
    }
}
$filterSubset = new MyReadFilter();
// 使用过滤器
$reader->setReadFilter($filterSubset);

// 4.读取表格表对象
$spreadsheet = $reader->load($inputFileName);

// 5. 得到工作表
$workSheet = $spreadsheet->getActiveSheet();

// 6. 读取表格内容
$cellA1 = $workSheet->getCell('A1');
echo 'Value: ', $cellA1->getValue(), '; Address: ', $cellA1->getCoordinate(), PHP_EOL;

// 7.超出范围不读取
$cellA9 = $workSheet->getCell('A9');
echo 'Value: ', $cellA9->getValue(), '; Address: ', $cellA9->getCoordinate(), PHP_EOL;

测试

D:\code-base\php\pkg_test>php "d:\code-base\php\pkg_test\xlsx.php"
Value: age; Address: A1
Value: ; Address: A9