CODEWITHSUNDEEP
phpexcel
mmalik
mmalik

Reputation: 1

Excel 2010 cannot open certain size xls files created by PHPExcel

We have been using PHPExcel in our production environment for over 4 years now and it has been working great without any problem until last week when Excel 2010 & 2103 was not able to open one of many xls files generated by PHPExcel. The error which we got was

Error: "Excel found unreadable content in file. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes." .

We tried to repair and step through other suggestions we found but none worked.

After generating many files we were at least able to reproduce this issue consistently. Though it seems very strange but Excel 2010 & 2013 running on windows 7 & 10 is not able to open any files generated by phpExcel that are between 6914K - 6977K file size.

We cannot find any solution or reason why Excel 2010/2013 would not open these files. Are we missing something? Thanks in advance for any suggestions.

We are using...

PHPExcel: 1.8 Webserver: Apache/2.2.15 (Unix) & PHP 5.3.3 running on Red Hat

Sample code to reproduce the issue.

require_once dirname(dirname(__FILE__)) . '/Classes/PHPExcel.php';

$timestamp = date("YmdHis");
$filename = "report_" . $timestamp . ".xls";
$rowcount = 20150;
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);

$objPHPExcel = new PHPExcel();

 $VAR1 = '99999';
 $VAR2 = 'XXX';
 $VAR3 = 'XXXX';
 $VAR4 = 'XXXXX';
 $VAR5 = 'XXX';
 $VAR6 = 'XXXXXXXXXXXXXXXXXXXX';
 $VAR7 = 'XXX';
 $VAR8 = 'XXXXXXXXXXXXXXXXXXX';
 $VAR9 = 'XXXXXXXXXXXXXXXXXXX';
 $VAR10 = '999999999';
 $VAR11 = 'xxxxxxxxxxx';
 $VAR12 = 'XXXXXXX';
 $VAR13 = 'XXXX';
 $VAR14 = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
 $VAR15 = 'XXXXXXXXXXXXXXXXXXXXXXXXXXX';
 $VAR16 = 'XXXXXXXXXXXXXXXXXXXXXXXXXXX';
 $VAR17 = 'XXX';
 $VAR18 = '99999';
 $VAR19 = '9999999999';
 $VAR20 = '999999999';
 $VAR21 = 'XXXXXXXXXXXXXXXXXX';
 $VAR22 = '99999';
 $VAR23 = '999999';

for ( $i=2 ; $i<intval($rowcount) ; $i++ )
{
    $objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue("A$i", "$VAR1")
    ->setCellValue("B$i", "$VAR2")
    ->setCellValue("C$i", "$VAR3")  
    ->setCellValue("D$i", "$VAR4")   
    ->setCellValue("E$i", "$VAR5")             
    ->setCellValue("F$i", "$VAR6")
    ->setCellValue("G$i", "$VAR7")
    ->setCellValue("H$i", "$VAR8")
    ->setCellValue("I$i", "$VAR9")
    ->setCellValue("J$i", "$VAR10")
    ->setCellValue("K$i", "$VAR11")
    ->setCellValue("L$i", "$VAR12")
    ->setCellValue("M$i", "$VAR13")
    ->setCellValue("N$i", "$VAR14")
    ->setCellValue("O$i", "$VAR15")
    ->setCellValue("P$i", "$VAR16")
    ->setCellValue("Q$i", "$VAR17")               
    ->setCellValue("R$i", "$VAR18")           
    ->setCellValue("S$i", "$VAR19")           
    ->setCellValue("T$i", "$VAR20")
    ->setCellValue("U$i", "$VAR21")
    ->setCellValue("V$i", "$VAR22")
    ->setCellValue("W$i", "$VAR23");
}


$objPHPExcel->getActiveSheet()->getColumnDimension("A")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("B")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("C")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("D")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("E")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("F")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("G")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("H")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("I")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("J")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("K")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("L")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("M")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("N")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("O")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("P")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("Q")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("R")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("S")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("T")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("U")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("V")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("W")->setAutoSize(true);

ini_set("max_execution_time","900");    
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save("reports/$filename"); 

$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
unset($objWriter);
echo "<a href=reports/$filename>Download this file</a><br><br>";

Upvotes: 0

Views: 456

Answers (1)

Jerson Supertramp
Jerson Supertramp

Reputation: 46

Try to change this:

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

To this:

$objWriter = new PHPExcel_Writer_Excel2007 ( $objPHPExcel );

And change the extension .xls to .xlsx

Upvotes: 1

Related Questions