PHPExcel generated file via $objWriter->save('php://output')- does it require permissions?

Question

I have a code in php CodeIgniter that extracts data from database and generates an xls file via PHPExcel.

The problem is that whenever I upload the code into another server, it generates an .xls file with 0kb, and the error while opening is:
"Excel cannot open the file "____" because the file format or file extension not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file".

The incoming data is from same table and all the libraries used are also same.

My questions:
1. Are there any r/w permission applied by the server which makes the phpexcel file 0kb? I am using CodeIgniter.
2. Is there any way to see what is written in the PHPExcel object?
3. Are there any things I am missing out?

My code that generates xls file is:

// Set header and footer. When no different headers for odd/even are used, odd header is assumed.
    $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BInvoice&RPrinted on &D');
    $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

// Set page orientation and size
    $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
    $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);

    $this->load->library('PHPExcel/PHPExcel_IOFactory');

    $file_name = $uri_year . "-" . $uri_month . "_staff_report.xlsx";
// Redirect output to a client web browser (Excel2007)
//ob_end_clean();
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename=' . $file_name);
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
//  ob_end_clean();

    $objWriter->save('php://output');

Thank you in advance.

My output as displayed in the test server is:

enter image description here

Solution: It turns out that the php version of the server1 is 5.2.9
The php version of server2 is 5.1.6

Error generated in server2: Fatal error: Class 'ZipArchive' not found in /var/www/html/APP_NAME/application/libraries/PHPExcel/Writer/Excel2007.php on line 225

The ZipArchive module requires php version >5.2

After the update, it works.

Thank you guys for your suggestions.

whats $uri_year and $uri_month, echo them if they have php error your file name will contain it & will cause error you mentioned. — mokNathal, Jul 03 '15 at 05:10
@mokNathal: they give the values month year values. They are well defined. Error reporting is off as well. — abhisekG, Jul 03 '15 at 05:23
if that is well defined, i believe it does not have a problem with that. what should be the expected output of this? — mmr, Jul 03 '15 at 05:26
Yes it is well defined. I am afraid I am not allowed to show the total code. — abhisekG, Jul 03 '15 at 05:29
@mmr: The output displays all the login reports of my company in xls. — abhisekG, Jul 03 '15 at 05:34
@mokNathal: The month year values are 2071 and 4 respectively. — abhisekG, Jul 03 '15 at 05:39
maybe you are just writing something after the $objWriter->save(); row? add exit; afterwards and see whether it works better... — ejuhjav, Jul 03 '15 at 05:48

score 3 · Answer 1 · answered Jul 03 '15 at 05:42

It outputs 0kb or "Excel cannot open the file "_ because the execution of the script encountered unusual error. Enable your error reporting, put this at the top of your script:

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

Yes, you may need to check the proper r/w permission and ownership of PHPExcel.php but the output file does not need anymore.

I have tested your code and it works fine..

<?php

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

/** Include PHPExcel */
require_once('PHPExcel.php');
$objPHPExcel = new PHPExcel();

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Set header and footer. When no different headers for odd/even are used, odd header is assumed.
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&BInvoice&RPrinted on &D');
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

// Set page orientation and size
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

// $this->load->library('PHPExcel/PHPExcel_IOFactory');

$uri_year = "2015";
$uri_month = "now";
$file_name = $uri_year . "-" . $uri_month . "_staff_report.xlsx";

$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', "This is a test");

// Redirect output to a client web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=' . $file_name);
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//  ob_end_clean();
$objWriter->save('php://output');

?>

You may need to check if you have the right libraries for Excel2007, i have changed it to Excel5, but you can use Excel2007 and it works fine for me.

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

Thank you for the help. however as i mentioned in mokNathal's code, its says: Fatal error: Class 'PHPExcel_IOFactory' not found in /var/www/html/lis-intranet/application/controllers/gen_xls.php on line 442 — abhisekG, Jul 03 '15 at 06:56

score 1 · Answer 2 · answered Jul 03 '15 at 06:17

