Read XLS in PHP using PhpSpreadsheet

Question

I have a requirements to read XLS files (not xlsx) using PhpSpreadsheet and I having trouble. I tried this (as the documentation say but...)

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load("lista.xls");
$worksheet = $spreadsheet->getActiveSheet();

echo '<table>' . PHP_EOL;
foreach ($worksheet->getRowIterator() as $row) {
    echo '<tr>' . PHP_EOL;
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,
                                                       //    even if a cell value is not set.
                                                       // By default, only cells that have a value
                                                       //    set will be iterated.
    foreach ($cellIterator as $cell) {
        echo '<td>' .
             $cell->getValue() .
             '</td>' . PHP_EOL;
    }
    echo '</tr>' . PHP_EOL;
}
echo '</table>' . PHP_EOL;

echo "<br>fin";

but didn't work (it worked with a xlsx file, but no with a xls file!)

Then I tried to open file differently:

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
$sheet = $reader->load("lista.xls");

but also doesn't work...

I really need to solve this... please help! PS: I've tried BasicExcel & PHPExcel but also didn't seem to work

I the manual it say to use the first method you used and it will attempt identify the type automatically! Are you sure your xls file is not corrupted — RiggsFolly, Sep 13 '18 at 16:57
RiggsFolly strange... because of your comment I tried myself to create a xls and it worked (the first method) but my client sent me quiet a few files and none worked... it seems he has a problema with his office... thanks a lot! — Ari Waisberg, Sep 13 '18 at 17:11
Try opening one of the clients files and using saveas save a version and see if that works — RiggsFolly, Sep 13 '18 at 17:12
Also check if client is using something OTHER than Excel and just saving as an Excel file, that may explain the issue — RiggsFolly, Sep 13 '18 at 17:13
Yes! He is exporting from some other system to an "xls file" that Office recognize but the library don't... Thank you very much! Do you want to post it as a "solution" so I mark it? — Ari Waisberg, Sep 13 '18 at 18:48

score 21 · Answer 1 · answered Sep 16 '19 at 00:32

<?php
require 'vendor/autoload.php';

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

$spreadsheet = new Spreadsheet();

$inputFileType = 'Xlsx';
$inputFileName = './mysheet.xlsx';

/**  Create a new Reader of the type defined in $inputFileType  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  Advise the Reader that we only want to load cell data  **/
$reader->setReadDataOnly(true);

$worksheetData = $reader->listWorksheetInfo($inputFileName);

foreach ($worksheetData as $worksheet) {

$sheetName = $worksheet['worksheetName'];

echo "<h4>$sheetName</h4>";
/**  Load $inputFileName to a Spreadsheet Object  **/
$reader->setLoadSheetsOnly($sheetName);
$spreadsheet = $reader->load($inputFileName);

$worksheet = $spreadsheet->getActiveSheet();
print_r($worksheet->toArray());

}

I am [unable to read excel file](https://stackoverflow.com/q/64906846/6854117) — Moeez, Nov 19 '20 at 07:13

score 7 · Accepted Answer · answered Sep 13 '18 at 19:14

7

I would check with your Client to see if they are using real Excel or some other spreadsheet.

If they are using some other spreadsheet and exporting using a "Export as Excel" functionality that may explain why its not being recognised by PHPSpreadsheet as any of the possible valid excel formats.

In which case, and depending what is in the spreadsheet, it may be worth asking them to export their spreadsheet as a csv (comma delimited values) file, as that is such a simple format it should be a valid output. You could then read it using fgetcsv() function calls instead of having to use PHPSpreadsheet.

answered Sep 13 '18 at 19:14

RiggsFolly

93,638
21
103
149

2

exactly! they are exporting from a system, not from excel itself! Thanx! – Ari Waisberg Sep 13 '18 at 20:41
1

How does exporting from a system, not excel, cause the format to become unrecognized? I am experiencing this issue, but in my case it is an `xlsx` file created in Excel. – Arya Dec 17 '18 at 20:52
2

@Arya xlsx is a whole different world that xls... xlsx it's pure xml and you don't need an excel library, you can open it in any code editor and understand it... xls is propietary so, you need them... In that case the lib seems to create the file in a way that excel itself recognize it, but other libs do not... – Ari Waisberg Dec 21 '18 at 15:52
I am [unable to read excel file](https://stackoverflow.com/q/64906846/6854117) – Moeez Nov 19 '20 at 07:13

Budi Prasetyo · Answer 3 · 2019-06-14T07:24:39.767

0

try to remove this statement

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load("lista.xls");

and change it with this

$inputFileName = "lista.xls";
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($inputFileName);
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load($inputFileName);

edited Jun 14 '19 at 07:24

answered Jun 14 '19 at 07:07

Budi Prasetyo

11
1

I am [unable to read excel file](https://stackoverflow.com/q/64906846/6854117) can you please check – Moeez Nov 19 '20 at 08:39

Vivek Tailor · Answer 4 · 2021-05-29T14:31:32.363

0

the catch here is to convert it to array after loading the file

$file = "file.xlsx";
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file);
$spreadsheet = $spreadsheet->getActiveSheet();
$data_array =  $spreadsheet->toArray();

edited May 29 '21 at 14:31

answered May 29 '21 at 11:25

Vivek Tailor

206
3
6

Read XLS in PHP using PhpSpreadsheet

4 Answers4