PHPExcel error while saving

15,605

Solution 1

I found a fix,go to Classes/PHPExcel/Writer/Excel2007.php comment out the following lines, php 7 has a wierd return date type so doing this will enable php excel to work in php 7 pronto. any way

$saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType();
PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType);

to

//$saveDateReturnType = PHPExcel_Calculation_Functions::getReturnDateType();
//PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
//PHPExcel_Calculation_Functions::setReturnDateType($saveDateReturnType);

worked for me.

Solution 2

Try install ZipArchive Class i.e sudo apt-get install php7.0-zip

as it might be the problem for newly formed server.(It was for me)

Solution 3

Add ob_end_clean()

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="Filename.xlsx"');
    header('Cache-Control: max-age=0');
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
    ob_end_clean();
    $objWriter->save('php://output');
    exit;

Solution 4

Saving the $writer output to a file and redirecting to it solved my problem

I also set the time limit and memory limit high so that they don't cause any problem

Code:

        $writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet); // instantiate Xlsx
        $writer->setPreCalculateFormulas(false);
        //ob_end_clean();
        set_time_limit(500);
        ini_set('memory_limit', '-1');
        $writer->save($filename . ".xlsx"); // save the file to root of project
        redirect('/' . $filename . ".xlsx"); // redirect the user to the file

Solution 5

I had same problem with 4000+ rows. Increasing both memory limit and time limit fixed the problem.

Reason for ERR_INVALID_RESPONSE behavior is that you send xls/x headers but then http error 500 from running out of memory or time.

set_time_limit(120);
ini_set('memory_limit', '256M');
...
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_end_clean();
$objWriter->save('php://output');
exit;
Share:
15,605

Related videos on Youtube

M Argus Chopin Gyver
Author by

M Argus Chopin Gyver

Just a junior programming, know about little PHP, little JS, little Jquery and another little. Hope I can find the answer from my question on this online forum..

Updated on September 15, 2022

Comments

  • M Argus Chopin Gyver
    M Argus Chopin Gyver about 1 year

    I'm using PHPExcel for exporting a report.

    It's gave me an error when I run it on internet (using PHP 5.6)..

    But, when I'm test on my localhost, it's fine. Works perfectly (using PHP 5.4.31)

    Here's my code

    function downloadExcelBrand($brand,$tglAwal,$tglAkhir)
    {
    
        $this->load->library('php_excel');
    
        $objPHPExcel = new PHPExcel(); 
        // print_r($objPHPExcel);die();
        $objPHPExcel->setActiveSheetIndex(0);
    
        $objPHPExcel->getActiveSheet()->SetCellValue('A1', "Concept");
        $objPHPExcel->getActiveSheet()->SetCellValue('B1', $brand);
    
        $objPHPExcel->getActiveSheet()->SetCellValue('A2', "Period");
        $objPHPExcel->getActiveSheet()->SetCellValue('B2', $tglAwal." to ".$tglAkhir);
    
        $objPHPExcel->getActiveSheet()->SetCellValue('A5', "Boutique");
        $objPHPExcel->getActiveSheet()->SetCellValue('B5', "Q1");
        $objPHPExcel->getActiveSheet()->SetCellValue('C5', "Q2");
        $objPHPExcel->getActiveSheet()->SetCellValue('D5', "Q3");
        $objPHPExcel->getActiveSheet()->SetCellValue('E5', "Q4");
        $objPHPExcel->getActiveSheet()->SetCellValue('F5', "BBC Score");
    
        $data = $this->surveymodel->getDataLaporanBrand($brand,$tglAwal,$tglAkhir);
        // print_r($data);die();
        $i = 5;
        foreach ($data as $index=>$value) {
            // print_r($data[$index+1]);die();
            if( $index%4 == 0){
                $i++;
                // print_r($value["Score"]);print_r($value['TotalData']);die();
                $AvgQ1 = $value["Score"] / $value['TotalData'];
                // print_r($AvgQ1);die();
                $AvgQ2 = $data[$index+1]["Score"]/$data[$index+1]['TotalData'];
                $AVGQ3 = $data[$index+2]["Score"]/$data[$index+2]['TotalData'];
                $AvgQ4 = $data[$index+3]["Score"]/$data[$index+3]['TotalData'];
                $BSC = $AvgQ1+$AvgQ2+$AVGQ3+$AvgQ4;
    
                $objPHPExcel->getActiveSheet()->SetCellValue('A'.$i, $value["boutiqueID"]);
                $objPHPExcel->getActiveSheet()->SetCellValue('B'.$i, $AvgQ1);
                $objPHPExcel->getActiveSheet()->SetCellValue('C'.$i, $AvgQ2);
                $objPHPExcel->getActiveSheet()->SetCellValue('D'.$i, $AVGQ3);
                $objPHPExcel->getActiveSheet()->SetCellValue('E'.$i, $AvgQ4);
                $objPHPExcel->getActiveSheet()->SetCellValue('F'.$i, $BSC);
            }
        }
    
        // Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
        // Write the Excel file to filename some_excel_file.xlsx in the current directory
        header('Content-type: application/vnd.ms-excel');
        // It will be called file.xls
        header('Content-Disposition: attachment; filename="Report_By_Brand.xlsx"');
        // Write file to the browser
        $objWriter->save('php://output');
    }
    

    the weird things happen when I remove the $objWriter->save('php://output'); code. It's asking for saving the excel, but the excel file can't be open because it's corrupted..

    *EDIT

    • The error is

      This site can’t be reached The webpage at http://my-link-in-here might be temporarily down or it may have moved permanently to a new web address. ERR_INVALID_RESPONSE

    *UPDATE

    • I try to add ob_end_clean(); or ob_clean(); before $objWriter->save('php://output'); and it saving the Excel, but I can't open it because Excel says "File Format or File Extention is not valid".

    • I try to change the xlsx extension to xls on filename properties, and the Excel now can be opened. But It shows php error Message: ob_end_clean(): failed to delete buffer. No buffer to delete

    • I try to keep the extension but I deleted the ob_end_clean();, and the error comes again..

    *Solution :

    • I changed the code to be Excel5 like this

      $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
      header('Content-Type: application/vnd.ms-excel');
      header('Content-Disposition: attachment;filename="userList.xls"');
      

      And it works like champion. Anyone can make a explanation ? I will give the solution to the one who can make the explanation

  • narasimharaosp
    narasimharaosp over 5 years
    Thanks this worked for me. I'm on php7, may be for below versions ob_end_clean will work.
  • wayneOS
    wayneOS over 5 years
    That should be a comment.
  • Hermit
    Hermit over 5 years
    it is an answer for the above problem @wayneOS
  • lemk0
    lemk0 almost 5 years
    tnx, you saved my night :-) btw. I'm on php7.2