在PhpSpreadsheet中读取Xlsx文件 [英] Read Xlsx file in PhpSpreadsheet

查看：450
本文介绍了在PhpSpreadsheet中读取Xlsx文件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想读取在Microsoft Excel中创建的xlsx文件，但是当我运行以下代码时...

I want to read an xlsx file that was created in Microsoft Excel, but when I run the following code...

$Source_File = "test.xlsx";
$Spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($Source_File);

...我收到以下错误:

...I receive the following error:

Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Reader\Exception: Unable to identify a reader for this file in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php:163
Stack trace:
  #0 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php(93): PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile('file:///home/ar...')
  #1 /var/www/html/Function_Spreadsheet.php(480): PhpOffice\PhpSpreadsheet\IOFactory::load('file:///home/ar...')
  #2 /var/www/html/Function_Home.php(3747): Spreadsheet_Reader_1('/var/www/html/F...', 3745, Array, Array)
  #3 {main} thrown in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php on line 163

如果我改用$Spreadsheet = IOFactory::load($Source_File);

如果我改用$Spreadsheet = $reader->load($Source_File); 

Warning: ZipArchive::getFromName(): Invalid or uninitialized Zip object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 311

Warning: ZipArchive::getFromName(): Invalid or uninitialized Zip object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 313

Notice: Trying to get property 'Relationship' of non-object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 350

Warning: Invalid argument supplied for foreach() in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 350

Warning: ZipArchive::getFromName(): Invalid or uninitialized Zip object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 311

Warning: ZipArchive::getFromName(): Invalid or uninitialized Zip object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 313

Notice: Trying to get property 'Relationship' of non-object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 397

Warning: Invalid argument supplied for foreach() in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 397

Warning: ZipArchive::getFromName(): Invalid or uninitialized Zip object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 311

Warning: ZipArchive::getFromName(): Invalid or uninitialized Zip object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 313

Notice: Trying to get property 'Override' of non-object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 1855

Warning: Invalid argument supplied for foreach() in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 1855

Warning: ZipArchive::close(): Invalid or uninitialized Zip object in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php on line 1883

我的PHP v7.2脚本在Ubuntu 18.04上的Apache中可以读取并打开该文件.我读了几篇论坛文章，这些文章提出了以下建议:

The file is readable and open-able by my PHP v7.2 script, in Apache on Ubuntu 18.04. I read several forum posts, which suggest the following, which I have done:

  • Install the required extensions and restart apache
  • Check the file permissions

我尝试在LibreOffice中打开文件并将其另存为xlsx，但是发生相同的错误(如果另存为xls，则不会发生错误).

I tried opening the file in LibreOffice and saving it as an xlsx there, but the same error occurs (no error if I save as xls).

我可以创建阅读器$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();，但是当我执行$Spreadsheet = $reader->load($Source_File);或$Spreadsheet = IOFactory::load($Source_File);时，会遇到相同的错误.

I can create a reader $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();, but when I do $Spreadsheet = $reader->load($Source_File); or $Spreadsheet = IOFactory::load($Source_File); I get the same error.

此外，我可以创建一个可以读取xls文件的xls阅读器.我也可以创建一个xlsx阅读器，但它不会读取xlsx文件，在尝试读取xlsx文件时会出现相同的错误. 那么，为什么xlsx文件会发生错误?

Also, I can create an xls reader that can read xls files. I can also create an xlsx reader, but it will not read the xlsx file, it gives the same error when trying to read the xlsx file. So, why is the error occurring with the xlsx file?

此外，我阅读了错误消息指向的源代码(IOFactory.php)，并找到了发生错误的以下位置(第139行附近)...