please check following code tested:

 public function demo()
    {

        error_reporting(E_ALL);
        ini_set('display_errors', TRUE);
        ini_set('display_startup_errors', TRUE);

        define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

        /** Include PHPExcel */
        $this->load->library('PHPExcel');

        $objPHPExcel = new PHPExcel();

        $objPHPExcel->getProperties()->setCreator("MokNathal")
                                     ->setLastModifiedBy("MokNathal")
                                     ->setTitle("STAFF REPORT")
                                     ->setSubject("STAFF REPORT")
                                     ->setDescription("STAFF REPORT")
                                     ->setKeywords("STAFF REPORT")
                                     ->setCategory("STAFF REPORT");



        $objPHPExcel->getActiveSheet()->mergeCells('A1:F1');
        $objPHPExcel->setActiveSheetIndex(0)
                    ->getCell('A1')->setValue("Name of staff:aaaaaa Lname");
        $objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getAlignment()->setWrapText(true);       

        $objPHPExcel->getActiveSheet()->mergeCells('A2:F2');
        $objPHPExcel->setActiveSheetIndex(0)
                    ->getCell('A2')->setValue("Group:Sunday Holiday Plan");
        $objPHPExcel->getActiveSheet()->getStyle('A2:F2')->getAlignment()->setWrapText(true);       

        $objPHPExcel->getActiveSheet()->mergeCells('A3:F3');
        $objPHPExcel->setActiveSheetIndex(0)
                    ->getCell('A3')->setValue("Login Details");
        $objPHPExcel->getActiveSheet()->getStyle('A3:F3')->getAlignment()->setWrapText(true);           

        $objPHPExcel->getActiveSheet()->mergeCells('A4:F4');
        $objPHPExcel->setActiveSheetIndex(0)
                    ->getCell('A4')->setValue("Checkin time:09:05:00am");
        $objPHPExcel->getActiveSheet()->getStyle('A4:F4')->getAlignment()->setWrapText(true);           

        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A4')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A5')->getFont()->setBold(true);
        $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A5', 'Year')
                     ->setCellValue('B5', 'Month')
                     ->setCellValue('C5', 'Day')
                     ->setCellValue('D5', 'Login Time')
                      ->setCellValue('E5', 'Logout Time')
                       ->setCellValue('F5', 'Login Status');

        $objPHPExcel->getActiveSheet()->getStyle('A5:F5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->getStyle('A5')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('B5')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C5')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('D5')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('E5')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('F5')->getFont()->setBold(true);

        $i=6;
        $uri_year=2071;
        $uri_month=4;
        for($j=1;$j<=30;$j++)
        {
        $objPHPExcel->setActiveSheetIndex(0)
                         ->setCellValue('A'.$i, $uri_year)
                         ->setCellValue('B'.$i, $uri_month)
                         ->setCellValue('C'.$i, $j);

                $i++;

        }
        foreach(range('A','F') as $columnID) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
                ->setAutoSize(true);
        }

        $objPHPExcel->getActiveSheet()->setTitle('Trasaction List');


        $objPHPExcel->setActiveSheetIndex(0);

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

        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header("Content-Disposition: attachment; filename=".$uri_year."-".$uri_month."_staff_report.xlsx\"");
        header("Cache-Control: max-age=0");

        $objWriter->save("php://output");

    }

Thank you very much for your script... I am getting the following error: Fatal error: Class 'PHPExcel_IOFactory' not found in /var/www/html/lis-intranet/application/controllers/gen_xls.php on line 442 Does this mean that the library that I am using is not good? What should I do? — abhisekG, Jul 03 '15 at 06:43
It is in application\libraries\PHPExcel Isnt it where it should be? — abhisekG, Jul 03 '15 at 06:48
if it PHPExcel.php file in application\libraries\PHPExcel folder modify line `$this->load->library('PHPExcel');` to `$this->load->library('PHPExcel/PHPExcel');` — mokNathal, Jul 03 '15 at 06:57
Thank you for the answer. But the file is in the right place. If the file was in the wrong location, I would have got an error while instantiating PHPExcel Object itself. — abhisekG, Jul 03 '15 at 07:18
Okay,does your library folder contains PHPExcel folder and PHPExcel.php(both in libraries folder) file from classes folder of PHPExcel developer package. — mokNathal, Jul 03 '15 at 07:24
It has. Nothing. No chance. Nothing is working. I will see other options. Thank you for your suggestions. I really appreciate it. — abhisekG, Jul 03 '15 at 08:12

PHPExcel generated file via $objWriter->save('php://output')- does it require permissions?

2 Answers2