2

I developed a PHP web application using PHPExcel libraries. All is working properly except with some Excel documents which strangely cannot be recognized as an Excel doc. When I open these files with Microsoft Excel and save them as xls, csv, xlsx, then everything works well. So... I think some Excel files are not exactly what their extension says. Perhaps they are disguised XML? Is it possible to get XML support with PHPExcel?

Here's the error:

Fatal error: Uncaught exception 'PHPExcel_Reader_Exception' with message 'The filename /tmp/phphrnIR2 is not recognised as an OLE file' in /home/www/text/excel/reader/Classes/PHPExcel/Shared/OLERead.php:89 Stack trace: #0 /home/test/excel/reader/Classes/PHPExcel/Reader/Excel5.php(1164): PHPExcel_Shared_OLERead->read('/tmp/phphrnIR2') #1 /home/www/text/excel/reader/Classes/PHPExcel/Reader/Excel5.php(612): PHPExcel_Reader_Excel5->_loadOLE('/tmp/phphrnIR2') #2 /home/www/test/excel/actions.php(60): PHPExcel_Reader_Excel5->load('/tmp/phphrnIR2') #3 /home/www/test/excel/index.php(77): include_once('/home/www/test/ex...') #4 {main} thrown in /home/www/test/excel/reader/Classes/PHPExcel/Shared/OLERead.php on line 89

And the php code for file recognition:

 $name     = $_FILES['file']['name'];
                    $tname    = $_FILES['file']['tmp_name'];
                    $type     = $_FILES['file']['type'];

                    if($type == 'application/vnd.ms-excel')
                    {
                            // Excel 97 extension
                            $ext = 'xls';
                    }
                    else if($type == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
                    {
                            // Excel 2007 and 2010 extension
                            $ext = 'xlsx';
                    }


                    else if($type == 'text/csv')
                    {
                            // Excel CSV extension
                            $ext = 'csv';


                    }else{

                            // Invalid Extension   
                    ?> 
                            <script languaje="javascript">
                                  swal("Bad file...", "A valid extension must be XLS, XLSX o CSV!", "error");
                            </script> 
                            <?php
                            exit();
                    }
 //reader creation
                    $objReader = PHPExcel_IOFactory::createReader($$ext);

                    //uploading file
                    $objPHPExcel = $objReader->load($tname);


                    $dim = $objPHPExcel->getActiveSheet()->calculateWorksheetDimension();
user3321425
  • 201
  • 1
  • 2
  • 17

2 Answers2

2

Well, despite the fact that it has a .xls extension, it's entirely possible that it isn't a BIFF-format xls file.

A lot of people give a file an xls extension when it's a csv format file, or even HTML markup..... never trust the extension; and as the mime type will be based on the file extension, you can't take that at face value either.

Rather than specifically creating a Reader based on the extension/mime type.... use the PHPExcel IOFactory identify() method to see what file format PHPExcel believes the file to be. Or call the IOFactory's load() method so that it can try to identify the file format for itself.

Clearly this file isn't BIFF-format (or was created using a very early version of the BIFF-format, before version 5), otherwise it would be recognised as an OLE file

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • Thanks a lot for your response. "Strange" Excel files are generated by an external application (I haven't access to its code), so I assume you are entirely right and maybe It is using an old BIFF-format version. Though I need to read about identify() and load(). – user3321425 Sep 16 '15 at 10:36
  • Well, after many attempts I've discovered the $type variable is returning an empty value when I try to upload the "strange" XLS file and I don't know why. Using the "file" command from my Debian Konsole says the file is "XML document text". When I do the same with a valid document "file" command says: "Composite Document File V2 Document, Little Endian, Os: Windows, Version 6.1, Code page: 1252, Author: User Me, Last Saved By: User Me, Create Time/Date: Mon Sep 14 10:59:28 2015, Last Saved Time/Date: Mon Sep 14 10:59:28 2015, Security: 0 Any help would be appreciated. Thank you. – user3321425 Sep 18 '15 at 10:31
  • Uncertain what type of file it is, and very difficult to tell without seeing it.... but if it's a plain XML file (as identified as "XML document text") then PHPExcel probably won't be able to read it, because it's not recognised as a spreadsheet format – Mark Baker Sep 18 '15 at 10:34
  • Thanks a lot Mark. Ummm.. is there a way to "export" or automatic "save as" a valid xls/csv/xlsx before upload the file to be able to process the file with PXPExcel? Maybe I could solve the problem this way. But maybe I should open another thread. :) – user3321425 Sep 18 '15 at 10:48
  • Forget what I said about the empty file. Server was uploading an empty file because a php restriction. I uploaded the upload_max_value and now the $type variable returns: "application/vnd.ms-excel" But the problem with "is not recognised as an OLE file" persists. – user3321425 Sep 18 '15 at 11:11
  • Don't trust mime type.... typically a browser sets that based purely on the file extension, and not on the actual format of the file.... try taking a look at it in a text editor and see what it looks like – Mark Baker Sep 18 '15 at 11:15
  • Using a text editor the content is: – user3321425 Sep 18 '15 at 11:23
  • Which is not, in any way a BIFF-format xls file, simply an xml file with an extension of .xls – Mark Baker Sep 18 '15 at 11:25
  • This doc is generated via web application, so It has an Excel look and can be opened with Microsoft Excel but... actually It's an XML. What a mess! – user3321425 Sep 18 '15 at 11:30
  • As an XML file can be pretty arbitrary in its structure, it would probably need a custom Reader.... there is a SpreadsheetML format that was introduced for Excel 2003, which uses the `Excel2003XML` reader in PHPExcel, which might read your file – Mark Baker Sep 18 '15 at 11:34
  • Thanks a lot again for your help and time Mark, I have to take a look at Excel2003XML. – user3321425 Sep 18 '15 at 11:38
  • "Invalid Spreadsheet file" with Excel2003XML and "not recognised as an OLE file" with Excel5 :( – user3321425 Sep 18 '15 at 11:53
1

I came across the same problem you have faced, anonymous user. This is how I fixed it:

Have PHPExcel discover the type

Load quickly and simply

require_once('Classes'.DIRECTORY_SEPARATOR.'PHPExcel.php');

// Tell PHPExcel to load this file and make its best guess as to its type.
$objPHPExcel = PHPExcel_IOFactory::load($_FILES['uploaded_file']['tmp_name']);

Load with options

require_once('Classes'.DIRECTORY_SEPARATOR.'PHPExcel.php');

// Tell PHPExcel that you will be loading a file.
$objReader = PHPExcel_IOFactory::createReaderForFile($_FILES['uploaded_file']['tmp_name']);

// Set your options.
$objReader->setReadDataOnly(true);

// Tell PHPExcel to load this file and make its best guess as to its type.
$objPHPExcel = $objReader->load($_FILES['uploaded_file']['tmp_name']);

source

Now that you have a PHPExcel object you can work with the worksheets this way:

    foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
        $worksheets[] = $worksheet->toArray();
    }

    foreach($worksheets as $sheet) {

        foreach($sheet as $row) {
            print_r($row);
        }

    }
Matthew
  • 309
  • 4
  • 12