Reading spreadsheet using PHPExcel

Question

I'm trying to upload a spreadsheet and read it into a MySQL database using PHPExcel.

For .xlsx files it works fine but whenever I try to upload a .ods file it throws the error: PHP Fatal error: Call to a member function getNamespaces() on a non-object in PHPExcel_1.7.9/Classes/PHPExcel/Reader/OOCalc.php on line 341

What's going wrong?

HTML Form:

<form method="post" enctype="multipart/form-data">
Upload File: <input type="file" name="spreadsheet"/>
<input type="submit" name="submit" value="Submit" />
</form>

PHP (In same file):

//Check valid spreadsheet has been uploaded
if(isset($_FILES['spreadsheet'])){
if($_FILES['spreadsheet']['name']){
    if(!$_FILES['spreadsheet']['error'])
    {

        $inputFile = $_FILES['spreadsheet']['name'];
        $extension = strtoupper(pathinfo($inputFile, PATHINFO_EXTENSION));
        if($extension == 'XLSX' || $extension == 'ODS'){

            //Read spreadsheeet workbook
            try {
                 $inputFile = $_FILES['spreadsheet']['tmp_name'];
                 $inputFileType = PHPExcel_IOFactory::identify($inputFile);
                 $objReader = PHPExcel_IOFactory::createReader($inputFileType);
                 $objPHPExcel = $objReader->load($inputFile);
            } catch(Exception $e) {
                    die($e->getMessage());
            }

            //Get worksheet dimensions
            $sheet = $objPHPExcel->getSheet(0); 
            $highestRow = $sheet->getHighestRow(); 
            $highestColumn = $sheet->getHighestColumn();

            //Loop through each row of the worksheet in turn
            for ($row = 1; $row <= $highestRow; $row++){ 
                    //  Read a row of data into an array
                    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
                    //Insert into database
            }
        }
        else{
            echo "Please upload an XLSX or ODS file";
        }
    }
    else{
        echo $_FILES['spreadsheet']['error'];
    }
}
}

?>

change this line ` $inputFile = $_FILES['spreadsheet']['name'];` to ` $inputFile = $_FILES['spreadsheet']['tmp_name'];` — Nicolas Racine, Feb 02 '14 at 06:53

Nicolas Racine · Accepted Answer · 2017-03-07T21:17:11.880

18

When a file is uploaded to your webserver, The file will be saved in the temporary folder of your system with a random name.

What you were trying to do was giving the actual name of the file you uploaded, But since the file was created with a random name in the tmp folder. You will need to use tmp_name instead, Which actually point the that random named file.

Also note, in name You only have the name of the file that was uploaded and not the path, But with tmp_name you have the actual path to the file.

See the following example of a file upload you would get.

array(
 [UploadFieldName]=>array(
    [name] => MyFile.jpg
    [type] => image/jpeg
    [tmp_name] => /tmp/php/php6hst32
    [error] => UPLOAD_ERR_OK
    [size] => 98174
  )
)

change your code to this instead

 //Check valid spreadsheet has been uploaded
if(isset($_FILES['spreadsheet'])){
if($_FILES['spreadsheet']['tmp_name']){
if(!$_FILES['spreadsheet']['error'])
{

    $inputFile = $_FILES['spreadsheet']['tmp_name'];
    $extension = strtoupper(pathinfo($inputFile, PATHINFO_EXTENSION));
    if($extension == 'XLSX' || $extension == 'ODS'){

        //Read spreadsheeet workbook
        try {
             $inputFileType = PHPExcel_IOFactory::identify($inputFile);
             $objReader = PHPExcel_IOFactory::createReader($inputFileType);
                 $objPHPExcel = $objReader->load($inputFile);
        } catch(Exception $e) {
                die($e->getMessage());
        }

        //Get worksheet dimensions
        $sheet = $objPHPExcel->getSheet(0); 
        $highestRow = $sheet->getHighestRow(); 
        $highestColumn = $sheet->getHighestColumn();

        //Loop through each row of the worksheet in turn
        for ($row = 1; $row <= $highestRow; $row++){ 
                //  Read a row of data into an array
                $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
                //Insert into database
        }
    }
    else{
        echo "Please upload an XLSX or ODS file";
    }
}
else{
    echo $_FILES['spreadsheet']['error'];
}
}
}

?>

edited Mar 07 '17 at 21:17

answered Feb 02 '14 at 06:59

Nicolas Racine

1,031
3
13
35

I've edited my code above as per your suggestion (copying directly made it fail my extension check). Now I get the following error in my error log: PHP Fatal error: Call to a member function getNamespaces() on a non-object in PHPExcel_1.7.9/Classes/PHPExcel/Reader/OOCalc.php on line 341 – baarkerlounger Feb 02 '14 at 07:03
1

let me download phpexcel il try it out. but it may be a bug in phpexcel try a different file – Nicolas Racine Feb 02 '14 at 07:06
Did you try an other xlsx file to see if it is working ? here after downloading phpExcel class i get the try catch error "File dont exist" ( so i guess file can't be access in my tmp folder. ) – Nicolas Racine Feb 02 '14 at 07:22
Trying the exact same file as before but this time saved as a .xlsx everything works fine - ODS continues to throw the same error... – baarkerlounger Feb 02 '14 at 07:25
When I upload a .xlsx file everything works fine. When I upload an open office .ods spreadsheet file I get the error log I posted. Seems like whatever is wrong is specific to the OOCalc Reader class – baarkerlounger Feb 02 '14 at 07:28
1

Hum.. if i use an ods format here it works fine too,. It might be some kind of formula that you are using in the ods format that make an bug in phpexcel,. I would love to help you more on that but i think i can't help more :s – Nicolas Racine Feb 02 '14 at 07:32
Actually I've noticed that .xlsx works fine and saving that as .ods in open office works fine. The problem (which apologies I didn't notice/mention) arises with .ods documents downloaded directly from Google Drive Documents. Google docs must save something extra or differently that screws it up. I'll mark this as solved since you fixed my original problem. – baarkerlounger Feb 02 '14 at 07:37
@NicolasRacine, in your code you have used `$_FILES['spreadsheet']['tmp_name']` for cheking file extension but if you check tmp_name file's extension it will be `.tmp` always then it will fail the condition, so you have to use `$_FILES['spreadsheet']['name']` for checking its extension. – Haritsinh Gohil May 21 '19 at 07:38

score 1 · Answer 2 · answered Nov 28 '17 at 12:25

In my case there was an error detecting the extension in this line

$extension = strtoupper(pathinfo($inputFile, PATHINFO_EXTENSION));

if you need to solve just check from the name parameter

$extension = strtoupper(explode(".", $_FILES['spreadsheet']['name'])[1]);

The rest is working thanks :)

Reading spreadsheet using PHPExcel

2 Answers2

Linked