PHPExcel error while saving
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;
Related videos on Youtube
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, 2022Comments
-
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();
orob_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 toxls
onfilename
properties, and the Excel now can be opened. But It shows php errorMessage: 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 over 5 yearsThanks this worked for me. I'm on php7, may be for below versions ob_end_clean will work.
-
wayneOS over 5 yearsThat should be a comment.
-
Hermit over 5 yearsit is an answer for the above problem @wayneOS
-
lemk0 almost 5 yearstnx, you saved my night :-) btw. I'm on php7.2