CODEWITHSUNDEEP
phphtmlphpexcel
Demosthenes
Demosthenes

Reputation: 125

'The filename 062014.xlsx is not recognised as an OLE file'

I am working on a complex program that deals with Excel, so I am using PHPExcel to search and edit the Excel file from a browser.

My problem comes in the editing portion of the program, so I wrote a basic program to edit the existing Excel page.

It seems that PHPExcel does not recognize the file created in Excel as an Excel file. This is done on my own server with an Excel page I created with Excel. The filename is 062014.xlsx.

On the HTML side I named the text boxes C3, D3, and E3, so their names will easily correspond with Excel cells ( where the php $cell variable comes from).

What I want to do is take the text in the html text boxes and rewrite corresponding cells in Excel with the data from the html textboxes. Posted is my whole code from html and php, if someone can tell me where my program is going wrong, I would greatly appreciate it.

    <html>
    <head>


    <form method="POST" action="lilrevisetest.php" id="excelform">

    <input type="text" value="foo" name="C3" />
        <input type="text" value="foo" name="D3" />
        <input type="text" value="foo" name="E3" />
    <button type="submit">Submit</button>
    </form>


    </body>
    </html>
    



    <body>
     <html>
          
      
    <?php

    include 'PHPExcel/IOFactory.php';
     $n=1;
     $x="C";
     $y=1;


     $file = "062014.xlsx";
  
   
      $inputFileType = PHPExcel_IOFactory::identify($file);
     $inputFileType = 'Excel5';
     $objReader = PHPExcel_IOFactory::createReader($inputFileType);
     $objReader->setReadDataOnly(false);

      $objPHPExcel = $objReader->load($file);

      $objWorksheet = $objPHPExcel->getActiveSheet();
      $fileObj = fopen("$file", "rt" );


       $y = 3; 
       $x= "C";

       for($n=1; $n<4; $n++){
        $cell = $x . $y; 

        echo $cell; 
        if (isset($_POST[$cell])){
        $string = ($_POST[$cell]);
           $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $inputFileType);
          
          $objWorksheet ->setCellValue("$cell","$string");
          $objWriter->save($file);
     }
     echo "<td> <input type ='text' value= '$string' name = '$cell'/></td>";
         $x= ++$x;
    }

    ?>
    
          </html>
       </body>

Upvotes: 12

Views: 35981

Answers (3)

Igor
Igor

Reputation: 1111

In case of Laravel. The possible reason of errors like [filename] is not recognised as an OLE file or Could not find zip member at Laravel Excel package can be that you use standard Queue Job dispatching when you handle an excel file import, but you have to use a Laravel Excel based Queued reading. If you working with Filament Actions (table header action for example) this also may happen because it handling via Queue dispatching.

An example of error:

<?php
// you have a Laravel Job like that

namespace App\Jobs;

use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;

class MyJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
    
    public function handle(): void
    {
        // something like that
        \Maatwebsite\Excel\Facades\Excel::import($someExcelObj, 'file.xlsx');
    }
}

And then you handle your Job via dispatching (this is WRONG):

<?php
// [...] some action code
// call job - WRONG
\App\Jobs\MyJob::dispatch();

Solution

Do not use Laravel based Queues, but use Laravel Excel Queued reading, but in this case you can only work with .xlsx

If nothing helps/work use alternative package SimpleXLSX or SimpleXLS

Hope that helps.

Upvotes: 1

dani supriyadi
dani supriyadi

Reputation: 1

This could work:

<?php
require 'vendor1/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Writer\Pdf;
$excelreader = IOFactory::createReader('Xml');
$spreadsheet = new Spreadsheet();
$writer = new Xlsx($spreadsheet);
$writer->setPreCalculateFormulas(false);

If the file being read is XLS simply replace

$excelreader = IOFactory::createReader('Xml');

with

$excelreader = IOFactory::createReader('Xlsx');

Upvotes: 0

Mark Baker
Mark Baker

Reputation: 212402

You are trying to load an xlsx file (OfficeOpenXML-format) using the Excel5 (BIFF-format) Reader.

$inputFileType = 'Excel5';
$objReader = PHPExcel_IOFactory::createReader($inputFileType);

You should be using the correct Reader for the file type you're trying to load, otherwise you will get errors.

You've already used 

$inputFileType = PHPExcel_IOFactory::identify($file);

to identify the filetype and the correct Reader to use, so why are you ignoring this and setting it manually (and incorrectly) yourself?

Additionally

Another problem is likely to be the fact that the file is already open when you try to save it.

You're loading $file (062014.xlsx) using the PHPExcel loader, no problem.

For some unknown reason, you then execute

$fileObj = fopen("$file", "rt" );

though you don't do anything with $fileObj at all, but doing this leaves it open

When you try to save using $objWriter->save($file);, the file is still held open, so the save will fail (nothing to do with the filename, simply the fact that the file is open).

The solution is as simple as deleting the line

$fileObj = fopen("$file", "rt" );

Upvotes: 26

Related Questions