2

I am facing problem in export excel using spout library. I don't know where is the issue in my code. I don't know much about spout library. I have tried many times but the same error occurs again and again. Please guide me where is the issue.

Code:

<?php 
include('php_script/db.php');
use Box\Spout\Common\Type;
use Box\Spout\Writer\Style\Border;
use Box\Spout\Writer\Style\BorderBuilder;
use Box\Spout\Writer\Style\Color;
use Box\Spout\Writer\Style\StyleBuilder;
use Box\Spout\Writer\WriterFactory;
include('php_script/spout/src/Spout/Autoloader/autoload.php');  
$sql = mysqli_query($con,"select * from person ");
$border = (new BorderBuilder())
        ->setBorderBottom(Color::GREEN, Border::WIDTH_THIN, Border::STYLE_DASHED)
        //->setFontColor(Color::BLUE)
        //->setBackgroundColor(Color::YELLOW)
        ->build();
    $style = (new StyleBuilder())
        ->setBorder($border)
        ->build();
    $filePath = "person".date("Y-m-d-H-i-s").'.xlsx';
    $writer = WriterFactory::create(Type::XLSX);
    $writer->openToFile($filePath);

    $array = ['TYPE'];
    
    $writer->addRowWithStyle($array, $style);

    
   
    while( $rows = mysqli_fetch_assoc($sql)) {
   
    $Type_subsidiary = $rows['Type_subsidiary'];
    $data =  [$Type_subsidiary];
    $writer->addRow($data);
    }

    $writer->close();
     if (file_exists($filePath)) {
            header('Content-Description: File Transfer');
            header('Content-Type: application/octet-stream');
            header('Content-Disposition: attachment; filename="'.basename($filePath).'"');
            header('Expires: 0');
            header('Cache-Control: must-revalidate');
            header('Pragma: public');
            header('Content-Length: ' . filesize($filePath));
            readfile($filePath);
            exit;
        }


?>

Error:

Excel cannot open the file 'person2018-09-19-07-20-30.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file

enter image description here

MaxiGui
  • 6,190
  • 4
  • 16
  • 33
Mohd Atiq
  • 151
  • 1
  • 13
  • Try changing XLSX to XLS. The xlsx file is basically a zip file containing multiple files to help construct the file. –  Sep 19 '18 at 06:03
  • I have changed XLSX to XLS. $filePath = "person".date("Y-m-d-H-i-s").'.xls'; but does not work. :( – Mohd Atiq Sep 19 '18 at 06:06
  • Pictures of code and not following the MVCE standard (https://stackoverflow.com/help/mcve) here on stack is bad form and won't get your question answered. – Alex Sep 19 '18 at 06:28
  • That being said, can you access the file that was created directly? It should be saved. – Alex Sep 19 '18 at 06:37
  • File is saved in same directory. – Mohd Atiq Sep 19 '18 at 06:49
  • and I'm asking if you can open it directly. as i'm assuming that the error is coming from the browser downloaded file – Alex Sep 19 '18 at 06:50
  • This code is working on local server but not on live server. – Mohd Atiq Sep 19 '18 at 07:26
  • Does this answer your question? [PHP : Excel cannot open the file because the file format or file extension is not valid](https://stackoverflow.com/questions/60941026/php-excel-cannot-open-the-file-because-the-file-format-or-file-extension-is-no) – MaxiGui Feb 18 '21 at 09:47

1 Answers1

1

have you tried to let spout create and "send" the file directly to browser without specifying headers as specified in their doc http://opensource.box.com/spout/getting-started/ ?

$writer->openToBrowser($fileName); // stream data directly to the browser

Hope this helps, in our applications (based on Symfony), we use spout but no need to specify headers and filepath.

S. Bureau
  • 146
  • 1
  • 6
  • I have tried $writer->openToBrowser($fileName); this is working on local server but not on live server. – Mohd Atiq Sep 19 '18 at 08:36
  • As Alex asked, are you able to open the created file directly on live server ? Not via browser but via command line, ftp or whatever you're used to. This will help to know if the error comes from the file creation or the "sending" to the browser. – S. Bureau Sep 19 '18 at 08:41