Also, I read the source code that the error message points to (IOFactory.php) and found the following location (near line #139) where the error occurs...

//Let's see if we are lucky
if (isset($reader) && $reader->canRead($filename))
{
    return $reader;
}

...并且我搜索了canRead的定义，但在/vendor/phpoffice/phpspreadsheet/的任何地方都找不到. canRead的定义是什么??我想，如果我能阅读canRead的定义，那么也许我会明白问题的根本原因是什么.

...and I searched for the definition of canRead, but did not find it anywhere in /vendor/phpoffice/phpspreadsheet/. Where is canRead defined? I think if I could read the definition of canRead, then maybe I will understand what the root cause of the issue is.

我从评论和讨论中了解到，canRead()是在\PhpSpreadsheet\Reader\Xlsx.php中在第65行附近定义的.在canRead()中，$zip->open($pFilename)返回错误代码ZipArchive::ER_NOENT，表示"那么，为什么会发生此错误?

I learned from comments and discussion that canRead() is defined in \PhpSpreadsheet\Reader\Xlsx.php starting around line 65. In canRead(), $zip->open($pFilename) returns an error code, ZipArchive::ER_NOENT, which means "No such file". However, the file exists. So, why is this error occurring?

此网页建议存在多种类型的xlsx文件.因此，我运行了file test.xlsx，它显示了Microsoft Excel 2007+.然后，我在LibreOffice Calc中打开电子表格，并将其保存为OOXML类型的xlsx文件，然后重新运行file test.xlsx，显示为Microsoft OOXML.然后，我重新运行了PHP脚本，但是遇到了同样的错误.因此，看来我的xlsx文件类型不是问题.

This web page suggests that there are multiple types of xlsx files. So, I ran file test.xlsx, which displayed Microsoft Excel 2007+. Then I opened up the spreadsheet in LibreOffice Calc and saved it as an OOXML type of xlsx file and re-ran file test.xlsx, which displayed Microsoft OOXML. Then I re-ran the PHP script, but got the same error. So, it seems that my xlsx file type is not the issue.

因此，我决定使用 PHPExcel (即使已弃用)来完成一些必要的工作.当我使用PHPExcel运行脚本时，收到类似的错误，提示canRead()无法检测到xlsx文件.

So, I decided to use PHPExcel (even though it is deprecated) to get some necessary work done. When I ran the script using PHPExcel, I received a similar error about canRead() not being able to detect the xlsx file.

因此，我继续阅读此网页，并遵循了wesood的最新内容建议，该建议来自此网页.此解决方案对我有用:在文件/PHPExcel/IOFactory.php中，我在if (isset($reader) && $reader->canRead($filename))之前紧接着添加了PHPExcel_Settings::setZipClass(\PHPExcel_Settings::PCLZIP);.

So, I continued reading this web page and followed wesood's last suggestion, which was sourced from the accepted answer on this web page. This solution worked for me: In the file /PHPExcel/IOFactory.php, I added PHPExcel_Settings::setZipClass(\PHPExcel_Settings::PCLZIP); immediately before if (isset($reader) && $reader->canRead($filename)).

但是，我仍然想知道如何在PhpSpreadsheet中解决此问题.看来我需要详细了解 pclzip 的工作原理，以及是否需要采取类似的措施用PhpSpreadsheet完成.

However, I still want to know how to solve this issue in PhpSpreadsheet. It seems I need to learn more about how pclzip works and if a similar action needs to be done with PhpSpreadsheet.

我今天尝试运行该脚本，看来添加PHPExcel_Settings::setZipClass(\PHPExcel_Settings::PCLZIP);不再有效.所以，我又被困住了...

I tried running the script today and it seems that the addition of PHPExcel_Settings::setZipClass(\PHPExcel_Settings::PCLZIP); no longer works. So, I am stuck again...

我在做什么错了?欢迎任何帮助！

根据评论的建议，我使用了通过Google搜索结果找到的随机XLSX文件对脚本进行了测试(例如，

Following recommendations from comments, I tested the script using random XLSX files found via Google search results (e.g., this file), which were either Excel 2007+ or Microsoft OOXML types and the same error displays for PhpSpreadsheet:

致命错误:未捕获的PhpOffice \ PhpSpreadsheet \ Reader \ Exception: 无法在以下位置识别此文件的阅读器 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php:176 堆栈跟踪:#0 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php(113): PhpOffice \ PhpSpreadsheet \ IOFactory :: createReaderForFile('file:///var/www ...')#1/var/www/html/Function_Spreadsheet.php(798):PhpOffice \ PhpSpreadsheet \ IOFactory :: identify('file :///var/www ...')#2 /var/www/html/Function_Home.php(3748): Spreadsheet_Reader_1('/var/www/html/F ...'，3746，数组，数组)#3 {main}在第176行的/var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php中抛出

Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Reader\Exception: Unable to identify a reader for this file in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php:176 Stack trace: #0 /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php(113): PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile('file:///var/www...') #1 /var/www/html/Function_Spreadsheet.php(798): PhpOffice\PhpSpreadsheet\IOFactory::identify('file:///var/www...') #2 /var/www/html/Function_Home.php(3748): Spreadsheet_Reader_1('/var/www/html/F...', 3746, Array, Array) #3 {main} thrown in /var/www/html/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/IOFactory.php on line 176

推荐答案

据我了解，您缺少了一块.为什么不先创建阅读器，然后加载文件.

From my understanding, you are missing a piece. Why don't you first create a reader and then load the file.

尝试以下代码. 它可以识别扩展名并相应地创建该类型的阅读器. 

$inputFileName = "Text.xlsx";

/**  Identify the type of $inputFileName  **/
$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName);

/**  Create a new Reader of the type that has been identified  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

/**  Load $inputFileName to a Spreadsheet Object  **/
$spreadsheet = $reader->load($inputFileName);

/**  Convert Spreadsheet Object to an Array for ease of use  **/
$schdeules = $spreadsheet->getActiveSheet()->toArray();

现在，您只需在结果数组上运行一个foreach循环即可.

Now you can simply run a foreach loop on the result array.

foreach( $schdeules as $single_schedule )
{               
    echo '<div class="row">';
    foreach( $single_schedule as $single_item )
    {
        echo '<p class="item">' . $single_item . '</p>';
    }
    echo '</div>';
}

这是从我的一个项目中经过测试和选择的所有工作代码.干杯.

This is all tested and working code picked from one of my projects. Cheers.

这篇关于在PhpSpreadsheet中读取Xlsx文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
PHP最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