PHPExcel - .xlsx file downloads unreadable content

1.5k Views Asked by At

I was trying export my data to spread sheet and it was working fine in my localhost , but when I uploaded that to server , it always downloads files with unreadable content . Writing the code here.

There is php_xml, php_zip and gd already installed in my server. The file downloaded is readonly.

    error_reporting(E_ALL);
    ini_set('display_errors', TRUE);
    ini_set('display_startup_errors', TRUE);
    date_default_timezone_set('Europe/London');

    if (PHP_SAPI == 'cli')
        die('This example should only be run from a Web Browser');

    /** Include PHPExcel */
    require_once dirname(__FILE__) . '/lib/PHPExcel.php';


    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();

    // Set document properties
    $objPHPExcel->getProperties()->setCreator("test")
                         ->setLastModifiedBy("test")
                         ->setTitle("test Report")
                         ->setSubject("test Report")
                         ->setDescription("test Report")
                         ->setKeywords("test Report")
                         ->setCategory("test Report");

    //Title
    $objPHPExcel->getActiveSheet()->mergeCells('A1:J1');
    $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getActiveSheet()->getStyle("A1:J1")->getFont()->setSize(20);
    $styleArray = array(
        'font' => array(
            'bold' => true
        )
    );
    $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->applyFromArray($styleArray);
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', 'test Report');
    $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(30);
    // //Set Column headlines
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A2', 'First Name')
        ->setCellValue('B2', 'Last name')
        ->setCellValue('C2', 'State')
        ->setCellValue('D2', 'Email')
        ->setCellValue('E2', 'Mob')
        ->setCellValue('F2', 'address')
        ->setCellValue('G2', 'PIN')
        ->setCellValue('H2', 'status')
        ->setCellValue('I2', 'condition')
        ->setCellValue('J2', 'log');

  $objPHPExcel->getActiveSheet()->getStyle("A2:J2")->getFont()->setSize(10);
  $objPHPExcel->getActiveSheet()->getStyle('A2:J2')->applyFromArray($styleArray);
  $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
  $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
  $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
  $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(18);
  $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(5);
  $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(7);
  $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
  $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
  $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(18);
  $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);

$colArray=array("A","B","C","D","E","F","G","H","I","J");
 $rowNumber = 3; 
    while ($row = mysqli_fetch_row($result)) { 
    $col = 0; 
    foreach($row as $cell) { 
        $objPHPExcel->getActiveSheet()->setCellValue($colArray[$col].$rowNumber,$cell); 
        $objPHPExcel->getActiveSheet()->getStyle($colArray[$col].$rowNumber)->getFont()->setSize(10);
        $col++; 
    } 
    $rowNumber++; 

    } 
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('test Report');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="testreport.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
2

There are 2 best solutions below

2
On

I really don't see a problem with your core logic. I was able to generate valid xlsx file off my linux box with slight modifications

  1. Updated require_once line as follows (I somehow felt comfortable in keep PHPExcel's default folder structure intact)

require_once dirname(FILE) . '/../Classes/PHPExcel/IOFactory.php';

  1. Since I don't have Mysqli corresponding to the example, I modified it to..

    $row = array(1,2,3,4,5,6,7,8,9);
    //while ($row = mysqli_fetch_row($result)) {

Overall, I guess, you need to see if mysqli data is valid and you initiate PHPExcel properly. Other than that, I don't see any problem here.

0
On

I used ob_start() before starting document generation then ended with ob_end_clean() which resolved my issue.